索引失效避坑: 明明是等值查询，为何EXPLAIN显示走了全表扫描？
引言: 一个让开发者怀疑人生的EXPLAIN
你写了一个简单的等值查询，建了索引，满怀信心地执行EXPLAIN，结果type列赫然显示ALL——全表扫描。你反复检查SQL和索引，百思不得其解。
索引失效不是玄学，而是精确的规则计算。MySQL优化器在决定是否使用索引时，会综合评估数据分布、索引选择性、回表成本等多种因素。有时即使索引存在，优化器也会认为全表扫描更快。
本文将系统梳理12种最常见的索引失效场景，从SQL语法到优化器决策，让你真正理解每一次索引失效背后的逻辑。
一、索引失效全景分类
二、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);/** * 索引列参与运算/函数时的失效原理 * * 核心: 索引存储的是列的原生值 * 运算/函数改变了比较目标 * B+Tree无法定位索引位置 */ public class FunctionOnIndexColumn { public static void main(String[] args) { System.out.println("=== 为什么函数操作会导致索引失效 ===\n"); System.out.println("B+Tree存储的是 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%'; -- B+Tree可以利用有序性，找到Alice开头的最小和最大范围 -- ❌ 索引失效: 左模糊(后缀匹配) SELECT * FROM users WHERE name LIKE '%Alice'; -- B+Tree只能按前缀定位，%开头无法确定范围 -- ❌ 索引失效: 全模糊 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 = 'alice@test.com'; -- 这相当于: -- (全表扫描找 email='alice@test.com') -- UNION -- (用索引找 age=25) -- ✅ 改写1: 使用UNION SELECT * FROM users WHERE age = 25 UNION SELECT * FROM users WHERE email = 'alice@test.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必须在条件中!/** * 最左前缀原理解析 * * 联合索引在B+Tree中按(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)在B+Tree中的排序:"); 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. 通过a=1定位到索引范围"); System.out.println(" 2. 在这个范围内，b是无序的"); System.out.println(" 3. 所以c=3无法利用索引顺序"); System.out.println(" 4. 只能对a=1的所有记录扫描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'; -- 如果type=ALL，不代表索引设计有问题 -- 只是优化器认为全表扫描成本更低四、索引设计缺陷导致的失效
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 gender='M' AND age > 25 可以有效利用4.2 索引列过长
-- ===== 场景10: 索引列过长 ===== -- 有索引: idx_description ON products(description) -- description是TEXT类型 -- 问题: -- 1. 一个索引页能存的键值很少(扇出小) -- 2. B+Tree高度增加 -- 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 FORMAT=JSON 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 = 'enabled=on'; -- 执行查询 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 = 'enabled=off';七、总结
7.1 索引失效速查卡
| 编号 | 失效原因 | 典型SQL | 解决方式 |
|------|---------|---------|---------|
| 1 | 列参与运算 |WHERE age+1=20|WHERE age=19|
| 2 | 隐式转换 |WHERE phone=138|WHERE phone='138'|
| 3 | 前导模糊 |LIKE '%Alice'| 全文索引 |
| 4 | OR非索引列 |OR col=val| UNION |
| 5 | 最左前缀 |WHERE b=2(跳a) | 调整索引顺序 |
| 6 | NOT IN/<> |WHERE col NOT IN| 覆盖索引 |
| 7 | IS NULL |WHERE col IS NULL| 覆盖索引 |
| 8 | 低区分度 |WHERE gender='M'| 联合索引 |
| 9 | 回表成本高 | 大量行回表 | 覆盖索引 |
| 10 | 统计不准 | 未ANALYZE | ANALYZE TABLE |
| 11 | 索引列过长 | TEXT索引 | 前缀索引 |
| 12 | 数据量太小 | <100行 | 不需要索引 |
7.2 排查口诀
查询用EXPLAIN，type是核心 ALL和index需警惕，range以上才满意 key_len看长度，联合索引验证他 Extra看Using，filesort和temporary要优化
