2017/01/13 09:45

# 官方文档翻译：mysql的索引——innodb索引（1）聚簇索引和次级索引

#### 14.2.9.1 Clustered and Secondary Indexes

Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table.

• If you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index.

• If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULLcolumns as the primary key and InnoDB uses it as the clustered index.

• If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

• 如果你为表定义了一个主键，innodb就使用它作为聚簇索引。
• 如果你没有定义主键，mysql选择非空类型的唯一索引来作为作为主键，并且innodb会用它作为聚簇索引。
• 如果表中既没有主键，又没有合适的唯一索引，innodb内部生成一个隐式聚簇索引，建在由rowid组成的虚拟列上。在这张表中，innodb为每行数据指定一个rowid，数据行根据ID来排序。这些row id是由一些占6字节空间的自增长列组成。当有新数据插入的时候，row id增长，这样，保证row id就是按照数据的物理写入顺序来组织行。

How the Clustered Index Speeds Up Queries

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAMuses one file for data rows and another for index records.)

How Secondary Indexes Relate to the Clustered Index

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

1、啥是聚簇索引？啥是主键索引？啥叫辅助索引（二级索引）？

2、聚簇索引的选择？

innodb中，聚簇索引是mysql自己来决定的。选择的顺序是：

（1）定义了主键，就选择主键；

（2）没有主键，选择第一个非空的唯一索引；

（3）两者都没有，innodb自己生成一个占6byte的自增长列。然后以它作为聚簇索引列

3、次级索引隐式包含主键的列。

4、在聚簇索引的每个叶节点上，存放以index键开始的index+数据行，省去了根据索引查找数据页的I/O;

0
0 收藏

0 评论
0 收藏
0