1. 先搞清楚为什么索引会失效B树和回表聊索引失效之前得先把底层原理捋一遍。很多人排查慢查询时只看表面什么函数导致失效隐式转换导致失效背了一堆口诀但一到真正复杂的SQL还是懵。原因很简单你没理解索引在MySQL里到底是怎么干活的。MySQL的InnoDB引擎用的索引结构是B树。这个东西你可以想象成一个书架的索引目录非叶子节点存的是引导信息叶子节点存的是实际数据。对于主键索引叶子节点直接存整行数据这叫聚簇索引对于二级索引普通索引叶子节点存的是索引列的值加上主键值。走二级索引查数据的时候MySQL先到二级索引的B树上找到主键再拿着主键回聚簇索引查整行这个过程叫回表。举个例子一张用户表有id、name、age三个字段你在name上建了索引。执行select * from user where name 张三的时候MySQL先在name这棵B树里找到张三对应的主键id再回到主键索引树里读这一行的完整数据。这一步回表消耗的是随机IO代价比顺序IO高不少。那联合索引又是什么联合索引是多个字段一起建索引排序规则是先按第一个字段排第一个字段相同再按第二个字段排以此类推。这就是为什么联合索引必须遵循最左前缀原则——你去查where b ?而索引是(a,b,c)MySQL在B树里根本没法直接定位因为第一层排序维度是a你跳过a直接用b查整个索引树的剪枝策略就失效了。弄懂这些之后索引失效的本质也就清楚了所谓失效就是MySQL优化器评估之后觉得走这个索引的成本比全表扫描还高或者SQL的写法导致B树无法按有序结构进行快速定位。两种情况一种是物理上没法走一种是逻辑上不值得走。这俩在排查的时候要分开看后面我会详细说。还有一个很多人忽略的概念基数。基数Cardinality指的是一列中不同值的个数。在name上建索引如果全表一万行名字只有张三和李四两种那这个索引的基数就是2区分度极低。MySQL优化器心里有杆秤走这种索引要回表几千次还不如直接全表扫描。所以有时候索引没失效但优化器主动放弃你explain看到的是type ALL别急着骂SQL先看看索引区分度。2. 实战中最高频的八种索引失效场景这一节是干货中的干货。我在实际工作里排查过的慢查询80%都逃不出下面这几个坑。每个场景我都会给示例、说原理、给解法你对照自己项目里的SQL检查就行。2.1 隐式类型转换最常见的隐形杀手这是排查慢查询时最容易撞见的问题隐蔽性极强。最常见的场景表里某个字段是varchar类型但你在查询条件里传了数字。-- user表中phone是varchar(20)索引建在phone上 select * from user where phone 13812345678;你以为你查的是字符串但MySQL看到等号右边是数字会自动把varchar列转成数值类型再比较。一旦对索引列做了类型转换索引就废了因为B树里存的是原始字符串没经过转换没法直接比较。同样的坑还有字符串和日期比较、字符集隐式转换比如utf8mb4和utf8比较有时会出现排序规则不兼容导致索引失效等。排查技巧执行explain看type是不是ALL再看Extra里有没有Using where如果有可疑就试试把SQL改成显式传字符串再跑一遍对比执行计划。select * from user where phone 13812345678;这条就是正确的写法。经验之谈所有查询条件里的值尽量跟字段类型严格对齐。前端传参、接口定义、框架的自动类型转换每一层都可能是隐患最好是代码里统一做类型校验。2.2 对索引列使用函数或表达式计算这几乎是教科书级别的禁令但工作中依然有人踩。原因很容易理解B树的叶子节点存的是字段的原始值如果对字段套了函数MySQL需要对每一行的字段值先做函数运算再拿结果去匹配。这个过程没法走索引的有序查找只能全表扫描。select * from user where DATE(create_time) 2024-05-15; select * from user where YEAR(create_time) 2024;第一个SQL的意图是查5月15日创建的用户正确写法应该是范围查询select * from user where create_time 2024-05-15 00:00:00 and create_time 2024-05-16 00:00:00;这样写create_time就不用套函数索引能直接用上。第二个SQL如果想按年份筛选建议在表里加一个year字段或者生成列MySQL 5.7支持。另外一个容易被忽视的点是表达式运算比如where price * 2 200。这种对索引列做算术运算的写法一样会导致失效。解决办法是把表达式移到等号右侧where price 200 / 2。规则是索引列单独出现在比较符的一侧不要有任何函数或运算包裹。2.3 前导模糊查询%开头的LIKEwhere name like %张%这条我几乎每轮面试都会问也是实际业务里最常见的模糊搜索写法。原理很简单B树是按顺序排列的查询时利用的是前缀匹配能力。你给的条件是以任意字符开头中间包含张MySQL没法用索引树从根节点快速定位第一条符合条件的数据只能扫描所有叶子节点。但不代表LIKE一定不能用索引。下面这几种情况就能走-- 右模糊可以用索引 select * from user where name like 张%; -- 左模糊后面条件是等值匹配联合索引可以局部利用 select * from user where name like %张 and age 18;第二种情况如果联合索引是(name, age)MySQL可以用age的等值条件去定位前导模糊不影响部分索引使用。但单独就%张这种写法基本放弃治疗。业务上真要支持任意位置的模糊搜索方案无非几种用全文索引FULLTEXT、用ES等搜索引擎、拆词存冗余字段。小表直接全表扫也就几十毫秒别过度设计大表就得从架构层面解决而不是跟SQL死磕。提示查explain如果看到type ALL且SQL里有LIKE优先怀疑前导%。还有一种情况like 张%能走索引但优化器评估回表成本太高也可能不走属于下一节的范围。2.4 OR连接条件一个不走全部遭殃OR是另一个高频刺客。很多人以为where a 1 or b 2只要a和b都有索引就能快速查实际上MySQL对OR的处理是只要有一个条件无法使用索引整个查询就可能变全表扫描。因为OR的语义是结果的并集MySQL需要一个统一的执行方案。-- a有索引, b没有索引 select * from user where a 1 or b 2; -- 上面这条,哪怕a有索引,也可能全表扫正确打开方式是拆成两条SQL用UNION合并select * from user where a 1 union all select * from user where b 2;但注意这里的union all可能产生重复数据需要根据业务确认。如果两边结果集理论上不重叠相当于互斥条件union all效率更高不放心就用union去重代价是多一次排序去重大表慎用。另外还有一种OR失效的情况OR两边的列都在联合索引里但顺序不对。比如索引(a,b)查询where a 1 or b 2OR条件下MySQL没法保证两段分别走索引的最优路径通常也会转为全表扫描。MySQL 8.0的优化器对OR的处理能力比5.7强不少但也不能完全指望它。实战中遇到OR慢查询第一反应就应该是改写为union或IN。2.5 联合索引违反最左前缀法则这是面试爱问、工作中最爱犯的一类问题。联合索引(a,b,c)查询条件必须从a开始匹配才有机会走索引。你写where b 2 and c 3完全没有a索引从第一个维度就无法定位直接失效。但有人会杠我的SQL明明按照最左前缀写了为什么还是不走索引这里有三个容易忽略的细节。第一个是顺序问题。where b 2 and a 1MySQL优化器会调整条件顺序这个其实是能走索引的。真正的问题是缺列。第二个是范围查询的截断效应。索引(a,b,c)查询where a 1 and b 5 and c 3。这时候a是等值匹配可以用索引定位b是范围查询B树在b这个维度上进入范围扫描c的等值条件没法继续走索引了因为b的范围导致c在索引中的排序不再连续。现象是explain里key_len只用到b的长度c没用上。解决方案是索引设计时把范围查询字段放在联合索引的后面建索引(b, a, c)或者(a, c, b)视具体高频查询而定。第三个是排序和分组字段。where a 1 order by c如果索引是(a,b,c)虽然a等值匹配了索引但order by c没法利用索引完成排序因为中间隔着b。MySQL要么在内存临时表排序Using filesort要么放弃索引。这也是索引失效的变种——索引能定位但不能避免排序性能依旧拉胯。2.6 IS NULL或IS NOT NULL的坑很多人以为索引列IS NULL走不了索引这个结论得看情况。到底是全NULL还是全是NULL决定了优化器的选择。如果一个表一百万行某索引列90%都是NULL那你查where col IS NULL优化器会觉得还不如全表扫因为走索引也差不多要访问90%的数据。反过来如果NULL值很少IS NULL是能走索引的。更多的坑出现在IS NOT NULL上。如果这个列大部分值不为NULL那么IS NOT NULL本质上要访问几乎所有行走索引回表成本更高优化器直接全表扫。实操层面的建议业务字段尽量避免允许NULL用默认值代替、0等建表时NOT NULL DEFAULT。有NULL的列is null要验证执行计划别想当然。2.7 !、、NOT IN 的负向查询负向查询能不能走索引同样取决于数据分布。where status ! 1如果你这个表99%的status都是1那查剩下的1%虽然用索引能精确定位到那2万行但回表2万次对优化器来说还不如全表扫一遍100万行。所以这种SQL有时候走索引有时候不走核心是区分度。NOT IN也有类似问题而且它还有一个潜在优化点如果子查询的结果集很小改写成LEFT JOIN往往更高效。说句公道话!和NOT IN不是绝对不能走索引MySQL 5.6在特定条件下是会用的。但你要知道负向查询天然不利于索引快速定位尽可能用正向查询表达。2.8 字符集与排序规则不统一这个坑比较隐蔽一般出现在多表关联或子查询嵌套场景里。如果两张表的相同含义字段一张是utf8mb4一张是utf8甚至有的老库是latin1join的时候MySQL需要把一边的字符集转换成另一边的转换操作会导致join列上的索引失效。我之前排查过一个案例订单表order.user_id是utf8mb4用户表user.id是utf8老表两个表join查数据user表虽然id有主键索引但explain显示join类型是ALL用户表全表扫几万行的小表还好等数据量到了一百万直接卡死。解决办法很粗暴统一所有库表字符集最好是utf8mb4——它兼容utf8还能存emoji。建库建表时保持CHARSET一致排序规则collation也要一致比如都是utf8mb4_general_ci。提示必要时可以用CONVERT(id USING utf8mb4)强制转换但这会让索引失效只适合临时救急不能长期依赖。3. 优化器的小算盘有时候不是SQL的错前面讲的场景大多是SQL写法问题但还有一种更隐蔽的情况SQL看起来完全规范索引也建得没毛病可执行计划就是扫全表。这时候要怀疑的不是SQL而是数据本身和优化器的判断逻辑。3.1 统计信息过期导致估算错误InnoDB通过采样统计索引的基数和数据分布这些统计信息不是实时的是定期更新的通过analyze table或后台自动触发。如果你表的数据量剧烈变化比如大批量导数据、delete大量行统计信息没跟上优化器可能基于旧数据做了错误判断选了一条不理想的执行路径。解决方式大操作后手动执行analyze table 表名;刷新统计信息。很多DBA发布的运维规范里都有这条。3.2 回表成本压倒一切记住这个公式逻辑走索引 索引查找成本 回表次数 × 单行回表成本。如果索引列区分度低要回表的行数占总行数比例高优化器毫不犹豫选全表扫描因为顺序IO比随机IO快得多。这类问题的核心解法不是改SQL而是优化索引设计尽量用覆盖索引让查询的字段全部包含在索引里避免回表。比如select name, age from user where name 张三如果建了(name, age)联合索引Extra里显示Using index这一步省掉了大量回表IO。大字段别往索引里塞比如text、长varchar索引会膨胀且排序成本高。高频查询考虑索引下推ICPMySQL 5.6默认开启能让WHERE条件在索引层面先过滤减少回表。3.3 LIMIT与ORDER BY的组合误导order by xx limit n是经典分页场景但很多人不知道limit并不总是帮助SQL变快它可能让优化器选错方案。举个例子select * from user order by age limit 10如果age上有索引优化器可能觉得走索引顺序读前10行很快但它忽略了还要回表读整行——如果age索引区分度低前1000行都落在同一个age值上回表成本瞬间暴涨还不如全表扫filesort。遇到分页越深越慢的问题limit 100000, 10常规解法是延迟关联select u.* from user u inner join (select id from user order by age limit 100000, 10) tmp on u.id tmp.id;子查询里只查索引列id和排序字段age走的是索引覆盖扫描不需要回表外层再根据id批量回表取整行数据只回表10次量级完全不一样。3.4 优化器开关与参数MySQL提供了一些开关控制优化行为。optimizer_switch里有个index_condition_pushdownICP一般默认开启但有些云厂商的默认参数可能不一样。还有optimizer_switch use_invisible_indexeson可以临时让隐藏索引生效用于测试。sql_mode和eq_range_index_dive_limit这种参数也不会直接影响索引失效但可能影响选择。遇到极其反常的执行计划时可以用explain formatjson配合optimizer_trace看优化器内部的成本计算过程这个后面排查篇细说。4. 排查与验证从EXPLAIN到Optimizer Trace很多新手学会了建索引但不会验证索引到底有没有被用上。这一节我直接把排查流程和实操经验给出来你按步骤操作基本能定位95%的索引失效问题。4.1 EXPLAIN的正确打开方式执行explain select ...重点看这几列type从好到差大致是system const eq_ref ref range index ALL。看到ALL就要警惕这是全表扫描看到index也不一定好它代表全索引扫描遍历整个索引树比ALL好一点但仍是扫描。key实际使用到的索引名。如果为NULL说明这条SQL没用索引。key_len使用的索引长度。联合索引场景特别有用如果你用了两个条件key_len只相当于一列的长度说明第二列没生效。rows优化器预估需要扫描的行数。这个数字异常大就说明有问题。Extra有很多信号值。Using index表示覆盖索引爽Using where配合typeALL大概率全表扫Using filesort表示排序没用上索引Using temporary表示用了临时表大查询要警惕Using index condition表示ICP下推5.6的正常表现。实操建议排查SQL时先看type和rows再看Extra。type是ALL且rows和表总行数差不多基本就是索引失效。注意explain的结果是基于优化器估算的不是实际执行值。8.0里可以用EXPLAIN ANALYZE看到真实的执行耗时和行数这个比只看计划靠谱得多。4.2 用SHOW WARNINGS看优化器改写MySQL优化器在真正执行前会做一些查询改写。有时候你写的SQL没问题但优化器为了统一处理偷偷改了一个函数或转换。用explain extended或者show warnings能看到优化器改写后的SQL。比如你写了隐式类型转换的SQLshow warnings里会显示类似cast(phone as double)的字样这样就实锤了索引失效的原因。这是我强烈推荐的排查手段比对着规则猜要快得多。4.3 追踪优化器决策过程MySQL 8.0的optimizer_trace可以查看优化器做了哪些成本计算、为什么选择全表扫描。开启方式set session optimizer_traceenabledon; select * from user where phone 13812345678; select trace from information_schema.optimizer_trace\Gtrace输出很长重点关注considered_execution_plans和rows_estimation部分你能看到优化器估算的走索引行数、回表成本以及最终选择的原因。这个工具比较高端一般只有遇到很难用常识解释的优化器行为时才会用。比如你觉得明明索引区分度很高但就不走索引——这时候用trace看一下可能是统计信息过期或者成本模型有偏差。4.4 慢查询日志和sys库配合定位如果光看单条SQL不够还需要从全局视角找到高频慢SQL-- 查看慢查询日志配置 show variables like slow_query%; -- 开启慢查询 set global slow_query_log ON; set global long_query_time 1;开启后把慢查询日志里的SQL捞出来逐一用explain排查。另外MySQL自带的sys库里有一些好用的视图-- 没有被使用的索引 select * from sys.schema_unused_indexes; -- 冗余索引检测 select * from sys.schema_redundant_indexes; -- 全表扫描的表 select * from sys.statements_with_full_table_scans;这三个视图能帮你从宏观角度发现索引问题schema_unused_indexes告诉你哪些索引建了但没被用过该删就删别让写性能白亏schema_redundant_indexes告诉你有没有跟现有索引重复的索引比如(a,b)和(a)基本重复后者纯属浪费空间和写入成本。4.5 常见索引失效问题速查表现象可能原因验证方法解决办法typeALLSQL含隐式类型转换条件值类型与字段不一致查询SHOW WARNINGS看cast提示条件值显式匹配字段类型typeALLWHERE列套函数索引列被函数包裹EXPLAIN看Extra改范围查询或拆分条件typeALLLIKE前导%无法用前缀匹配定位执行计划确认全文索引/ES/改右模糊索引有但不用OR条件有分支不能走索引EXPLAIN确认拆UNION ALL联合索引只用了一部分违反最左前缀或范围截断看key_len是否偏短调整索引顺序/拆分索引大表查询反直觉统计信息过期ANALYZE后重试EXPLAIN手动更新统计信息索引覆盖不完整查询列超出索引范围看Extra是否有Using index建覆盖索引排序后有文件排序ORDER BY列不在索引中或顺序不符Extra出现Using filesort调整索引列顺序承担排序5. 建索引与改SQL的实用清单踩过足够多的坑之后我总结了一套比较实用的规则分享给你。建索引五问第一这个查询的高频条件是什么提取出所有等值查询、范围查询、排序、分组字段。第二哪个查询是业务核心优先为最高频、最敏感的查询设计联合索引不要人为了一两个低频查询建一堆索引。第三联合索引字段顺序怎么排区分度从高到低放前面范围查询放后面这是基本原则但也要看实际查询分布。第四能不能做到覆盖索引高频查询的select字段能塞进索引就尽量塞。第五这个索引值不值得建写入频繁的表索引过多会拖慢insert、update需要在读写之间权衡。改SQL五招条件值严格匹配字段类型杜绝隐式类型转换。不在索引列上做函数、运算、拼接。范围查询优先写成区间形式、、between and别写函数提取。OR改写UNIONNOT IN改写关联或反向条件。order by和group by字段尽量和where条件构成联合索引。6. 我个人在实际排查中的几点体会最后分享三个我自己的习惯算不上什么高深技巧但真的能省很多事。第一个习惯是每次改完SQL或索引都立刻跑一遍执行计划并记录前后对比。我见过太多人在Navicat里点一下explain看到type变成ref就完事了结果上线后实际执行依然慢。原因很简单explain是估算的死数据的统计信息可能不准。真正稳妥的做法是用explain analyze8.0或者直接把SQL执行一遍看耗时。执行计划前后对比至少保留到项目上线一周后万一性能波动可以翻出来复核。第二个习惯是定期清理冗余索引。用sys库的视图每个月跑一遍把重复索引、无用索引列出来拿到群里跟同事确认后删除。索引不是越多越好每次写操作都要更新所有索引三个冗余索引能把写入性能拖掉20%。第三个习惯是给联合索引列顺序写注释。建索引的SQL后面随手加一行注释说明这个列顺序是按xx业务查询设计的不要随便调整。不然半年后新同事看不懂当初为什么这么建为了一个临时需求加了一个普通索引反而影响高频SQL走原来的联合索引。索引失效这个问题看着是几十个碎片化的知识点但内核只有一句话索引是有序的数据结构你写的SQL要么打破了有序定位要么让优化器觉得走有序定位不划算。把每个案例归到这两类里你的排查思路就会清晰很多。
1. 先搞清楚为什么索引会失效B树和回表聊索引失效之前得先把底层原理捋一遍。很多人排查慢查询时只看表面什么函数导致失效隐式转换导致失效背了一堆口诀但一到真正复杂的SQL还是懵。原因很简单你没理解索引在MySQL里到底是怎么干活的。MySQL的InnoDB引擎用的索引结构是B树。这个东西你可以想象成一个书架的索引目录非叶子节点存的是引导信息叶子节点存的是实际数据。对于主键索引叶子节点直接存整行数据这叫聚簇索引对于二级索引普通索引叶子节点存的是索引列的值加上主键值。走二级索引查数据的时候MySQL先到二级索引的B树上找到主键再拿着主键回聚簇索引查整行这个过程叫回表。举个例子一张用户表有id、name、age三个字段你在name上建了索引。执行select * from user where name 张三的时候MySQL先在name这棵B树里找到张三对应的主键id再回到主键索引树里读这一行的完整数据。这一步回表消耗的是随机IO代价比顺序IO高不少。那联合索引又是什么联合索引是多个字段一起建索引排序规则是先按第一个字段排第一个字段相同再按第二个字段排以此类推。这就是为什么联合索引必须遵循最左前缀原则——你去查where b ?而索引是(a,b,c)MySQL在B树里根本没法直接定位因为第一层排序维度是a你跳过a直接用b查整个索引树的剪枝策略就失效了。弄懂这些之后索引失效的本质也就清楚了所谓失效就是MySQL优化器评估之后觉得走这个索引的成本比全表扫描还高或者SQL的写法导致B树无法按有序结构进行快速定位。两种情况一种是物理上没法走一种是逻辑上不值得走。这俩在排查的时候要分开看后面我会详细说。还有一个很多人忽略的概念基数。基数Cardinality指的是一列中不同值的个数。在name上建索引如果全表一万行名字只有张三和李四两种那这个索引的基数就是2区分度极低。MySQL优化器心里有杆秤走这种索引要回表几千次还不如直接全表扫描。所以有时候索引没失效但优化器主动放弃你explain看到的是type ALL别急着骂SQL先看看索引区分度。2. 实战中最高频的八种索引失效场景这一节是干货中的干货。我在实际工作里排查过的慢查询80%都逃不出下面这几个坑。每个场景我都会给示例、说原理、给解法你对照自己项目里的SQL检查就行。2.1 隐式类型转换最常见的隐形杀手这是排查慢查询时最容易撞见的问题隐蔽性极强。最常见的场景表里某个字段是varchar类型但你在查询条件里传了数字。-- user表中phone是varchar(20)索引建在phone上 select * from user where phone 13812345678;你以为你查的是字符串但MySQL看到等号右边是数字会自动把varchar列转成数值类型再比较。一旦对索引列做了类型转换索引就废了因为B树里存的是原始字符串没经过转换没法直接比较。同样的坑还有字符串和日期比较、字符集隐式转换比如utf8mb4和utf8比较有时会出现排序规则不兼容导致索引失效等。排查技巧执行explain看type是不是ALL再看Extra里有没有Using where如果有可疑就试试把SQL改成显式传字符串再跑一遍对比执行计划。select * from user where phone 13812345678;这条就是正确的写法。经验之谈所有查询条件里的值尽量跟字段类型严格对齐。前端传参、接口定义、框架的自动类型转换每一层都可能是隐患最好是代码里统一做类型校验。2.2 对索引列使用函数或表达式计算这几乎是教科书级别的禁令但工作中依然有人踩。原因很容易理解B树的叶子节点存的是字段的原始值如果对字段套了函数MySQL需要对每一行的字段值先做函数运算再拿结果去匹配。这个过程没法走索引的有序查找只能全表扫描。select * from user where DATE(create_time) 2024-05-15; select * from user where YEAR(create_time) 2024;第一个SQL的意图是查5月15日创建的用户正确写法应该是范围查询select * from user where create_time 2024-05-15 00:00:00 and create_time 2024-05-16 00:00:00;这样写create_time就不用套函数索引能直接用上。第二个SQL如果想按年份筛选建议在表里加一个year字段或者生成列MySQL 5.7支持。另外一个容易被忽视的点是表达式运算比如where price * 2 200。这种对索引列做算术运算的写法一样会导致失效。解决办法是把表达式移到等号右侧where price 200 / 2。规则是索引列单独出现在比较符的一侧不要有任何函数或运算包裹。2.3 前导模糊查询%开头的LIKEwhere name like %张%这条我几乎每轮面试都会问也是实际业务里最常见的模糊搜索写法。原理很简单B树是按顺序排列的查询时利用的是前缀匹配能力。你给的条件是以任意字符开头中间包含张MySQL没法用索引树从根节点快速定位第一条符合条件的数据只能扫描所有叶子节点。但不代表LIKE一定不能用索引。下面这几种情况就能走-- 右模糊可以用索引 select * from user where name like 张%; -- 左模糊后面条件是等值匹配联合索引可以局部利用 select * from user where name like %张 and age 18;第二种情况如果联合索引是(name, age)MySQL可以用age的等值条件去定位前导模糊不影响部分索引使用。但单独就%张这种写法基本放弃治疗。业务上真要支持任意位置的模糊搜索方案无非几种用全文索引FULLTEXT、用ES等搜索引擎、拆词存冗余字段。小表直接全表扫也就几十毫秒别过度设计大表就得从架构层面解决而不是跟SQL死磕。提示查explain如果看到type ALL且SQL里有LIKE优先怀疑前导%。还有一种情况like 张%能走索引但优化器评估回表成本太高也可能不走属于下一节的范围。2.4 OR连接条件一个不走全部遭殃OR是另一个高频刺客。很多人以为where a 1 or b 2只要a和b都有索引就能快速查实际上MySQL对OR的处理是只要有一个条件无法使用索引整个查询就可能变全表扫描。因为OR的语义是结果的并集MySQL需要一个统一的执行方案。-- a有索引, b没有索引 select * from user where a 1 or b 2; -- 上面这条,哪怕a有索引,也可能全表扫正确打开方式是拆成两条SQL用UNION合并select * from user where a 1 union all select * from user where b 2;但注意这里的union all可能产生重复数据需要根据业务确认。如果两边结果集理论上不重叠相当于互斥条件union all效率更高不放心就用union去重代价是多一次排序去重大表慎用。另外还有一种OR失效的情况OR两边的列都在联合索引里但顺序不对。比如索引(a,b)查询where a 1 or b 2OR条件下MySQL没法保证两段分别走索引的最优路径通常也会转为全表扫描。MySQL 8.0的优化器对OR的处理能力比5.7强不少但也不能完全指望它。实战中遇到OR慢查询第一反应就应该是改写为union或IN。2.5 联合索引违反最左前缀法则这是面试爱问、工作中最爱犯的一类问题。联合索引(a,b,c)查询条件必须从a开始匹配才有机会走索引。你写where b 2 and c 3完全没有a索引从第一个维度就无法定位直接失效。但有人会杠我的SQL明明按照最左前缀写了为什么还是不走索引这里有三个容易忽略的细节。第一个是顺序问题。where b 2 and a 1MySQL优化器会调整条件顺序这个其实是能走索引的。真正的问题是缺列。第二个是范围查询的截断效应。索引(a,b,c)查询where a 1 and b 5 and c 3。这时候a是等值匹配可以用索引定位b是范围查询B树在b这个维度上进入范围扫描c的等值条件没法继续走索引了因为b的范围导致c在索引中的排序不再连续。现象是explain里key_len只用到b的长度c没用上。解决方案是索引设计时把范围查询字段放在联合索引的后面建索引(b, a, c)或者(a, c, b)视具体高频查询而定。第三个是排序和分组字段。where a 1 order by c如果索引是(a,b,c)虽然a等值匹配了索引但order by c没法利用索引完成排序因为中间隔着b。MySQL要么在内存临时表排序Using filesort要么放弃索引。这也是索引失效的变种——索引能定位但不能避免排序性能依旧拉胯。2.6 IS NULL或IS NOT NULL的坑很多人以为索引列IS NULL走不了索引这个结论得看情况。到底是全NULL还是全是NULL决定了优化器的选择。如果一个表一百万行某索引列90%都是NULL那你查where col IS NULL优化器会觉得还不如全表扫因为走索引也差不多要访问90%的数据。反过来如果NULL值很少IS NULL是能走索引的。更多的坑出现在IS NOT NULL上。如果这个列大部分值不为NULL那么IS NOT NULL本质上要访问几乎所有行走索引回表成本更高优化器直接全表扫。实操层面的建议业务字段尽量避免允许NULL用默认值代替、0等建表时NOT NULL DEFAULT。有NULL的列is null要验证执行计划别想当然。2.7 !、、NOT IN 的负向查询负向查询能不能走索引同样取决于数据分布。where status ! 1如果你这个表99%的status都是1那查剩下的1%虽然用索引能精确定位到那2万行但回表2万次对优化器来说还不如全表扫一遍100万行。所以这种SQL有时候走索引有时候不走核心是区分度。NOT IN也有类似问题而且它还有一个潜在优化点如果子查询的结果集很小改写成LEFT JOIN往往更高效。说句公道话!和NOT IN不是绝对不能走索引MySQL 5.6在特定条件下是会用的。但你要知道负向查询天然不利于索引快速定位尽可能用正向查询表达。2.8 字符集与排序规则不统一这个坑比较隐蔽一般出现在多表关联或子查询嵌套场景里。如果两张表的相同含义字段一张是utf8mb4一张是utf8甚至有的老库是latin1join的时候MySQL需要把一边的字符集转换成另一边的转换操作会导致join列上的索引失效。我之前排查过一个案例订单表order.user_id是utf8mb4用户表user.id是utf8老表两个表join查数据user表虽然id有主键索引但explain显示join类型是ALL用户表全表扫几万行的小表还好等数据量到了一百万直接卡死。解决办法很粗暴统一所有库表字符集最好是utf8mb4——它兼容utf8还能存emoji。建库建表时保持CHARSET一致排序规则collation也要一致比如都是utf8mb4_general_ci。提示必要时可以用CONVERT(id USING utf8mb4)强制转换但这会让索引失效只适合临时救急不能长期依赖。3. 优化器的小算盘有时候不是SQL的错前面讲的场景大多是SQL写法问题但还有一种更隐蔽的情况SQL看起来完全规范索引也建得没毛病可执行计划就是扫全表。这时候要怀疑的不是SQL而是数据本身和优化器的判断逻辑。3.1 统计信息过期导致估算错误InnoDB通过采样统计索引的基数和数据分布这些统计信息不是实时的是定期更新的通过analyze table或后台自动触发。如果你表的数据量剧烈变化比如大批量导数据、delete大量行统计信息没跟上优化器可能基于旧数据做了错误判断选了一条不理想的执行路径。解决方式大操作后手动执行analyze table 表名;刷新统计信息。很多DBA发布的运维规范里都有这条。3.2 回表成本压倒一切记住这个公式逻辑走索引 索引查找成本 回表次数 × 单行回表成本。如果索引列区分度低要回表的行数占总行数比例高优化器毫不犹豫选全表扫描因为顺序IO比随机IO快得多。这类问题的核心解法不是改SQL而是优化索引设计尽量用覆盖索引让查询的字段全部包含在索引里避免回表。比如select name, age from user where name 张三如果建了(name, age)联合索引Extra里显示Using index这一步省掉了大量回表IO。大字段别往索引里塞比如text、长varchar索引会膨胀且排序成本高。高频查询考虑索引下推ICPMySQL 5.6默认开启能让WHERE条件在索引层面先过滤减少回表。3.3 LIMIT与ORDER BY的组合误导order by xx limit n是经典分页场景但很多人不知道limit并不总是帮助SQL变快它可能让优化器选错方案。举个例子select * from user order by age limit 10如果age上有索引优化器可能觉得走索引顺序读前10行很快但它忽略了还要回表读整行——如果age索引区分度低前1000行都落在同一个age值上回表成本瞬间暴涨还不如全表扫filesort。遇到分页越深越慢的问题limit 100000, 10常规解法是延迟关联select u.* from user u inner join (select id from user order by age limit 100000, 10) tmp on u.id tmp.id;子查询里只查索引列id和排序字段age走的是索引覆盖扫描不需要回表外层再根据id批量回表取整行数据只回表10次量级完全不一样。3.4 优化器开关与参数MySQL提供了一些开关控制优化行为。optimizer_switch里有个index_condition_pushdownICP一般默认开启但有些云厂商的默认参数可能不一样。还有optimizer_switch use_invisible_indexeson可以临时让隐藏索引生效用于测试。sql_mode和eq_range_index_dive_limit这种参数也不会直接影响索引失效但可能影响选择。遇到极其反常的执行计划时可以用explain formatjson配合optimizer_trace看优化器内部的成本计算过程这个后面排查篇细说。4. 排查与验证从EXPLAIN到Optimizer Trace很多新手学会了建索引但不会验证索引到底有没有被用上。这一节我直接把排查流程和实操经验给出来你按步骤操作基本能定位95%的索引失效问题。4.1 EXPLAIN的正确打开方式执行explain select ...重点看这几列type从好到差大致是system const eq_ref ref range index ALL。看到ALL就要警惕这是全表扫描看到index也不一定好它代表全索引扫描遍历整个索引树比ALL好一点但仍是扫描。key实际使用到的索引名。如果为NULL说明这条SQL没用索引。key_len使用的索引长度。联合索引场景特别有用如果你用了两个条件key_len只相当于一列的长度说明第二列没生效。rows优化器预估需要扫描的行数。这个数字异常大就说明有问题。Extra有很多信号值。Using index表示覆盖索引爽Using where配合typeALL大概率全表扫Using filesort表示排序没用上索引Using temporary表示用了临时表大查询要警惕Using index condition表示ICP下推5.6的正常表现。实操建议排查SQL时先看type和rows再看Extra。type是ALL且rows和表总行数差不多基本就是索引失效。注意explain的结果是基于优化器估算的不是实际执行值。8.0里可以用EXPLAIN ANALYZE看到真实的执行耗时和行数这个比只看计划靠谱得多。4.2 用SHOW WARNINGS看优化器改写MySQL优化器在真正执行前会做一些查询改写。有时候你写的SQL没问题但优化器为了统一处理偷偷改了一个函数或转换。用explain extended或者show warnings能看到优化器改写后的SQL。比如你写了隐式类型转换的SQLshow warnings里会显示类似cast(phone as double)的字样这样就实锤了索引失效的原因。这是我强烈推荐的排查手段比对着规则猜要快得多。4.3 追踪优化器决策过程MySQL 8.0的optimizer_trace可以查看优化器做了哪些成本计算、为什么选择全表扫描。开启方式set session optimizer_traceenabledon; select * from user where phone 13812345678; select trace from information_schema.optimizer_trace\Gtrace输出很长重点关注considered_execution_plans和rows_estimation部分你能看到优化器估算的走索引行数、回表成本以及最终选择的原因。这个工具比较高端一般只有遇到很难用常识解释的优化器行为时才会用。比如你觉得明明索引区分度很高但就不走索引——这时候用trace看一下可能是统计信息过期或者成本模型有偏差。4.4 慢查询日志和sys库配合定位如果光看单条SQL不够还需要从全局视角找到高频慢SQL-- 查看慢查询日志配置 show variables like slow_query%; -- 开启慢查询 set global slow_query_log ON; set global long_query_time 1;开启后把慢查询日志里的SQL捞出来逐一用explain排查。另外MySQL自带的sys库里有一些好用的视图-- 没有被使用的索引 select * from sys.schema_unused_indexes; -- 冗余索引检测 select * from sys.schema_redundant_indexes; -- 全表扫描的表 select * from sys.statements_with_full_table_scans;这三个视图能帮你从宏观角度发现索引问题schema_unused_indexes告诉你哪些索引建了但没被用过该删就删别让写性能白亏schema_redundant_indexes告诉你有没有跟现有索引重复的索引比如(a,b)和(a)基本重复后者纯属浪费空间和写入成本。4.5 常见索引失效问题速查表现象可能原因验证方法解决办法typeALLSQL含隐式类型转换条件值类型与字段不一致查询SHOW WARNINGS看cast提示条件值显式匹配字段类型typeALLWHERE列套函数索引列被函数包裹EXPLAIN看Extra改范围查询或拆分条件typeALLLIKE前导%无法用前缀匹配定位执行计划确认全文索引/ES/改右模糊索引有但不用OR条件有分支不能走索引EXPLAIN确认拆UNION ALL联合索引只用了一部分违反最左前缀或范围截断看key_len是否偏短调整索引顺序/拆分索引大表查询反直觉统计信息过期ANALYZE后重试EXPLAIN手动更新统计信息索引覆盖不完整查询列超出索引范围看Extra是否有Using index建覆盖索引排序后有文件排序ORDER BY列不在索引中或顺序不符Extra出现Using filesort调整索引列顺序承担排序5. 建索引与改SQL的实用清单踩过足够多的坑之后我总结了一套比较实用的规则分享给你。建索引五问第一这个查询的高频条件是什么提取出所有等值查询、范围查询、排序、分组字段。第二哪个查询是业务核心优先为最高频、最敏感的查询设计联合索引不要人为了一两个低频查询建一堆索引。第三联合索引字段顺序怎么排区分度从高到低放前面范围查询放后面这是基本原则但也要看实际查询分布。第四能不能做到覆盖索引高频查询的select字段能塞进索引就尽量塞。第五这个索引值不值得建写入频繁的表索引过多会拖慢insert、update需要在读写之间权衡。改SQL五招条件值严格匹配字段类型杜绝隐式类型转换。不在索引列上做函数、运算、拼接。范围查询优先写成区间形式、、between and别写函数提取。OR改写UNIONNOT IN改写关联或反向条件。order by和group by字段尽量和where条件构成联合索引。6. 我个人在实际排查中的几点体会最后分享三个我自己的习惯算不上什么高深技巧但真的能省很多事。第一个习惯是每次改完SQL或索引都立刻跑一遍执行计划并记录前后对比。我见过太多人在Navicat里点一下explain看到type变成ref就完事了结果上线后实际执行依然慢。原因很简单explain是估算的死数据的统计信息可能不准。真正稳妥的做法是用explain analyze8.0或者直接把SQL执行一遍看耗时。执行计划前后对比至少保留到项目上线一周后万一性能波动可以翻出来复核。第二个习惯是定期清理冗余索引。用sys库的视图每个月跑一遍把重复索引、无用索引列出来拿到群里跟同事确认后删除。索引不是越多越好每次写操作都要更新所有索引三个冗余索引能把写入性能拖掉20%。第三个习惯是给联合索引列顺序写注释。建索引的SQL后面随手加一行注释说明这个列顺序是按xx业务查询设计的不要随便调整。不然半年后新同事看不懂当初为什么这么建为了一个临时需求加了一个普通索引反而影响高频SQL走原来的联合索引。索引失效这个问题看着是几十个碎片化的知识点但内核只有一句话索引是有序的数据结构你写的SQL要么打破了有序定位要么让优化器觉得走有序定位不划算。把每个案例归到这两类里你的排查思路就会清晰很多。
