日常问题：MySQL排序字段数据相同不能分页问题

【问题日期】

• 2022-11-14 22:45:12

【问题描述】

• MySQL 排序字段数据相同不能分页问题：在分页查询数据时，按创建时间排序，由于数据是批量创建的，导致部分数据创建时间一样，而此时分页查询数据，翻页后出现重复数据

【问题拆解】

• 分页查询数据
• 按照创建时间排序&存在创建时间相同的数据
• 翻页后出现重复数据

【问题来源】

• 朋友遇到的

【可能原因】

• 是因为排序字段只有创建时间

【参考链接】

• MySQL 官方文档：
  • https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html
  • 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，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回。换句话说，这些行的排序顺序对于无序的列是不确定的。
• 51CTO 博客 - 《MySQL排序字段数据相同不能分页问题》：
  1. https://blog.51cto.com/u_15704340/5439010
  2. 如果 order by 排序的字段有相同的数据，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回。

【解决方案】

• 可以在 order by 后面加上一个唯一的 id

【问题总结】

• 如果多行在列中具有相同的值 ORDER BY，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回。换句话说，这些行的排序顺序对于无序的列是不确定的。
• 对于带有ORDER BY 或 GROUP BY 和 LIMIT 子句的查询，优化器会在默认情况下尝试选择有序索引，因为这样做会加快查询执行速度。在 MySQL 5.7.33 之前，无法覆盖此行为，即使在使用其他优化可能更快的情况下也是如此。从 MySQL 5.7.33 开始，可以通过将 optimizer_switch系统变量的 prefer_ordering_index标志设置为off.