MySQL回表和索引下推

左手的ㄟ右手 2023-09-27 21:34 16阅读 0赞

## 回表 覆盖索引 索引下推 ##

MySQL回表和索引下推是两个与索引优化相关的概念：

*  **回表**：当使用非聚簇索引（辅助索引）查询数据时，如果需要获取除索引列以外的其他列的值，就需要通过索引列的值回到主键索引（聚簇索引）中再次查找，这个过程就叫做回表。³⁴
 *  **覆盖索引**：如果查询的列都被包含在非聚簇索引中，就不需要回表，这种索引就叫做覆盖索引。覆盖索引可以减少IO次数，提高查询效率。³⁴
 *  **索引下推**：当使用非聚簇索引查询数据时，如果有其他的过滤条件，就可以在非聚簇索引上先进行过滤，再回表，这样可以减少回表的次数，这个优化技术就叫做索引下推。MySQL 5.6及以上版本支持索引下推。³⁵

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

![在这里插入图片描述][d27eaac937704753bc9f10e773234abd.png]

索引下推是索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。

索引下推是把本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表。

举例说明：

首先使用联合索引（name，age），现在有这样一个查询语句：

select *  from t_user where name like 'L%' and age = 17;

这条语句从最左匹配原则上来说是不符合的，原因在于只有name用的索引，但是age并没有用到。

不用索引下推的执行过程：

第一步：利用索引找出name带'L'的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步：再根据这四条索引数据中的 id 值，逐一进行回表扫描，从聚簇索引中找到相应的行数据，将找到的行数据返回给 server 层。
    第三步：在server层判断age = 17,进行筛选，最终只留下 Lucy 用户的数据信息。

使用索引下推的执行过程：

第一步：利用索引找出name带'L'的数据行：LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步：根据 age = 17 这个条件，对四条索引数据进行判断筛选，最终只留下 Lucy 用户的数据信息。
    （注意：这一步不是直接进行回表操作，而是根据 age = 17 这个条件，对四条索引数据进行判断筛选）
    第三步：将符合条件的索引对应的 id 进行回表扫描，最终将找到的行数据返回给 server 层。

比较二者的第二步我们发现，索引下推的方式极大的减少了回表次数。

索引下推需要注意的情况：

下推的前提是索引中有 age 列信息，如果是其它条件，如 gender = 0，这个即使下推下来也没用

开启索引下推：

索引下推是 MySQL 5.6 及以上版本上推出的，用于对查询进行优化。默认情况下，索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

set optimizer_switch='index_condition_pushdown=off';  -- 关闭索引下推
    set optimizer_switch='index_condition_pushdown=on';  -- 开启索引下推

![在这里插入图片描述][98b91ed9f12142858aa02d92b7b90382.png]

## 主键索引和聚簇索引 ##

主键索引和聚簇索引是两个与MySQL表结构相关的概念：

*  **主键索引**：是一种特殊的唯一索引，用于标识表中的每一行数据，不能有重复值或空值。¹²
 *  **聚簇索引**：是一种将数据行存储在索引中的索引，也就是说，索引和数据是一体的，按照索引的顺序存储。¹²

在MySQL的InnoDB存储引擎中，主键索引就是聚簇索引，也就是说，表中的数据按照主键的顺序存储。¹²⁴

如果没有定义主键，InnoDB会选择一个唯一非空索引作为聚簇索引；如果没有这样的索引，InnoDB会生成一个隐藏的行ID作为聚簇索引。¹²

聚簇索引有利于提高主键查询和范围查询的速度，但也会影响插入和更新操作的性能。⁴

## 开启索引下推 ##

![在这里插入图片描述][3e358bbb23224e919c9f8fd6b812f116.png]

[d27eaac937704753bc9f10e773234abd.png]: https://img-blog.csdnimg.cn/d27eaac937704753bc9f10e773234abd.png
[98b91ed9f12142858aa02d92b7b90382.png]: https://img-blog.csdnimg.cn/98b91ed9f12142858aa02d92b7b90382.png
[3e358bbb23224e919c9f8fd6b812f116.png]: https://img-blog.csdnimg.cn/3e358bbb23224e919c9f8fd6b812f116.png