用条件聚合替代多个标量子查询可显著提升性能，如用case when在单次扫描中统计多指标，避免重复全表扫描；需注意where过滤优先、避免case中用函数、慎用窗口与聚合混用。
用条件聚合替代多个子查询
标量子查询在多指标场景下是性能杀手。比如要同时查「当日订单数」「支付成功数」「退款单数」，写成三个 (SELECT COUNT(*) FROM orders WHERE status = 'paid') 类似结构，数据库会为每一行主查询重复执行三次全表扫描或索引遍历。
正确做法是用 CASE WHEN 在单次扫描中完成所有统计：
SELECT COUNT(*) AS total_orders, COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_count, COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded_count, AVG(amount) AS avg_amount FROM orders WHERE create_time >= '2026-09-17';
- 所有指标共享同一张表的扫描结果，
WHERE过滤只做一次 -
COUNT(CASE ...)比SUM(CASE ...)更安全：NULL 不参与计数，无需额外处理 - 避免在
CASE中调用函数（如DATE(create_time)），否则可能使索引失效
避免在聚合字段上使用函数或表达式
像 AVG(DATEDIFF(NOW(), create_time)) 或 SUM(price * 1.1) 这类写法，会让优化器无法利用覆盖索引，还可能触发隐式类型转换。
更稳妥的方式是把计算逻辑下沉到应用层，或在数据库里提前物化中间字段：
- 如果必须算「下单到支付时长」，优先建一个
pay_duration_sec计算列并加索引，而不是每次SELECT DATEDIFF(paid_at, create_at) - 对金额加税这类固定比例运算，建议在应用层做，避免数据库重复计算浮点数
-
GROUP BY字段若含函数（如GROUP BY YEAR(create_time)），基本等于放弃索引加速，应改用范围条件 + 预计算分区
大表聚合前先过滤再分组
错误写法：SELECT user_id, COUNT(*) FROM events GROUP BY user_id HAVING COUNT(*) > 100 —— 先分组再过滤，中间结果集可能爆炸。
正确顺序是：WHERE → GROUP BY → HAVING。尤其要注意 HAVING 不能替代 WHERE：
-
WHERE过滤的是原始行，能用索引；HAVING过滤的是分组后结果，无法走索引 - 想查「华东地区下单超100次的用户」，必须把
region = 'east'放WHERE，不能塞进HAVING - 对超千万行的表，考虑先用
WHERE create_time BETWEEN ...缩小数据集，再分组，比无条件分组快一个数量级
注意窗口函数与聚合的混合陷阱
当需要「每个用户的订单总数」+「全局平均订单数」时，有人会写 COUNT(*) OVER() / COUNT(*)，这看似简洁，实则危险。
窗口函数和聚合函数混用会导致执行计划退化为两趟扫描，甚至临时表落盘：
- 优先拆成两个 CTE 或子查询，明确分离「按用户聚合」和「全局聚合」逻辑
- MySQL 8.0+ 支持
GROUP BY后接窗口函数，但仅限于不改变分组粒度的场景（如ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount)） - PostgreSQL 的
WITH ROLLUP或GROUPING SETS更适合多维汇总，比嵌套窗口更可控
真正难的不是写出语法正确的语句，而是让每一行扫描都算得其所——别让 COUNT 跑在没 WHERE 的大表上，也别让 CASE WHEN 套着函数白忙活。
