java八股文面试[数据库]——索引下推

谁践踏了优雅 2024-03-08 08:02 122阅读 0赞

###### 什么是索引下推？ ######

索引下推（index **condition pushdown** ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

需求: 查询users表中 "名字第一个字是张，年龄为10岁的所有记录"。

SELECT \* FROM users WHERE user\_name LIKE '张%' AND user\_age = 10;

根据**最左前缀法则**，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录，接下来是怎么处理的呢？当然就是从该记录开始，逐个回表，到主键索引上找出相应的记录，再比对 \`age\` 这个字段的值是否符合。

图1: 在 (name,age) 索引里面**特意去掉了** age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次

![image.png][]

MySQL **5.6**引入了索引下推优化，可以在索引遍历过程中，**对索引中包含的字段先做判断**，**过滤掉**不符合条件的记录，**减少回表次数**。

图2: InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过,减少回表次数.

![image.png][image.png 1]

总结

如果没有索引下推优化（或称ICP优化），当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录；

在支持**ICP**优化后，MySQL会在**取出索引的同时**，**判断**是否可以进行where条件过滤再进行索引查询，也就是说提前执行where的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

###### 索引下推优化的原理 ######

我们先简单了解一下MySQL大概的架构：

![e1ccd5edbc38bdf2a6eb27c3230a2b72.jpeg][]

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

`索引下推`的**下推**其实就是指将**部分上层**（服务层）负责的事情，交给了**下层**（**引擎层**）去处理。

我们来具体看一下，在**没有使用**ICP的情况下，MySQL的查询：

*  存储引擎读取索引记录；
 *  根据索引中的主键值，定位并读取完整的行记录；
 *  存储引擎把记录交给**`Server`层去检测**该记录是否满足`WHERE`条件。

使用ICP的情况下，查询过程：

*  存储引擎读取索引记录（**不是完整的行记录**）；
 *  判断条件部分**能否用索引中的列来做检查**，条件不满足，则处理下一行索引记录；
 *  条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
 *  存储引擎把记录交给层，层检测该记录是否满足条件的**其余部分**。

#### 索引下推使用条件 ####

*  只能用于**`range`、 `ref`、 `eq_ref`、`ref_or_null`**访问方法；
 *  只能用于**`InnoDB`**和 **`MyISAM`**存储引擎及其分区表；
 *  对存储引擎来说，索引下推只适用于**二级索引（**也叫**辅助索引**）;

索引下推的目的是为了**减少回表次数**，也就是要减少IO操作。对于的**聚簇索引**来说，数据和索引是在一起的，**不存在回表这一说。**

*  引用了**子查询的条件**不能下推；
 *  引用了**存储函数的条件**不能下推，因为存储引擎**无法调用存储函数**。

知识来源：马士兵教育

[百度安全验证][Link 1]

[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/dafabce4bb704b9fb4b0f888462d7b4c.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/b388b0abcdbc48b1ba07101ba5b2c27b.png
[e1ccd5edbc38bdf2a6eb27c3230a2b72.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/08/ab853a6443794a3eb457026203a15eea.png
[Link 1]: https://baijiahao.baidu.com/s?id=1716515482593299829&wfr=spider&for=pc