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)
  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
引用\[1\]:从执行计划看到,type=index,Extra=Using where; Using index,索引全扫描,但是需要的数据都在索引列中能找到,不需要回表。利用这个特点,将原始的SQL语句先获取主键id,然后通过id跟原表进行关联,分析其执行计划。引用\[2\]:MySQL 5.6开始支持ICP(Index Condition Pushdown),不支持ICP之前,当进行索引查询时,首先根据索引来查找数据,然后再根据where条件来过滤,扫描了大量不必要的数据,增加了数据库IO操作。引用\[3\]:使用的表结构 CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(32) DEFAULT NULL COMMENT '姓名', `city` varchar(32) DEFAULT NULL COMMENT '城市', `age` int(11) DEFAULT NULL COMMENT '年龄', PRIMARY KEY (`id`), KEY `idx_name_city` (`name`,`city`), KEY `idx_city` (`city`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8; insert into user(name, city, age) values("ZhaoDa", "BeiJing", 20),("QianEr", "ShangHai", 21),("SunSan", "GuanZhou", 22), ("LiSi", "ShenZhen", 24), ("ZhouWu", "NingBo", 25), ("WuLiu", "HangZhou", 26), ("ZhengQi", "NanNing", 27), ("WangBa", "YinChuan", 28), ("LiSi", "TianJin", 29), ("ZhangSan", "NanJing", 30), ("CuiShi", "ZhengZhou", 65), ("LiSi", "KunMing", 29), ("LiSi", "ZhengZhou", 30);。 问题: mysql 模糊查询、 回答: 在MySQL中进行模糊查询可以使用LIKE操作符。例如,如果要在名字列中查找包含字母"h"的记录,可以使用以下语句:SELECT * FROM user WHERE name LIKE '%h%'; 这将返回名字列中包含字母"h"的所有记录。在这个例子中,我们使用了%作为通配符,表示可以匹配任意字符。所以,'%h%'表示匹配包含字母"h"的任意字符的字符串。请注意,模糊查询可能会导致性能问题,特别是在大型数据集上。为了提高查询性能,可以考虑使用索引来加速模糊查询。在给定的表结构中,可以使用idx_name_city索引来加速对name列的模糊查询。例如,可以使用以下语句进行模糊查询:SELECT * FROM user WHERE name LIKE '%h%' AND city = 'Beijing'; 这将返回名字列中包含字母"h"并且城市为"Beijing"的所有记录。通过使用索引,可以减少不必要的数据扫描,提高查询性能。 #### 引用[.reference_title] - *1* *2* *3* [MySQL模糊查询详解](https://blog.csdn.net/weixin_46058921/article/details/125346940)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值