MySQL索引下推

最新推荐文章于 2024-06-05 16:05:06 发布

开水烫蛤蟆

最新推荐文章于 2024-06-05 16:05:06 发布

阅读量423

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/jiayoubaobei2/article/details/128459067

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

文章借鉴：

MySQL中的索引下推_猪粉肠的博客-CSDN博客

概述

索引下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

作用

索引下推是数据库检索数据过程中为减少回表次数而做的优化

为什么产生索引下推

例如，执行下面一条SQL语句：复合索引为name_age(name,age)

select * from usertest where name like 'a%' and age = 10;

在Mysql5.6之前的执行流程是这样的：

1.根据最左前缀原则，执行name like 'a%'可以快速检索出id的值为1，5。

2.然后根据id的值进行回表操作，再次进行过滤age=10的数据。

查询id=1回表1次，id=5回表1次，这个过程总共回表了2次。

可能到这里都会有疑问：

为什么不在索引里面直接过滤age=10的数据，因为复合索引里面也存了age的数据，这样明明可以减少回表1次。恭喜啦，Mysql5.6以后就这么做了，这就是索引下推。

结论：这样可以看出，索引下推具体是在复合索引的查询中，针对特定的过滤条件而进行减少回表次数而做的优化(个人觉得本来就该这么设计的)。

工作过程：

EXPLAN分析
当使用explan进行分析时，如果使用了索引条件下推，Extra会显示Using index condition。并不是Using index因为并不能确定利用索引条件下推查询出的数据就是符合要求的数据，还需要通过其他的查询条件来判断。

图一：不使用ICP技术（过程使用数字符号标示，如①②③等）

过程解释：

①：MySQL Server发出读取数据的命令，这是在执行器中执行如下代码段，通过函数指针和handle接口调用存储引擎的索引读或全表表读。此处进行的是索引读。

if (in_first_read)
{
in_first_read= false;
error= (*qep_tab->read_first_record)(qep_tab); //设定合适的读取函数，如设定索引读函数/全表扫描函数
}
else
error= info->read_record(info);

②、③：进入存储引擎，读取索引树，在索引树上查找，把满足条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要进行进行步骤④，通常有IO。
⑥：从存储引擎返回查找到的多条元组给MySQL Server，MySQL Server在⑦得到较多的元组。
⑦--⑧：⑦到⑧依据WHERE子句条件进行过滤，得到满足条件的元组。注意在MySQL Server层得到较多元组，然后才过滤，最终得到的是少量的、符合条件的元组。

图二：使用ICP技术（过程使用数字符号标示，如①②③等）

过程解释：

①：MySQL Server发出读取数据的命令，过程同图一。
②、③：进入存储引擎，读取索引树，在索引树上查找，把满足已经下推的条件的（经过查找，红色的满足）从表记录中读出（步骤④，通常有IO），从存储引擎返回⑤标识的结果。

此处，不仅要在索引行进行索引读取（通常是内存中，速度快。步骤③），还要在③这个阶段依据下推的条件进行进行判断，不满足条件的，不去读取表中的数据，直接在索引树上进行下一个索引项的判断，直到有满足条件的，才进行步骤④，这样，较没有ICP的方式，IO量减少。(通过过滤也相当于减少数据量，也是减少io了)
⑥：从存储引擎返回查找到的少量元组给MySQL Server，MySQL Server在⑦得到少量的元组。因此比较图一无ICP的方式，返回给MySQL Server层的即是少量的、符合条件的元组。