Loading

浅谈InnoDB中的聚簇索引和二级索引[译]

聚簇索引 (主键索引)(Clustered Index (Primary Index))

聚簇索引与其说是索引,不如说是InnoDB用来存储记录的数据容器更为恰当。

InnoDB中的聚簇索引采用B-Tree组织起来,每个节点都是一个Page(InnoDB存储记录的最小单位);非叶节点存 Key 的值和指向孩子节点的指针,叶子节点则存储记录和指向相邻叶节点的指针(所有叶节点构成一个双向链表),下面是一个简单的示意图:

InnoDB根据Key值顺序存储记录,相邻的Node彼此通过指针连接,这样有两个好处:

  1. 这样相关的记录会存储的比较近,读取相关记录的时候只需要Load少数Page就好。例如上图若要读取key 5和key 6的记录,只要加载page 5就好。
  2. 执行范围查询时不用整棵B+树都扫描一遍,只要找到最小的key值所在的叶节点Page,一直往后读即可。例如上图查询key值在5〜10的记录,只要找到key 5所在的page 5,然后一直往后读值到key 10所在的page 7为止。

当然排好序的记录也有一些缺点,如果插入key值无序的记录时容易造成性能问题。例如上图如果插入一条key为13的记录时,MySQL会直接写入到page 8;但若是插入一条key为9的记录时会导致page 7发生页拆分(Page Split),这种情况下MySQL会在Page之间移动记录,继而影响性能。

MySQL并不会把Page的全部空间都用完,相反,它会保留一部分空间为日后添加或更新使用,如上图的page 6。根据MySQL 5.7 Reference Manual

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full.

如果index的记录是顺序插入,索引页(Index Page)的使用率会在15/16左右,但若是乱序插入的话,索引页的使用率则会在1/2到15/16之间。

由上述原因可以知道,聚簇索引是如何影响范围查询、插入记录的性能以及Page空间的使用率的,所以一个恰当的聚簇索引键值(Clustered Key)很重要。MySQL选择聚簇索引键值的规则如下:

  1. 有Primary Key,选Primary Key;
  2. 没有Primary Key,选第一个NOT NULL的UNIQUE Key;
  3. 若都没有,则InnoDB生成一个auto increment的隐藏字段做聚簇索引键值。

二级索引(Secondary Index)

二级索引同样使用B-Tree数据结构,不同的是叶节点只存储二级索引的键值和聚簇索引键值(通常是Primary Key),聚簇索引键值是用于回表查询该条记录。

注意到上图中二级索引键值的顺序和聚簇索引键值顺序通常不同,所以二级索引做范围查询读取记录的性能通常不如聚簇索引高效(回表操作会有大量的随机IO)。因为二级索引会存储聚簇索引的键值,因此储聚簇索引键值的大小也会影响二级索引的大小,所以在选择聚簇索引键值时需要注意这点。

另外当SELECT的字段被二级索引覆盖的话,MySQL就不需要再回表查询了,这样执行速度更快。例如:

CREATE TABLE `test_table` (
    `primary_key` int(11) NOT NULL,
    `secondary_key` int(11) DEFAULT NULL,
    `other_key` int(11) DEFAULT NULL,
    PRIMARY KEY (`primary_key`),
    KEY `SECONDARY` (`secondary_key`)
)

用explain命令分析SELECT primary_key 和 secondary_key 的 SQL,extra 字段显示 using index,即 MySQL 执行这条语句时直接从索引取值。

反之,则会看到extra字段显示 using index condition,即需要进行回表取需要的字段值。

小结

索引是个复杂的主题,但通过了解索引底层的运行原理可以帮助我们更精准的使用索引,基本原则如下:

  1. InnoDB读取记录是以Page为单位,加载一个Page只为读取一条记录是很浪费且低效的行为。挑选好Primary Key,可以利用访问局部性(Locality of Reference)提高性能(让相关的记录集中在几个Page里面,这样InnoDB加载一个Page就可以读取到多条记录)。
  2. 使用二级索引读取记录需要进行回表操作,正如同上面第一点提到的,加载一个Page读取一条记录是低效的。因此二级索引覆盖所有需要的字段对性能会有显著提升。

译自 A Brief Introduction to Cluster Index and Secondary Index in InnoDB

posted @ 2020-03-24 15:08  dai.sp  阅读(1497)  评论(1编辑  收藏  举报