为何同一条SQL查询在MSSQL运行正常，MySQL却卡顿耗时久？

• 你此前看到的“非相关子查询导致效率低”的说法存在错误，该SQL实际使用的是相关子查询：外层查询每扫描1行MyTable的记录，就会触发1次内层子查询执行，匹配当前行的location、item值查找符合要求的最大日期。当数据量达到50万级时，相当于要执行近50万次子查询，性能自然急剧下降。
• MSSQL的查询优化器内置了更完善的子查询扁平化逻辑，会自动将这种相关子查询重写成效率更高的关联查询执行计划，所以不会出现性能问题；而MySQL（尤其是5.7及更早版本）的优化器对这类场景的重写支持较差，会严格按照嵌套逻辑逐行执行，因此耗时极长。
• 1000行小数据量下子查询执行次数少，所以感知不到性能差异。

你可以根据自己的MySQL版本选择对应写法，两种写法的返回结果都和预期完全一致：

方案1：MySQL 8.0+ 窗口函数写法（性能最优、代码最简洁） #

仅需扫描1次表即可得到结果，性能远高于原相关子查询写法：

SELECT location, item, cost, date
FROM (
    SELECT 
        location, item, cost, date,
        ROW_NUMBER() OVER (
            PARTITION BY location, item 
            ORDER BY date DESC, cost DESC
        ) AS rn
    FROM MyTable
    WHERE date <= '2021/10/31' AND cost <> 0
) t
WHERE rn = 1;

方案2：兼容MySQL 5.7及更低版本的关联写法 #

如果你的MySQL版本不支持窗口函数，可以使用两次表扫描的关联写法，性能也远高于原嵌套写法：

SELECT t.location, t.item, MAX(t.cost) AS cost, t.date
FROM MyTable t
INNER JOIN (
    SELECT location, item, MAX(date) AS max_date
    FROM MyTable
    WHERE date <= '2021/10/31' AND cost <> 0
    GROUP BY location, item
) md ON t.location = md.location AND t.item = md.item AND t.date = md.max_date
WHERE t.cost <> 0
GROUP BY t.location, t.item, t.date;

新建以下覆盖联合索引后，上述两种写法都不需要回表查询，性能可以再提升数倍：

CREATE INDEX idx_loc_item_date_cost ON MyTable (location, item, date DESC, cost);

内容的提问来源于stack exchange，提问作者Randy Soriano