MySQL慢SQL优化实战指南：EXPLAIN分析+索引优化+8个真实案例

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

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

开启慢查询日志：

SETGLOBALslow_query_log=ON;SETGLOBALlong_query_time=2;-- 超过2秒就记录

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

步骤二：看执行计划

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

EXPLAINSELECT...;

重点看这几个字段：

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：

UPDATEtest.testSETtrans_rec_status='13'WHEREidIN(几千个ID...)

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

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

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

怎么优化：

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

问题SQL：

SELECT...FROM`user`u,`user_test`a,customer cWHEREa.UserID=u.UserIDANDc.CustID=u.CustIDANDu.`Status`<>99ANDa.ExpireTS<DATE_SUB(CURDATE(),INTERVAL80DAY)ANDc.`Name`NOTLIKE'%test%'ANDu.CreateTS<DATE_SUB(CURDATE(),INTERVAL12MONTH)LIMIT150001;

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

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

优化步骤：

改成显式INNER JOIN：

SELECT...FROM`user`uINNERJOIN`user_test`aONu.UserID=a.UserIDINNERJOIN`customer`cONu.CustID=c.CustIDWHEREu.`Status`<>99ANDu.CreateTS<DATE_SUB(CURDATE(),INTERVAL12MONTH)ANDa.ExpireTS<DATE_SUB(CURDATE(),INTERVAL80DAY)ANDc.`Name`NOTLIKE'%test%'LIMIT150001;

创建复合索引：

ALTERTABLEuser_testADDINDEXidx_userid_expirets(UserID,ExpireTS);ALTERTABLEuserADDINDEXidx_userid_custid_createts_status(UserID,CustID,CreateTS,Status);ALTERTABLEcustomerADDINDEXidx_custid_name(CustID,Name);

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

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

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

问题SQL：

SELECTSUBSTRING(ICCIDFROM9FOR2)ICCIDGroup,u.*FROMuseru,user_test vWHEREu.UserID=v.UserIDANDu.Carrier='150'ANDu.`Status`!=99ANDv.ExpireTS>NOW()ANDSUBSTRING(ICCIDFROM9FOR2)='09'ORDERBYrand()LIMIT50;

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

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

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

优化方案：

方案一：利用主键随机

-- 先获取最大最小IDSELECTMIN(id),MAX(id)INTO@min_id,@max_idFROMuserWHERE...条件...;-- 随机取50个SELECT...FROMuseruINNERJOINuser_test vONu.UserID=v.UserIDWHEREu.id>=(SELECTFLOOR(RAND()*(@max_id-@min_id)+@min_id))AND...其他条件...ORDERBYu.idLIMIT50;

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

ALTERTABLEuserADDINDEXidx_carrier_status_iccid(Carrier,Status,ICCID);

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

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

问题SQL：

SELECTDISTINCTMERCHANT_CODE,CUST_ID,MERCHANT_NAMEFROMtest bWHEREMANAGE_TAG='00'ANDSUBSTR(b.CUST_ID,-1)='9'LIMIT164000,1000;

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

优化方案：

方案一：生成列 + 索引（MySQL 5.7+支持）

-- 添加生成列ALTERTABLEtestADDCOLUMNcust_id_last_charCHAR(1)GENERATED ALWAYSAS(SUBSTR(CUST_ID,-1))STORED;-- 创建复合索引ALTERTABLEtestADDINDEXidx_manage_cust_char_sn(MANAGE_TAG,cust_id_last_char,SN);

改写后的SQL：

SELECTDISTINCTMERCHANT_CODE,CUST_ID,MERCHANT_NAMEFROMtestWHEREMANAGE_TAG='00'ANDcust_id_last_char='9'ORDERBYSNDESCLIMIT1000;

方案二：游标分页

-- 第一页SELECT...WHERE...ORDERBYSNDESCLIMIT1000;-- 后续页，传入上一页最后一条的SN值SELECT...WHERE...ANDSN<@last_snORDERBYSNDESCLIMIT1000;

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

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

问题SQL：

SELECT...FROMtest c...WHEREc.last_modify_time>=CURDATE()-2ANDc.last_modify_time<CURDATE()UNIONSELECT...FROMtest2 cga...WHEREcga.last_modify_time>=CURDATE()-2ANDcga.last_modify_time<CURDATE()

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

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

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

优化步骤：

改成UNION ALL（业务上确认不会重复的话）：

SELECT...FROMtest c...WHERE...UNIONALLSELECT...FROMtest2 cga...WHERE...

创建索引：

ALTERTABLEtestADDINDEXidx_last_modify(last_modify_time,cons_id,addr_id);ALTERTABLEtest2ADDINDEXidx_last_modify(last_modify_time,addr_id);

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

案例6：超长IN列表的SELECT

问题SQL：

SELECT...FROMtest4 m...JOINtest5 acm...WHEREacm.card_noIN('0516201060473122','0516201061224952',...约1万个卡号)

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

推荐方案：临时表JOIN

-- 创建临时表CREATETEMPORARYTABLEtemp_card_nos(card_noVARCHAR(50)PRIMARYKEY);-- 批量插入INSERTINTOtemp_card_nosVALUES('0516201060473122'),('0516201061224952'),...;-- JOIN查询SELECT...FROMtest4 mJOINtest3 pONm.mp_id=p.mp_idJOINtest cONp.cons_id=c.cons_idJOINtest5 acmONm.meter_id=acm.gm_idJOINtest2 cgaONc.addr_id=cga.addr_idJOINtemp_card_nos tONacm.card_no=t.card_noWHEREm.factory_numberISNOTNULL;

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

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

问题SQL：

SELECT...FROMtest wWHEREw.return_code='0001'ANDw.return_state='01'ANDw.trans_typeIN('0','1')ANDw.CHANNEL_RESP_IDISNOTNULLORDERBYorder_id;

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

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

优化：创建覆盖索引

ALTERTABLEtestADDINDEXidx_return_cover(return_code,return_state,trans_type,CHANNEL_RESP_ID,order_id);

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

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

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

问题SQL：

UPDATEtestSET...WHEREid='1999857891477553154';

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

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

解决方案：

ALTERTABLEtestADDPRIMARYKEY(id);

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

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

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

1. 索引是基石，但要合理设计

   • 复合索引列顺序：等值在前，范围在后，排序最后
   • 避免索引列用函数或隐式转换
   • 尽量用覆盖索引

2. 小结果集驱动大结果集

   • JOIN时让行数少的表当驱动表
   • 被驱动表连接字段必须有索引

3. 分而治之

   • 大操作拆成小操作
   • UNION改UNION ALL
   • 大分页改游标分页

4. 警惕隐蔽杀手

   • ORDER BY RAND() → 程序随机或主键随机
   • 超长IN列表 → 临时表JOIN
   • LIKE ‘%xx’ → 全文索引或冗余字段

5. 善用生成列

   • 函数计算结果存成列再建索引

6. 永远先看执行计划

   • EXPLAIN是你的眼睛
   • type=ALL和巨大rows是危险信号

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

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