MySQL 中索引失效的12种常见场景

---

在 MySQL 中，索引失效会导致查询性能急剧下降，甚至退化为全表扫描。以下是 12 种常见导致索引失效的场景，结合原理和示例详细说明：

---

1. 数据类型隐式转换

原理：
当查询条件中的数据类型与索引列定义的数据类型不一致时，MySQL 会进行隐式类型转换，导致无法使用索引。
示例：

-- 假设 `user_id` 是 VARCHAR 类型且有索引
SELECT * FROM users WHERE user_id = 10086;  -- 隐式转换为字符串，索引失效

修复：

SELECT * FROM users WHERE user_id = '10086';  -- 保持类型一致

---

2. 对索引列使用函数或表达式

原理：
对索引列进行函数操作或计算时，索引无法匹配原始值。
示例：

-- 假设 `create_time` 是 DATETIME 类型且有索引
SELECT * FROM orders WHERE DATE(create_time) = '2023-10-01';  -- 索引失效

修复：

SELECT * FROM orders 
WHERE create_time >= '2023-10-01 00:00:00' 
  AND create_time <= '2023-10-01 23:59:59';  -- 避免使用函数

---

3. 联合索引未遵循最左前缀原则

原理：
联合索引 (a, b, c) 只能按 a → a+b → a+b+c 的顺序使用，跳过左侧列会导致索引失效。
示例：

-- 索引为 (age, name)
SELECT * FROM users WHERE name = 'Alice';  -- 未使用 age，索引失效

修复：

ALTER TABLE users ADD INDEX idx_name (name);  -- 单独为 name 建索引
-- 或调整查询条件：
SELECT * FROM users WHERE age = 25 AND name = 'Alice';  -- 使用联合索引

---

4. 使用 OR 连接非索引列

原理：
如果 OR 连接的列中有一个无索引，优化器可能选择全表扫描。
示例：

-- 假设 `age` 有索引，`address` 无索引
SELECT * FROM users WHERE age = 25 OR address = 'Beijing';  -- 索引失效

修复：

-- 改写为 UNION：
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE address = 'Beijing';

---

5. 使用 LIKE 以通配符开头

原理：
LIKE '%abc' 或 LIKE '%abc%' 无法利用索引的 B+Tree 有序性。
示例：

-- 假设 `title` 有索引
SELECT * FROM articles WHERE title LIKE '%mysql%';  -- 索引失效

修复：

-- 使用全文索引（FULLTEXT）优化模糊查询：
ALTER TABLE articles ADD FULLTEXT INDEX ft_title (title);
SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql');

---

6. 范围查询后的列无法使用索引

原理：
在联合索引中，若某一列使用范围查询（>、<、BETWEEN），其后的列无法使用索引。
示例：

-- 索引为 (age, salary)
SELECT * FROM employees WHERE age > 30 AND salary = 50000;  -- salary 无法使用索引

修复：

-- 调整索引顺序（若 salary 查询频率高）：
ALTER TABLE employees ADD INDEX idx_salary_age (salary, age);

---

7. 使用 != 或 <> 操作符

原理：
非等值查询需要扫描大部分数据，优化器可能放弃索引。
示例：

-- 假设 `status` 有索引
SELECT * FROM orders WHERE status != 'completed';  -- 索引可能失效

修复：

-- 改写为范围查询：
SELECT * FROM orders WHERE status IN ('pending', 'cancelled');

---

8. 索引列参与计算

原理：
对索引列进行数学运算或逻辑操作会导致索引失效。
示例：

-- 假设 `price` 有索引
SELECT * FROM products WHERE price * 0.9 = 100;  -- 索引失效

修复：

SELECT * FROM products WHERE price = 100 / 0.9;  -- 改写为无计算的表达式

---

9. 数据量过少或全表扫描更快

原理：
优化器可能认为全表扫描比索引扫描更快（如小表或高比例数据命中）。
示例：

-- 表中仅 100 行数据，`age` 有索引
SELECT * FROM users WHERE age > 10;  -- 可能全表扫描

修复：
无需修复，优化器的选择是合理的。

---

10. 隐式字符集转换

原理：
JOIN 操作中，若两表的字符集或排序规则不同，索引可能失效。
示例：

-- 表 A 的 `name` 是 utf8mb4，表 B 的 `name` 是 latin1
SELECT * FROM A JOIN B ON A.name = B.name;  -- 索引失效

修复：
统一字符集：

ALTER TABLE B MODIFY name VARCHAR(255) CHARACTER SET utf8mb4;

---

11. 使用 IS NULL 或 IS NOT NULL

原理：
若索引列允许 NULL 且大部分值为 NULL，优化器可能放弃索引。
示例：

-- 假设 `address` 有索引且 90% 为 NULL
SELECT * FROM users WHERE address IS NULL;  -- 可能全表扫描

修复：
避免在索引列中存储大量 NULL 值，或强制使用索引：

SELECT * FROM users USE INDEX (idx_address) WHERE address IS NULL;

---

12. 参数化查询的值分布不均

原理：
当查询条件的值在表中占比过高时，优化器可能选择全表扫描。
示例：

-- 假设 `gender` 有索引，但 95% 的值为 'M'
SELECT * FROM users WHERE gender = 'M';  -- 可能全表扫描

修复：
无需修复，优化器的选择是合理的。

---

如何诊断索引失效？

1. 使用 EXPLAIN：

   EXPLAIN SELECT * FROM users WHERE age = 25;
   

   • 检查 type 字段：index 或 range 表示使用索引，ALL 表示全表扫描。
   • 检查 key 字段：显示实际使用的索引。

2. 开启慢查询日志：

   SET GLOBAL slow_query_log = ON;
   SET GLOBAL long_query_time = 1;  -- 记录执行超过 1 秒的查询
   

---

总结与优化建议

1. 设计索引时：

   • 遵循最左前缀原则。
   • 优先选择区分度高（基数大）的列。
   • 避免过度索引，定期清理无用索引。

2. 编写 SQL 时：

   • 避免类型转换、函数操作、计算表达式。
   • 谨慎使用 OR、LIKE 通配符、范围查询。

3. 维护数据时：

   • 定期执行 ANALYZE TABLE 更新统计信息。
   • 使用 OPTIMIZE TABLE 重建表（针对 MyISAM 或 InnoDB 碎片化严重时）。

通过合理设计索引和编写高效的 SQL，可显著提升查询性能。