揭秘Mysql 5.6 Index Condition Pushdown(ICP)

ICP(index condition pushdown)是mysql利用索引(二级索引)元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作。ICP的思想是:存储引擎在访问索引的时候检查筛选字段在索引中的where条件(pushed index condition,推送的索引条件),如果索引元组中的数据不满足推送的索引条件,那么就过滤掉该条数据记录。ICP(优化器)尽可能的把index condition的处理从server层下推到storage engine层。storage engine使用索引过过滤不相关的数据,仅返回符合index condition条件的数据给server层。也是说数据过滤尽可能在storage engine层进行,而不是返回所有数据给server层,然后后再根据where条件进行过滤。

使用ICP(mysql 5.6版本以前)和没有使用ICP的数据访问和提取过程如下:

1)    当storage engine读取下一行时,首先读取索引元组(index tuple),然后使用索引元组在基表中(base table)定位和读取整行数据。

2)    sever层评估where条件,如果该行数据满足where条件则使用,否则丢弃。

3)    执行1),直到最后一行数据。



优化器使用ICP时,server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。数据访问和提取过程如下:

1)    storage engine从索引中读取下一条索引元组。

2)    storage engine使用索引元组评估下推的索引条件。如果没有满足wehere条件,storage engine将会处理下一条索引元组(回到上一步)。只有当索引元组满足下推的索引条件的时候,才会继续去基表中读取数据。

3)    如果满足下推的索引条件,storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。

4)    server层评估没有被下推到storage engine层的where条件,如果该行数据满足where条件则使用,否则丢弃(第二图虚线的using where 表示如果where条件中含有没有被索引的字段,则还是要经过MySQL Server 层过滤)。



ICP的开启优化功能与关闭

MySQL5.6可以通过设置optimizer_switch([global|session],dynamic)变量开启或者关闭index_condition_push优化功能,默认开启。

mysql > set optimizer_switch=’index_condition_pushdown=on|off’

用explain查看执行计划时,如果执行计划中的Extra信息为“using index condition”,表示优化器使用的index condition pushdown。


在mysql5.6以前,还没有采用ICP这种查询优化,where查询条件中的索引条件在某些情况下没有充分利用索引过滤数据。假设一个组合索引(多列索引)K包含(c1,c2,…,cn)n个列,如果在c1上存在范围扫描的where条件,那么剩余的c2,…,cn这n-1个上索引都无法用来提取和过滤数据(不管不管是唯一查找还是范围查找),索引记录没有被充分利用。即组合索引前面字段上存在范围查询,那么后面的部分的索引将不能被使用,因为后面部分的索引数据是无序。比如,索引key(a,b)中的元组数据为(0,100)、(1,50)、(1,100) ,where查询条件为 a < 2 and b = 100。由于b上得索引数据并不是连续区间,因为在读取(1,50)之后不再会读取(1,100),mysql优化器在执行索引区间扫描之后也不再扫描组合索引其后面的部分。


演示实例

当开启ICP时(默认开启):

mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006  | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                          |
+----------+------------+--------------------------------------------------------------------------------+
| 1        | 0.00060275 | select * from employees where first_name='Anneke' and last_name like '%sig'    |
+----------+------------+--------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000094 | 0.000000 | 0.000000   | 0            | 0             |
| checking permissions | 0.000011 | 0.000000 | 0.000000   | 0            | 0             |
| Opening tables       | 0.000025 | 0.000000 | 0.000000   | 0            | 0             |
| init                 | 0.000044 | 0.000000 | 0.000000   | 0            | 0             |
| System lock          | 0.000014 | 0.000000 | 0.000000   | 0            | 0             |
| optimizing           | 0.000021 | 0.000000 | 0.000000   | 0            | 0             |
| statistics           | 0.000093 | 0.000000 | 0.000000   | 0            | 0             |
| preparing            | 0.000024 | 0.000000 | 0.000000   | 0            | 0             |
| executing            | 0.000006 | 0.000000 | 0.000000   | 0            | 0             |
| Sending data         | 0.000189 | 0.000000 | 0.000000   | 0            | 0             |
| end                  | 0.000019 | 0.000000 | 0.000000   | 0            | 0             |
| query end            | 0.000012 | 0.000000 | 0.000000   | 0            | 0             |
| closing tables       | 0.000013 | 0.000000 | 0.000000   | 0            | 0             |
| freeing items        | 0.000034 | 0.000000 | 0.000000   | 0            | 0             |
| cleaning up          | 0.000007 | 0.000000 | 0.000000   | 0            | 0             |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
根据MySQL的最左前缀原则, first_name 可以使用索引,last_name采用了like 模糊查询,不能使用索引。查询时间耗时0.00060275秒,sending data耗时0.000189秒。


当关闭ICP时:

mysql> set optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from employees where first_name='Anneke' and last_name like '%sig' ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10006  | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                          |
+----------+------------+--------------------------------------------------------------------------------+
| 2        | 0.00097000 | select * from employees where first_name='Anneke' and last_name like '%sig'    |
+----------+------------+--------------------------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000045 | 0.000000 | 0.000000   | 0            | 0             |
| checking permissions | 0.000007 | 0.000000 | 0.000000   | 0            | 0             |
| Opening tables       | 0.000015 | 0.000000 | 0.000000   | 0            | 0             |
| init                 | 0.000024 | 0.000000 | 0.000000   | 0            | 0             |
| System lock          | 0.000009 | 0.000000 | 0.000000   | 0            | 0             |
| optimizing           | 0.000012 | 0.000000 | 0.000000   | 0            | 0             |
| statistics           | 0.000049 | 0.000000 | 0.000000   | 0            | 0             |
| preparing            | 0.000016 | 0.000000 | 0.000000   | 0            | 0             |
| executing            | 0.000005 | 0.000000 | 0.000000   | 0            | 0             |
| Sending data         | 0.000735 | 0.001000 | 0.000000   | 0            | 0             |
| end                  | 0.000008 | 0.000000 | 0.000000   | 0            | 0             |
| query end            | 0.000008 | 0.000000 | 0.000000   | 0            | 0             |
| closing tables       | 0.000009 | 0.000000 | 0.000000   | 0            | 0             |
| freeing items        | 0.000023 | 0.000000 | 0.000000   | 0            | 0             |
| cleaning up          | 0.000007 | 0.000000 | 0.000000   | 0            | 0             |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

此时查询耗时0.00097000秒,sending data耗时0.000735秒。可以看出ICP 开启时整个sql执行时间是未开启的2/3,sending data环节的时间消耗前者仅是后者的1/4。


开启ICP时:
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows | Extra                 |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
| 1  | SIMPLE      | employees | ref  | idx_emp_fnln  | idx_emp_fnln | 44      | const | 224  | Using index condition |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

关闭ICP时:
mysql> explain select * from employees where first_name='Anneke' and last_name like '%nta' ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
| 1  | SIMPLE      | employees | ref  | idx_emp_fnln  | idx_emp_fnln | 44      | const | 224  | Using where |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

ICP 开启时的执行计划 含有 Using index condition 标示 ,表示优化器使用了ICP对数据访问进行优化。ICP 关闭时的执行计划显示use where。

以上面的查询为例关闭ICP 时,存储引擎通前缀index first_name 访问表中225条first_name 为Anneke的数据,并在MySQL server层根据last_name like '%sig' 进行过滤
开启ICP 时,last_name 的like '%nta'条件可以通过索引字段last_name进行过滤,在存储引擎内部通过与where条件的对比,直接过滤掉不符合条件的数据。该过程不回表,只访问符合条件的1条记录并返回给MySQL Server ,有效的减少了io访问和各层之间的交互。


参考文章:《MySQL Index Condition Pushdown(ICP)性能优化方法实例》、《MySQL5.6之Index Condition Pushdown(ICP,索引条件下推)

没有更多推荐了,返回首页