你有没有见过这种画面一条SQL把数据库CPU打到100%接口超时告警刷屏业务群里一片“数据库怎么了”。我在好几家公司都遇到过最棘手的一次是活动前夕压测慢SQL直接把连接池吃光整个订单服务跟着雪崩。说实话到了那个级别的问题已经不是“加个索引”能糊弄过去的而是需要一套完整的MySQL SQL调优方法。这篇文章想讲的就是MySQL里SQL调优到底怎么落地从慢查询定位、执行计划分析到索引设计、SQL改写再到优化器行为与线上治理闭环每一步我都会配合实际踩过的例子来谈。无论你是写业务代码的开发者还是负责生产库稳定性的DBA或者已经看过不少调优理论、但一到真实业务场景就不知道从哪下手的同学这篇文章应该能帮你把散落的知识点串成一条完整可用的操作路径。1. 先定位再动手从慢查询日志到EXPLAIN的完整排查链路1.1 慢查询日志的正确打开方式很多人在收到“数据库慢”的反馈后第一反应是打开监控看CPU或者直接去问“哪条SQL慢”。这是把因果搞反了——CPU高往往是结果慢SQL才是源头。正确做法是把慢查询日志打开让MySQL帮你记录那些超过阈值的长耗时查询。生产环境我一般这样配置slow_query_log ON slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time 1表示执行时间超过1秒的SQL会被记录默认的10秒太宽松等到10秒的业务早炸了。log_queries_not_using_indexes 1会额外记录那些没走索引的SQL哪怕执行时间只有几十毫秒。这个参数在找“隐性慢SQL”时特别好用很多问题SQL不是真的慢而是全表扫描拖垮了整体性能必须靠它抓出来。变更配置可以写到my.cnf后重启也可以用SET GLOBAL在线修改但log_queries_not_using_indexes的在线修改在部分版本里需要重启才生效建议改完观察确认。日志有了里面可能成千上万条不能肉眼翻。我常用mysqldumpslow按执行次数或总耗时排序先把“最值得处理”的SQL挑出来mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.log-s at按平均时间排序-t 20取前20条。如果公司有percona toolkitpt-query-digest的分析维度更多会按指纹聚合、给出响应时间占比比mysqldumpslow直观很多。1.2 EXPLAIN读懂执行计划的每一列定位到可疑SQL后最核心的步骤就是看执行计划。MySQL里执行EXPLAIN加在SQL前面就能看到优化器打算怎么执行这条语句。但很多初学者只盯着type那一列看到ALL就觉得“完了全表扫了”看到index就以为走索引了这种粗放理解会误判。我一般按这个顺序关注type访问类型从好到差大致是system const eq_ref ref range index ALL。其中index是扫了整棵索引树效率比ALL略好但同样是全量扫描千万不能看到index就放松警惕。key实际用到的索引名possible_keys是可能用到的索引列表两者对比能看出优化器到底选了什么。rows优化器预估要扫描的行数这是个估算值和实际行数可能有偏差偏差大的时候需要盯一下统计信息。filtered经过where条件过滤后剩余行数的百分比rows * filtered大约等于最终返回结果量。Extra这是信息量最大的一列常见的有Using filesort有排序开销、Using temporary用了临时表、Using index覆盖索引扫描、Using where存储引擎返回后Server层再过滤。拿一个实际例子来说EXPLAIN SELECT id, order_no, amount FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 20;如果执行计划里type ref、key idx_user_id、Extra Using filesort说明这条SQL虽然利用了索引过滤用户但排序没有走索引MySQL需要额外做一次文件排序。优化方向通常是加一个(user_id, created_at)的组合索引让过滤和排序都走索引。这个例子特别典型因为“有索引但还慢”的情况有相当一部分问题出在排序字段没有吃到索引红利。1.3 不止EXPLAINEXPLAIN ANALYZE和Optimizer TraceEXPLAIN只能告诉我们优化器“打算”怎么做但它是基于统计信息的预估不一定反映真实运行情况。MySQL 8.0.18及以上提供了一个利器EXPLAIN ANALYZE。它会真实执行SQL并输出每一步的实际耗时和扫描行数对于判断“预估和实际差距大”的场景特别有效。EXPLAIN ANALYZE SELECT id, order_no, amount FROM orders WHERE user_id 123 ORDER BY created_at DESC LIMIT 20;输出结果会带上每个步骤的耗时占比比如Sort: 2.3s、Index lookup on orders using idx_user_id之类的信息。我拿到后通常先看耗时最长的步骤再反推是索引没用对、排序太重还是连接次数太多。如果需要更细节地看优化器决策过程可以开启optimizer_traceSET SESSION optimizer_trace enabledon; -- 再执行一次你的SQL SELECT * FROM information_schema.OPTIMIZER_TRACE\G这里能看到优化器对每个可行执行方案的成本估算为什么选了A索引而不是B索引一目了然。不过这个输出格式很冗长适合在疑难杂症时用日常调优不需要每次都看。1.4 首先判断SQL慢还是数据库慢有一类问题容易被忽略——SQL本身没问题是整个数据库负载太高所有人的SQL都慢。这种情况下你分析单条SQL会走很大弯路。区分方法是看慢查询日志里的时间分布。如果慢SQL集中在某个时间段且涉及的表五花八门、执行计划都很正常大概率是数据库整体的CPU、IO或锁竞争出了问题。这时候应当去看全局状态而不是纠结单条查询。我有个习惯接到“SQL慢”的反馈先花3分钟看SHOW GLOBAL STATUS LIKE Threads_running、SHOW ENGINE INNODB STATUS里的事务和锁信息确认到底是单点还是面性故障。这个习惯救过我很多次避免在错误的表上折腾两个小时。2. 索引设计SQL调优的胜负手为什么加了索引还是不生效2.1 索引到底加速了什么要理解SQL调优绕不开索引的工作原理。MySQL的InnoDB引擎用的是B树索引聚簇索引的叶子节点直接存着整行数据二级索引的叶子节点存的是主键值和一个索引字段值的组合。你可以把聚簇索引想象成一本按页码排列的字典正文二级索引是一本单独的“检索目录”。通过目录找到词条再翻回正文那一页——这个过程叫“回表”。如果你查询所需的字段恰好都在二级索引里就不需要翻正文了这叫“覆盖索引”性能会有质的提升。理解了回表和覆盖索引再看很多索引设计的问题就能想通为什么不要无脑给所有字段加索引因为每个二级索引都要占空间每次写入都要维护索引太多会让写入放大。为什么建议组合索引尽量覆盖高频查询的字段因为可以减少回表次数。2.2 最左前缀原则不是死记硬背组合索引(a, b, c)会按照a、然后b、然后c的顺序建立一棵B树。查询条件里如果包含索引最左侧的列优化器才有可能用上这个索引。具体能用多长取决于条件里等值命中到哪里。很多人死记“最左前缀”却踩过这个坑有一条SQL是WHERE b ? AND c ?没有条件用a但有位同学认为既然建了组合索引(a,b,c)其中两个字段都在索引里应该能走索引吧。答案是不会因为没有从最左列开始匹配这棵树根本没法定位。正确做法是再建(b,c)或者(c,b)的索引。还有一点容易被忽略等值条件的顺序不影响索引使用。WHERE b 1 AND a 2和WHERE a 2 AND b 1在优化器眼里是一样的MySQL会自己做等价转换不需要你特意调换字段顺序。2.3 索引失效的六种典型场景与其背“失效规则”不如理解失效的本质一旦索引列被“变形”导致B树的有序性无法利用优化器就只能放弃它。这是最核心的原因。场景例子本质原因常见改写索引列套函数WHERE DATE(created_at)2024-01-01函数破坏了有序性改范围为created_at 2024-01-01 AND created_at 2024-01-02隐式类型转换varchar列phone与数字比较类型转换让索引列变形改成字符串比较phone 13912345678LIKE前置通配符WHERE name LIKE %张三%无法确定后缀起点能避免就避免或考虑全文索引索引列参与运算WHERE price * 1.1 100运算破坏有序性移到等式另一边OR连接非索引条件WHERE a 1 OR b 2b无索引无法合并两种访问路径拆成UNION或给b也建索引排序与过滤字段不搭过滤走(a)排序用b索引建议顺序不匹配调整为(a,b)覆盖两边需求这些场景在实际工单里出现频率很高。尤其是隐式类型转换因为隐藏性极强执行计划看上去key有值但rows依旧很大不仔细看字段类型很难发现。我排查这类问题时有个习惯在EXPLAIN之后先用SHOW WARNINGSMySQL会告诉你改写后的SQL长什么样一眼就能看到是不是多做了一次转换。2.4 组合索引设计字段顺序的取舍逻辑组合索引的设计我一般遵循三个原则第一等值查询的字段放前面。WHERE a 1 AND b 2这种等值条件对索引定位的压缩最有效不管字段区分度多高、放在前面都能快速筛掉大量数据。第二排序字段跟在等值字段之后。如果查询是WHERE a 1 ORDER BY b那么设计(a,b)索引排序直接走索引顺序避免filesort。第三在满足前两条前提下区分度高的字段放在更靠前的位置。因为区分度高的字段能让B树的分支更快收敛。如果你纠结(status, created_at)和(created_at, status)哪个好status可能只有几个枚举值区分度低那优先把created_at放前面除非业务上status是必然的等值条件。还有一个进阶经验尽量构造覆盖索引。比如要查orders表的user_id和amount建一个(user_id, amount)索引查询两个字段都不用回表。覆盖索引在高并发场景下的收益非常可观它直接把一次查询从两次IO压缩到一次索引扫描。3. SQL写法上的常见病与药理从改写中要性能3.1 深分页LIMIT 10万行后的魔鬼细节翻页是业务系统里最常见的需求但“深分页”是性能杀手。SELECT id, order_no, amount FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 100000, 20;这条SQL的逻辑是先扫到第100020行然后扔掉前100000行只返回20行。数据库扫描的物理行数远超需要返回的行数翻页越深成本越高。实测在千万级表上翻到10000页时这条SQL可能从几十毫秒涨到几秒甚至十几秒。常见的改写方案有两个方案一是“延迟关联”也叫延迟join。先查出目标行的主键再用主键关联回原表取完整数据SELECT t.id, t.order_no, t.amount FROM ( SELECT id FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 100000, 20 ) tmp JOIN orders t ON tmp.id t.id;内层查询只扫二级索引速度远快于回表扫全行拿到20个主键后再回去取20行完整数据。方案二是“基于游标”的翻页用WHERE id 上一次返回的最小id替代LIMIT OFFSETSELECT id, order_no, amount FROM orders WHERE user_id 123 AND id 100000 ORDER BY id DESC LIMIT 20;这种方案适合App端“下拉加载更多”的场景前提是排序字段是有序的主键且翻页条件能用上主键定位。它把深分页的不确定性直接消灭了每页扫描行数恒定。方案一适合无法改前端传参的老系统方案二适合能改协议的类无限滚动场景。两者都值得掌握我线上处理过的大部分深分页问题都能用其中之一解决。3.2 隐式类型转换和字符集不一致两个隐形杀手隐式类型转换在上一节索引失效的场景里提过我再展开一个具体例子。常见事故是phone字段是varchar(11)SQL写成WHERE phone 13800138000数字和字符串比较MySQL会把字段值转成数字做比较索引列就被函数包裹了索引失效。排查这类问题的小技巧是和SHOW WARNINGS配合。执行EXPLAIN后紧接着执行SHOW WARNINGSMySQL可能提示“Cannot use index ... due to type or collation conversion on column”这句话几乎是直接告诉你索引为什么失效。字符集不一致也是一个隐蔽问题。如果关联的两张表一张用utf8mb4、另一张用latin1JOIN的时候MySQL需要把一边的字符串做转换才能比较和类型转换一样会让索引失效。这种情况在从老系统迁移、或者新老表混用的库中很常见我处理过多次。解决办法是统一表字段的字符集和排序规则至少在关联字段上做到一致。3.3 条件写法IN、OR、BETWEEN、LIKE的索引利用差异同样的业务需求不同写法对索引利用的差异很大。OR连接多个条件的时候只要其中一个字段没有索引整个OR就没法走索引MySQL只能在外层做全表扫描。遇到这种情况最直接的改写是把OR拆成多个等值条件用UNION ALL合并结果-- 原始写法 SELECT * FROM orders WHERE user_id 123 OR status PAID; -- 改写 SELECT * FROM orders WHERE user_id 123 UNION ALL SELECT * FROM orders WHERE status PAID;但要小心OR的分支如果有重叠UNION ALL会多返回重复数据这时需要改成UNION去重。两个分支如果都是高频查询给两个字段分别建索引配合UNION走两个ref访问路径效果也会好很多。IN在MySQL 5.6之后可以做索引条件下推优化能走range扫描一般比OR改写省事。但如果IN列表特别大比如几千上万个值执行计划仍然可能退化成全表扫描因为优化器要评估列表长度。遇到超大IN列表可以考虑分批查询或在业务侧用临时表join。BETWEEN是范围查询能利用range访问。LIKE的尾部通配符abc%也能走range但前置通配符%abc不行。这些规则本质上都和B树的有序定位能力相关理解原理后不难记住。3.4 多表关联驱动表是谁为什么那么重要多表JOIN慢很多时候不是索引问题而是驱动表顺序错了。MySQL会选择一个驱动表然后循环去另一张表匹配。默认期望是用“小表驱动大表”因为外层扫描次数越少越好。看执行计划时EXPLAIN输出的靠前几行通常是驱动表。如果大表在最前面小表在后面通常意味着优化器选择的路径不理想。这时候有几个办法给被驱动表的关联字段加索引。这一步是最基本的没加索引的JOIN会变成嵌套循环全表扫。调整查询条件让SQL结构引导优化器选择小表作为驱动表。比如在派生表里先把大表过滤到很小的结果集再参与JOIN。在极端情况下可以STRAIGHT_JOIN强制指定驱动表顺序但这是最后的办法别一上来就用。另外JOIN的条件如果出现隐式类型转换或两边字段的字符集不一致也会导致索引失效被驱动表怎么加索引都没用。遇到JOIN慢的问题第一件事永远不是改SQL而是确认关联字段类型、字符集和索引三件事是否齐备。3.5 ORDER BY和GROUP BY的隐性成本很多人只关注WHERE条件是否走索引把排序和分组完全忽略。但实际上SQL执行计划里Using filesort和Using temporary往往是慢查询的主要耗时点。ORDER BY能走索引的条件是排序字段满足索引的最左前缀且排序方向一致。一旦要排序的字段和WHERE条件不能共用同一个有序结构MySQL就要在内存或磁盘上做文件排序。数据量小时毫秒级数据量大时排序开销能拖垮整个查询。GROUP BY底层通常要做分组和排序。如果分组字段没有索引它很可能生成临时表再配合字段比较多的大表查询内存不够就落到磁盘临时表性能断崖式下跌。优化思路是给分组字段建合适的索引或者在不影响业务的前提下把需求改成用唯一键上的子查询先收敛范围。这条经验我特别想强调SQL调优别只盯着查询条件执行计划里任何一步出现 filesort/temporary都要当作重点嫌疑对象去排查。4. 别忽略优化器统计信息、意外执行计划与force index4.1 统计信息过期这几天还正常的SQL突然慢了有一种非常磨人的调优场景SQL一直运行很好某天突然变慢但SQL和索引都没改过。这时候最可能的元凶是统计信息过期。MySQL优化器决定走哪个索引靠的是每个索引的基数cardinality和选择性等统计信息。这些信息不是实时的而是采样估计的。当表数据大量变更大批量删除、导入、更新统计信息和真实数据偏差过大优化器可能做出错误的成本判断——用一个区分度很低的索引或者直接选择全表扫。我遇到过一个真实案例一张流水表平时几百万行优化器走idx_user_id只需要几十毫秒。某天做了一次批量历史数据清理删了将近40%的数据analytics报表SQL一下就慢了10倍。原因就是删完之后统计信息没来得及更新优化器还按旧的数据分布估算成本选了一个错索引。排查方法很简单执行ANALYZE TABLE 表名;更新统计信息再看执行计划是否变化。这个方法成本极低遇到“突然变慢”的问题应该先做而不是一上来就改SQL加索引。我自己的处理顺序是先ANALYZE再EXPLAIN再动手改。4.2 为什么优化器会“选错”索引统计信息没问题的前提下优化器也可能选错索引。原因在于优化器评估的是“估算成本”不是真实成本。它根据统计信息估算扫描行数再结合内存、IO模型算一个成本分选最低的。如果统计采样不准或者索引数据分布极度不均匀比如某个值的行数占了90%估算就会失准。这类问题的典型特征执行计划里的rows和真实返回行数差很多倍。EXPLAIN ANALYZE在8.0里能直接反映真实行数拿它和EXPLAIN的预估对比一旦发现差异巨大就说明优化器基于了一个错误的“世界观”在做选择。4.3 干预手段USE INDEX、FORCE INDEX的使用边界确定优化器选错索引后怎么干预我一般按这个顺序尝试先试试USE INDEX它只是给MySQL一个建议最终选择权还在优化器手里SELECT * FROM orders USE INDEX (idx_user_id) WHERE user_id 123;如果USE INDEX不管用再用FORCE INDEX强制指定索引SELECT * FROM orders FORCE INDEX (idx_user_id) WHERE user_id 123;但FORCE INDEX是把双刃剑。它写死在SQL里一旦这张表的索引结构调整、或者数据分布再次变化这条SQL可能因为被迫走一个不合适的索引而更慢。所以我的建议是FORCE INDEX只能作为短期止血手段长期方案还是要修正索引本身或者把SQL改写成能让优化器自然选对索引的形式。可以在发布FORCE INDEX的变更单上顺手写一条“下个迭代优化索引设计”的备忘别让这种临时方案活在线上无人问津。还有一类干预手段是optimizer_switch比如关闭某个特性来改变优化器的行为。这个影响面更大属于全局维度我不到万不得已不去动它因为影响的不只是一条SQL而是整个实例的计划选择。4.4 rows偏差、key_len、filtered如何用EXPLAIN细节反推优化方向再回到EXPLAIN本身有三个细节值得多花点时间看。key_len表示索引字段的最大字节长度它能告诉你SQL实际使用了组合索引里的几个字段。比如(a,b,c)组合索引如果key_len只有字段a的长度说明只用了a一个字段。这个信息比肉眼看SQL猜“应该用全索引”要可靠得多。filtered前面说过是过滤比例。如果rows * filtered和真实返回行数差距很大说明过滤条件中有些字段实际上有很强选择性但索引没有覆盖到它导致MySQL在Server层做了大量额外的where再过滤。优化方向就是把那个字段加到索引里让过滤在索引层一并完成。rows偏差大时除了统计信息问题还要注意是不是SQL里有非等值条件或JOIN产生笛卡尔积的中间结果。遇到偏差大的情况结合EXPLAIN ANALYZE确认实际行数别只看预估就下结论。5. 慢SQL治理闭环线上真实案例的完整复盘5.1 一次典型慢查询的完整排查过程说了这么多方法论我拿一个真实处理过的订单查询案例串一遍。背景一张订单表约3000万行业务方反馈“订单列表第10000页以后打不开”接口超时超过10秒。原SQL大概是这样SELECT id, order_no, amount, status, created_at FROM orders WHERE user_id 8823 ORDER BY created_at DESC LIMIT 100000, 20;我拿到反馈后没急着看代码先做四件事慢查询日志里找到这条SQL确认平均耗时3.2秒。EXPLAIN看执行计划type ref走了idx_user_id但Extra Using filesort说明排序没有吃上索引。SHOW WARNINGS没有类型转换问题。检查表结构idx_user_id只包含user_id一个字段created_at不在任何索引里。问题诊断很清晰过滤走索引没问题但排序要额外做文件排序再加上LIMIT 100000的深分页需要先扫10万行再丢掉双重开销叠加SQL自然跑不动。5.2 优化步骤与效果对比我做了两处修改第一把索引从idx_user_id调整为idx_user_id_created_at (user_id, created_at)让排序直接利用索引的有序性去掉filesort。第二把接口的翻页逻辑从LIMIT OFFSET改为游标式翻页前端传上一页的最小created_atSQL改成SELECT id, order_no, amount, status, created_at FROM orders WHERE user_id 8823 AND created_at 上次返回的最小值 ORDER BY created_at DESC LIMIT 20;优化后的对比指标优化前优化后第1页耗时42ms35ms第10000页耗时3.2s38msfilesort有无每页扫描行数约100000约20这次改动没有动业务逻辑只是把索引和翻页协议调整了一下效果立竿见影。核心经验是深分页和排序优化要一起解决单改一个往往不够。5.3 参数配合说不清的三件套与真正该调的参数谈到性能调优很多人会提到“参数三件套”不同场景说法不一但本质上绕不开内存和IO相关的核心参数。我的观点是SQL调优不要一上来就调参参数是配合SQL执行方式发挥作用的SQL没写对参数调到天上去也白搭。在SQL层面优化到位前提下最值得关注的参数有这几个innodb_buffer_pool_sizeInnoDB的缓冲池大小决定数据页和索引页能有多少驻留内存。生产经验值通常是物理内存的60%-75%。如果该值过小热点数据频繁从磁盘读入再好的SQL也会因为IO放大而变慢。join_buffer_size每次JOIN操作在内存中分配的缓冲区大小。小表驱动大表时这个缓冲区可以承载被驱动表的匹配操作避免磁盘临时表。但它是会话级参数不能盲目调太大否则高并发下内存会迅速被多个会话吃满。sort_buffer_size排序缓冲区ORDER BY或GROUP BY时用到。同样不建议全局调太大。我见过有人把它调到256MB结果几十个并发排序直接把内存打爆。tmp_table_size和max_heap_size决定内存临时表上限超过后落盘。参数调整有个基本原则先定位瓶颈在CPU、内存还是IO再对症下药。不是打开配置文件把所有参数都改一遍更不是照着网上的“最佳配置”无脑抄。每台机器的内存、磁盘类型、业务负载都不一样参数必须是基于观测结果的决策否则就是碰运气。5.4 把SQL调优固化到开发流程而不是救火最后说一点比调优本身更重要的经验。我见过很多团队数据库一慢就找DBA救火DBA分析完发一条慢SQL产出报告然后就没有然后了。同样的SQL可能过了三个月又被别人写出来再慢一次。所以真正有价值的做法是把SQL调优的检查点前移到开发和发布阶段上线前用EXPLAIN检查所有新SQL的执行计划凡是出现Using filesort、Using temporary、type ALL的都要给出合理解释才能过评审。慢查询日志每周分析一次把响应时间占比最高的前10条SQL拿出来复盘重点看新增的慢SQL是不是来自最近上线的需求。建表阶段就评估索引设计而不是等慢查询出来了再补索引。一张表建索引要统筹考虑高频查询的where、order by、group by和join条件而不是为每个查询单独建一个索引。遇到大批量更新、导入、删除后主动执行ANALYZE TABLE减少统计信息过期带来的执行计划突变。我这里最后补充一个关于字符集的小提醒。很多老项目里表之间混用了utf8和utf8mb4JOIN关联字段如果不统一MySQL做字符集转换时可能直接让索引失效。这种问题排查起来比慢查询日志还隐蔽因为执行计划看着走了索引但实际还要做转换。检查全库表和字段的字符集统一性应该列入每次数据库巡检的固定动作。SQL调优这件事说难也难说简单也简单。难在每次问题都可能是多种因素叠加简单在只要按“定位慢查询、读懂执行计划、优化索引与SQL写法、验证并复盘”这个闭环走绝大多数慢SQL都能在半小时内给出明确结论。多在自己的环境里练习分析EXPLAIN遇到真实案例时你才不会慌。