SQL 优化的 20 条军规（MySQL 为主，生产环境实战版）
以下 20 条规则是大量互联网公司（尤其是中大厂）在 MySQL 环境下总结出的高频、致命、必须遵守的优化军规。很多慢查询、线上事故都和违反其中几条直接相关。
优先级排序（从最致命到相对次要）：
- **禁止 SELECT ***
只查询真正需要的字段。
反例：SELECT * FROM user WHERE id = 1
正例：SELECT id, name, phone FROM user WHERE id = 1
理由：减少 IO、降低网络传输、防止表结构变更影响。 - WHERE 条件字段必须走索引
所有过滤、排序、连接字段尽量建索引。
没有索引 = 全表扫描（大表秒变 OOM）。 - 上线前必用 EXPLAIN / EXPLAIN ANALYZE 分析
关注 type、key、rows、Extra（Using filesort / Using temporary 最危险）。
目标：type ≥ ref / range，Extra 不出现 filesort / temporary。 - 禁止在 WHERE 条件列上使用函数
反例：WHERE DATE(create_time) = '2025-01-01'
正例：WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'
函数导致索引失效。 - 避免 OR 条件
OR 很容易导致全表扫描。
改写为 UNION ALL 或拆成多条 SQL + 代码合并。
极少数情况下可用IN替代（但 IN 元素过多也危险）。 - LIKE 左边不要加 %
反例：WHERE name LIKE '%张三%'→ 索引失效
正例：WHERE name LIKE '张三%'→ 可走前缀索引 - JOIN 表数量控制在 3 张以内
多表 join 容易产生笛卡尔积爆炸。
超过 3 张表建议拆 SQL 或引入中间表。 - JOIN 时字段类型、字符集、排序规则必须完全一致
否则隐式转换导致索引失效（最常见 varchar 与 char、utf8 与 utf8mb4）。 - 小表驱动大表
永远让小表（行数少）放在左边（驱动表）。
MySQL 执行计划会尽量把小表放在前面，但不要依赖它。 - 禁止在索引列上做计算
反例：WHERE age + 1 > 18
正例：WHERE age > 17 - 分页大偏移优化（千万级表致命）
反例：LIMIT 1000000,10（扫描 1000010 行）
正例：SELECT id FROM t ORDER BY id LIMIT 1000000,10; -- 然后用 id 回表 SELECT * FROM t WHERE id IN (……) ORDER BY id;或用延迟关联 / 覆盖索引。 - 优先使用覆盖索引
把查询字段全部放到索引中（索引覆盖所有 select + where + order by + group by），Extra = Using index。 - 拒绝在 WHERE 中使用 !=、<>、NOT IN、NOT EXISTS
这些条件很难利用索引，优先改成正向条件。 - ORDER BY / GROUP BY 字段要走索引
否则 Using filesort（内存/磁盘排序）非常耗资源。 - 控制 IN 列表长度
IN 超过 1000 条左右性能急剧下降，建议分批或用临时表。 - 避免隐式类型转换
反例：WHERE phone = 13812345678（phone 是 varchar）
正例：WHERE phone = '13812345678' - COUNT(*) vs COUNT(1) vs COUNT(主键)
MySQL 5.7.18+ 后三者性能几乎无差别，但建议用COUNT(*)（最清晰）。 - 批量操作优于循环单条
INSERT/UPDATE/DELETE 优先批量（VALUES 多行或 JOIN + UPDATE）。 - 禁止在事务中做大批量更新
大事务持锁时间长，容易死锁 / 阻塞。拆小批次 + 合理 sleep。 - 合理使用索引下推（Index Condition Pushdown）
MySQL 5.6+ 支持，把 where 条件推到存储引擎层减少回表。
尤其对复合索引 + 范围 + 其他过滤条件非常有效。
快速记忆口诀版（浓缩 10 条最狠的）
- 别 *，要字段
- 索必须有，where 必中
- Explain 先行，type ref range
- 函数、计算、OR 尽量别上
- Like 右模糊，左模糊别用
- Join 三张内，小表驱动大
- 类型一致，字符集一致
- 大偏移分页要用 id 优化
- 覆盖索引最香
- 批量 > 循环，事务别太大
最后提醒：
SQL 优化不是“一招鲜”，先用 EXPLAIN 定位问题，再针对性优化。
线上慢查询日志 + 定期 review 是长治久安的根本。
你最近在优化哪类 SQL？是分页、JOIN 还是聚合？可以贴出来具体语句，我帮你一起分析。
