大家好，我是数据库小学妹 👋

最近在帮公司做数据库巡检，发现一个有意思的现象：很多慢SQL问题其实都踩在同样的坑里。有的UPDATE语句能跑5分钟，有的报表查询扫了几千万行数据，还有的统计SQL动不动就卡住……

说真的，我第一次看到这些案例的时候，自己也踩过类似的坑。今天就结合这些真实案例，聊聊怎么优化慢SQL，希望能帮大家少走点弯路~

## 一、先搞懂原理：SQL为什么会变慢？

动手优化之前，得先明白MySQL怎么执行一条SQL。这个就像修车之前得先懂发动机原理一样~

### 1.1 MySQL执行一条SQL的旅程

一条SQL从发出去到返回结果，要经过这些环节：

```
客户端 → 连接器 → 分析器 → 优化器 → 执行器 → 返回结果
```

对性能影响最大的是优化器和执行器。

优化器就像个参谋，负责决定"怎么查"：选哪个索引、表按什么顺序连接、要不要用临时表排序。执行器就按优化器的计划去干活，可以理解成执行者。

SQL变慢通常有这几个原因：

1. 扫描了太多无关数据（比如没建索引导致全表扫描）
2. 生成了巨大的临时表（比如GROUP BY没索引）
3. 锁等待（比如长事务占着锁不放）
4. 优化器选错了计划（统计信息不准）

### 1.2 索引为什么能让查询变快？

MySQL最常用的索引结构是B+树，这个你可以把它想象成字典的目录。

等值查询（WHERE id = 100）就像直接翻到对应页码，3~4次就能找到。范围查询（WHERE create_time BETWEEN ... AND ...）就像找到起点，顺着目录往后翻。覆盖索引更省事，查询字段都在索引里，连回表都省了。

但索引不是万能的，这些情况会让索引失效：
- 对索引列用函数（比如WHERE YEAR(create_time) = 2024）
- 隐式类型转换（字符串字段和数字比较）
- 左模糊匹配（LIKE '%xx'）
- 否定条件（!=、NOT IN）

后面案例里大部分问题都是因为这些原因，我当初踩坑也是因为这个~

### 1.3 JOIN连接是怎么工作的？

MySQL执行表连接用的是嵌套循环连接算法，简单说就是：

```
for each row in 驱动表 {
    for each row in 被驱动表 where 连接条件匹配 {
        输出结果
    }
}
```

JOIN优化的关键：驱动表要小，被驱动表要有索引。

很多慢查询就是因为被驱动表没建索引，扫描行数飙到千万级别。我之前就遇到过一个case，加了索引之后从30秒变成0.1秒，效果立竿见影！

## 二、慢SQL优化方法论：跟着步骤走

遇到慢SQL不要慌，按这个流程来，保证你不会乱~

### 步骤一：找到问题SQL

开启慢查询日志：
```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过2秒就记录
```

用pt-query-digest或mysqldumpslow分析慢日志，找出最慢的SQL。也可以用`SHOW FULL PROCESSLIST`看看当前正在跑什么。

### 步骤二：看执行计划

用`EXPLAIN`命令看看MySQL打算怎么执行：

```sql
EXPLAIN SELECT ...;
```

重点看这几个字段：

| 字段 | 含义 | 怎么判断 |
| --- | --- | --- |
| `type` | 访问类型 | system > const > eq_ref > ref > range > index > ALL，越靠后越慢 |
| `key` | 用的哪个索引 | 没用索引就要注意了 |
| `rows` | 扫描行数 | 数字越大越危险 |
| `Extra` | 额外信息 | Using filesort、Using temporary 都是坏消息 |

`type`是ALL（全表扫描）或者`rows`特别大，基本就是索引的问题啦~

### 步骤三：分析问题根源

对着SQL和表结构检查：
- WHERE条件的列有没有索引？
- 索引列上用了函数吗？
- 有没有隐式类型转换？
- JOIN时被驱动表的连接列有索引吗？
- ORDER BY、GROUP BY的列在索引里吗？
- 是不是用了SELECT *导致没法用覆盖索引？
- 分页的OFFSET是不是太大了？

### 步骤四：实施优化

- 加索引：针对WHERE、JOIN、ORDER BY创建复合索引
- 改写SQL：子查询改JOIN，UNION改UNION ALL
- 分批处理：大事务拆成小事务
- 用生成列：函数操作存成列再建索引

### 步骤五：验证效果

改完用EXPLAIN确认执行计划变了，测试环境跑一下看看效果。别急着上线，先验证一下~

## 三、实战案例：手把手教你优化

### 案例1：超大IN列表的UPDATE——锁住一切

**问题SQL**：
```sql
UPDATE test.test SET trans_rec_status = '13' WHERE id IN (几千个ID...)
```

**症状**：执行时间212秒和532秒，锁住大量行，其他操作全被阻塞。

**为什么会这样**：IN列表太长，MySQL解析起来很累。一次性更新几千行，锁累积在内存里。大事务还会撑大undo日志。

我第一次遇到这个问题的时候，还以为是数据库慢，后来才发现是SQL写得有问题……

**怎么优化**：

| 方案 | 做法 | 优点 | 缺点 |
| --- | --- | --- | --- |
| 分批处理（推荐） | 每次更新100~200个ID | 锁影响小，简单 | 总耗时可能略长 |
| 临时表JOIN | ID存到临时表再JOIN更新 | 执行快 | 步骤多一点 |
| 业务层优化 | 看看为什么一次要更新这么多 | 根本解决 | 可能要改业务逻辑 |

### 案例2：三表JOIN扫描1300万行只返回15万行

**问题SQL**：
```sql
SELECT ... FROM `user` u, `user_test` a, customer c 
WHERE a.UserID = u.UserID AND c.CustID = u.CustID 
AND u.`Status` <> 99 AND a.ExpireTS < DATE_SUB(CURDATE(), INTERVAL 80 DAY) 
AND c.`Name` NOT LIKE '%test%' AND u.CreateTS < DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
LIMIT 150001;
```

**EXPLAIN结果**：type是ALL（全表扫描），rows 1300万+，Extra显示Using where; Using join buffer (Block Nested Loop)。

**问题诊断**：用的是逗号连接，优化器不好生成高效计划。三张表都没建合适的复合索引。`c.Name NOT LIKE '%test%'`前导通配符让索引失效。

**优化步骤**：

改成显式INNER JOIN：
```sql
SELECT ... FROM `user` u
INNER JOIN `user_test` a ON u.UserID = a.UserID 
INNER JOIN `customer` c ON u.CustID = c.CustID
WHERE u.`Status` <> 99 
AND u.CreateTS < DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
AND a.ExpireTS < DATE_SUB(CURDATE(), INTERVAL 80 DAY)
AND c.`Name` NOT LIKE '%test%'
LIMIT 150001;
```

创建复合索引：
```sql
ALTER TABLE user_test ADD INDEX idx_userid_expirets (UserID, ExpireTS);
ALTER TABLE user ADD INDEX idx_userid_custid_createts_status (UserID, CustID, CreateTS, Status);
ALTER TABLE customer ADD INDEX idx_custid_name (CustID, Name);
```

NOT LIKE '%test%'如果必须用，可以考虑全文索引或者加个标记字段。

**效果**：扫描行数从1300万降到几万，查询时间从30秒降到毫秒级。这个优化效果真的太明显了~

### 案例3：ORDER BY RAND()——扫描1100万行取50条

**问题SQL**：
```sql
SELECT SUBSTRING(ICCID FROM 9 FOR 2) ICCIDGroup, u.*
FROM user u, user_test v 
WHERE u.UserID = v.UserID AND u.Carrier = '150' 
AND u.`Status` != 99 AND v.ExpireTS > NOW() 
AND SUBSTRING(ICCID FROM 9 FOR 2) = '09'
ORDER BY rand() LIMIT 50;
```

**症状**：每2~5分钟执行一次，累计417次，每次扫描1143万行。

**为什么会这样**：ORDER BY rand()要给所有符合条件的行生成随机数，放进临时表，全排序，最后返回前50行。为了取50条数据，MySQL要处理1100万行。数据量大的时候临时表还会写磁盘，更慢。

我第一次看到这个SQL的时候，心想"这不是杀鸡用牛刀嘛"……

**优化方案**：

方案一：利用主键随机
```sql
-- 先获取最大最小ID
SELECT MIN(id), MAX(id) INTO @min_id, @max_id FROM user WHERE ...条件...;

-- 随机取50个
SELECT ... FROM user u 
INNER JOIN user_test v ON u.UserID = v.UserID 
WHERE u.id >= (SELECT FLOOR(RAND() * (@max_id - @min_id) + @min_id))
AND ...其他条件...
ORDER BY u.id LIMIT 50;
```

方案二：建索引减少扫描基数
```sql
ALTER TABLE user ADD INDEX idx_carrier_status_iccid (Carrier, Status, ICCID);
```

SUBSTRING函数还是会导致部分索引失效，但前缀能先过滤掉大量数据。

### 案例4：大偏移量分页——LIMIT 164000, 1000

**问题SQL**：
```sql
SELECT DISTINCT MERCHANT_CODE, CUST_ID, MERCHANT_NAME FROM test b 
WHERE MANAGE_TAG = '00' AND SUBSTR(b.CUST_ID, -1) = '9'
LIMIT 164000, 1000;
```

**问题分析**：MySQL处理LIMIT M, N的方式是先扫描M+N行，丢掉前M行，返回后N行。M=164000时，MySQL要扫描并丢弃16.4万行，全是无效开销。SUBSTR函数导致索引失效，每次都要全表扫描。

**优化方案**：

方案一：生成列 + 索引（MySQL 5.7+支持）
```sql
-- 添加生成列
ALTER TABLE test ADD COLUMN cust_id_last_char CHAR(1) 
GENERATED ALWAYS AS (SUBSTR(CUST_ID, -1)) STORED;

-- 创建复合索引
ALTER TABLE test ADD INDEX idx_manage_cust_char_sn (MANAGE_TAG, cust_id_last_char, SN);
```

改写后的SQL：
```sql
SELECT DISTINCT MERCHANT_CODE, CUST_ID, MERCHANT_NAME FROM test 
WHERE MANAGE_TAG = '00' AND cust_id_last_char = '9'
ORDER BY SN DESC LIMIT 1000;
```

方案二：游标分页
```sql
-- 第一页
SELECT ... WHERE ... ORDER BY SN DESC LIMIT 1000;

-- 后续页，传入上一页最后一条的SN值
SELECT ... WHERE ... AND SN < @last_sn ORDER BY SN DESC LIMIT 1000;
```

这种方式不管翻到第几页，扫描行数都稳定在1000行左右，性能杠杠的！

### 案例5：UNION去重导致9小时长查询

**问题SQL**：
```sql
SELECT ... FROM test c ... WHERE c.last_modify_time >= CURDATE() - 2 AND c.last_modify_time < CURDATE()
UNION
SELECT ... FROM test2 cga ... WHERE cga.last_modify_time >= CURDATE() - 2 AND cga.last_modify_time < CURDATE()
```

**症状**：查询时间32426秒（约9小时），扫描行数6500万+，返回行数20499。

**为什么会这样**：UNION默认会去重，需要全排序或哈希临时表。last_modify_time字段没索引，全表扫描。其实两个子查询的结果根本不会重复。

我第一次看到9小时的查询时间，还以为看错了……

**优化步骤**：

改成UNION ALL（业务上确认不会重复的话）：
```sql
SELECT ... FROM test c ... WHERE ...
UNION ALL
SELECT ... FROM test2 cga ... WHERE ...
```

创建索引：
```sql
ALTER TABLE test ADD INDEX idx_last_modify (last_modify_time, cons_id, addr_id);
ALTER TABLE test2 ADD INDEX idx_last_modify (last_modify_time, addr_id);
```

**效果**：从9小时降到几秒。这个优化效果真的太夸张了~

### 案例6：超长IN列表的SELECT

**问题SQL**：
```sql
SELECT ... FROM test4 m ... JOIN test5 acm ...
WHERE acm.card_no IN ('0516201060473122', '0516201061224952', ... 约1万个卡号)
```

**症状**：执行585次，平均耗时33秒，最长255秒。

**推荐方案：临时表JOIN**
```sql
-- 创建临时表
CREATE TEMPORARY TABLE temp_card_nos (card_no VARCHAR(50) PRIMARY KEY);

-- 批量插入
INSERT INTO temp_card_nos VALUES ('0516201060473122'), ('0516201061224952'), ...;

-- JOIN查询
SELECT ... FROM test4 m 
JOIN test3 p ON m.mp_id = p.mp_id 
JOIN test c ON p.cons_id = c.cons_id 
JOIN test5 acm ON m.meter_id = acm.gm_id 
JOIN test2 cga ON c.addr_id = cga.addr_id 
JOIN temp_card_nos t ON acm.card_no = t.card_no
WHERE m.factory_number IS NOT NULL;
```

**为什么这样更快**：临时表有主键索引，优化器会选它当驱动表，避免了超长IN列表的解析开销。频繁执行的话可以改成普通表。

### 案例7：索引失效——隐式类型转换

**问题SQL**：
```sql
SELECT ... FROM test w
WHERE w.return_code = '0001' AND w.return_state = '01' 
AND w.trans_type IN ('0','1') AND w.CHANNEL_RESP_ID IS NOT NULL
ORDER BY order_id;
```

**症状**：每次扫描36万行，耗时7秒。

**问题定位**：没有索引能同时覆盖WHERE和ORDER BY。

**优化：创建覆盖索引**
```sql
ALTER TABLE test ADD INDEX idx_return_cover (return_code, return_state, trans_type, CHANNEL_RESP_ID, order_id);
```

**索引列顺序设计原则**：等值条件列在前，范围条件列居中，排序列放最后。

这样MySQL就能直接用索引定位数据，还天然有序，省掉排序。我一般会画个图来理解这个顺序~

### 案例8：没主键导致UPDATE锁等待51秒

**问题SQL**：
```sql
UPDATE test SET ... WHERE id = '1999857891477553154';
```

**症状**：平均耗时51秒，其中锁等待就占了51秒。

**根本原因**：id字段没主键或唯一索引，MySQL只能全表扫描并加锁。

**解决方案**：
```sql
ALTER TABLE test ADD PRIMARY KEY (id);
```

加了主键后，锁等待降到毫秒级。这个是最基础的，但真的很多人会忽略~

## 四、总结：慢SQL优化黄金法则

好啦，总结一下今天的内容：

1. **索引是基石，但要合理设计**
   - 复合索引列顺序：等值在前，范围在后，排序最后
   - 避免索引列用函数或隐式转换
   - 尽量用覆盖索引

2. **小结果集驱动大结果集**
   - JOIN时让行数少的表当驱动表
   - 被驱动表连接字段必须有索引

3. **分而治之**
   - 大操作拆成小操作
   - UNION改UNION ALL
   - 大分页改游标分页

4. **警惕隐蔽杀手**
   - ORDER BY RAND() → 程序随机或主键随机
   - 超长IN列表 → 临时表JOIN
   - LIKE '%xx' → 全文索引或冗余字段

5. **善用生成列**
   - 函数计算结果存成列再建索引

6. **永远先看执行计划**
   - EXPLAIN是你的眼睛
   - type=ALL和巨大rows是危险信号

---

今天的分享就到这里啦~ 这些都是我踩坑总结出来的经验，希望对朋友有帮助！

有问题欢迎在评论区讨论，我是数据库小学妹，咱们下篇见 👋


最近在帮公司做数据库巡检，发现一个有意思的现象：很多慢SQL问题其实都踩在同样的坑里。有的UPDATE语句能跑5分钟，有的报表查询扫了几千万行数据，还有的统计SQL动不动就卡住……

MySQL慢SQL优化实战：深入理解执行计划与索引优化策略

结合企业生产环境真实案例，从原理到实战讲解MySQL慢SQL优化方法，涵盖索引失效、JOIN优化、分页优化、UNION去重等8个典型场景，附完整解决方案。

数据库

学习MySQL慢SQL优化技巧，掌握索引原理、JOIN优化、分页处理等实战案例，解决UPDATE锁等待、超大IN列表、ORDER BY RAND()等常见性能问题，提升数据库查询效率。

代码解释

MySQL

腾讯云数据库特惠，新用户首单0.5折起


腾讯云BI特惠9.9元起！


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

MySQL慢SQL优化实战：深入理解执行计划与索引优化策略-腾讯云开发者社区-腾讯云

MySQL慢SQL优化实战：深入理解执行计划与索引优化策略

MySQL慢SQL优化实战：深入理解执行计划与索引优化策略

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐