OVER 比关联子查询省IO，因其单次扫描+内存窗口计算，避免主表每行触发独立查询导致的N次IO；典型场景如“查每个用户最新订单”，改写后用PARTITION BY和ROW_NUMBER()精准定位，且LEFT JOIN+COUNT(o.id) OVER确保无订单商品不丢失。
为什么 OVER 能比关联子查询省 IO
因为关联子查询对主表每行都触发一次独立查询，导致 N 次磁盘/缓存访问；而 OVER 是单次扫描 + 窗口计算，所有聚合或排序逻辑在内存中完成。尤其当子查询涉及 GROUP BY 或 ORDER BY 时，IO 放大效应更明显。
把 WHERE 子句里的关联子查询改写成窗口函数的典型模式
常见于“查每个用户最新订单”“每个部门最高薪员工”这类需求。原写法常是：
SELECT * FROM orders o1 WHERE order_date = ( SELECT MAX(order_date) FROM orders o2 WHERE o2.user_id = o1.user_id );
改用 OVER 后只需一次扫描：
SELECT user_id, order_id, order_date
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM orders
) t
WHERE rn = 1;
-
PARTITION BY对应原子查询的关联条件（如o2.user_id = o1.user_id） -
ORDER BY决定“最新”“最高”等业务顺序，必须明确 - 用
ROW_NUMBER()而非RANK()，避免并列时多返回结果 - 注意：若原逻辑允许并列（如“所有最高薪员工”），才考虑
RANK()或DENSE_RANK()
替代 COUNT(*) 关联子查询时的性能陷阱
比如统计每个商品的订单数，错误地写成：
SELECT p.name,
(SELECT COUNT(*) FROM orders o WHERE o.product_id = p.id) AS cnt
FROM products p;
正确改写为：
SELECT DISTINCT p.name,
COUNT(o.id) OVER (PARTITION BY p.id) AS cnt
FROM products p
LEFT JOIN orders o ON p.id = o.product_id;
- 必须用
LEFT JOIN，否则没订单的商品会被丢掉 -
COUNT(o.id)而非COUNT(*)，确保 NULL 不计入（o.id在 LEFT JOIN 中为 NULL 表示无订单） - 不能直接在
SELECT里写COUNT() OVER后再GROUP BY—— 窗口函数和分组混用易出错，优先用DISTINCT + OVER或 CTE - 某些旧版 MySQL（OVER，需确认执行环境
ORDER BY 和 LIMIT 在窗口函数里不生效？
这是最常被忽略的点：OVER 子句里的 ORDER BY 仅影响窗口内计算顺序（如累计求和、排名），它不会改变最终结果集的物理顺序。如果需要“每个用户最近 3 笔订单”，不能只靠 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY date DESC) 就完事，还必须在外层加 ORDER BY user_id, order_date DESC 才能保证可读性；而 LIMIT 必须放在最外层，窗口函数内部不接受 LIMIT。
另外，OVER 的排序字段如果有 NULL，不同数据库行为不一致（PostgreSQL 默认把 NULL 排最前，MySQL 8.0 默认最后），生产环境务必显式写 ORDER BY col DESC NULLS LAST（如支持）或用 COALESCE 处理。
