前言：MySQL5.6推出了ICP和MRR特性，一定程度上解决了MySQL索引范围查询的问题，之前对于ICP有过一些了解，对MRR并不是很熟，今天对这两个特性做一次对比总结。

一、MRR

MRR全称（ Multi-Range Read），我的理解是MRR主要是合并离散IO为顺序IO，这跟MySQL大体的优化思路是一致的。

MySQL的二级索引的值在引擎层存储一般来说都是比较离散的，并且由于index extentions的特性，通过二级索引找到主键值，再通过主键值找到整行数据，如果没有开启MRR，这样一来势必会造成很多的随机IO。例如，我要选择B列值范围大于3小于6的行数据，那么b=4或者b=5可能有很多值，对应的主键值也有很多，而这些主键值不一定分布在一起，可能分布在很多的page上，如果一个一个去读取的话，可能就会读取很多”无用page”（比如一个page只有一条符合的记录）
那么如果开启了MRR，MySQL会怎么做呢？MySQL会将二级索引缓存在buffer里，根据pk的值排序后再去主键索引上去获取相应的值，这样一来就将随机IO合并成顺序IO，可能一次读取一个page就有很多符合条件的记录
MRR的限制：
- MRR不支持InnoDB的虚拟列
- 在InnoDB和MyISAM引擎上，MRR只能用于范围查询和等值连接
- 如果不需要全部访问整个表的数据，那么MRR将不会用到，例如使用了覆盖索引
MRR的作用原理
- 索引的一部分在缓冲区中缓存。
- 缓冲中的元组按照它们的数据行ID进行排序
- 根据排序后的索引元组序列访问数据行

启用MRR（5.7默认开启）

root@localhost [(none)]>show global variables like '%optimizer_switch%';
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                            |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

上面提到的缓存二级索引的buffer大小由变量read_rnd_buffer_size控制

root@localhost [(none)]>show global variables like '%rnd%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| read_rnd_buffer_size | 16777216 |
+----------------------+----------+
1 row in set (0.00 sec)

例子
- ```
SELECT * FROM t
WHERE a >= 1000 AND a< 2000
AND b= 10000;
```
- 二级索引由(a, b)组成，这些值首先由a排序，然后由b排序。
- 没有MRR，索引扫描将覆盖a列所有索引元组，范围从1000到2000，而不考虑这些元组中的b列的值。扫描做了额外的工作，范围内的元组包含除了10000之外的b列值。
- 使用MRR，扫描被分成多个范围，每个范围对应一个值a(1000, 1001…1999)，在这些范围扫描内，只需要去查找b列等于10000的值即可，如果这些范围内有很多b不等于10000的值，MRR需要读取的索引元组就更小了。
- 为了使用区间表示法表示，非mrr扫描必须检查索引范围[{1000,10000}，{2000,MIN_INT}]，其中可能包许多不是b = 10000的元组。MRR扫描检查多个单点间隔[{1000,10000}]，…，[{1999, 10000}]，其中只包含key_part2 = 10000的元组。

二、ICP

ICP全称（Index Condition Pushdown），是对MySQL从表中检索行使用索引的一种优化。如果没有ICP，存储引擎就会遍历索引以定位基表中的行，并将它们返回到server层，server层会评估获取行的WHERE条件。启用了ICP，并且如果WHERE条件的部分可以通过只使用索引中的列进行计算，那么MySQL服务器将该部分WHERE条件下推到存储引擎。在存储引擎层就过滤掉不需要的记录。ICP可以减少存储引擎访问基表的次数，以及server层访问存储引擎的次数。

ICP适用于以下条件：
- ICP用于range,ref,eq_ref和ref_or_null方式的表访问
- ICP适用于InnoDB引擎和MyISAM引擎表，包含分区表
- 对于InnoDB表，ICP只用于二级索引。ICP的目的就是减少回表的次数和IO操作。对于InnoDB聚集索引来说，会将整行数据都读入buffer中，这种情况下ICP并不能减少IO。
- ICP不能用于虚拟列
- 子查询的条件不能下推
- 涉及到函数的条件不能下推
- 触发器条件不能下推

没有使用ICP的情况下：

获取下一行，首先读取索引元组，然后使用索引元组定位和读取整个数据行。
测试适用于此表的WHERE条件的部分。根据测试结果筛选行。

使用ICP的情况下;

获取下一行的索引元组(但不获取整个表行)。
测试应用于此表的WHERE条件的部分，并且只能使用索引列进行检查。如果条件不满足，则进入下一行的索引值。
如果满足条件，则使用索引值查找并读取整个表行。
测试应用于该表的WHERE条件的其余部分。根据测试结果筛选行。

如果使用了ICP，那么执行计划会显示Using index condition

开启ICP（5.7默认开启）

SET optimizer_switch = 'index_condition_pushdown=on';

例子
- ```
SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%etrunia%'
AND address LIKE '%Main Street%';
```
- 有一个多列索引(zipcode,lastname,address)
- 我们分析下where条件，zipcode是等值查询，lastname和address是范围查询
- 如果没有ICP，那么条件匹配到的列只有zipcode
- 但是如果开启ICP，那么除了用到zipcode列以外，也会用到lastname列在引擎层做过滤

以上就是我对MRR和ICP的一点总结，基本上也是对官方文档的翻译
参考文章：
https://dev.mysql.com/doc/refman/8.0/en/mrr-optimization.html
https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

MySQL知识库

【MySQL】优化器之MRR&ICP

一、MRR

二、ICP