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=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 index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)