mysql 5.6 limit_mysql 5.6 order by Limit执行效率问题

最新推荐文章于 2024-04-15 14:00:39 发布

橘玄雅

最新推荐文章于 2024-04-15 14:00:39 发布

阅读量250

点赞数

文章标签： mysql 5.6 limit

本文链接：https://blog.csdn.net/weixin_35682224/article/details/113905265

版权

本文探讨了在MySQL 5.6.10版本中，使用`ORDER BY`和`LIMIT`结合时导致查询效率降低的问题。当`LIMIT`值增大时，查询影响的行数增加，效率显著下降。原因是执行顺序为`ORDER BY`->`LIMIT`->`WHERE`，而非预期的`WHERE`->`ORDER BY`->`LIMIT`。为优化性能，提出了使用子查询来提前限制行数的方法，从而大幅提高查询速度。

摘要由CSDN通过智能技术生成

今天研发的同事反馈一个sql执行140+s但是sql很简单，也有索引，那么问题出在哪里呢？

经过排查发现,mysql中，order by limit 一起用的时候是有问题的不是我们常用的思路，下面举例说明：

select tid, productname, pic, minorder, minorderunit from `f_product` where cid = 6234052 and `status`=1 order by repubtime desc limit 8

mysql> explain select tid, productname, pic, minorder, minorderunit from `f_product` where cid = 6234052 and `status`=1 order by repubtime desc limit 8\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: f_product

type: index

possible_keys: cid_status_endtime_repubtime,status_repubtime,status_endtime,status_tid,status_pubtime_tid

key: repubtime

key_len: 5

ref: NULL

rows: 9486

Extra: Using where

1 row in set (0.00 sec)

ERROR:

No query specified

不加Limit8 时：

mysql> explain select tid, productname, pic, minorder, minorderunit from `f_product` where cid = 6234052 and `status`=1 order by repubtime desc \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: f_product

type: ref

possible_keys: cid_status_endtime_repubtime,status_repubtime,status_endtime,status_tid,status_pubtime_tid

key: cid_status_endtime_repubtime

key_len: 6

ref: const,const

rows: 12950

Extra: Using where; Using filesort

1 row in set (0.00 sec)

ERROR:

No query specified

发现加limit，和不加走的索引不一样。测试当不加Limit时执行时间：

mysql> select tid, productname, pic, minorder, minorderunit from `f_product` where cid = 6234052 and `status`=1 order by repubtime desc;

7183 rows in set (0.03 sec)

mysql> select tid, productname, pic, minorder, minorderunit from `f_product` where cid = 6234052 and `status`=1 order by repubtime desc limit 8;

8 rows in set (0.68 sec)

发现效率慢了20倍。

而且发现：limit 后面的值越大，影响的行数越多，例如limit 1 ,limit 5, limit8影响的行数都是不一样的。

经线下测试发现：limit 偏移量越小，用Limit效率越高，limit 越大效率越差。

所以在这里我调整了sql:

mysql> select tid, productname, pic, minorder, minorderunit from (select tid, productname, pic, minorder, minorderunit from `f_product` where cid = 6234052 and `status`=1 order by repubtime desc ) c limit 8;

8 rows in set (0.04 sec) //执行时间缩小了17被。

同时也发现 :我们在order by limit 一起时执行顺序不是按照：where ------order by ------ limit

而是：order by ----- limit -------where的顺序去执行，这样就会有一个问题，按照我们管用的思路，上面的查询肯定是会丢失数据的。具体后面遇到再测试。

目前我们的数据库版本是mysql5.6.10不知道其他版本的如何，这可能是mysql5.6版本的一个优化设计。