MySQL Index Condition Pushdown

最新推荐文章于 2023-12-27 15:42:43 发布

weixin_34379433

最新推荐文章于 2023-12-27 15:42:43 发布

阅读量101

点赞数

原文链接：https://my.oschina.net/u/3023401/blog/1535362

版权

2019独角兽企业重金招聘Python工程师标准>>>

一、Index Condition Pushdown简介

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开启和关闭时数据访问和提取过程对比

优化器没有使用ICP时，数据访问和提取的过程如下：

1）：MySQL Server发出读取数据的命令，这是在执行器中执行如下代码段，通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。

if (in_first_read)
    {
      in_first_read= false;
      error= (*qep_tab->read_first_record)(qep_tab); //设定合适的读取函数，如设定索引读函数/全表扫描函数
    }
    else
      error= info->read_record(info);

2、3）：进入存储引擎，读取索引树，在索引树上查找，把满足条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要进行进行步骤④，通常有IO。

6）：从存储引擎返回查找到的多条元组给MySQL Server，MySQL Server在⑦得到较多的元组。

7、8）：⑦到⑧依据WHERE子句条件进行过滤，得到满足条件的元组。注意在MySQL Server层得到较多元组，然后才过滤，最终得到的是少量的、符合条件的元组。

优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层。

数据访问和提取过程如下：

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

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

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

4) server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃。

三、ICP测试

3.1 对比执行计划的差别

联合索引的第一个条件可以使用索引，第二个不能使用索引

root@localhost:mysql.sock  15:33:47 [test]>explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40;

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

| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |

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

|  1 | SIMPLE      | person | NULL       | range | idx_p_a       | idx_p_a | 7       | NULL |    1 |    33.33 | Using index condition |

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

1 row in set, 1 warning (0.11 sec)

关闭ICP后

root@localhost:mysql.sock  15:35:42 [test]>set optimizer_switch = "index_condition_pushdown=off";

Query OK, 0 rows affected (0.00 sec)



root@localhost:mysql.sock  15:39:48 [test]>explain select  *   from person  where postadlcode between 300000 and 400000 and age > 40;

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

| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |

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

|  1 | SIMPLE      | person | NULL       | range | idx_p_a       | idx_p_a | 7       | NULL |    1 |    33.33 | Using where |

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

1 row in set, 1 warning (0.00 sec)

where条件包含索引字段但用不到索引

root@localhost:mysql.sock  15:39:49 [test]>explain select  *   from person  where age > 40;

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

| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

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

|  1 | SIMPLE      | person | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    33.33 | Using where |

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

1 row in set, 1 warning (0.00 sec)



root@localhost:mysql.sock  15:41:03 [test]>set optimizer_switch = "index_condition_pushdown=on";

Query OK, 0 rows affected (0.00 sec)



root@localhost:mysql.sock  15:41:09 [test]>explain select  *   from person  where age > 40;

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

| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

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

|  1 | SIMPLE      | person | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    33.33 | Using where |

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

1 row in set, 1 warning (0.01 sec)

结论：

需要index condition pushdown 的query通常索引的字段出现where子句里面都是范围查询。比如：

select * from tb where tb.key_part1 < x and tb.key_part2 = y       
select * from tb where tb.key_part1 = x andtb.key_part2 like '%yyyy%'
select * from tb where tb.key_part1 > x and tb.key_part1 < y and tb.key_part1 > xx and tb.key_part2 < yy

但是需要注意的是：
1. 如果索引的第一个字段的查询就是没有边界的比如 key_part1 like '%xxx%'，那么不要说ICP，就连索引都会没法利用。
2. 如果select的字段全部在索引里面，那么就是直接的index scan了，没有必要什么ICP。