再谈ICP

准确地说，这是一篇打脸文章，打的还是自己的脸，真疼。

旧文为什么你的SQL执行很慢曾经简单分析了一下SQL慢查的原因，然而，文中关于ICP的论述却是错误的。但是因为公众号文章无法修改，所以今天，我们就再来详细了解一下ICP。

为了方便说明，这里先给出建表语句和初始化语句：

CREATE TABLE t (id int(10) NOT NULL AUTOINCREMENT,a int(10) DEFAULT NULL,b varchar(16) COLLATE utf8mb4unicodeci DEFAULT NULL,c varchar(8) COLLATE utf8mb4unicodeci DEFAULT NULL,
PRIMARY KEY ( id),
KEY idx_a ( a)
KEY idx_b ( b),
KEY idx_a_c ( a, c)
) ENGINE=InnoDB AUTOINCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4unicodeci




delimiter ;;
create procedure init()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t values(i, i, cast(i as char), '12345678');
set i=i+1;
end while;
end;;
delimiter ;
call init();
复制

ICP

ICP，全称Index Condition Pushdown，是MySQL5.6版本推出的一项新特性。旨在将部分where条件的过滤从server层下放至engine层，以减少回表次数和server层对数据记录的读取行数，从而提高SQL的执行效率。

为了弄清楚为什么ICP可以提高优化SQL的执行效率，我们先简单了解一下MySQL的整体架构。

从大的层面来说，MySQL可以分为两层。上层是Server层，主要包含了MySQL一些核心功能的实现，比如优化器等等；下层是Engine层，主要就是各种存储引擎，负责数据的存储。如下图：

其中Server层又可以进一步细分如下：

连接器：主要是负责建立连接、权限校验等等工作，解决who的问题

分析器：主要是负责语法分析和词法分析，解决what的问题

优化器：主要是负责执行计划的确定，解决how的问题

执行器：根据优化器确定的执行计划向Engine层发起数据请求，返回结果

下图给出了在开启ICP前后SQL的执行示意图。

举个例子，对于这样一条SQL语句：

select * from t2 where a > 9000 and c like '%1'; 关闭ICP时，其执行计划如下：

开启ICP时，其执行计划则变为，Extra字段出现了Using index condition的提示，表示使用的ICP优化：

对应的慢查日志分别为：

可以看到，关闭ICP时，Engine层最终返回了1000条记录，但最终结果集为空，而开启ICP时，Engine层返回的结果集就已经为空了，减少了1000次不必要的回表操作和Server层对这1000条记录的读取操作。而这其中的原因就在于前者在Engine层只应用了a > 9000的条件， c like '%1'这个条件是放到Server层比较的，开启ICP之后，Engine层则是同时应用了 a > 9000 and c like '%1'的条件。

MRR

既然谈到了ICP，那就顺便再聊一聊同样是MySQL5.6版本推出的另外一项新特性：MRR(Multi-Range Read)。MRR的主要目的是减少对主键索引的随机访问，将随机访问转化为较为顺序的数据访问。

下图给出了开启MRR前后的SQL执行示意图：

简单解释一下，通常情况下，根据二级索引过滤出来的数据是按照二级索引有序，因此回表的时候也是按照二级索引的顺序回表的。然而，由于主键索引的顺序和二级索引的顺序通常不一样，因此这就会造成对主键索引的随机访问。为了避免这一问题，开启MRR后，MySQL会对二级索引过滤之后的结果先按照主键进行排序，然后再按照这个按主键有序的列表进行回表操作。

举个例子，假设我们的二级索引树如下，现在我们要查询二级索引字段大于1的数据：

在未开启MRR的条件下，由于是按照二级索引顺序回表，所以回表时的主键依次为：40、20、10、60、30、50，这就造成了对主键索引的随机访问。

开启MRR之后，按照主键排序，这时回表的主键顺序就变成了：10、20、30、40、50、60，从随机访问变成了顺序访问。

在MySQL5.6之后的版本中，MRR都是默认开启的，但是与ICP只有一个参数控制不同，MRR有两个参数共同控制:

其中mrr用于控制是否开启mrr优化，但开启mrr优化并不意味着所有符合条件的SQL语句都会采用此优化，因为排序操作也是有代价的，如果优化器评估认为排序代价太大，也可能会放弃mrr优化。因此如果你想一直启用mrr功能，则需要这么设置：mrr=on,mrrcostbased=off.

全文完(๑′ᴗ‵๑)