Mysql OrderBy 乱序问题

doMore 126 2024-07-12

前言

问题

在执行各种列表等诸多操作时，都需要将从数据库中查出的结果按照时间（create_time） 做倒排处理和翻页处理。同一时间点（例如1s内），若包含的结果数量过多，将不可避免的出现前后页结果重复的问题。
例如：在一个 message 表中，有在同一时间有一部分消息的发送时间是相同的。

-- 第一页
select message_id, create_time  
from message  
where message_id in (  
                     14128,  
                     14127,  
                     14126,  
                     14125,  
                     14124,  
                     14123,  
                     14122,  
                     14121  
    )  
order by create_time limit 0,3  
;

-- 结果
-- 14127,2024-02-09 10:57:17
-- 14126,2024-02-09 10:57:17
-- 14125,2024-02-09 10:57:17

-- 第二页 结果
-- 14124,2024-02-09 10:57:17
-- 14125,2024-02-09 10:57:17
-- 14126,2024-02-09 10:57:17

修复

在原有的时间排序上增加 id 作为辅助排序。


select message_id, create_time  
from message  
where message_id in (  
                     14128,  
                     14127,  
                     14126,  
                     14125,  
                     14124,  
                     14123,  
                     14122,  
                     14121  
    )  
order by create_time, message_id limit 0,3  
;

-- 第一页
-- 14121,2024-02-09 10:57:17
-- 14122,2024-02-09 10:57:17
-- 14123,2024-02-09 10:57:17

-- 第二页
-- 14124,2024-02-09 10:57:17
-- 14125,2024-02-09 10:57:17
-- 14126,2024-02-09 10:57:17
  1. 这个问题一般需要满足两个条件，一是要根据大量具有相同值的字段排序，一是需要使用到LIMIT关键字。
  2. ORDER BY 的排序顺序对于无序列是非确定性的。

结论

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

官方文档

https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html

mysql