直接开始聊聊索引失效这件事。做后端开发的就没有不被索引失效坑过的。明明查询走了索引结果一夜之间接口变慢或者你可能都不知道那条慢SQL压根儿没用上索引——等DBA找上门的时候数据量已经涨到几千万再想改代价就不是几分钟能搞定的了。我写这篇文章就是想把MySQL里常见的索引失效原因系统地捋一遍。不说教科书式的概念就讲实际情况里大家会遇到的坑、排查的思路以及怎么预判“这个SQL到底会不会走索引”。文章里不会只列现象我会把每种场景的机制讲透再给上可直接验证的样例SQL帮你彻底避开那些隐藏的坑。适合谁看呢正在调优老项目的、准备面试聊SQL优化的、还有被慢查询日志折磨过的这篇都能派上用场。1. 索引生效的底层预判先搞懂“走不走索引”的决定因素很多新手上来就背“最左前缀、不要用函数、不要隐性转换”背得很熟但一到实际场景就懵。原因很简单只记住了结论没理解MySQL到底是怎么选路的。索引失效不是说索引突然“坏了”而是查询优化器觉得“用索引还不如全表扫”或者你的SQL写法让索引“够不着”目标数据。想真正掌握索引失效的排查能力一定得先建立两个认知基础。1.1 优化器的成本估算逻辑MySQL在执行一条SQL之前会交给优化器做一件事从可能的执行路径里选一条成本最低的。这个“成本”不是玄学是基于行数、数据分布、IO次数等做的一套估算模型。优化器手里有一张表的信息——行数、索引选择性、数据页缓存情况——它会根据这些数字去判断走索引回表的开销大还是扫全表的开销大。举个例子你查一个字段而这个字段只有两个值男、女。优化器一看我猜你要么查男要么查女每个都查出来一半的行那我还不如直接扫全表呢还扫得痛快。这就是为什么“低选择性的列建了索引也常被放弃”。说白了索引本身没问题是优化器觉得不划算。这也是为什么同样的SQL表中数据量从10万涨到1000万后执行计划会突然变化。因为随着数据量变大索引扫描的IO成本在全表扫描面前已经开始有竞争力了。你会在实战中发现有些SQL在测试环境飞快上了生产就慢得像蜗牛——不是代码变了是数据规模变了优化器的选择就变了。1.2 回表成本索引不是万能的辅助索引也叫二级索引、非聚簇索引的叶子节点存的是索引列值加上主键值。当你用辅助索引查数据时如果需要的列不在索引里就得拿着主键再到主键索引聚簇索引里找一次完整的行——这个动作就叫回表。回表一次两次还好如果命中了上万行就要做上万次随机IO成本迅速飙升。这也是很多时候“索引明明可用优化器却选了全表扫描”的重要原因。理解了这个你就能看懂后面很多场景比如为什么select *在某些情况下比只查索引列更容易索引失效为什么覆盖索引这么推荐。因为只要查询列全部包含在索引里就压根不需要回表索引成本大大降低优化器自然就更愿意走索引。这背后的一切都是成本和收益的权衡。2. 八种常见的索引失效场景与原理剖析这一节是文章的核心我按照实际开发中最容易踩的顺序来排。每一种都会给原理、样例和避坑方法。2.1 违反最左前缀原则联合索引a, b, c在B树里是先按a排序a相同再按b排序b相同再按c排序。所以你的查询条件如果不包含最左列a索引在这个查询里基本没有用武之地。我自己在实际开发里见过最无语的一种情况就是同事建了一个联合索引查的时候却只用了后面的列还跑来问索引怎么不起效。比如索引是idx_shop_status (shop_id, status)SQL却是WHERE status 1这就完全用不上这个联合索引。但这里有两个容易判断错的细节。第一WHERE a 1 AND c 3能用索引吗能但只用到a这一列c用不上。因为中间隔了b索引无法直接跳到c有序的位置。MySQL 8.0有索引跳跃扫描Index Skip Scan在特定条件下能弥补这个场景但那是“特定条件”不能当成常规依赖。第二WHERE a 1 AND b 5 AND c 3这里c也是用不上的。范围查询右边的列会中断索引——因为b 5是个范围在这一范围内c字段是无序的没法继续用索引定位。这个细节非常经典面试也爱考实际开发也最容易被忽略。避坑方法建联合索引之前先梳理业务查询中最常用的一组等值条件把等值列放前面范围列放后面。同时如果无法覆盖所有查询组合那就要在“最常用的组合”与“覆盖更多场景的组合”之间做取舍不能贪多求全。2.2 隐式类型转换字段类型和传入参数类型不一致时MySQL会把其中一个转换成另一个再比较。问题就出在这个“转换”上——如果转换发生在索引列这一侧索引就失效了。最典型的例子表字段user_id是varcharSQL写WHERE user_id 123456数字MySQL会把字符串列转成数字再比较等价于对索引列做了CAST(user_id AS SIGNED)函数一上索引就废了。类似的情况还有字段是datetime你传了字符串字段是char你传了null然后拼接等。凡是索引列参与类型转换的基本都逃不掉失效的命运。一个常见的误解是认为“字符串转数字没关系MySQL会处理的”。MySQL确实会处理但代价是放弃索引。如果你数据量不大可能感觉不明显数据量上来了这条SQL就是全表扫描的慢SQL早晚会出现在慢查询日志里。避坑方法核对参数类型和字段类型。拿不准的时候把SQL里的参数类型显式转换比如WHERE user_id 123456让类型对上索引就能正常用上。注意WHERE user_id CAST(123456 AS CHAR)这种把参数转成字符串的写法是可以的因为转换发生在参数一侧不影响索引列。搞清楚“转换发生在哪一侧”才是这个问题的关键。2.3 索引列使用函数或表达式这和隐式类型转换是同一个大类但更隐蔽。因为很多时候你并没有刻意写函数只是一个计算条件。比如订单表有个pay_time字段你想查最近7天的订单很自然地写了WHERE DATE(pay_time) CURDATE() - INTERVAL 7 DAY。这属于在索引列上套了函数索引失效。正确写法是WHERE pay_time CURDATE() - INTERVAL 7 DAY AND pay_time CURDATE() INTERVAL 1 DAY让索引列保持原状函数都放到参数一侧。还有更隐蔽的WHERE price 1 100对索引列做了表达式运算同样失效WHERE LEFT(name, 1) 张函数作用于索引列也失效。我在实际排查里见过一个特别典型的一个订单查询列表按order_time倒序本来该走得很好。结果某天需求加了“按创建时间和更新时间取较近的那个”有人直接在WHERE里写GREATEST(create_time, update_time) 2024-01-01。就这一个改动接口从几十毫秒变成了几秒钟。避坑方法把所有需要加工的逻辑要么挪到参数侧要么在写入时就冗余出需要的字段并加索引要么构建表达式索引。MySQL 8.0开始支持函数索引可以ALTER TABLE ADD INDEX idx_func ((DATE(pay_time)))MySQL 5.7及以下就别想了老老实实改SQL或者加冗余字段。2.4 LIKE模糊查询的边界问题很多文章的结论是“like %keyword%会失效like keyword%不会”。这句话大致对但没说为什么也没说边界情况。B树索引是有序的所以它能做的是“范围定位”。like abc%本质上是定位到abc开头这个范围从abc到abd之前这个范围在索引里是连续的一小段可以被索引快速找到。但like %abc或者like %abc%开头就是通配符你不知道匹配从哪开始索引无从定位只能全表扫。边界情况like abc%def能用索引吗能。因为前缀是确定值abcMySQL能先根据abc把范围锁定在锁定的范围内再过滤%def这个后缀。这里前缀部分的索引是能发挥作用的这也是很多人容易误判的点。避坑方法业务允许的话尽量保证模糊匹配的字符串是“前缀定值”。实在需要做全文搜索的比如搜索商品名中的关键词直接用全文索引别硬用LIKE。2.5 使用OR连接条件OR这个老生常谈的问题很多人知其然不知其所以然。其实核心原因在于WHERE a 1 OR b 2MySQL可能有两种选择——走a的索引得到一批rowid再走b的索引得到另一批rowid最后合并去重。这在MySQL里叫索引合并Index Merge是可行的但前提是a和b都有独立索引。但这里有个执行计划里的陷阱即使走了索引合并代价也不一定比全表扫低。如果两个条件中有一个是低选择性字段合并后要处理的行数可能超过全表的百分之二三十优化器就会选择干脆扫全表。还有一种情况也常见WHERE a 1 OR a 2这种写法本身没问题但如果a列没有索引那就直接全表扫了。另外如果OR连接的两个条件里有一个条件涉及到的列没有索引那整个查询基本就走不了索引了——因为索引合并要求两路都有索引可用。避坑方法优先把OR改成UNION ALL前提是结果集明确无重复或有去重逻辑可控或者将多个查询条件改写成IN列表。在开发时多留意执行计划如果看到type: ALL且SQL里有OR大概率就是踩了这个坑。2.6 字段编码不一致导致的隐式转换这个场景最常见于多表关联——两个表join字段都是varchar但一个表的字段是utf8mb4另一个表是utf8或者utf8mb4_general_ci和utf8mb4_unicode_ci这种排序规则不同MySQL必须先把其中一个字段转成另一个的编码格式才能比较。这个转换同样是发生在索引列上的关联字段的索引就会失效直接导致关联查询变慢而且这个慢会随着数据量增长急剧放大。举个例子订单表和用户表joinorders.user_id是utf8mb4users.id是utf8关联条件ON orders.user_id users.id。MySQL会将users.id转成utf8mb4去比较。如果users.id是主键主键索引在这个关联里就废了MySQL只能对users表做全表扫描。避坑方法在设计表结构时就统一字符集和排序规则数据库连接串的characterEncoding也保持一致。老项目改起来成本高的话优先修改小表或者被驱动表的那一侧让它的字符集向另一侧看齐。提示在排查多表join变慢的问题时用EXPLAIN看关联的驱动表和被驱动表再检查两张表的关键字段字符集。这个细节经常被忽略但往往是性能瓶颈的真正根源。2.7 使用IS NOT NULL或对NULL做判断这个坑分两种情况。IS NULL有时候能用索引看优化器心情和数据分布IS NOT NULL绝大多数情况是没法有效利用索引的因为优化器假设大部分行都是非NULL那扫索引跟扫全表没差。更常见的是开发时用了WHERE column IS NOT NULL或者WHERE column ! 以为数据库会聪明地走索引其实直接在扫全表。尤其在字段值大部分都满足条件的情况下全表扫描反而是最优解。避坑方法能用默认值替代NULL的就用默认值比如状态字段默认0避免在代码里到处写IS NOT NULL来判断“有值”的情况。如果业务上就是需要判断NULL可以考虑配合IS NULL一起用走索引的可能性会高一些。2.8 数据分布不均的“优化器预判失效”最后一个场景也是最玄学的SQL写法完全标准索引设计也没毛病但还是偶尔不走索引。这通常和统计信息有关。MySQL的优化器依赖表的统计信息行数、索引基数等来预估成本。如果你的表很久没做ANALYZE TABLE统计信息陈旧优化器就会基于错误信息做决策明明该走索引的它算出来觉得走全表更快。还有一种情况是字段的选择性极差比如一个状态字段只有0和1两个值分布又是99:1。当你查那个占比极小的值时优化器有可能会正确走索引但当你查那1%再反转过来时就可能判断错误。这种是基于数据分布的“合理失效”不算错误但非常容易让人困惑。避坑方法定期对大表执行ANALYZE TABLE尤其在大批量数据变更之后。同时不要迷信“建了索引就该用”要学会用FORCE INDEX做临时验证看看强制走索引和默认选择之间到底差多少能帮你判断到底是优化器的问题还是SQL写法的问题。3. 一次真实的索引失效排查实战光说不练假把式我拿一个真实案例来复盘整个排查过程你能看到从现象到定位再到解决的全链路。3.1 问题现象一个管理后台的订单查询接口数据量在300万左右某天突然变慢从平均200ms涨到3秒多。接口的查询条件是店铺ID、订单状态、下单时间范围支持分页。表结构简化后大概是这样CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, shop_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, pay_time datetime DEFAULT NULL, buyer_id bigint NOT NULL, total_amount decimal(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_shop_status_time (shop_id, status, pay_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;看一眼索引设计似乎没毛病三列联合索引覆盖了查询条件。代码里SQL大概是WHERE shop_id ? AND status ? AND pay_time ? AND pay_time ?。3.2 排查过程先EXPLAIN一把发现type是ALL驱动表直接全表扫描索引完全没用上。第一反应是查数据分布。这个表里有几个店铺的数据量特别大占了全表80%以上。优化器估算扫这几个店铺的行数就超过了几十万行直接全表扫反而可能更划算。这是数据的锅不是SQL的锅。确认了这一点后又看了下统计信息发现最近有一次大批量数据导入统计信息可能没更新。执行ANALYZE TABLE t_order;之后再用EXPLAIN看索引已经能正常命中了。3.3 深层原因与修复表面看是统计信息过期但再往下挖一层发现真正的问题在于查询条件里pay_time的范围查询会让联合索引的后续条件失效吗不会——这里shop_id和status都是等值条件pay_time是范围条件索引完全能覆盖三层。问题只出现在统计信息偏差上。优化器以为走索引要扫描几十万行再回表实际上回表行数远小于这个数因为status字段的选择性在特定店铺下很高。统计信息更新后优化器发现了这个规律执行计划就恢复正常了。修复之后我在代码层还顺手做了个优化把SELECT *改成了只查必要的列让查询直接走覆盖索引尽量避免回表。这一步对接口性能也有稳定帮助。3.4 这次排查里最有价值的经验这个案例让我印象最深的一点是不要一看到索引失效就急着改SQL先看执行计划再看统计信息最后再改代码。有时候一条ANALYZE TABLE就能解决的问题被开发改了几版SQL都没找到根源。另外排查过程中我习惯用一个固定动作分步验证。先把SQL改成最基础的等值条件看索引走不走再往里面加一个条件看索引还走不走逐层定位是哪个条件的加入导致了执行计划的变化。这种方式在面对复杂查询时非常高效。4. 索引使用优化的经验清单与设计建议把前面的案例和原理总结成一套可以快速落地的操作清单方便你在新代码上线前做一次自查也方便在慢SQL出现时按图索骥。4.1 5条自查清单写SQL前过一遍联合索引的查询条件里第一个等值列是否在最左前缀参数类型和字段类型是否完全匹配隐式类型转换索引列有没有被函数、计算、隐式转换包裹函数操作模糊查询的前缀是不是确定值有没有用OR连接条件且两边都有索引join关联字段的字符集和排序规则是否一致这五条如果能形成肌肉记忆大多数索引失效的问题在代码评审阶段就能被拦截下来。我的习惯是在每次写完一条稍微复杂的查询后顺手用EXPLAIN看一眼执行计划养成这个习惯之后慢SQL数量会明显下降。4.2 业务设计层面上怎么减少索引失效除了写SQL时注意规则业务设计上也有一些规避思路。一是反范式冗余。很多时候查询条件需要的字段分布在多张表里硬要join就会面临字符集不一致、关联字段没索引、优化器选错驱动表等一堆问题。不如把常用的查询字段冗余到一张宽表里加好索引查询就变成一个单表简单查询稳定性高得多这也是目前很多大数据方案里常见的建模思路。二是用覆盖索引保底。设计的查询尽量把返回列也包含在索引里让MySQL可以直接读索引返回结果不回表。比如查询店铺订单列表只需要返回id、shop_id、status、pay_time这几个字段那联合索引(shop_id, status, pay_time)就足够覆盖了怎么查都快。三是对大字段或超宽表的处理。如果表里有text字段或者列特别多查询尽量只取必要列把大字段拆到附属表。不仅减少回表开销还能降低索引页的缓存压力。很多时候索引失效不是索引的问题而是查询列太宽回表成本超出优化器容忍范围。4.3 关于索引失效排查的实战建议先说工具。MySQL 8.0里EXPLAIN ANALYZE很好用它直接告诉你每一步实际执行时间和行数比看估算值直观得多。遇到可疑SQL我一般先跑一遍EXPLAIN ANALYZE哪个环节行数爆炸立刻就能看到。再就是慢查询日志。如果线上出现性能问题直接捞慢SQL用工具分析。打开slow_query_log设置long_query_time为1秒对业务影响很小长期开着不亏。最后处理索引失效问题时务必要用生产数据量做验证。测试环境几千行数据什么SQL都快根本暴露不了问题。我的做法是定期从生产环境脱敏数据后同步到预发环境特别是那些大表这样在预发阶段就能提前发现索引失效的隐患。根据我个人的排查经验索引失效这件事最大的成本其实不是“建索引”而是“发现索引没生效”的时间成本。如果你能在写SQL和执行SQL这两个阶段都把好关线上的慢查询基本就能控制在一个很低的水平。哪怕真的出了问题按照EXPLAIN→统计信息→SQL改写这条路径走一遍大多数情况半小时内都能定位到根因。
直接开始聊聊索引失效这件事。做后端开发的就没有不被索引失效坑过的。明明查询走了索引结果一夜之间接口变慢或者你可能都不知道那条慢SQL压根儿没用上索引——等DBA找上门的时候数据量已经涨到几千万再想改代价就不是几分钟能搞定的了。我写这篇文章就是想把MySQL里常见的索引失效原因系统地捋一遍。不说教科书式的概念就讲实际情况里大家会遇到的坑、排查的思路以及怎么预判“这个SQL到底会不会走索引”。文章里不会只列现象我会把每种场景的机制讲透再给上可直接验证的样例SQL帮你彻底避开那些隐藏的坑。适合谁看呢正在调优老项目的、准备面试聊SQL优化的、还有被慢查询日志折磨过的这篇都能派上用场。1. 索引生效的底层预判先搞懂“走不走索引”的决定因素很多新手上来就背“最左前缀、不要用函数、不要隐性转换”背得很熟但一到实际场景就懵。原因很简单只记住了结论没理解MySQL到底是怎么选路的。索引失效不是说索引突然“坏了”而是查询优化器觉得“用索引还不如全表扫”或者你的SQL写法让索引“够不着”目标数据。想真正掌握索引失效的排查能力一定得先建立两个认知基础。1.1 优化器的成本估算逻辑MySQL在执行一条SQL之前会交给优化器做一件事从可能的执行路径里选一条成本最低的。这个“成本”不是玄学是基于行数、数据分布、IO次数等做的一套估算模型。优化器手里有一张表的信息——行数、索引选择性、数据页缓存情况——它会根据这些数字去判断走索引回表的开销大还是扫全表的开销大。举个例子你查一个字段而这个字段只有两个值男、女。优化器一看我猜你要么查男要么查女每个都查出来一半的行那我还不如直接扫全表呢还扫得痛快。这就是为什么“低选择性的列建了索引也常被放弃”。说白了索引本身没问题是优化器觉得不划算。这也是为什么同样的SQL表中数据量从10万涨到1000万后执行计划会突然变化。因为随着数据量变大索引扫描的IO成本在全表扫描面前已经开始有竞争力了。你会在实战中发现有些SQL在测试环境飞快上了生产就慢得像蜗牛——不是代码变了是数据规模变了优化器的选择就变了。1.2 回表成本索引不是万能的辅助索引也叫二级索引、非聚簇索引的叶子节点存的是索引列值加上主键值。当你用辅助索引查数据时如果需要的列不在索引里就得拿着主键再到主键索引聚簇索引里找一次完整的行——这个动作就叫回表。回表一次两次还好如果命中了上万行就要做上万次随机IO成本迅速飙升。这也是很多时候“索引明明可用优化器却选了全表扫描”的重要原因。理解了这个你就能看懂后面很多场景比如为什么select *在某些情况下比只查索引列更容易索引失效为什么覆盖索引这么推荐。因为只要查询列全部包含在索引里就压根不需要回表索引成本大大降低优化器自然就更愿意走索引。这背后的一切都是成本和收益的权衡。2. 八种常见的索引失效场景与原理剖析这一节是文章的核心我按照实际开发中最容易踩的顺序来排。每一种都会给原理、样例和避坑方法。2.1 违反最左前缀原则联合索引a, b, c在B树里是先按a排序a相同再按b排序b相同再按c排序。所以你的查询条件如果不包含最左列a索引在这个查询里基本没有用武之地。我自己在实际开发里见过最无语的一种情况就是同事建了一个联合索引查的时候却只用了后面的列还跑来问索引怎么不起效。比如索引是idx_shop_status (shop_id, status)SQL却是WHERE status 1这就完全用不上这个联合索引。但这里有两个容易判断错的细节。第一WHERE a 1 AND c 3能用索引吗能但只用到a这一列c用不上。因为中间隔了b索引无法直接跳到c有序的位置。MySQL 8.0有索引跳跃扫描Index Skip Scan在特定条件下能弥补这个场景但那是“特定条件”不能当成常规依赖。第二WHERE a 1 AND b 5 AND c 3这里c也是用不上的。范围查询右边的列会中断索引——因为b 5是个范围在这一范围内c字段是无序的没法继续用索引定位。这个细节非常经典面试也爱考实际开发也最容易被忽略。避坑方法建联合索引之前先梳理业务查询中最常用的一组等值条件把等值列放前面范围列放后面。同时如果无法覆盖所有查询组合那就要在“最常用的组合”与“覆盖更多场景的组合”之间做取舍不能贪多求全。2.2 隐式类型转换字段类型和传入参数类型不一致时MySQL会把其中一个转换成另一个再比较。问题就出在这个“转换”上——如果转换发生在索引列这一侧索引就失效了。最典型的例子表字段user_id是varcharSQL写WHERE user_id 123456数字MySQL会把字符串列转成数字再比较等价于对索引列做了CAST(user_id AS SIGNED)函数一上索引就废了。类似的情况还有字段是datetime你传了字符串字段是char你传了null然后拼接等。凡是索引列参与类型转换的基本都逃不掉失效的命运。一个常见的误解是认为“字符串转数字没关系MySQL会处理的”。MySQL确实会处理但代价是放弃索引。如果你数据量不大可能感觉不明显数据量上来了这条SQL就是全表扫描的慢SQL早晚会出现在慢查询日志里。避坑方法核对参数类型和字段类型。拿不准的时候把SQL里的参数类型显式转换比如WHERE user_id 123456让类型对上索引就能正常用上。注意WHERE user_id CAST(123456 AS CHAR)这种把参数转成字符串的写法是可以的因为转换发生在参数一侧不影响索引列。搞清楚“转换发生在哪一侧”才是这个问题的关键。2.3 索引列使用函数或表达式这和隐式类型转换是同一个大类但更隐蔽。因为很多时候你并没有刻意写函数只是一个计算条件。比如订单表有个pay_time字段你想查最近7天的订单很自然地写了WHERE DATE(pay_time) CURDATE() - INTERVAL 7 DAY。这属于在索引列上套了函数索引失效。正确写法是WHERE pay_time CURDATE() - INTERVAL 7 DAY AND pay_time CURDATE() INTERVAL 1 DAY让索引列保持原状函数都放到参数一侧。还有更隐蔽的WHERE price 1 100对索引列做了表达式运算同样失效WHERE LEFT(name, 1) 张函数作用于索引列也失效。我在实际排查里见过一个特别典型的一个订单查询列表按order_time倒序本来该走得很好。结果某天需求加了“按创建时间和更新时间取较近的那个”有人直接在WHERE里写GREATEST(create_time, update_time) 2024-01-01。就这一个改动接口从几十毫秒变成了几秒钟。避坑方法把所有需要加工的逻辑要么挪到参数侧要么在写入时就冗余出需要的字段并加索引要么构建表达式索引。MySQL 8.0开始支持函数索引可以ALTER TABLE ADD INDEX idx_func ((DATE(pay_time)))MySQL 5.7及以下就别想了老老实实改SQL或者加冗余字段。2.4 LIKE模糊查询的边界问题很多文章的结论是“like %keyword%会失效like keyword%不会”。这句话大致对但没说为什么也没说边界情况。B树索引是有序的所以它能做的是“范围定位”。like abc%本质上是定位到abc开头这个范围从abc到abd之前这个范围在索引里是连续的一小段可以被索引快速找到。但like %abc或者like %abc%开头就是通配符你不知道匹配从哪开始索引无从定位只能全表扫。边界情况like abc%def能用索引吗能。因为前缀是确定值abcMySQL能先根据abc把范围锁定在锁定的范围内再过滤%def这个后缀。这里前缀部分的索引是能发挥作用的这也是很多人容易误判的点。避坑方法业务允许的话尽量保证模糊匹配的字符串是“前缀定值”。实在需要做全文搜索的比如搜索商品名中的关键词直接用全文索引别硬用LIKE。2.5 使用OR连接条件OR这个老生常谈的问题很多人知其然不知其所以然。其实核心原因在于WHERE a 1 OR b 2MySQL可能有两种选择——走a的索引得到一批rowid再走b的索引得到另一批rowid最后合并去重。这在MySQL里叫索引合并Index Merge是可行的但前提是a和b都有独立索引。但这里有个执行计划里的陷阱即使走了索引合并代价也不一定比全表扫低。如果两个条件中有一个是低选择性字段合并后要处理的行数可能超过全表的百分之二三十优化器就会选择干脆扫全表。还有一种情况也常见WHERE a 1 OR a 2这种写法本身没问题但如果a列没有索引那就直接全表扫了。另外如果OR连接的两个条件里有一个条件涉及到的列没有索引那整个查询基本就走不了索引了——因为索引合并要求两路都有索引可用。避坑方法优先把OR改成UNION ALL前提是结果集明确无重复或有去重逻辑可控或者将多个查询条件改写成IN列表。在开发时多留意执行计划如果看到type: ALL且SQL里有OR大概率就是踩了这个坑。2.6 字段编码不一致导致的隐式转换这个场景最常见于多表关联——两个表join字段都是varchar但一个表的字段是utf8mb4另一个表是utf8或者utf8mb4_general_ci和utf8mb4_unicode_ci这种排序规则不同MySQL必须先把其中一个字段转成另一个的编码格式才能比较。这个转换同样是发生在索引列上的关联字段的索引就会失效直接导致关联查询变慢而且这个慢会随着数据量增长急剧放大。举个例子订单表和用户表joinorders.user_id是utf8mb4users.id是utf8关联条件ON orders.user_id users.id。MySQL会将users.id转成utf8mb4去比较。如果users.id是主键主键索引在这个关联里就废了MySQL只能对users表做全表扫描。避坑方法在设计表结构时就统一字符集和排序规则数据库连接串的characterEncoding也保持一致。老项目改起来成本高的话优先修改小表或者被驱动表的那一侧让它的字符集向另一侧看齐。提示在排查多表join变慢的问题时用EXPLAIN看关联的驱动表和被驱动表再检查两张表的关键字段字符集。这个细节经常被忽略但往往是性能瓶颈的真正根源。2.7 使用IS NOT NULL或对NULL做判断这个坑分两种情况。IS NULL有时候能用索引看优化器心情和数据分布IS NOT NULL绝大多数情况是没法有效利用索引的因为优化器假设大部分行都是非NULL那扫索引跟扫全表没差。更常见的是开发时用了WHERE column IS NOT NULL或者WHERE column ! 以为数据库会聪明地走索引其实直接在扫全表。尤其在字段值大部分都满足条件的情况下全表扫描反而是最优解。避坑方法能用默认值替代NULL的就用默认值比如状态字段默认0避免在代码里到处写IS NOT NULL来判断“有值”的情况。如果业务上就是需要判断NULL可以考虑配合IS NULL一起用走索引的可能性会高一些。2.8 数据分布不均的“优化器预判失效”最后一个场景也是最玄学的SQL写法完全标准索引设计也没毛病但还是偶尔不走索引。这通常和统计信息有关。MySQL的优化器依赖表的统计信息行数、索引基数等来预估成本。如果你的表很久没做ANALYZE TABLE统计信息陈旧优化器就会基于错误信息做决策明明该走索引的它算出来觉得走全表更快。还有一种情况是字段的选择性极差比如一个状态字段只有0和1两个值分布又是99:1。当你查那个占比极小的值时优化器有可能会正确走索引但当你查那1%再反转过来时就可能判断错误。这种是基于数据分布的“合理失效”不算错误但非常容易让人困惑。避坑方法定期对大表执行ANALYZE TABLE尤其在大批量数据变更之后。同时不要迷信“建了索引就该用”要学会用FORCE INDEX做临时验证看看强制走索引和默认选择之间到底差多少能帮你判断到底是优化器的问题还是SQL写法的问题。3. 一次真实的索引失效排查实战光说不练假把式我拿一个真实案例来复盘整个排查过程你能看到从现象到定位再到解决的全链路。3.1 问题现象一个管理后台的订单查询接口数据量在300万左右某天突然变慢从平均200ms涨到3秒多。接口的查询条件是店铺ID、订单状态、下单时间范围支持分页。表结构简化后大概是这样CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, shop_id bigint NOT NULL, status tinyint NOT NULL DEFAULT 0, pay_time datetime DEFAULT NULL, buyer_id bigint NOT NULL, total_amount decimal(10,2) NOT NULL, PRIMARY KEY (id), KEY idx_shop_status_time (shop_id, status, pay_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;看一眼索引设计似乎没毛病三列联合索引覆盖了查询条件。代码里SQL大概是WHERE shop_id ? AND status ? AND pay_time ? AND pay_time ?。3.2 排查过程先EXPLAIN一把发现type是ALL驱动表直接全表扫描索引完全没用上。第一反应是查数据分布。这个表里有几个店铺的数据量特别大占了全表80%以上。优化器估算扫这几个店铺的行数就超过了几十万行直接全表扫反而可能更划算。这是数据的锅不是SQL的锅。确认了这一点后又看了下统计信息发现最近有一次大批量数据导入统计信息可能没更新。执行ANALYZE TABLE t_order;之后再用EXPLAIN看索引已经能正常命中了。3.3 深层原因与修复表面看是统计信息过期但再往下挖一层发现真正的问题在于查询条件里pay_time的范围查询会让联合索引的后续条件失效吗不会——这里shop_id和status都是等值条件pay_time是范围条件索引完全能覆盖三层。问题只出现在统计信息偏差上。优化器以为走索引要扫描几十万行再回表实际上回表行数远小于这个数因为status字段的选择性在特定店铺下很高。统计信息更新后优化器发现了这个规律执行计划就恢复正常了。修复之后我在代码层还顺手做了个优化把SELECT *改成了只查必要的列让查询直接走覆盖索引尽量避免回表。这一步对接口性能也有稳定帮助。3.4 这次排查里最有价值的经验这个案例让我印象最深的一点是不要一看到索引失效就急着改SQL先看执行计划再看统计信息最后再改代码。有时候一条ANALYZE TABLE就能解决的问题被开发改了几版SQL都没找到根源。另外排查过程中我习惯用一个固定动作分步验证。先把SQL改成最基础的等值条件看索引走不走再往里面加一个条件看索引还走不走逐层定位是哪个条件的加入导致了执行计划的变化。这种方式在面对复杂查询时非常高效。4. 索引使用优化的经验清单与设计建议把前面的案例和原理总结成一套可以快速落地的操作清单方便你在新代码上线前做一次自查也方便在慢SQL出现时按图索骥。4.1 5条自查清单写SQL前过一遍联合索引的查询条件里第一个等值列是否在最左前缀参数类型和字段类型是否完全匹配隐式类型转换索引列有没有被函数、计算、隐式转换包裹函数操作模糊查询的前缀是不是确定值有没有用OR连接条件且两边都有索引join关联字段的字符集和排序规则是否一致这五条如果能形成肌肉记忆大多数索引失效的问题在代码评审阶段就能被拦截下来。我的习惯是在每次写完一条稍微复杂的查询后顺手用EXPLAIN看一眼执行计划养成这个习惯之后慢SQL数量会明显下降。4.2 业务设计层面上怎么减少索引失效除了写SQL时注意规则业务设计上也有一些规避思路。一是反范式冗余。很多时候查询条件需要的字段分布在多张表里硬要join就会面临字符集不一致、关联字段没索引、优化器选错驱动表等一堆问题。不如把常用的查询字段冗余到一张宽表里加好索引查询就变成一个单表简单查询稳定性高得多这也是目前很多大数据方案里常见的建模思路。二是用覆盖索引保底。设计的查询尽量把返回列也包含在索引里让MySQL可以直接读索引返回结果不回表。比如查询店铺订单列表只需要返回id、shop_id、status、pay_time这几个字段那联合索引(shop_id, status, pay_time)就足够覆盖了怎么查都快。三是对大字段或超宽表的处理。如果表里有text字段或者列特别多查询尽量只取必要列把大字段拆到附属表。不仅减少回表开销还能降低索引页的缓存压力。很多时候索引失效不是索引的问题而是查询列太宽回表成本超出优化器容忍范围。4.3 关于索引失效排查的实战建议先说工具。MySQL 8.0里EXPLAIN ANALYZE很好用它直接告诉你每一步实际执行时间和行数比看估算值直观得多。遇到可疑SQL我一般先跑一遍EXPLAIN ANALYZE哪个环节行数爆炸立刻就能看到。再就是慢查询日志。如果线上出现性能问题直接捞慢SQL用工具分析。打开slow_query_log设置long_query_time为1秒对业务影响很小长期开着不亏。最后处理索引失效问题时务必要用生产数据量做验证。测试环境几千行数据什么SQL都快根本暴露不了问题。我的做法是定期从生产环境脱敏数据后同步到预发环境特别是那些大表这样在预发阶段就能提前发现索引失效的隐患。根据我个人的排查经验索引失效这件事最大的成本其实不是“建索引”而是“发现索引没生效”的时间成本。如果你能在写SQL和执行SQL这两个阶段都把好关线上的慢查询基本就能控制在一个很低的水平。哪怕真的出了问题按照EXPLAIN→统计信息→SQL改写这条路径走一遍大多数情况半小时内都能定位到根因。
