先分享一个真实排查经历前几天有个同事跑过来问说订单表里明明建了索引查询状态字段还是慢接口动不动就几百毫秒。我第一反应不是去看索引建没建而是先让他把执行计划发我。EXPLAIN一出来typeALLkeyNULL千万级的表在裸扫索引压根没被用上。这类问题我这些年踩过太多次了MySQL索引不是建了就万事大吉它有自己的成本判断和使用前提。索引明明建了查询还是慢通常不是缺索引而是SQL写法、隐式转换、数据分布、优化器选择这些隐藏的坑在作怪。这篇文章就把我从现象到定位再到解决的完整过程整理出来帮大家少走弯路。1. 索引明明建了却没用上先搞清楚MySQL的索引使用逻辑1.1 为什么优化器不选已建好的索引MySQL的InnoDB存储引擎中索引本质上是一棵B树。主键索引的叶子节点存的是整行数据二级索引的叶子节点存的是主键值。你建了一个普通索引不代表查询就一定走它因为真正做决定的是优化器它会根据统计信息、数据分布、回表成本、扫描行数等因素算出各种执行路径的费用然后选择它认为最低的那个方案。很多人对“建了索引”有个误区觉得索引就像一本书的目录只要建了查什么都应该翻目录。但MySQL不是这么工作的。优化器判断用不用索引会估算扫描行数和IO成本。如果你查询条件命中的行数占全表的比例太高比如一个字段只有两种值查其中一种命中一半数据这时候走二级索引需要不断回表取数据一次一次随机IO代价可能比直接全表顺序扫描还高。优化器算出这个结果后宁可选择全表扫描也不会用你那个索引。这也解释了为什么很多慢查询看起来索引列都在但执行计划里type是ALL。不是MySQL傻了而是它对成本做了计算觉得你这个索引在这种查询场景下并不划算。遇到这种情况急着加索引没用得先想清楚查询到底需要过滤掉多少数据以及怎样减少回表。1.2 回表与覆盖索引决定查询成本的关键差异二级索引只存了索引列和主键值你要查询的其它字段都不在里面。所以一个select *查询如果走了二级索引MySQL需要先从二级索引里找到匹配的主键再拿着主键回聚簇索引里取整行数据这个过程叫回表。回表本质上是一个随机IO操作尤其在数据量大的时候回表次数越多查询越慢。我经常用查资料来类比目录只告诉你内容在第几页你还需要翻到那一页才算真正看到内容。如果目录本身就把摘要印出来了你就不用翻正文这就是覆盖索引的意义。覆盖索引指的是查询需要读取的列全部包含在索引树里这时候InnoDB可以直接从索引中返回结果不需要回表执行计划里的Extra会显示Using index。所以在排查慢查询时不要只看“这个查询有没有用上索引”还要看它用的是不是覆盖索引。同样是走idx_status_create这个联合索引select *和select id, status, create_time成本完全不一样。很多慢查询优化并不是换了一个更复杂的索引而是把无谓的回表省掉了。2. 六个最常见的索引失效场景对照排查2.1 联合索引最左前缀原则被突破联合索引是多个列组成的一棵B树索引内部先按第一列排序再按第二列排序以此类推。所以查询条件必须从最左侧的列开始才能命中索引。比如你建了idx_a_b_c(a, b, c)实际相当于创建了a、a,b、a,b,c三套索引能力但如果你直接写where b 1 and c 2MySQL没法从b开始定位这个联合索引就会失效只能全表扫描。很多人以为自己建了联合索引就万事大吉但写SQL时经常忽略顺序。比如where a 1 and c 2这种情况下a等值条件可以用上索引但c的条件没法继续在索引里精确定位只能作为过滤条件。如果中间列是范围查询比如where a 1 and b 100 and c 3那么b后面的c通常就没法继续走索引定位了。这里要补充一个细节MySQL 5.6以后引入了索引下推Index Condition Pushdown像上面这种c 3的条件虽然不能用来在B树上精确定位但可以在读取回表数据之前先用索引里的c值过滤一遍减少回表次数。这个机制对性能有帮助但它和“用索引定位”不是一回事排查时别把这两者混为一谈。2.2 隐式类型转换和字符集不一致隐式类型转换是慢查询里特别容易被忽略的一个坑。最常见的情况是表里的字段是varchar类型但SQL里却写成了数字。比如手机号字段phone varchar(11)查询写where phone 13812345678MySQL会把字符串列转换成数字再去比较相当于对索引列做了CAST(phone AS signed)这一层隐式函数会让索引失效。反过来如果索引列是int类型你写where user_id 123MySQL会把字符串常量转成数字这个转换发生在常量上索引列本身没有加函数所以通常不影响索引使用。但最佳实践还是按字段类型传参不要让MySQL猜你的类型。尤其在用框架拼接SQL时要留意参数类型的传递别把数字参数传成字符串或反过来。字符集不一致也是同一个道理。两张表关联查询时如果关联字段的字符集不同比如一张表是utf8mb4另一张表是utf8MySQL需要在连接时把一边的字符集转换成另一边的字符集这种转换如果作用在索引列上就会导致关联查询走不上索引。解决方式很简单全库统一采用utf8mb4关联字段的字符集和排序规则保持一致。2.3 对索引列做函数运算或表达式这是另一个高频踩坑点。只要索引列参与了函数运算比如where DATE(create_time) 2024-01-01、where LENGTH(name) 5、where amount 1 100B树就没法直接利用索引了。因为索引里存的是原始字段值MySQL需要把每一行的原始值算成函数结果再和条件比较这跟全表扫描没什么区别。有朋友会问那我真的很想按日期查怎么办正确写法是用范围查询create_time 2024-01-01 AND create_time 2024-01-02。这不仅能走索引语义上还更严谨。对于确实无法避免函数运算的场景可以考虑增加冗余字段或者使用MySQL的生成列generated column在生成列上建索引。比如把月份提取成虚拟列再对虚拟列建索引查询时直接按虚拟列过滤性能会比函数运算好很多。保持索引列“裸用”是编写高效SQL的一条重要原则。什么是裸用就是让索引列独立出现在比较运算符的一侧所有计算、函数、类型转换都不要套在它上面。2.4 模糊查询、OR条件和不规范的IN用法模糊查询LIKE %关键字%因为前导通配符B树没法按前缀去匹配所以用不上索引。但LIKE 关键字%这种前缀匹配是可以走索引的前提是查询返回的数据量不高。很多搜索场景确实需要包含匹配这种时候不要指望普通索引能兜底要么考虑全文索引要么用专门的搜索引擎要么就用更精确的查询条件缩小范围。OR条件也是个经典坑。比如where status 1 OR user_id 123如果两个条件分别在不同的索引上MySQL有时会把OR改写成UNION来做但这不是所有情况都能高效处理尤其当两边数据分布不平衡时优化器更容易犯迷糊最后可能变成全表扫描。稳妥的做法是主动改写成两条SQL用UNION ALL合并或者用IN条件替代。注意IN本身是可以正常走索引的并不像某些文章说的“IN必慢”关键还是看IN列表数据量和总体基数。3. 别猜了用EXPLAIN查看慢查询的真实执行计划3.1 EXPLAIN输出里到底看哪几个字段排查索引问题最高效的手段就是执行计划。MySQL提供了一个简单又关键的查看工具EXPLAIN。用法就是把EXPLAIN放在SQL前面比如EXPLAIN SELECT id, order_no, status, create_time FROM orders WHERE status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;执行计划里字段很多但排查索引失效时我一般重点看几列。type列显示访问类型从好到坏大概是system、const、eq_ref、ref、range、index、ALL。如果看到ALL就是全表扫描基本可以确定索引没被用上看到index说明扫描了整棵索引树也不一定高效range和ref才算是正常的索引范围扫描。key列表示实际用到的索引名是NULL就是没走索引。rows列是优化器估算的需要扫描的行数虽然只是估算但能直观反映查询大概扫了多少数据。Extra列信息量很大。出现Using filesort说明排序没有利用索引顺序Using temporary说明查询用了临时表常见于分组或去重Using index说明使用了覆盖索引这个是好现象Using where说明有普通过滤Using index condition表示索引下推被用上了。组合起来看基本就能判断一条SQL的问题在哪。3.2 执行计划“看似用了索引”的陷阱有时候执行计划里显示key不是NULL但查询还是很慢这种别急着下结论要往下看key_len和rows。key_len代表索引中实际被使用的字节长度如果联合索引有多个列但key_len只显示了第一列的长度说明后面几列没被用上。比如你的联合索引是(status, create_time, user_id)但查询里只用了status条件key_len就不会包含create_time和user_id的部分。rows列如果非常高比如扫描了几十万行即便type是range查询也不可能快。这时候的索引只是“用上了”但过滤效果很差。另一个陷阱是type显示index它表示遍历了整棵索引树这本质上也不是高效的查询方式常见于查询的列刚好都被某索引覆盖但WHERE条件没有落到索引前缀上结果优化器只能扫完整棵索引树。所以分析执行计划不能只看一列要综合type、key、key_len、rows、Extra一起看。如果某个查询的rows接近全表行数那就别纠结为什么慢先考虑怎样缩小结果集而不是盲目加索引。3.3 优化器选错索引时的处理顺序还有一种少见但真实存在的情况索引明明合适统计信息也正常但优化器就是选了一个错误索引。通常是因为表数据分布发生了大变化而统计信息还没及时更新。这时候第一件事不是FORCE INDEX而是先跑一次ANALYZE TABLE刷新统计信息然后再重新EXPLAIN很多时候问题就消失了。如果刷新统计信息后还是选错可以使用FORCE INDEX来验证比如SELECT * FROM orders FORCE INDEX(idx_user_time) WHERE ...。但FORCE INDEX只是排查手段不是长期方案因为数据分布一旦变化强制索引可能会导致后面更慢。更稳妥的做法是调整联合索引设计让优化器自然选择正确路径。MySQL 8.0还可以用EXPLAIN ANALYZE直接看到SQL实际执行时间和各步骤耗时这对判断瓶颈特别有帮助。4. 一次订单慢查询的完整排查实录4.1 现场报警与表面判断某个业务系统的订单表表结构大概是这样的CREATE TABLE orders ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, status VARCHAR(10) NOT NULL, amount DECIMAL(12,2), create_time DATETIME NOT NULL, KEY idx_status_time (status, create_time) ) ENGINEInnoDB;表里数据量大约500万行慢查询日志里出现了一条高频SQLSELECT * FROM orders WHERE status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;开发同事很困惑他说status和create_time都建了联合索引为什么还是慢。我执行EXPLAIN后发现typeALLkeyNULLrows接近500万Extra显示Using where这是典型的全表扫描。看到这里我第一反应就是status字段类型是varchar但条件写的是数字1很可能触发了隐式类型转换。4.2 深入检查发现的一连串问题我先把SQL里的status 1改成status 1重新EXPLAIN之后type从ALL变成了rangekey也变成了idx_status_time说明索引确实能用了。但rows仍然显示接近250万查询时间只是从900毫秒降到了500毫秒还是没法接受。这时候再往下挖发现两个问题。第一status只有0和1两种取值区分度非常低等于命中了一半数据。即便走了索引MySQL也要在索引里扫描一大段范围然后不停回表拿整行数据。第二SQL里写的是select *包括amount、user_id、order_no等字段这些字段都不在idx_status_time里每一行命中都要回表产生了大量的随机IO。而且业务上这个查询其实是想查最近7天下单并且状态为1的订单但SQL只写了create_time 2024-01-01这个时间范围跨度太大。真正合理的查询应该把时间起点设为一周前比如create_time NOW() - INTERVAL 7 DAY。时间范围限制住了索引的过滤效果才会真正体现出来。4.3 改造后的对比与结论最后我给出了两个方向的优化。第一是调整查询条件把时间范围收窄并且只查询业务真正需要的字段。第二是新建一个更合理的覆盖索引ALTER TABLE orders ADD INDEX idx_status_time_order (status, create_time, order_no);这个索引可以把order_no直接覆盖进去查询时不再需要回表。改造后的SQL如下SELECT id, status, create_time, order_no FROM orders WHERE status 1 AND create_time NOW() - INTERVAL 7 DAY ORDER BY create_time DESC LIMIT 20;对比一下优化前后的表现阶段typekeyrows耗时原始SQL隐式转换ALLNULL约500万900ms修复引号但select *rangeidx_status_time约250万500ms收窄范围覆盖索引rangeidx_status_time_order约1.2万30ms从900毫秒降到30毫秒问题解决了。这个案例其实很典型看起来是“索引没生效”实际上是隐式转换、低区分度、无脑select *、时间范围过宽几个问题叠加在一起。排查慢查询时不要只盯一个点要顺着执行计划一层层往下看。5. 索引设计阶段容易忽略的三类细节5.1 索引基数与选择性不能只看字段长度索引基数指的是某个索引列有多少个不同的值。基数越高索引的区分度越好。区分度通常用选择性表示也就是COUNT(DISTINCT col) / COUNT(*)。选择性越接近1说明该列重复值越少索引过滤效果越明显。反之如果选择性不到0.01那么查这个字段大概率会扫出大量数据。很多人建索引时喜欢挑字符串长的字段觉得越长越唯一但实际B树索引对长字段会有前缀索引的优化也不是越长越好。关键是看组合后的整体选择性。比如单独的status选择性很低但status加上create_time后因为时间粒度细组合选择性就能达到很高。判断一个联合索引是否合理可以把查询条件里的列放在一起算区分度不能只看单个字段。5.2 冗余索引和隐形写放大索引不是免费的。每多一个索引就多一棵B树插入、删除、更新时都要同步维护这就是写放大。一个表如果索引太多写入性能会明显下降存储空间也会增加。尤其是那种为了某一个特殊查询临时加上的索引后面查询不用了索引还在默默拖累所有写入操作。冗余索引是更隐蔽的问题。比如你已经有了联合索引(a, b)又单独建了索引(a)那这个单独索引其实是可以删掉的因为联合索引的最左前缀已经覆盖了a列单独查询的场景。联合索引(a, b, c)也能覆盖(a, b)和(a)的能力。设计索引时要习惯性地检查现有索引避免重复建设。5.3 排序、分组和深分页对索引的影响索引本身是有序的查询里的ORDER BY如果能匹配索引列的顺序MySQL就可以直接按索引顺序扫描避免额外的排序操作。比如联合索引(status, create_time)查询条件status 1 ORDER BY create_time DESC这个顺序和索引排列完全一致MySQL可以反向扫描索引Extra里不会出现Using filesort。但如果条件里跳过中间的status直接按create_time排序就不能指望索引了。分组语句GROUP BY也有类似规律索引顺序和分组列顺序一致时可以减少临时表。深分页问题是另一个老大难LIMIT 100000, 20这种写法即使走了索引MySQL也要扫描前面十万行然后丢弃掉后面的20行才返回扫描开销很大。优化深分页的办法一般是改成游标分页比如WHERE id last_id ORDER BY id LIMIT 20或者用延迟关联先在索引上拿到主键ID范围再回表取所需字段。6. 我日常维护时的几个习惯和避坑心得6.1 慢查询日志与定期巡检现在我接手任何系统第一件事就是确认慢查询日志开没开。MySQL里可以设置long_query_time 1超过1秒的查询都会记录到慢日志中。有了慢日志再配合工具定期做聚合分析就能看出哪些SQL是高频慢查询、哪些表是重灾区。这个习惯能让我在问题爆发之前发现隐患而不是等用户抱怨接口慢才追着日志翻。巡检周期我建议至少每月一次重点看两类内容一类是慢查询数量趋势另一类是最近新增的索引有没有被实际使用。如果某个索引长时间没有出现在执行计划里说明它可能是冗余索引就要考虑是否拆除。数据库不是建完就不管的索引和查询条件随时都在变化需要持续维护。6.2 索引变更前必做的几件事给线上表加索引前我一般会先用测试环境复现SQL记录执行计划。上线前还要确认变更对写入的影响因为一个大表加索引可能锁表最好选择业务低峰期操作使用在线DDL工具来做避免长时间阻塞业务。加完索引之后不要立刻就走要重新跑一遍原SQL确认执行计划确实用了新索引再观察一段时间慢查询有没有下降。最后分享一个小技巧怀疑索引没生效时先别急着加索引按“执行计划 - 字段类型 - 字符集 - 函数运算 - 数据分布”的顺序排查一遍。这套流程我用了很多年帮我避开了大量重复加索引的坑。很多慢查询不是缺索引而是SQL本身不尊重索引的使用规则把规则摸清楚了优化起来其实很快。