MySQL 5.7升级到8.0后HAVING子句表现异常原因及高效解决方案问询

问题原因说明 #

这不是MySQL 8.0的Bug：

• MySQL从未对SELECT表达式内用户变量的赋值、取值执行顺序做出官方保证，5.7版本的执行逻辑刚好让你的查询按「先ORDER BY排序→再计算变量→最后HAVING过滤」的顺序执行，所以得到了预期结果。
• 8.0版本对优化器做了大量重构，默认会调整执行顺序提升执行效率，你的查询中HAVING过滤被提前到变量计算或排序之前执行，因此Counter<=1的条件不生效。同时官方已经明确将表达式内赋值用户变量的行为标记为废弃，后续版本会直接移除，不建议继续使用该写法。

高性能解决方案 #

你的表主键为(ExchNo, StkID, Date)，天然支持按索引高效查询，以下两种方案性能都和你原5.7版本的写法接近，无额外性能损耗：

方案1：索引关联写法（全版本兼容） #

SELECT t1.*
FROM `Schema`.`Table` t1
LEFT JOIN `Schema`.`Table` t2 
  ON t1.ExchNo = t2.ExchNo 
  AND t1.StkID = t2.StkID 
  AND t1.Date < t2.Date
WHERE t1.Date <= '2018-12-31' 
  AND t2.StkID IS NULL;

原理：同ExchNo、同StkID下，如果没有比t1.Date更大的记录，说明t1就是当前StkID对应的最新记录，全程走主键索引，无需额外排序计算，百万级数据量下响应速度和原写法完全一致。

注：如果实际业务中ExchNo存在多个值，该写法也能正确返回每个ExchNo下每个StkID的最新记录。

方案2：8.0+窗口函数写法（可读性更高） #

如果只需要兼容MySQL 8.0及以上版本，窗口函数的写法逻辑更清晰，只要查询字段都在主键索引内（或建立对应覆盖索引），性能损耗极低：

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY ExchNo, StkID ORDER BY Date DESC) AS rn
  FROM `Schema`.`Table`
  WHERE Date <= '2018-12-31'
) t WHERE rn = 1;

内容的提问来源于stack exchange，提问作者Ryan Tan