索引失效避坑: 明明是等值查询，为何EXPLAIN显示走了全表扫描？

索引失效避坑: 明明是等值查询为何EXPLAIN显示走了全表扫描引言: 一个让开发者怀疑人生的EXPLAIN你写了一个简单的等值查询建了索引满怀信心地执行EXPLAIN结果type列赫然显示ALL——全表扫描。你反复检查SQL和索引百思不得其解。索引失效不是玄学而是精确的规则计算。MySQL优化器在决定是否使用索引时会综合评估数据分布、索引选择性、回表成本等多种因素。有时即使索引存在优化器也会认为全表扫描更快。本文将系统梳理12种最常见的索引失效场景从SQL语法到优化器决策让你真正理解每一次索引失效背后的逻辑。一、索引失效全景分类索引失效原因SQL写法问题索引设计问题优化器选择1. 索引列参与运算2. 隐式类型转换3. 前导模糊查询 LIKE %xx4. OR条件有非索引列5. NOT IN / 否定条件6. IS NULL/IS NOT NULL7. 联合索引不满足最左前缀8. 索引列区分度低9. 索引列过长10. 回表成本过高11. 统计信息不准12. 数据量太小二、SQL写法导致的索引失效2.1 索引列参与运算或函数操作-- 场景1: 索引列参与运算 -- 有索引: idx_age ON users(age) -- ❌ 索引失效: age列参与了运算 SELECT * FROM users WHERE age 1 20; -- ✅ 等价改写: 把运算移到常量侧 SELECT * FROM users WHERE age 19; -- ❌ 索引失效: 使用了函数 SELECT * FROM users WHERE YEAR(create_time) 2024; -- ✅ 等价改写: 使用范围查询 SELECT * FROM users WHERE create_time 2024-01-01 AND create_time 2025-01-01; -- ❌ 索引失效: 隐式函数(字符集转换) SELECT * FROM users WHERE name CONVERT(Alice USING utf8mb4);/** * 索引列参与运算/函数时的失效原理 * * 核心: 索引存储的是列的原生值 * 运算/函数改变了比较目标 * BTree无法定位索引位置 */ public class FunctionOnIndexColumn { public static void main(String[] args) { System.out.println( 为什么函数操作会导致索引失效 \n); System.out.println(BTree存储的是 age 列的原生值:); System.out.println( 索引树: [18, 19, 20, 21, 22, ...]\n); System.out.println(WHERE age 1 20:); System.out.println( 优化器无法在索引树中定位 age 1 20 的节点); System.out.println( 必须取出所有age值计算1再与20比较); System.out.println( → 索引失效全表扫描\n); System.out.println(WHERE YEAR(create_time) 2024:); System.out.println( 索引中存的是完整时间戳); System.out.println( 无法直接定位 2024年 的边界); System.out.println( 需要计算每行的YEAR值); System.out.println( → 索引失效\n); System.out.println(解决: 让比较在常量侧完成); System.out.println( age 19); System.out.println( create_time 2024-01-01 AND 2025-01-01); } }2.2 隐式类型转换-- 场景2: 隐式类型转换 -- 建表: phone字段是 VARCHAR(20) -- 有索引: idx_phone ON users(phone) -- ❌ 索引失效: phone是字符串但传入的是数字 SELECT * FROM users WHERE phone 13800138000; -- MySQL会隐式转换: CAST(phone AS UNSIGNED) 13800138000 -- 相当于 phone 列参与了函数操作! -- ✅ 索引生效: 传入字符串 SELECT * FROM users WHERE phone 13800138000; -- 验证: 使用EXPLAIN对比 -- EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type: ALL (全表扫描) -- EXPLAIN SELECT * FROM users WHERE phone 13800138000; -- type: ref (索引查找)/** * 隐式类型转换的方向决定索引是否失效 * * 规则: MySQL中字符串与数字比较时 * 会将字符串转为数字 * 即 CAST(字符串列 AS UNSIGNED) * * 关键: 转换发生在索引列上 → 索引失效 * 转换发生在条件值上 → 索引可用 */ public class ImplicitTypeConversion { public static void main(String[] args) { System.out.println( 隐式类型转换规则 \n); System.out.println(规则: 字符串和数字比较字符串转为数字\n); System.out.println(varchar_col 123 (条件值是数字):); System.out.println( → CAST(varchar_col AS UNSIGNED) 123); System.out.println( → 转换在索引列上 → 索引失效!\n); System.out.println(int_col 123 (条件值是字符串):); System.out.println( → int_col CAST(123 AS UNSIGNED)); System.out.println( → 转换在条件值上 → 索引可用!\n); System.out.println(其他隐式转换场景:); System.out.println( - 不同字符集比较 (utf8 vs utf8mb4)); System.out.println( - 不同排序规则比较); System.out.println( - 日期格式的比较); } }2.3 前导模糊查询-- 场景3: LIKE前导模糊 -- 有索引: idx_name ON users(name) -- ✅ 索引生效: 右模糊(前缀匹配) SELECT * FROM users WHERE name LIKE Alice%; -- BTree可以利用有序性找到Alice开头的最小和最大范围 -- ❌ 索引失效: 左模糊(后缀匹配) SELECT * FROM users WHERE name LIKE %Alice; -- BTree只能按前缀定位%开头无法确定范围 -- ❌ 索引失效: 全模糊 SELECT * FROM users WHERE name LIKE %Alice%; -- 例外: 覆盖索引下可能使用索引全扫描 -- SELECT name FROM users WHERE name LIKE %Alice%; -- type: index (索引全扫描比全表扫描快) -- 解决: 使用全文索引或倒排索引(Elasticsearch) ALTER TABLE users ADD FULLTEXT INDEX ft_name (name); SELECT * FROM users WHERE MATCH(name) AGAINST(Alice);2.4 OR条件中混入非索引列-- 场景4: OR条件有非索引列 -- 有索引: idx_age ON users(age) -- email 没有索引 -- ❌ 索引失效: OR的一侧无法使用索引 SELECT * FROM users WHERE age 25 OR email alicetest.com; -- 这相当于: -- (全表扫描找 emailalicetest.com) -- UNION -- (用索引找 age25) -- ✅ 改写1: 使用UNION SELECT * FROM users WHERE age 25 UNION SELECT * FROM users WHERE email alicetest.com AND age ! 25; -- ✅ 改写2: 给email也建索引 -- ALTER TABLE users ADD INDEX idx_email (email);2.5 联合索引与最左前缀原则-- 场景5: 联合索引不满足最左前缀 -- 联合索引: idx_a_b_c ON orders(a, b, c) -- ✅ 索引生效: 覆盖最左列 SELECT * FROM orders WHERE a 1; SELECT * FROM orders WHERE a 1 AND b 2; SELECT * FROM orders WHERE a 1 AND b 2 AND c 3; -- ✅ 索引生效: 范围查询后的列也可用(索引下推) SELECT * FROM orders WHERE a 1 AND b 2 AND c 3; -- a走索引, b走范围, c走索引下推 -- ❌ 索引失效: 跳过最左列 SELECT * FROM orders WHERE b 2; -- 跳过a SELECT * FROM orders WHERE c 3; -- 跳过a和b SELECT * FROM orders WHERE b 2 AND c 3; -- 跳过a -- ⚠️ 部分生效: 中间断档 SELECT * FROM orders WHERE a 1 AND c 3; -- 只有a走索引, c不走(b断档) -- 索引生效的关键: a必须在条件中!/** * 最左前缀原理解析 * * 联合索引在BTree中按(a,b,c)的顺序排列 * 只有a确定时b才有顺序 * 只有a和b都确定时c才有顺序 */ public class LeftmostPrefixPrinciple { public static void main(String[] args) { System.out.println( 最左前缀原则 \n); System.out.println(联合索引(a,b,c)在BTree中的排序:); System.out.println( 先按a排序); System.out.println( a相同则按b排序); System.out.println( a和b相同则按c排序\n); System.out.println(WHERE a 1 AND c 3 的执行:); System.out.println( 1. 通过a1定位到索引范围); System.out.println( 2. 在这个范围内b是无序的); System.out.println( 3. 所以c3无法利用索引顺序); System.out.println( 4. 只能对a1的所有记录扫描c\n); System.out.println(类比: 电话簿); System.out.println( 联合索引(a,b,c) (姓, 名, 电话)); System.out.println( 跳过姓直接查名 - 无法定位); System.out.println( 有姓没名 - 可以在姓的范围内扫描电话); } }三、优化器选择导致的伪失效3.1 回表成本过高-- 场景6: 回表成本超过全表扫描 -- 表: users (id, name, age, email, address, phone, ...) -- 索引: idx_age ON users(age) -- 查询: 查找年龄为25的用户的所有信息 SELECT * FROM users WHERE age 25; -- 如果表中90%的用户都是25岁: -- 使用索引 → 回表读90%的数据行 → 大量随机IO -- 全表扫描 → 顺序读 → 可能更快! -- 优化器计算公式: -- 索引成本 索引扫描行数 × 1.0 回表行数 × 1.0 -- 全表扫描成本 总页数 × 1.0 -- 临界点: 约总行数的10%-20% -- 超过此比例优化器倾向于全表扫描/** * 回表成本计算 * * 回表: 二级索引查询需要回到聚簇索引获取完整行数据 * * 为什么回表比全表扫描慢? * - 全表扫描是顺序读 * - 回表是随机读(根据主键分散读取) * - 随机读的速度远低于顺序读(机械盘约100倍) */ public class TableAccessCostAnalysis { public static void main(String[] args) { System.out.println( 回表 vs 全表扫描 \n); System.out.println(全表扫描(顺序读):); System.out.println( - 按页顺序读取预读机制高效); System.out.println( - HDD: ~50-100MB/s); System.out.println( - SSD: ~500MB/s\n); System.out.println(回表(随机读):); System.out.println( - 先查二级索引获取主键ID); System.out.println( - 再根据ID去聚簇索引读取完整行); System.out.println( - ID可能是分散的随机读取不同页); System.out.println( - HDD: ~0.5-1MB/s (慢100倍!)); System.out.println( - SSD: 影响较小但仍慢于顺序读\n); System.out.println(优化器的选择:); System.out.println( 回表行数 总行数×10% → 用索引); System.out.println( 回表行数 总行数×30% → 全表扫描); System.out.println( 10%-30%之间 → 根据统计信息动态决定); } }3.2 统计信息不准确-- 场景7: 统计信息过时 -- 查看表的统计信息 SHOW INDEX FROM users; -- 关键字段: Cardinality (基数即不重复值的估计数) -- Cardinality越接近行数索引区分度越高 -- 如果统计信息不准优化器可能误判 -- 手动更新统计信息 ANALYZE TABLE users; -- 对于InnoDB: -- 默认通过采样(随机读取少量页)估算Cardinality -- 采样页数: innodb_stats_sample_pages (默认20, 最大可设200) -- 增大采样页数可提高统计精度 SET GLOBAL innodb_stats_sample_pages 100; ANALYZE TABLE users;3.3 数据量太小-- 场景8: 数据量太小全表扫描更快 -- 表只有100行数据 -- 全表扫描可能只需要1-2个页 -- 使用索引反而增加一次索引查找的IO -- 验证: -- EXPLAIN SELECT * FROM small_table WHERE indexed_col value; -- 如果typeALL不代表索引设计有问题 -- 只是优化器认为全表扫描成本更低四、索引设计缺陷导致的失效4.1 索引列区分度太低-- 场景9: 低区分度索引 -- 有索引: idx_gender ON users(gender) -- gender只有 M 和 F 两个值 -- 查询: SELECT * FROM users WHERE gender M; -- 如果表有100万行约50万行是M -- 索引需要扫描50万行回表50万次 -- 全表扫描只需顺序读全表 -- 计算区分度: -- 区分度 不重复值数量 / 总行数 -- gender: 2 / 1,000,000 0.000002 (极低!) -- 主键: 1,000,000 / 1,000,000 1 (完美) -- 这种列不适合单独建索引 -- 可以考虑联合索引: idx_gender_age (gender, age) -- WHERE genderM AND age 25 可以有效利用4.2 索引列过长-- 场景10: 索引列过长 -- 有索引: idx_description ON products(description) -- description是TEXT类型 -- 问题: -- 1. 一个索引页能存的键值很少(扇出小) -- 2. BTree高度增加 -- 3. 缓存命中率降低 -- 解决: 使用前缀索引 ALTER TABLE products ADD INDEX idx_desc_prefix (description(50)); -- 前缀长度的选择: -- 先计算前缀区分度 SELECT COUNT(DISTINCT LEFT(description, 20)) / COUNT(*) AS selectivity_20, COUNT(DISTINCT LEFT(description, 50)) / COUNT(*) AS selectivity_50, COUNT(DISTINCT LEFT(description, 100)) / COUNT(*) AS selectivity_100 FROM products; -- 选择区分度接近完整列的最小长度五、EXPLAIN结果速查5.1 type字段(访问类型)从优到劣/** * EXPLAIN type 字段含义 */ public class ExplainTypeReference { public static void main(String[] args) { System.out.println( EXPLAIN type 访问类型 \n); String[][] types { {system, 系统表仅一行, 极少}, {const, 主键/唯一索引等值查询, 单行最快}, {eq_ref, 关联查询唯一匹配, 极快}, {ref, 非唯一索引等值查询, 快}, {range, 索引范围扫描, 较快}, {index, 索引全扫描, 较慢}, {ALL, 全表扫描, 最慢需优化}, }; System.out.println(Type | 含义 | 速度); System.out.println(-.repeat(50)); for (String[] t : types) { System.out.printf(%-8s | %-20s | %s%n, t[0], t[1], t[2]); } System.out.println(\n目标: 至少达到range级别); System.out.println(应避免: ALL 全表扫描); } }5.2 关键辅助字段-- possible_keys: 可能使用的索引 -- key: 实际使用的索引 -- key_len: 使用索引的长度(判断联合索引用了几个字段) -- rows: 预估扫描行数 -- Extra: 额外信息 -- 重点关注Extra: -- Using index: 覆盖索引(最好) -- Using where: 索引查找过滤 -- Using index condition: 索引下推 -- Using filesort: 文件排序(需优化) -- Using temporary: 临时表(需优化)六、诊断SQL与排查步骤6.1 排查索引失效的标准步骤-- 步骤1: 查看表结构和索引 SHOW CREATE TABLE users; SHOW INDEX FROM users; -- 步骤2: 查看执行计划 EXPLAIN SELECT * FROM users WHERE ...; -- 步骤3: 查看详细执行计划(MySQL 8.0) EXPLAIN FORMATJSON SELECT * FROM users WHERE ...; -- 输出包含cost_info可以看到具体成本估算 -- 步骤4: 查看实际执行统计 EXPLAIN ANALYZE SELECT * FROM users WHERE ...; -- MySQL 8.0.18 支持显示实际执行时间和行数 -- 步骤5: 检查统计信息 SELECT * FROM mysql.innodb_table_stats WHERE table_name users; SELECT * FROM mysql.innodb_index_stats WHERE table_name users; -- 步骤6: 强制使用索引对比 SELECT * FROM users FORCE INDEX(idx_name) WHERE ...; -- 对比FORCE INDEX前后的执行时间和EXPLAIN6.2 优化器Trace分析-- 开启优化器trace(会话级别) SET optimizer_trace enabledon; -- 执行查询 SELECT * FROM users WHERE ...; -- 查看优化器的决策过程 SELECT * FROM information_schema.OPTIMIZER_TRACE\G -- 输出中包含: -- potential_range_indexes: 候选索引 -- analyzing_range_alternatives: 分析各索引成本 -- considered_execution_plans: 最终选择的执行计划 -- attached_conditions_summary: 附加条件 -- cause: cost // 因成本选择全表扫描 -- 关闭trace SET optimizer_trace enabledoff;七、总结7.1 索引失效速查卡| 编号 | 失效原因 | 典型SQL | 解决方式 ||------|---------|---------|---------|| 1 | 列参与运算 |WHERE age120|WHERE age19|| 2 | 隐式转换 |WHERE phone138|WHERE phone138|| 3 | 前导模糊 |LIKE %Alice| 全文索引 || 4 | OR非索引列 |OR colval| UNION || 5 | 最左前缀 |WHERE b2(跳a) | 调整索引顺序 || 6 | NOT IN/ |WHERE col NOT IN| 覆盖索引 || 7 | IS NULL |WHERE col IS NULL| 覆盖索引 || 8 | 低区分度 |WHERE genderM| 联合索引 || 9 | 回表成本高 | 大量行回表 | 覆盖索引 || 10 | 统计不准 | 未ANALYZE | ANALYZE TABLE || 11 | 索引列过长 | TEXT索引 | 前缀索引 || 12 | 数据量太小 | 100行 | 不需要索引 |7.2 排查口诀查询用EXPLAINtype是核心 ALL和index需警惕range以上才满意 key_len看长度联合索引验证他 Extra看Usingfilesort和temporary要优化