5分钟搞懂MySQL - 索引下推优化

原创

山一程水一更 2022-02-10 10:52:54 博主文章分类：MySQL江湖路 ©著作权

文章标签 数据库 mysql 索引下推索引下推优化数据 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者山一程水一更的原创作品，请联系作者获取转载授权，否则将追究法律责任

对于长期与MySQL同流合污的朋友们来说，或许，“索引下推优化”这个词并不陌生，嗯。。经常听到，但是MySQL的这个“优化”到底优化了啥？就懵懵懂懂了，反正不是公司优化我就行了是吧。。来，让我们继续快乐的卷下去~

5分钟搞懂MySQL - 索引下推优化_mysql

其实呢，这个索引下推优化起源于MySQL5.6版本，全名叫：“索引条件下推”，英文名字 Index Condition Pushdown，我们叫他 ICP吧，ICP的诞生主要是为了进一步提高B+Tree索引查询的可用性。

它的作用通俗一些的话，我们举个栗子。

如下，在表 T 创建联合索引 index(name,age)

select * from T where name like '提莫%' and age = 100；

MySQL没有索引下推优化时：

对于联合索引index(name,age)，我们知道，根据B+Tree天然有序的存储特性，LIKE + 右侧模糊匹配虽可以使用到name索引，但模糊匹配后得到的结果变成无序，所以后面条件无法再使用到索引，因此需回表提取出name like '提莫%'结果集后，再通过普通查询得到age = 100的最终结果。

那么 age 字段的索引就这么浪费了么？MySQL想白嫖我服务器资源？

5分钟搞懂MySQL - 索引下推优化_索引下推_02

通过参考李海翔老师的流程图，我们可以看出，没有索引下推的情况下，第三步从索引树上取到 name like ‘提莫%’ 数据后，就回表了，后续再处理where剩下的条件，剩下的条件在我们这里也就是 age = 100；相当于多了一次查询过滤操作。

机灵的小伙伴发现，索引字段换个顺序不就得了？聪明~ 这确实是5.6之前的一种处理方式，但缺点也很明显，比如字段过滤数据能力降低，没了免费午餐（排序）等~

MySQL引入了ICP优化后；

如图，在索引内部取到name结果之后（步骤3），步骤4就顺便判断了结果中的age是否等于100，对于不等于100的记录直接跳过，因此在index(name,age)这棵索引树中直接匹配到了结果记录，减少了完整查询记录（一条完整元组）读取的个数，此时拿着结果集的id去主键索引树中回表查询全部数据，减少了二次查询时间，I/O次数也会减少。

Cool~

mysql> explain select name from T where name like '提莫%' and age = 100;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | T     | NULL       | range | index_name    | index_name | 72      | NULL |    3 |    25.00 | Using index condition    |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

当你在使用Explain解析SQL时，可以看出Extra的值为Using index condition，表示已经使用了索引下推。