企业官网建设流程全解析

别再说SQL简单了，这五个优化技巧至少值二十万年薪

不知道你有没有过这样的经历：凌晨两点，手机突然疯狂震动，运维同事在群里@你：“线上数据库CPU飙到100%，整个交易系统挂了，快看看是不是你的SQL有问题！”你从睡梦中惊醒，一身冷汗，手忙脚乱地打开电脑，面对屏幕上那条跑了一个小时还没出结果的SQL语句，大脑一片空白。

我在数据库行业摸爬滚打了十年，这样的场景经历了不下二十次。每一次都是血与泪的教训，每一次都在倒逼我重新审视那些看似简单、实则暗藏杀机的SQL语句。今天，我不想给你罗列一堆枯燥的优化法则，而是想带你走进我亲身经历的几个真实事故现场，看看那些让系统崩溃的SQL到底长什么样，以及我是如何一步步把它们从“性能杀手”改造成“优雅跑车”的。

一、一个索引引发的血案：从两小时到0.03秒

先从我职业生涯中最惨痛的一次事故说起。那是2019年双十一的前一天晚上，我们正在做最后的压力测试。订单表里大约有八百万条数据，业务方需要导出过去一年所有已完成订单的明细，用于财务对账。

我写了一条看起来人畜无害的SQL：

sql

SELECT order_id, customer_name, order_amount, create_time

FROM orders

WHERE order_status = '已完成'

AND create_time >= '2018-11-01'

AND create_time < '2019-11-01'

ORDER BY create_time DESC;

这条语句提交之后，数据库连接池瞬间被打满，其他正常的交易请求全部阻塞。监控大屏上，数据库服务器的CPU使用率曲线像坐火箭一样直冲100%。我当时的后背瞬间就湿透了。

问题出在哪里？我用EXPLAIN一看，发现这条查询走了全表扫描，扫描了全部八百万行数据。原来，orders表上虽然有一个create_time的索引，但order_status字段没有索引，而MySQL的优化器在评估之后，错误地选择了全表扫描。

当时的紧急处理方案是强制使用索引：

sql

SELECT order_id, customer_name, order_amount, create_time

FROM orders FORCE INDEX(idx_create_time)

WHERE order_status = '已完成'

AND create_time >= '2018-11-01'

AND create_time < '2019-11-01'

ORDER BY create_time DESC;

但这只是临时止血，根本问题没有解决。事后复盘，我做了三件事：

第一，创建了一个联合索引。order_status和create_time的联合索引，把过滤性更好的order_status放在前面：

sql

ALTER TABLE orders ADD INDEX idx_status_time (order_status, create_time);

第二，用EXPLAIN再次验证执行计划。这次显示type为range，key为idx_status_time，扫描行数从八百万降到了五十万。

第三，进一步优化查询逻辑。与业务方沟通后发现，财务对账其实不需要实时数据，完全可以走离线报表。于是我们搭建了读写分离架构，把这类统计查询全部路由到只读从库，主库只负责在线交易。

改造之后，同样的查询耗时从两小时降到了0.03秒。那天晚上我坐在电脑前，看着执行计划里那个漂亮的ref类型，心里只有一个念头：索引不是建了就完事，你得知道优化器是怎么选的，以及为什么它会选错。

二、JOIN的陷阱：一条SQL拖垮整个数据库

第二次重大事故发生在2021年春天。我们上线了一个新的营销活动，需要给符合条件的用户推送优惠券。活动规则比较复杂：过去三十天内有下单记录、且订单金额超过200元、且用户等级为VIP的用户，才能参与活动。

我写了这样一条SQL来筛选用户：

sql

SELECT DISTINCT u.user_id, u.phone_number

FROM users u

LEFT JOIN orders o ON u.user_id = o.user_id

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

WHERE o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)

AND o.order_amount > 200

AND l.level_name = 'VIP';

这条语句一执行，数据库立刻开始剧烈抖动。我赶紧用SHOW PROCESSLIST查看，发现这条查询的状态一直是“Sending data”，而且已经跑了快十分钟了。

问题出在LEFT JOIN上。我用了两个LEFT JOIN，但WHERE条件里又对右表做了过滤，这实际上把LEFT JOIN变成了INNER JOIN的效果，但MySQL优化器在处理时，仍然会先生成笛卡尔积再过滤，数据量一大就直接爆炸。

我当时的改造思路是这样的：

首先，把LEFT JOIN改成INNER JOIN，明确告诉优化器我们只需要匹配的数据：

sql

SELECT DISTINCT u.user_id, u.phone_number

FROM users u

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

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

WHERE o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)

AND o.order_amount > 200

AND l.level_name = 'VIP';

其次，调整JOIN的顺序。小表驱动大表是基本原则，user_level表的数据量最小，应该作为驱动表：

sql

SELECT DISTINCT u.user_id, u.phone_number

FROM user_level l

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

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

WHERE l.level_name = 'VIP'

AND o.create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)

AND o.order_amount > 200;

最后，用EXPLAIN对比了优化前后的执行计划。优化前，orders表走了全表扫描，rows显示为两百万；优化后，user_level表作为驱动表，只扫描了三千行，然后通过索引关联到users和orders表，整体扫描行数控制在五万以内。

这次事故让我深刻理解了一个道理：JOIN不是越多越好，LEFT JOIN更不是万能的安全套。每多一个JOIN，查询复杂度就呈指数级上升。写SQL之前，一定要先想清楚各表的数据量级，以及它们之间的关联关系。

三、子查询的迷思：改写前后性能相差百倍

第三个故事和子查询有关。有一次，产品经理提了一个需求：找出那些购买过A商品、也购买过B商品、但没有购买过C商品的用户。这个需求听起来像绕口令，但业务场景很真实——我们要做交叉销售分析。

我最初用子查询来实现：

sql

SELECT DISTINCT user_id

FROM orders

WHERE user_id IN (

SELECT user_id FROM orders WHERE product_name = '商品A'

)

AND user_id IN (

SELECT user_id FROM orders WHERE product_name = '商品B'

)

AND user_id NOT IN (

SELECT user_id FROM orders WHERE product_name = '商品C'

);

这条语句在测试环境跑得还行，因为数据量只有几万条。但上了生产环境，面对百万级的订单数据，它直接卡死了。我用EXPLAIN分析，发现NOT IN子查询的执行计划极其糟糕，MySQL对每一行外层数据都要执行一次子查询，相当于嵌套循环。

我决定用EXISTS改写：

sql

SELECT DISTINCT o1.user_id

FROM orders o1

WHERE EXISTS (

SELECT 1 FROM orders o2

WHERE o2.user_id = o1.user_id

AND o2.product_name = '商品A'

)

AND EXISTS (

SELECT 1 FROM orders o3

WHERE o3.user_id = o1.user_id

AND o3.product_name = '商品B'

)

AND NOT EXISTS (

SELECT 1 FROM orders o4

WHERE o4.user_id = o1.user_id

AND o4.product_name = '商品C'

);

EXISTS的执行逻辑是：只要子查询能找到一条匹配记录就立即返回TRUE，不再继续扫描。配合user_id和product_name的联合索引，每条子查询都能在索引中快速定位，性能提升了近百倍。

但后来我发现，还有一种更优雅的写法，用GROUP BY配合HAVING条件：

sql

SELECT user_id

FROM orders

WHERE product_name IN ('商品A', '商品B', '商品C')

GROUP BY user_id

HAVING SUM(CASE WHEN product_name = '商品A' THEN 1 ELSE 0 END) > 0

AND SUM(CASE WHEN product_name = '商品B' THEN 1 ELSE 0 END) > 0

AND SUM(CASE WHEN product_name = '商品C' THEN 1 ELSE 0 END) = 0;

这种写法只需要扫描一次orders表，通过CASE WHEN在聚合时做条件判断，效率最高。我用EXPLAIN对比了三种写法，第三种在百万级数据量下，执行时间只有前两种的十分之一。

这次经历教会我：SQL不是只有一种写法，同样的需求，不同的实现方式，性能可能相差百倍。遇到复杂的条件筛选，不妨多想想能不能用GROUP BY加HAVING来解决，往往会有惊喜。

四、模糊查询的痛：LIKE '%keyword%' 真的无解吗

第四个故事发生在一个后台管理系统的搜索功能上。用户需要在文章列表里搜索标题包含某个关键词的文章，我最初写的SQL是这样的：

sql

SELECT article_id, title, author, publish_time

FROM articles

WHERE title LIKE '%数据库优化%'

ORDER BY publish_time DESC

LIMIT 20;

文章表有五十万条数据，这条查询每次都要全表扫描，耗时在三秒以上。产品经理拍着桌子说：“这个搜索太慢了，用户体验极差！”

我知道，LIKE以百分号开头会导致索引失效，这是MySQL的机制决定的，无法通过建普通索引来解决。但问题总要解决，我尝试了三种方案：

第一种方案是使用全文索引。MySQL从5.6版本开始，InnoDB引擎支持全文索引，对于中文搜索，需要配置ngram分词器：

sql

ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;

然后改写查询语句：

sql

SELECT article_id, title, author, publish_time

FROM articles

WHERE MATCH(title) AGAINST('数据库优化' IN BOOLEAN MODE)

ORDER BY publish_time DESC

LIMIT 20;

全文索引的查询速度非常快，五十万数据量下基本在0.1秒以内。但它的缺点是分词粒度不可控，对于短关键词的匹配效果不太理想。

第二种方案是引入Elasticsearch。我们在应用层做了改造，文章发布时同步写入ES，搜索时先查ES拿到文章ID列表，再回MySQL查详细信息。这种方案灵活性最高，支持复杂的搜索需求，但引入了额外的组件，增加了系统复杂度。

第三种方案是业务层面的妥协。和产品经理沟通后发现，用户其实很少搜单个字或两个字的短词，大部分搜索都是三字以上的词组。于是我们做了一个折中：在应用层对搜索关键词做判断，如果长度大于等于三个字，就用LIKE查询，同时在title字段上建了前缀索引；如果长度小于三个字，就提示用户输入更具体的关键词。

最终我们选择了方案一加方案三的组合，对于大部分场景用全文索引，对于短词搜索做业务限制。改造之后，搜索功能的平均响应时间从三秒降到了0.2秒，产品经理终于露出了满意的笑容。

五、那些年我总结的SQL调优军规

经历了这么多次事故，我逐渐形成了一套自己的SQL编写规范。这些规范不是教科书上的教条，而是用真金白银的故障换来的经验。

第一条军规：EXPLAIN是写SQL的标配，不是调优时才用的工具。我现在养成了一个习惯，任何要在生产环境执行的SQL，先在测试环境用EXPLAIN看一遍执行计划。重点关注type列，至少要做到range级别，最好能到ref。如果看到ALL，说明全表扫描了，必须想办法优化。

第二条军规：索引不是越多越好，但关键字段必须覆盖。我见过一个表建了十五个索引，每次写入都要更新所有索引，写入性能极差。后来分析发现，真正用到的只有三个。建索引的原则是：WHERE条件中的过滤字段、JOIN的关联字段、ORDER BY的排序字段，这三个优先考虑。对于复合索引，记住最左前缀原则，把区分度高的字段放在前面。

第三条军规：禁止SELECT *，字段按需索取。这个原则看似简单，但在实际开发中经常被忽略。SELECT *不仅增加了网络传输的数据量，更重要的是，它可能导致优化器放弃使用覆盖索引，转而回表查询，性能下降数倍。

第四条军规：大表查询必须带LIMIT。我见过最离谱的一次，一个开发同学在百万级的表上执行了一条不带LIMIT的SELECT，结果把数据库的连接池全部占满，导致整个服务不可用。从那以后，我在代码审查时，看到不带LIMIT的大表查询就直接打回。

第五条军规：WHERE条件中避免对字段做函数操作。比如WHERE YEAR(create_time) = 2023，这种写法会导致索引失效。正确的写法是WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。这个原则同样适用于隐式类型转换，比如字段是varchar类型，但传入的是数字，也会导致索引失效。

第六条军规：能用INNER JOIN就别用LEFT JOIN，能用EXISTS就别用IN。INNER JOIN比LEFT JOIN效率高，因为优化器有更多的优化空间。EXISTS比IN效率高，因为EXISTS是找到一条就返回，而IN需要构建完整的子查询结果集。

第七条军规：分批处理，避免长事务。对于UPDATE和DELETE操作，如果涉及的数据量很大，一定要分批执行，每批处理几千条，提交一次事务。我见过一个开发同学在一个事务里更新了两百万条数据，导致undo log暴涨，数据库性能急剧下降，最后不得不强制回滚。

六、写在最后

SQL调优这件事，没有一劳永逸的银弹。不同的数据量级、不同的业务场景、不同的数据库版本，最优的SQL写法可能完全不同。我上面分享的这些经验，都是基于MySQL 5.7和8.0版本，在百万到千万级数据量下的实践总结。

如果你现在问我，SQL调优最重要的是什么？我的回答是：第一，读懂执行计划；第二，理解索引原理；第三，知道数据量级。这三者缺一不可。

最后，我想说的是，SQL调优不是等到系统崩溃了才去做的紧急补救，而是应该贯穿在日常开发中的基本素养。每一条SQL在提交代码之前，都值得你多花五分钟，用EXPLAIN看一看，想一想有没有更好的写法。这五分钟，可能会在未来的某个凌晨两点，为你省下两个小时的紧急排障时间。

💡注意：本文所介绍的软件及功能均基于公开信息整理，仅供用户参考。在使用任何软件时，请务必遵守相关法律法规及软件使用协议。同时，本文不涉及任何商业推广或引流行为，仅为用户提供一个了解和使用该工具的渠道。

你在生活中时遇到了哪些问题？你是如何解决的？欢迎在评论区分享你的经验和心得！

希望这篇文章能够满足您的需求，如果您有任何修改意见或需要进一步的帮助，请随时告诉我！

感谢各位支持，可以关注我的个人主页，找到你所需要的宝贝。

博文入口：山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口：常用软件宝贝：精品文件

作者郑重声明，本文内容为本人原创文章，纯净无利益纠葛，如有不妥之处，请及时联系修改或删除。诚邀各位读者秉持理性态度交流，共筑和谐讨论氛围～