1. 慢SQL排查链路从发现到定位的完整方法接手任何一个MySQL优化任务首先要想清楚一件事优化不是凭感觉猜而是要有数据支撑。我见过太多人上来就改索引、调参数结果改了半天慢查询还在那里原因就是没找到真正的瓶颈。这一节先讲清楚排查链路后面所有优化动作都是建立在这个基础上的。1.1 慢查询日志最廉价也最有效的入口MySQL自身的慢查询日志是第一手材料不需要额外装任何工具就能开启。我建议在测试环境或者低峰期的生产环境做如下配置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 100这里几个参数要解释一下。long_query_time设置成1秒是个相对务实的值——如果业务比较简单0.5秒也可以但不要一上来就设0.1秒否则日志量会爆炸反而淹没了真正的问题。log_queries_not_using_indexes用于记录那些没走索引的查询配合min_examined_row_limit可以过滤掉扫描行数很少的查询避免日志被小查询刷屏。日志开启后用mysqldumpslow工具做聚合分析是基本功mysqldumpslow -s at -t 20 /var/log/mysql/slow.log-s at表示按平均查询时间排序-t 20取前20条。这个命令能快速告诉你哪些SQL是真正的常客——注意优化价值最高的是那些执行次数多且单次耗时不低的SQL而不是单次极慢但很少执行的SQL。前者优化一次整个系统都受益。1.2 用EXPLAIN读懂执行计划别只看type和rows拿到慢SQL之后第一件事就是用EXPLAIN看执行计划。网上很多文章教人只看type字段什么ALL全表扫描不行、ref可以、const最好这个说法没错但远远不够。我习惯按这个顺序逐列检查字段关注点常见问题select_type是否出现DEPENDENT SUBQUERY相关子查询通常是性能杀手table关联顺序驱动表选择是否合理type访问类型出现ALL或index需要警惕possible_keys可能用到的索引为空说明没有可用索引key实际用到的索引与possible_keys差距大说明优化器没选好key_len索引使用长度值偏小说明索引利用不充分尤其是联合索引ref等值匹配的列const最优多列关联要留意顺序rows预估扫描行数与实际值偏差过大说明统计信息不准ExtraUsing filesort / Using temporary / Using index三类信息分别对应排序、临时表、覆盖索引举个实际排查过的例子。有个订单查询慢EXPLAIN显示typeALL、rows380万但possible_keys里明明有idx_user_id。为什么优化器不用看了一眼WHERE条件WHERE user_id 0 12345——对索引列做了隐式运算索引直接失效。这就是为什么只看type不够必须结合WHERE条件和key_len一起分析。1.3 定位索引失效的几类典型场景这里顺便把索引失效的主要场景列一下都是实战中非常高频的坑类型隐式转换WHERE phone 13800138000如果phone是VARCHARMySQL会尝试把字符串转成数字再比较导致索引失效。解决办法是让类型严格一致或者主动加引号。对索引列使用函数或运算WHERE DATE(create_time) 2025-01-01这种写法直接让create_time上的索引失效。改写思路是create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00。LIKE前置通配符LIKE %keyword%无法走索引。可以考虑用全文索引或者把模糊匹配拆成前缀匹配加后续筛选。OR条件中包含非索引列WHERE id 1 OR status pending如果status上没有索引优化器可能放弃id的索引。改写为UNION ALL或者给status也加上索引。联合索引未遵守最左前缀在(user_id, status, create_time)联合索引上直接WHERE status pending没法走索引。隐式字符集不统一JOIN两表的关联字段一个utf8mb4一个utf8mb4_general_ci或乱序比较会产生隐式转换索引失效。这一点在联表查询里很隐蔽。NOT IN / NOT EXISTS / !通常会导致索引失效需要结合业务改写为LEFT JOIN ... IS NULL或用EXISTS替代。2. 联合索引与优化器取舍为什么有时候索引加了也不走索引基础知识网上一抓一大把我想重点讲的是联合索引的字段顺序设计和优化器为什么不走索引。这两个问题恰恰是区分新手和老手的地方。2.1 联合索引字段顺序区分度与查询模式的平衡联合索引(a, b, c)相当于建了(a)、(a,b)、(a,b,c)三个索引这是最左前缀原则。但实际设计字段顺序的时候很多人的第一反应是哪个区分度高就放前面这个原则不能机械套用。举个例子。订单表有一个联合索引要覆盖常见的查询WHERE user_id ? AND status ? ORDER BY create_time DESC。user_id区分度当然比status高但如果把user_id放第一位、status放第二位、create_time放第三位那最终排序还得走filesort——因为status只用了等值匹配create_time在这个查询里是前导列等值之后的排序字段这是可以利用索引有序性的。**设计联合索引的第一步不是想字段选择性而是列出这个索引要服务的所有核心查询找出它们的公共等值条件。**公共等值字段放最前面紧接着是排序字段最后才是范围查询字段。范围字段放中间会截断后续字段的有序利用。尽量把范围条件放到联合索引的末尾。假设核心查询是WHERE user_id ? AND status ? ORDER BY create_time DESC那么设计(user_id, status, create_time)就是合理的排序直接走索引不需要临时文件排序。反过来如果一个查询是WHERE user_id ? AND create_time BETWEEN ? AND ?那(user_id, create_time)就是更好的选择因为你没法同时让create_time既做范围过滤又维持后续字段有序。2.2 优化器选错索引怎么处理才不硬碰硬有一种很尴尬的场景你建好了索引EXPLAIN一看优化器偏偏选了另一个区分度很低的索引。常见原因是统计信息不准也可能是优化器基于成本模型判断失误。这时候不要急着FORCE INDEX因为一旦数据分布变化FORCE INDEX会成为负优化。先试试ANALYZE TABLE更新统计信息这个操作成本很低有时候跑了立刻就好了。如果还不行可以考虑改写SQL让优化器走预期路径比如转换写法、调整JOIN顺序。实在不行也可以用FORCE INDEX但要加注释说明原因并定期手动检查——这是把双刃剑。另外要提一嘴MySQL 8.0引入了不可见索引INVISIBLE INDEX可以先把索引设置为不可见观察一段时间确认没有业务依赖后再删除比直接DROP安全得多。做索引下线的同学建议用这个特性。2.3 索引下推与覆盖索引两个容易被忽略的关键特性索引下推Index Condition PushdownICP在网络热词里经常被问到。简单说在没有ICP之前存储引擎通过索引拿到数据行之后要回表把整行数据交给Server层再由Server层过滤其它条件。有了ICP之后能在索引层直接过滤的条件会在存储引擎层面先做掉减少回表次数和Server层处理的数据量。这对联合索引尤其有用——联合索引中某个字段没法走索引过滤但可以通过ICP在索引扫描时提前过滤掉一部分行。覆盖索引的价值就更直接了。如果查询的字段全部在索引节点里那就不需要回表。在设计索引时可以考虑把高频查询需要的字段塞进联合索引的尾部让它变成覆盖索引。但要权衡好索引字段越多写入性能和存储成本越高不要为了偶尔一次查询把所有字段都塞进索引。3. SQL改写与慢查询治理从执行计划到真实收益索引加好了执行计划也对了但很多情况下SQL本身的写法还在拖后腿。这一节专门讲SQL改写。它不像加索引那么直观但往往是收益最明显的一环。3.1 子查询与JOIN的取舍MySQL 5.6之前IN (SELECT ...)的优化做得不好很多时候会把外层表逐行代入子查询性能很差。5.6之后优化器做了改进但依然不能说所有子查询都安全。我的经验是能用JOIN表达的关联逻辑优先用JOIN。看一个具体场景。统计近30天有下单的VIP用户-- 不推荐 SELECT id, name FROM users WHERE vip 1 AND id IN ( SELECT user_id FROM orders WHERE create_time 2025-01-01 );改为SELECT DISTINCT u.id, u.name FROM users u INNER JOIN orders o ON o.user_id u.id WHERE u.vip 1 AND o.create_time 2025-01-01;改成JOIN之后执行计划更清晰优化器能更好地选择驱动表和join buffer策略。但也要注意JOIN如果搞出超大中间结果集也会出问题。关键是看EXPLAIN里的rows和实际耗时而不是凭感觉选写法。3.2 大偏移量分页优化LIMIT 100000, 20为何这么慢很多人在分页上吃过亏。LIMIT 100000, 20慢不是因为要取20条而是因为要先扫描并丢弃前100000条记录再取20条。越往后的页扫描成本越高。常见的优化方案有两种。一种是用覆盖索引先定位主键再回表取数据SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON tmp.id orders.id;这里内层查询走了(create_time, id)的覆盖索引只扫描索引不碰数据行获取20个主键后再回表代价小很多。另一种是基于排序字段的游标分页keyset pagination适合评论区、消息列表这类场景SELECT * FROM orders WHERE create_time 上一页最后一条记录的create_time ORDER BY create_time DESC LIMIT 20;当然游标分页的前提是排序字段足够唯一否则会出现漏数据。需要在排序字段上加一个唯一性兜底比如ORDER BY create_time DESC, id DESC同时WHERE条件也要带上(create_time, id)的联合游标条件。3.3 深挖慢日志中的高频模式打散大事务与批量写日志看多了会发现慢SQL很多时候不是单条语句的问题而是大事务或者批量任务把资源占住了导致普通查询排队。典型场景是凌晨的批量对账任务一个事务里更新几十万条记录持有行锁不释放白天的查询全部被堵住。对这种任务核心思路是拆批把50万条数据的更新拆成每批1000条分批提交。批与批之间加一点延迟给其它会话让路。这不是SQL层面的花活而是工程层面的取舍。很多慢查询治理到后面拼的不是写SQL的技巧而是对业务场景和锁竞争的理解。另外SELECT里用*的问题也要提。不需要的列尽量别查不是为了省那点带宽而是尽可能让查询命中覆盖索引。SELECT *大概率会让覆盖索引失效哪怕只是多取一列不需要的TEXT字段都可能让原本已经在索引里完成的操作被迫回表。4. 分库分表的启动信号与中间件选型到底什么时候该上前面讲的索引和SQL优化都是单库单表还能扛得住的范围。一旦数据量和QPS上到一个量级单库单表再优化也有物理上限。这一节聊分库分表。4.1 什么时候才应该考虑分库分表先说结论分库分表是系统复杂度剧增的起点能不上就不上。我见过太多业务数据量只有几千万就兴师动众搞分表结果把简单查询硬生生变成了路由计算和数据聚合现场。分库分表的启动信号是什么我个人的判断标准单表数据量超过2000万~5000万跟行宽和索引大小有关且常规索引优化、归档、冷热分离都已经做过一遍写入QPS持续高涨单库写入成为瓶颈Buffer Pool命中率下降主从复制延迟成为常态化问题核心查询即使命中了索引单次响应时间也在恶化因为索引体积变大BTree层数增加随机IO成本上升。如果只是数据量大但读多写少优先考虑读写分离 归档 冷热数据分离 缓存这些方案的成本比分库分表低一个量级。分库分表应该是被业务逼到墙角之后的最后手段而不是未雨绸缪的第一选择。4.2 中间件选型ShardingSphere vs MyCat以及其他选项这几年分库分表的中间件格局变化不小。主流选择就三个方向应用层ShardingSphere-JDBC、代理层ShardingSphere-Proxy、代理层MyCat。简单对比一下维度ShardingSphere-JDBCShardingSphere-ProxyMyCat部署方式应用内集成jar包独立服务应用无感知独立服务应用无感知性能损耗低SQL解析在应用侧中多一次网络交互中高历史版本有较多瓶颈功能生态分片、读写分离、数据加密、分布式事务同左还支持SQL网关分片、读写分离较强依赖XML配置维护成本需要应用发版DBA维护独立中间件集群DBA维护适合场景新项目或可以发版改造的存量项目多语言团队、希望集中管控历史系统逐步改造我的倾向是新项目可以直接用ShardingSphere-JDBC因为它在应用内做路由没有额外网络跳转性能更好而且分片规则由应用侧管理开发可控。多团队、多语言异构的场景才考虑Proxy模式让DBA统一管理规则应用方只对接一个标准MySQL协议端口。用中间件之前先用一个标准问题自测这个系统里是否所有SQL都能明确回答它要访问哪张分片如果存在大量非分片键查询比如运营后台各种按名称、按状态模糊查分库分表之后要么全路由扫全片要么引入ES这种二级索引体系复杂度直接翻倍。4.3 分片键选错之后一次真实的教训选分片键是分库分表最关键、也最难改的决定。分享一个教训。之前做过一个订单系统分片当时拍板用order_id做分片键理由是订单ID主键分布均匀范围也好。结果上线半年后运营高频需求是查某个用户的全部订单而user_id不是分片键这类查询只能对所有分片做广播路由每来一次就压垮一次数据库。正确做法是在设计分片键时把未来一年最高频的查询场景枚举出来优先保证这些查询能通过分片键直接定位到分片。订单类系统通常用user_id做分片键更合理因为查某用户的订单是最高频天然维度。如果还需要查某订单详情则可以通过订单号反查分片在订单号里带上用户ID的冗余或者维护一个映射表。分片键的设计不是纯技术问题是业务模型问题。中间件只是执行你定的路由规则定错了规则后续想改意味着所有存量数据要重分布成本巨大。4.4 分库分表之后四个躲不开的连带问题分库分表只解决了数据和写入的天花板但会带来一系列新问题。列四个最常见的全局主键单库自增ID彻底失效。常见方案是雪花算法Snowflake但这依赖机器时钟要注意时钟回拨问题也可以做号段模式从独立发号器批量取号段性能可控。跨分片分页与排序ORDER BY create_time LIMIT 10在10个分片上就变成每个分片各取10条再在应用层合并排序深度分页时每个分片都要把offset之前的数据查出来放大效应明显。应对思路是禁止深分页或者用ES承担这层搜索聚合职责。分布式事务原来一个事务就能完成的多表明细更新分片后可能横跨多个数据库。方案从全局XA到TCC到消息最终一致性都有没有银弹。能用最终一致性解决的问题不要上强一致。数据迁移与扩容分片数定死未来扩容又要重分布数据。常见方案是分片数按2的倍数扩容配合基于hash取模范围映射的平滑迁移工具。分组法分成N个逻辑库组每组若干物理分片能缓解但解决不了根本问题。5. 优化器统计信息与参数调优容易被忽略的收尾工作索引和SQL是MySQL优化的明面功夫但经常有个情况——SQL写得没问题索引也在可生产环境就是慢。这时候要看看优化器统计信息和MySQL自身的参数配置。5.1 统计信息不准引发的幽灵慢查询MySQL优化器决定是否用索引依赖SHOW INDEX里的Cardinality等统计信息。如果表的增删改频繁而统计信息没及时更新优化器会基于过期数据做判断选出错误执行计划。一个经典场景某个状态字段status只有2个值分布相对均匀各50%你建了索引优化器也不太会用因为它觉得全表扫描一半数据走索引也没意义。但是某天业务出现倾斜某个状态值占比突然只有1%索引开始有价值了可统计信息还没跟上于是明明可以秒回的查询跑了全表扫描。解决办法是评估是否需要调整innodb_stats_on_metadata以及innodb_stats_persistent的采样设置。批量更新数据之后及时跑一次ANALYZE TABLE。8.0里可以对大表做ANALYZE TABLE ... WITH 64 BUFFERED ROWS之类的采样控制灵活一些。5.2 InnoDB关键参数不那么玄幻但确实有影响很多人喜欢背一堆Buffer Pool参数但真正跟线上优化强相关的我建议先关注这几个参数作用建议innodb_buffer_pool_sizeInnoDB缓冲池大小设为物理内存的60%~75%不要贪多留给OS Cache和连接数innodb_change_buffer_size变更缓冲默认25%如果写多读少可适当提高反之调低innodb_log_file_sizeRedo日志大小日志偏小会导致频繁刷盘建议1GB~4GB起步long_query_time慢查询阈值线上建议1s核心库可以0.5smax_connections最大连接数别盲目调大连接多不一定好配合thread_pool或连接池控制innodb_buffer_pool_instances缓冲池实例数大内存机器建议设置为多实例减少缓存页竞争参数调优的原则是一次只动一个参数观察效果再动下一个。切忌从网上找一份最牛配置直接照抄机器内存、磁盘类型、业务模型都不一样照抄容易出事。5.3 定期巡检把优化做在事故发生之前最后分享一个日常巡检的清单。我每个月会在核心库上做一轮慢查询日志趋势是否上升新增慢SQL是否有人认领和跟进performance_schema或者sys库里的语句延迟Top N是否有明显变化索引使用率sys.schema_unused_indexes查一查长期没用的索引择机下线表碎片情况OPTIMIZE TABLE不要频繁跑但要关注是否有大量UPDATE/DELETE导致表膨胀SHOW GLOBAL STATUS里的关键指标Innodb_row_lock_current_waits、Threads_running、QPS变化趋势。这种巡检不复杂但贵在坚持。优化工作真正的护城河不是哪一次灵光一现把某个慢SQL从10秒优化到10毫秒而是建立一套可持续发现、整改、回归的机制。6. 一条慢SQL的完整排查复盘与最终落地效果用一条真实案例收尾。前一阵一个库存扣减接口出现频繁超时trace_id显示一段时间内P99延迟从80ms涨到1.2s。我按前面说的链路排查了一遍。6.1 从慢日志到执行计划一步步锁死问题第一步慢日志里捞到了核心SQLSELECT * FROM inventory WHERE sku_id SKU1024 AND warehouse_id 5 AND status available ORDER BY create_time DESC LIMIT 10;先看执行计划结果是typeALLrows850万但表里明明有(sku_id, warehouse_id, status)的联合索引。这看起来不该全表扫描。再看表结构发现sku_id是VARCHAR(64)但SQL里传入的是字符串不存在数字隐式转换。继续查发现联合索引的字段顺序是(status, warehouse_id, sku_id)而查询以sku_id和warehouse_id为等值条件status只是过滤条件之一——最左前缀失效了。这里还有个细节ORDER BY create_time DESC也需要看能不能走索引。现有联合索引无论如何都没法覆盖排序需求所以Extra字段大概率是Using filesort。6.2 索引重建与SQL微调的组合拳我的调整方案分两步第一步重新设计联合索引改成(sku_id, warehouse_id, status, create_time)。sku_id和warehouse_id是最高频等值条件放前面status继续做过滤create_time放在最后利用BTree有序性消除filesort。注意statusavailable在业务上是绝大多数库存的常态区分度极低但它不会阻断后续create_time的排序用途放在第三个位置是为了过滤语义同时不影响整体有效性。第二步SQL层面把SELECT *改成SELECT sku_id, warehouse_id, status, quantity, update_time这些字段都在索引或主键的覆盖范围内吗quantity如果不在索引里就还需要回表。为了极致的覆盖索引效果可以把quantity也加进索引尾部(sku_id, warehouse_id, status, create_time, quantity)这样整个查询都在索引里完成零回表。当然这个方案要看写入频率是否可接受额外索引存储成本库存表写多读多但索引尺寸增加对写入的压力在可控范围内。改造后执行计划typeref、key_len大幅增加、rows降到个位数、ExtraUsing index。线上实测P99从1.2s回落到70ms左右效果非常明显。6.3 复盘后沉淀的可复用检查清单这个案例本身不复杂但能代表一类问题索引设计必须从实际SQL出发而不能从字段语义出发。我把这次排查过程沉淀成一份清单分享出来每次做SQL优化时对照着过一遍拿到慢SQL先看EXPLAIN确认type、key、rows、Extra四个核心字段检查WHERE条件里是否对索引列做了隐式转换、函数运算、隐式字符集转换这些会让索引悄悄失效联合索引字段顺序是否与查询的等值、排序、范围条件匹配注意最左前缀和范围字段截断排序字段有没有进索引如果出现Using filesort考虑调整索引顺序覆盖排序是否能够通过加索引尾部字段实现覆盖索引避免回表优化器是否因为统计信息过期选了错误执行计划必要时ANALYZE TABLE不要止步于单条SQL要看相似模式是否在其它业务出现统一改。这套方法跑下来绝大多数慢SQL都能在索引和SQL层面解决掉。真正需要分库分表的场景反而比想象中少得多。如果哪天真到了那一步前面说的分片键设计和连带问题希望你不需要踩一遍我踩过的坑。
MySQL慢SQL排查与优化：从执行计划到索引设计的全流程实践
1. 慢SQL排查链路从发现到定位的完整方法接手任何一个MySQL优化任务首先要想清楚一件事优化不是凭感觉猜而是要有数据支撑。我见过太多人上来就改索引、调参数结果改了半天慢查询还在那里原因就是没找到真正的瓶颈。这一节先讲清楚排查链路后面所有优化动作都是建立在这个基础上的。1.1 慢查询日志最廉价也最有效的入口MySQL自身的慢查询日志是第一手材料不需要额外装任何工具就能开启。我建议在测试环境或者低峰期的生产环境做如下配置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 100这里几个参数要解释一下。long_query_time设置成1秒是个相对务实的值——如果业务比较简单0.5秒也可以但不要一上来就设0.1秒否则日志量会爆炸反而淹没了真正的问题。log_queries_not_using_indexes用于记录那些没走索引的查询配合min_examined_row_limit可以过滤掉扫描行数很少的查询避免日志被小查询刷屏。日志开启后用mysqldumpslow工具做聚合分析是基本功mysqldumpslow -s at -t 20 /var/log/mysql/slow.log-s at表示按平均查询时间排序-t 20取前20条。这个命令能快速告诉你哪些SQL是真正的常客——注意优化价值最高的是那些执行次数多且单次耗时不低的SQL而不是单次极慢但很少执行的SQL。前者优化一次整个系统都受益。1.2 用EXPLAIN读懂执行计划别只看type和rows拿到慢SQL之后第一件事就是用EXPLAIN看执行计划。网上很多文章教人只看type字段什么ALL全表扫描不行、ref可以、const最好这个说法没错但远远不够。我习惯按这个顺序逐列检查字段关注点常见问题select_type是否出现DEPENDENT SUBQUERY相关子查询通常是性能杀手table关联顺序驱动表选择是否合理type访问类型出现ALL或index需要警惕possible_keys可能用到的索引为空说明没有可用索引key实际用到的索引与possible_keys差距大说明优化器没选好key_len索引使用长度值偏小说明索引利用不充分尤其是联合索引ref等值匹配的列const最优多列关联要留意顺序rows预估扫描行数与实际值偏差过大说明统计信息不准ExtraUsing filesort / Using temporary / Using index三类信息分别对应排序、临时表、覆盖索引举个实际排查过的例子。有个订单查询慢EXPLAIN显示typeALL、rows380万但possible_keys里明明有idx_user_id。为什么优化器不用看了一眼WHERE条件WHERE user_id 0 12345——对索引列做了隐式运算索引直接失效。这就是为什么只看type不够必须结合WHERE条件和key_len一起分析。1.3 定位索引失效的几类典型场景这里顺便把索引失效的主要场景列一下都是实战中非常高频的坑类型隐式转换WHERE phone 13800138000如果phone是VARCHARMySQL会尝试把字符串转成数字再比较导致索引失效。解决办法是让类型严格一致或者主动加引号。对索引列使用函数或运算WHERE DATE(create_time) 2025-01-01这种写法直接让create_time上的索引失效。改写思路是create_time 2025-01-01 00:00:00 AND create_time 2025-01-02 00:00:00。LIKE前置通配符LIKE %keyword%无法走索引。可以考虑用全文索引或者把模糊匹配拆成前缀匹配加后续筛选。OR条件中包含非索引列WHERE id 1 OR status pending如果status上没有索引优化器可能放弃id的索引。改写为UNION ALL或者给status也加上索引。联合索引未遵守最左前缀在(user_id, status, create_time)联合索引上直接WHERE status pending没法走索引。隐式字符集不统一JOIN两表的关联字段一个utf8mb4一个utf8mb4_general_ci或乱序比较会产生隐式转换索引失效。这一点在联表查询里很隐蔽。NOT IN / NOT EXISTS / !通常会导致索引失效需要结合业务改写为LEFT JOIN ... IS NULL或用EXISTS替代。2. 联合索引与优化器取舍为什么有时候索引加了也不走索引基础知识网上一抓一大把我想重点讲的是联合索引的字段顺序设计和优化器为什么不走索引。这两个问题恰恰是区分新手和老手的地方。2.1 联合索引字段顺序区分度与查询模式的平衡联合索引(a, b, c)相当于建了(a)、(a,b)、(a,b,c)三个索引这是最左前缀原则。但实际设计字段顺序的时候很多人的第一反应是哪个区分度高就放前面这个原则不能机械套用。举个例子。订单表有一个联合索引要覆盖常见的查询WHERE user_id ? AND status ? ORDER BY create_time DESC。user_id区分度当然比status高但如果把user_id放第一位、status放第二位、create_time放第三位那最终排序还得走filesort——因为status只用了等值匹配create_time在这个查询里是前导列等值之后的排序字段这是可以利用索引有序性的。**设计联合索引的第一步不是想字段选择性而是列出这个索引要服务的所有核心查询找出它们的公共等值条件。**公共等值字段放最前面紧接着是排序字段最后才是范围查询字段。范围字段放中间会截断后续字段的有序利用。尽量把范围条件放到联合索引的末尾。假设核心查询是WHERE user_id ? AND status ? ORDER BY create_time DESC那么设计(user_id, status, create_time)就是合理的排序直接走索引不需要临时文件排序。反过来如果一个查询是WHERE user_id ? AND create_time BETWEEN ? AND ?那(user_id, create_time)就是更好的选择因为你没法同时让create_time既做范围过滤又维持后续字段有序。2.2 优化器选错索引怎么处理才不硬碰硬有一种很尴尬的场景你建好了索引EXPLAIN一看优化器偏偏选了另一个区分度很低的索引。常见原因是统计信息不准也可能是优化器基于成本模型判断失误。这时候不要急着FORCE INDEX因为一旦数据分布变化FORCE INDEX会成为负优化。先试试ANALYZE TABLE更新统计信息这个操作成本很低有时候跑了立刻就好了。如果还不行可以考虑改写SQL让优化器走预期路径比如转换写法、调整JOIN顺序。实在不行也可以用FORCE INDEX但要加注释说明原因并定期手动检查——这是把双刃剑。另外要提一嘴MySQL 8.0引入了不可见索引INVISIBLE INDEX可以先把索引设置为不可见观察一段时间确认没有业务依赖后再删除比直接DROP安全得多。做索引下线的同学建议用这个特性。2.3 索引下推与覆盖索引两个容易被忽略的关键特性索引下推Index Condition PushdownICP在网络热词里经常被问到。简单说在没有ICP之前存储引擎通过索引拿到数据行之后要回表把整行数据交给Server层再由Server层过滤其它条件。有了ICP之后能在索引层直接过滤的条件会在存储引擎层面先做掉减少回表次数和Server层处理的数据量。这对联合索引尤其有用——联合索引中某个字段没法走索引过滤但可以通过ICP在索引扫描时提前过滤掉一部分行。覆盖索引的价值就更直接了。如果查询的字段全部在索引节点里那就不需要回表。在设计索引时可以考虑把高频查询需要的字段塞进联合索引的尾部让它变成覆盖索引。但要权衡好索引字段越多写入性能和存储成本越高不要为了偶尔一次查询把所有字段都塞进索引。3. SQL改写与慢查询治理从执行计划到真实收益索引加好了执行计划也对了但很多情况下SQL本身的写法还在拖后腿。这一节专门讲SQL改写。它不像加索引那么直观但往往是收益最明显的一环。3.1 子查询与JOIN的取舍MySQL 5.6之前IN (SELECT ...)的优化做得不好很多时候会把外层表逐行代入子查询性能很差。5.6之后优化器做了改进但依然不能说所有子查询都安全。我的经验是能用JOIN表达的关联逻辑优先用JOIN。看一个具体场景。统计近30天有下单的VIP用户-- 不推荐 SELECT id, name FROM users WHERE vip 1 AND id IN ( SELECT user_id FROM orders WHERE create_time 2025-01-01 );改为SELECT DISTINCT u.id, u.name FROM users u INNER JOIN orders o ON o.user_id u.id WHERE u.vip 1 AND o.create_time 2025-01-01;改成JOIN之后执行计划更清晰优化器能更好地选择驱动表和join buffer策略。但也要注意JOIN如果搞出超大中间结果集也会出问题。关键是看EXPLAIN里的rows和实际耗时而不是凭感觉选写法。3.2 大偏移量分页优化LIMIT 100000, 20为何这么慢很多人在分页上吃过亏。LIMIT 100000, 20慢不是因为要取20条而是因为要先扫描并丢弃前100000条记录再取20条。越往后的页扫描成本越高。常见的优化方案有两种。一种是用覆盖索引先定位主键再回表取数据SELECT * FROM orders INNER JOIN ( SELECT id FROM orders ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON tmp.id orders.id;这里内层查询走了(create_time, id)的覆盖索引只扫描索引不碰数据行获取20个主键后再回表代价小很多。另一种是基于排序字段的游标分页keyset pagination适合评论区、消息列表这类场景SELECT * FROM orders WHERE create_time 上一页最后一条记录的create_time ORDER BY create_time DESC LIMIT 20;当然游标分页的前提是排序字段足够唯一否则会出现漏数据。需要在排序字段上加一个唯一性兜底比如ORDER BY create_time DESC, id DESC同时WHERE条件也要带上(create_time, id)的联合游标条件。3.3 深挖慢日志中的高频模式打散大事务与批量写日志看多了会发现慢SQL很多时候不是单条语句的问题而是大事务或者批量任务把资源占住了导致普通查询排队。典型场景是凌晨的批量对账任务一个事务里更新几十万条记录持有行锁不释放白天的查询全部被堵住。对这种任务核心思路是拆批把50万条数据的更新拆成每批1000条分批提交。批与批之间加一点延迟给其它会话让路。这不是SQL层面的花活而是工程层面的取舍。很多慢查询治理到后面拼的不是写SQL的技巧而是对业务场景和锁竞争的理解。另外SELECT里用*的问题也要提。不需要的列尽量别查不是为了省那点带宽而是尽可能让查询命中覆盖索引。SELECT *大概率会让覆盖索引失效哪怕只是多取一列不需要的TEXT字段都可能让原本已经在索引里完成的操作被迫回表。4. 分库分表的启动信号与中间件选型到底什么时候该上前面讲的索引和SQL优化都是单库单表还能扛得住的范围。一旦数据量和QPS上到一个量级单库单表再优化也有物理上限。这一节聊分库分表。4.1 什么时候才应该考虑分库分表先说结论分库分表是系统复杂度剧增的起点能不上就不上。我见过太多业务数据量只有几千万就兴师动众搞分表结果把简单查询硬生生变成了路由计算和数据聚合现场。分库分表的启动信号是什么我个人的判断标准单表数据量超过2000万~5000万跟行宽和索引大小有关且常规索引优化、归档、冷热分离都已经做过一遍写入QPS持续高涨单库写入成为瓶颈Buffer Pool命中率下降主从复制延迟成为常态化问题核心查询即使命中了索引单次响应时间也在恶化因为索引体积变大BTree层数增加随机IO成本上升。如果只是数据量大但读多写少优先考虑读写分离 归档 冷热数据分离 缓存这些方案的成本比分库分表低一个量级。分库分表应该是被业务逼到墙角之后的最后手段而不是未雨绸缪的第一选择。4.2 中间件选型ShardingSphere vs MyCat以及其他选项这几年分库分表的中间件格局变化不小。主流选择就三个方向应用层ShardingSphere-JDBC、代理层ShardingSphere-Proxy、代理层MyCat。简单对比一下维度ShardingSphere-JDBCShardingSphere-ProxyMyCat部署方式应用内集成jar包独立服务应用无感知独立服务应用无感知性能损耗低SQL解析在应用侧中多一次网络交互中高历史版本有较多瓶颈功能生态分片、读写分离、数据加密、分布式事务同左还支持SQL网关分片、读写分离较强依赖XML配置维护成本需要应用发版DBA维护独立中间件集群DBA维护适合场景新项目或可以发版改造的存量项目多语言团队、希望集中管控历史系统逐步改造我的倾向是新项目可以直接用ShardingSphere-JDBC因为它在应用内做路由没有额外网络跳转性能更好而且分片规则由应用侧管理开发可控。多团队、多语言异构的场景才考虑Proxy模式让DBA统一管理规则应用方只对接一个标准MySQL协议端口。用中间件之前先用一个标准问题自测这个系统里是否所有SQL都能明确回答它要访问哪张分片如果存在大量非分片键查询比如运营后台各种按名称、按状态模糊查分库分表之后要么全路由扫全片要么引入ES这种二级索引体系复杂度直接翻倍。4.3 分片键选错之后一次真实的教训选分片键是分库分表最关键、也最难改的决定。分享一个教训。之前做过一个订单系统分片当时拍板用order_id做分片键理由是订单ID主键分布均匀范围也好。结果上线半年后运营高频需求是查某个用户的全部订单而user_id不是分片键这类查询只能对所有分片做广播路由每来一次就压垮一次数据库。正确做法是在设计分片键时把未来一年最高频的查询场景枚举出来优先保证这些查询能通过分片键直接定位到分片。订单类系统通常用user_id做分片键更合理因为查某用户的订单是最高频天然维度。如果还需要查某订单详情则可以通过订单号反查分片在订单号里带上用户ID的冗余或者维护一个映射表。分片键的设计不是纯技术问题是业务模型问题。中间件只是执行你定的路由规则定错了规则后续想改意味着所有存量数据要重分布成本巨大。4.4 分库分表之后四个躲不开的连带问题分库分表只解决了数据和写入的天花板但会带来一系列新问题。列四个最常见的全局主键单库自增ID彻底失效。常见方案是雪花算法Snowflake但这依赖机器时钟要注意时钟回拨问题也可以做号段模式从独立发号器批量取号段性能可控。跨分片分页与排序ORDER BY create_time LIMIT 10在10个分片上就变成每个分片各取10条再在应用层合并排序深度分页时每个分片都要把offset之前的数据查出来放大效应明显。应对思路是禁止深分页或者用ES承担这层搜索聚合职责。分布式事务原来一个事务就能完成的多表明细更新分片后可能横跨多个数据库。方案从全局XA到TCC到消息最终一致性都有没有银弹。能用最终一致性解决的问题不要上强一致。数据迁移与扩容分片数定死未来扩容又要重分布数据。常见方案是分片数按2的倍数扩容配合基于hash取模范围映射的平滑迁移工具。分组法分成N个逻辑库组每组若干物理分片能缓解但解决不了根本问题。5. 优化器统计信息与参数调优容易被忽略的收尾工作索引和SQL是MySQL优化的明面功夫但经常有个情况——SQL写得没问题索引也在可生产环境就是慢。这时候要看看优化器统计信息和MySQL自身的参数配置。5.1 统计信息不准引发的幽灵慢查询MySQL优化器决定是否用索引依赖SHOW INDEX里的Cardinality等统计信息。如果表的增删改频繁而统计信息没及时更新优化器会基于过期数据做判断选出错误执行计划。一个经典场景某个状态字段status只有2个值分布相对均匀各50%你建了索引优化器也不太会用因为它觉得全表扫描一半数据走索引也没意义。但是某天业务出现倾斜某个状态值占比突然只有1%索引开始有价值了可统计信息还没跟上于是明明可以秒回的查询跑了全表扫描。解决办法是评估是否需要调整innodb_stats_on_metadata以及innodb_stats_persistent的采样设置。批量更新数据之后及时跑一次ANALYZE TABLE。8.0里可以对大表做ANALYZE TABLE ... WITH 64 BUFFERED ROWS之类的采样控制灵活一些。5.2 InnoDB关键参数不那么玄幻但确实有影响很多人喜欢背一堆Buffer Pool参数但真正跟线上优化强相关的我建议先关注这几个参数作用建议innodb_buffer_pool_sizeInnoDB缓冲池大小设为物理内存的60%~75%不要贪多留给OS Cache和连接数innodb_change_buffer_size变更缓冲默认25%如果写多读少可适当提高反之调低innodb_log_file_sizeRedo日志大小日志偏小会导致频繁刷盘建议1GB~4GB起步long_query_time慢查询阈值线上建议1s核心库可以0.5smax_connections最大连接数别盲目调大连接多不一定好配合thread_pool或连接池控制innodb_buffer_pool_instances缓冲池实例数大内存机器建议设置为多实例减少缓存页竞争参数调优的原则是一次只动一个参数观察效果再动下一个。切忌从网上找一份最牛配置直接照抄机器内存、磁盘类型、业务模型都不一样照抄容易出事。5.3 定期巡检把优化做在事故发生之前最后分享一个日常巡检的清单。我每个月会在核心库上做一轮慢查询日志趋势是否上升新增慢SQL是否有人认领和跟进performance_schema或者sys库里的语句延迟Top N是否有明显变化索引使用率sys.schema_unused_indexes查一查长期没用的索引择机下线表碎片情况OPTIMIZE TABLE不要频繁跑但要关注是否有大量UPDATE/DELETE导致表膨胀SHOW GLOBAL STATUS里的关键指标Innodb_row_lock_current_waits、Threads_running、QPS变化趋势。这种巡检不复杂但贵在坚持。优化工作真正的护城河不是哪一次灵光一现把某个慢SQL从10秒优化到10毫秒而是建立一套可持续发现、整改、回归的机制。6. 一条慢SQL的完整排查复盘与最终落地效果用一条真实案例收尾。前一阵一个库存扣减接口出现频繁超时trace_id显示一段时间内P99延迟从80ms涨到1.2s。我按前面说的链路排查了一遍。6.1 从慢日志到执行计划一步步锁死问题第一步慢日志里捞到了核心SQLSELECT * FROM inventory WHERE sku_id SKU1024 AND warehouse_id 5 AND status available ORDER BY create_time DESC LIMIT 10;先看执行计划结果是typeALLrows850万但表里明明有(sku_id, warehouse_id, status)的联合索引。这看起来不该全表扫描。再看表结构发现sku_id是VARCHAR(64)但SQL里传入的是字符串不存在数字隐式转换。继续查发现联合索引的字段顺序是(status, warehouse_id, sku_id)而查询以sku_id和warehouse_id为等值条件status只是过滤条件之一——最左前缀失效了。这里还有个细节ORDER BY create_time DESC也需要看能不能走索引。现有联合索引无论如何都没法覆盖排序需求所以Extra字段大概率是Using filesort。6.2 索引重建与SQL微调的组合拳我的调整方案分两步第一步重新设计联合索引改成(sku_id, warehouse_id, status, create_time)。sku_id和warehouse_id是最高频等值条件放前面status继续做过滤create_time放在最后利用BTree有序性消除filesort。注意statusavailable在业务上是绝大多数库存的常态区分度极低但它不会阻断后续create_time的排序用途放在第三个位置是为了过滤语义同时不影响整体有效性。第二步SQL层面把SELECT *改成SELECT sku_id, warehouse_id, status, quantity, update_time这些字段都在索引或主键的覆盖范围内吗quantity如果不在索引里就还需要回表。为了极致的覆盖索引效果可以把quantity也加进索引尾部(sku_id, warehouse_id, status, create_time, quantity)这样整个查询都在索引里完成零回表。当然这个方案要看写入频率是否可接受额外索引存储成本库存表写多读多但索引尺寸增加对写入的压力在可控范围内。改造后执行计划typeref、key_len大幅增加、rows降到个位数、ExtraUsing index。线上实测P99从1.2s回落到70ms左右效果非常明显。6.3 复盘后沉淀的可复用检查清单这个案例本身不复杂但能代表一类问题索引设计必须从实际SQL出发而不能从字段语义出发。我把这次排查过程沉淀成一份清单分享出来每次做SQL优化时对照着过一遍拿到慢SQL先看EXPLAIN确认type、key、rows、Extra四个核心字段检查WHERE条件里是否对索引列做了隐式转换、函数运算、隐式字符集转换这些会让索引悄悄失效联合索引字段顺序是否与查询的等值、排序、范围条件匹配注意最左前缀和范围字段截断排序字段有没有进索引如果出现Using filesort考虑调整索引顺序覆盖排序是否能够通过加索引尾部字段实现覆盖索引避免回表优化器是否因为统计信息过期选了错误执行计划必要时ANALYZE TABLE不要止步于单条SQL要看相似模式是否在其它业务出现统一改。这套方法跑下来绝大多数慢SQL都能在索引和SQL层面解决掉。真正需要分库分表的场景反而比想象中少得多。如果哪天真到了那一步前面说的分片键设计和连带问题希望你不需要踩一遍我踩过的坑。