mysql的索引——innodb索引（1）聚簇索引和次级索引

2019独角兽企业重金招聘Python工程师标准>>>

官方文档翻译：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.

每张Innodb引擎表都有一个特殊的索引，聚簇索引，它保存着每行数据。一般情况，聚簇索引就是主键索引。为了得到更高效的查询，插入，或者其他的数据库操作，你必须理解innodb引擎如何使用聚簇索引优化大多数查询和dml操作。

• 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.)

通过聚簇索引来寻找一行数据是非常快的，这是因为行数据和index保存在由index开头的同一个page上。如果表特别大，聚簇索引的这种构造就能节省磁盘I/O资源（索引和数据在不同页上时，根据索引来寻找数据存储页消耗的IO），比如myisam引擎，把索引和数据页分开存放；

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.

除了聚簇索引之外的其他索引类型都属于次级索引。在Innodb中，次级索引的每行数据都包含这条数据的主键列，主键列就像是为次级索引自己制定的列；聚集索引中，innodb通过主键值来查找数据行。

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

如果主键过长，次级索引就需要更大的空间，因此，使用短的主键列是非常有用的。

 

总结：

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

聚簇索引：索引指针直接指向数据页的索引，聚簇索引对数据物理页按索引键值进行物理组织排序；

主键索引：建在主键上的索引；innodb中，聚簇索引在有主键的情况下，默认指定主键为聚簇索引,因此,innodb中，主键索引一般都是聚簇索引。

二级索引：除了聚簇索引的，都称为2级索引；innodb中，二级索引查找数据行，需要先找到对应的主键，然后根据主键查找对应的行值。

 

2、聚簇索引的选择？

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

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

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

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

很多参考上提到：不同表的自增长列存放在同一个表中，由mysql自己管理。查询时，需要先在这张表中找到自增长列，然后再去找对应的数据；这样一来，因为所有的自建ID存放在这张表中，因此，这张表就变成了查询的瓶颈，导致innodb自建主键的效率比指定主键差。

还没有做过验证。

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

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

 

转载于:https://my.oschina.net/u/1034481/blog/824094