InnoDB: Topic: Index

 27th January 2021 at 8:20am

InnoDB 的索引实现。

索引被用来加速数据查询。索引所使用的 B+ 树，在 Database: Index 中提及。

主键索引和非主键索引

区别：

  • 主键索引中，索引的值和关联的行存储在一起，也被称为 聚簇索引（clustered index）
  • 非主键索引的叶子节点存的是主键的值，也被称为 二级索引（secondary index）。查询时需要多扫描一颗索引树

《高性能 MySQL》中的图片：

掘金的这篇 文章 总结得非常好：

展开详情

聚簇索引与非聚簇索引的特点：

  1. 聚簇索引的主键索引，数据行是和索引在一起的
  2. 非聚簇索引的主键索引，数据行和索引分开存储，索引中保存数据行的地址
  3. 聚簇索引的二级索引中，保存的是主键索引的值。之所以保存主键索引的值，而不是保存数据行的地址，是因为聚簇索引会发生页分裂，页分裂后数据存储的地址就会发生变化。当页地址发生变化后，只需要维护主键索引的数据即可，不需要维护二级索引，减少了索引的维护工作
  4. 非聚簇索引的主键索引和二级索引没有什么实质性的区别

聚簇索引有些优点：

  1. 可以把相关的数据保存在一起。比如一个用户的流水记录，按照用户的 ID 建立聚簇索引，这样用户所有的数据都会聚集在一起，因此查询时只需要读少数几个磁盘的数据块就可以获取一个用户所有的数据
  2. 数据访问更快。聚簇索引的数据行与索引在一起，因此在聚簇索引上搜索时，完成索引的搜索就找到了数据，不需要再进行一次磁盘 I/O
  3. 使用覆盖索引时可以直接使用页子节点中的主键值，因为二级索引的叶子节点上保存是主键值。

聚簇索引有些缺点：

  1. 插入的速度严重依赖于插入的顺序。如果按照主键的顺序进行插入，插入可以很快。如果不按照主键的顺序进行插入，则可能产生页分裂。页分裂不仅影响插入的速度，而且原来一个页，现在分成两个页来存储，而两个页又没有都存满，占用了更多的磁盘空间。所以这就是为什么 DBA 总是建议在使用 InnoDB 引擎时，使用自增 ID 做主键
  2. 更新聚簇索引的代价很高，因为每个被更新的行会被移动到新的位置
  3. 二级索引需要两次查找，因为二级索引中存放的是主键的值，需要再进行一次回表操作
  4. 二级索引中存储主键的值，可能会导致二级索引占用更大的空间

覆盖索引

当查询时仅需要索引中的数据（比如 ID），MySQL 查完索引就可以给返回，不需要查数据行。这意味着 为高频查询建立联合索引是值得的（比如查 ID 和名字）（如何衡量？）。

最左前缀原则

联合索引 (a, b) 中，在 B+ 树中索引项是按定义的字段顺序排序的。比如下面，数据先按姓名排序，再按年龄排序：

这意味着，只查姓名时也可以利用这个联合索引。但是查年龄时不行。即是说，查 (a) 或者 (a, b) 时也可以利用到联合索引 (a, b, c) 。

如果你有多个查询场景，尽量使它们可以用同一个联合索引。

模糊查询和范围查询都会导致联合索引上该查询列之后的列失效。比如：

SELECT * FROM t WHERE a LIKE 'test%' AND b='luck'

因为 a 使用了模糊查询，因此即使有联合索引 (a, b)，也只能利用到 a 无法用 b。

前缀索引

对字符串列做索引时，有时候完整的索引太占用空间。可以选择该列的前几个字符做索引。比如 key(email(4))。这样减少了索引的空间，但是增加了回表（即回到主键索引 B+ 树）的次数。

前缀索引会引起覆盖索引失效，因为前缀索引的字符串不完整。

参考