有了order by 反而加快了sql的执行--解释? - 51ste.com
登录|注册

有了order by 反而加快了sql的执行--解释?

作者:fisher  发布时间:2019-07-03 22:23:39  关键字:order by性能 

通过一个简单的案例,说明一下最优的执行计划应该是选择where条件上的 列的索引,或者应该选择 以 order by 字段上的索引?

如下图

有了order by 反而加快了sql的执行

这样的一个简单sql, 两个表通过健值关联,where条件中除users.active=1,除此之外,再无其他有传值(=某个特定值)的条件 ,最后, 对出来的结果用countintergal字段以倒序排序。

分析执行计划:通过active列上的索引进行第一次查找 ,然后通过主键关联,去找另外一个表上的记录。视乎的确完全合理。因为where中只有active 字段有值。

实际上,这个sql的效率并不高,执行时间花掉了0.46sec. 原因是active列上等于1的值比较多,这个sql需要扫描几万条记录。

如果没有后面的order by 语句, 上面的sql确实无法再进行优化,对于users表,要么走active字段的索引进行查找, 要么就全表扫描,只有这两种结果。

一般来说,同样的sql语句,如where条件后还接有order by 的语句, 一般要比没有接order by 的语句要慢, 因为多了一个排序的操作。但在此sql中,反而我们可以恰恰利用order by字段进行快速查找,迅速找出需要的数据。

因为有基于countintergal字段排序,为了避免排序,我们在排序字段上建立一个索引。如下图

有了order by 反而加快了sql的执行

然后我们再来分析与执行sql,居然发现执行计划没有任何改变,执行效率也么有变。-----不合符预期,索引白建了,why?

有了order by 反而加快了sql的执行

为什么没有使用到新建立countintergal列上的索引,而是依然使用了active 字段上的索引? 这个就是mysql优化器的原因了,它“傻傻”地认为通过active字段上的索引进行查找应该是最优的,但实际上不是。

前面我们已经分析过,active 字段上的索引效率不高,同时,为了避免干扰了上述sql的执行计划, 所以决定删除active 列上的索引。

有了order by 反而加快了sql的执行

如上图,删除掉索引之后,再来看执行计划,已经使用到了countintergal列上的索引,然后将sql执行,飕飕地出来结果。由之前的执行时间0.46s,瞬间变成0.01s.

执行效率变高的原因因为结果是countintergal列倒序排序,且只取结果的前10行数据。所以,“我们”(实际上指mysql)可以通过从countintergal列的索引上取到countintergal值最大的记录,然后通过跟users关联获得一条关联后的结果,如果该条结果满足users.active的值等于1,则我们就获得了第一条记录。 如果不满足users.active等于1,则该条舍弃。 然后再从countintergal列的索引上取值第二大的,依次类推。最好的情况下,需要在countintergal列的索引查找10次,就完成了查找。所以效率比之前大大提高。

源自微信公众号 数据库随笔 

本文由51ste.com网友编辑,未经授权,不得转载使用上述作品盈利;个人转载,需标明原文作者及出处。

更多相关文章

说说看法查看全部评论(0)

微信二维码

部落微信公众号

QQ二维码

部落QQ群二维码