一个逗号干翻整个系统，Explain一看我直接傻眼了

上周五晚上十一点多，我正准备关电脑走人，手机突然震了。运维发来一条消息："线上订单接口响应超过8秒，用户都在投诉。"我当时心里就咯噔一下，这种事十有八九又是数据库的锅。果不其然，打开慢查询日志一看，一条看起来特别普通的SQL，居然在全表扫描600多万行。更离谱的是，这个字段明明有索引，但Explain一跑，type显示ALL——索引压根没生效。后来排查了半个多小时，发现问题居然出在一个小小的逗号上。干了这么多年数据库开发，我最大的感触就是：SQL优化这事，真的不能靠感觉，得靠数据说话。今天我就拿三个亲身经历的真实案例，从索引设计到Explain分析，把查询优化这件事给你讲透。

数据库工程与查询优化案例实战

一、为什么查询优化这么重要，却总被人忽视

☆ 很多开发者写SQL有个习惯：先写出来，能跑就行，跑得慢再说。等数据量一上来，页面从200毫秒变成5秒，用户投诉满天飞，才开始慌。这时候大部分人的第一反应就是加索引，但说实话，加索引这事跟开盲盒差不多，你不知道加在哪、加什么类型，很可能加了等于白加。

☆ 我见过太多这种情况了：索引建了，但SQL写得有问题，优化器根本不走索引；或者索引建对了，但建的顺序不对，效果大打折扣。还有一种更隐蔽的情况——索引确实生效了，但因为查询写法不合理，依然慢得离谱。

☆ 所以查询优化这件事，说白了就是四个字：减少扫描。让数据库用最小的代价，找到你真正需要的那几行数据。你写的每一条SQL，数据库都会生成一个执行计划，决定它怎么去查数据。如果这个计划不合理，哪怕你建了再多索引也白搭。

☆ 所以在动手改任何东西之前，第一件事应该是跑一下EXPLAIN。这个命令会告诉你MySQL打算怎么执行这条SQL，用了哪个索引、扫描了多少行、有没有额外的排序开销，全都一清二楚。说白了，Explain就是SQL的体检报告，不看报告就动刀，跟闭着眼睛做手术没啥区别。

二、Explain到底该看什么，一张表给你讲明白

☆ Explain输出的结果有十几列，但真正需要重点盯着看的就那么四五个。我把这些年总结出来的核心指标整理成了下面这张表：

字段名 含义 怎么判断好坏

type 访问类型，决定查询效率 system > const > eq_ref > ref > range > index > ALL，至少要到range级别

possible_keys 可能用到的索引 有值比NULL强，说明优化器有选择余地

key 实际使用的索引 NULL说明没用上任何索引，必须优化

key_len 索引使用的字节长度 越长说明索引用得越完整

rows 预估扫描的行数 越少越好，最好是个位数或两位数

Extra 额外执行信息 出现Using filesort或Using temporary要高度警惕

☆ 上面这张表里，最最最核心的就是type和rows。type告诉你数据库用什么方式在扫数据，rows告诉你它打算扫多少行。这两个数字一看，基本就能判断这条SQL的性能处于什么水平。

☆ 举个例子，如果type是ALL，那就是全表扫描，这是最差的情况，必须优化。如果是ref或者range，说明用上了索引，已经及格了。如果是const或者eq_ref，那基本就是秒回级别，可以放心上线。

三、案例一：订单列表从5秒到3毫秒，就改了两行代码

1、问题是怎么发现的

☆ 去年我接手了一个电商项目的订单模块，用户反馈"我的订单"页面加载特别慢，动辄四五秒。我打开代码一看，SQL是这么写的：

sql

SELECT * FROM orders WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 20;

☆ orders表有将近600万条数据，user_id字段上压根没建索引。

2、问题出在哪

☆ 这条SQL至少犯了三个错误：

（1）用了SELECT *，把订单详情、备注、物流信息全拉出来了，但页面其实只需要订单号、金额、状态、时间这几个字段。

（2）user_id没有索引，MySQL只能全表扫描，600万行一行一行地比对user_id。

（3）虽然加了LIMIT 20，但数据库得先把所有符合条件的行全部找出来、排好序，然后才能取前20条。也就是说，LIMIT在这里几乎没起到作用。

☆ 我跑了一下Explain看看：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE orders ALL NULL NULL NULL 6000000 Using where; Using filesort

☆ 看到这张表我就知道完蛋了：type = ALL说明全表扫描，rows = 6000000说明要扫600万行，Extra里还出现了Using filesort，说明还要额外做文件排序。三重debuff叠满了，能不慢吗？

3、我是怎么优化的

☆ 第一步，建联合索引，把查询条件和排序字段都放进去：

sql

CREATE INDEX idx_user_time ON orders(user_id, create_time DESC);

☆ 为什么要把create_time也放进索引？因为查询里有ORDER BY create_time DESC，如果排序字段在索引里，数据库可以直接按索引顺序读取，根本不需要额外做文件排序。

☆ 第二步，把SELECT *改成只查需要的字段：

sql

SELECT order_id, create_time, total_amount, status

FROM orders

WHERE user_id = 10086

ORDER BY create_time DESC

LIMIT 20;

4、优化后的效果对比

对比项 优化前 优化后

扫描方式 全表扫描（type=ALL） 索引范围扫描（type=ref）

扫描行数 约600万行 20行

执行时间 4.8秒 0.003秒

是否文件排序 是（Using filesort） 否（索引已有序）

☆ 从4.8秒降到3毫秒，提升了1600倍。就改了两行代码、加了一个索引，效果就是这么炸裂。你说Explain重不重要？不看Explain，你可能花一整天都找不到问题在哪。

四、案例二：报表统计慢了20倍，换个写法直接起飞

1、问题描述

☆ 另一个项目有个运营后台，需要统计每个商品分类的销售总额。原始SQL长这样：

sql

SELECT c.category_name, SUM(o.total_amount) as total_sales

FROM categories c

LEFT JOIN orders o ON c.id = o.category_id

GROUP BY c.category_name;

☆ categories表大概300条数据，orders表有1200万条。这条SQL跑一次要将近10秒，运营的同事天天在群里催。

2、为什么慢

☆ 很多人一看"统计每个分类的总额"，第一反应就是JOIN加GROUP BY。逻辑上没毛病，但性能上是个大坑。

☆ LEFT JOIN会先把两张表做关联，orders有1200万行，关联之后中间结果集依然是1200万行级别。然后再对这1200万行做GROUP BY聚合，MySQL得开临时表、做文件排序，开销巨大。

☆ 我跑了一下Explain看看：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE c ALL NULL NULL NULL 300 Using temporary; Using filesort

1 SIMPLE o ALL NULL NULL NULL 12000000 Using where; Using join buffer

☆ 你看，orders表那边type = ALL，1200万行全表扫描，还用了join buffer。categories表那边也出现了Using temporary; Using filesort。两边都有问题。

3、优化方案：先聚合再关联

sql

SELECT c.category_name,

COALESCE(t.total_sales, 0) as total_sales

FROM categories c

LEFT JOIN (

SELECT category_id, SUM(total_amount) as total_sales

FROM orders

GROUP BY category_id

) t ON c.id = t.category_id;

☆ 核心思路特别简单：先在orders表内部完成聚合，把1200万行压缩成几百行（每个分类一行），然后再和categories表关联。这样JOIN的数据量就从1200万降到了几百。

4、优化后的Explain

id select_type table type possible_keys key key_len rows Extra

1 PRIMARY c ALL NULL NULL NULL 300 —

1 PRIMARY ref 5 1 —

2 DERIVED orders index NULL idx_category 5 450 Using index

☆ orders表那边type = index，只扫描了450行，而且Extra显示Using index，说明走了覆盖索引。categories表只有300行，影响可以忽略。

5、效果对比

对比项 直接JOIN写法 子查询先聚合写法

JOIN时的数据量 约1200万行 约450行

是否使用临时表 是 否

执行时间 9.8秒 0.45秒

性能提升倍数 基准 约22倍

☆ 同样的业务结果，换一种写法，性能直接提升二十多倍。所以说，不要迷信JOIN，有时候把子查询用对了，比JOIN快得多。这个案例我印象特别深，因为当时运营的同事都惊了，说怎么改了几行SQL就快了这么多。

五、案例三：一个逗号引发的惨案，索引建了但没生效

1、问题背景

☆ 有一次线上出现慢查询告警，SQL看着特别简单：

sql

SELECT * FROM user_log WHERE action = 'login' AND log_time > '2024-01-01';

☆ user_log表大概400万行，action和log_time我都建了索引。但这条SQL跑一次要6秒多，我觉得不对劲，就跑了一下Explain：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE user_log ALL NULL NULL NULL 4000000 Using where

☆ 我一看就懵了：两个字段都有索引，怎么type还是ALL？possible_keys居然是NULL？这不科学啊。

2、真相大白

☆ 我仔细看了一下SQL，发现问题了。原始SQL里WHERE条件的两个字段之间，用的是中文逗号，不是英文逗号：

sql

-- 错误写法（中文逗号）

WHERE action = 'login'，log_time > '2024-01-01'

-- 正确写法（英文逗号）

WHERE action = 'login' AND log_time > '2024-01-01'

☆ 因为用了中文逗号，MySQL把整个条件当成了一个字符串字面量，根本没当成WHERE子句来解析。所以索引完全失效，直接全表扫描。

☆ 说出来你可能不信，就这么一个标点符号的问题，导致400万行全表扫描，性能差了上千倍。这种坑你不跑Explain根本发现不了，光看SQL语句是看不出问题的。

3、优化后的Explain

sql

-- 修正后的SQL

SELECT * FROM user_log

WHERE action = 'login' AND log_time > '2024-01-01';

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE user_log range idx_action,idx_time idx_action 767 150 Using index condition

☆ type从ALL变成了range，rows从400万降到了150。就因为把一个中文逗号改成了英文AND，性能提升了两万多倍。

六、索引策略：不是越多越好，要建在刀刃上

1、一个常见的坑

☆ 我见过不少团队，觉得索引是万能药，恨不得给每个字段都建一个。结果呢？写入性能暴跌，因为每次INSERT、UPDATE、DELETE都要同步更新所有索引。而且索引太多，优化器反而会选错索引，性能更差。

2、几条实战中总结出来的索引原则

（1）优先给WHERE、JOIN、ORDER BY、GROUP BY里出现的字段建索引。这是最基本的，也是最有效的。你写SQL的时候经常用到哪些字段做条件、做排序，就优先给这些字段建索引。

（2）联合索引一定要注意最左前缀原则。比如你建了(a, b, c)的联合索引，那么WHERE a=1能用上索引，WHERE a=1 AND b=2也能用上，但如果你只写WHERE b=2，索引就完全废了。所以建联合索引的时候，一定要把最常用的、区分度最高的字段放在最左边。

（3）区分度低的字段别建索引。比如"性别"字段，只有男和女两个值，建了索引也没啥用，因为数据库扫描完索引还是得回表查大量数据。但像"身份证号""手机号"这种区分度极高的字段，建索引效果立竿见影。

（4）能用覆盖索引就别回表。所谓覆盖索引，就是索引本身就包含了查询需要的所有字段，数据库根本不用去查原始数据行。

3、覆盖索引长什么样

sql

-- 建一个覆盖索引

CREATE INDEX idx_cover ON orders(user_id, create_time, order_id, total_amount);

-- 这条SQL可以完全走覆盖索引，不需要回表

SELECT order_id, create_time, total_amount

FROM orders

WHERE user_id = 10086

ORDER BY create_time DESC

LIMIT 20;

☆ 你用Explain看一下，Extra里会出现Using index，这就是覆盖索引的标志，意味着这条SQL的性能已经拉满了。数据库连数据行都不用访问，直接在索引里就把结果返回了。

七、写在最后

☆ SQL优化这件事，说穿了就三板斧：看Explain、建对索引、写好SQL。但就是这三板斧，很多人用了好几年都没用明白。

☆ 我的建议是养成一个习惯：每次写完SQL，先跑一下EXPLAIN看看。不要等出了问题再去排查，那时候往往已经火烧眉毛了。

☆ 记住几个关键信号：type = ALL是全表扫描，必须优化；Extra里出现Using filesort或Using temporary，说明有额外开销；rows超过一千，就该警惕了。

☆ 数据库优化不是一次性的活，是个持续的过程。业务在变，数据量在涨，今天的最优解可能半年后就不够用了。所以定期review慢查询日志、定期检查索引使用情况，这个习惯比什么都值钱。

☆ 希望今天这几个案例能帮你在实际工作中少踩点坑。如果你也有什么SQL优化的血泪故事，欢迎在评论区聊聊，咱们一起交流。

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

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

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

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

博文入口：https://blog.csdn.net/Start_mswin 复制到【浏览器】打开即可,宝贝入口：https://pan.quark.cn/s/b42958e1c3c0 宝贝：https://pan.quark.cn/s/1eb92d021d17

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

📋 复制整篇文章