★ FEATURED ARTICLE 1. 开局思路为什么先盯慢查询日志和EXPLAIN做MySQL优化最忌讳的就是凭感觉瞎猜。SQL慢就加索引加完还是慢就换配置换了配置还慢就说是硬件问题——这种套路我见得太多了最后往往不是没效果而是越优化越乱索引一大堆写入性能反而被拖垮。我们先冷静下来想一个问题数据库卡顿绝大多数情况下是因为某几条SQL吃掉了大部分资源。可能是查询本身写得烂可能是索引没设计对也可能是表结构本身有问题。不管哪种情况第一步一定是先把“罪魁祸首”找出来。这时候就轮到慢查询日志出场了。它能告诉你是哪条SQL慢、跑了多久、扫了多少行拿到具体SQL之后再用EXPLAIN看它到底是怎么执行的——是全表扫描还是用了索引估算扫了多少行有没有临时表、文件排序。这套组合拳适用面很广。不管你是刚接触MySQL没多久的新手还是已经在生产环境里救过几次火的运维开发只要你手里的MySQL开始“拉胯”这条排查路径基本都走得通。后面的内容我会按实际操作的顺序来写包括怎么把慢查询日志调到一个好用的状态、怎么读懂EXPLAIN输出里那些字段的真正含义以及两者配合起来定位问题的完整思路。2. 慢查询日志的配置与使用细节2.1 慢查询日志怎么开启才合适慢查询日志默认是关闭的因为记录日志本身也有开销。但既然是排障该开就得开问题在于怎么开——生产环境直接改配置文件再重启动静太大而且不一定能复现问题。更好的做法是动态修改用MySQL的SET GLOBAL命令即时生效。-- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 设置日志文件路径 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log; -- 设置慢查询阈值单位是秒 SET GLOBAL long_query_time 1;需要留意一个点long_query_time的判定是基于执行时间的绝对值而且SHOW GLOBAL VARIABLES查到的阈值和你当前会话的连接值可能不一样。如果你在某一个客户端连接里执行SHOW VARIABLES LIKE long_query_time看到的还是默认的10秒原因是你这个会话的变量在建立连接时就已经固化了需要重新连接才能生效。这个坑很容易让人误判配置没生效实际上配置已经改了只是会话没刷新。另外还有一个容易被忽略的参数叫log_queries_not_using_indexes。从名字就能看出来它的作用是把没有走索引的SQL也记进慢查询日志哪怕那条SQL本身执行得很快。这个参数在做全表扫描排查时特别有用因为不少慢查询日志里捞出来的问题SQL执行时间其实没到阈值但每次调用都在扫全表积少成多就成了性能隐患。建议在用EXPLAIN分析前先把这类SQL也捞出来一并排查。2.2 慢查询日志怎么看才高效日志文件是纯文本格式但直接打开硬啃效率很低尤其是积累了几天的日志之后。推荐两个工具一个是官方的mysqldumpslow另一个是Percona Toolkit里的pt-query-digest。mysqldumpslow胜在简单装好MySQL自带就有适合快速看一眼# 按执行时长排序只看前20条 mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log # 按平均执行时间排序 mysqldumpslow -s ar /var/log/mysql/mysql-slow.logpt-query-digest就强大得多能对SQL做归一化聚合把长得不一样但结构相同的SQL比如条件里的数值不同归成同一类再按总耗时、平均耗时、出现次数排序。这样一来你就能一眼看出哪些SQL是“低频但极慢”哪些是“高频但中速”两者的优化策略完全不同。pt-query-digest /var/log/mysql/mysql-slow.log analyze.txt打开分析报告先看顶部“Profile”区的总耗时排名再看每个SQL类别里的具体执行计划。这里有个经验总耗时排名靠前的SQL优先看有没有优化空间出现次数很多但单次不慢的SQL优先看是不是每次都在做不必要的重复查询。3. EXPLAIN执行计划的深入拆解3.1 EXPLAIN输出字段到底在说什么拿到一条慢SQL之后前缀加EXPLAIN就能看到执行计划。先说明一下EXPLAIN并不真正执行查询它只是估算但估算的信息足够我们定位绝大多数问题。EXPLAIN SELECT u.id, u.name, o.order_no FROM users u JOIN orders o ON u.id o.user_id WHERE u.status 1 AND o.create_time 2024-01-01;执行后一堆列新手容易直接懵。其实关键就看几个字段。type是扫描方式的级别从好到差大体是type含义风险等级system表只有一行极优const主键或唯一索引等值匹配极优eq_ref连接查询中被驱动表用主键或唯一索引匹配优ref非唯一索引等值匹配良好range索引范围扫描良好index全索引扫描一般ALL全表扫描危险看到ALL基本可以判定这条SQL没走上索引。index看着带“index”字样好像不错但它实际是扫了整个索引树和数据量成正比不一定比ALL快多少。key是实际用到的索引名。possible_keys列出的索引只是“有可能用”实际聚焦key这一列就行。rows是估算扫描行数。这个数字只是估算不一定精确但量级很有参考价值。一条SQL如果估算扫描几万行那肯定有问题如果只扫十几行还慢那瓶颈就在别处——比如触发了文件排序或者临时表。Extra里藏着大量细节下面几个最常碰到Using filesort需要文件排序。排序字段没走索引MySQL会把数据取出来放到内存或磁盘排序。结果集越大越致命。Using temporary用了临时表。常见于GROUP BY、DISTINCT、ORDER BY混用或者多表聚合时索引设计不对。Using index说明是覆盖索引扫描不用回表这是最优状态。Using where表示在存储引擎层拿到数据后又进行了条件过滤。如果配合ALL说明过滤是在取到全部数据之后才做的止损效果有限。Using join buffer连接时没有走索引被迫使用连接缓冲区。多表连接时频繁出现这个基本就是对连接字段缺索引。3.2 一次真实的EXPLAIN分析过程假设现在有一条SQL慢得很明显SELECT u.id, u.name, COUNT(o.id) AS order_cnt FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.status 1 GROUP BY u.id, u.name ORDER BY order_cnt DESC LIMIT 20;EXPLAIN输出大致如下users表type ALLrows 50000Extra Using where。orders表type refkey idx_user_idrows 每个用户约10行Extra Using temporary; Using filesort。从这个执行计划能读出两个信息一是users表在按status过滤时是全表扫的5万行如果状态字段区分度很低这本身不是致命问题但后面还牵着订单聚合就危险了二是GROUP BY和ORDER BY同时存在导致走了临时表加文件排序在用户基数大时性能会急剧劣化。优化思路大概是给users.status加一个普通索引虽然区分度低但至少能把扫描范围缩小到需要的行然后考虑改写SQL先聚合订单数据再连接用户表尽量避免大结果集的GROUP BY。这里有一个高级但很实用的技巧用EXPLAIN ANALYZE来替代普通EXPLAINMySQL 8.0支持。它不但显示执行计划还会真实执行一遍SQL并把每一步的耗时、扫描行数打印出来。生产环境慎用但测试环境或者流量低峰期它能帮你看到估算和实际之间的差距。4. 慢查询日志与EXPLAIN结合的完整排查流程4.1 从日志定位到一条SQL的完整步骤很多文章只教你单看慢查询日志或者单看EXPLAIN但实际排障是把两者串起来用的。第一步先把慢查询日志按总耗时排序挑出排名前10的SQL。这一步的目的是缩小范围。第二步对挑出来的SQL逐个加EXPLAIN确认执行计划中的type、key、rows、Extra。把执行计划可疑的SQL单独记下来。第三步回到日志看这条SQL的执行频率。如果一分钟跑几百次每次都扫全表哪怕单次30毫秒累积的资源消耗也很可观。这类SQL优先级甚至比单次跑3秒的高频更值得处理。第四步用pt-query-digest或者自己写脚本统计同类SQL的总耗时占比。这个占比就是优化后能回收的收益上限。如果一条SQL优化后能省掉90%时间但是它在整体耗时里只占2%那优先处理它就不划算——不如先处理占比高的大头。4.2 一张排查对照表现象大概率原因下一步动作慢查询日志频繁出现同一条SQLrows极大索引缺失或失效查看EXPLAIN的key字段确认是否走索引type为ALLfiltered很低查询条件无可用的索引检查WHERE字段是否适合建索引Extra出现Using temporaryGROUP BY / ORDER BY字段与索引顺序不匹配调整索引顺序或改写SQLExtra出现Using filesort排序字段未包含在索引中将排序字段加入复合索引的末尾连接查询出现Using join buffer连接字段缺索引给被驱动表的连接字段建立索引key显示PRIMARY或unique但rows依旧大结果集本来就大查询逻辑需要调整考虑分页、分批或加条件过滤表格不是万能药但可以作为排查初期的检查清单。很多时候问题不会只出现在一行里比如一张表既有Using temporary又有Using filesort那就得先理清楚SQL的执行顺序先解决掉临时表再看排序还慢不慢。5. 慢查询日志里的高频选手排序和分组5.1 为什么GROUP BY和ORDER BY这么容易慢GROUP BY和ORDER BY的慢本质上是无索引排序和临时表在作怪。MySQL索引天然是有序的如果查询语句里的GROUP BY和ORDER BY字段与索引顺序一致优化器可以直接利用索引的有序性跳过显式排序。一旦顺序不一致就只能把查出来的数据先放进内存临时表再排序数据量超过tmp_table_size阈值还会落到磁盘上性能一落千丈。举个实际的例子SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id ORDER BY dept_id;这个SQL看起来毫无问题但如果employees表没有dept_id索引执行计划里就会出现Using temporary; Using filesort。解决办法也不难ALTER TABLE employees ADD INDEX idx_dept (dept_id);加了索引之后优化器就能直接从索引读取有序数据分组和排序一步到位不再需要临时表。再进阶一点如果查询是WHERE status 1 GROUP BY dept_id那么复合索引建(status, dept_id)比单独建dept_id更好。**原则是等值条件放前面分组或排序字段放后面。**这个顺序对B树索引的检索效率影响很大。5.2 分页查询的隐性问题慢查询日志里还有一类高频选手就是带LIMIT的分页查询。小分页没问题但翻到很后面的时候比如LIMIT 100000, 20MySQL会先把前100020条全查出来再丢掉前100000条这就是典型的深分页。EXPLAIN看这种SQLrows会特别大但type可能还是range或者ref不容易一眼看出问题。优化方案通常是延迟关联-- 原始写法深分页慢 SELECT id, name, order_no FROM orders ORDER BY id LIMIT 100000, 20; -- 延迟关联先取主键再回原表 SELECT o.id, o.name, o.order_no FROM orders o JOIN ( SELECT id FROM orders ORDER BY id LIMIT 100000, 20 ) tmp ON o.id tmp.id;子查询里只查主键走索引快得多再把需要的列通过主键关联回来避免了全行数据的无意义扫描。实测下来深分页场景下这个改写通常能提升一个数量级。6. 索引设计的实用经验6.1 索引不是越多越好慢查询日志和EXPLAIN分析得越多越容易陷入“看见慢SQL就想加索引”的惯性。但索引本身也有代价每次INSERT、UPDATE、DELETE都要维护索引树索引越多写入越慢。一张表如果索引超过5个还频繁写入性能损失就很可观了。所以我的习惯是**索引宁缺毋滥但一旦加了就要让一条索引覆盖尽量多的热点查询。**比如上面提到的(status, dept_id)复合索引既能覆盖WHERE status 1 GROUP BY dept_id又能覆盖单独的WHERE status 1查询还能加速ORDER BY dept_id。一条索引三种用途这才能算好索引。6.2 前缀索引和函数索引的使用场景在VARCHAR大字段上做等值匹配时比如WHERE title xxx如果字段特别长整个字段建索引既占空间又慢。这时候可以提取字段前缀建前缀索引ALTER TABLE articles ADD INDEX idx_title_prefix (title(20));需要注意前缀索引可能会让部分查询无法走索引因为优化器需要判断前缀是否足够区分。一般建议先测一下区分度SELECT COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS ratio FROM articles;比例越接近1前缀索引的效果越好。另外如果查询里对字段做了运算比如WHERE DATE(create_time) 2024-01-01普通索引会直接失效。MySQL 8.0支持函数索引可以这么建ALTER TABLE orders ADD INDEX idx_create_date ((DATE(create_time)));但如果线上还是5.7就得反过来改写SQL把函数放在等号右侧WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;这个改写是SQL优化里最常见的手法——让索引字段保持裸奔状态不要在索引列上套函数或运算。7. 实际项目中的优化案例记录7.1 案例一条报表SQL拖垮整个线上库之前维护过一个模拟项目X系统跑了一段时间后线上库开始出现连接堆积监控显示CPU持续高位。打开慢查询日志排在第一名的是条报表SQL总耗时占了整库的60%。EXPLAIN一看三张大表做JOIN驱动表直接ALL全表扫另两张表的连接字段也都没索引执行计划里一行连着排了三个Using join buffer。处理过程分三步先给三张表的连接字段补上索引驱动表的WHERE条件字段也加了索引。这一步做完那三个Using join buffer彻底消失。再对GROUP BY字段做了索引调整把临时表排掉了。最后改写了一下报表的查询逻辑把子查询里不必要的字段去掉只保留用于过滤和聚合的列。这三步下来这条SQL的执行时间从8秒多降到了0.2秒以内线上CPU直接回落。这个案例里面最值得说的经验是别一上来就改硬件或者调MySQL缓冲池参数先把SQL和索引搞对90%的问题都在这一层。7.2 案例频繁更新导致索引碎片问题另一个案例有点隐蔽。有个用户的更新接口不复杂单条SQL执行时间也就十几毫秒但慢查询日志里持续出现频率还特别高。EXPLAIN看执行计划key和rows都正常新索引也加了就是慢。最后检查表状态发现表数据行数不多但表碎片极其严重。原因是这个表频繁做UPDATE和DELETE索引页频繁分裂和合并产生了大量碎片。OPTIMIZE TABLE重建表后慢查询日志那条SQL就再没出现过。这个案例提醒我EXPLAIN不是万能的。它反映的是执行计划但执行计划正常不代表存储层状态正常。如果SQL没问题索引没问题别忽略表碎片、表统计信息过期这些“暗坑”。-- 查看表碎片情况 SELECT table_name, data_free FROM information_schema.tables WHERE table_schema your_db AND data_free 0;有大量data_free碎片时低峰期执行一次OPTIMIZE TABLE往往是性价比极高的一个操作。8. 慢查询日志的采集和分析需要注意什么8.1 阈值怎么定日志怎么轮转慢查询阈值不是固定的。一个刚上线的业务库和跑了几年的核心交易库合理阈值完全不同。我的做法是先设1秒跑一周看每天慢SQL的数量和分布如果条数太多或太少再动态调整。目标是让日志体量处于“能看”“不刷屏”的中间状态。日志膨胀也是个现实问题。生产环境不可能手动定期删日志直接用系统自带的logrotate做轮转比较省心/var/log/mysql/mysql-slow.log { daily rotate 7 compress missingok }保留一周的日志足够做趋势分析既能回溯问题有没有复发又不会占太多磁盘。8.2 安全操作上要避开的风险SET GLOBAL long_query_time 1这种操作是全局生效的当前已存在的连接不会同步新阈值只有新建立的连接才会。这一点前面提过但值得再强调一次。另外线上如果已经有高并发流量直接开启慢查询日志最好在低峰期执行避免日志写入本身带来额外IO压力。还有一个容易被忽略的点log_queries_not_using_indexes开启后理论上任何一条非索引查询都会进日志包括一些本来很快但频繁执行的查询。如果业务里有个高频小查询没走索引日志量会瞬间暴涨磁盘也可能被撑爆。所以这功能建议只在排障期临时打开别长开。9. 优化完成后如何验证效果改完SQL或者加完索引不能只看EXPLAIN变好看了就收工。我的验证流程一般是这样先在测试环境跑一遍优化后的SQL对比执行时间确认数据量级和线上接近。然后到线上小流量试跑同时用SHOW PROFILE或者performance_schema确认耗时分布。最后回到慢查询日志连续观察两天确认那条SQL从“高频出现”变成“彻底消失”或者频率大幅下降。这里有个容易被忽视的对比项不只看单条SQL耗时还要看资源消耗。优化前如果CPU被这条SQL占满优化后CPU空闲了这才是真正的效果。有些SQL执行时间没降多少但扫描行数大幅下降那对数据库整体的IO压力缓解也很有帮助。关于验证结果我个人的习惯是整理一份优化前后对照表记录SQL原文、执行时间、扫描行数、类型、Extra信息、索引变化、最终效果。这个表在复盘和技术分享时非常实用也能帮自己积累经验避免同样的坑踩第二次。10. 拿到慢SQL之后先别急着动手最后说一个实际干活时非常容易踩的坑拿到一条慢SQL立刻就想加索引。有一次我分析一条排名靠前的慢SQL从执行计划看就是全表扫描加索引似乎天经地义。但仔细翻日志发现这条SQL执行频率极低一天可能就跑几次而且都是后台报表在调。当时如果真给它加了索引收益非常有限反而每次写入都要背着新索引往前走。所以我现在的习惯是先看慢SQL在整个库里的占比再决定要不要优化、优化到什么程度。一条慢SQL本身不可怕可怕的是它占据了大量总执行时间和资源。判断优先级的标准很朴素优化后能省出来的资源才是这次优化的真实收益。慢查询日志帮我们看到现象EXPLAIN帮我们理解原因但最终决定优化动作的是对业务和数据特征的判断。这两样工具组合起来能解决线上绝大多数MySQL性能问题关键是把分析步骤走完整别在中间跳步。我自己实际用过下来的体会是这套排查流程真正吃透之后从“某个库慢得不行”到“定位到一行SQL”基本只需要十几分钟。剩下的时间都花在理解业务和设计更好的索引上。这比任何“大招”都靠谱。
阅读完成 · 觉得有帮助？
