MySQL 结果排序不正确？在子查询中排序

Question

我发现有时，我的结果使用以下 SQL (MySQL) 查询排序不正确:

SELECT u.id, u.firstName, u.lastName, u.email, u.ssoProfile, u.vip, SUM(p.number) AS totalPoints 
FROM (
      SELECT u.id FROM user u 
      ORDER BY u.firstName ASC, u.lastName ASC, u.email ASC 
      LIMIT 100, 10
) u2 
INNER JOIN `user` u 
ON u2.id = u.id 
LEFT JOIN `point` p 
ON u.id = p.user 
AND p.expiryDate > NOW() 
GROUP BY u.id

如您所见，我在子查询中按用户排序。然而，我注意到有时(很少见)我得到的结果不遵循指定的顺序。我想我需要在外部查询上排序？但我想了解这个查询有什么问题。为什么我的用户订单困惑？

更新

我已将 order by 添加到外部查询中。令人惊讶的是，我发现我的结果仍然可以以错误的顺序返回

SELECT u.id, u.firstName, u.lastName, u.email, u.ssoProfile, u.vip, SUM(p.number) AS totalPoints 
FROM (
      SELECT u.id FROM user u 
      ORDER BY u.firstName ASC, u.lastName ASC, u.email ASC 
      LIMIT 100, 10
) u2 
INNER JOIN `user` u 
ON u2.id = u.id 
LEFT JOIN `point` p 
ON u.id = p.user 
AND p.expiryDate > NOW() 
GROUP BY u.id
ORDER BY u.firstName ASC, u.lastName ASC, u.email ASC

返回的结果如下:

[ { id: 4834, firstName: 'F01', lastName: 'L01' },
  { id: 4835, firstName: 'F00', lastName: 'L00' }, // << notice F00 is after F01?
  { id: 4836, firstName: 'F02', lastName: 'L02' },
  { id: 4837, firstName: 'F03', lastName: 'L03' },
  { id: 4838, firstName: 'F04', lastName: 'L04' },
  { id: 4839, firstName: 'F05', lastName: 'L05' },
  { id: 4840, firstName: 'F06', lastName: 'L06' },
  { id: 4841, firstName: 'F07', lastName: 'L07' },
  { id: 4842, firstName: 'F08', lastName: 'L08' },
  { id: 4843, firstName: 'F09', lastName: 'L09' } ]

@lad2025 提到这可能是因为我在评论中错误地使用了 GROUP BY。不过，我检查了 ID 是否匹配。例如，意味着 F00 确实属于用户 ID 4835。

Answer 1

首先，@lad2025和@Tim Biegeleisen都给出了正确的评论。查询优化器会更改查询，以便结果集保持不变，并且时间保持尽可能短。查询优化器会更改命令的顺序，例如 JOIN 和 WHERE 条件。

这些链接可以为您提供一些有关查询优化如何工作的有用信息( link1 和 link2 )。

在 MySQL 中，您可以使用 EXPLAIN命令来查看查询优化更改后实际查询的执行情况。

关于解决您的问题，您只需在外部查询中复制您的 ORDER BY ，因为这样您就告诉 RDBMS 您也希望对结果进行排序。

我的怀疑是查询重新排序(看看这个 -> heuristic optimisation )改变了一些东西，因为子查询中的表与外部查询中的表相同(我知道这不是最准确的解释)。这很难确定，因为它取决于您拥有的索引、执行时的内存大小、表的大小等等......

您的查询应如下所示:

SELECT u.id, u.firstName, u.lastName, u.email, u.ssoProfile, u.vip, SUM(p.number) AS totalPoints 
FROM (
      SELECT u.id FROM user u 
      ORDER BY u.firstName ASC, u.lastName ASC, u.email ASC 
      LIMIT 100, 10
) u2 
INNER JOIN `user` u 
ON u2.id = u.id 
LEFT JOIN `point` p 
ON u.id = p.user 
AND p.expiryDate > NOW() 
GROUP BY u.id
ORDER BY u.firstName ASC, u.lastName ASC, u.email ASC 

我要补充的一点是，您仍然应该在子查询中保留 ORDER BY，因为 LIMIT 条件是在 ORDER BY 之后执行的 因此，与原始查询相比，您最终会得到不同的结果。