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

原创
03/17 18:15
阅读数 222

 对于长期与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想白嫖我服务器资源?

 

        通过参考李海翔老师的流程图,我们可以看出,没有索引下推的情况下,第三步从索引树上取到 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,表示已经使用了索引下推。

 

附、一张有故事的照片(十五)

 

吉林的一位母亲因癌症晚期担心去世后照顾不了孩子在她生命的最后一段时间里给儿子织完了25岁之前需要的所有毛裤
展开阅读全文
打赏
0
0 收藏
分享
加载中
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部