为什么你的"优化"SQL比优化前还慢？一次线上事故的血泪教训

事情是这样的。那天凌晨两点，我的钉钉突然响了——监控报警，数据库CPU飙到98%。作为一个写过无数SQL的老司机，我自信满满地打开慢查询日志，准备表演一个"药到病除"。

然后我发现，让我引以为傲的那条"优化后"查询，赫然排在慢查询第一位。

打脸来得太快，就像龙卷风。

事情是这样的

先交代一下背景。我们有个订单查询接口，最初的SQL是这样的：

SELECT o.id, o.order_no, o.created_at, u.name, u.phone
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 1
ORDER BY o.created_at DESC
LIMIT 20;

这条SQL跑了大概200ms，还行，能接受。但是产品经理说："能不能加上筛选条件？按地区筛选。"

好家伙，产品一句话，程序员跑断腿。我在users表加了个region字段，然后潇洒地改成了：

SELECT o.id, o.order_no, o.created_at, u.name, u.phone, u.region
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 1
AND u.region = 'Shanghai'
ORDER BY o.created_at DESC
LIMIT 20;

本地测试，完美。部署上线，监控报警。我那个慌啊，赶紧回滚，然后开始排查。

问题的根源：EXPLAIN一下？

很多同学写SQL，就像相亲——只看表面条件，不看内在逻辑。你得用EXPLAIN看看执行计划啊！

原SQL的执行计划：

id | select_type | table | type   | key          | rows   | Extra
---|-------------|-------|--------|--------------|--------|------------------
1  | SIMPLE      | o     | ref    | idx_status   | 1523   | Using filesort
1  | SIMPLE      | u     | eq_ref | PRIMARY      | 1      | NULL

优化后SQL的执行计划：

id | select_type | table | type   | key     | rows    | Extra
---|-------------|-------|--------|---------|---------|------------------
1  | SIMPLE      | u     | ref    | idx_region | 89521 | NULL
1  | SIMPLE      | o     | ALL    | NULL    | 895210  | Using where; Using temporary; Using filesort

看到问题了吗？优化后的查询，MySQL选择了先扫users表（89521行），然后再关联orders表。而原SQL是先用索引过滤orders（1523行），然后关联users。

89521行 vs 1523行，这差了将近60倍！

为什么MySQL会选错？

这里有个关键点：LEFT JOIN的ON条件和WHERE条件，MySQL处理方式不一样。

在我的查询里：

LEFT JOIN users u ON o.user_id = u.id
WHERE ... AND u.region = 'Shanghai'

虽然我写在WHERE里，但MySQL一看，u.region = 'Shanghai'，这条件跟users表强相关啊。于是它想："反正你是LEFT JOIN右表，我先把region='Shanghai'的用户找出来，再关联。"

结果就是灾难性的全表扫描。

正确的写法应该是：

SELECT o.id, o.order_no, o.created_at, u.name, u.phone, u.region
FROM orders o
LEFT JOIN users u ON o.user_id = u.id AND u.region = 'Shanghai'
WHERE o.status = 1
ORDER BY o.created_at DESC
LIMIT 20;

把u.region = 'Shanghai'放到ON后面，而不是WHERE里。

这样MySQL的执行计划就变成：先从orders表用idx_status索引查出数据（1523行），然后关联users时，只关联region='Shanghai'的用户。

更深一层：索引的代价

你以为这就完了？Too young, too simple.

我还发现另一个问题：orders表的created_at没有索引。ORDER BY created_at DESC意味着Using filesort，也就是内存排序。当数据量大的时候，这玩意儿能把你的服务器CPU干到100%。

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

加了这个索引之后，执行计划变成：

id | select_type | table | type   | key              | rows | Extra
---|-------------|-------|--------|------------------|------|------------------
1  | SIMPLE      | o     | ref    | idx_status_created | 1523 | NULL
1  | SIMPLE      | u     | eq_ref | PRIMARY          | 1    | NULL

注意看，Extra列没有Using filesort了！因为MySQL发现idx_status_created已经包含了status和created_at，查询结果本身就是按created_at排好序的，不需要再排序。

这一波操作下来，查询时间从200ms降到了8ms。60倍提升，不是我吹的。

经验总结：SQL优化的一些反常识

经过这次事故，我总结了几个反直觉的经验：

1. LEFT JOIN不一定是"左表优先"

MySQL优化器会根据统计信息选择驱动表，有时候你以为的"左表"反而被先扫描。记住：驱动表的选择是基于成本估算的，不是基于你写SQL的顺序。

2. WHERE条件不一定是"过滤条件"

对于LEFT JOIN来说，WHERE条件是在关联之后才生效的，会把左表的数据也过滤掉。如果你想在关联阶段就过滤右表，请用ON。

3. 索引不是越多越好

每个索引都会增加写入开销，还会占用磁盘空间。我们加的idx_status_created索引，对写操作（INSERT/UPDATE）来说就是负担。所以优化要权衡读和写的比例。

4. 覆盖索引是最容易被忽略的优化

所谓覆盖索引，就是索引包含了查询需要的所有字段。这样MySQL只需要扫描索引，不需要回表查询数据。比如：

ALTER TABLE orders ADD INDEX idx_covering (status, created_at, id, order_no);

这个索引就能覆盖我们的查询，实现真正的"索引扫描"。

最后说两句

写SQL这件事，入门容易，深入难。很多人都知道要用索引、要用EXPLAIN，但真正线上出问题的时候，往往是那些"我以为"的细节。

就像这次，我以为加个筛选条件是小改动，结果差点把数据库搞挂。还好是凌晨两点报的警，要是白天流量高峰期，那可就是一场生产事故。

所以啊，对SQL要有敬畏之心。你随手写的一条SELECT，可能是线上几十GB数据的命运。

下次有人跟你说"就加个筛选条件而已"，你可以把这条链接甩给他：comck.com，我们一起学习一起进步。

我是小龙虾，我们下期见。