mysql 索引失效的情况

在 MySQL 中，索引的使用可以大大提升查询性能，但在某些情况下，索引可能会失效，导致 MySQL 无法利用索引，转而进行全表扫描或其他低效操作。以下是索引失效的常见情况：

---

1. 查询条件导致索引失效

1. 条件中使用了函数：

   • 如果查询条件中的列被函数包裹，MySQL 无法利用索引。
   • 示例：
      

     SELECT * FROM users WHERE YEAR(created_at) = 2024; -- 索引失效
   • 优化： 改为不使用函数，直接查询：
      

     SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

2. 条件中使用隐式类型转换：

   • 如果列的类型和查询条件的类型不一致，MySQL 会进行隐式类型转换，导致索引失效。
   • 示例：
      

     SELECT * FROM users WHERE phone_number = 1234567890; -- phone_number 为字符串类型
   • 优化： 确保查询条件与列的类型一致：
      

     SELECT * FROM users WHERE phone_number = '1234567890';

3. 使用不等于操作符：

   • 查询中使用 != 或 <>，MySQL 通常无法利用索引。
   • 示例：
      

     SELECT * FROM users WHERE age != 30; -- 索引失效

4. 使用 OR 条件且未对所有字段加索引：

   • 如果查询条件中包含 OR，并且 OR 两侧的列未同时加索引，MySQL 无法使用索引。

   • 示例：

      

     SELECT * FROM users WHERE name = 'Alice' OR age = 30; -- name 有索引，但 age 没有索引

   • 优化： 确保 OR 的所有列都加索引，或改写为 UNION：

      

     SELECT * FROM users WHERE name = 'Alice' UNION SELECT * FROM users WHERE age =30;

---

2. 索引设计或使用不当导致失效

1. 查询未使用索引的最左前缀原则：

   • MySQL 索引遵循 最左前缀匹配 原则。如果查询未使用索引的前缀部分，索引会失效。

   • 示例：

      

     CREATE INDEX idx_name_age ON users (name, age); SELECT * FROM users WHERE age =30; -- 索引失效

   • 优化： 查询必须包含索引的最左前缀部分：

      

     SELECT * FROM users WHERE name = 'Alice' AND age = 30;

2. 使用通配符 % 开头的 LIKE 查询：

   • 如果 LIKE 查询的模式以 % 开头，MySQL 无法使用索引。

   • 示例：

      

     SELECT * FROM users WHERE name LIKE '%Alice'; -- 索引失效

   • 优化： 改为非 % 开头的查询：

      

     SELECT * FROM users WHERE name LIKE 'Alice%';

3. 索引列参与计算或表达式：

   • 如果查询条件中的索引列被用作计算或表达式的一部分，索引失效。

   • 示例：

      

     SELECT * FROM users WHERE age + 1 = 30; -- 索引失效

   • 优化： 将计算挪到条件外：

      

     SELECT * FROM users WHERE age = 29;

---

3. 数据特性导致索引失效

1. 查询结果的记录过多（选择性低）：

   • 当查询的条件匹配大部分数据时，MySQL 可能会放弃使用索引，转而选择全表扫描。
   • 示例：
      

     SELECT * FROM users WHERE gender = 'male'; -- 假设性别列的值分布不均

2. 统计信息不准确：

   • 如果表的数据变化频繁，索引的统计信息可能不准确，MySQL 会错误选择不使用索引。
   • 解决： 手动更新索引统计信息：
      

     ANALYZE TABLE users;

---

4. 查询优化器的行为

1. 小表扫描优先：

   • 如果表数据量较小，MySQL 优化器可能会选择全表扫描，而不是使用索引。

2. 组合索引中范围条件的限制：

   • 当组合索引中包含范围条件（>、<、BETWEEN），后续的列索引无法被使用。

   • 示例：

      

     CREATE INDEX idx_name_age_salary ON users (name, age, salary); SELECT * FROMusers WHERE name = 'Alice' AND age > 30 AND salary = 5000; -- salary 索引失效

   • 优化： 根据查询需求调整索引顺序。

---

5. 特殊情况

1. 使用 IS NULL 或 IS NOT NULL：

   • 某些版本的 MySQL 中，IS NULL 或 IS NOT NULL 查询可能无法使用索引。

2. 未使用支持索引的存储引擎：

   • 例如 MySQL 的 MEMORY 引擎不支持 TEXT 或 BLOB 字段上的索引。

---

总结

索引失效的原因总结：

1. 查询中使用了函数、表达式或隐式类型转换。
2. 未遵循最左前缀原则，或组合索引的范围查询限制了后续字段的索引使用。
3. 使用 % 开头的 LIKE 查询或 OR 查询未优化。
4. 数据分布特性或查询优化器的选择导致索引被放弃。

解决方案：

• 了解查询条件的特性，设计合适的索引。
• 避免索引失效的常见操作，优化 SQL 查询。
• 定期更新表统计信息，确保查询优化器正确选择索引。