别再说SQL简单了这五个优化技巧至少值二十万年薪不知道你有没有过这样的经历凌晨两点手机突然疯狂震动运维同事在群里你“线上数据库CPU飙到100%整个交易系统挂了快看看是不是你的SQL有问题”你从睡梦中惊醒一身冷汗手忙脚乱地打开电脑面对屏幕上那条跑了一个小时还没出结果的SQL语句大脑一片空白。我在数据库行业摸爬滚打了十年这样的场景经历了不下二十次。每一次都是血与泪的教训每一次都在倒逼我重新审视那些看似简单、实则暗藏杀机的SQL语句。今天我不想给你罗列一堆枯燥的优化法则而是想带你走进我亲身经历的几个真实事故现场看看那些让系统崩溃的SQL到底长什么样以及我是如何一步步把它们从“性能杀手”改造成“优雅跑车”的。一、一个索引引发的血案从两小时到0.03秒先从我职业生涯中最惨痛的一次事故说起。那是2019年双十一的前一天晚上我们正在做最后的压力测试。订单表里大约有八百万条数据业务方需要导出过去一年所有已完成订单的明细用于财务对账。我写了一条看起来人畜无害的SQLsqlSELECT order_id, customer_name, order_amount, create_timeFROM ordersWHERE order_status 已完成AND create_time 2018-11-01AND create_time 2019-11-01ORDER BY create_time DESC;这条语句提交之后数据库连接池瞬间被打满其他正常的交易请求全部阻塞。监控大屏上数据库服务器的CPU使用率曲线像坐火箭一样直冲100%。我当时的后背瞬间就湿透了。问题出在哪里我用EXPLAIN一看发现这条查询走了全表扫描扫描了全部八百万行数据。原来orders表上虽然有一个create_time的索引但order_status字段没有索引而MySQL的优化器在评估之后错误地选择了全表扫描。当时的紧急处理方案是强制使用索引sqlSELECT order_id, customer_name, order_amount, create_timeFROM orders FORCE INDEX(idx_create_time)WHERE order_status 已完成AND create_time 2018-11-01AND create_time 2019-11-01ORDER BY create_time DESC;但这只是临时止血根本问题没有解决。事后复盘我做了三件事第一创建了一个联合索引。order_status和create_time的联合索引把过滤性更好的order_status放在前面sqlALTER TABLE orders ADD INDEX idx_status_time (order_status, create_time);第二用EXPLAIN再次验证执行计划。这次显示type为rangekey为idx_status_time扫描行数从八百万降到了五十万。第三进一步优化查询逻辑。与业务方沟通后发现财务对账其实不需要实时数据完全可以走离线报表。于是我们搭建了读写分离架构把这类统计查询全部路由到只读从库主库只负责在线交易。改造之后同样的查询耗时从两小时降到了0.03秒。那天晚上我坐在电脑前看着执行计划里那个漂亮的ref类型心里只有一个念头索引不是建了就完事你得知道优化器是怎么选的以及为什么它会选错。二、JOIN的陷阱一条SQL拖垮整个数据库第二次重大事故发生在2021年春天。我们上线了一个新的营销活动需要给符合条件的用户推送优惠券。活动规则比较复杂过去三十天内有下单记录、且订单金额超过200元、且用户等级为VIP的用户才能参与活动。我写了这样一条SQL来筛选用户sqlSELECT DISTINCT u.user_id, u.phone_numberFROM users uLEFT JOIN orders o ON u.user_id o.user_idLEFT JOIN user_level l ON u.user_id l.user_idWHERE o.create_time DATE_SUB(NOW(), INTERVAL 30 DAY)AND o.order_amount 200AND l.level_name VIP;这条语句一执行数据库立刻开始剧烈抖动。我赶紧用SHOW PROCESSLIST查看发现这条查询的状态一直是“Sending data”而且已经跑了快十分钟了。问题出在LEFT JOIN上。我用了两个LEFT JOIN但WHERE条件里又对右表做了过滤这实际上把LEFT JOIN变成了INNER JOIN的效果但MySQL优化器在处理时仍然会先生成笛卡尔积再过滤数据量一大就直接爆炸。我当时的改造思路是这样的首先把LEFT JOIN改成INNER JOIN明确告诉优化器我们只需要匹配的数据sqlSELECT DISTINCT u.user_id, u.phone_numberFROM users uINNER JOIN orders o ON u.user_id o.user_idINNER JOIN user_level l ON u.user_id l.user_idWHERE o.create_time DATE_SUB(NOW(), INTERVAL 30 DAY)AND o.order_amount 200AND l.level_name VIP;其次调整JOIN的顺序。小表驱动大表是基本原则user_level表的数据量最小应该作为驱动表sqlSELECT DISTINCT u.user_id, u.phone_numberFROM user_level lINNER JOIN users u ON l.user_id u.user_idINNER JOIN orders o ON u.user_id o.user_idWHERE l.level_name VIPAND 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商品的用户。这个需求听起来像绕口令但业务场景很真实——我们要做交叉销售分析。我最初用子查询来实现sqlSELECT DISTINCT user_idFROM ordersWHERE 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改写sqlSELECT DISTINCT o1.user_idFROM orders o1WHERE EXISTS (SELECT 1 FROM orders o2WHERE o2.user_id o1.user_idAND o2.product_name 商品A)AND EXISTS (SELECT 1 FROM orders o3WHERE o3.user_id o1.user_idAND o3.product_name 商品B)AND NOT EXISTS (SELECT 1 FROM orders o4WHERE o4.user_id o1.user_idAND o4.product_name 商品C);EXISTS的执行逻辑是只要子查询能找到一条匹配记录就立即返回TRUE不再继续扫描。配合user_id和product_name的联合索引每条子查询都能在索引中快速定位性能提升了近百倍。但后来我发现还有一种更优雅的写法用GROUP BY配合HAVING条件sqlSELECT user_idFROM ordersWHERE product_name IN (商品A, 商品B, 商品C)GROUP BY user_idHAVING SUM(CASE WHEN product_name 商品A THEN 1 ELSE 0 END) 0AND SUM(CASE WHEN product_name 商品B THEN 1 ELSE 0 END) 0AND SUM(CASE WHEN product_name 商品C THEN 1 ELSE 0 END) 0;这种写法只需要扫描一次orders表通过CASE WHEN在聚合时做条件判断效率最高。我用EXPLAIN对比了三种写法第三种在百万级数据量下执行时间只有前两种的十分之一。这次经历教会我SQL不是只有一种写法同样的需求不同的实现方式性能可能相差百倍。遇到复杂的条件筛选不妨多想想能不能用GROUP BY加HAVING来解决往往会有惊喜。四、模糊查询的痛LIKE %keyword% 真的无解吗第四个故事发生在一个后台管理系统的搜索功能上。用户需要在文章列表里搜索标题包含某个关键词的文章我最初写的SQL是这样的sqlSELECT article_id, title, author, publish_timeFROM articlesWHERE title LIKE %数据库优化%ORDER BY publish_time DESCLIMIT 20;文章表有五十万条数据这条查询每次都要全表扫描耗时在三秒以上。产品经理拍着桌子说“这个搜索太慢了用户体验极差”我知道LIKE以百分号开头会导致索引失效这是MySQL的机制决定的无法通过建普通索引来解决。但问题总要解决我尝试了三种方案第一种方案是使用全文索引。MySQL从5.6版本开始InnoDB引擎支持全文索引对于中文搜索需要配置ngram分词器sqlALTER TABLE articles ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;然后改写查询语句sqlSELECT article_id, title, author, publish_timeFROM articlesWHERE MATCH(title) AGAINST(数据库优化 IN BOOLEAN MODE)ORDER BY publish_time DESCLIMIT 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博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围