MYSQL索引失效情况

MySQL 索引失效的常见场景可以分为以下几类，结合具体示例更容易理解：

1. 违反最左前缀原则（Leftmost Prefix Rule）

适用场景：联合索引（复合索引）。
失效原因：未从索引的最左列开始查询，或未连续使用索引列。
示例：

-- 假设索引为 (a, b, c)
SELECT * FROM table WHERE b = 2;          -- 不生效（未从 a 开始）
SELECT * FROM table WHERE a = 1 AND c = 3; -- 部分生效（仅用到 a）

2. 对索引列进行计算或函数操作

失效原因：索引存储的是原始值，无法匹配计算后的结果。
示例：

-- 索引列参与计算
SELECT * FROM table WHERE a + 1 = 5;      -- 失效（应改为 a = 4）
-- 使用函数
SELECT * FROM table WHERE YEAR(date_col) = 2023; -- 失效

3. 隐式类型转换

失效原因：字段类型与查询值类型不一致，触发隐式转换。
示例：

-- 假设 phone 字段是 VARCHAR，但查询时用数字
SELECT * FROM table WHERE phone = 13800138000; -- 失效（应改为 '13800138000'）

4. 使用 OR 连接非索引列

失效原因：若 OR 连接的字段中有一个无索引，则全查询失效。
示例：

-- 假设 a 有索引，b 无索引
SELECT * FROM table WHERE a = 1 OR b = 2; -- 全表扫描

5. 模糊查询以通配符开头

失效原因：无法利用索引的有序性。
示例：

SELECT * FROM table WHERE name LIKE '%abc'; -- 失效
SELECT * FROM table WHERE name LIKE 'abc%'; -- 可能生效（取决于优化器）

6. 范围查询后的索引列失效

失效原因：范围查询（>, <, BETWEEN）可能导致后续索引列失效。
示例：

-- 索引 (a, b, c)
SELECT * FROM table WHERE a > 1 AND b = 2; -- 仅 a 生效，b 可能失效

7. 数据分布不均或全表扫描更快

失效原因：当优化器认为全表扫描成本更低时（如查询覆盖大部分数据）。
示例：

-- 假设 status 有索引，但 90% 的数据满足 status=1
SELECT * FROM table WHERE status = 1; -- 可能全表扫描

8. 使用 != 或 NOT IN

失效原因：非等值查询可能无法有效利用索引。
示例：

SELECT * FROM table WHERE a != 5;       -- 可能失效
SELECT * FROM table WHERE a NOT IN (1,2); -- 可能失效

9. 索引列允许为 NULL 时的陷阱

失效原因：IS NULL 可能用索引，但 IS NOT NULL 可能失效（取决于数据分布）。
示例：

SELECT * FROM table WHERE a IS NOT NULL; -- 可能全表扫描

10. 索引统计信息不准确

失效原因：统计信息过期导致优化器误判（如频繁增删数据）。
修复方法：

ANALYZE TABLE table_name; -- 更新统计信息

11. 隐式字符集转换

失效原因：JOIN 操作时字段字符集不一致。
示例：

-- 假设 table1.col1 是 utf8，table2.col2 是 utf8mb4
SELECT * FROM table1 JOIN table2 ON col1 = col2; -- 索引可能失效

如何验证索引是否失效？

使用 EXPLAIN 分析查询计划：

EXPLAIN SELECT * FROM table WHERE a = 1;

• 查看 key 字段确认使用的索引。
• type 字段为 ref/range 通常表示索引生效，ALL 表示全表扫描。

总结

索引失效的本质是优化器认为使用索引的成本高于全表扫描。实际场景中需结合表结构、数据分布、查询条件等综合分析。定期使用 EXPLAIN 检查执行计划是优化索引的关键步骤。