关于mysql中的key和index

最新推荐文章于 2022-10-20 14:46:07 发布

转载最新推荐文章于 2022-10-20 14:46:07 发布 · 2.3k 阅读

文章标签：

#mysql #primary key #index

MySQL 专栏收录该内容

5 篇文章

订阅专栏

本文详细解释了MySQL中主键与索引的区别、作用及创建方式，包括如何选择合适的主键，以及如何利用索引来优化查询效率。

1. 官方文档：http://dev.mysql.com/doc/refman/5.5/en/create-table.html

KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multipleNULL values for columns that can contain NULL.
A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY.

In InnoDB tables, keep the PRIMARY KEY short to minimize storage overhead for secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row. (SeeSection 14.3.11, “InnoDB Table and Index Structures”.)
In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicatedUNIQUE keys.
A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using thePRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause.
If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements.
In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See Section 13.7.5.23, “SHOW INDEXSyntax”.

2. http://forums.macrumors.com/showthread.php?t=334697

Note that "primary" is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (i.e. enforces referential integrity ...)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).

3. http://wenku.baidu.com/view/ff96febb1a37f111f1855bf8.html

在创建innoDB表时，在每张表中都只能有一个主键，如果在创建表时没有明确地定义主键（Primary Key），则innoDB引擎会自动按照如下方式选择或创建主键：首先看表中是否有非空唯一索引（Unique NOT NULL）如果有，则该列即为主键。不符合上述条件，则自动创建一个6字节大小的RowID。