先讲一件上周的事。凌晨两点半我被告警叫醒线上一个订单详情接口的P99延迟从80ms直接飙到2.3秒。打开慢查询日志罪魁祸首是一条看着特别老实的SQL单表查询、条件清晰、索引也建了但执行计划里赫然写着全表扫描优化器预估扫描行数差了三个数量级。这个场景对做MySQL性能调优的人来说应该不陌生——索引明明就在那里优化器却像一个心态失衡的赌徒把筹码全压在了它感觉最对的路上结果把慢查询这口锅扣在了所有DBA和开发头上。这类问题的本质不是索引没建而是优化器选择的执行计划与真实数据分布严重背离。MySQL优化器会根据表的统计信息、索引基数、代价模型去估算一个成本最低的执行路径但统计信息过期、字段数据分布倾斜、多表关联顺序判断失误都会让它的估算变成乱猜。这篇文章不打算讲教科书式的索引原理而是把这次慢查询重构的完整过程拆开问题怎么定位、优化器为什么判断失误、SQL怎么改写、索引怎么重建、后续怎么防止复发全部可以照着操作。1. 现场还原一条SQL从毫秒到秒级的完整排查路径1.1 业务背景和表结构出问题的是订单查询接口对应一张订单主表和一张订单扩展表。订单主表大概1200万行扩展表400万行两表通过order_id关联。接口逻辑很简单根据用户ID和时间范围查最近的订单列表再联扩展表取几条附加属性。简化后的核心SQL长这样SELECT o.id, o.order_no, o.user_id, o.amount, e.extra_info FROM orders o LEFT JOIN order_ext e ON o.id e.order_id WHERE o.user_id 123456 AND o.create_time 2024-03-01 00:00:00 AND o.create_time 2024-03-02 00:00:00 ORDER BY o.id DESC LIMIT 20;表上的索引原本有KEY idx_user_time (user_id, create_time), KEY idx_create_time (create_time), PRIMARY KEY (id)按照常规认知这SQL有用户条件、有时间范围、有排序、有limitidx_user_time(user_id, create_time)这个复合索引无论从过滤还是排序角度看都算合适。可实际执行计划显示优化器直接忽略了idx_user_time走了全表扫描过滤完还要filesort最后取出20条。这不是索引设计的问题是优化器在选路时赌错了。1.2 慢查询日志与EXPLAIN解析排查的第一步永远是确认到底哪条SQL慢而不是靠猜。我这里开启了慢查询日志设置了long_query_time1把超过1秒的语句记录下来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;log_queries_not_using_indexes这个参数要重点提一下它会额外记录那些虽然没超过阈值、但没走索引的查询。很多慢查询不是一开始就慢是从没走索引但数据量少慢慢演变成没走索引且数据量爆炸的这个开关能帮你提前发现问题。抓到慢SQL之后用EXPLAIN看执行计划EXPLAIN SELECT ...同上...;关键输出如下字段值解读tableorders驱动表是订单主表typeALL全表扫描问题出现的地方keyNULL实际没用到任何索引rows11800000预估扫描接近全表filtered0.02过滤后预计只剩0.02%ExtraUsing where; Using filesort先过滤再排序性能杀手ALL加filesort这是能让任何DBA皱眉头的组合。更讽刺的是filtered0.02说明优化器自己也知道这个条件下能命中的数据极少但它依然选择了全表扫——因为它的成本模型认为走索引回表的开销比全表扫描还大。这就要说到优化器赌的核心机制了。1.3 用profile确认时间分布EXPLAIN看的是执行计划但时间到底消耗在哪个阶段需要看profile。MySQL 8.0里用performance_schema5.7及以下用SHOW PROFILESHOW PROFILE CPU, BLOCK IO FOR QUERY 123;结果非常直白Sending data阶段占了92%的时间这就是全表扫描回表排序的代价在真实运行中的体现。扫描环节并没有被某个慢操作卡住就是扫描的行实在太多I/O和CPU都被堆满了。到这一步问题已经从SQL慢定位到了优化器选错了索引。接下来更关键的是搞清楚它为什么会选错2. 优化器的赌徒心理统计信息、代价模型与基数估算2.1 优化器闭眼决策的基础统计信息MySQL优化器不是AI它不会真地去扫描一遍数据再决定怎么查。它手里的牌只有三样表的统计信息、索引的基数cardinality、以及一套代价计算模型。它根据这三样东西估算出走哪个计划最便宜然后闭眼执行。问题恰恰在于统计信息不是实时更新的它更像一张可能过期几个月的地图。看索引基数的命令很常用SHOW INDEX FROM orders;其中有几个关键字段字段含义你该关注什么Cardinality索引中唯一值的估算数量和真实值差距过大说明统计信息失真Sub_part前缀索引长度索引的是前几个字符Null是否允许NULLNULL对索引选择有影响Index_typeBTREE / HASH等决定适用场景正常情况下user_id区分度很高Cardinality应该接近实际用户数。但在我这次排查中idx_user_time的Cardinality显示只有3000多而业务实际用户数超过80万。为什么差这么多因为在大量数据被定时清理后SHOW INDEX里的基数没有自动更新优化器看到这个数字会本能地认为走这个索引要扫太多重复值不划算。提示Cardinality是估算值不是精确值。它来自采样统计InnoDB会在索引树中随机抽取若干页来估算。数据变更频繁、大量删除插入后这个估算值可能严重失真。2.2 代价模型是怎么把索引算输的MySQL的优化器用一个相对简单的代价模型总成本 读取页的成本 比较运算成本 回表成本。它不会真的去读所有页只是用统计信息里的行数、页面数、索引分布去套公式。如果统计信息说这个索引有大量重复值套出来的结果就是回表次数多、代价高自然被排到后面。还有一点很关键代价模型对回表的估算非常悲观。假设走idx_user_time需要筛选出1000条记录再回表1000次而全表扫描是1200万次顺序读。优化器在1000次随机I/O和1200万次顺序I/O之间二选一时假如统计信息显示前者要扫的索引页很多它真的可能赌后者更快。在小表上这个判断没错但在千万级表上顺序扫描全表的真实耗时远高于小规模回表这就是估算和现实脱节的地方。MySQL 5.7之后有一个参数optimizer_switch里面包含很多可以调整的子策略。有些团队为了强制优化器更保守会调整index_merge、semijoin等选项。但我不建议普通业务直接动手改全局参数那相当于把所有查询的赌法都改了副作用很难预估。更稳妥的做法是针对具体的慢查询用SQL改写和索引设计去影响它。2.3 数据分布倾斜与直方图的缺席另一个导致赌输的常见原因是数据分布严重不均。比如user_id123456这个用户有10万条订单而绝大多数用户只有几条。优化器只有一个平均值没有分布曲线。它不知道热门用户和普通用户的查询成本天差地别只会按平均值估算。MySQL 8.0引入了直方图histogram来解决这类问题ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id WITH 100 BUCKETS;创建直方图后优化器能更准确地感知数据分布尤其适合那些索引本身没问题但统计信息太粗的场景。这个功能在8.0里表现不错但要注意直方图对已经存在索引的列帮助有限它真正解决的是列上没有合适的索引、优化器完全靠猜的情况。因为我这里的idx_user_time本身就是正确的索引所以直方图不是这次重构的核心手段但它是一个值得记录在案的后备方案。3. 慢查询重构实操从索引重建到SQL改写3.1 第一步先更新统计信息别急着加索引排查到这一步我做的第一件事不是改代码而是先执行一次ANALYZE TABLE orders;这个命令会重新收集表的统计信息更新Cardinality。执行完后再次查看SHOW INDEX FROM ordersidx_user_time的Cardinality从3000多恢复到了接近80万的真实水平。然后重新EXPLAINEXPLAIN SELECT ...;执行计划已经变了优化器终于选择了idx_user_timerows预估从1180万降到了5000多。这是一个立竿见影但很容易被忽视的操作——很多慢查询其实只需要一次ANALYZE TABLE就能神奇恢复背后的原理不是索引变了而是优化器重新睁眼了。注意ANALYZE TABLE在InnoDB里会短暂获取表的元数据锁在高并发写入的业务表上建议放在低峰期执行。MySQL 8.0支持在线DDL但ANALYZE依然可能引起短暂的等待。3.2 第二步索引重构补上排序和覆盖的缺口统计信息虽然恢复但执行计划里依然出现了Using filesort。原因很简单idx_user_time(user_id, create_time)的索引顺序里create_time是升序的而查询要求ORDER BY id DESC排序字段和索引键不一致优化器只能把筛选出来的数据再做一次显式排序。这里有两种思路。第一种思路是把排序字段融入复合索引。如果业务场景固定是按user_id过滤、按id倒序取最近记录那可以考虑让索引在user_id之后带上id即ALTER TABLE orders ADD KEY idx_user_id_id (user_id, id);这样查询可以直接从索引里按id倒序取前20行连排序都省了。这个索引的核心逻辑是过滤后直接按目标顺序输出代价是会多维护一个索引。第二种思路是设计覆盖索引。原SQL里SELECT了o.amount和e.extra_info前者在主表上导致回表后者在扩展表上靠LEFT JOIN解决。如果某些查询只取少量字段可以把字段直接塞进索引让查询完全不用回表ALTER TABLE orders ADD KEY idx_user_id_time_amount (user_id, create_time, amount);但要注意索引不是越多越好。覆盖索引能减少回表但会增加写入开销和存储成本。如果表本身写入频繁要给每个索引的维护成本算笔账。我这次在权衡后保留了idx_user_id_id把查询改成了两条避免为所有查询都建覆盖索引。3.3 第三步SQL改写让优化器一下就看明白有些场景下不管怎么建索引复杂的SQL写法就是会让优化器绕远路。这时候需要对SQL本身做重构。我整理了这次遇到的高频问题分三类讲第一种子查询改JOIN。优化器对子查询的处理往往不如JOIN直观尤其当子查询涉及非相关子查询时它的优化策略经常让人看不懂。像查有扩展信息的订单这种需求用IN子查询写成SELECT * FROM orders WHERE id IN (SELECT order_id FROM order_ext WHERE extra_type 1);优化器在5.7以下版本可能把它转成半连接semi-join但也可能老实巴交地逐行执行。手动改成SELECT o.* FROM orders o JOIN order_ext e ON o.id e.order_id WHERE e.extra_type 1;通常执行计划更可控也更容易利用上驱动表的索引。第二种OR条件拆分。OR两端的条件如果各自有索引优化器有可能会选index_merge但也可能因为估算失误走全表。比如WHERE user_id 123 OR status 2当user_id有索引、status有索引时优化器可能尝试合并两个索引也可能干脆放弃。改成UNION ALLWHERE user_id 123 UNION ALL WHERE status 2 AND user_id 123执行计划会清晰得多。当然原查询如果本身能命中一个复合索引则不需要拆。第三种函数包裹列导致索引失效。最常见的是对索引字段套函数比如DATE(create_time) 2024-03-01。这个写法会让索引字段失去有序性优化器直接不认索引。正确的改写是WHERE create_time 2024-03-01 00:00:00 AND create_time 2024-03-02 00:00:00这是一个非常经典的范围等价改写。不光是日期对字符串做LEFT(name, 3)、对数字做id 1都属于这类问题。核心原则是不要让索引列单独出现在表达式或函数里要把条件改写成索引列比较原始值的形式。3.4 实例对比重构前后的执行计划与耗时以我这次的订单查询为例我把最终的改动完整列出来改动清单执行ANALYZE TABLE orders恢复统计信息。新增索引KEY idx_user_id_id (user_id, id)。将原SQL拆成两步第一步从主表用idx_user_id_id取订单ID和关键字段第二步再回表或联扩展表补全详细信息。查询语句改为SELECT o.id, o.order_no, o.user_id, o.amount, e.extra_info FROM orders o STRAIGHT_JOIN order_ext e ON o.id e.order_id WHERE o.user_id 123456 AND o.create_time 2024-03-01 00:00:00 AND o.create_time 2024-03-02 00:00:00 ORDER BY o.id DESC LIMIT 20;这里用了STRAIGHT_JOIN强制左表为驱动表因为在这个场景下先按user_id过滤订单主表再关联扩展表比让优化器自己选驱动表更稳定。但要说清楚STRAIGHT_JOIN是应急手段不是常规手段。它违反了让优化器自己选路的原则过度使用会导致SQL不可移植、维护困难。只有在明确了解数据特征、且优化器反复走错的情况下才建议用。重构后的执行计划字段重构前重构后typeALLrefkeyNULLidx_user_id_idrows118000005200ExtraUsing where; filesortUsing index condition线上实测这个查询的耗时从2.1秒降到了35ms左右。P99延迟降回90ms以内。整个过程没有加缓存、没有上读写分离纯粹靠索引和SQL层面重构解决。4. 常见问题与排查技巧实录4.1 索引失效场景速查表重构过程中我顺手整理了一份索引失效场景速查表每次排查慢查询都会先对照一遍能省很多时间场景原因正确姿势索引列参与函数运算索引有序性被破坏表达式移到等号右侧隐式类型转换字段与条件类型不一致保证字段和参数类型统一LIKE以通配符开头无法利用B树前缀匹配反向索引或全文索引OR两段条件不兼顾优化器无法合并多个索引拆分UNION或建复合索引统计信息严重过期基数估算失真ANALYZE TABLE索引列上做计算有序性破坏改写为纯粹范围/等值NULL值判断索引对NULL处理不友好设计默认值替代NULL拿隐式类型转换举例WHERE user_id 123中字段是BIGINT传入的是字符串MySQL会把字段隐式转换成字符串再比较这会导致索引失效。解决办法是确保代码里参数类型和表结构完全一致。这种问题在Java里尤其常见ORM框架没处理好就会踩坑。4.2 优化器执迷不悟时能用的三板斧如果统计信息更新了、索引也重建了优化器还是不走你想让它走的索引常规手段有三板斧按温和程度排序。第一是FORCE INDEXSELECT ... FROM orders FORCE INDEX (idx_user_time) WHERE ...;它的意思是能走这个索引就走不走就报错非常强硬。适合用来确认这个索引到底能不能用以及临时止血但不建议长期留在代码里。因为一旦表结构或数据分布发生变化这个索引可能不再是好的选择而FORCE INDEX会强迫它继续用反而变成新的瓶颈。第二是IGNORE INDEX让优化器别用某个坑人的索引SELECT ... FROM orders IGNORE INDEX (idx_bad) WHERE ...;这比FORCE INDEX温和适合优化器非要用一个明显很差的索引的场景。第三是调整optimizer_switch比如关掉某个策略SET optimizer_switch index_merge_intersectionoff;这个是全局或者会话级别都能设但影响面大一般不在生产环境乱动。我个人的习惯是如果用FORCE INDEX才能解决的慢查询我会把它当作一个设计问题去深挖而不是停在加个关键字就快了的层面。因为本质上优化器不愿意选这条路通常是真实数据特征、统计信息、索引结构三者之间出现了某种矛盾不解决矛盾只压制症状迟早会在另一个量级上爆发。4.3 深分页与排序优化的实战案例和这次慢查询一起处理的还有一个深分页问题。类似ORDER BY id DESC LIMIT 100000, 20的写法在数据量大之后会越翻越慢。原因是MySQL需要先把前10万行全部读出来排序再丢弃只回最后20行。常规的优化办法是延迟关联先取主键再回表SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE user_id 123456 ORDER BY id DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id;子查询里只走索引拿主键不回表速度快很多。如果业务上允许还可以改成基于上次位置的游标分页比如WHERE id ? ORDER BY id DESC LIMIT 20这种写法的时间复杂度基本恒定是最推荐的做法。提示深分页优化并不是换个写法就快了的魔法它的核心思路是让排序阶段只处理尽可能少的数据。凡是要排序的查询都该先想清楚能不能让索引本身提供有序输出能不能减少参与排序的行数4.4 运维侧如何预防优化器赌输慢查询重构完了更重要的是一套能长期运转的预防机制。我的日常工作中这几项是必须做的第一慢查询日志要每天扫。用pt-query-digest对慢查询日志做聚合分析按总耗时排序每周至少看一次Top 20。很多问题在变成事故之前会先在慢查询日志里露出苗头。第二关键表定期做ANALYZE TABLE。尤其是那些频繁批量更新、删除、导入的表建议在每次大批量数据变更后立即执行一次。可以写个定时脚本半夜跑统计信息刷新。第三关注MySQL的异常执行计划变化趋势。可以每天对比performance_schema.events_statements_summary_by_digest里的平均执行时间一旦某个SQL的平均耗时突然上涨就主动去查执行计划是否漂移了。这种事后比对比等到用户投诉再去查要主动得多。第四在设计阶段控住索引数量。每个表索引建议控制在6个以内复合索引要遵循最左前缀原则。索引本身是有维护成本的每多一个索引写入性能都会受损。这也是重构的一部分——把能用复合索引覆盖的多个单列索引合并掉。5. 关于重构的边界代码层和数据库层要分开说5.1 哪些问题应该靠SQL解决哪些应该靠换思路慢查询重构不等于无脑改索引。有一部分慢查询的解法其实不在数据库里。比如某个接口把好几张表的查询都串在一个事务里锁竞争严重又比如某条SQL在代码里被循环调用了上千次。这种问题即使你把索引建出花来作用也有限。以循环里查数据库为例很多团队在ORM里用类似遍历列表逐条查询的写法这在数据量大时会产生海量小查询。正确的方向是批量查出结果集在内存中做关联组装。这种重构看起来和MySQL无关但往往是根因。判断依据很简单如果去掉这条SQL的排序和过滤它依然慢那问题多半不是索引而是查询次数或数据结构本身不合理。索引能解决的是一次查询要在表里找出一小部分数据却被迫扫全表的问题。5.2 和开发同学协作时我常用的沟通方式这次重构不是DBA单方面改完收工还需要后端开发配合发版。历史上很多次索引优化都卡在DBA说改了SQL开发说代码还没合这类沟通问题上。我现在通常会把为什么需要改讲得很直白不是你SQL写得烂而是这个写法在某个数据量级下会被优化器误判我们换个写法让它在所有量级下都稳定。把目标定在执行计划稳定可控上而不是这个查询要快合作起来会更顺畅。另外给开发同学提供一段可以直接用在后端代码里的避坑红线会省掉很多来回确认查询条件里的字段类型必须和表结构一致避免隐式转换。索引列不要包函数、不要参与运算。深分页用游标或延迟关联不要用OFFSET大跳。联表查询要控制驱动表必要时用STRAIGHT_JOIN临时固定。任何新上线SQL先跑一次EXPLAIN确认执行计划。注意这些不是银弹它们只覆盖索引和优化器相关的问题。真正完整的上线前SQL审核还需要考虑锁、事务隔离级别、主从延迟等维度。6. 复盘与后续维护这次重构解决了一条具体SQL但它暴露的是一类问题当数据规模和数据分布发生变化时优化器手里的地图如果不更新它的决策就会失灵。我从这次经历里提炼出三个长期有效的维护动作。第一个动作是建立索引健康档案。每个月的月初对核心表的索引使用情况做一次摸底用performance_schema.table_io_waits_summary_by_index_usage看哪些索引从来没被用过哪些索引的访问次数异常高。长期没被用的无用索引该删就删减少写入负担和存储空间。第二个动作是对慢查询做回归测试。我习惯把过去一个季度出现过的慢查询存成一个SQL清单在每次技术改造、版本升级、索引变更后用这批SQL做一次执行计划对比看有没有本来走索引后来变全表扫描的回归。这种预防成本很低但能提前发现统计信息漂移导致的问题。第三个动作是建立索引使用审计机制。不是单纯看有没有走索引而是看走的索引是否最优。比如一个查询明明可以命中三个候选索引优化器选了哪个如果它选了一个rows很大的就要检查统计信息是否过期或者这段SQL是否触发了优化器不擅长的复杂逻辑。最后再分享一个我踩过多次坑之后养成的习惯任何索引变更都要留足回滚方案。在8.0里做在线DDL虽然不锁表但它会占用额外空间和I/O。我会先把新索引加上观察一段时间再下掉旧索引而不是一次把两种操作同时做完。这听起来保守但生产环境的稳定性往往就靠这些多余的谨慎撑着。
