MySQL 明明建了索引为什么还是全表扫描EXPLAIN 实战拆解 12 种“不走索引”场景先说结论“建了索引”不等于“本次查询一定使用索引”。MySQL 优化器会比较不同执行计划的成本条件写法无法形成可搜索的索引范围、联合索引顺序不匹配或者命中数据过多、回表代价过高时全表扫描反而可能是更便宜的正确选择。线上慢 SQL 最容易被误诊的一句话是“索引失效了再加一个索引吧。”结果往往是索引越加越多写入更慢原查询仍然是typeALL。真正可靠的顺序应该是固定 SQL 与参数 → 看 EXPLAIN 估算 → 判断属于哪类问题 → 改写 SQL 或索引 → 用 EXPLAIN ANALYZE 验证实际扫描行数和耗时。本文准备一张 20 万行、数据明显倾斜的订单表逐一拆解 12 种常见场景。你不仅能记住“哪里容易不走索引”还会知道为什么、怎样修改以及哪些所谓“索引失效口诀”并不严谨。一、先纠正概念不是“索引失效”而是计划没有选择它索引仍然存在也没有突然坏掉。一次查询不使用某个索引通常只有三类原因条件不可检索non-sargable例如对索引列做函数、发生不合适的隐式转换、使用前置通配符优化器无法把条件转换成连续的 BTree 搜索区间。索引结构不匹配例如跳过联合索引左侧列、等值/范围/排序的顺序不合适或者连接列类型和字符集不一致。成本不划算虽然可以使用索引但预计要命中大部分数据并频繁回表顺序扫描可能比随机访问二级索引再回主键树更便宜。图 1优化器比较的不是“有没有索引”而是候选计划的总成本。原创教学图Image2 生成。这也解释了为什么同一条 SQL 在测试库使用索引到了生产库却全表扫描两边的数据量、值分布、缓存状态与统计信息可能完全不同。执行计划属于“SQL 参数 表结构 数据分布 MySQL 版本”的组合结果不能只复制一条索引语句就下结论。二、搭建可复现实验让“选择性”真正出现先建立订单表CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idBIGINTNOTNULL,statusVARCHAR(16)NOTNULL,phoneVARCHAR(32),amountDECIMAL(12,2)NOTNULL,created_atDATETIMENOTNULL,cityVARCHAR(32)NOTNULL,noteVARCHAR(255),KEYidx_user_status_time(user_id,status,created_at),KEYidx_created_at(created_at),KEYidx_phone(phone),KEYidx_city(city))ENGINEInnoDB;配套的code/seed_orders.py会插入 20 万行数据其中PAID约占 70%北京约占 45%。这种倾斜是故意的如果所有值都均匀分布很多真实的成本选择就无法复现。运行前安装驱动并修改连接密码pipinstallmysql-connector-python mysql-uroot-p-eCREATE DATABASE index_labmysql-uroot-pindex_labcode/setup.sql python code/seed_orders.py实验时不要只看耗时。首次执行可能包含磁盘读取后续执行可能命中缓冲池客户端网络和结果集传输也会污染时间。本文优先比较EXPLAIN的访问路径、估算扫描行数以及EXPLAIN ANALYZE的实际行数和迭代器耗时。三、EXPLAIN 到底看什么按这 5 列读不要只盯 keyEXPLAINSELECTid,amount,created_atFROMordersWHEREuser_id10086ANDstatusPAIDANDcreated_at2025-07-01;图 2先确认访问方式再看实际索引、估算扫描量和附加动作possible_keys只表示候选并不代表真的使用。原创教学图Image2 生成。type表的访问方式。ALL通常表示全表扫描index是完整索引扫描也不是精准查找range、ref、eq_ref等表示不同的索引访问方式。不要背一条绝对排名仍需结合扫描行数判断。possible_keys / key前者是优化器认为可能可用的索引后者才是选中的索引。候选不为空而keyNULL常见原因就是成本不划算。key_len本计划实际使用的索引键长度可辅助判断联合索引用到了几部分它受字段类型、字符集和可空性影响不能只按肉眼猜列数。rows × filtered前者是预计检查行数后者是条件过滤后预计保留比例。它们都是估算不是结果集真实行数。Extra关注Using index condition、Using index、Using filesort、Using temporary等。这里的Using index多指覆盖索引不等于“只要用了索引就显示”。图 3MySQL 8.4 官方文档说明 EXPLAIN 展示优化器如何处理语句并明确提到认为索引本应使用却未使用时可更新表统计信息。来源MySQL 8.4 Reference Manual原始页面。EXPLAIN只展示估算。MySQL 8.0.18 可对支持的语句使用EXPLAIN ANALYZE它会真正执行查询并返回每个迭代器的实际耗时、实际行数和循环次数EXPLAINANALYZESELECTid,amountFROMordersWHEREuser_id10086ANDstatusPAID;因为它会执行语句在线上必须控制查询范围与资源影响。本文示例只对SELECT使用它不要把“分析执行计划”误解成完全无副作用的静态检查。四、12 种“不走索引”场景症状、原因与正确改法图 412 种场景可归为条件不可检索、索引结构不匹配、优化器成本判断三组。原创教学图Image2 生成。A 组WHERE 条件无法形成高效索引范围1. 对索引列使用函数-- 容易失去普通 created_at 索引的范围搜索能力SELECT*FROMordersWHEREDATE(created_at)2025-07-01;-- 改成左闭右开区间SELECT*FROMordersWHEREcreated_at2025-07-01 00:00:00ANDcreated_at2025-07-02 00:00:00;普通索引保存的是created_at原值DATE(created_at)的结果并没有直接存进该索引。左闭右开区间既能利用 BTree 范围又不会遗漏带时分秒的数据。若业务长期按同一表达式查询MySQL 8.0.13 还可评估函数索引CREATEINDEXidx_order_dateONorders((DATE(created_at)));函数索引不是免费午餐它增加写入与存储成本并且查询表达式需要与索引表达式匹配。2. 在索引列上做计算-- 不推荐WHEREamount*100500000-- 把计算移到常量侧WHEREamount5000原则不是“SQL 中禁止函数或计算”而是尽量保持索引列本身裸露让优化器可以求出连续边界。3. 隐式类型转换phone是VARCHAR却传入数字SELECTidFROMordersWHEREphone13812345678;-- 参数类型与列一致SELECTidFROMordersWHEREphone13812345678;最稳妥的修复不是给 SQL 补引号而是在应用层绑定正确类型并让表结构与领域含义一致。手机号、身份证、订单号通常不是参与算术的数字使用字符串也能保留前导零。不同转换方向的结果可能不同务必用当前版本和真实字段执行EXPLAIN不要把“字符串和数字比较必然全表扫”当作无条件定律。4. LIKE 以前置通配符开头WHEREnoteLIKE%campaign-42%BTree 按值的左侧前缀有序开头未知时无法直接定位起点。若需求是前缀检索改为LIKE campaign-42%若需求是任意子串搜索应评估 FULLTEXT、专用搜索引擎或业务倒排索引而不是期待普通 BTree 擅长所有搜索模式。5. OR 的各分支缺少合适索引SELECTidFROMordersWHEREphone13812345678ORnotecampaign-42;phone有索引note没有整条查询可能出现代价较高的计划。但不能简单说“有 OR 就不走索引”MySQL 可能使用 Index Merge也可能选择扫描。可选方案包括为高选择性分支建立合适索引或在语义允许且去重规则明确时拆成UNION ALL。修改前后都要看计划避免拆成两条更慢的查询。B 组联合索引与查询顺序不匹配图 5MySQL 官方文档说明多列索引可使用全部列也可使用任意最左前缀。来源MySQL 8.4 Reference Manual原始页面。6. 跳过联合索引最左列已有idx_user_status_time(user_id, status, created_at)-- 没有 user_id无法直接利用这棵树的最左前缀定位 statusSELECT*FROMordersWHEREstatusREFUNDED;联合索引先按user_id排序同一个用户内部再按status和时间排序。跳过user_id后所有用户的REFUNDED并不是一段连续区间。如果“按状态查”是稳定且高价值的独立入口应基于真实选择性设计以status开头的索引如果它一次返回 70% 的PAID新增索引也未必划算。7. 范围条件之后的列作用容易被夸大或误解WHEREuser_id10086ANDstatusIN(PAID,CLOSED)ANDcreated_at2025-07-01常见口诀是“范围后面的列全部失效”这并不精确。范围构造能使用哪些键部分要看操作符和具体计划后续列还可能参与索引条件下推ICP或过滤只是不一定继续收窄同一段索引搜索边界。设计时通常把稳定的等值条件放前面把范围、排序需求放后面但最终要以key_len、Extra和实际扫描行数验证。8. ORDER BY 与索引顺序或方向不兼容SELECTid,created_atFROMordersWHEREuser_id10086ORDERBYstatusASC,created_atDESCLIMIT20;过滤使用了索引不代表排序也一定使用索引。排序列顺序、升降序组合、前导列是否被常量约束、查询是否跨越多个范围都会影响是否需要filesort。MySQL 8 支持降序索引可为稳定高频查询评估CREATEINDEXidx_user_status_time_descONorders(user_id,statusASC,created_atDESC);图 6MySQL 官方文档列举可使用索引排序和必须 filesort 的条件。来源MySQL 8.4 Reference Manual原始页面。9. JOIN 两侧类型、长度或字符集不一致SELECTo.id,u.nameFROMorders oJOINusers uONo.phoneu.phone;如果一侧是VARCHAR、另一侧是数字或字符集/排序规则不兼容比较过程可能引入转换影响索引使用与估算。长期修复应统一关联键的数据类型、长度、字符集和 collation临时在 JOIN 条件里CAST往往只是把函数问题转移到某一侧。迁移大表时应先核对重复值、截断风险和锁表影响。C 组索引能用但优化器认为扫描更便宜10. 低选择性一个值命中大部分数据实验数据中PAID约占 70%。即使建立idx_status(status)下面查询也可能扫描SELECT*FROMordersWHEREstatusPAID;二级索引先找到大量主键再逐条回聚簇索引取完整行随机访问成本可能超过顺序扫描。相反只有 5% 的REFUNDED更可能受益。索引价值取决于选择性与返回列不是取决于字段有没有出现在 WHERE 中。11. 返回行太多SELECT * 放大回表成本SELECT *本身不会从语法上“让索引失效”但它让覆盖索引几乎不可能并扩大回表与网络传输。当页面只展示订单号、金额和时间时应只取需要的列并按高频读路径评估覆盖索引CREATEINDEXidx_user_time_coverONorders(user_id,created_at,amount);SELECTid,created_at,amountFROMordersWHEREuser_id10086ANDcreated_at2025-07-01;InnoDB 二级索引叶子包含主键因此示例中的id通常无需额外加入索引。覆盖索引适合高频、字段稳定的读路径不要把十几个大字段全部塞进索引否则会增加页数、写放大和缓存压力。12. 统计信息陈旧或分布倾斜没有被表达大量导入、删除或值分布剧烈变化后估算可能偏离实际。先检查而不是直接强制索引ANALYZETABLEorders;EXPLAINFORMATTREESELECTidFROMordersWHEREcity其他;对于没有合适索引、但分布明显不均的列可在确认版本和运维影响后评估直方图ANALYZETABLEordersUPDATEHISTOGRAMONcityWITH64BUCKETS;直方图帮助优化器估算分布不会让一个不存在的访问路径凭空变成索引查询。统计信息修复后仍要比较计划。除紧急止血外不建议先用FORCE INDEX数据分布变化后今天的强制计划可能成为明天的性能事故。五、一套可复用的排查流程否是否是否是是否发现慢 SQL固定参数与数据快照执行 EXPLAINtype 是否为 ALL / index检查 rows、filtered、Extra条件能否形成连续索引范围改写函数、类型、LIKE、OR联合索引顺序匹配吗按等值、范围、排序重排索引检查选择性、回表量与统计信息执行 EXPLAIN ANALYZE实际扫描行数与耗时下降压测并上线观察回到成本与数据分布继续定位按以下顺序操作通常比“凭感觉加索引”更快从慢日志或 APM 获取完整 SQL、真实绑定参数、数据库与时间窗口。保存SHOW CREATE TABLE、SHOW INDEX FROM orders和行数/分布信息。执行EXPLAIN FORMATTREE或传统 EXPLAIN先判断扫描发生在哪张表、哪一个连接步骤。如果条件不可形成范围优先改 SQL如果是联合索引顺序问题再评估新索引。如果候选索引存在却未选中检查返回比例、回表量、排序/临时表和统计信息。使用EXPLAIN ANALYZE对安全的 SELECT 做实际验证重点看估算行数与实际行数是否相差几个数量级。在接近生产的数据量和分布上压测同时观察 P95/P99、CPU、磁盘读、缓冲池命中和写入成本。InnoDB 表候选索引统计信息MySQL 优化器SQL 查询InnoDB 表候选索引统计信息MySQL 优化器SQL 查询alt[索引计划成本更低][全表扫描成本更低]EXPLAIN 展示估算EXPLAIN ANALYZE 执行并返回实际行数与耗时解析条件、连接与排序读取基数、直方图与行数估计估算索引扫描、回表与排序成本估算全表扫描成本选择 range / ref 等访问方式选择 ALL这张时序图强调一个经常被忽略的事实优化器只能根据统计信息估算。它不是读取未来真实耗时后再选计划。因此“估算偏差”与“执行器变慢”要分开排查前者可能需要更新统计或改善数据模型后者还要检查锁等待、I/O、缓存和并发。六、一次完整的改写示例从函数扫描到范围 覆盖需求查询某用户 7 月已支付订单用于列表页展示订单号、金额、时间。错误起点SELECT*FROMordersWHEREDATE(created_at)BETWEEN2025-07-01AND2025-07-31ANDuser_id10086ANDstatusPAIDORDERBYcreated_atDESC;问题有四个对时间列做函数BETWEEN 2025-07-01 AND 2025-07-31只到 7 月 31 日零点容易漏数据取出全部列增加回表和网络成本没有 LIMIT 可能一次返回大量行。改写SELECTid,amount,created_atFROMordersWHEREuser_id10086ANDstatusPAIDANDcreated_at2025-07-01ANDcreated_at2025-08-01ORDERBYcreated_atDESCLIMIT50;现有(user_id, status, created_at)与等值、等值、范围/排序的访问模式一致。验证时不要只看到keyidx_user_status_time就结束还要确认rows是否从接近全表行数降到该用户的候选订单数量Extra是否仍有不必要的临时表或 filesortEXPLAIN ANALYZE中估算行数与实际行数是否接近LIMIT 50 是否真的能沿索引顺序提前停止而不是先计算海量结果再截断新写法在冷缓存、热缓存和并发压力下是否都更稳定。七、6 个高频误区面试和线上都容易踩“typeindex 就很好。”它可能是完整扫描整棵二级索引只是索引比表窄扫描行数仍然可能很大。“possible_keys 有值就说明用了索引。”实际选择看key候选索引也可能因成本被放弃。“WHERE 中每一列都建单列索引。”优化器不一定能高效组合它们写入成本却一定会上升应围绕查询模式设计联合索引。“范围条件后面的列完全无效。”后续键部分可能仍用于 ICP 或过滤只是不一定继续缩小索引范围。“!、NOT IN、OR 必然全表扫描。”它们常常选择性差但具体仍取决于数据分布、可用索引和成本不能脱离 EXPLAIN 下结论。“FORCE INDEX 能永久修好。”它绕过优化器的一部分选择可能暂时止血也可能在数据增长后锁死错误计划。八、如何设计联合索引而不是机械套“等值在前、范围在后”“等值列在前、范围列在后”是一个常用起点不是自动生成索引的公式。真正设计时至少还要看四个维度。第一是入口稳定性。如果业务几乎总是先给出user_id它适合作为前导列如果后台运营经常跨用户按status created_at查询就应把它视为另一条访问路径而不是强迫一棵索引兼顾所有入口。第二是选择性与范围大小。等值条件也可能非常不具选择性例如 70% 的记录都是PAID时间范围也可能只覆盖一分钟。列的操作符不能单独代表过滤能力必须看真实分布。第三是排序与 LIMIT。一条索引若能在过滤后直接按需要的顺序返回前 50 行可能比“过滤更强但需要对数万行排序”的索引更有价值。反过来如果查询本来只返回 5 行消除一次小 filesort 未必值得新增索引。第四是覆盖收益与写入代价。把列表页需要的短字段放进索引可能减少回表把大文本、低频字段和频繁更新列塞入索引会增大叶子页、页分裂、redo、备份与缓存压力。因此应从“查询族”而不是单条 SQL 出发。先统计 Top SQL 的调用量与资源占比将 WHERE、JOIN、ORDER BY、SELECT 列和 LIMIT 归一化再让一个联合索引服务一组相近查询。每新增一棵索引也要检查它是否被现有索引覆盖、是否与另一棵高度重复以及写入峰值是否仍满足目标。九、上线前检查清单检查项你要得到的证据SQL 与参数来自慢日志/APM 的真实值而非手写示例表与索引SHOW CREATE TABLE、SHOW INDEX 的现场快照数据分布高频值占比、NULL 比例、时间范围与增长趋势估算计划key、type、rows、filtered、Extra实际计划EXPLAIN ANALYZE 的实际行数、耗时、loops资源影响P95/P99、CPU、I/O、缓冲池、锁等待写入代价新增索引后的 INSERT/UPDATE、磁盘与备份体积回滚能力可撤销索引/SQL 改动保留变更前基线总结排查 MySQL“不走索引”不要从“再建一个索引”开始而要连续回答三个问题WHERE/JOIN/ORDER BY 能否映射为 BTree 上的有效搜索范围现有联合索引的列顺序是否匹配等值、范围、排序与返回列在当前数据分布下索引扫描 回表真的比全表扫描便宜吗然后用EXPLAIN看估算用EXPLAIN ANALYZE看实际用压测看并发与尾延迟。只有扫描行数、实际耗时和资源成本都改善才算修复完成。索引不是越多越好也不是查询不用它就一定错高质量优化的核心是让正确的数据访问路径在真实数据上可验证地更便宜。