5.2 SQL 语句与 WHERE 优化

5.2 SQL 语句与 WHERE 优化

本节摘要：同一语义的查询，写法能差出两个数量级。本节汇总 WHERE 条件的改写手法与语句级优化清单：范围替代函数、拆解 OR、裁剪 SELECT 列、破解深分页。位置：EXPLAIN 之后的第一个专项，多数慢查询的处方都开在这一节。

WHERE 的第一戒律：别动索引列

4.4 已从失效机理讲过，本节换个视角——从改写手法角度把常用套路备齐。核心思想一句话：让索引列以"裸列 + 常量"的形式参与比较。

-- 模式一：函数包列 → 范围改写
WHERE DATE(created_at) = '2026-08-15'
-- 改为
WHERE created_at >= '2026-08-15' AND created_at < '2026-08-16'

-- 模式二：字符串截取 → 前缀匹配
WHERE SUBSTRING(phone, 1, 3) = '138'
-- 改为
WHERE phone LIKE '138%'

-- 模式三：拼接 → 等值
WHERE CONCAT(area_code, '-', phone) = '010-12345678'
-- 改为（若 area_code 固定两位）
WHERE area_code = '010' AND phone = '12345678'

-- 模式四：运算 → 常量归并
WHERE YEAR(birthday) = 1995
-- 改为
WHERE birthday >= '1995-01-01' AND birthday < '1996-01-01'
​

四个模式的共同本质：把"对列做变换"改成"对常量做变换"。写代码时养成习惯：条件里的列名永远保持裸露，所有加工都在字面量侧完成。

OR、IN 与条件组合的分寸

OR 的风险在 4.4 讲过：跨非索引列会拖垮整条查询。但注意分寸——OR 两侧都是索引列时，优化器可以做 index merge，不必急着拆。拿不准就拆成 UNION ALL 实测。IN 列表几百个值一般没问题（优化器按多路等值处理），但上万的 IN 列表会让解析与优化开销显著上升，该分批就分批。

另一个高频坑是条件顺序迷信。老经验说"把过滤性强的条件放前面"，这在优化器时代基本失效——优化器基于统计信息自行决定谓词求解顺序，人为排序主要影响的是可读性。真正决定性的仍是索引能否覆盖这些条件。

SELECT 列裁剪与深分页

*别写 SELECT 。 三重代价：多列传输与解析；打破覆盖索引的可能（多取一列就回表）；表结构变更时的隐性耦合（应用拿到从没用的列）。评审会检查 SQL 时第一眼就看它。

深分页是另一个重灾区：

-- 第 100000 页：先扫 200 万行再扔掉 1999980 行
SELECT order_id, amount FROM orders
ORDER BY created_at DESC LIMIT 2000000, 20;

-- 改法一：延迟关联——先用覆盖索引定位主键，再回表取 20 行
SELECT o.order_id, o.amount
FROM orders o
JOIN (SELECT order_id FROM orders
      ORDER BY created_at DESC LIMIT 2000000, 20) t
  ON o.order_id = t.order_id;

-- 改法二：游标式翻页——记住上一页末尾的排序值
SELECT order_id, amount FROM orders
WHERE created_at < '2026-08-01 12:00:00'
ORDER BY created_at DESC LIMIT 20;
​

解读：改法一让子查询全程走覆盖索引（只碰 order_id 与 created_at），回表只剩 20 次；改法二把"跳过 N 行"变成"从游标继续"，成本恒定，但只支持顺序翻页，不支持随机跳页。两者按业务交互形态选择：导出类场景游标式最优，管理后台的跳页需求用延迟关联。

演练：一次语句级优化全景

背景：订单导出接口超时。语句：

SELECT * FROM orders
WHERE status = 2 OR remark LIKE '%加急%'
ORDER BY created_at DESC
LIMIT 100000, 50;
​

操作：按清单逐项过堂。SELECT * 裁成五个业务列；OR 拆成两条 UNION ALL，前半走 idx_status，后半用全文索引；深分页改游标式（导出本就是顺序翻页）。改后：

(SELECT order_id, customer_id, amount, created_at
 FROM orders WHERE status = 2
   AND created_at < '2026-08-20'
 ORDER BY created_at DESC LIMIT 50)
UNION ALL
(SELECT order_id, customer_id, amount, created_at
 FROM orders WHERE remark LIKE '加急%'
   AND created_at < '2026-08-20'
 ORDER BY created_at DESC LIMIT 50)
ORDER BY created_at DESC LIMIT 50;
​

结果：扫描行数从两千万级降到十万级以内，导出首屏从超时到 1 秒内。解读：这次优化四刀齐下——列裁剪、OR 拆分、前缀匹配、游标分页，每一刀都能单独用 EXPLAIN 验证收益。变式：若业务坚持随机跳页，用延迟关联并把排序键纳入覆盖索引。

易错点与评审清单

• ORDER BY 求快加 force index：强制走某个索引可能长期固化错误决策，统计信息更新后优化器本来会选得更好；先改写、后评估，实在不行再考虑 hint 并注明原因；
• 关联子查询当过滤器：WHERE (SELECT COUNT(*) ...) > 0 逐行执行，改 EXISTS 或 JOIN；
• LIMIT 无序分页当稳定结果用：无 ORDER BY 的 LIMIT 翻页结果不稳定，要么补 ORDER BY 要么接受"任意 50 条"；
• 优化只改代码不留档：改写前后各存一份 EXPLAIN 输出进评审记录，性能口径有据可查。

要点回顾：索引列保持裸露，加工移到字面量侧；OR 拆分是保底，index merge 是意外之喜；SELECT 列裁剪顺手打破覆盖索引的僵局；深分页两招——延迟关联与游标式，按交互形态选。写法层面清完，下一节专攻排序与分组这两大信号词的老巢。