MySQL 查询优化之 Index Condition Pushdown
MySQL 查询优化之 Index Condition Pushdown
Index Condition Pushdown （ICP）是MySQL使用索引从表中检索行数据的一种优化方式。
-
禁用ICP时，存储引擎会通过遍历索引定位基表中的行，然后返回给Server层，再去为这些数据行进行where条件的过滤。
-
启用ICP时，如果where条件可以使用索引，MySQL会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。 ICP可以减少存储引擎必须访问基表的次数以及Server曾必须访问存储引擎的次数。
1. Index Condition Pushdown限制条件
-
当需要访问全表时，ICP用于range，ref，eq_ref和ref_or_null访问类型。
-
ICP可用于InnoDB和MyISAM表，包括分区的InnoDB和MyISAM表。
-
对于InnoDB表，ICP仅用于辅助索引。ICP的目标是减少全行读取的数量，从而减少I/O操作。 对于InnoDB聚簇索引，完整记录已经读入InnoDB缓冲区。 在这种情况下使用ICP不会降低I/O.
-
在虚拟生成列上创建的辅助索引不支持ICP。 InnoDB支持虚拟生成列的辅助索引。
-
子查询的条件无法下推。
-
存储函数的条件无法下推。存储引擎无法调用存储的函数。
-
触发条件无法下推。
2. Index Condition Pushdown工作原理
1) 不使用ICP时，如何进行索引扫描
-
（1）当storage engine读取下一行时，首先读取索引元组（index tuple），然后使用索引元组在基表中（base table）定位和读取整行数据。
-
（2） sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。
-
（3）执行第1步，直到最后一行数据。
2）使用ICP时，如何进行索引扫描
-
（1）storage engine从索引中读取下一条索引元组。
-
（2） storage engine使用索引元组评估下推的索引条件。如果没有满足where条件，storage engine将会处理下一条索引元组（回到上一步）。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据。
-
（3）如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。
-
（4）server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃。
3. ICP的开启与关闭
默认情况下启用Index Condition Pushdown 。可以通过设置optimizer_switch系统变量来控制它：
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...'; SET optimizer_switch = 'index_condition_pushdown=off'; SET optimizer_switch = 'index_condition_pushdown=on';
用explain查看执行计划时，如果执行计划中的Extra信息为using index condition，表示优化器使用的index condition pushdown。
4. 使用ICP示例
表结构定义
CREATE TABLE `address` ( `address_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(50) NOT NULL, `address2` varchar(50) DEFAULT NULL, `district` varchar(20) NOT NULL, `city_id` smallint(5) unsigned NOT NULL, `postal_code` varchar(10) DEFAULT NULL, `phone` varchar(20) NOT NULL, `location` geometry NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`address_id`), KEY `idx_fk_city_id` (`city_id`), SPATIAL KEY `idx_location` (`location`), CONSTRAINT `fk_address_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=606 DEFAULT CHARSET=utf8;
关闭ICP优化，Extra信息为“Using Where”
mysql> set optimizer_switch = "index_condition_pushdown=off"; mysql> explain select * from sakila.address d where d.city_id > 500; +----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ | 1 | SIMPLE | d | NULL | range | idx_fk_city_id | idx_fk_city_id | 2 | NULL | 101 | 100.00 | Using where | +----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
开启ICP之后，Extra信息为“Using Index Condition”
mysql> set optimizer_switch = "index_condition_pushdown=on"; mysql> explain select * from sakila.address d where d.city_id > 500; +----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | d | NULL | range | idx_fk_city_id | idx_fk_city_id | 2 | NULL | 101 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
