大家好我是数据库小学妹 最近在帮公司做数据库巡检发现一个有意思的现象很多慢SQL问题其实都踩在同样的坑里。有的UPDATE语句能跑5分钟有的报表查询扫了几千万行数据还有的统计SQL动不动就卡住……说真的我第一次看到这些案例的时候自己也踩过类似的坑。今天就结合这些真实案例聊聊怎么优化慢SQL希望能帮大家少走点弯路~一、先搞懂原理SQL为什么会变慢动手优化之前得先明白MySQL怎么执行一条SQL。这个就像修车之前得先懂发动机原理一样~1.1 MySQL执行一条SQL的旅程一条SQL从发出去到返回结果要经过这些环节客户端 → 连接器 → 分析器 → 优化器 → 执行器 → 返回结果对性能影响最大的是优化器和执行器。优化器就像个参谋负责决定怎么查选哪个索引、表按什么顺序连接、要不要用临时表排序。执行器就按优化器的计划去干活可以理解成执行者。SQL变慢通常有这几个原因扫描了太多无关数据比如没建索引导致全表扫描生成了巨大的临时表比如GROUP BY没索引锁等待比如长事务占着锁不放优化器选错了计划统计信息不准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_logON;SETGLOBALlong_query_time2;-- 超过2秒就记录用pt-query-digest或mysqldumpslow分析慢日志找出最慢的SQL。也可以用SHOW FULL PROCESSLIST看看当前正在跑什么。步骤二看执行计划用EXPLAIN命令看看MySQL打算怎么执行EXPLAINSELECT...;重点看这几个字段字段含义怎么判断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子查询改JOINUNION改UNION ALL分批处理大事务拆成小事务用生成列函数操作存成列再建索引步骤五验证效果改完用EXPLAIN确认执行计划变了测试环境跑一下看看效果。别急着上线先验证一下~三、实战案例手把手教你优化案例1超大IN列表的UPDATE——锁住一切问题SQLUPDATEtest.testSETtrans_rec_status13WHEREidIN(几千个ID...)症状执行时间212秒和532秒锁住大量行其他操作全被阻塞。为什么会这样IN列表太长MySQL解析起来很累。一次性更新几千行锁累积在内存里。大事务还会撑大undo日志。我第一次遇到这个问题的时候还以为是数据库慢后来才发现是SQL写得有问题……怎么优化方案做法优点缺点分批处理推荐每次更新100~200个ID锁影响小简单总耗时可能略长临时表JOINID存到临时表再JOIN更新执行快步骤多一点业务层优化看看为什么一次要更新这么多根本解决可能要改业务逻辑案例2三表JOIN扫描1300万行只返回15万行问题SQLSELECT...FROMuseru,user_testa,customer cWHEREa.UserIDu.UserIDANDc.CustIDu.CustIDANDu.Status99ANDa.ExpireTSDATE_SUB(CURDATE(),INTERVAL80DAY)ANDc.NameNOTLIKE%test%ANDu.CreateTSDATE_SUB(CURDATE(),INTERVAL12MONTH)LIMIT150001;EXPLAIN结果type是ALL全表扫描rows 1300万Extra显示Using where; Using join buffer (Block Nested Loop)。问题诊断用的是逗号连接优化器不好生成高效计划。三张表都没建合适的复合索引。c.Name NOT LIKE %test%前导通配符让索引失效。优化步骤改成显式INNER JOINSELECT...FROMuseruINNERJOINuser_testaONu.UserIDa.UserIDINNERJOINcustomercONu.CustIDc.CustIDWHEREu.Status99ANDu.CreateTSDATE_SUB(CURDATE(),INTERVAL12MONTH)ANDa.ExpireTSDATE_SUB(CURDATE(),INTERVAL80DAY)ANDc.NameNOTLIKE%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秒降到毫秒级。这个优化效果真的太明显了~案例3ORDER BY RAND()——扫描1100万行取50条问题SQLSELECTSUBSTRING(ICCIDFROM9FOR2)ICCIDGroup,u.*FROMuseru,user_test vWHEREu.UserIDv.UserIDANDu.Carrier150ANDu.Status!99ANDv.ExpireTSNOW()ANDSUBSTRING(ICCIDFROM9FOR2)09ORDERBYrand()LIMIT50;症状每2~5分钟执行一次累计417次每次扫描1143万行。为什么会这样ORDER BY rand()要给所有符合条件的行生成随机数放进临时表全排序最后返回前50行。为了取50条数据MySQL要处理1100万行。数据量大的时候临时表还会写磁盘更慢。我第一次看到这个SQL的时候心想这不是杀鸡用牛刀嘛……优化方案方案一利用主键随机-- 先获取最大最小IDSELECTMIN(id),MAX(id)INTOmin_id,max_idFROMuserWHERE...条件...;-- 随机取50个SELECT...FROMuseruINNERJOINuser_test vONu.UserIDv.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问题SQLSELECTDISTINCTMERCHANT_CODE,CUST_ID,MERCHANT_NAMEFROMtest bWHEREMANAGE_TAG00ANDSUBSTR(b.CUST_ID,-1)9LIMIT164000,1000;问题分析MySQL处理LIMIT M, N的方式是先扫描MN行丢掉前M行返回后N行。M164000时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);改写后的SQLSELECTDISTINCTMERCHANT_CODE,CUST_ID,MERCHANT_NAMEFROMtestWHEREMANAGE_TAG00ANDcust_id_last_char9ORDERBYSNDESCLIMIT1000;方案二游标分页-- 第一页SELECT...WHERE...ORDERBYSNDESCLIMIT1000;-- 后续页传入上一页最后一条的SN值SELECT...WHERE...ANDSNlast_snORDERBYSNDESCLIMIT1000;这种方式不管翻到第几页扫描行数都稳定在1000行左右性能杠杠的案例5UNION去重导致9小时长查询问题SQLSELECT...FROMtest c...WHEREc.last_modify_timeCURDATE()-2ANDc.last_modify_timeCURDATE()UNIONSELECT...FROMtest2 cga...WHEREcga.last_modify_timeCURDATE()-2ANDcga.last_modify_timeCURDATE()症状查询时间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问题SQLSELECT...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_idp.mp_idJOINtest cONp.cons_idc.cons_idJOINtest5 acmONm.meter_idacm.gm_idJOINtest2 cgaONc.addr_idcga.addr_idJOINtemp_card_nos tONacm.card_not.card_noWHEREm.factory_numberISNOTNULL;为什么这样更快临时表有主键索引优化器会选它当驱动表避免了超长IN列表的解析开销。频繁执行的话可以改成普通表。案例7索引失效——隐式类型转换问题SQLSELECT...FROMtest wWHEREw.return_code0001ANDw.return_state01ANDw.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秒问题SQLUPDATEtestSET...WHEREid1999857891477553154;症状平均耗时51秒其中锁等待就占了51秒。根本原因id字段没主键或唯一索引MySQL只能全表扫描并加锁。解决方案ALTERTABLEtestADDPRIMARYKEY(id);加了主键后锁等待降到毫秒级。这个是最基础的但真的很多人会忽略~四、总结慢SQL优化黄金法则好啦总结一下今天的内容索引是基石但要合理设计复合索引列顺序等值在前范围在后排序最后避免索引列用函数或隐式转换尽量用覆盖索引小结果集驱动大结果集JOIN时让行数少的表当驱动表被驱动表连接字段必须有索引分而治之大操作拆成小操作UNION改UNION ALL大分页改游标分页警惕隐蔽杀手ORDER BY RAND() → 程序随机或主键随机超长IN列表 → 临时表JOINLIKE ‘%xx’ → 全文索引或冗余字段善用生成列函数计算结果存成列再建索引永远先看执行计划EXPLAIN是你的眼睛typeALL和巨大rows是危险信号今天的分享就到这里啦~ 这些都是我踩坑总结出来的经验希望对朋友有帮助有问题欢迎在评论区讨论我是数据库小学妹咱们下篇见