第26期：索引设计（索引下推）

于由杨涛涛发布

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是 MySQL 5.6 发布后针对扫描二级索引的一项优化改进。总的来说是通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。ICP 适用于 MYISAM 和 INNODB，本篇的内容只基于 INNODB。

MySQL ICP 里涉及到的知识点如下：

1. MySQL 服务层：也就是 SERVER 层，用来解析 SQL 的语法、语义、生成查询计划、接管从 MySQL 存储引擎层上推的数据进行二次过滤等等。

2. MySQL 存储引擎层：按照 MySQL 服务层下发的请求，通过索引或者全表扫描等方式把数据上传到 MySQL 服务层。

3. MySQL 索引扫描：根据指定索引过滤条件（比如 where id = 1) ，遍历索引找到索引键对应的主键值后回表过滤剩余过滤条件。

4. MySQL 索引过滤：通过索引扫描并且基于索引进行二次条件过滤后再回表。

ICP 就是把以上索引扫描和索引过滤合并在一起处理，过滤后的记录数据下推到存储引擎后的一种索引优化策略。这样做的优点如下：

    1. 减少了回表的操作次数。

    2. 减少了上传到 MySQL SERVER 层的数据。

ICP 默认开启，可通过优化器开关参数关闭 ICP：optimizer_switch=’index_condition_pushdown=off’ 或者是在 SQL 层面通过 HINT 来关闭。

接下来，看下不使用 ICP、使用 ICP 的详细示例来理清 ICP 的概念。

在不使用 ICP 索引扫描的过程：

MySQL 存储引擎层只把满足索引键值对应的整行表记录一条一条取出，并且上传给 MySQL 服务层。

MySQL 服务层对接收到的数据，使用 SQL 语句后面的 where 条件过滤，直到处理完最后一行记录，再一起返回给客户端。

假设 SQL 语句为：

(localhost:mysqld.sock)|(ytt)>select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)

关闭 ICP 的处理流程大概如图 1：

使用 ICP 扫描的过程：

MySQL 存储引擎层，先根据过滤条件中包含的索引键确定索引记录区间，再在这个区间的记录上使用包含索引键的其他过滤条件进行过滤，之后规避掉不满足的索引记录，只根据满足条件的索引记录回表取回数据上传到 MySQL 服务层。

MySQL 服务层对接收到的数据，使用 where 子句中不包含索引列的过滤条件做最后的过滤，然后返回数据给客户端。

如下图所示：

上面两张图很明显的对比出开启 ICP 比不开启 ICP 的效率。返回数据这一块虚线表示规避掉的记录，开启 ICP 很明显减少了上传到 MySQL 存储引擎层、MySQL 服务层的记录条数，节省了 IO。

查看语句是否用了 ICP，只需要对语句进行 EXPLAIN，在 EXTRA 信息里可以看到 ICP 相关信息。

以下分别为关闭 ICP 与开启 ICP 的 EXPLAIN 结果：

(localhost:mysqld.sock)|(ytt)>explain select /*+ no_icp (t1)  */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r4,idx_u1
          key: idx_u1
      key_len: 5
          ref: const
         rows: 325
     filtered: 0.12
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

(localhost:mysqld.sock)|(ytt)>explain select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G  *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: idx_r4,idx_u1
          key: idx_u1
      key_len: 5
          ref: const
         rows: 325
     filtered: 0.12
        Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)

其中 extra 里显示 “Using index condition” 就代表用了 ICP。不过这个信息有点过于简单了，除了 EXTRA 列结果显示不同外，其他的列结果都一样，没法从执行计划结果判断 ICP 的优略。

可以通过以下几种方法来查看 ICP 带来的直观性能提升。

1. show status like ‘%handler%’

show status 语句可以查看对存储引擎的相关指标监控结果。从以下结果可以看出：指标 Handler_read_next（表示 MySQL 存储引擎按照索引键顺序读取下一行记录的请求数，也就是说这个值表示按照索引键值来访问基表的请求数）在没有开启 ICP 时，值为 325，也就是说对基表读取请求 325 次；而开启 ICP 后，这个值仅有 14 次。所以开启 ICP 效率提升很明显。

(localhost:mysqld.sock)|(ytt)>flush status;
Query OK, 0 rows affected (0.01 sec)

(localhost:mysqld.sock)|(ytt)> select /*+ no_icp (t1) */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)


(localhost:mysqld.sock)|(ytt)>show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 325   |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

(localhost:mysqld.sock)|(ytt)>flush status;
Query OK, 0 rows affected (0.01 sec)


(localhost:mysqld.sock)|(ytt)>select * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)


(localhost:mysqld.sock)|(ytt)>show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 14    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

(localhost:mysqld.sock)|(ytt)>

2. 开启 profiles

查看 profile 结果的总体时间，关闭 ICP 为：0.00101900，开启 ICP 为：0.00100325。时间上开启 ICP 占优势。

(localhost:mysqld.sock)|(ytt)>set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

(localhost:mysqld.sock)|(ytt)>show profiles;
Empty set, 1 warning (0.00 sec)

(localhost:mysqld.sock)|(ytt)> select /*+ no_icp (t1) */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G
*************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)

(localhost:mysqld.sock)|(ytt)> select  * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5\G        *************************** 1. row ***************************
id: 28965
f1: 81
f2: 89
f3: 100
f4: 35
r1: 1
r2: 12844bda dog 11ea a051 08002753f58d
r3: 17
r4: 5
1 row in set (0.00 sec)


(localhost:mysqld.sock)|(ytt)>show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00101900
Query: select /*+ no_icp (t1) */ * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00100325
Query: select  * from t1 where r1 = 1 and r2 like '%dog%' and r4 = 5
2 rows in set, 1 warning (0.00 sec)

任何需要下推到底层存储层的操作一般都有诸多限制，MySQL ICP 也不例外，ICP 限制如下：

1. ICP 仅用于需要访问基表所有记录时使用，适用的访问方法为：range、ref、eq_ref、ref_or_null。我上面举的例子即是 ref 类型，ICP 尤其是对联合索引的部分列模糊查找非常有效。

2. ICP 同样适用于分区表。

3. ICP 的目标是减少全行记录读取，从而减少 I/O 操作，仅用于二级索引。主键索引本身即是表数据，不存在下推操作。

4. ICP 不支持基于虚拟列上建立的索引，比如函数索引。

5. ICP 不支持引用子查询的条件。

文章推荐：

第25期：索引设计（索引的基数与可选择性）

第24期：索引设计（多值索引的适用场景）
第23期：索引设计（组合索引不适用场景改造）

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

分类： MySQL 调优
标签：

相关文章

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句 阅读更多…

MySQL 调优

第44期：无主键分区表该不该使用

本来想着分区表在上一篇后就不续写了，最近又有同学咨询我分区表的新问题： 阅读更多…

MySQL 调优

第43期：多表关联场景下如何用好分区表

如何在多表关联场景下合理利用分区表来提升查询性能？基于前几篇关于分区表 阅读更多…