欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL 查询优化之 Index Condition Pushdown

程序员文章站 2024-03-17 18:37:04
...

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步,直到最后一行数据。
    MySQL 查询优化之 Index Condition Pushdown

2)使用ICP时,如何进行索引扫描

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

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

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

  • (4)server层评估没有被下推到storage engine层的where条件,如果该行数据满足where条件则使用,否则丢弃。
    MySQL 查询优化之 Index Condition Pushdown

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)