聊聊：什么是索引下推？

索引下推 也被称为 索引条件下推 （Index Condition Pushdown）ICP

MySQL新添加的特性，用于优化数据查询的。

这里尼恩给大家做一下系统化、体系化的线程池梳理，使得大家可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”。

也一并把这个题目以及参考答案，收入咱们的 《尼恩Java面试宝典 PDF》，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。

5.6 之前通 过非主键索引查询时，存储引擎通过索引查询数据，然后将结果返回给MySQL server层，在server层判断是否符合条件，

在以后的版本可以使用索引下推，当存在索引列作为判断条件时，Mysql server 将这一部分判断条件传递给存储引擎，

然后存储引擎会筛选出符合传递传递条件的索引项，即在存储引擎层根据索引条件过滤掉不符合条件的索引项，然后回表查询得到结果，将结果再返回给Mysql server，

有了索引下推的优化，在满足一定条件下，存储 引擎层会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。

注：本文以 PDF 持续更新，最新尼恩 架构笔记、面试题 的PDF文件，请从这里获取：码云

假如有一张表user, 表有四个字段 id,name,level,tool

idnameleveltool
1大王1电话
2小王2手机
3小李3BB机
4大李4马儿

建立联合索引（name,level）

匹配姓名第一个字为“大”，并且level为1的用户，sql语句为

select * from user where name like "大%" and level = 1；

在5.6之前，执行流程是如下图

根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘大的记录，接下来是怎么处理的呢？

当然是ID 1 、ID4开始，逐个回表，到主键索引上找出相应的记录，再比对level这个字段的值是否符合。

图 1 中，在 (name,level) 索引里，只是按顺序把“name 第一个字是’大’”的记录一条条取出来回表。

因此，需要回表 2次。

但是！MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
下面图1、图2分别展示这两种情况。

5.6及之后，执行流程图如下

图 2 跟图 1 的区别是，InnoDB 在 (name,level) 索引内部就判断了 level是否等于1，对于不等于1 的记录，直接判断并跳过。

在我们的这个例子中，只需要对ID 1 、ID4 这两条记录回表取数据判断，就只需要回表 1 次。

使用索引下推后由两次回表变为一次，提高了查询效率。

总结

如果没有索引下推优化（或称ICP优化），

当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；

在支持ICP优化后，MySQL会在取出索引的同时，判断是否可以进行where条件过滤再进行索引查询，

也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

聊聊：MySQL索引使用的注意事项？

MySQL 索引通常是被用于提高 WHERE 条件的数据行匹配时的搜索速度，

在索引的使用过程中，存在一些使用细节和注意事项。

1.不要在列上使用函数和进行运算

不要在列上使用函数，这将导致索引失效而进行全表扫描。

select * from news where year(publish_time) < 2017

为了使用索引，防止执行全表扫描，可以进行改造。

select * from news where publish_time < '2017-01-01'

还有一个建议，不要在列上进行运算，这也将导致索引失效而进行全表扫描。

select * from news where id / 100 = 1

为了使用索引，防止执行全表扫描，可以进行改造。

select * from news where id = 1 * 100
2.尽量避免使用 != 或 not in或 <> 等否定操作符

应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，

因为这几个操作符都会导致索引失效而进行全表扫描。

尽量避免使用 or 来连接条件

应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描。

select * from news where id = 1 or id = 2
3.多个单列索引并不是最佳选择、当查询条件为多个的时候，可以采用复合索引

MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，

因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。

假设，有两个单列索引，分别为 news_year_idx(news_year) 和 news_month_idx(news_month)。

现在，有一个场景需要针对资讯的年份和月份进行查询，那么，SQL 语句可以写成：

select * from news where news_year = 2017 and news_month = 1

事实上，MySQL 只能使用一个单列索引。

为了提高性能，可以使用复合索引 news_year_month_idx(news_year, news_month) 保证 news_year 和 news_month 两个列都被索引覆盖。

4、复合索引的最左前缀原则

复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。

因此，在复合索引中索引列的顺序至关重要。

如果不是按照索引的最左列开始查找，则无法使用索引。

假设，有一个场景只需要针对资讯的月份进行查询，那么，SQL 语句可以写成：

select * from news where news_month = 1

此时，无法使用 news_year_month_idx(news_year, news_month) 索引，因为遵守“最左前缀”原则，在查询条件中没有使用复合索引的第一个字段，索引是不会被使用的。

覆盖索引的好处

如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。

因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

5、范围查询对多列查询的影响

查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。

举个例子，假设有一个场景需要查询本周发布的资讯文章，其中的条件是必须是启用状态，且发布时间在这周内。那么，SQL 语句可以写成：

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

这种情况下，因为范围查询对多列查询的影响，将导致 news_publish_idx(publish_time, enable) 索引中 publish_time 右边所有列都无法使用索引优化查找。

换句话说，news_publish_idx(publish_time, enable) 索引等价于 news_publish_idx(publish_time) 。

对于这种情况，我的建议：对于范围查询，务必要注意它带来的副作用，并且尽量少用范围查询，可以通过曲线救国的方式满足业务场景。

例如，上面案例的需求是查询本周发布的资讯文章，因此可以创建一个news_weekth 字段用来存储资讯文章的周信息，使得范围查询变成普通的查询，SQL 可以改写成：

select * from news where     news_weekth = 1 and enable = 1

然而，并不是所有的范围查询都可以进行改造，对于必须使用范围查询但无法改造的情况，

建议：不必试图用 SQL 来解决所有问题，可以使用其他数据存储技术控制时间轴，

例如 Redis 的 SortedSet 有序集合保存时间，或者通过缓存方式缓存查询结果从而提高性能。

6、索引不会包含有NULL值的列

只要列中包含有 NULL 值，都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。

因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。

8、隐式转换的影响

当查询条件左右两侧类型不匹配的时候会发生隐式转换，

隐式转换带来的影响就是可能导致索引失效而进行全表扫描。

下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。

select * from news where date_str = 201701

因此，要谨记隐式转换的危害，时刻注意通过同类型进行比较。

9、like 语句的索引失效问题

like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，

这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情。

所以，根据业务需求，考虑使用 ElasticSearch 或 Solr 是个不错的方案。

聊聊：如何创建有效的索引？

1. 如果需要索引很长的字符串，此时需要考虑前缀索引

前缀索引即选择所需字符串的一部分前缀作为索引，这时候，需要引入一个概念叫做索引选择性，

索引选择性是指不重复的索引值与数据表的记录总数的比值，可以看出索引选择性越高则查询效率越高，

当索引选择性为1时，效率是最高的，

但是在这种场景下，很明显索引选择性为1的话我们会付出比较高的代价，索引会很大，

这时候我们就需要选择字符串的一部分前缀作为索引，通常情况下一列的前缀作为索引选择性也是很高的

如何选择前缀：计算该列完整列的选择性，使得前缀选择性接近于完整列的选择性

2、使用多列索引

尽量不要为多列上创建单列索引，因为这样的情况下最多只能使用一个索引，

这样的话，不如去创建一个全覆盖索引，在多列上创建单列索引大部分情况下并不能提高 MySQL 的查询性能，

MySQL 5.0 中引入了合并索引，在一定程度上可以表内多个单列索引来定位指定的结果，

但是 5.0 以前的版本，如果 where 中的多个条件是基于多个单列索引，那么 MySQL 是无法使用这些索引的，这种情况下，还不如使用 union。

3、选择合适的索引列顺序

经验是将选择性最高的列放到索引最前列，可以在查询的时候过滤出更少的结果集。

但这样并不总是最好的，如果考虑到 group by 或者 order by 等情况，再比如考虑到一些特别场景下的 guest 账号等数据情况，上面的经验法则可能就不是最适用的

4、覆盖索引

所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况下就不需要再进行回表查询了

覆盖索引对于 MyISAM 和 InnoDB 都非常有效，可以减少系统调用和数据拷贝等时间.

Tips：减少 select * 操作

5、使用索引扫描来做排序

MySQL 生成有序的结果有两种方法：通过排序操作，或者按照索引顺序扫描；

使用排序操作需要占用大量的 CPU 和内存资源，而使用 index性能是很好的，所以，当我们查询有序结果时，尽量使用索引顺序扫描来生成有序结果集。

怎样保证使用索引顺序扫描？

  • 索引 列 顺序和 ORDER BY 顺序一致
  • 所有列的排序方向一致
  • 如果关联多表，那么只有当 ORDER BY 子句引用的字段全部为第一张表时，才能使用索引做排序，限制依然是需要满足索引的最左前缀要求
6、压缩索引

MyISAM 中使用了前缀压缩技术，会减少索引的大小，可以在内存中存储更多的索引，这部分优化默认也是只针对字符串的，但是可以自定义对整数做压缩。

这个优化在一定情况下性能比较好，但是对于某些情况可能会导致更慢，因为前缀压缩决定了每个关键字都必须依赖于前面的值，所以无法使用二分查找等，只能顺序扫描，所以如果查找的是逆序那么性能可能不佳。

7、减少重复、冗余以及未使用的索引

MySQL 的唯一限制和主键限制都是通过索引实现的，所以不需要在同一列上增加主键、唯一限制再创建索引，这样是重复索引

再举个例子，如果已经创建了索引(A，B)，那么再创建索引(A)的话，就属于重复索引，因为 MySQL 索引是最左前缀，所以索引(A，B)本身就可以使用索引(A)，但是创建索引(B)的话不属于重复索引

尽量减少新增索引，而应该扩展已有的索引，因为新增索引可能会导致 INSERT、UPDATE、DELETE 等操作更慢

可以考虑删除没有使用到的索引，定位未使用的索引，有两个办法，在 Percona Server 或者 MariaDB 中打开 userstates 服务器变量，然后等服务器运行一段时间后，通过查询 INFORMATION_SCHEMA.INDEX_STATISTICS 就可以查询到每个索引的使用频率

8、索引和锁

InnoDB 支持行锁和表锁，默认使用行锁，而 MyISAM 使用的是表锁，

所以使用索引可以让查询锁定更少的行，这样也会提升查询的性能，

如果查询中锁定了1000行，但实际只是用了100行，

那么在 5.1 之前都需要提交事务之后才能释放这些锁，5.1 之后可以在服务器端过滤掉行之后就释放锁，不过依然会导致一些锁冲突

9、减少索引和数据碎片

首先我们需要了解一下为什么会产生碎片，比如 InnoDB 删除数据时，这一段空间就会被留空，

如果一段时间内大量删除数据，就会导致留空的空间比实际的存储空间还要大，这时候如果进行新的插入操作时，MySQL 会尝试重新使用这部分空间，但是依然无法彻底占用，这样就会产生碎片

产生碎片带来的后果当然是，降低查询性能，因为这种情况会导致随机磁盘访问

可以通过 OPTIMIZE TABLE 或者重新导入数据表来整理数据

聊聊：使用索引优化查询问题？

1、创建单列索引还是多列索引？

如果查询语句中的where、order by、group 涉及多个字段，一般需要创建多列索引，

比如：

select * from user where nick_name = 'ligoudan' and job = 'dog';

2、多列索引的顺序如何选择？

一般情况下，把选择性高的字段放在前面，

比如：查询sql：

select * from user where age = '20' and name = 'zh' order by nick_name;

这时候如果建索引的话，首字段应该是age，因为age定位到的数据更少，选择性更高。

但是务必注意一点，满足了某个查询场景就可能导致另外一个查询场景更慢。

3、避免使用范围查询

很多情况下，范围查询都可能导致无法使用索引。

4、尽量避免查询不需要的数据

explain select * from user where job like 'ligoudan%';
explain select job from user where job like 'ligoudan%';

同样的查询，不同的返回值，第二个就可以使用覆盖索引，第一个只能全表遍历了。

5、查询的数据类型要正确

explain select * from user where create_date >= now();
explain select * from user where create_date >= '2020-05-01 00:00:00';

第一条语句就可以使用create_date的索引，第二个就不可以。

聊聊：什么是MySQL 的 MRR 优化？

什么是MRR优化？

MRR，全称「Multi-Range Read Optimization」。

在不使用 MRR 时，优化器需要根据二级索引返回的记录来进行“回表”，这个过程一般会有较多的随机 IO,

使用 MRR 时，SQL 语句的执行过程是这样的：

1、先把通过二级索引取出的值缓存在缓冲区中，

这个缓冲区叫做 read_rnd_buffer ，简称 rowid buffer。

2、再把这部分缓冲区中的数据按照ID进行排序。

如果二级索引扫描到索引文件的末尾或者缓冲区已满，则使用快速排序对缓冲区中的内容按照主键进行排序；

3、然后再依次根据ID去聚集索引中获取整个数据行。

线程调用 MRR 接口取 rowId，然后根据rowId 取行数据；

当根据缓冲区中的 rowId 取完数据，则继续调用过程 2) 3)，直至扫描结束；

MRR 的本质：

是在回表的过程中， 把分散的无序回表， 变成排序后有序的回表， 从而实现 随机磁盘读 尽可能变成 顺序读。

通过上述过程，优化器将二级索引随机的 IO 进行排序，转化为主键的有序排列，从而实现了随机 IO 到顺序 IO 的转化，提升性能。

可以看出，只需要通过一次排序，就使得随机IO，变为顺序IO，使得数据访问更加高效。

read_rnd_buffer_size控制了数据能放入缓冲区的大小，如果一次性不够放就会分多次完成。

MRR优化的本质

简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

MRR 的本质：

是在回表的过程中， 把分散的无序回表， 变成排序后有序的回表， 从而实现 随机磁盘读 尽可能变成 顺序读。

接下来的问题是：

  • 为什么要把随机读转化为顺序读？
  • 怎么转化的？
  • 为什么顺序读就能提升读取性能？

首先，从一个没有做MRR优化的普通 回表查询说起。

从一个没有做MRR优化的普通 回表查询说起

执行一个范围查询：

mysql > explain select * from stu where age between 10 and 20;
+----+-------------+-------+-------+------+---------+------+------+-----------------------+
| id | select_type | table | type  | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+----------------+------+------+-----------------------+
|  1 | SIMPLE      |  stu  | range | age  | 5       | NULL |  960 | Using index condition |
+----+-------------+-------+-------+----------------+------+------+-----------------------+

当这个 sql 被执行时，MySQL 会按照下图的方式，去磁盘读取数据（假设数据不在数据缓冲池里）：

图中红色线就是整个的查询过程，蓝色线则是磁盘的运动路线。

为了对画图进行简化，这张图是按照 Myisam 的索引结构画的，

Innodb 涉及到二级索引、聚簇索引（cluster index）的二级结构，因为，Innodb 涉及到回表，这里的Myisam ，没有涉及到回表。所以，画起来，会更复杂，这里，就不去耗费大量时间了。

不过上面的 Myisam 磁盘运动路线原理， 对于 Innodb 也同样适用。

对于 Myisam，左边就是字段 age 的二级索引，右边是存储完整行数据的地方。

查找的过程是：

先到左边的二级索引找，找到第一条符合条件的记录（实际上每个节点是一个页，一个页可以有很多条记录，这里我们假设每个页只有一条）

接着到右边去读取这条数据的完整记录。

读取完后，回到左边，继续找下一条符合条件的记录，

找到后，再到右边读取，

就是这么一条一条的记录，去读取的。

这时，问题来了：

在读取的过程中，会发现上一条数据的位置，和下一条数据的位置，在物理存储位置上，离的贼远！

每次读取数据，磁盘和磁头都得跑好远一段路。

咋办呢，没办法，

只能让磁盘和磁头一起做机械运动，去给你疯狂跑腿，来回跑腿，去读取下一条数据。

磁盘的简化结构可以看成这样：

可以想象一下，为了执行你这条 sql 语句，磁盘要不停的旋转，磁头要不停的移动，

这些机械运动，都是很费时的。

10,000 RPM（Revolutions Per Minute，即转每分） 的机械硬盘，每秒大概可以执行 167 次磁盘读取，

所以在极端情况下，MySQL 每秒只能给你返回 167 条数据，这还不算上 CPU 排队时间。

对于 Innodb，也是一样的。Innodb 是聚簇索引（cluster index）：

  • 如果没有涉及到回表，只需要把右边也换成一颗叶子节点带有完整数据的 B+ tree 就可以了。
  • 如果有涉及到回表，只需要把右边换成一颗叶子节点聚簇索引（cluster index）B+ tree，和一颗叶子节点是 主键值的 二级索引 B+ tree，就可以了。

磁盘IOPS的计算规则

主要影响的三个参数，分别是平均寻址时间、盘片旋转速度以及最大传送速度：

第一个寻址时间，

考虑到被读写的数据可能在磁盘的任意一个磁道，既有可能在磁盘的最内圈(寻址时间最短)，也可能在磁盘的最外圈(寻址时间最长)，

所以在计算中我们只考虑平均寻址时间，也就是磁盘参数中标明的那个平均寻址时间，这里就采用当前最多的10krmp硬盘的5ms。

寻道时间Tseek是指将读写磁头移动至正确的磁道上所需要的时间。

寻道时间越短，I/O操作越快，目前磁盘的平均寻道时间一般在3-15ms。

第二个旋转延时，

和寻址一样，当磁头定位到磁道之后有可能正好在要读写扇区之上，这时候是不需要额外额延时就可以立刻读写到数据，但是最坏的情况确实要磁盘旋转整整一圈之后磁头才能读取到数据，

所以这里我们也考虑的是平均旋转延时，对于10krpm的磁盘就是(60s/10k)*(1/2) = 2ms。

第三个传送时间，

磁盘参数提供我们的最大的传输速度，当然要达到这种速度是很有难度的，

但是这个速度却是磁盘纯读写磁盘的速度，因此只要给定了单次 IO的大小，我们就知道磁盘需要花费多少时间在数据传送上，这个时间就是IO Chunk Size / Max Transfer Rate。（数据传输率，单位是Mb/s，兆每秒）。

数据传输时间Ttransfer是指完成传输所请求的数据所需要的时间，它取决于数据传输率，其值等于数据大小除以数据传输率。

目前IDE/ATA能达到133MB/s，SATA II可达到300MB/s的接口数据传输率，数据传输时间通常远小于前两部分时间。

因此，理论上可以计算出磁盘的最大IOPS，即IOPS = 1000 ms/ (Tseek + Troatation)，忽略数据传输时间。

假设磁盘平均物理寻道时间为3ms, 磁盘转速为7200,10K,15K rpm，

则磁盘IOPS理论最大值分别为，

IOPS = 1000 / (3 + 60000/7200/2) = 140

IOPS = 1000 / (3 + 60000/10000/2) = 167

IOPS = 1000 / (3 + 60000/15000/2) = 200

到这里你知道了磁盘随机访问是多么奢侈的事了，所以，很明显，要把随机访问转化成顺序访问：

顺序读：一场狂风暴雨般的革命

开启了 MRR很明显，要把随机访问转化成顺序访问。

设置开启MRR, 重新执行 sql 语句，发现 Extra 里多了一个「Using MRR」。

mysql > set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.06 sec)

mysql > explain select * from stu where age between 10 and 20;
+----+-------------+-------+-------+------+---------+------+------+----------------+
| id | select_type | table | type  | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+-------+------+---------+------+------+----------------+
|  1 | SIMPLE      | tbl   | range | age  |    5    | NULL |  960 | ...; Using MRR |
+----+-------------+-------+-------+------+---------+------+------+----------------+

这下 MySQL 的查询过程会变成这样：

对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。

对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。

顺序读带来了几个好处：

1、磁盘和磁头不再需要来回做机械运动；

2、可以充分利用磁盘预读

比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，

这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。

这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。

3、在一次查询中，每一页的数据只会从磁盘读取一次

MySQL 从磁盘读取页的数据后，会把数据放到数据缓冲池，下次如果还用到这个页，就不需要去磁盘读取，直接从内存读。

但是如果不排序，可能你在读取了第 1 页的数据后，会去读取第2、3、4页数据，

接着你又要去读取第 1 页的数据，这时你发现第 1 页的数据，已经从缓存中被剔除了，于是又得再去磁盘读取第 1 页的数据。

而转化为顺序读后，你会连续的使用第 1 页的数据，这时候按照 MySQL 的缓存剔除机制，

这一页的缓存是不会失效的，直到你利用完这一页的数据，由于是顺序读，

在这次查询的余下过程中，你确信不会再用到这一页的数据，可以和这一页数据说告辞了。

顺序读就是通过这三个方面，最大的优化了索引的读取。

别忘了，索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大。

拆分查询条件，进行批量查询

此外，MRR还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。

这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据。

SELECT * FROM t WHERE key_part1 >=1000 AND key_part1 < 2000 AND key_part2 = 1000;

表t有(key_part1,key_part2)的联合索引，因此索引根据key_part1,key_part2的位置关系进行排序。

若没有MRR，此时查询类型为Range，SQL优化器会先将key_part1大于1000且小于2000的数据都取出来，即便key_part2不等于1000。

取出后再根据key_part2的条件进行过滤。这会导致无用的数据被取出。

如果启用MRR优化器会使性能有巨大的提升，优化器会先将查询条件拆分为(1000,1000),(1001,1000),(1002,1000)…(1999,1000) 最后再根据这些拆分出的条件进行数据的查询。

一些关于这场革命的配置

是否启用MRR优化，可以通过参数optimizer_switch中的flag来控制。

1、MRR 的开关：mrr =（on | off）

例如，打开MRR的开关:

mysql > set optimizer_switch='mrr=on';

2、用来告诉优化器，要不要基于使用 MRR 的成本：

mrr_cost_based = （on | off)

例如，通通使用MRR:

SET GLOBAL optimizer_switch='mrr=on,mrr_cost_based=off';

考虑使用 MRR 是否值得(cost-based choice)，来决定具体的 sql 语句里要不要使用 MRR。

很明显，对于只返回一行数据的查询，是没有必要 MRR 的，而如果你把 mrr_cost_based 设为 off，那优化器就会通通使用 MRR，

这在有些情况下是很 stupid 的，所以建议这个配置还是设为 on，毕竟优化器在绝大多数情况下都是正确的。

3、设置用于给 rowid 排序的内存的大小：read_rnd_buffer_size，该值默认是256KB

查看配置

show VARIABLES like 'read_rnd_buffer_size';

显然，MRR 在本质上是一种用空间换时间的算法。

MySQL 不可能给你无限的内存来进行排序，如果 read_rnd_buffer 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 read_rnd_buffer 又达到 read_rnd_buffe 配置的上限，如此循环。

没有MRR的情况下，二级索引里面得到多少行，那么就要去访问多少次主键索引(也不能完全这样说，因为MySQL实现了BNL，是把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价)，而有了MRR的时候，次数就大约减少为之前次数 t / buffer_size。

可以简单理解为：

MRR 把分散的 回表操作， 聚合成了 批量的回表操作， 当然，是借助 空间的局部性原理和磁盘预读取等底层机制完成的。

MRR 使用限制

MRR 适用于range、ref、eq_ref的查询

聊聊：如何使用EXPLAIN关键字？

在日常工作中, 我们会记录一些执行时间比较久的SQL语句, 找出这些SQL语句并不意味着完事了,

我们常常用到explain这个命令来查看一个这些SQL语句的执行计划, 查看该SQL语句有没有使用上了索引, 有没有做全表扫描, 所以我们需要深入了解MySQL基于开销的优化器.

什么是 EXPLAIN关键字

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。

分析你的查询语句或是表结构的性能瓶颈。

通过EXPLAIN，我们可以分析出以下结果：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

EXPLAIN关键字使用方式如下：

EXPLAIN + SQL语句

explain select * from t_member where member_id = 1;

在执行explain命令之后, 显示的信息一共有12列,

执行计划包含的信息

分别是:

  • id: 选择标识符
  • select_type: 查询类型
  • table: 输出结果集的表
  • partitions: 匹配的分区
  • type: 表的连接类型
  • possible_keys: 查询时可能使用的索引
  • key: 实际使用的索引
  • key_len: 索引字段的长度
  • ref: 列与索引的比较
  • rows: 扫描出的行数
  • filtered: 按表条件过滤的行百分比
  • extra: 执行情况描述和说明

执行计划各字段含义

1. id: 查询中执行select子句或操作表的顺序

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同时执行顺序从上到下, 在所有组中, id值越大, 优先级越高, 越先执行

id的结果共有3中情况

  • id相同，执行顺序由上至下

[总结] 加载表的顺序如上图table列所示：t1 t3 t2

  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

  • id相同不同，同时存在

如上图所示，在id为1时，table显示的是 <derived2> ,这里指的是指向id为2的表，即t3表的衍生表。

2. select_type：每个select子句的类型.

常见和常用的值有如下几种：

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

  • SIMPLE 简单的select查询，查询中不包含子查询或者UNION
  • PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
  • SUBQUERY 在SELECT或WHERE列表中包含了子查询
  • DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT 从UNION表获取结果的SELECT

3. table 表

指的就是当前执行的表

4. type 查询类型

type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：

从最好到最差依次是：

system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少达到range级别，最好能达到ref。

  • system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

  • const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。

    因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

    首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的相当于只查询一条记录，所以type为system。

  • eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。

    常见于主键或唯一索引扫描

  • ref 非唯一性索引扫描，返回匹配某个单独值的所有行，

    本质上也是一种索引访问，它返回所有匹配某个单独值的行，

    然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

  • range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，

    一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，

    因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  • index Full Index Scan，

    Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。

    （也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

    id是主键，所以存在主键索引

  • all Full Table Scan 将遍历全表以找到匹配的行

5. possible_keys 和 key

possible_keys 显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

  • 实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

  • 查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中

    则该索引仅出现在key列表中

6. key_len 索引中使用的字节数

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，

即key_len是根据表定义计算而得，不是通过表内检索出的。

7 ref 那一列被使用

显示索引的那一列被使用了，如果可能的话，最好是一个常数。

哪些列或常量被用于查找索引列上的值。

8. rows 所需要读取的行数

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

9. Extra

包含不适合在其他列中显式但十分重要的额外信息

9.1 Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

MySQL中无法利用索引完成的排序操作, 称为“文件排序”。

9.2 Using temporary

使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。

常见于排序order by和分组查询group by。

9.3 Using index

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。

如果同时出现using where，表明索引被用来执行索引键值的查找；

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LuLcvY7Y-1683194805529)(E:\topcoder\blog\公众号\img\20180521090712785.png)]

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

理解方式一:

就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

理解方式二:

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。

毕竞竟索引叶子节点存储了它们索引的数据:当能通过速取索引就可以得到想要的数据，那就不需要速取行了。

一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意:
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *,

因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

9.4 Using where

表明使用了where过滤

9.5 Using join buffer

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

9.6 impossible where

where子句的值总是false，不能用来获取任何元组

SELECT * FROM t_user WHERE id = '1' and id = '2'
9.7 select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

9.8 distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

实例分析

执行顺序1：

id为4，select_type为UNION，

说明第四个select是UNION里的第二个select，最先执行【select name,id from t2】

执行顺序2：

id为3，是整个查询中第三个select的一部分。

因查询包含在from中，所以为DERIVED【select id,name from t1 where other_column=’’】

执行顺序3：

id为2，select列表中的子查询select_type为subquery,

为整个查询中的第二个select【select id from t3】

执行顺序4：

id为1，表示是UNION里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为<derived3>,表示查询结果来自一个衍生表，其中derived3中的3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name …】

执行顺序5：

id为null，代表从UNION的临时表中读取行的阶段，table列的< union1,4 >表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】

实战：MySQL索引优化深入实战

前言：该篇随笔通过一些案例，对索引相关的面试题进行分析。

0.准备

1.创建test表(测试表)。

drop table if exists test; 
 
create table test( 
	id int primary key auto_increment, 
	c1 varchar(10), 
	c2 varchar(10), 
	c3 varchar(10), 
	c4 varchar(10), 
	c5 varchar(10) 
) ENGINE=INNODB default CHARSET=utf8;
insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

2.创建索引。

3.普通查询情况

1.根据以下Case分析索引的使用情况

Case 1：

分析：

①创建复合索引的顺序为c1,c2,c3,c4。

②上述四组explain执行的结果都一样：type=ref，key_len=132，ref=const,const,const,const。

结论：

在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，

因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。

Case 2：

分析：

当出现范围的时候，type=range，key_len=99，比不用范围key_len=66增加了，说明使用上了索引，

但对比Case1中执行结果，说明c4上索引失效。

结论：范围右边索引列失效，但是范围当前位置(c3)的索引是有效的，从key_len=99可证明。

Case 2.1：

分析：

与上面explain执行结果对比，key_len=132说明索引用到了4个，

因为对此sql语句mysql底层优化器会进行优化：

范围右边索引列失效(c4右边已经没有索引列了)，注意索引的顺序(c1,c2,c3,c4)，所以c4右边不会出现失效的索引列，因此4个索引全部用上。

结论：

范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。

Case 2.2：

分析：

如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，

这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。

解决方式使用覆盖索引。

结论：在最佳左前缀法则中，如果最左前列(带头大哥)的索引失效，则后面的索引都失效。

Case 3：

分析：

利用最佳左前缀法则：

中间兄弟不能断，因此用到了c1和c2索引(查找)，从key_len=66，ref=const,const，c3索引列用在排序过程中。

Case 3.1：

分析：

从explain的执行结果来看：key_len=66，ref=const,const，从而查找只用到c1和c2索引，c3索引用于排序。

Case 3.2：

分析：

从explain的执行结果来看：key_len=66，ref=const,const，查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，出现了Using filesort。

Case 4：

分析：

查找只用到索引c1，c2和c3用于排序，无Using filesort。

Case 4.1：

分析：

和Case 4中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。

Case 4.2：

分析：

在查询时增加了c5，但是explain的执行结果一样，因为c5并未创建索引。

Case 4.3：

分析：

与Case 4.1对比，在Extra中并未出现Using filesort，因为c2为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

Case 5：

分析：

只用到c1上的索引，因为c4中间间断了，根据最佳左前缀法则，所以key_len=33，ref=const，表示只用到一个索引。

Case 5.1：

分析：

对比Case 5，在group by时交换了c2和c3的位置，结果出现Using temporary和Using filesort，极度恶劣。原因：c3和c2与索引创建顺序相反。

Case 6

分析：

①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效（其实这里MySQL底层也是有优化的，如果where后的字段是索引的第一个字段使用了范围查询，如果这个范围很大，几乎已经是要扫描所有数据了，

MySQL就会用全表扫描，如果这个范围不是很大，那么MySQL底层依旧还会使用索引来进行查询），

全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。

②使用c1进行排序，但是索引失效，出现了Using filesort。

③解决方法：使用覆盖索引。

就是将索引字段覆盖掉查询字段，实现索引覆盖，MySQL就不会扫描全表而去使用索引了。

Case 7：

分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，

因为索引的所有字段都是按照同一个方向的顺序进行排序的，如果出现了排序方向不同，那么已经排列好的索引自然也就失效了，从而产生Using filesort，而且type还是index（index是扫描全表索引，所以这一个的key_len是132，说明4个索引字段全部都扫描了，ALL是扫描全表，index比ALL稍微快一点）。

Case 8：
EXPLAIN extended select c1 from test where c1 in ('a1','b1') ORDER BY c2,c3;

分析：

对于排序来说，多个相等条件也是范围查询，所以索引失效，c2,c3都无法使用索引，出现Using filesort。

并且这里type是index，扫描全表索引。

总结

  • MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
  • order by满足两种情况会使用Using index。
    • order by语句使用索引最左前列。
    • 使用where子句与order by子句条件列组合满足索引最左前列。
  • 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
  • 如果order by的条件不在索引列上，就会产生Using filesort。
  • group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。注意where高于having，能写在where中的限定条件就不要去having限定了。
通过以上Case的分析，进行如下总结：

①最佳左前缀法则。

1.在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。

2.在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。

②group by容易产生Using temporary。

③通俗理解口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用。

参考文献：

https://blog.csdn.net/qq_39708228/article/details/118692397

https://zhuanlan.zhihu.com/p/401198674

https://cloud.tencent.com/developer/article/1774781

https://blog.csdn.net/sufu1065/article/details/123343482

https://www.cnblogs.com/xiatc/p/16363312.html

https://blog.csdn.net/a303549861/article/details/96117063

https://segmentfault.com/a/1190000021086051

https://blog.csdn.net/CSDN_WYL2016/article/details/120500830

https://blog.csdn.net/xiao__jia__jia/article/details/117408114

https://blog.csdn.net/why15732625998/article/details/80388236

https://blog.csdn.net/weixin_39928017/article/details/113217272

技术自由的实现路径 PDF 获取：

实现你的 架构自由：

《吃透8图1模板，人人可以做架构》

《10Wqps评论中台，如何架构？B站是这么做的！！！》

《阿里二面：千万级、亿级数据，如何性能优化？ 教科书级 答案来了》

《峰值21WQps、亿级DAU，小游戏《羊了个羊》是怎么架构的？》

《100亿级订单怎么调度，来一个大厂的极品方案》

《2个大厂 100亿级 超大流量 红包 架构方案》

… 更多架构文章，正在添加中

实现你的 响应式 自由：

《响应式圣经：10W字，实现Spring响应式编程自由》

这是老版本 《Flux、Mono、Reactor 实战（史上最全）》

实现你的 spring cloud 自由：

《Spring cloud Alibaba 学习圣经》 PDF

《分库分表 Sharding-JDBC 底层原理、核心实战（史上最全）》

《一文搞定：SpringBoot、SLF4j、Log4j、Logback、Netty之间混乱关系（史上最全）》

实现你的 linux 自由：

《Linux命令大全：2W多字，一次实现Linux自由》

实现你的 网络 自由：

《TCP协议详解 (史上最全)》

《网络三张表：ARP表, MAC表, 路由表，实现你的网络自由！！》

实现你的 分布式锁 自由：

《Redis分布式锁（图解 - 秒懂 - 史上最全）》

《Zookeeper 分布式锁 - 图解 - 秒懂》

实现你的 王者组件 自由：

《队列之王： Disruptor 原理、架构、源码 一文穿透》

《缓存之王：Caffeine 源码、架构、原理（史上最全，10W字 超级长文）》

《缓存之王：Caffeine 的使用（史上最全）》

《Java Agent 探针、字节码增强 ByteBuddy（史上最全）》

实现你的 面试题 自由：

4000页《尼恩Java面试宝典 》 40个专题

以上尼恩 架构笔记、面试题 的PDF文件更新，请到《技术自由圈》公号获取↓↓↓

免费领取11个技术圣经PDF：

# mysql # 数据库 # sql
Logo
GitCode 开源社区

旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。

更多推荐

cover

GitTalk | DevUI Admin 前端项目构建

avatar GitCode 开源社区
cover

GitTalk | 使用面向业务的狮偶编程语言提升开发效率

avatar GitCode 开源社区
cover

GitTalk | DevUI Suits 场景解决方案

avatar GitCode 开源社区