先说个得罪人的结论八成以上的线上慢查询根子不在MySQL本身而在SQL写法跟B树的有序性对着干。我自己就被索引失效折腾过三回第一回是函数包索引列第二回是隐式类型转换第三回是LIKE前导通配符每一回都把生产环境CPU顶到报警线以上。这篇文章会从索引底层的逻辑讲起把常见的索引失效场景、慢查询定位手段、优化实操套路一次说透最后附上一张可以贴在工位上的排查清单。内容都是我被真实事故教育出来的经验适合正在被线上慢SQL折磨的开发和DBA同学。1. 为什么索引会失效先搞懂B树那点事1.1 一个书架理解索引有序性在聊索引失效之前我建议你先建立一个画面感B树的索引就像图书馆里一个按“书名首字母”排序的书架。你要找“MySQL实战”这本书可以直接跑到M区域去再根据第二个字母定位这就是索引的“有序性”带来的查找效率。顺序查找、范围查找、前缀匹配都能利用这个顺序但只要你换一种玩法比如“把所有书名里含‘MySQL’的书找出来”这个书架就帮不上忙了因为你根本不知道第二个字是什么位置只能一本一本地翻。索引失效的本质就是SQL中的写法破坏了B树叶子节点的这种有序性。B树叶子节点按索引键值升序排列普通等值比较和范围比较都能利用这个顺序。一旦你在索引列上套了函数、做了运算或者让MySQL必须先把索引列转成别的类型它就没办法拿原始键值去和B树中的有序节点直接比较了。理解这个底层逻辑比背“索引失效十种情况”重要得多因为你以后遇到没见过的写法也能自己推出来会不会走索引。1.2 InnoDB回表索引能少查一次就少查一次InnoDB的索引结构还要再补一层认识主键索引的叶子节点直接存整行数据这叫聚簇索引普通二级索引的叶子节点存的是“索引列值 主键值”。所以走二级索引查出主键之后还得再回聚簇索引里取一次完整行数据这个过程叫回表。回表很贵尤其当一条SQL命中几千几万行时回表次数就是命中行数。这解释了一个很多人忽略的现象明明字段上有索引执行计划却不用它。比如status字段只有0和1两个值表里95%的数据都是1你查WHERE status1优化器算了一笔账走二级索引大概要扫一大半索引再回表读一大半数据还不如直接全表扫描来得痛快。所以“有索引不用”不一定是索引失效也可能是优化器认为你不划算。后面要优化的方向就变成要么减少回表比如用覆盖索引要么改变查询条件的选择性让优化器觉得索引更便宜。1.3 看执行计划之前先设置好EXPLAIN输出格式排查索引问题第一件事永远是看执行计划但别只会EXPLAIN SELECT ...。MySQL 5.6开始支持EXPLAIN FORMATJSON里面能看到cost_info、attached_condition等更多细节MySQL 8.0.18开始支持EXPLAIN ANALYZE它会真实执行SQL并返回每个环节的实际耗时和行数这对定位“优化器估算不准”特别有用。需要注意EXPLAIN ANALYZE是真的会跑SQL的在线上生产环境执行前务必确认是在只读实例或者选在低峰期别为了查一条慢SQL把库里搞得更慢。还有一个容易被忽略的小技巧执行EXPLAIN之后立刻执行SHOW WARNINGSMySQL会告诉你它把SQL重写成了什么样子。很多时候你写的条件被优化器做了什么隐式转换这里一眼就能看到。我个人习惯是先在测试库用EXPLAIN ANALYZE拿到实际执行数据再在生产库用EXPLAIN FORMATJSON确认成本估算两块信息拼起来基本能还原一条SQL的真实执行全貌。2. 我踩过的6类索引失效场景含事故经过2.1 场景一函数包裹索引列定时任务把CPU打满第一次事故发生在订单表上表里有1800万行数据create_time上明明建了索引。某天凌晨定时任务一跑数据库CPU直接冲到100%慢查询日志里刷出来一条SQLSELECT COUNT(*) FROM orders WHERE DATE(create_time) CURDATE();执行计划是typeALL扫描行数接近全表。问题就出在DATE(create_time)这个函数上MySQL要对create_time逐行执行DATE()之后再和CURDATE()比较B树索引里存的是原始时间值没法直接参与比较所以这个索引等于报废。改写其实很简单把函数放到等号右边左边保留裸露的索引列SELECT COUNT(*) FROM orders WHERE create_time CURDATE() AND create_time CURDATE() INTERVAL 1 DAY;同样的逻辑改写后这条SQL从跑40分钟变成80毫秒。如果你的业务SQL实在改不了比如是ORM自动生成的MySQL 5.7可以用生成列MySQL 8.0可以直接建函数索引让MySQL在写入时就把DATE(create_time)的值维护好查询时就能走索引。但我还是建议优先改写SQL函数索引虽然好用却增加了写入成本而且会让索引定义变得隐蔽后来人维护起来容易懵。核心原则条件左侧尽量留裸列运算全部放到右侧。这条规矩几乎覆盖90%的“函数导致索引失效”场景。2.2 场景二隐式类型转换idx_user_id形同虚设第二次事故排查起来比第一次隐蔽。业务侧反馈用户列表页越来越卡查出一条SQLSELECT * FROM user WHERE mobile 13800138000;mobile字段在表结构里是varchar(20)也建了索引但EXPLAIN显示key是NULL。原因是MySQL的隐式类型转换规则当字符串列和数字常量比较时MySQL会把字符串列转成数字再比较。等价于在索引列上执行了CAST(mobile AS signed)又踩了“函数包索引列”的坑。解决办法就是让传入参数与字段类型完全一致SELECT * FROM user WHERE mobile 13800138000;在应用层还有一个更隐蔽的来源Java的Long类型、Python的int类型从接口入参一路透传到SQL里数字就变成了字符串列的比较。我现在的习惯是所有代码评审里出现“数字字段”和“字符串字段”的关联条件都要顺手看一眼表结构确认两边类型一致。两个表join的时候也一样a.user_id b.user_id一个bigint一个varcharMySQL同样可能做隐式转换导致索引失效。遇到这种直接SHOW WARNINGS如果看到类似“Converted ... to bigint”的提示那就是它了。2.3 场景三LIKE前导通配符搜索接口全表扫第三次事故是我自己写出来的。当时给内容系统加了一个标题搜索接口SQL长这样SELECT * FROM article WHERE title LIKE %MySQL%;上线前压测QPS惨到只有几十一查执行计划typeALL全表扫描。这是因为B树索引按“前缀”有序你搜“MySQL%”它还能定位到M开头的位置一搜“%MySQL%”MySQL不知道第一个字符是什么只能把所有title都取出来做匹配。这次事故教会我两件事。第一业务上如果真要做模糊搜索最省事的是把数据同步到ES这类搜索引擎里别硬蹭MySQL。第二如果数据量不大至少改成后缀匹配LIKE MySQL%让它能利用前缀索引。还有一个折中方案是MySQL全文索引FULLTEXT配合MATCH...AGAINST做全文检索但它对中文分词的支持有限词库需要自己处理只适合简单场景。真要说保命技巧那就记住前导通配符放弃索引没有例外。2.4 场景四OR条件连接一个没有索引全报废OR也是个经典刺客。比如订单查询SELECT * FROM orders WHERE user_id 123 OR order_status 1;user_id上有索引order_status上没索引你可能觉得至少能先走user_id的索引把范围缩小再过滤order_status。但现实是优化器评估后经常直接全表扫因为要满足“OR两边的并集”它得把所有order_status1的行都找出来这部分没有索引就等同于全表扫描。如果你历史数据分布特殊order_status1只是极少数那可以拆成两个查询再合并结果SELECT * FROM orders WHERE user_id 123 UNION ALL SELECT * FROM orders WHERE order_status 1;两条子查询各走各的索引再把结果合并。前提是你要确认两个结果集不会有重复行否则用UNION去重。还有一种优化思路是给OR两侧都建索引让优化器考虑index_merge但它的选择受数据分布影响很大不如拆查询稳定。从我个人经验看遇到跨列OR我默认先改成UNION方案再看执行计划。2.5 场景五复合索引乱序与范围截断复合索引是慢查询优化里最容易“以为能走索引实际只有一半能走”的场景。假设订单表建了联合索引(user_id, status, create_time)如果你查WHERE user_id 123 AND status 1没问题。如果查WHERE status 1 AND user_id 123MySQL优化器其实会做谓词排序依然能走索引所以“顺序写反了”不是最大的坑。真正的坑有两个。第一个坑是缺前导列。比如查WHERE status 1 AND create_time 2024-01-01联合索引最左边是user_id查询里没有它后续列status、create_time就都用不上。第二个坑是范围截断。WHERE user_id 123 AND status 0 AND create_time 2024-01-01虽然user_id和status都能用索引但status是范围条件它后面的create_time就没法继续利用索引的有序性了只能回到表里再过滤。一个实用技巧是把范围条件改成多个等值条件比如status IN (1, 2, 3)。MySQL对IN列表在复合索引里的处理通常会把每个值当作等值来看后面的create_time还能继续用索引。我每次用这个技巧都会重新看一遍EXPLAIN确认key_len是否有变化因为不同版本优化器的行为不完全一致。建复合索引的核心思路应该是把等值条件放在前面范围条件放在最后然后为高频查询单独设计索引而不是一个宽索引包打天下。2.6 场景六优化器“看不上”索引的时候怎么办有时候你检查了所有SQL写法都没问题索引也建了但执行计划就是不走这时候大概率是优化器的成本估算问题。第一个常见原因是统计信息过期表频繁增删改之后rows估算严重失真。解决办法是低峰期执行ANALYZE TABLE orders;让优化器重新采样。第二个原因是索引列基数太低。比如status字段只有0、1、2三个值你查WHERE status0即使有索引优化器也知道会命中大量行再加上SELECT *回表成本高它宁可全表扫描。这时候加一个(status, create_time)的联合索引让过滤条件更精确效果往往比单列索引好得多。第三个原因是你可以用FORCE INDEX临时验证一下“强制走索引会不会更快”但千万不要长期写死在业务SQL里数据分布一变强制索引可能比全表扫描还慢。3. 慢查询定位把线上慢SQL连锅端3.1 慢查询日志的正确打开方式优化慢查询的前提是先拿到慢查询别等出事了才临时查。MySQL的慢查询日志是排查的第一入口推荐这样设置SET GLOBAL slow_query_log ON; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON; SET GLOBAL min_examined_row_limit 100;long_query_time是慢查询阈值单位秒支持小数线上建议先设1秒稳定后再压到0.5秒。log_queries_not_using_indexes会额外记录“没用索引”的SQL但要配合min_examined_row_limit使用不然一个只扫描几行的小表没走索引也会刷日志慢查询文件会爆炸。这里的参数很多都是SET GLOBAL生效后不持久化重启MySQL就丢了最好同时写进my.cnf的[mysqld]段。慢日志文件默认是文本格式可以直接用tail看但因为线上日志量很大我强烈建议不要把log_output改成TABLE否则写系统表也会有额外开销。正确做法是让慢日志落在文件里再交给工具分析后面2.2会讲怎么分析。3.2 用pt-query-digest把日志变成报表Percona Toolkit里的pt-query-digest是分析慢日志的标配工具它会把相似的SQL按指纹聚合按总响应时间排序输出报表。用法很简单pt-query-digest /var/log/mysql/slow.log slow_report.txt打开报表第一屏的Profile就是“慢SQL排行榜”重点看三个指标Count代表执行次数Exec time代表总耗时Rows_examined代表扫描行数。如果一条SQL的Rows_examined是几百万但Rows_sent只有几十行这就是典型的“用命去筛选数据”你不用看SQL都知道索引没用好。如果暂时没有Percona工具MySQL自带的mysqldumpslow也能应急mysqldumpslow -s t -t 10 /var/log/mysql/slow.log直接列出执行时间最长的10条。不过它输出的信息不如pt工具丰富生产环境我建议还是装一把Percona Toolkit它是开源工具用起来也放心。分析完日志之后建议定期把历史报表归档方便对比一周前后慢查询趋势。3.3 没有Percona工具时用sys库应急有些公司环境不允许装额外工具这时候MySQL 8.0自带的sys库就派上用场了。我最常用的一条SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 20;statement_analysis视图本质上是聚合了performance_schema.events_statements_summary_by_digest的数据能直接看到“累计执行时间最长”的SQL模板、平均扫描行数、平均返回行数。再配合sys.session查当前正在执行的会话可以快速抓住正在拖垮数据库的“元凶”。如果发现某条SQL一直卡住我还会看sys.innodb_lock_waits因为有时候慢不是因为查询本身而是被锁阻塞了。这个细节很关键很多人一看到慢SQL就直接加索引加了半天没效果结果查出来是别的会话锁着同一行。所以定位慢查询不只要看执行计划还要看会话和锁等待。3.4 建立慢查询治理的日常节奏工具再好没有固定节奏也白搭。我现在在团队里推的是“三个每天”每天早上看一遍慢日志Top10每周汇总一次新增的SQL模板每次功能上线前跑一遍典型查询的EXPLAIN ANALYZE。慢查询治理和告警规则一样宁可先让我“狼来了”几次也不能等全表扫描把数据库打挂了才发现。自动化方面可以用脚本定时跑pt-query-digest把Top10慢查询推送到群里。也可以结合监控系统对performance_schema里的events_statements_summary_by_digest做采集当某条SQL的平均执行时间超过阈值就告警。慢查询治理的核心不是“今天救火”而是让团队形成条件反射看到慢SQL先定位再分析最后才动手优化。4. 慢查询优化的完整实操套路4.1 从EXPLAIN ANALYZE开始别只看type拿到一条慢SQL我第一步不是改代码而是复制到测试库跑一遍EXPLAIN ANALYZEEXPLAIN ANALYZE SELECT user_id, COUNT(*) FROM orders WHERE status 0 GROUP BY user_id;输出会像这样虽然不同版本格式略有差异但能看清每步的真实耗时- Group aggregate: count(*) (actual time35.2..36.1 rows1999 loops1) - Index range scan on orders using idx_status_user_id (actual time0.02..31.5 rows1999 loops1)EXPLAIN ANALYZE和普通EXPLAIN最大的区别就是“实际执行”。普通EXPLAIN里的rows是估计值可能和实际差出十万八千里EXPLAIN ANALYZE告诉你真实扫描了多少行每一步花了多少毫秒。生产中如果没法执行真实查询退而求其次用EXPLAIN FORMATJSON看cost_info里的read_cost和eval_cost也能大致判断代价分布。在EXPLAIN结果里我最关注两块第一是type从好到差大致是const、eq_ref、ref、range、index、ALL看到ALL就要警惕全表扫描第二是Extra出现Using filesort代表排序没走索引出现Using temporary代表可能需要临时表。这两块是我优先优化的切入点。也别迷信Using index condition它只是表示用了索引条件下推具体效果还要看扫描行数。4.2 覆盖索引是银弹之一但别乱加SELECT *是回表大户一个典型的覆盖索引优化案例SELECT user_id, status FROM orders WHERE user_id 123 AND status 1;如果你建了联合索引(user_id, status)查询需要的两个字段全都在索引里MySQL直接从二级索引返回不需要回表EXPLAIN里的Extra会显示Using index这就是覆盖索引。这个优化对高频小查询特别有效能把几次随机I/O变成一次有序索引扫描。但覆盖索引不是万能药。如果你把一个大字段content也塞进索引里索引会变得又肥又大插入和更新都要多维护一份数据反而拖慢写TPS。我的经验是覆盖索引的收益一定要和写放大做权衡。先看这条SQL的执行频率一天跑几千次且都是SELECT很重的场景才值得为它专门建覆盖索引低频跑批任务就真的没必要。索引不是装饰品每多一个索引写一条记录就多了一棵B树要更新。4.3 SQL改写很多“慢”不是索引问题有些慢查询就算把索引建满也救不回来因为SQL的算法效率太低。比如经典的深分页问题SELECT * FROM orders ORDER BY id LIMIT 100000, 20;MySQL要把前100000行都扫描并丢弃只返回最后20行。虽然表的主键索引能用到但越往后翻页偏移量越大耗时越长。改写思路是“延迟关联”先通过索引快速定位到需要的20个主键再回表取完整数据SELECT o.* FROM orders o JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id t.id;子查询t里只查id可以用主键索引很轻地跳过前面的行外层再根据20个主键回表效果立竿见影。还有一个场景是ORDER BY create_time LIMIT 10如果查询条件里没有其他过滤你为它建一个(create_time)单列索引就能让排序走索引如果还有WHERE status0那索引应该是(status, create_time)这样既能过滤又能排序。SQL改写很多时候比的不是你SQL写得有多炫而是你能不能顺着索引结构思考。4.4 索引维护和统计信息更新索引不是建完就一劳永逸的。数据频繁增删改以后索引会产生碎片B树的页利用率下降扫描范围变大。这时候可以考虑OPTIMIZE TABLE重建表但它是重量级操作会锁表或占用大量I/O必须选在维护窗口做。更常见的做法是执行ANALYZE TABLE更新优化器需要的统计信息让执行计划重新变得准确。另外冗余索引是很多公司的通病。有(user_id, status)这个联合索引有些人还习惯单独建一个(user_id)索引其实联合索引已经覆盖了user_id这一列的前缀能力单独索引纯属浪费。你可以用sys库的两个视图来排查SELECT * FROM sys.schema_redundant_indexes; SELECT * FROM sys.schema_unused_indexes;redundant_indexes会列出被其他索引覆盖的索引unused_indexes会列出从来没被用过的索引。删除索引之前我会先观察一段时间确认没有业务高峰期查询依赖它再在低峰期删除。这个习惯帮我删掉过不少“纸面索引”释放了很多不必要的写入开销。5. 索引失效与慢查询排查速查清单5.1 一张常用的排查流程表我自己把排查经验浓缩成下面这张表贴在了团队Wiki首页。遇到慢SQL先对号入座大部分问题都能在几分钟内定位。现象可能原因建议动作typeALLkeyNULL函数包列 / 隐式转换 / 真的没索引用SHOW WARNINGS看重写SQL再补索引或改写有索引但rows接近全表选择性太低 / 回表太多改覆盖索引或者改变查询条件ExtraUsing filesortORDER BY没走索引建(过滤列, 排序列)联合索引ExtraUsing temporaryGROUP BY和ORDER BY不一致调整字段顺序或改写查询平时快偶尔慢统计信息过期 / 锁等待ANALYZE TABLE查锁等待联表查询很慢关联字段类型、字符集不一致统一字段类型和排序规则这张表最左边是“现象”不要一上来就归因到“索引失效”。很多慢查询其实是“没有匹配的索引”和“数据分布不配合”共同造成的只有按表里的节奏一步步排查才能避免在错误方向上加索引。5.2 三个事故教训的复盘回看我自己的三次事故每次都不是什么高深问题全是基础中的基础。第一次函数包列是因为当时觉得DATE(create_time)CURDATE()“看起来挺清晰”忽略了索引列上的函数是索引杀手。第二次隐式类型转换是因为接口传参的类型和表结构字段类型不一致代码评审时没人查数据库字段定义。第三次前导通配符是因为只想着“给搜索功能加个LIKE”却没想过这种写法压根走不了索引。这三次事故有一个共同点线上数据库已经有明显症状了才想起来看执行计划。如果我在上线前就把每条SQL的EXPLAIN ANALYZE结果放进发布检查项里后面两起事故根本不会发生。所以我现在给团队立的规矩很简单涉及新查询的变更必须附带测试环境的执行计划和预估扫描行数没有执行计划的SQL不允许上生产。5.3 最后嘱咐不要一上来就加索引我知道很多同学看到慢SQL第一反应就是“给表加个索引”。这个动作可以理解但一定忍住。加索引不是不行而是要先回答三个问题这条SQL跑在什么场景、数据分布怎么样、有没有更合理的SQL写法。很多时候改写掉一个函数、统一一个参数类型比增加一个索引有效得多还不用承担写入放大。我个人现在的习惯是先开慢日志再抓SQL模板然后用EXPLAIN ANALYZE在测试环境还原最后才决定要不要加索引。如果加索引我也只加“刚刚好够用”的联合索引不多建一个冗余字段。这套流程走下来我踩过的那些坑基本都能在半小时内解掉。希望这份用三次事故换来的指南也能帮你少走几次弯路。