慢SQL优化实战：从慢查询日志到执行计划与索引设计

1. 从一次线上事故说起我们为什么必须正视慢SQL先讲一件我想起来还心有余悸的事。去年夏天某个周五晚高峰我们一个核心订单系统的数据库CPU直接飙到95%以上请求耗时从正常的30毫秒一路涨到3秒开外监控大屏上全是红色的告警。当时我们几个人围在工位前第一反应是服务器是不是被攻击了结果查了一圈发现就是几条慢SQL把整个连接池打满了。说真的慢SQL这个东西很像是温水煮青蛙。平时跑得好好的接口随着业务表数据量从几十万涨到上千万某一天突然开始变慢。更麻烦的是它不是整个系统都慢而是特定接口、特定时段偶尔慢一下你甚至不知道该从哪里排查。我们当时用的排查手段就是慢查询日志加执行计划分析折腾了一个多小时才定位到罪魁祸首——一条关联了5张表、没走任何索引的聚合查询。那次事故之后我花了大半年时间梳理团队所有核心链路的SQL把能优化的全部优化了一遍数据库负载直接降了六成以上。今天这篇文章我就把完整的排查思路、优化手段和那些踩过的坑一次性讲透。不管你是后端开发、DBA还是运维同学只要工作上要和数据库打交道这篇内容都值得认真看完。这里说的SQL优化绝不只是给查询加点索引那么简单。它是一个完整的闭环要解决的核心问题是什么为什么慢以及如何在不动业务逻辑的前提下让数据库效率起飞。全文会覆盖慢查询日志分析、执行计划解读、索引设计、SQL改写、参数调优和并行SQL优化最后再附上我整理的避坑清单。2. 慢查询日志定位问题的第一把钥匙2.1 如何开启并配置慢查询日志既然要告别慢查询第一步当然是把慢查询暴露出来。MySQL的慢查询日志是最基础的观测手段但很多开发环境的默认配置其实是关闭的。我见过不少新人同事上来就问我“为什么我SQL很慢但日志里什么都没有”大概率就是没开慢查询日志或者阈值设得太高了。在MySQL中慢查询日志相关的核心参数有这几个-- 查看当前慢查询日志状态 SHOW VARIABLES LIKE slow_query_log; -- 开启慢查询日志动态参数无需重启 SET GLOBAL slow_query_log ON; -- 设置慢查询阈值单位秒建议设置为1秒以内 SET GLOBAL long_query_time 1; -- 设置慢查询日志文件路径 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log; -- 记录未使用索引的SQL SET GLOBAL log_queries_not_using_indexes ON;这里要注意两个很容易被忽略的细节。第一long_query_time的单位是秒并且它的生效机制是以实际执行时间大于阈值才记录等于的情况不会记。第二log_queries_not_using_indexes这个参数如果不打开那些全表扫描但恰好跑得快的小表查询就不会被记录下来而这种查询往往是业务隐患。在生产环境我一般建议把long_query_time设成1秒高峰期如果慢SQL特别多可以先调到2秒快速过滤之后再逐步下调。日志文件的轮转也得考虑可以用系统的logrotate定时切割或者直接打开MySQL自带的log_output参数配合定期手动归档。否则慢查询日志文件越积越大磁盘被写满也是很常见的生产事故。2.2 看懂慢日志里的每一列信息慢查询日志打开了怎么读它才是关键。这里贴一段典型的生产环境慢日志记录# Query_time: 2.531537 Lock_time: 0.000418 Rows_sent: 10 Rows_examined: 8934512 SET timestamp1719403200; SELECT o.order_id, u.user_name, p.product_name FROM order_info o LEFT JOIN user_info u ON o.user_id u.user_id LEFT JOIN product_info p ON o.product_id p.product_id WHERE o.order_status 1 AND o.create_time BETWEEN 2024-06-01 00:00:00 AND 2024-06-30 23:59:59 ORDER BY o.create_time DESC LIMIT 10;大家注意看Rows_examined这个字段893万行但最终只返回了10行。这意味着数据库为了这10条结果扫描了接近900万行数据这个查询不慢才是怪事。慢日志的主旨其实就一句话优化目标就是让Rows_examined尽可能贴近Rows_sent二者的比值越小越好。Lock_time如果特别大则说明查询在等待锁释放这种情况往往是并发写入导致的需要去优化事务的锁粒度或者业务执行顺序而不是单纯优化SQL语句本身。Rows_sent过大则说明查询返回了太多用不上的数据应用层可能只需要聚合后的结果SQL却把明细数据全捞回来了。每一列信息背后都对应着一条优化路径这正是慢日志的价值所在。提示配置慢查询日志只是第一步真正值钱的是对日志内容的解读能力。我建议每天定时分析一次慢日志梳理出Top N的慢查询然后逐个击破。2.3 用工具自动分析慢查询日志如果慢查询量大到肉眼看不过来的程度建议直接用工具。MySQL官方自带了mysqldumpslow用起来很简单# 按照查询耗时排序展示前10条 mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log # 按照扫描行数排序展示前10条 mysqldumpslow -s ar -t 10 /var/log/mysql/mysql-slow.logmysqldumpslow会把相似的SQL自动聚合归并比如同一条SQL仅参数不同输出结果里包含执行次数、平均耗时、扫描行数等信息。它的优势在于零依赖任何装了MySQL的机器都能用。缺点是报表不够美观对于复杂场景的归类也比较粗。更现代的方案是使用pt-query-digestPercona Toolkit中的工具它能生成结构化的HTML/文本报告按查询指纹自动聚类还能展示每个类别的耗时分布、访问表的信息等。我用下来感觉信息量比mysqldumpslow大得多。实际运维中我通常是先用pt-query-digest跑一轮报告把Top 20的SQL列出来再根据业务知识逐个判断哪些需要立刻处理。分析工具不是万能的它只能帮你定位慢SQL真正解决慢的问题还是得回到执行计划和索引本身。3. EXPLAIN执行计划SQL慢的根本原因藏在这里3.1 快速掌握执行计划关键字段慢日志告诉我们哪些SQL慢但为什么慢需要靠执行计划来回答。MySQL的EXPLAIN命令就是用来查看一条SQL的执行计划的用法非常直白EXPLAIN SELECT o.order_id, u.user_name, p.product_name FROM order_info o LEFT JOIN user_info u ON o.user_id u.user_id LEFT JOIN product_info p ON o.product_id p.product_id WHERE o.order_status 1 AND o.create_time BETWEEN 2024-06-01 00:00:00 AND 2024-06-30 23:59:59 ORDER BY o.create_time DESC LIMIT 10;输出结果大致如下精简过idselect_typetabletypekeyrowsfilteredExtra1SIMPLEoALLNULL900万10.0Using filesort1SIMPLEueq_refPRIMARY1100.0NULL1SIMPLEpeq_refPRIMARY1100.0NULL这里最刺眼的两个地方一是驱动表order_info的type为ALL说明在做全表扫描二是Extra里有Using filesort说明排序没走索引而是额外开辟了排序缓冲区。执行计划里type的优劣顺序大概是system const eq_ref ref range index ALL。ALL是最差的情况正常业务SQL至少要达到range级别主键查询则应该是const或eq_ref。很多初级开发者只盯着rows字段看认为“看到大数字就是慢”。但rows是估算值有时候并不准确。真正要关注的是执行计划选择的驱动表、连接方式、索引使用情况和排序路径。这些信息组合起来才能还原出SQL执行的完整路径。3.2 从执行计划反推索引设计方案还是拿上面那条SQL来说执行计划告诉我们问题出在order_info表的全表扫描和文件排序。我的优化思路通常分两步第一步搞清SQL最核心的过滤条件第二步是为过滤和排序设计合适的联合索引。这条SQL最核心的过滤条件是order_status 1和create_time BETWEEN ...排序条件是create_time DESC。依据最左前缀原则可以设计联合索引(order_status, create_time)。这样条件下查询就能通过索引先过滤订单状态再对时间范围做区间扫描同时create_time天然有序Using filesort也会随之消失。-- 添加联合索引 ALTER TABLE order_info ADD INDEX idx_status_time (order_status, create_time);这里我见到很多人容易犯错单独给order_status建索引又单独给create_time建索引。但MySQL优化器针对多条件过滤时通常只能选择一个索引另一个条件仍需要回表过滤。两个单列索引在AND组合场景下效果远不如一个联合索引。更严重的场景是OR连接多个条件可能导致索引完全失效直接全表扫描。索引设计是优化SQL的核心手段但不是所有场景都适合加索引加的太多反而会拖慢写入。这就是为什么我们还需要从SQL本身入手。3.3 覆盖索引让查询直接起飞的关键细节覆盖索引是优化查询时性价比极高的一种手段。所谓覆盖索引是指查询需要读取的所有列都包含在索引中查询过程根本不需要回表去读数据行。覆盖索引的思想有点像你在通讯录里就存了朋友的名字和电话完全没有必要再翻一遍名片本。继续用上面的场景举例。如果查询只涉及order_id、order_status、create_time三列那么idx_status_time(order_status, create_time)这个索引不够因为还需要返回order_id。这时候可以用一个覆盖索引-- 覆盖索引将order_id也纳入索引 ALTER TABLE order_info ADD INDEX idx_status_time_id (order_status, create_time, order_id);而查询时写成SELECT order_id, order_status, create_time FROM order_info WHERE order_status 1 AND create_time BETWEEN 2024-06-01 00:00:00 AND 2024-06-30 23:59:59 ORDER BY create_time DESC;这样执行计划里的Extra会出现Using index说明查询完全在索引中完成速度会有质的提升。在实际项目中把SELECT *改成明确的列列表更多情况下并不是为了少传几列数据而是为了创造覆盖索引的生效条件。这也是最简单的提升性能的手段之一。4. SQL改写实战那些提升一个量级的核心写法4.1 避免SELECT *只取必要列很多刚入行的开发写查询时喜欢图方便一句SELECT *搞定所有。这在数据量小的阶段完全没问题但一到生产环境就是隐患。服务端要把表里的每一列都读出来再通过网线传给应用消耗的是磁盘IO、内存和网络带宽。之前帮一个朋友公司排查线上接口慢发现他们一个列表页查询里写了SELECT *其实前端只需要展示5个字段但数据表有40多列。单单网络传输这一项就浪费了大量时间。改成明确列之后响应时间从2.1秒降到了400毫秒。更妙的是这种改写还顺带让覆盖索引生效了因为查询列都包含在索引里。这里有一点值得强调不要以为数据库会自动优化把用不到的列挑出去。MySQL的优化器目前还不具备这种智能识别能力。SQL里写了哪些列数据库就会老老实实读取哪些列。4.2 优化深分页LIMIT偏移量的痛分页查询是慢SQL的重灾区尤其是大偏移量的深分页比如用户翻到第10000页SQL写成LIMIT 200000, 20。这种写法的底层逻辑是先扫描200020行然后丢弃前200000行只返回最后20行。这就意味着越往后的页面扫描的行数越多速度自然越慢。我见过一个后台管理系统的订单列表用户点击“下一页”到几千页时接口响应时间从200毫秒飙升到7秒。我当时给的优化方案是“延迟关联”加“书签分页”。延迟关联的核心思路是先用索引快速定位到目标行的主键ID集合再通过主键ID去关联表获取完整数据-- 优化前扫描大量数据再丢弃 SELECT * FROM order_info WHERE create_time BETWEEN 2024-06-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 200000, 20; -- 优化后先走覆盖索引找到ID再关联返回数据 SELECT o.* FROM order_info o INNER JOIN ( SELECT order_id FROM order_info WHERE create_time BETWEEN 2024-06-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 200000, 20 ) tmp ON o.order_id tmp.order_id;优化后的SQL让子查询完全通过覆盖索引完成扫描的数据量从全表变成了索引内的小范围操作再根据ID去聚簇索引中取完整行。对于深分页场景这一招几乎是百试百灵。如果业务允许换换交互形式更直接的方式是“键集分页”。也就是记住上一页最后一条记录的时间戳然后查询下一页时带上这个边界条件。这种方式不走偏移量查询性能随时间推移依然保持稳定特别适合移动端的“下拉加载更多”场景。代价是需要修改应用层传参逻辑改动量略大。4.3 排序和分组优化不只是加索引那么简单排序慢的场景通常可以在Extra里看到Using filesort。这里要说明一点Using filesort并不代表一定会用磁盘文件排序当排序数据量小于sort_buffer_size时排序操作完全在内存中完成速度也可以接受。但一旦数据量超出缓冲区MySQL不得不借助临时文件性能就开始断崖式下跌。我实际工作中的优化优先级是这样的先看排序字段能不能被联合索引覆盖让排序直接走索引如果不行再优化排序缓冲区参数最后才考虑用应用层排序替代数据库排序。GROUP BY的优化思路则要复杂一些。很多分组慢查询的真正瓶颈不在于分组本身而在于GROUP BY之后还要做聚合计算或关联其他表。比如你想要每个用户的订单总额如果先做大范围分组再和用户表关联性能往往很差。优化方式是先缩小分组范围把关联下推到子查询中。拿一个真实场景举例。需求是统计6月份有下单用户的基本信息你可能第一反应是SELECT u.user_id, u.user_name, COUNT(*) AS order_cnt FROM user_info u LEFT JOIN order_info o ON u.user_id o.user_id WHERE o.create_time BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY u.user_id, u.user_name;这个查询的问题是先关联后分组order_info表可能被关联出海量的中间结果再聚合。更优的方案是先分组聚合拿到小结果集再关联用户表SELECT u.user_id, u.user_name, t.order_cnt FROM ( SELECT user_id, COUNT(*) AS order_cnt FROM order_info WHERE create_time BETWEEN 2024-06-01 AND 2024-06-30 GROUP BY user_id ) t INNER JOIN user_info u ON t.user_id u.user_id;这个改写背后的逻辑是把代价最高的聚合操作现在一个有索引过滤的小范围内完成然后用主键去关联外表避免了一次巨大的临时中间表。两种写法的差距在数据量大时可能是几十倍。4.4 JOIN关联优化驱动表决定查询效率关于JOIN很多人有个误区认为只要关联字段加了索引就行。实际上JOIN的效率还取决于谁做驱动表。以MySQL的嵌套循环连接算法为例优化器会选择它认为更小的一张表作为驱动表然后逐行在被驱动表中匹配索引。在无法控制优化器选择时我们可以通过STRAIGHT_JOIN强制指定驱动表顺序。但这样做有风险因为这个顺序在当前数据分布下可能是高效的一旦数据分布变化反而可能变慢。我只有在对数据分布有充分把握时才会这么做。更稳妥的思路是保证被驱动表的关联字段有索引让每一次匹配都走索引查询而不是全表扫描。另外连接条件写清楚关联字段不要在ON后面带函数运算比如ON DATE(o.create_time) DATE(u.reg_time)这会直接导致索引失效。SQL优化中有个铁律索引列上做运算索引就废了。5. 并行SQL优化在合理场景下给查询提提速5.1 并行SQL到底是什么传统观念里一条SQL执行时是串行工作的。数据量大时我们通常只能靠优化SQL或者加索引去降低扫描量。但总有那么些场景SQL已经优化到极致可单分片数据就是太大比如数仓里的汇总查询、报表统计、大范围的数据扫描。这时候并行SQL优化就能派上用场。并行SQL优化的思路很简单粗暴把一个大任务拆成多个小任务让多个CPU核心或IO通道同时处理。行为上就是把原本一个线程完成的全表扫描拆分成多个分区或分片交给多个线程并行扫描最后再合并结果。MySQL 8.0引入了InnoDB并行扫描策略同一个查询可以利用多个核心来处理聚簇索引中的页面数据。对于全表扫描和范围扫描类的SQL在数据量足够大、磁盘IO有富余的前提下性能提升非常明显。如果一个表只有几万行并行反而是负优化因为线程切换和合并结果的开销超过了并行带来的收益。5.2 并行参数怎么调什么情况值得开并行度的设计是一个基于现实环境不断调优的过程。MySQL中有一个核心参数叫innodb_parallel_read_threads它控制的是InnoDB引擎在执行读写操作时使用的并行线程数。默认值在版本间略有差异通常为4或8可调范围一般是1到256。我个人的建议是不要一开始就调到64或者128先从4开始观察SQL耗时变化然后逐步增加到8、16直到耗时不再明显下降甚至开始反弹就说明CPU上下文切换的成本已经抵消了并行收益这时候要果断回退。在实际项目中16个线程通常是比较稳妥的选择。并行读并不是对InnoDB的每个操作都能生效的。根据MySQL官方文档它主要针对的是全表扫描类型的查询尤其是无索引条件下的大范围扫描。如果是通过二级索引回表查询由于回表操作本身是离散IO并行收益并不明显甚至可能因为额外线程调度导致更差。另外需要注意并行SQL并不适合在OLTP在线事务处理核心链路上开启。我们这里虽然是“快速起飞”的实战场景但仍要谨慎判断在数仓类查询、报表任务、批量数据校验等低并发、大查询场景开并行是值得的。相反在高并发、短小精悍的线上接口上开并行很容易把数据库CPU池打满引来新的性能灾难。注意并行不仅是SQL能「整并行」也可以在应用层把一个大查询按维度拆成多个小查询并发执行。比如按月份拆成12个查询用线程池并发执行再合并结果。这种方案在分库分表和不支持并行的旧版本数据库中尤其实用。5.3 并行SQL优化的适用场景如果做一个简单归类并行SQL适合三类任务第一类是数据统计类比如跑月度报表、用户活跃分析这些任务对实时性要求低但数据扫描量大并发跑能明显缩短执行时间。第二类是批量数据处理比如给几千万用户做标签更新用并行扫描能加速数据读取。第三类是历史数据归档按时间范围拆分成多个子任务并行复制到归档表。不适合并行SQL的场景也很清晰数据量小、查询频繁、有锁竞争、CPU资源本身已经饱和的状态。在这些场景下盲目开启并行有时反而把原本优雅的架构拖垮。6. 常见问题与排查技巧实录最后这部分我把工作中被问得最多的一些问题以及我自己踩过的坑按速查表的形式整理出来。遇到类似问题时可以直接对照排查。问题现象可能原因解决方案索引明明存在但没有生效查询条件中对索引列做了运算或函数操作表达式移到等号右侧或改造成范围匹配加索引后查询反而变慢存在多个单列索引优化器选错索引建立联合索引或使用FORCE INDEX指定深分页越来越慢LIMIT偏移量过大导致全表扫描式跳页延迟关联或键集分页替代Rows_examined巨大但返回行数很小驱动表选择不当或过滤条件无法下推重写SQL顺序或拆分查询同一SQL时快时慢事务隔离级别下的锁等待或缓存命中率波动查看Lock_time优化事务与索引多个线程同时写入时相互等待间隙锁或删除更新导致的锁竞争缩小事务范围开启锁监控进一步定位CPU占用极高慢日志刷屏大量并发执行低效查询或单条SQL过于复杂慢日志聚类分析集中优化Top SQL查询数据量不大但排序很慢排序字段与where条件组成联合索引不匹配重新设计联合索引顺序或减少排序列在排查技巧上我强烈建议把慢查询SQL统一收集到一个分析专用的库表里每天跑定时任务把慢日志解析入库。这样就不必反复人工翻日志还能按天看到Top SQL趋势变化很容易发现性能退化是从哪次发布开始的。另外一个很实用的手段是“抓现场”。线上出现慢SQL时不要只盯着优化本身先去看看数据库层是否锁等待严重、连接数是否打满、磁盘IO是否饱和。很多时候SQL只是一个导火索真正的根源在系统资源已经绷得很紧。我经历过一次事故一条扫描200万行的SQL平时只要400毫秒那天因为磁盘IO队列积压硬是跑了4秒多。这类问题光靠优化SQL是解决不了的要先保障底层资源稳定再谈SQL优化。还有一个小技巧几乎每个大促前我都会做一遍随机抽样业务核心表用真实值做一遍EXPLAIN看看执行计划有没有因为数据分布变化而走上偏路。索引失效很多时候不是SQL写错了而是数据变了。这个习惯救过我很多次。7. 写在最后的经验沉淀优化SQL这事做得越久越会发现它不单纯是技术活更多的是一种思维方式。遇到慢查询先读慢日志再看执行计划最后动手优化这个顺序永远不会错。分享一个我自己的小习惯每次优化完一条SQL我都会把优化前后的执行计划、耗时、扫描行数记录在一张表里形成自己的知识库。以后再遇到相似场景直接到知识库里查答案省时省力。最后想说的是SQL优化没有银弹没有一条SQL是加了索引就一定快的。你的数据量、数据分布、业务访问模式才是最终的决策依据。把基础原理吃透把排查工具用熟练在真实场景里多做几个对照实验你会发现自己判断慢SQL的眼光会越来越准。这个能力比背诵任何优化口诀都值钱。