MySQL——MRR、索引跳跃式扫描、索引覆盖、索引下推详解

一、索引类型

索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引。
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引。
• 从索引键值类型划分：主键索引、辅助索引（二级索引）。
• 从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）。

1.1 普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。 创建普通索引的方法如下：

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

1.2 唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。 创建唯一索引的方法如下：

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

1.3 主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。 创建主键索引的方法如下：

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

1.4 复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。 索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。 创建组合索引的方法如下：

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

• 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
• 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

1.5 全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。 创建全文索引的方法如下：

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from user where match(name) against('aaa');

全文索引使用注意事项：

• 全文索引必须在字符串、文本字段上建立。
• 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
• 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
• 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from user where match(name) against('a*' in boolean mode);

1.6 索引原理

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

• 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
• 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash和B+Tree。

二、使用索引的正确姿势

其实到这里，对于如何使用索引才是正确的呢？总结如下：

• ①查询SQL中尽量不要使用OR关键字，可以使用多SQL或子查询代替。
• ②模糊查询尽量不要以%开头，如果实在要实现这个功能可以建立全文索引。
• ③编写SQL时一定要注意字段的数据类型，否则MySQL的隐式转换会导致索引失效。
• ④一定不要在编写SQL时让索引字段执行计算工作，尽量将计算工作放在客户端中完成。
• ⑤对于索引字段尽量不要使用计算类函数，一定要使用时请记得将函数计算放在=后面。
• ⑥多条件的查询SQL一定要使用联合索引中的第一个字段，否则会打破最左匹配原则。
• ⑦对于需要对比多个字段的查询业务时，可以拆分为连表查询，使用临时表代替。
• ⑧在SQL中不要使用反范围性的查询条件，大部分反范围性、不等性查询都会让索引失效。
• ⑨.......

实际上无非就是根据前面给出的索引失效情况，尽量让自己编写的SQL不会导致索引失效即可，写出来的SQL能走索引查询，那就能在很大程度上提升数据检索的效率。

 

接下来再重点讲几个较重要的内容，既索引覆盖、索引下推、Multi-Range Read机制、索引跳跃式扫描机制。

三、索引覆盖

由于表中只能存在一个聚簇索引，一般都为主键索引，而建立的其他索引都为辅助索引，包括联合索引也例外，最终索引节点上存储的都是指向主键索引的值，拿前面的用户表为例：

SELECT * FROM `zz_users` WHERE `user_name`="竹子" AND `user_sex`="男";

虽然这条SQL会走联合索引查询，但是基于联合索引查询出来的值仅是一个指向主键索引的ID，然后会拿着这个ID再去主键索引中查一遍，这个过程之前聊过，被称为回表过程。

那么回表问题无法解决吗？必须得经过两次查询才能得到数据吗？答案并非如此。

比如假设此时只需要user_name、user_sex、password这三个字段的信息，此时SQL语句可以更改为如下情况：

SELECT 
    `user_name`,`user_sex`,`password`
FROM 
    `zz_users` 
WHERE 
    `user_name` = "竹子" AND `user_sex` = "男";

此时将SQL更改为查询所需的列后，就不会发生回表现象，Why？再这里很多小伙伴可能会疑惑，这是什么道理啊？因为此时所需的user_name、user_sex、password三个字段数据，在联合索引中完全包含，因此可以直接通过联合索引获取到数据。

但如果查询时用*，因为联合索引中不具备完整的一行数据，只能再次转向聚簇索引中获取完整的行数据，因此到这里大家应该也明白了为什么查询数据时，不能用*的原因，这是因为会导致索引覆盖失效，造成回表问题。

当然，再来提一点比较有意思的事情，先看SQL：

EXPLAIN SELECT 
    `user_name`,`user_sex`
FROM 
    `zz_users`
WHERE 
    `password` = "1234" AND `user_sex` = "男";

比如上述这条SQL，显然是不符合联合索引的最左前缀匹配原则的，但来看看执行结果：

这个结果是不是很令你惊讶，通过EXPLAIN分析的结果显示，这条SQL竟然使用了索引，这是什么原因呢？也是因为索引覆盖。

一句话概述：就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这种情况称之为索引覆盖。

四、索引下推

索引下推是MySQL5.6版本以后引入的一种优化机制，还是以之前的用户表为例，先来看一条SQL语句：

INSERT INTO `zz_users` VALUES(5,"竹竹","女","8888","2022-09-20 22:17:21");

SELECT * FROM `zz_users` WHERE `user_name` LIKE "竹%" AND `user_sex`="男";

首先为了更加直观的讲清楚索引下推，因此先再向用户表中增加一条数据。然后再来看看后面的查询SQL，这条SQL会使用联合索引吗？答案是会的，但只能部分使用，因为联合索引的每个节点信息大致如下：

{
    ["熊猫","女","6666"] : 1,
    ["竹子","男","1234"] : 2,
    ["子竹","男","4321"] : 3,
    ["1111","男","4321"] : 4,
    ["竹竹","女","8888"] : 5
}

由于前面使用的是模糊查询，但%在结尾，因此可以使用竹这个字作为条件在联合索引中查询，整个查询过程如下：

• ①利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
• ②返回索引节点存储的值「2、5」给Server层，然后去逐一做回表扫描。
• ③在Server层中根据user_sex="男"这个条件逐条判断，最终筛选到「竹子」这条数据。

有人或许会疑惑，为什么user_sex="男"这个条件不在联合索引中处理呢？因为前面是模糊查询，所以拼接起来是这样的：竹x男，由于这个x是未知的，因此无法根据最左前缀原则去匹配数据，最终这里只能使用联合索引中user_name字段的一部分，后续的user_sex="男"还需要回到Server层处理。

那什么又叫做索引下推呢？也就是将Server层筛选数据的工作，下推到引擎层处理。

以前面的案例来讲解，MySQL5.6加入索引下推机制后，其执行过程是什么样子的呢？

• ①利用联合索引中的user_name字段找出「竹子、竹竹」两个索引节点。
• ②根据user_sex="男"这个条件在索引节点中逐个判断，从而得到「竹子」这个节点。
• ③最终将「竹子」这个节点对应的「2」返回给Server层，然后聚簇索引中回表拿数据。

相较于没有索引下推之前，原本需要做「2、5」两次回表查询，但在拥有索引下推之后，仅需做「2」一次回表查询。

索引下推在MySQL5.6版本之后是默认开启的，可以通过命令set optimizer_switch='index_condition_pushdown=off|on';命令来手动管理。

五、MRR(Multi-Range Read)机制

Multi-Range Read简称为MRR机制，这也是和索引下推一同在MySQL5.6版本中引入的性能优化措施，那什么叫做MRR优化呢？

一般来说，在实际业务中我们应当尽量通过索引覆盖的特性，减少回表操作以降低IO次数，但在很多时候往往又不得不做回表才能查询到数据，但回表显然会导致产生大量磁盘IO，同时更严重的一点是：还会产生大量的离散IO，下面举个例子来理解。

SELECT * FROM `zz_student_score` WHERE `score` BETWEEN 0 AND 59;

上述这条SQL所做的工作很简单，就是在学生成绩表中查询所有成绩未及格的学生信息，假设成绩字段上存在一个普通索引，那思考一下，这条SQL的执行流程是什么样的呢？

• ①先在成绩字段的索引上找到0分的节点，然后拿着ID去回表得到成绩零分的学生信息。
• ②再次回到成绩索引，继续找到所有1分的节点，继续回表得到1分的学生信息。
• ③再次回到成绩索引，继续找到所有2分的节点…
• ④周而复始，不断重复这个过程，直到将0~59分的所有学生信息全部拿到为止。

那此时假设此时成绩0~5分的表数据，位于磁盘空间的page_01页上，而成绩为5~10分的数据，位于磁盘空间的page_02页上，成绩为10~15分的数据，又位于磁盘空间的page_01页上。此时回表查询时就会导致在page_01、page_02两页空间上来回切换，但0~5、10~15分的数据完全可以合并，然后读一次page_01就可以了，既能减少IO次数，同时还避免了离散IO。

而MRR机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散IO，并且将随机IO转换为顺序IO，从而提高查询效率。

那MRR机制具体是怎么做的呢？MRR机制中，对于辅助索引中查询出的ID，会将其放到缓冲区的read_rnd_buffer中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到read_rnd_buffer_size大小时，此时MySQL会对缓冲区中的数据排序，从而得到一个有序的ID集合：rest_sort，最终再根据顺序IO去聚簇/主键索引中回表查询数据。

SET @@optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

可以通过上述这条命令开启或关闭MRR机制，MySQL5.6及以后的版本是默认开启的。

六、Index Skip Scan索引跳跃式扫描

在讲联合索引时，咱们提到过最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

但跳跃扫描究竟是怎么实现的呢？上个栗子快速理解一下。

比如此时通过(A、B、C)三个列建立了一个联合索引，此时有如下一条SQL：

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`;

按理来说，这条SQL既不符合最左前缀原则，也不具备使用索引覆盖的条件，因此绝对是不会走联合索引查询的，但思考一个问题，这条SQL中都已经使用了联合索引中的两个字段，结果还不能使用索引，这似乎有点亏啊对不？因此MySQL8.x推出了跳跃扫描机制，但跳跃扫描并不是真正的“跳过了”第一个字段，而是优化器为你重构了SQL，比如上述这条SQL则会重构成如下情况：

SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx`
UNION ALL
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "yyy"
......
SELECT * FROM `tb_xx` WHERE B = `xxx` AND C = `xxx` AND A = "zzz";

其实也就是MySQL优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍，一句话来概述就是：虽然你没用第一个字段，但我给你加上去，今天这个联合索引你就得用，不用也得给我用。

 

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发…，总之有很多限制条件，具体的可以参考《MySQL官网8.0-跳跃扫描》。

限制条件

• 查询必须只能 依赖 一张表，不能多表JOIN。
• 查询中不能使用 GROUP BY 或 DISTINCT 语句。
• 查询的字段必须是索引中的列。
• 组合索引形式：([A_1, …, A_k,] B_1, …, B_m, C [, D_1, …, D_n])，A，D 可以为空，但是B ,C 不能为空。

其实这个跳跃性扫描机制，只有在唯一性较差的情况下，才能发挥出不错的效果，如果你联合索引的第一个字段，是一个值具备唯一性的字段，那去重一次再拼接，几乎就等价于走一次全表。

最后，可以通过通过set @@optimizer_switch = 'skip_scan=off|on';命令来选择开启或关闭跳跃式扫描机制。当然，该参数仅限MySQL8.0以上的版本，如果在此之下的版本暂时就不用考虑了。

参考： https://baijiahao.baidu.com/s?id=1710034439941187868

https://blog.csdn.net/weixin_63566550/article/details/129484014