索引失效的场景

  1. 对索引列进行计算、函数操作或类型转换：

    • 这是最常见也最容易被忽略的情况。如果在WHERE子句中，对索引列应用了任何函数（无论是内置函数还是自定义函数）或进行了计算，那么索引将不会被使用。
    • 原因：索引中存储的是原始列的值，而经过函数或计算后的值，数据库无法直接与索引树中的值进行匹配。它需要对表中的每一行都执行一次函数或计算，然后再进行比较，这等同于全表扫描。
    • 示例：
      • 失效：SELECT * FROM users WHERE YEAR(create_time) = 2023; (使用了YEAR()函数)
      • 有效：SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'; (将条件转换为对原始列的范围查询)
      • 失效：SELECT * FROM users WHERE score / 10 = 9; (进行了计算)
      • 有效：SELECT * FROM users WHERE score >= 90 AND score < 100;
      • 失效（隐式类型转换）：SELECT * FROM users WHERE phone = 1234567890; (假设phone是VARCHAR类型，而查询条件是数字，数据库可能会将列的值转换为数字再比较，导致索引失效)
      • 有效：SELECT * FROM users WHERE phone = '1234567890';

  2. 不符合最左前缀原则（Leftmost Prefix Principle）：

    • 这主要针对联合索引（Composite Index）。例如，有一个联合索引 idx(a, b, c)。
    • 优化器要使用这个索引，查询条件必须从索引的最左边的列开始，并且不能跳过中间的列。
    • 示例（对于索引 idx(a, b, c)）：
      • 有效：WHERE a = 1;
      • 有效：WHERE a = 1 AND b = 2;
      • 有效：WHERE a = 1 AND b = 2 AND c = 3;
      • 失效：WHERE b = 2; (没有使用最左边的列a)
      • 失效：WHERE a = 1 AND c = 3; (跳过了中间的列b，只有列a部分的索引会生效)

  3. 范围查询右侧的列失效：

    • 在使用联合索引时，如果对某个列使用了范围查询（如 >、<、BETWEEN、LIKE），那么该列右边的所有列的索引都将失效。
    • 原因：范围查询之后，后续列的索引顺序就无法保证了，优化器无法继续利用索引进行快速定位。
    • 示例（对于索引 idx(a, b, c)）：
      • WHERE a = 1 AND b > 10 AND c = 3;
      • 这个查询中，索引会用于a列的等值匹配和b列的范围查找，但c列的索引部分将不会被使用，因为b的范围查找已经中断了索引的有序性。

  4. 不当的LIKE查询：

    • 当LIKE查询的模式以通配符%开头时，索引会失效。
    • 原因：索引是按从左到右的顺序建立的，如果左边是未知的（%），数据库无法定位索引的起始点。
    • 示例：
      • 失效：WHERE name LIKE '%john%';
      • 有效：WHERE name LIKE 'john%'; (可以使用name列的索引)

  5. 使用OR连接条件：

    • 如果OR连接的多个条件中，有任何一个条件所在的列没有索引，那么通常整个查询都会放弃使用索引，转而进行全表扫描。
    • 原因：优化器认为，如果一部分数据需要全表扫描才能找到，那么合并两个结果集（一个来自索引，一个来自全表扫描）的成本可能比直接一次性全表扫描更高。
    • 优化：确保OR两边的所有条件列都有索引。

  6. 使用NOT或不等于操作：

    • !=、<>、NOT IN、NOT EXISTS等操作通常无法有效利用索引，因为它们不指向一个明确的范围或值，需要扫描大部分数据。
    • IS NULL 和 IS NOT NULL：在早期的MySQL版本中，IS NULL不走索引。现在版本的优化器已经有所改进，但如果NULL值非常多，优化器也可能放弃索引。

  7. 优化器认为全表扫描更快：

    • 即使查询符合所有使用索引的规则，优化器也可能最终选择不使用索引。
    • 原因：
      • 表的数据量非常小：对于小表，全表扫描的成本（主要是CPU）可能低于使用索引的成本（I/O + CPU）。
      • 索引的选择性（Cardinality）很差：如果一个索引列的值重复度非常高（如性别列），通过索引过滤掉的数据很少。优化器估算到即使使用索引，仍然需要回表查询大量的数据行，其随机I/O的成本可能高于一次顺序的全表扫描。
      • 查询需要返回表中大部分的数据：当优化器估算查询结果会覆盖表中大部分行时（例如超过20%-30%），它通常会选择全表扫描。
      • 数据库的统计信息不准确：优化器依赖于表的统计信息（如行数、值的分布等）来做决策。如果统计信息过时或不准，可能导致它做出错误的判断。

如何排查和解决： * 使用EXPLAIN命令：这是分析SQL查询是否使用索引以及如何使用索引的最重要工具。通过查看EXPLAIN的输出（特别是type, key, rows, Extra等字段），可以清晰地了解索引的使用情况。 * 保持索引列的“干净”：避免在WHERE子句中对索引列进行任何形式的转换或计算。 * 精心设计索引：根据业务查询模式设计合适的单列索引和联合索引，并注意联合索引的列顺序。 * 定期更新统计信息：使用ANALYZE TABLE等命令确保优化器有准确的数据来做决策。