生产环境 MySQL 8.0 LATERAL 实战：3s 慢查询优化到 0.8s 的完整过程

select
  overdue_amount
from
  cont_execute exe
leftjoin (
    SELECT
      cont_number,
      is_important_cont,
      is_important_cont_in,
      ROW_NUMBER() OVER (
        PARTITIONBY
          cont_number
        ORDERBY
          create_time DESC
      ) AS rn
    FROM
      cont_review_main
    WHERE
      del_flag = 0
  ) mainon 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科技有限公司'
  

Using filesort  对 77,724 行做窗口函数排序

SELECT
  exe.overdue_amount
FROM
  cont_execute exe
INNERJOINLATERAL (
    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
    ORDERBY main.create_time DESC
    LIMIT1
  ) mainON main.is_important_cont_in = 0
WHERE
  exe.del_flag = 0
AND exe.cont_company_name = '伟仕佳杰（重庆）科技有限公司';

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

-- 报错：exe.cont_number 在子查询里不认识
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 允许子查询引用 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