MySQL 5.7与8.0使用GROUP BY及OFFSET LIMIT的结果差异疑问

问题背景 #

以下SQL查询在调整OFFSET进行分页时，MySQL 5.7和8.0返回结果存在差异：

SELECT 
  users.partner_id, 
  users_summaries.total_count,
  users.last_working_date
FROM 
  `users` 
  LEFT OUTER JOIN (
    SELECT 
      user_loc_summaries.partner_id, 
      user_loc_summaries.loc_id, 
      SUM(user_loc_summaries.total_count) AS total_count 
    FROM 
      user_loc_summaries 
    WHERE 
      user_loc_summaries.loc_id = '2222' 
    GROUP BY
      user_loc_summaries.partner_id
  ) AS users_summaries ON users_summaries.partner_id = users.partner_id 
WHERE 
  `users`.`loc_id` IN (2222, 666, 666) -- 原语句开头多了无效的AND，已修正
GROUP BY 
  users.partner_id 
ORDER BY 
  (users.loc_id = '2222') DESC, 
  users.loc_id ASC, 
  users.last_working_date DESC 
LIMIT 
  10 OFFSET 0

已知情况：

• last_working_date字段允许为空，数据库中该字段几乎全为NULL
• MySQL 5.7中调整OFFSET分页，结果无重复、无重叠
• MySQL 8.0中调整OFFSET分页，结果会与之前页面重叠

疑问：已知GROUP BY未搭配稳定ORDER BY可能引发分页问题，但为何MySQL 5.7能返回“正常”结果？

核心原因分析 #

1. 排序键不唯一导致的不稳定排序 #

你的ORDER BY子句最后一个字段last_working_date几乎全为NULL，这使得排序键组合不唯一。当大量记录拥有相同的排序键时，MySQL无法保证这些记录的相对顺序在每次查询中保持一致——这个顺序取决于存储引擎的物理读取顺序、查询优化器生成的执行计划，属于未定义行为。

2. 5.7与8.0的GROUP BY隐式排序差异 #

• MySQL 5.7默认会对GROUP BY的分组字段进行隐式排序，这意味着GROUP BY后的结果本身带有固定的顺序，间接让ORDER BY后的最终结果相对稳定，恰好避开了分页重叠的问题。但这是旧版本的默认行为，并非官方承诺的稳定特性。
• MySQL 8.0默认关闭了GROUP BY的隐式排序（优化器参数调整），GROUP BY后的结果是无序的。当ORDER BY的键不唯一时，最终结果的顺序更容易出现波动，导致分页时记录顺序打乱，出现重叠。

3. 查询优化器的执行计划变化 #

MySQL 8.0对查询优化器做了大量升级，包括GROUP BY的处理逻辑、排序阶段的算法优化等。这些变化使得相同查询的执行路径可能与5.7不同，进一步加剧了非唯一排序键下的顺序不确定性。

解决方案 #

要彻底解决分页重叠问题，必须让ORDER BY的排序键绝对唯一，具体操作：

• 在ORDER BY末尾添加唯一标识字段（比如用户表的主键users.id），确保即使前面的排序键相同，最后也能通过唯一键固定顺序：

  ORDER BY 
    (users.loc_id = '2222') DESC, 
    users.loc_id ASC, 
    users.last_working_date DESC,
    users.id ASC -- 添加唯一主键作为最终排序依据
  

• 修正原查询的语法错误：原WHERE子句开头多了一个AND，会导致查询无法执行，需删除。

内容的提问来源于stack exchange，提问作者Nguyen Ruby