mysql中的索引下推(ICP)

索引下推(index condition pushdown)简称ICP,在Mysql5.6版本上推出,用于优化查询。索引下推的下推其实就是指将部分上层(服务层)负责的事情,交给了下层(引擎层)去处理

 

MySQL服务层负责SQL语法解析、生成执行计划等,并调用存储引擎层去执行数据的存储和检索。

其实就是把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断,这样能有效减少回表。因为MySQL的架构原因,分成了server层和引擎层,才有“下推”的说法。主要实现了index filter技术,将原来的在server层进行的table filter中可以进行index filter的部分,在引擎层面使用index filter进行处理,不再需要回表进行table filter。

查询是否开启:(索引条件下推默认是开启)

select @@optimizer_switch;

开启:

SET optimizer_switch = 'index_condition_pushdown=on';

适用条件:

1、只能用于辅助索引(非主键索引),不能用于聚集索引【对于InnoDB的聚簇索引来说,完整的行记录已经加载到缓存区了,且数据和索引是在一起的,不存在回表这一说,索引下推没意义】。
2、只用于单表,不是多表连接。
开启索引下推优化的情况下,Extra的值是Using index condition。

使用前后的成本差别

1、使用ICP前,存储层多返回了需要被index filter过滤掉的整行记录。

2、使用ICP后,直接就去掉了不满足index filter条件的记录,省去了回表和传递到server层的成本。

举例:

使用一张用户表tuser,表里创建联合索引(name, age)

 如果现在有一个需求:检索出表中名字第一个字是张,而且年龄是10岁的所有用户。SQL语句:

select * from tuser where name like '张%' and age=10;

如果了解索引最左匹配原则,那么就知道这个语句在搜索索引树的时候,只能用 “张”,找到的第一个满足条件的记录id为1。

没有使用ICP,在MySQL 5.6之前,存储引擎根据通过联合索引找到name likelike ‘张%’ 的主键id(1、4),逐一进行回表扫描,去聚簇索引找到完整的行记录,server层再对数据根据age=10进行筛选。

 

以上可以看到需要回表两次,把我们联合索引的另一个字段age浪费了。

使用ICP,MySQL 5.6 以后, 存储引擎根据(name,age)联合索引,由于联合索引中包含列,所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。

以上可以看到只回表了一次。

 

posted @   李若盛开  阅读(88)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
历史上的今天:
2022-09-13 FileCoin核心概念
2022-09-13 IPFS基本原理
2022-09-13 区块链入门知识
点击右上角即可分享
微信分享提示