关于mysql:第26期索引设计索引下推

40次阅读

共计 5530 个字符，预计需要花费 14 分钟才能阅读完成。

索引下推（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 不反对援用子查问的条件。

对于 MySQL 的技术内容，你们还有什么想晓得的吗？连忙留言通知小编吧！

正文完
mysql
发表至： mysql
2021-04-21
 0
关于mysql:数据库漫谈四
关于mysql:docker-部署mysql数据库
关于mysql:MacBook安装Mysql
MySQL数据库简介