MySQL索引的条件下推例（ICP）的性能优化方法

概念简介

指数条件下推（ICP），在MySQL 5.6版本的新功能，是使用索引的存储引擎层过滤数据的优化方法。

当关闭ICP时，索引仅仅是数据访问的一种访问方式。存储引擎通过索引返回表获得的数据将传输到MySQL服务器层，用于条件过滤。

当B ICP打开时，如果有一部分可以在字段条件下使用索引，MySQL服务器将把这部分推到引擎层，数据可以在存储引擎层过滤，使用索引过滤的条件，而不是全部到MySQL服务器层，通过索引访问结果过滤其中的结果。

优化结果：ICP可以减少引擎层访问基本表的次数和MySQL服务器访问存储引擎的次数，减少IO数量，提高查询语句的性能。

两个原则

指数条件下推是没有用的：

1获取下一行，首先读取索引元组，然后

2测试适用于本表的部分条件。

指数条件下使用

1获取下一行的索引元组（但不是完整的表）。

2测试适用于本表的部分条件

如果条件不满意，则继续到索引元组。

3如果满足条件，使用索引元组来定位和

4 EST是适用于此的条件的剩余部分

三实践案例

一个环境准备

数据库版本5.6.16

关闭缓存

复制代码代码如下所示：

集query_cache_size = 0；

集query_cache_type =关闭；
测试数据下载地址

b打开ICP时

复制代码代码如下所示：

设置分析= 1；

查询OK，受影响的0行，1警告（0秒）

MySQL >选择*从员工那里first_name = 'anneke'and last_name是信号；

+ + + + -------- ------------ ------------ ----------- -------- ------------ + + +

emp_no birth_date first_name | | | | last_name |性别| hire_date |

+ + + + -------- ------------ ------------ ----------- -------- ------------ + + +

| 10006 | 1953-04-20 Anneke Preusig F | | | | 1989-06-02 |

+ + + + -------- ------------ ------------ ----------- -------- ------------ + + +

1行集（0秒）

显示配置文件；

+ + + + ---------- ------------ --------------------------------------------------------------------------------

query_id时间查询| | | |

+ + + + ---------- ------------ --------------------------------------------------------------------------------

| 1 | 0.00060275 |选择*从员工那里first_name = 'anneke'and last_name是信号|

+ + + + ---------- ------------ --------------------------------------------------------------------------------

3行，1警告（0秒）
在这种情况下，按照最左前缀原理MySQL，first_name可以用索引，和last_name使用模糊查询，没有指标可。

C关闭ICP

复制代码代码如下所示：

MySQL >设置optimizer_switch = 'index_condition_pushdown =关闭；

查询OK，0行受影响（0秒）

设置分析= 1；

查询OK，受影响的0行，1警告（0秒）

MySQL >选择*从员工那里first_name = 'anneke'and last_name是信号；

+ + + + -------- ------------ ------------ ----------- -------- ------------ + + +

emp_no birth_date first_name | | | | last_name |性别| hire_date |

+ + + + -------- ------------ ------------ ----------- -------- ------------ + + +

| 10006 | 1953-04-20 Anneke Preusig F | | | | 1989-06-02 |

+ + + + -------- ------------ ------------ ----------- -------- ------------ + + +

1行集（0秒）

设置分析= 0；

查询OK，受影响的0行，1警告（0秒）

显示配置文件；

+ + + + ---------- ------------ --------------------------------------------------------------------------------

query_id时间查询| | | |

+ + + + ---------- ------------ --------------------------------------------------------------------------------

| 2 | 0.00097000 |选择*从员工那里first_name = 'anneke'and last_name是信号|

+ + + + ---------- ------------ --------------------------------------------------------------------------------

6行，1警告（0秒）
当ICP打开，在发送数据链路的时间消耗0.000189s

复制代码代码如下所示：

显示配置文件CPU，查询1的块IO；

+ + + + ---------------------- ---------- ---------- ------------ -------------- --------------- + + +

状态持续时间cpu_user | | | | cpu_system | block_ops_in | block_ops_out |

+ + + + ---------------------- ---------- ---------- ------------ -------------- --------------- + + +

|开始| 0.000094 | 0 0 0 0 | | | |

|检查权限| 0.000011 | 0 0 0 0 | | | |

|打开表| 0.000025 | 0 0 0 0 | | | |

| init | 0.000044 | 0 0 0 0 | | | |

|系统锁| 0.000014 | 0 0 0 0 | | | |

|优化| 0.000021 | 0 0 0 0 | | | |

|统计| 0.000093 | 0 0 0 0 | | | |

| | 0.000024 | 0 | 0 | 0 | 0 |准备

|执行| 0.000006 | 0 0 0 0 | | | |

|发送数据| 0.000189 | 0 0 0 0 | | | |

|端| 0.000019 | 0 0 0 0 | | | |

|查询结束| 0.000012 | 0 0 0 0 | | | |

|关闭表| 0.000013 | 0 0 0 0 | | | |

|释放项目| 0.000034 | 0 0 0 0 | | | |

|清理| 0.000007 | 0 0 0 0 | | | |

+ + + + ---------------------- ---------- ---------- ------------ -------------- --------------- + + +

15行，1警告（0秒）
当ICP是封闭的，在发送数据链路的时间消耗是0.000735s

复制代码代码如下所示：

显示配置文件CPU，查询2的块IO；

+ + + + ---------------------- ---------- ---------- ------------ -------------- --------------- + + +

状态持续时间cpu_user | | | | cpu_system | block_ops_in | block_ops_out |

+ + + + ---------------------- ---------- ---------- ------------ -------------- --------------- + + +

|开始| 0.000045 | 0 0 0 0 | | | |

|检查权限| 0.000007 | 0 0 0 0 | | | |

|打开表| 0.000015 | 0 0 0 0 | | | |

| init | 0.000024 | 0 0 0 0 | | | |

|系统锁| 0.000009 | 0 0 0 0 | | | |

|优化| 0.000012 | 0 0 0 0 | | | |

|统计| 0.000049 | 0 0 0 0 | | | |

| | 0.000016 | 0 | 0 | 0 | 0 |准备

|执行| 0.000005 | 0 0 0 0 | | | |

|发送数据| 0.000735 | 0.001000 0 0 0 | | | |

|端| 0.000008 | 0 0 0 0 | | | |

|查询结束| 0.000008 | 0 0 0 0 | | | |

|关闭表| 0.000009 | 0 0 0 0 | | | |

|释放项目| 0.000023 | 0 0 0 0 | | | |

|清理| 0.000007 | 0 0 0 0 | | | |

+ + + + ---------------------- ---------- ---------- ------------ -------------- --------------- + + +

15行，1警告（0秒）
从剖面上，可以看出ICP整个SQL执行时间是未开封的2 / 3，和发送数据链路的时间消耗仅为后者的1／4。

ICP开放的执行计划包含索引条件，指示优化器使用ICP优化数据访问。

复制代码代码如下所示：

MySQL >解释选择*从员工那里first_name = 'anneke'and last_name是NTA；

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------- ------ ----------------------- + + +

我select_type表| | | |型possible_keys关键key_len | | | | REF |行|额外|

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------- ------ ----------------------- + + +

| 1 |简单|员工参考idx_emp_fnln idx_emp_fnln | | | | 44 | const | 224 |索引的使用条件|

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------- ------ ----------------------- + + +

1行集（0秒）
ICP关闭时的执行计划显示在何处使用。

复制代码代码如下所示：

MySQL >解释选择*从员工那里first_name = 'anneke'and last_name是NTA；

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------- ------ ------------- + + +

我select_type表| | | |型possible_keys关键key_len | | | | REF |行|额外|

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------- ------ ------------- + + +

| 1 |简单|员工参考idx_emp_fnln idx_emp_fnln | | | | 44 | const | 224 |使用|

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------- ------ ------------- + + +

1行集（0秒）
案例分析

以上面的查询为例，当ICP是封闭的，存储引擎前缀索引first_name访问Anneke 225 first_name数据，过滤在MySQL服务器层根据last_name是信号。

当打开ICP，类似% sig'condition的last_name可以过滤通过索引字段last_name，和不符合要求的数据可以直接过滤掉在存储引擎相比，条件，过程不返回的表，只有1个记录，符合条件并返回MySQL服务器，有效地降低了IO访问层与层之间的相互作用。

当ICP关闭时，索引仅用作访问数据的方法。
在启用ICP时，MySQL将对存储引擎层索引中的数据进行过滤，减少不必要的返回表，并注意使用虚线的位置。如果条件包含没有索引的字段，它仍然会通过MySQL服务器层过滤。
四使用限制

1当SQL需要全表访问，ICP的优化策略可用于访问数据的方法的范围，裁判，eq_ref，和ref_or_null类型。

2支持InnoDB和MyISAM表。

3 ICP只能用于二级指标，不能作为主要指标。

4并非所有可以用ICP筛选条件的地方。

如果WHERE条件字段不在索引列中，也需要将整个表的记录读取到服务器端，以便进行筛选。

5 ICP的加速效应取决于通过ICP在存储引擎中过滤的数据的比例。

65.6版本不支持子表的ICP功能，也不支持5.7版本的开始支持。

7当SQL使用覆盖索引时，它不支持ICP优化方法。

复制代码代码如下所示：

MySQL >解释选择*从员工那里first_name = 'anneke'and last_name = 'porenta；

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------------- ------ ----------------------- + + +

我select_type表| | | |型possible_keys关键key_len | | | | REF |行|额外|

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------------- ------ ----------------------- + + +

| 1 |简单的员工idx_emp_fnln |裁判| | | idx_emp_fnln | | 94 const，const 1索引的使用条件| | |

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------------- ------ ----------------------- + + +

1行集（0秒）

MySQL >解释选择first_name，从员工那里first_name = 'anneke'and last_name = 'porentalast_name；

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------------- ------ -------------------------- + + +

我select_type表| | | |型possible_keys关键key_len | | | | REF |行|额外|

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------------- ------ -------------------------- + + +

| 1 |简单的员工idx_emp_fnln |裁判| | | idx_emp_fnln | | const常量94，1 | |使用；使用索引|

+ -- + + + + ------------- ----------- ------ --------------- + -------------- + --------- + ------------- ------ -------------------------- + + +

1行集（0秒）