1. 先从一次看起来没什么问题的慢查询说起前阵子帮某团队排查一个线上问题一个列表查询接口数据量不过几十万行索引也都建了但每到下午高峰期接口响应时间就从 200ms 飙到 3 秒以上。数据库 CPU 不算高连接数也没爆就是慢。查了半天最后定位到了一条 SQL——执行计划里明明走了索引但 rows 列显示扫描了十几万行Extra 里写着 Using filesort。这个案例特别典型也是我写这篇慢SQL治理的直接动机。很多人对慢SQL治理的理解停留在建个索引就完事但实际上慢SQL治理是一条完整的链路从慢日志采集分析用什么工具看、看哪些字段到单条SQL的索引优化为什么走了索引还慢、索引失效的坑再到表结构层面的分片键设计单表扛不住时怎么拆分、分片键定错了有多痛。这三层缺一不可而且顺序不能乱先能发现才能优化优化到单表实在顶不住了才轮到分片。这篇文章我把这三块内容串起来讲结合我实际排查过的问题和踩过的坑。适合谁看后端开发、DBA、以及所有需要和数据库性能问题打交道的同学。如果你的系统正在被慢查询困扰或者你想提前建立一套治理思路而不是每次都临时救火这篇文章应该能给你一条可落地的路线。2. 慢日志分析先搞清楚你的数据库到底在慢什么2.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。默认值是 10 秒这个阈值在生产环境基本等于形同虚设——真正让你难受的往往是那些执行时间 1 秒到 5 秒的查询它们单看不算离谱但架不住频繁调用累积起来就把数据库拖垮了。我自己一般建议从 1 秒开始设如果慢日志量太大再逐步调到 2 秒而不是反过来从大往小调。log_queries_not_using_indexes这个参数容易被忽略。它会把所有没走索引的查询都记下来哪怕执行时间只有几毫秒。这在排查潜在慢查询时特别有用——很多全表扫描的查询在数据量小的时候毫秒级完成等数据涨上来了才爆发提前抓到它们就能避免后续的突发事故。还有一个参数值得注意min_examined_row_limit。它设置的是扫描行数下限只有扫描行数超过这个值的查询才会被记录。这个参数和log_queries_not_using_indexes配合使用效果很好可以过滤掉那些没走索引但只扫了十几行的鸡肋记录让慢日志里的内容更聚焦。2.2 mysqldumpslow 的正确打开方式慢日志是纯文本文件数据量大了之后肉眼根本看不过来。mysqldumpslow是MySQL自带的日志分析工具功能不算花哨但胜在简单直接。最常用的几种用法# 按平均查询时间排序取前10条 mysqldumpslow -s at -t 10 /var/log/mysql/slow.log # 按执行次数排序取前10条 mysqldumpslow -s c -t 10 /var/log/mysql/slow.log # 按总执行时间排序执行次数*平均时间 mysqldumpslow -s t -t 20 /var/log/mysql/slow.log # 只看涉及某张表的慢查询 mysqldumpslow -g order_info /var/log/mysql/slow.log这里有个关键的细节mysqldumpslow默认会把 SQL 中的数字抽象成N把字符串抽象成S所以同一条 SQL 只要参数不同会被归并成一条记录统计。比如SELECT * FROM order_info WHERE user_id 12345; SELECT * FROM order_info WHERE user_id 67890;会被归并成SELECT * FROM order_info WHERE user_id N;这个设计在绝大多数场景下是合理的但有一个例外如果你需要针对某个具体参数值定位问题比如某个大客户的 user_id 特别大导致查询特别慢就需要用-a参数禁用数字抽象mysqldumpslow -a -s t -t 10 /var/log/mysql/slow.log我实际用下来的心得是先按执行次数排序把调用最频繁的慢查询捞出来优先优化性价比最高。因为优化一条每秒执行 100 次的慢查询比优化一条每天只执行 1 次的重查询带来的收益大得多。2.3 慢日志里最值得关注的三列看 mysqldumpslow 的输出时大部分人的注意力都在 Query_time 上但有两列信息更值得深挖第一是 Rows_examined扫描行数和 Rows_sent返回行数的比值。这个比值是判断索引质量的核心指标。如果扫描了 10 万行只返回 10 行说明索引选择性和过滤性都很差SQL 虽然走了索引但走的是个低质量的索引。这时候优化的方向不是加索引而是改索引结构或调整 SQL 写法。第二是锁等待时间。慢日志里 Lock_time 如果持续偏高说明问题可能不在 SQL 本身而在并发竞争上。常见场景是一个事务长时间持有行锁不释放其他事务的更新操作排队等待。这种情况你单纯优化 SQL 是没用的得去看事务隔离级别、事务里的操作顺序、以及是否有大事务。有一个容易被忽略的点慢日志记录的时间不包含锁等待时间只包含执行时间。所以如果你发现一条 SQL 的 Query_time 很小但业务上感觉很慢那大概率是卡在了锁等待或网络传输上这时慢日志帮不了你需要换工具排查。2.4 快速从慢日志到优化清单的实操套路拿到慢日志之后别急着逐条分析。我一般会走这么几步第一步用 mysqldumpslow 按次数排序圈定 Top 20 的 SQL。这些是影响面最大的。第二步把这几条 SQL 逐条用 EXPLAIN 分析执行计划记录 type、key、rows、Extra 四列。重点关注 type 为 ALL全表扫描和 Extra 里出现 Using filesort、Using temporary 的。第三步把 SQL 按优化难度做个分级。我的分级标准很简单级别判断标准处理策略P0全表扫描 高频调用当天加索引或改 SQLP1走了索引但扫描行数大改索引结构或重写 SQLP2低频但超时严重先加超时保护再深究P3偶发影响不大记录观察暂不处理注意慢日志治理最忌讳遇到一条优化一条没有优先级排序的优化很容易让你在低价值的 SQL 上耗费大量时间。3. 索引优化走对路只是及格走好路才是目标3.1 执行计划里被忽视的索引质量信号很多人看到 EXPLAIN 结果里 key 字段有值就觉得索引生效了。其实这是最大的误区。走了索引和索引用得好是两回事。看一个实际的执行计划id | select_type | table | type | key | rows | Extra 1 | SIMPLE | order_info | ref | idx_user_id | 2453 | Using index conditiontype 是 refkey 是 idx_user_id看起来没毛病。但如果这条 SQL 的 WHERE 条件是user_id ? AND status ?而 idx_user_id 只包含 user_id 一个字段那么 status 的过滤就得靠回表之后在服务层完成。数据量小的时候没问题当某个 user_id 下有几千条订单时这个查询就要回表几千次。这时候正确的优化方案不是新加索引而是把索引改成联合索引ALTER TABLE order_info DROP INDEX idx_user_id; ALTER TABLE order_info ADD INDEX idx_user_status (user_id, status);这两个操作在线上执行时要特别注意加索引和删索引都会造成表级锁或元数据锁高并发期间执行容易引发阻塞。我一般会先在备库执行确认执行计划正确后再切到主库并使用pt-online-schema-change这类工具来减少锁的影响。3.2 三个最常见的索引失效场景再复习三个高频踩坑的索引失效场景每个都是我实际遇到过的场景一对索引字段做函数运算。比如WHERE DATE(create_time) 2024-01-01。索引里存的是create_time的原始值你拿DATE()函数处理完的结果去匹配索引自然用不上。正确写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02把查询条件改造成范围匹配索引才能生效。场景二隐式类型转换。最常见的是手机号、订单号这类字段。如果表里定义的是varchar但查询时传入了数字类型的参数MySQL 会对你传入的参数做类型转换导致索引失效。比如-- order_no 是 varchar 类型 SELECT * FROM order_info WHERE order_no 20240101123456;这个查询的索引会失效。正确做法是传字符串WHERE order_no 20240101123456。排查这类问题有个小技巧用EXPLAIN看执行计划时如果 type 从 ref 掉到 ALL同时看到CAST或隐式转换的告警基本就是这个问题。场景三联合索引不满足最左前缀。联合索引(a, b, c)可以支持a、a,b、a,b,c三种查询条件但不支持b单独查询或b,c组合查询。我在设计联合索引时有个习惯第一个字段选择查询频率最高、区分度最好的字段而不是按照表结构里字段的排列顺序来建。3.3 覆盖索引和索引下推两条被低估的性能通道除了基础的索引匹配还有两个机制经常被忽略但实际效果非常明显。覆盖索引的含义是查询需要的所有列都包含在索引里InnoDB 就不需要回表去聚簇索引里取数据了。比如SELECT user_id, status FROM order_info WHERE user_id 123;如果联合索引是(user_id, status)那么这条查询的所有数据都从索引里拿到了Extra 会显示Using index不需要回表。这个优化对于高频小查询效果极好能把一次查询的 IO 次数从N 次回表降为1 次索引扫描。索引下推Index Condition PushdownICP是 MySQL 5.6 引入的优化原理是把 WHERE 条件中的部分过滤条件下推到存储引擎层在读取索引时就完成过滤减少回表次数。看一个例子-- 联合索引 (user_id, status) SELECT * FROM order_info WHERE user_id 123 AND status 1;没有 ICP 时InnoDB 会读取所有 user_id123 的索引记录然后逐条回表再在服务层判断 status。有了 ICPstatus1 的过滤在索引扫描阶段就完成了只有满足条件的记录才回表。这个优化在联合索引的非第一个字段上有过滤条件时特别明显。提示ICP 默认是开启的但如果你在 WHERE 条件里对字段使用了函数或表达式ICP 无法生效这也是前面说不要对索引字段做函数运算的另一个原因。3.4 索引优化必须配套的数据治理动作有一个点我觉得必须单独拿出来说索引不是加了就一劳永逸的数据本身的分布情况直接决定索引效果。举个例子。某业务表的 create_time 字段建了索引但因为业务原因近 80% 的数据都是最近 7 天产生的。那么查询WHERE create_time 2024-06-01时优化器算一下发现要扫的数据超过了全表的 30%它就会放弃索引选择全表扫描——因为全表扫描的顺序 IO 反而比索引带来的大量随机回表更高效。这种情况加索引不管用需要的是数据归档或分区。比如把历史数据按月归档到冷表或者用 MySQL 的 RANGE 分区按时间拆分让每个分区的数据量可控索引才能重新生效。还有一个经常被忽略的动作定期清理冗余索引。MySQL 不会主动告诉你哪些索引没用但performance_schema里的table_io_waits_summary_by_index_usage表记录了每个索引的使用次数。如果某个索引长时间没有被使用就可以考虑删掉——它不仅在写入时要维护还占磁盘空间更可能干扰优化器的选择。4. 分片键设计单表极限之下的必然选择4.1 什么时候才真的需要分片索引优化做到位之后单表能扛的数据量其实相当可观。MySQL 单表在几百万到一两千万行时只要索引设计合理读写性能都能保持不错。所以我不建议过早分片——分片带来的复杂度是实打实的跨片查询、聚合统计、事务一致性、数据迁移每一个都是大工程。那什么时候才需要考虑分片我的判断标准有三个同时满足两个以上才动手指标阈值参考说明数据量单表超过 2000 万行且持续增长数据量本身不是绝对标准结合业务增速写入 QPS单库写入超过 5000 TPS写入成为瓶颈索引优化已无法分担单表查询 RTP99 超过 500ms且通过索引优化无法改善索引效果已经到极限需要从架构层面解决这里特别想强调一点不要在数据量还没到瓶颈时就提前分片。我见过一个反面案例某团队因为订单表到了 800 万行就开始分片结果分片键选得不好跨片查询一大堆性能反而比不分片时还差。分片是最后的手段不是预防性的手段。4.2 分片键选型的核心原则跟着最高频查询路径走分片键的选择是整个分片设计里最重要、也最不可逆的决策。改分片键比加索引困难得多因为数据已经物理上散到多个节点了重新分片等于全量数据重分布。我总结的核心原则只有一条分片键必须匹配你最高频、最核心的查询访问路径。选分片键之前先做一件事把系统的 SQL 按访问频率拉一个列表找出那些每次请求都必然执行的查询看它们的 WHERE 条件里最常出现哪个字段。举两个典型场景场景一订单表。如果 90% 的查询都是WHERE user_id ?查某用户的订单列表那 user_id 就是分片键。这样同一个用户的所有订单都落在同一个分片上单条查询完全不需要跨片。反过来如果选了 order_id 做分片键那么查用户订单列表时就必须把请求广播到所有分片再合并结果——这是分片设计的大忌。场景二流水表。如果系统经常按时间范围做统计查询比如最近 30 天每天的订单量那么时间字段更适合做分片键范围分片。但如果同时存在大量按用户查询的请求这就产生了设计矛盾需要做取舍或增加冗余。分片键设置好之后最好保证它永不更新。分片键一旦更新数据就要从一个分片搬到另一个分片这个操作在线上极难做到无损。所以选分片键时也要考虑业务上这个字段是否会变化。4.3 两种分片策略的取舍Hash 分片 vs 范围分片确定了分片键之后下一步是选择分片规则。主流方案有两种各自适用不同场景。Hash 分片对分片键取哈希值再对分片数取模得到分片编号。比如shard_id hash(user_id) % 16。这种方式的数据分布最均匀能很好地避免热点问题但跨分片的范围查询能力很弱——因为相同时间段的订单会散落在不同分片。范围分片按分片键的取值区间划分比如按订单创建时间拆成按月分片。这种方式对范围查询特别友好但容易造成数据倾斜——比如大促月的数据量可能是平时月份的几倍导致单分片负载过高。我在实际项目中的做法是混合使用核心的、高频的单点查询场景用 Hash 分片保障性能均衡低频但必须的范围统计查询用一套独立的汇总表来支撑而不是强行走分片查询。这种分片表 汇总表的模式在大多数业务场景下比纯分片方案更实用。注意Hash 分片有一个需要在初期就定死的参数——分片数量。一旦数据写入后修改分片数量需要对所有数据做重新分布Resharding成本极高。所以分片数宁多勿少要给未来至少 2 到 3 年的数据增长留足余量。4.4 一个分片键选错的实际案例高频查询被迫全片扫描最后分享一个具体的反面案例这是我调研时遇到的一个真实架构问题。某团队设计了一套消息推送记录表分片键选的是channel_id渠道 ID理由是不同渠道的数据天然隔离查询时也经常按渠道查。但他们忽略了一个事实系统里最高频的查询其实是 App 端用户的消息列表查询WHERE user_id ?每天执行几百万次。由于分片键是 channel_id 而不是 user_id这个查询根本没法直接定位到分片只能把请求广播到全部分片再在服务层做数据合并。结果是单次查询在单个分片上的耗时只有 30ms但因为有 8 个分片最慢的分片决定了整体耗时加上合并逻辑最终 P99 耗时超过 1 秒。用户体感就是消息列表打开特别慢。这个案例的教训很明确分片键的选型不是看哪些字段有区分度而是看哪些字段出现在最高频的 WHERE 条件里。区分度再高如果不在高频查询路径上也不是好的分片键。4.5 分片之后的查询治理哪些查询类型必须规避分片不是终点分片之后对查询类型的约束才是真正的治理难点。以下三类查询在分片架构下要么被禁止要么需要专门改造第一类非分片键的单点查询。比如分片键是 user_id但业务需要按 order_no 精确查单。解决方案一般是维护一张order_no - user_id的映射表先查到 user_id 再定位分片而不是直接扫全片。第二类无分片键的批量聚合查询。比如按天统计全平台订单量。这类查询如果在分片上执行就是一次全片扫描加结果合并。正确的做法是引入离线或异步的汇总机制把统计结果预计算好查询只读结果表。第三类跨分片事务。分片后原来一个本地事务里能完成的更新订单 更新库存现在可能需要跨两个物理库操作。这必须引入分布式事务方案如基于消息的最终一致性方案事务的复杂度会明显上升。在设计分片方案时要尽量把强一致性要求高的操作约束在单个分片内完成。5. 治理效果的监控与回验别只信一次的执行计划SQL 优化完、分片方案上线这不代表治理就结束了。我比较坚持一个观点所有的优化都必须用数据来验证而且要持续验证。因为线上数据的分布是动态变化的今天好的执行计划下个月数据量涨了之后可能就变了。我常用的回验手段有三个第一慢日志对比。优化上线后持续观察慢日志里同类 SQL 的出现频率和 Query_time 分布。最理想的效果是这条 SQL 从慢日志里消失。如果只是平均耗时下降但依然上榜说明还没根治。第二performance_schema 里的语句分析。查events_statements_summary_by_digest表按SUM_TIMER_WAIT排序看优化前后 Top 语句的总耗时变化。这个指标反映的是总体负载变化比单独看一条 SQL 更全面。SELECT digest_text, count_star, ROUND(SUM_TIMER_WAIT/1000000000, 2) AS total_sec, ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;第三业务指标监控。接口响应时间的 P99、P95 变化数据库 CPU 使用率变化这些是最终的验证指标。一条 SQL 优化得再好如果业务指标没变化说明它本来就不是瓶颈反之业务指标明显改善才是治理有效的最终证明。我在实际排查中发现很多团队做完索引优化后只看了 EXPLAIN 觉得没问题了但上线后夜间批量任务依然卡死。后来定位到问题是EXPLAIN 的结果基于当前的统计信息但 InnoDB 的统计信息是采样估算的数据分布变化后执行计划可能完全不同。所以我习惯在优化后主动执行一次ANALYZE TABLE刷新统计信息再重新看执行计划。另外慢日志治理最好形成周期性机制。我建议的频率是每两周拉一次慢日志做对比分析重点检查是否有新增的慢查询类型。数据库的问题像杂草不持续清理很快就又长满了。与其每次等线上报警了才去翻慢日志不如建立一个定期巡检 快速响应的闭环流程让慢SQL治理从一开始就是系统性的工程而不是一次性的运动。