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

爱可生开源社区

发布于 2021-04-23 10:24:32

5800

发布于 2021-04-23 10:24:32

索引下推（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 不支持引用子查询的条件。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-04-21，如有侵权请联系 cloudcommunity@tencent.com 删除

sql