用窗口函数、lateral和降序索引可减少60%以上sql行数并避免dependent subquery；lateral使子查询按行求值；row_number()替代变量或自连接实现稳定排名；降序索引优化order by执行路径。
直接用窗口函数、LATERAL 和降序索引替代旧版中那些靠变量、自连接、相关子查询硬凑出来的逻辑，能砍掉 60% 以上 SQL 行数，同时避免 DEPENDENT SUBQUERY 类型的执行计划恶化。
替换相关子查询：用 LATERAL 消除重复执行
MySQL 8.0.14+ 支持 LATERAL，它让子查询真正“按行求值”，而不是每次扫描都重跑一遍——这是旧版相关子查询性能崩盘的根源。
- 必须显式写
LATERAL关键字，不能省略；只允许出现在FROM子句中 - 子查询内可直接引用外层表字段（如
orders.id），无需再写WHERE关联条件 - 若子查询返回多行，会自动转为
CROSS JOIN；返回 0 行则该外层行被丢弃（类似INNER JOIN） - 对比旧写法：
SELECT ..., (SELECT status FROM logistics WHERE order_id = o.order_id ORDER BY update_time DESC LIMIT 1)，EXPLAIN 显示为DEPENDENT SUBQUERY，订单量每增 10 倍，耗时几乎翻倍
重构排名与 Top-N 查询：用 ROW_NUMBER() 替代变量或自连接
MySQL 5.7 及更早版本里靠用户变量（@rank := @rank + 1）实现排名，极易因优化器重排执行顺序出错；用自连接统计“比当前工资高的人数”又导致 O(n²) 复杂度。
-
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)是稳定、可预测、单次扫描的方案 - 想取每个部门前 3 名？外层加
WHERE rn 即可，不用嵌套两层子查询 - 注意：如果
department和salary上没索引，OVER子句仍会触发临时排序，性能不升反降 - 别混用
RANK()和ROW_NUMBER()：前者同值同名（1,2,2,4），后者强制唯一（1,2,3,4），业务语义不同
简化时间序列分析：用 LAG() / LEAD() 替代自连接
计算环比增长率、同比差值、首次/末次行为等，旧写法基本靠 JOIN sales s1 ON s1.date = DATE_SUB(s2.date, INTERVAL 1 DAY)，索引难利用，且日期缺失时结果错乱。
-
LAG(amount, 1) OVER (ORDER BY sale_date)直接拿到前一行值，空缺自动为NULL，语义干净 - 配合
frame_clause可控范围，例如ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING做三日滑动平均 - 若
sale_date有重复，需在ORDER BY中加入唯一列（如id）保证排序确定性，否则LAG结果不可复现 - 不要在
WHERE中过滤LAG()的结果（如WHERE LAG(...) > 100）——窗口函数在WHERE之后执行，会报错
升级索引策略：降序索引不是“语法糖”，是执行路径开关
MySQL 8.0 之前，ORDER BY created_time DESC 只能靠普通 B+ 树索引“倒着扫”，优化器常放弃走索引而选 filesort；现在可建真实降序索引，让排序变成索引遍历。
- 建混合索引时，ASC/DESC 要和查询中
ORDER BY严格一致，例如：CREATE INDEX idx_status_time ON orders(status ASC, created_time DESC) - 这个索引能直接支撑
WHERE status = 'shipped' ORDER BY created_time DESC，免filesort - 但无法用于
ORDER BY created_time ASC—— 降序索引不支持反向扫描，这点和 PostgreSQL 不同 - 低版本迁移时，别盲目把所有
ORDER BY ... DESC都加DESC，先看EXPLAIN是否真走了索引；没走，说明还有其他过滤条件干扰了索引选择
最易被忽略的是 OVER 子句中 ORDER BY 的确定性：没有唯一排序键时，ROW_NUMBER() 或 LAG() 的结果可能每次执行都不一样，线上排查会陷入死循环。
