MySQL 45讲 | 05 深入浅出索引（下）

MySQL 45讲 | 05 深入浅出索引（下）

刚开始没看太懂，B站找了两个视频看了觉得挺好的，可以结合文章学习。

覆盖索引(联合索引)优化回表查询、最左前缀原则原理
什么是MySQL索引下推？

提出问题

在下面这个表T中，如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜 索操作，会扫描多少行？

mysql> create table T ( 
    ID int primary key, 
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '', 
    index k(k)) engine=InnoDB;

• InnoDB的索引组织结构 :

• 这条SQL查询语句的执行流程：select * from T where k between 3 and 5

  1. 在k索引树上找到k=3的记录，取得 ID = 300；
  2. 再到ID索引树查到ID=300对应的R3；
  3. 在k索引树取下一个值k=5，取得ID=500；
  4. 再回到ID索引树查到ID=500对应的R4；
  5. 在k索引树取下一个值k=6，不满足条件，循环结束。

  在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了k 索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）。

• 由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有
  可能经过索引优化，避免回表过程呢？

覆盖索引

• 在这个查询里面， 索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引（联合索引）。

• 覆盖索引可以减少树的搜索次数，显著提升查询性能，是一个常用的性能优化手段。

问题：在一个市民信息表上，是否有必要将身份 在一个市民信息表上，是否有必要将身份
证号和名字建立联合索引？

CREATE TABLE `tuser` (  
    `id` int(11) NOT NULL, 
    `id_card` varchar(32) DEFAULT NULL,  
    `name` varchar(32) DEFAULT NULL, 
    `age` int(11) DEFAULT NULL,  
    `ismale` tinyint(1) DEFAULT NULL,  
    PRIMARY KEY (`id`), 
    KEY `id_card` (`id_card`),  
    KEY `name_age` (`name`,`age`) ) ENGINE=InnoDB

再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

• 如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

• 当然，索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

最左前缀原则

单独为一个不频繁的请求创建一个（身份证号，地址）的索引又感觉有点浪费。应该怎么做呢？

• B+树这种索引结构，可以利用索引的 “最左前缀 ”，来定位记录。

• 用（name，age）这个联合索引来分析：

  • （name，age）索引示意图：

  

  • 可以看到，索引项是按照索引定义里面出现的字段顺序排序的。

    1. 当你的逻辑需求是查到所有名字是“张三”的人时，可以快速定位到ID4，然后向后遍历得到所有需要的结果。
    2. 如果你要查的是所有名字第一个字是“张”的人，你的SQL语句的条件是"where name like ‘张%’"。这时，你也能够用上这个索引，查找到第一个符合条件的记录是ID3，然后向后遍历， 直到不满足条件为止。

  • 这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

问题：在建立联合索引的时候，如何安排索 在建立联合索引的时候，如何安排索引内的字段顺序。

• 评估标准是，索引的复用能力。

  • 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

  • 查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的。（最左前缀才符合顺序）

  • 第二个原则就是空间。

索引下推

那些不符合最左前缀的部分，会怎么样呢？

• 还是以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一 个字是张，而且年龄是10岁的所有男孩”。

  那么，SQL语句是这么写的：

  mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
  

  • 已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足 条件的记录ID3。当然，这还不错，总比全表扫描要好。

  • 然后是判断其他条件是否满足。

• 在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。

• MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索 引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

  • 无索引下推执行流程：

  

  • 索引下推执行流程 ：

  

• 这两个图里面，每一个虚线箭头表示回表一次。

  图3中，在(name,age)索引里面我特意去掉了age的值，这个过程InnoDB并不会去看age的值， 只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此，需要回表4次。

  图4跟图3的区别是，InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的 记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、ID5这两条记录回表取数据判 断，就只需要回表2次。

小结

• 覆盖索引
• 前缀索引
• 索引下推
• 尽量少地访问资源是数据库设计的重要原则之一。