索引下推介绍

太过爱你忘了你带给我的痛 2024-03-24 18:28 69阅读 0赞

#### [索引下推][Link 1] ####

*  介绍
 *  作用
 *  MySQL5.6之前
 *  MySQL5.6及以上版本
 *  举例说明
 *   *  该语句的具体执行
     *   *  MySQL 5.6之前
         *  MySQL 5.6之后
     *  判断方法
     *  总结

## 介绍 ##

索引条件下推，也叫索引下推，英文全称‘Index Condition Pushdown’, 简称[ICP][]。

## 作用 ##

索引下推是MySQL5.6新添加的特性，用于优化数据的查询。

## MySQL5.6之前 ##

通过使用非主键索引进行查询的时候，[存储引擎][Link 2]通过索引查询数据。

然后将结果返回给MySQL server层，在server层判断是否符合条件。

## MySQL5.6及以上版本 ##

可以使用索引下推的特性。

当存在索引的列作为判断条件时，MySQL server将这一部分判断条件传递给存储引擎。

然后存储引擎会筛选出符合MySQL server传递条件的索引项。

即在存储引擎层根据索引条件过滤掉不符合条件的索引项，然后回表查询得到结果，将结果返回给MySQL server。

有了索引下推的优化，在满足一定的条件下，存储引擎层会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。

## 举例说明 ##

假设有一张用户信息表user\_info

表有三个字段name, level, weapon（装备），建立联合索引`(name, level)`, 表初始数据如图：  
![在这里插入图片描述][40b2946d22c64be0b13d61c0a216040c.png]  
假如需要匹配姓名第一个字为"大“，并且level为1的用户，SQL语句为：

SELECT * FROM user_info WHERE name LIKE "大%" AND level = 1;

### 该语句的具体执行 ###

#### MySQL 5.6之前 ####

没有索引下推，其执行过程如图：  
![在这里插入图片描述][11047dfc78984ac5aa869d3f27dc0d32.png]  
查询条件name“大%”不是等值匹配。

根据最左匹配原则，在(name, level)索引树上只用到name去匹配，查找到两条记录(id为1和4)。

拿到这两条记录的id分别回表查询，然后将结果返回给MySQL server。

在MySQL server层进行level = 1字段的判断。

#### MySQL 5.6之后 ####

MySQL5.6及以上版本的执行过程如下：  
![在这里插入图片描述][7c70acf0bcaa4f33855001169cb84a38.png]  
相比5.6以前的版本，多了索引下推的优化。

在索引遍历过程中，对索引中的字段先做判断，过滤掉不符合条件的索引项。

也就是判断level是否等于1，level不为1则直接跳过。

因此在（name，level）索引树只匹配一个记录，之后拿着此记录对应的id（id=1）回表查询全部数据。

整个过程回表1次。

### 判断方法 ###

可以使用explain查看是否使用了索引下推。

当explain输出的Extra列的值为Using Index condition，则标识使用了索引下推。

### 总结 ###

从上面的列子可以看出，使用索引下推在某些场景下可以有效减少回表次数，从而提高查询效率。

**参考资料**：[什么是索引下推？][Link 3]

[Link 1]: https://so.csdn.net/so/search?q=%E7%B4%A2%E5%BC%95%E4%B8%8B%E6%8E%A8&spm=1001.2101.3001.7020
[ICP]: https://so.csdn.net/so/search?q=ICP&spm=1001.2101.3001.7020
[Link 2]: https://so.csdn.net/so/search?q=%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E&spm=1001.2101.3001.7020
[40b2946d22c64be0b13d61c0a216040c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/22512dde423647b0bed014a4e04db7ae.png
[11047dfc78984ac5aa869d3f27dc0d32.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/fabae709fd8248cca264e3703ac06411.png
[7c70acf0bcaa4f33855001169cb84a38.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/13/cc106ebe33da4feeb5e9ac876f25127d.png
[Link 3]: https://www.bilibili.com/video/BV1VL4y1V7XE/?spm_id_from=333.880.my_history.page.click&vd_source=a1af903e57688df870625021f7f3a7eb