MySQL索引失效的十五种常见场景与避坑指南

MySQL索引失效的15个典型场景：从原理到避坑指南

理解索引失效的核心在于：查询条件无法与B+树索引的有序结构进行高效匹配。掌握这一原理，就能有效规避数据库性能陷阱。

EXPLAIN 看到 key 为 NULL 就说明没走索引？没那么简单

使用EXPLAIN分析SQL时，key列为NULL通常意味着未使用索引。但这并非绝对，key有值也可能存在性能问题。

关键在于综合解读type和rows字段。type显示ALL即为全表扫描；rows预估扫描行数若接近表总量，则索引效率低下。

需警惕以下几种常见误判情况：

• 数据量过小：当表记录极少时，优化器判定全表扫描的I/O成本低于索引查找加回表成本，此时不使用索引是合理决策。
• 覆盖索引不完整：使用SELECT *查询而索引未包含全部所需列，高代价的回表操作可能导致优化器放弃使用索引。
• 统计信息陈旧：MySQL依赖统计信息估算查询成本。若表数据分布发生重大变化后未执行ANALYZE TABLE更新统计信息，优化器的选择可能失真。

联合索引不满足最左前缀，后面字段全作废

这是联合索引最核心的失效场景。假设建立索引KEY idx_code_age_name (code, age, name)，其排序逻辑类似于“姓氏-名字-性别”结构的电话簿。

能够高效利用该索引的查询条件包括：

• 匹配首列（WHERE code = ?）
• 匹配前两列（WHERE code = ? AND age = ?）
• 匹配所有列（WHERE code = ? AND age = ? AND name = ?）
• 匹配首列及第三列（WHERE code = ? AND name = ?）。此情况可利用索引快速定位“姓氏”范围，但无法对“性别”进行索引查找，仅能在范围内过滤。

以下写法则完全无法利用索引的有序性：

• WHERE age = ?（跳过最左列）
• WHERE name = ?（跳过前两列）
• WHERE age = ? AND name = ?（缺少最左前缀，索引完全失效）

根本原因在于B+树按定义顺序逐级排序，缺失起点则无法定位扫描范围。

WHERE 里对索引列用函数或运算，索引直接“看不见”

索引存储的是列的原始值，而非计算后的结果。在WHERE条件中对索引列进行任何“加工”，MySQL便无法直接使用索引树进行快速比对。

典型失效案例如下：

• WHERE DATE(create_time) = '2024-04-21' → 应优化为范围查询：WHERE create_time >= '2024-04-21' AND create_time
• WHERE UPPER(name) = 'SUNYANG' → 应确保数据格式统一：WHERE name = 'sunyang'
• WHERE price * 1.1 > 100 → 将计算移至等号右侧：WHERE price > 100 / 1.1
• WHERE id + 1 = 100 → 直接计算常量：WHERE id = 99

需特别注意，即使如IFNULL(col, 'default')或COALESCE(col, 'x')这类看似无害的函数，也会导致该列索引失效。

隐式类型转换和 NOT 类操作让优化器放弃索引

当发生字符串与数字间的隐式类型比较时，MySQL会在索引列上执行隐式转换，等效于应用函数，从而导致索引失效。

例如，user_id为INT类型，查询WHERE user_id = '123'。MySQL实际执行WHERE CAST(user_id AS CHAR) = '123'，索引无法使用。

以下几类操作同样极易导致索引失效，尤其在数据量庞大时：

• 否定操作：WHERE status != 1 或 WHERE status 1
• 非集合：WHERE name NOT IN ('a', 'b')
• 非空判断：WHERE age IS NOT NULL（值得注意的是，IS NULL通常可利用索引）
• 前导通配符：WHERE name LIKE '%三' 或 WHERE name LIKE '%三%'（因无法确定查找起点）
• OR连接不同索引列：WHERE a = 1 OR b = 2（若a和b非联合索引，优化器通常选择全表扫描）

最隐蔽的风险在于，这些写法在数据量小的测试环境中可能运行流畅，一旦部署至千万级的生产大表，将引发严重的性能骤降，且问题根源难以直观发现。