SQL 性能优化与慢查询治理十条军规 (附 EXPLAIN 与索引失效场景)

在互联网业务系统演进中，80% 的系统性能瓶颈最终都会落在数据库的慢查询（Slow Query）上。一次不合理的索引设计或全表扫描，轻则导致接口超时，重则拖垮整个数据库连接池造成服务雪崩。

本文汇聚一线互联网公司 DBA 与核心架构师的实战经验，提炼出价值千金的 SQL 性能优化与慢查询治理十条军规，配以清晰的场景对比与调优方案。

军规一：警惕隐式类型转换，杜绝索引失效

当查询条件中的字段类型与传入值的类型不匹配时，数据库会在底层自动调用转换函数，导致原本建立好的索引瞬间失效。

经典血案：

表字段 phone 的数据类型为 VARCHAR(20)，但查询时传入了未加单引号的数值：

-- ❌ 错误示范：隐式类型转换，直接触发全表扫描！
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 正确姿势：类型严格匹配，稳稳命中索引
SELECT * FROM users WHERE phone = '13800138000';

为什么失效？ MySQL 在遇到字符串与数值比较时，会隐式调用 CAST(phone AS SIGNED)。任何作用在索引列上的函数运算，都会导致 B+ 树索引结构无法直接定位！

军规二：严禁在 WHERE 字段左侧进行表达式或函数运算

不要让数据库替你的代码计算字段本身的值。

-- ❌ 错误示范：在字段左侧套用 DATE() 函数
SELECT * FROM orders WHERE DATE(created_at) = '2026-10-31';

-- ✅ 正确姿势：改写为范围查询，保留字段原貌
SELECT * FROM orders 
WHERE created_at >= '2026-10-31 00:00:00' 
  AND created_at <  '2026-11-01 00:00:00';

同理，WHERE id + 1 = 10 必须改写为 WHERE id = 9。

军规三：最左前缀原则不能破，避免左模糊匹配

1. 复合联合索引的最左前缀

如果建立了联合索引 INDEX idx_abc (a, b, c)：

• WHERE a = 1 AND b = 2 AND c = 3 ➔ 完美命中 a, b, c；
• WHERE a = 1 AND b = 2 ➔ 命中 a, b；
• WHERE a = 1 AND c = 3 ➔ 命中 a，但 c 无法使用索引直接定位；
• WHERE b = 2 AND c = 3 ➔ 完全无法使用索引！

2. LIKE 模糊查询的禁区

-- ❌ 错误示范：以百分号开头，B+ 树无法进行前缀排序，必走全表扫描
SELECT * FROM products WHERE name LIKE '%手机';

-- ✅ 正确姿势：右模糊查询可以正常命中索引
SELECT * FROM products WHERE name LIKE '华为%';

[!TIP] 如果业务确实需要前后全模糊检索，请使用专门的搜索引擎（如 Elasticsearch）或数据库的全文索引（FullText Index），而不是用普通 B+ 树硬抗。

军规四：彻底搞懂深分页（Deep Paging）的延迟关联优化

当执行 LIMIT 1000000, 10 时，数据库并不会直接跳转到第 100 万行，而是： 扫描前 1000010 条记录 ➔ 取出对应的主键回表获取整行数据 ➔ 抛弃前 100 万条 ➔ 仅返回最后 10 条！ 随着页码后移，磁盘 I/O 呈几何级数暴增。

终极优化方案：基于覆盖索引的“延迟关联（Deferred Join）”

-- ❌ 低效写法：深分页时回表 1000010 次，耗时 5~10 秒
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- ✅ 极致优化：先在纯主键索引树上完成定位，只对最终 10 行回表（耗时 < 0.05 秒）
SELECT o.* 
FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;

军规五：杜绝 SELECT *，善用覆盖索引（Covering Index）

在开发中随手写 SELECT * 是极其危险的习惯：

1. 网络与内存浪费：传输了大量前端根本不用的宽字段（如长文本、详情 JSON）；
2. 丧失了覆盖索引的机会： 如果一条查询只需要 id, username, email，而联合索引 (username, email) 包含了这些字段，数据库直接从索引树上就能拿到数据并返回，完全不需要回表扫描聚簇索引（Clustered Index）！

在 EXPLAIN 中，若看到 Extra: Using index，代表达到了覆盖索引的最优状态。

军规六：小表驱动大表，让连接（JOIN）高效运转

在多表关联查询中：

-- 当 table_a (1,000行) 与 table_b (1,000,000行) 进行关联时
SELECT a.*, b.status
FROM small_table a
LEFT JOIN big_table b ON a.user_id = b.user_id;

铁律要求：

1. 被驱动表（右表）的关联字段必须建有索引；
2. 始终使用过滤后数据量较小的表作为驱动表，尽量减少嵌套循环（Nested-Loop）的外部扫描次数。

军规七：OR 条件两边必须都有索引，否则用 UNION ALL 代替

-- 假设 status 列有索引，但 memo 列没有索引：
-- ❌ 错误示范：因为 memo 没有索引，MySQL 无法单纯靠 status 索引搞定，直接退化为全表扫描！
SELECT * FROM orders WHERE status = 'paid' OR memo = 'urgent';

-- ✅ 正确姿势：拆解为 UNION ALL（合并两个有索引的子查询）
SELECT * FROM orders WHERE status = 'paid'
UNION ALL
SELECT * FROM orders WHERE memo = 'urgent' AND status != 'paid';

军规八：批量插入代替单条循环，事务按批分流

在导入或批量同步千万级数据时：

❌ 严禁：在 Java/Python 中开 for 循环执行 10,000 次单个 INSERT。每次都涉及网络往返和磁盘刷盘（fsync），耗时几十分钟。

正确姿势：

1. 多值单语句批量插入（单次批量建议 500 ~ 2000 条）：

   INSERT INTO logs (user_id, action, created_at) VALUES 
   (1, 'login', NOW()),
   (2, 'view', NOW()),
   ...
   (500, 'logout', NOW());
   

2. 大事务分批提交：避免单个事务锁定超量行导致 Undo Log 暴涨或引发锁等待超时（1205）。

军规九：利用 EXPLAIN 进行严密体检，认准关键三指标

上线任何重要 SQL 前，必须先执行 EXPLAIN，重点观察三大关键指标：

EXPLAIN SELECT id, title FROM articles WHERE status = 'published' ORDER BY created_at DESC;

军规十：定期归档历史冷热数据，为单表瘦身

没有任何优化手段能抵挡无限增长的单表数据量。 当单表数据超过 1,000 万 ~ 2,000 万行 时，B+ 树层高从 3 层增至 4 层，索引缓存命中率断崖式下跌。

最佳治理策略：

1. 冷热分离：将 3 个月或 1 年前的历史订单、日志数据，定期归档转移至冷库或只读历史表；
2. 在线 DDL 工具：对超大表执行 ALTER TABLE 加索引时，使用 gh-ost 或 pt-online-schema-change，严禁直接在生产大表上执行长时间锁表 DDL。