MySQL慢SQL最佳实践：索引设计与性能优化全指南

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

最近在帮公司做数据库巡检，发现一个有意思的现象：很多慢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

开启慢查询日志：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;  -- 超过2秒就记录

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

步骤二：看执行计划

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

EXPLAIN SELECT ...;

重点看这几个字段：

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：

UPDATE test.test SET trans_rec_status = '13' WHERE id IN (几千个ID...)

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

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

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

怎么优化：

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

问题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：

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;

创建复合索引：

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：

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的时候，心想"这不是杀鸡用牛刀嘛"……

优化方案：

方案一：利用主键随机

-- 先获取最大最小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;

方案二：建索引减少扫描基数

ALTER TABLE user ADD INDEX idx_carrier_status_iccid (Carrier, Status, ICCID);

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

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

问题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+支持）

-- 添加生成列
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：

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;

方案二：游标分页

-- 第一页
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：

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（业务上确认不会重复的话）：

SELECT ... FROM test c ... WHERE ...
UNION ALL
SELECT ... FROM test2 cga ... WHERE ...

创建索引：

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：

SELECT ... FROM test4 m ... JOIN test5 acm ...
WHERE acm.card_no IN ('0516201060473122', '0516201061224952', ... 约1万个卡号)

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

推荐方案：临时表JOIN

-- 创建临时表
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：

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。

优化：创建覆盖索引

ALTER TABLE test ADD INDEX idx_return_cover (return_code, return_state, trans_type, CHANNEL_RESP_ID, order_id);

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

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

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

问题SQL：

UPDATE test SET ... WHERE id = '1999857891477553154';

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

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

解决方案：

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是危险信号

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

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