从全表扫描到毫秒响应：一次优化提升了150倍

山峰哥

344人浏览 · 2026-05-15 08:15:00

山峰哥 · 2026-05-15 08:15:00 发布

从全表扫描到毫秒响应：一次优化提升了150倍

上周三凌晨两点，手机突然炸了。线上核心接口响应时间直接飙到10秒，用户投诉像潮水一样涌进来。我睡眼惺忪地打开电脑，找到那条罪魁祸首的SQL，跑了一遍Explain，发现type是ALL——全表扫描，扫描了180万行。加了一个联合索引，10秒变30毫秒。这种事我这几年碰过太多次了，每次都让我更加坚信一句话：不会看Explain的开发者，写出来的SQL就是埋在系统里的定时炸弹。今天这篇文章不讲理论，全部拿真实的Explain执行计划做前后对比，让你一眼看清楚优化前后的差距到底有多大。

数据库工程与Explain执行计划对比优化实战

一、Explain到底是个什么东西，为什么它这么重要

很多开发者写SQL有个坏习惯：能跑就行，快不快再说。等出了问题再去排查，排查完也说不清为什么改了之后就快了。这种靠运气写代码的方式，在小项目里或许能混过去，但数据量一上来，必然翻车。

Explain是MySQL提供的一个命令，你在任何一条SELECT语句前面加上它，MySQL就不会真的去执行查询，而是告诉你它打算怎么执行这条SQL。换句话说，Explain就是MySQL给你开的一张"体检报告单"。它会告诉你这条SQL用了哪个索引、扫描了多少行、有没有做额外的排序、有没有建临时表。

☆ 我给自己定了一条铁规矩：任何一条要上线的SQL，必须先跑Explain。type低于range的，直接打回重写，没有例外。

这不是矫情，是血泪教训。我之前带过一个实习生，写了一条查询订单的SQL，测试环境数据量只有几千条，跑得飞快。上了生产之后数据量涨到两百万，直接把数据库CPU干到95%。我让他跑了一遍Explain，type是ALL，扫描了两百多万行。他自己看完都傻了。

二、Explain核心字段拆解：看懂这张表你就赢了一半

下面我用一个真实场景来逐个拆解Explain输出的关键字段。

假设我们有一张订单表，结构如下：

sql

CREATE TABLE orders (

id BIGINT PRIMARY KEY AUTO_INCREMENT,

user_id BIGINT NOT NULL COMMENT '用户ID',

status TINYINT NOT NULL COMMENT '订单状态：1待付款 2已付款 3已发货 4已完成',

amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',

create_time DATETIME NOT NULL COMMENT '创建时间',

INDEX idx_user_id (user_id),

INDEX idx_status (status),

INDEX idx_create_time (create_time)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

现在有一条查询语句，要查某个用户在2024年的已支付订单：

sql

SELECT id, order_no, amount, create_time

FROM orders

WHERE user_id = 10086

AND status = 2

AND create_time >= '2024-01-01 00:00:00';

跑一下Explain，结果如下：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE orders ALL idx_user_id,idx_status,idx_create_time NULL NULL 1850000 Using where

下面我把每个关键字段掰开了讲：

1、type：这是整张表中最核心的字段，代表MySQL访问表的方式。从最优到最差依次是：system > const > eq_ref > ref > range > index > ALL。上面这个例子是ALL，也就是全表扫描，最差的那一档。

2、possible_keys：优化器认为可以用到的索引。这里列了三个，但实际一个都没用上。

3、key：实际使用的索引。这里是NULL，说明没走任何索引。

4、rows：预估要扫描的行数。185万行，对于一条按user_id查询的语句来说，这个数字触目惊心。

5、Extra：额外信息。这里出现了Using where，说明MySQL在存储引擎层拿到数据之后，在服务层又做了一次过滤。如果出现Using filesort或Using temporary，那就更糟糕了，意味着额外做了排序或建了临时表。

☆ 实战中我有个快速判断标准：如果type不是range及以上，或者Extra里出现了Using filesort、Using temporary，这条SQL大概率有问题，必须动手优化。

三、优化前后Explain对比：差距大到让人头皮发麻

上面那条SQL为什么走了全表扫描？原因很简单：三个单列索引都无法完全匹配查询条件。user_id单独用可以，但加上status和create_time之后，优化器发现任何一个单列索引都覆盖不了全部条件，索性就不用了，直接全表扫描。

解决方案也很直接：建一个联合索引。

sql

ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

建完索引之后再跑Explain：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE orders range idx_user_status_time idx_user_status_time 27 8 Using index condition

把两次Explain放在一起对比：

对比项优化前优化后

type ALL（全表扫描） range（范围扫描）

key NULL idx_user_status_time

rows 1850000行 8行

Extra Using where Using index condition

从185万行降到8行，扫描行数减少了二十多万倍。实际响应时间从10秒降到了30毫秒以内。这就是Explain对比的威力——你不需要猜SQL为什么慢，Explain直接把答案拍在你脸上。

四、联合索引的最左前缀原则：这个搞不懂，索引白建

上面建的联合索引idx_user_status_time (user_id, status, create_time)之所以有效，靠的就是最左前缀原则。

这个原则的意思是：联合索引在匹配查询条件时，必须从最左边的字段开始依次匹配，一旦中间断了，后面的字段全部失效。

举个例子，索引是(user_id, status, create_time)：

1、查询条件有user_id，索引生效 ✅

2、查询条件有user_id + status，索引完全生效 ✅

3、查询条件有user_id + status + create_time，索引完美匹配 ✅

4、查询条件只有status，索引不生效 ❌

5、查询条件只有create_time，索引不生效 ❌

所以建联合索引的时候，字段顺序非常有讲究。一般原则是：把区分度最高的、最常出现在等值查询里的字段放在最左边。什么叫区分度高？user_id有几百万个不同的值，区分度就高；status只有4个值，区分度就很低。所以user_id放最前面是对的。

☆ 我的习惯是：建索引之前，先把这个表上最高频的十条SQL拉出来，看它们的WHERE条件里都有哪些字段，然后按最左前缀原则来设计索引。这样建出来的索引，命中率最高。

五、覆盖索引：让查询连回表都省了

还是上面那个例子，如果我的查询只需要返回user_id、status、create_time这几个字段：

sql

SELECT user_id, status, create_time

FROM orders

WHERE user_id = 10086

AND status = 2

AND create_time >= '2024-01-01 00:00:00';

这时候Explain的Extra字段会出现Using index，说明用了覆盖索引。什么意思？就是查询需要的所有字段都在索引树上，MySQL根本不需要回表去查聚簇索引，直接从二级索引上就把数据拿全了。

回表这个操作，很多人不当回事。InnoDB的二级索引里只存了索引字段的值和主键ID，真正的行数据在聚簇索引里。所以如果查询的字段不在二级索引里，MySQL就得拿着主键ID再去聚簇索引里查一遍，这就是回表。扫描1000行就要回表1000次，在高并发场景下这个开销非常可怕。

覆盖索引场景下的Explain结果：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE orders range idx_user_status_time idx_user_status_time 27 8 Using index condition; Using index

注意最后那个Using index，这就是覆盖索引的标志。看到它，就说明这条SQL已经优化到位了。

☆ 实战中我有个小技巧：写完SELECT之后，先看一眼查询的字段是不是都在联合索引里。如果不是，要么把字段加到索引里做成覆盖索引，要么想想这条查询是不是真的需要那么多字段。很多时候前端根本用不到那么多数据，是后端图省事一次全查出来了。

六、那些让索引失效的坑：建了索引也不一定快

很多人觉得建了索引就万事大吉了，其实不然。我在实际项目中遇到过太多"明明建了索引却没生效"的情况，下面这几个坑你一定要避开：

1、对索引字段使用函数：

sql

-- 索引失效，因为对create_time用了YEAR函数

WHERE YEAR(create_time) = 2024

-- 索引生效，直接用范围查询

WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'

2、隐式类型转换：

sql

-- user_id是BIGINT，这里传了字符串，索引失效

WHERE user_id = '10086'

-- 改成数字，索引生效

WHERE user_id = 10086

3、LIKE以通配符开头：

sql

-- 索引失效

WHERE nickname LIKE '%三'

-- 索引生效

WHERE nickname LIKE '张%'

4、使用NOT IN或!=：

sql

-- 索引大概率失效

WHERE status != 2

-- 改成IN，索引可以生效

WHERE status IN (1, 3, 4)

5、OR连接的条件中只要有一个字段没索引，就可能全表扫描：

sql

-- user_id有索引，但status没有，整个查询可能走全表扫描

WHERE user_id = 10086 OR status = 2

☆ 这些坑看起来简单，但在实际业务代码里出现的频率非常高。我的做法是写完SQL之后一定要跑一遍Explain确认索引确实被用到了。养成这个习惯，能帮你避免80%的性能问题。

七、一个完整的查询优化案例：从Explain对比到上线

最后讲一个我在上家公司做过的真实优化案例。

背景：我们有一个运营后台的报表接口，需要查询某个时间段内所有已完成订单的汇总数据。原始SQL如下：

sql

SELECT DATE(create_time) AS order_date,

COUNT(*) AS order_count,

SUM(amount) AS total_amount

FROM orders

WHERE status = 4

AND create_time >= '2024-01-01'

AND create_time < '2025-01-01'

GROUP BY DATE(create_time)

ORDER BY order_date DESC;

原始Explain结果：

id select_type table type possible_keys key rows Extra

1 SIMPLE orders ALL idx_status,idx_create_time NULL 2100000 Using where; Using temporary; Using filesort

问题非常严重：全表扫描210万行，还出现了Using temporary和Using filesort，说明MySQL又建了临时表又做了文件排序，性能差到了极点。

优化过程：

1、首先解决GROUP BY DATE(create_time)的问题。对create_time用了函数，索引肯定失效。把查询改成按天分组，利用索引的范围扫描：

sql

SELECT create_time,

COUNT(*) AS order_count,

SUM(amount) AS total_amount

FROM orders

WHERE status = 4

AND create_time >= '2024-01-01 00:00:00'

AND create_time < '2025-01-01 00:00:00'

GROUP BY create_time

ORDER BY create_time DESC;

2、建立联合索引：

sql

ALTER TABLE orders ADD INDEX idx_status_time_amount (status, create_time, amount);

3、优化后的Explain：

id select_type table type possible_keys key key_len rows Extra

1 SIMPLE orders range idx_status_time_amount idx_status_time_amount 12 8500 Using index condition

对比数据：

对比项优化前优化后

type ALL range

扫描行数 210万行 8500行

Using temporary 有无

Using filesort 有无

响应时间 12秒+ 80毫秒

从12秒降到80毫秒，提升了150倍。而且因为用了覆盖索引，连回表都省了。

☆ 这里有个细节很多人会忽略：BETWEEN '2024-01-01' AND '2024-12-31'实际上包含了2024-12-31 23:59:59这一秒的所有数据，但如果create_time字段有毫秒精度，可能会漏掉最后一秒的部分数据。用>=和<的写法更加严谨，也更容易让索引生效。