写SQL十年，我踩过的坑比你们吃过的盐还多

原创于 2026-06-26 09:02:44 发布 · 176 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#SQL #性能优化 #数据库 #大数据 #深度优先

话题

#编程达人挑战赛·第10期

写SQL十年，我踩过的坑比你们吃过的盐还多

你有没有在代码评审会上被人当众指出SQL写得烂？我有过。那种感觉就像小时候考试卷子被贴在黑板报上，全班同学围观你的错题，脸烧得发烫，恨不得找个地缝钻进去。

那是我入行第三年，自认为技术已经不错了，写了一条自以为很优雅的联表查询。结果被当时的架构师老周在会上直接点名：“这条SQL上了生产，用户量翻一倍就得崩。”我当时不服气，觉得他在危言耸听。三个月后，用户量真的翻了一倍，数据库真的崩了。凌晨三点爬起来救火的时候，我脑子里全是老周那句话。

从那以后，我开始系统性地研究SQL优化，踩了无数坑，读了无数执行计划，慢慢总结出了一套自己的军规。今天我把这七条掏心窝子的经验分享出来，每一条背后都是一次真实的线上事故，希望能帮你少走几年弯路。

一、EXPLAIN不是调优工具，是写SQL的标配

很多开发同学的习惯是：SQL写完了，功能跑通了，就提交代码了。等哪天线上慢了，才想起来用EXPLAIN看看执行计划。这个习惯，是我花了两年时间才改掉的。

我现在写任何一条SQL，只要涉及两张以上的表，或者单表数据量超过十万行，一定会在测试环境先跑一遍EXPLAIN。看什么？主要看三个地方。

第一看type列。这是执行计划里最重要的一个字段，它告诉你MySQL是以什么方式访问数据的。从好到差依次是：system、const、eq_ref、ref、range、index、ALL。如果你看到ALL，说明全表扫描了，除非表里只有几百条数据，否则必须想办法优化。我给自己定的底线是至少做到range级别，争取做到ref。

举个例子，下面这条查询在orders表有五十万数据时，type显示为ALL：

sql

EXPLAIN SELECT * FROM orders WHERE order_status = '已发货';

输出结果里，type列赫然写着ALL，rows显示五十万。这意味着MySQL要扫描全部五十万行数据来找到符合条件的记录。我当时的处理方式是给order_status加一个索引：

sql

ALTER TABLE orders ADD INDEX idx_status (order_status);

再次执行EXPLAIN，type变成了ref，rows降到了八万。这就是索引的力量。

第二看key列。它告诉你MySQL实际使用了哪个索引。有时候你明明建了索引，但优化器偏偏不用，这时候key列就是空的。原因可能是索引区分度太低、统计信息不准、或者优化器认为全表扫描更快。遇到这种情况，我会用FORCE INDEX强制指定索引，但只是临时方案，根本解决还是要优化索引设计。

第三看Extra列。如果看到Using filesort或者Using temporary，说明查询用到了文件排序或临时表，这在数据量大的时候是性能杀手。比如下面这条语句：

sql

EXPLAIN SELECT customer_id, SUM(order_amount)

FROM orders

GROUP BY customer_id

ORDER BY SUM(order_amount) DESC;

Extra列显示Using temporary和Using filesort，说明MySQL先建了临时表做分组，又用文件排序处理了ORDER BY。这种查询在十万级数据量下就要跑好几秒。我的优化方案是加一个覆盖索引，让分组和排序都能在索引里完成：

sql

ALTER TABLE orders ADD INDEX idx_customer_amount (customer_id, order_amount);

现在，我要求团队里每个开发同学提交代码时，如果SQL涉及复杂查询，必须附上EXPLAIN结果的截图。这不是形式主义，而是对生产环境最基本的敬畏。

二、索引不是建了就完事，你得知道它为什么不走

索引失效是SQL优化里最常见的问题，也是最容易被忽视的问题。我见过太多开发同学，明明建了索引，查询还是慢，一看执行计划，索引根本没走。为什么？因为他们在不经意间踩了索引失效的坑。

第一个坑是对索引列做函数操作。比如下面这条语句：

sql

SELECT * FROM orders WHERE YEAR(create_time) = 2025;

create_time字段上明明有索引，但YEAR函数一包，索引就失效了。因为索引里存的是create_time的原始值，而YEAR(create_time)是一个计算后的值，MySQL没办法用索引去匹配。正确的写法是：

sql

SELECT * FROM orders

WHERE create_time >= '2025-01-01'

AND create_time < '2026-01-01';

第二个坑是隐式类型转换。假设phone_number字段是varchar类型，但你写成了这样：

sql

SELECT * FROM users WHERE phone_number = 13800138000;

传入的是数字，字段是字符串，MySQL会偷偷把phone_number转成数字再比较，这一转，索引就失效了。正确的写法是给值加上引号：

sql

SELECT * FROM users WHERE phone_number = '13800138000';

第三个坑是LIKE以百分号开头。这个应该很多人都知道，但实际开发中还是经常犯。比如搜索功能里写：

sql

SELECT * FROM articles WHERE title LIKE '%数据库%';

百分号在前面，索引就没办法用，因为B+树索引是从左到右匹配的，你开头就是通配符，MySQL不知道从哪里开始找。如果业务确实需要模糊搜索，可以考虑用全文索引，或者引入Elasticsearch。

第四个坑是复合索引没遵循最左前缀原则。假设你建了一个联合索引：

sql

ALTER TABLE orders ADD INDEX idx_a_b_c (a, b, c);

那么以下查询能用到索引：WHERE a = 1（用到a）、WHERE a = 1 AND b = 2（用到a和b）、WHERE a = 1 AND b = 2 AND c = 3（全部用到）。但以下查询用不到索引：WHERE b = 2（跳过了a）、WHERE c = 3（跳过了a和b）。记住，复合索引就像楼梯，你得从第一级开始走，不能直接从中间跨上去。

三、JOIN不是越多越酷，每多一个JOIN复杂度翻倍

我刚入行那会儿，特别喜欢写多表JOIN，觉得一条SQL把四五张表的数据全查出来，特别有技术含量。直到有一次，我写了一条五表JOIN的查询，直接把测试环境的数据库跑挂了，才意识到自己有多蠢。

JOIN的本质是嵌套循环。MySQL会选一张表作为驱动表，然后逐行去其他表里匹配数据。假设驱动表有一万行，被驱动表有十万行，那就是一万乘以十万，十亿次匹配。每多一个JOIN，这个嵌套就多一层，复杂度呈指数级上升。

我现在的原则是：JOIN不超过三张表。如果确实需要关联更多表，我会拆分成多条简单的查询，在应用层做数据组装。比如下面这个需求：查询用户的订单详情，同时要显示用户的等级信息、订单的商品信息、商品的分类信息。四张表，我不会一条SQL全查出来，而是分两步：

第一步，先查订单和用户等级：

sql

SELECT o.order_id, o.order_amount, o.create_time, u.user_name, l.level_name

FROM orders o

INNER JOIN users u ON o.user_id = u.user_id

INNER JOIN user_level l ON u.user_id = l.user_id

WHERE o.order_id = 12345;

第二步，再查订单的商品明细：

sql

SELECT od.product_name, od.quantity, od.unit_price, c.category_name

FROM order_detail od

INNER JOIN product_category c ON od.category_id = c.category_id

WHERE od.order_id = 12345;

然后在应用层把两次查询的结果拼在一起。虽然多了一次数据库请求，但每次查询都简单高效，整体性能反而更好。

另外，JOIN的时候还要注意驱动表的选择。小表驱动大表是基本原则。MySQL优化器通常会自己选择，但有时候统计信息不准，它会选错。我习惯在JOIN语句里，把数据量最小的表放在最前面，给优化器一个明确的提示。

四、子查询写得爽，上线火葬场

子查询是SQL里最容易被滥用的特性。它写起来很直观，符合人的思维习惯，但执行效率往往惨不忍睹。我见过最夸张的一次，一个开发同学用子查询嵌套了三层，外层两千条数据，中层五万条，内层十万条，查询跑了四十分钟还没出结果。

子查询的问题在于，MySQL对它的优化能力有限。尤其是WHERE IN子查询，很多情况下会变成对外层每一行数据都执行一次子查询，相当于嵌套循环，数据量一大就崩。

我现在的原则是：能用JOIN解决的，绝不用子查询；能用EXISTS解决的，绝不用IN。

举个例子，查询下过订单的用户信息，用IN子查询的写法：

sql

SELECT * FROM users

WHERE user_id IN (

SELECT user_id FROM orders WHERE order_amount > 1000

);

如果orders表有五十万条数据，这个子查询会先查出所有符合条件的user_id，构建一个大的结果集，然后外层查询再逐条匹配。改写为EXISTS：

sql

SELECT * FROM users u

WHERE EXISTS (

SELECT 1 FROM orders o

WHERE o.user_id = u.user_id

AND o.order_amount > 1000

);

EXISTS的执行逻辑是：对于users表的每一行，去orders表里找有没有匹配的记录，找到一条就立即返回TRUE，不再继续找。配合user_id和order_amount的联合索引，这个查询的效率比IN高出几十倍。

还有一种情况是NOT IN，这个更危险。如果子查询的结果集里包含NULL值，NOT IN会直接返回空结果，因为NULL和任何值比较的结果都是UNKNOWN。我吃过这个亏，排查了半天才发现子查询里混进了一个NULL。现在我都用NOT EXISTS代替NOT IN，语义更清晰，也不会被NULL坑。

五、大表查询不加LIMIT，等于在马路上开坦克

这条军规说起来简单，但实际开发中违反的人特别多。很多开发同学在写后台管理系统的列表查询时，觉得数据量不大，就不加LIMIT。结果有一天运营同学导出了一年的数据，几十万条，数据库连接池瞬间被打满。

我经历过最严重的一次事故，是一个实习生写的报表查询，在订单表上执行了一条不带LIMIT的SELECT，当时订单表已经有八百万条数据。这条查询跑了十分钟，占用了大量内存和CPU，导致其他正常的交易请求全部超时。那次故障持续了四十分钟，直接经济损失六位数。

从那以后，我在团队里定了一条铁律：任何查询，只要表的数据量可能超过一万行，必须加LIMIT。哪怕是后台管理系统，也要加LIMIT并配合分页。如果业务确实需要导出全量数据，走离线任务，不能走在线接口。

分页查询也有讲究。传统的LIMIT offset, size方式，在offset很大的时候性能很差。比如LIMIT 1000000, 20，MySQL需要先扫描前面一百万条数据，再取二十条。优化方案是用游标分页，记住上一页最后一条数据的ID，下一页从那个ID之后开始查：

sql

SELECT * FROM orders

WHERE order_id > 1000000

ORDER BY order_id

LIMIT 20;

前提是order_id有索引，这样查询可以直接定位到索引的某个位置，不需要扫描前面的数据。

六、WHERE条件里的小细节，决定了查询的快慢

这条军规涵盖了很多容易被忽视的细节，我挑三个最常犯的讲。

第一个是OR条件。如果OR两边有一个字段没有索引，整个查询就会全表扫描。比如：

sql

SELECT * FROM orders

WHERE order_status = '已完成' OR order_amount > 1000;

假设order_status有索引，但order_amount没有，MySQL可能会选择全表扫描。解决方案是用UNION代替OR：

sql

SELECT * FROM orders WHERE order_status = '已完成'

UNION

SELECT * FROM orders WHERE order_amount > 1000;

这样每条子查询都能用上各自的索引，效率高很多。

第二个是负向条件。!=、<>、NOT IN这些负向条件，大部分情况下无法使用索引。因为索引是告诉你数据在哪里，而负向条件是告诉你数据不在哪里，MySQL还是得全表扫描才能确定。如果业务允许，尽量把负向条件改写为正向条件。

第三个是COUNT的用法。COUNT()和COUNT(列名)是有区别的，COUNT()统计所有行，COUNT(列名)统计该列不为NULL的行。很多人以为COUNT(1)比COUNT()快，其实在MySQL里它们是一样的，优化器会把COUNT()优化成COUNT(0)。真正影响性能的是COUNT(DISTINCT 列名)，这个操作需要去重，数据量大的时候很慢。