MySQL查询时，ORDER BY 后面根据时间(TIMESTAMP)排序并做翻页(LIMIT)处理经常出现顺序紊乱和导致前后页的数据重复问题调查及解决

最新推荐文章于 2023-08-12 10:13:47 发布

Vicolen.Hao

最新推荐文章于 2023-08-12 10:13:47 发布

阅读量1.2w

点赞数 7

分类专栏： MySQL 文章标签： MySQL关键字

本文链接：https://blog.csdn.net/qq_30146831/article/details/101080831

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

ORDER BY后面谨慎使用按照时间排序

问题展示
修正结果
原因详查
个人建议
官方文档

问题展示

在执行获取评论列表、留言列表等诸多操作时，都需要将从数据库中查出的结果按照时间做倒排处理和翻页处理。同一时间点（例如1s内），若包含的结果数量过多，将不可避免的出现前后页结果重复的问题。
例如：执行下面sql，其中id为主键，gmt_create为创建时间。

SELECT
	id,
	gmt_create 
FROM
	r_cloud_disk 
ORDER BY
	gmt_create desc
	LIMIT 7000,
	1000

结果如下：
在这里插入图片描述
很显然，在时间相等时（同一秒内），id是混乱的（id混乱，整句也一定是混乱的，此处为简便，并未查出全部内容），并不完全是倒序的。

修正结果

sql语句如下：

SELECT
	id,
	gmt_create 
FROM
	r_cloud_disk 
ORDER BY
	gmt_create DESC,
	id DESC --只是加了一个按照id倒排
	LIMIT 7000,
	1000

结果如下：
在这里插入图片描述
查出的结果与正常顺序完全一致，很神奇。

原因详查

这个问题一般需要满足两个条件，一是要根据大量具有相同值的字段排序，一是需要使用到LIMIT关键字。
ORDER BY 的排序顺序对于无序列是非确定性的。

个人建议

当需要根据记录的创建时间排序时，不妨直接使用id排序，两者具有同等的效果。
如需要根据记录的更新时间排序，不妨先按照更新时间排序，再按照id排序。
所有的数据表一定要有一个主键，因为即使你不显示的创建主键，MySQL会判断表中是否有非NULL的整型唯一索引,如果有,则该列为主键，没有的话则会自动创建一个6字节的主键（很容易撑爆数据库）。

官方文档

MySQL官方对ORDER BY 和LIMIT的详细解说和演示

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

译：如果ORDER BY 行后面具有相同的排序值，服务器可以自由地以任何顺序返回这些行，并且可能根据总体执行计划以不同的方式返回。换句话说，这些行的排序顺序是无序列的、不确定的。

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:

译：影响执行计划的一个因素是LIMIT，因此ORDER BY查询可能会返回不同排序行。考虑这个查询，它按类别列排序，但id列和评级列不确定:

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

Including LIMIT may affect order of rows within each category value. For example, this is a valid query result:

译：包含LIMIT可能会影响每个分类值中的行顺序。例如，这是一个有效的查询结果:

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

In each case, the rows are sorted by the ORDER BY column, which is all that is required by the SQL standard.

译：在每种情况下，行按列的顺序排序，是SQL标准所要求的全部内容

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

译：如果确保在没有LIMIT时，相同行的顺序非常重要，则在ORDER BY子句中包含附加列，使顺序具有确定性。例如，如果id值是惟一的，则可以通过这样的排序使给定类别值的行按id顺序出现

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+