Mysql where、order by和limit一起使用有什么影响

他叫你黄方块

已于 2022-11-25 13:51:08 修改

阅读量2.6k

点赞数 2

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

于 2022-11-25 13:46:26 首次发布

本文链接：https://blog.csdn.net/qq_39944052/article/details/128036572

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一.背景：

研发在业务上查询mysql的时候，发现同样的sql语句，where+order by+limit的执行顺序是不一样的。

还原现场sql：

sql1:select * from audience_funnel where source='walmart' order by up_time desc limit 200; -- 快
sql2:select * from audience_funnel_new where source='walmart'order by up_time desc limit 200; -- 慢
sql3:select * from audience_funnel_new where source='walmart'order by up_time desc limit 2000; -- 快

二.分析问题

1.为什么sql2和sql3的执行时间差这么多？
（知识补充：where和order by同时出现的时候，mysql只会去查where字段或者order by字段的索引，如果用where字段查索引的话，sql执行到order by阶段的时候，order by是不走索引的。）
这个问题首先是跟mysql的执行器相关的，当我们使用到limit的时候，mysql会去选择这个时候应该用where字段作为索引还是用order by字段作为索引，正常情况下sql是使用where的字段作为索引的，当limit小于某个阙值，sql会采用order by的字段来做索引。通过mysql的执行器，我们能看到sql2是查up_time的索引，sql3是查source的索引。那么为什么sql2查up_time的索引就要比sql3查source的索引要慢呢，下面分析sql2和sql3的执行过程。
sql2:受到limit200的影响，这行sql查up_time的索引，所以执行过程是先查up_time字段，对up_time字段作降序，然后依次遍历up_time字段，回到人群表中查找符合where条件的数据，直到查找到200条数据或者up_time遍历完成了之后这个查询才算完成，这个时候where条件中source索引是不生效的，因此做的是全表扫描。
sql3:虽然也用到了limit，但是mysql的优化器认为这个时候选用source还是最优解，因此执行过程是先查人群表满足where条件的数据，然后where过滤完的数据在根据up_time进行排序，这个时候因为up_time索引是不生效的，因此排序这个过程会走filesort，如果排序的数据很大，甚至会将数据放到磁盘做文件搜索。
结论：
·根据sql2可以知道，如果最终查询结果是小于200条的，那么这个sql将会去遍历所有的up_time，然后每个up_time去做全表扫描（GG），理所当然很慢。
·而根据sql3可以知道，如果where过滤出来的数据很小，即使它在order by阶段不查索引，那么执行起来也很快。
2.为什么sql1和sql2同样的查询条件，执行时间差这么多？
问题1的结论1其实已经回答了这个问题，符合条件的数据越早出现sql查的就越快，因此下面我们用对比实验来证明这个回答是对的。
论据：

1.select * from audience_funnel_new where source ='hyper' order by up_time desc limit 200; -- 快  在up_time最新日期里面是有hyper这个source的。
2.select * from audience_funnel_new where source ='hyper' order by up_time asc limit 200; -- 慢   在up_time比较早的日期里面是没有hyper这个source的。
3.select * from audience_funnel where source='walmart' order by up_time desc limit 200; -- 快    在up_time最新日期里面是有walmart这个source的。
4.select * from audience_funnel where source='walmart' order by up_time asc limit 200; -- 慢     在up_time最新日期里面是有walmart这个source的。

结论：
从1和2，3和4可以看出，如果在遍历up_time的时候，遍历到第一个up_time的值，就在人群表中能查到200条符合where条件的数据，那么这个sql就执行的很快；反之，如果遍历了up_time的很多个值才查得出符合where条件的数据，那么sql就执行的很慢。

三.探索：

1.在我们执行sql的时候，可以通过使用FORCE_INDEX来强制sql查询的时候去查那个字段作为索引。
案例：select * from audience_funnel_new FORCE INDEX (source) where source =‘’ order by up_time desc limit 200。
案例中的sql强制使用了source来作为本次sql执行要查询的索引，这个时候up_time索引会失效，source索引生效，因此是先执行where条件在执行order by条件，也是可以很快查出结果的，比sql2快非常多。但是这个关键字不能滥用，因为要用FORCE INDEX 这个取决于where结果有多少行，如果where的结果很多，那这个时候where结果再去做order by的话，是走文件搜索，特别慢。
2.既然FORCE INDEX不能滥用，那么有什么办法不用FORCE INDEX也能加快sql2的执行效率吗？
两个方案：
1）缩小查找范围再做order by。比如sql2，我们本次是要查人群表中，我们今天加了多少个source为‘walmart’的人群，因此可以改写成：
select * from audience_funnel_new where source =‘hyper’ and up_time>=‘2022-11-24’ order by up_time desc limit 200; – 这个时候是先过滤在排序
2）建立联合索引，对(source,up_time)建立联合索引，那么这个时候就会先去过滤在进行排序，当source索引生效的时候，up_time通常也能够生效，查询速度同样很快。
既然说到通常也能生效，那么up_time还是有可能失效的，比如说source和up_time违反了最左原则，亦或者是up_time字段的区分度比较低，那么up_time的索引在本次查询会失效，导致排序走的是filesort。