索引下推

原文：五分钟搞懂 MySQL 索引下推

什么是索引下推

索引下推 (Index Condition Pushdown，简称 ICP)，是 MySQL 5.6 版本的新特性，它能减少回表次数，提高查询效率。

索引下推优化的原理

我们先简单了解一下 MySQL 大概的架构：

MySQL Server 层负责 SQL 语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用 ICP 的情况下，MySQL 的查询：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给 Server 层去检测该记录是否满足WHERE条件。

使用 ICP 的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，如果检查发现条件不满足，则处理下一行索引记录（也就是按WHERE条件过滤掉不满足的索引记录）；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给 Server 层，Server 层检测该记录是否满足WHERE条件的其余部分。

索引下推的具体实践

举个例子。使用一张用户表tuser，表里创建联合索引（name, age）。

现有如下 SQL 语句：

select * from tuser where name like '张%' and age=10;

根据最左匹配原则，只能用到联合索引（name, age）中的 name。

没有使用 ICP

在 MySQL 5.6 之前，存储引擎根据通过联合索引找到name like '张%' 的主键 id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，Server 层再对数据根据age=10进行筛选。

我们看一下示意图：

可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

使用 ICP

而 MySQL 5.6 以后，存储引擎根据（name，age）联合索引，找到name like '张%'，由于联合索引中包含age列，所以存储引擎直接按照age=10过滤联合索引。按照过滤后的数据再进行回表扫描。

我们看一下示意图：

可以看到只回表了一次。

除此之外我们还可以看一下执行计划，看到Extra一列中有Using index condition，表示用到了索引下推。

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

索引下推使用条件

只能用于range、 ref、 eq_ref、ref_or_null访问方法；
只能用于InnoDB和 MyISAM存储引擎及其分区表；
对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;

索引下推的目的是为了减少回表次数，也就是要减少 IO 操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。
引用了子查询的条件不能下推；
引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。