MySQL 8.0 LATERAL 子查询优化实战 3秒慢查询提速至0.8秒全记录

今天我们来深入探讨一个MySQL慢查询优化的实战案例。一个看似常规的查询，平均执行时间却高达2秒，在一小时内被执行了超过700次，这个性能瓶颈必须得到解决。经过优化，执行时间从3秒大幅降低至约0.8秒，效果非常显著。整个优化过程的核心思路可以总结为下图：

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

一、问题定位与深度分析

监控系统明确地指出了问题所在：一个查询在过去一小时内的执行次数超过700次，平均耗时达到2秒。下图展示了慢查询的监控概览：

引发问题的SQL语句如下：

SELECT overdue_amount
FROM cont_execute exe
LEFT JOIN (
  SELECT
    cont_number,
    is_important_cont,
    is_important_cont_in,
    ROW_NUMBER() OVER (
      PARTITION BY cont_number
      ORDER BY create_time DESC
    ) AS rn
  FROM cont_review_main
  WHERE del_flag = 0
) main
ON exe.cont_number = main.cont_number
WHERE exe.del_flag = 0
  AND main.rn = 1
  AND main.is_important_cont_in = 0
  AND exe.cont_company_name = 'xx科技有限公司'

值得注意的是，当时监控工具给出的常规优化建议，例如删除冗余索引或简单改写SQL，均未能有效提升性能。这表明问题的根源可能更为深层。

1.1 执行计划深度剖析

要找到根本原因，必须分析SQL的执行计划。下图是优化前的执行计划详情：

通过执行计划，我们识别出了两个核心的性能瓶颈。

1.2 核心瓶颈识别

第一个瓶颈：派生表缺乏索引，导致全表扫描。
子查询生成的派生表，MySQL无法为其自动创建索引（除非使用LATERAL或物化视图技术）。因此，过滤条件main.rn = 1实际上是在一个没有索引的派生表上进行的全表扫描，这次扫描涉及高达77,724行数据。

第二个瓶颈：cont_review_main表的filesort操作开销巨大。
尽管查询使用了idx_htps1_main索引（基于del_flag字段），但窗口函数中的PARTITION BY cont_number ORDER BY create_time DESC子句，需要对这77,724行数据执行一次额外的排序操作，即Using filesort，这个过程消耗了大量CPU和内存资源。

二、优化方案与具体实施

针对上述瓶颈，一个高效的优化策略是使用LATERAL关联子查询。

2.1 采用LATERAL关联子查询

将原始SQL改写为如下形式（要求MySQL版本在8.0.14及以上）：

SELECT exe.overdue_amount
FROM cont_execute exe
INNER JOIN LATERAL (
  SELECT
    is_important_cont,
    is_important_cont_in
  FROM cont_review_main main
  WHERE main.cont_number = exe.cont_number
    AND main.del_flag = 0
  ORDER BY main.create_time DESC
  LIMIT 1
) main
ON main.is_important_cont_in = 0
WHERE exe.del_flag = 0
  AND exe.cont_company_name = '伟仕佳杰（重庆）科技有限公司';

这种写法的优势是什么？
关键在于LATERAL关键字。它允许子查询引用外层查询（exe）当前行的列值（此处是exe.cont_number）。这意味着，对于cont_execute表中的每一行，子查询都能利用cont_number进行高效过滤，直接获取对应合约的最新一条审核记录，从而彻底避免了生成庞大的中间派生表并进行全表扫描。

配套的索引优化建议：
为了进一步提升子查询中排序操作的效率，建议在cont_review_main表上创建如下复合索引：

CREATE INDEX idx_main_cont_time ON cont_review_main(cont_number, create_time DESC, del_flag);

优化效果立竿见影。在约3万条测试数据的环境下，查询执行时间从原来的约3秒下降到了0.75秒。

三、深入理解LATERAL关键字

LATERAL是MySQL 8.0.14版本引入的关键字，可以理解为“横向关联”或“逐行引用”。它打破了一个传统限制：普通子查询是独立执行的，而LATERAL子查询可以引用其外部查询中当前行的列值。

简单来说，普通子查询是“闭门造车”，LATERAL子查询则是“协同作战”。

3.1 普通子查询与LATERAL子查询对比

普通子查询（错误示例）：
下面的写法会报错，因为子查询内部无法识别和引用外部表exe的字段。

-- 报错：Unknown column 'exe.cont_number' in 'where clause'
SELECT *
FROM cont_execute exe
INNER JOIN (
  SELECT *
  FROM cont_review_main m
  WHERE m.cont_number = exe.cont_number -- 这里exe不可见！
  LIMIT 1
) main

LATERAL子查询（正确示例）：
使用LATERAL后，子查询就能“看到”并引用外部表的cont_number了。

-- 正确：LATERAL允许子查询引用exe的当前行
SELECT *
FROM cont_execute exe
INNER JOIN LATERAL (
  SELECT *
  FROM cont_review_main m
  WHERE m.cont_number = exe.cont_number -- 现在可以用了！
  ORDER BY m.create_time DESC
  LIMIT 1
) main

可以打一个形象的比方：普通子查询像是先做好一整桌菜（派生表）再端上来匹配；而LATERAL子查询更像是根据每位客人的口味（外部表的每一行），现场单独制作一道菜。

四、优化前后的逻辑对比与最佳实践建议

优化前后的两个SQL在业务逻辑上基本是等价的，但存在一个微妙的潜在差异，需要特别注意。

这个差异点在于：当cont_review_main表中，同一个cont_number存在多条create_time完全相同的记录时，两个SQL选择“最新记录”的结果可能不一致。

• 原SQL（使用ROW_NUMBER()）： 在窗口函数排序时，如果create_time相同，数据库内部的排序是不确定的，可能导致每次选出的“rn=1”的行不同。
• 优化后SQL（使用LIMIT 1）： 同样，当ORDER BY create_time DESC遇到相同时间戳时，返回哪一行也是不确定的。

4.1 结论与最佳实践建议

在create_time具有唯一性（或业务上不存在重复时间戳）的前提下，两个SQL完全等价。但如果存在重复时间，两者的结果都可能是不确定的，不应依赖这种不确定性。

最佳实践建议：
为了保证查询结果的绝对确定性，建议在排序条件中增加一个具有唯一性的列作为“决胜局”（tie-breaker）。例如，假设表有自增主键id，可以将两个SQL中的排序都修改为：

ORDER BY create_time DESC, id DESC

这样，即使创建时间相同，也会按照主键降序来明确选取一条记录，确保每次查询结果都严格一致。