SQL 查询重写的系统方法_技术教程

必须重写SQL而非加索引的情况包括：EXPLAIN显示全表扫描且rows值高，且WHERE含函数（如YEAR(created_at)=2025）、隐式类型转换（如user_id='123'）或OR多非覆盖条件；此时应改函数为范围查询、避免隐式转换、拆分OR条件。

什么时候必须重写 SQL 而不是加索引

索引解决不了所有性能问题。当 EXPLAIN 显示全表扫描 + 高 rows 值，且 WHERE 条件里有函数调用（如 WHERE YEAR(created_at) = 2025）、隐式类型转换（如 WHERE user_id = '123' 而 user_id 是 INT）、或 OR 连接多个非覆盖条件时，索引很可能失效——这时重写比调优更有效。

函数包裹列 → 改成范围查询：WHERE created_at >= '2025-01-01' AND created_at
OR 多条件 → 拆成 UNION ALL（注意去重开销）或改用 IN（当语义等价时）
隐式转换 → 统一数据类型：WHERE user_id = 123（而非字符串）

JOIN 顺序与驱动表选择的实际影响

MySQL 5.7+ 默认使用基于成本的优化器，但小表驱动大表仍是可靠原则。如果 EXPLAIN 中 type 是 ALL 且 rows 极高，说明该表被当作驱动表，而它本身又没走索引——这是重写的明确信号。

强制小表在前：把过滤后结果集最小的表放在 FROM 后第一个位置
避免 LEFT JOIN 后再 WHERE 过滤右表字段（会退化为 INNER JOIN），应把条件移到 ON 子句
多表 JOIN 时，用 STRAIGHT_JOIN（MySQL）临时固定顺序，验证效果后再决定是否保留

子查询转 JOIN 或窗口函数的取舍

相关子查询（correlated subquery）是性能黑洞，尤其在外部表数据量大时。但并非所有子查询都该转——要看数据库版本、数据分布和语义约束。

标量子查询（SELECT (SELECT name FROM users WHERE id = t.user_id)）→ 优先转 JOIN，除非 users.id 不唯一需保语义
存在性检查（WHERE EXISTS (SELECT 1 FROM logs l WHERE l.order_id = o.id)）→ 可用 INNER JOIN 替代，但注意重复行；更稳的是保持 EXISTS 并确保 logs.order_id 有索引
MySQL 8.0+ / PostgreSQL 可用窗口函数替代部分聚合子查询，例如用 ROW _NUMBER() OVER (PARTITION BY category ORDER BY price DESC) 替代“每类最贵商品”类子查询

GROUP BY 和 HAVING 的隐藏开销点

GROUP BY 本身不慢，慢在分组键未索引、或 HAVING 中用了无法下推的表达式。如果 EXPLAIN 显示 Using temporary; Using filesort，且分组字段无索引，重写重点就在这里。

确保 GROUP BY 字段有联合索引，顺序与分组顺序一致（如 GROUP BY dept, role → 索引应为 (dept, role)）
HAVING COUNT(*) > 1 无法下推，但若能提前用 WHERE 过滤（如 WHERE status = 'active'），先缩小输入集再分组，效果远好于依赖 HAVING
避免在 GROUP BY 中用函数或表达式（如 GROUP BY DATE(created_at)），改用生成列（MySQL 5.7+）或预计算字段