天鹅绒房间 主页 | Home归档 | Archives分类 | Categories
索引：深入篇
|MySQL索引
Word count: 4.8k|Reading time: 14 min

索引：深入篇

索引类型

索引主要分为哪几类？每种索引的特点是什么？

MySQL InnoDB 索引主要分为聚簇索引和非聚簇索引两大类：

聚簇索引实际指的就是每张表中的主键，非聚簇索引主要包含普通索引，唯一索引，联合索引，前缀索引四种

聚簇索引主要的特点：

非聚簇索引主要特点：

注：聚簇索引和非聚簇索引大致就是这些区别，剩下就是每种索引的详细内容

如何在普通索引和唯一索引中选择？

索引主要的目的就是为了提高效率，那么选择哪种索引的决定性因素就是索引的效率高低；接下来从查询和更新两个角度分析普通索引和唯一索引的效率，从而得出在大多数情况下应该选择哪种索引

查询过程：

  • 首先需要根据索引查找到数据所在的数据页然后将其读取到内存中，如果查找的数据不在内存中的话

  • 然后在内存中通过数据页内部的 Page Directory 查询到第一个符合条件的行记录（这里假设是等值查询）

    • 唯一索引在查询到第一个符合条件的行记录之后就会立刻返回，因为不会再有重复的索引

    • 但是普通索引就会继续查询直到发现有不符合条件的行记录之后才会停止，毕竟会有重复的内容

从整个过程来看，在等值查询的情况下唯一索引可能扫描的行数会比普通索引更少，但是对于内存中的读取操作来说其实差距是微乎其微的，所以唯一索引和普通索引在查询过程中的效率是没有太大区别的

但是存在一个比较特殊的情况，如果等值查询到的第一个符合条件的记录是数据页中的最后一行，那么普通索引想要向后继续扫描就必须将下一个数据页读取到内存中才可以继续扫描，这就会增大查询带来的成本，但是这种情况的概率比较低

更新过程：

总的来说，普通索引和唯一索引在查询过程基本没有性能差距，但是在更新过程中由于查询语句可以使用 change buffer 减少磁盘 IO 的次数，所以效率会优于唯一索引。不过，如果在业务逻辑不能够保证数据的唯一性的时候还是有必要使用唯一索引的，不过在其他情况下还是建议使用普通索引

补充：change buffer

change buffer 将需要更新的内容暂时缓存在内存，然后后台线程选择合适的时机将 change buffer 合并到数据库中

这里的合适的时机主要分为三种情况：

  • 如果更新完数据之后，需要访问更新的数据，那么就需要执行合并的行为，确保读取到的是最新的数据
  • 后台线程在主循环中会定期将 change buffer 中的内容合并到数据库中
  • 数据库正常关闭的时候也会将 change buffer 中的内容合并到数据库中

change buffer 发生更新时，其更新的内容也会记录在 redo log 中，所以不需要担心 change buffer 中的内容因为数据库宕机或者断电丢失。此外，change buffer 会定期刷新到 ibdata1 这个共享表空间中。

索引优化

覆盖索引

简单来说，如果需要查询的字段刚好就在非聚簇索引（辅助索引）的叶子结点中，那么就认为这个索引已经覆盖了我们的查询需求，那么就称这个索引为覆盖索引。覆盖索引主要有两种具体的情况，如果没有使用联合索引，那么只有在查询主键字段的时候才会启用覆盖索引；如果使用联合索引，那么只要查询的字段在联合索引中，那么就可以启用覆盖索引

覆盖索引的好处非常明显，如果能够在非聚簇索引对应的 B+ 树的叶子结点中查询到我们需要的字段，那么就可以避免回表查询从而减少树的搜索次数，显著提升查询性能

-- 假设 name 字段是普通索引
select id from user where name like "张三";
-- 假设 name age gender 是联合索引
select name, age from user where name like "张三";

注：对联合索引使用覆盖索引的前提是联合索引生效，但是如果使用联合索引的时候不遵循最左匹配原则，那么联合索引就不会生效，相应的覆盖索引也就不会生效

联合索引

什么是联合索引？联合索引的最左匹配原则是什么？

简单来说，联合索引就是将多个字段共同组合成一个索引，对应的 B+ 树中的每个结点中的关键字可以存储多个字段而不只是单个字段，并且字段之间也会按照特定的顺序进行排序，特定的顺序其实就是定义联合索引时字段的顺序

此外，联合索引在创建的同时，还会同时从左到右依次组合字段创建相应的索引：比如将 (key1,key2,key3) 作为联合索引创建，那么同时还会创建 (key1), (key1, key2) 这两个索引。如果还有更多的字段，那么依次类推就可以

使用联合索引查询是有限制条件的，并不是随便就可以使用的，只有满足最左匹配原则才可以使用联合索引。最左匹配原则实际上指的就是在使用联合索引的时候，要求 where 条件中使用的索引字段必须是联合索引的最左 N 个字段

比如还是将 (ke1,key2,key3) 作为联合索引，那么使用 (key1), (key1, key2), (key1, key2, key3) 就可以启用联合索引查询，如果使用的是 (key2, key3)，(key3) 那么就是无法使用联合索引的，也就是出现索引失效的情况。这主要是因为联合索引中首先是按照第一个字段排序的，只有在第一个字段相同的情况下才会按照第二个字段排序，那么就有可能出现之后的字段无序，所以需要最左匹配原则。

此外，既然存在最左匹配原则，那么就需要考虑字段在联合索引中的顺序，主要通过两个方面考虑

  • 维护的索引数量：如果某种联合索引中字段的顺序能够减少维护索引的数量，那么就会优先考虑

  • 查询条件的频率：如果联合索引中某个字段的查询频率比较高，那么这个字段的顺序就应该靠前

索引下推优化是什么？

如果 where 条件中使用的索引字段只有部分符合最左匹配原则，而剩下使用的索引字段虽然在联合索引中但是不在最左前缀索引中这种情况：比如 (key1, key3) 这种使用方式。

MySQL 在不同的版本中对于这种情况的处理方式存在差距：

如果是在 MySQL 5.6 之前，那么依然可以使用部分生效的最左前缀索引查询数据，然后将查询到的所有数据依次回表，找到整行数据，然后和 where 条件中剩余的联合索引字段进行比较，但是这样回表的次数会比较多，效率相对较低

在 MySQL 5.6 之后，那么就会在使用部分生效的最左前缀索引查询数据的过程中，就将 where 条件中剩余的联合索引字段挨个比较，从而在回表之前就可以过滤很多不需要回表比较的数据，从而减少回表的次数，提高查询的效率

总的来说，索引下推旨在仅能够利用部分最左前缀索引而不是全部的联合索引时，对不在最左前缀索引中的其他联合索引字段加以利用的手段，其实就是在使用最左前缀索引查询的过程中就对 where 条件中剩下的字段进行比较，从而过滤数据，最终减少回表次数

注：explain 输出的信息中 extra 字段如果出现 using index condition，那么就意味着启动了索引下推优化

前缀索引

简单来说，前缀索引就是将字符串的部分字符取出来作为索引，对应的 B+ 树中每个结点存储的关键字的值也只有字符串的部分字符而不是全部

使用前缀索引的原因主要是可以减少字符串索引占用的空间大小，但是可能造成回表次数增多，以及覆盖索引失效的问题

  • 回表次数增多：主要是因为各个字符串的部分前缀可能是一样的，那么就会导致很多索引的前缀也是一样的，导致索引的区分度就相对较差过滤的数据就少，回表的次数就变多了
  • 覆盖索引失效：主要因为前缀索引中仅包含字符串的部分信息，但是查询的内容包含的是完整的字符串信息，如果直接使用覆盖索引，那么就可能返回多个值，这肯定是不对的，所以只能够全部回表查询

所以使用前缀索引时要注意区分度是否足够优秀，如果区分度太差就应该适当增加前缀索引的长度，从而避免区分度太差

-- 可以用下面这个函数来验证区分度
select 
  count(distinct left(email, 4)),
  count(distinct left(email, 5))
from user;

通常会在身份证号，邮箱等字符串信息中使用前缀索引

如果前缀索引的区分度始终不够好，那么就要考虑和其他的方法结合使用

  • 倒序存储 + 前缀索引
  • 哈希校验码

索引失效

注：索引失效有非常多种情况，只需要记住几种常见的失效情况就可以了，我会尽可能解释每种情况为什么失效

解释索引的命令：explain

// TODO

索引失效的几大情况：

注：索引失效的很大程度上都是因为 MySQL 优化器对于成本的判断和我们预想中的不同造成的

索引失效

where 条件中包含 or 逻辑运算符的时候，可能导致索引失效

如果 or 关联的字段不是索引的话，那么索引基本都会失效。如果走了索引那么就会先遍历一次索引树，然后再走非索引字段进行全表扫描，最后再将两者的结果进行合并，就相当于需要 索引扫描 + 全表扫描 + 合并三次操作。MySQL 优化器此时就会认为还不如直接走全表扫描，然后比较两个字段的值就可以找到符合条件的数据了，显然效率更高。如果 or 关联的字段是索引的话，那么是有可能会走索引的，这个就取决于 MySQL 的优化器对于执行成本的估计了。

-- 这种没有关联的字段不是索引的情况下, 大概率会失效
explain select * from t where id = 1 or name = '张三';
-- 这种关联的字段是索引的情况下, 是有可能成功的
explain select * from t where id = 1 or uid = 2;

where 条件中使用 like 通配符可能导致索引失效

如果查询内容中将 % 通配符放在最前边就可能导致索引失效，最好是将 % 通配符放在最后边

explain select * from t where name like '%三';

where 条件中查询内容的类型和字段的类型不匹配可能导致索引失效

如果定义的字段类型是字符串类型，而传递的查询内容是数字类型，那么就会导致索引失效。主要是因为 MySQL 会隐式地将两者都转换为浮点数再进行比较，所以就不会走字符串索引了

-- 索引直接失效
explain select * from t where id = '1';

where 条件中使用联合索引的时候，不满足最左匹配原则会导致索引失效

where 条件中使用 != / <> / not in 等类似表达，都可能导致索引失效

where 条件中使用前缀索引的时候，也会导致覆盖索引失效

MySQL 错误估计全表扫描的成本，认为比不使用索引会更快

什么情况下可能出现选错索引的情况呢？

注：其实索引选错的情况没有太具体的例子，MySQL 实战 45 讲 中的例子感觉也不是很典型，所以这里主要讲 MySQL 优化器如何选择索引的，然后根据相关的命令查看信息后判断是否选错索引

SQL 的执行流程中会经过优化器这个部分，那么优化器主要就是根据扫描行数最少的原则来选择最优的执行方案，选择的过程就是通过调整联表的顺序以及选择的索引。

优化器肯定是无法在 SQL 执行之前就直接清楚地知道每种方案下扫描的行数是多少，所以优化器会采用基数（cardinality）来估计每种执行方案大致的扫描行数，也就是每个索引对应的扫描行数。而基础则是采用采样统计的方式来计算的，采样统计就是选取 N 个数据页然后扫描每个数据页中该索引下的不同的值的数量，然后取平均值，最后将平均值 x 索引页的数量，就可以得到基数的大小。只要基数统计出现错误，那么很有可能导致优化器之后的代价计算错误，最终导致选错索引

例子：

现在存在两个事务，第一个事务开启之后不做任何操作，第二个事务开启之后先将表中的数据全部删除，然后再向表中插入 10w 行数据，之后再使用索引查询 10000~20000 范围内的数据，此时就会出现索引选择错误的情况，优化器会选择全表扫描而不走索引

-- 事务-1
start transaction with consistent snapshot;
-- 什么也不做

-- 事务-2
start transaction;
-- 删除数据
delete from t;
-- 插入数据: 调用的是存储过程
call idata();
-- 查询数据
explain select * from t where a between 10000 and 20000;
commit;

原因分析：主要是因为事务-1在启动之后立刻记录了当前数据库的快照，那么此时的快照记录的就是旧版本的数据，而之后事务-2在启动之后删除了表中的数据并插入了新的数据，对应快照记录的是新版本的数据，此时就相当于表中的每行数据都有两个版本，从而导致优化器在采用基数统计的时候出现错误，统计多了。

那么只要出现索引选择错误的情况，那肯定是优化器认为选择这个索引或者说不选择索引带来的开销会低于选择我们想要使用的索引带来的开销，但是这个有可能是优化器估计错误了，如果想要修正，那么可以采用其他的办法。

常见的几种办法：

  • 直接使用 force index( 索引名字 ) , 那么就可以强制优化器走某个索引
  • 扫描的行数出现错误可以使用 analyze table t 这个命令来要求重新统计
  • 使用 explain 命令查看执行计划，然后具体原因具体分析

参考内容：

https://juejin.cn/post/6844903859673006094#heading-1

https://juejin.cn/post/6844903845554814983

https://juejin.cn/post/6844904073955639304#heading-0

https://juejin.cn/post/6844903885501530125

https://juejin.cn/post/6844904015872917517#heading-1

https://www.cnblogs.com/michael9/p/12929775.html

《MySQL 实战 45 讲》

Author: Fuyusakaiori
Link: http://example.com/2022/01/25/database/mysql/index/索引：深入篇/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
索引日志change buffer