MySQL索引分类与索引失效

MySQL 索引分类

MySQL 中的索引主要用于加快数据检索速度。常见的索引类型包括：

1. 主键索引（Primary Key Index）

   • 每张表只能有一个主键索引。
   • 主键列的值必须唯一且不能为 NULL。
   • 主键索引通常是聚簇索引（Clustered Index），即数据行的物理存储顺序与索引顺序一致。

2. 唯一索引（Unique Index）

   • 唯一索引列的值必须唯一，但允许有 NULL 值。
   • 一张表可以有多个唯一索引。

3. 普通索引（Normal Index）

   • 最基本的索引类型，没有唯一性约束。
   • 适用于频繁查询但不需要唯一性的列。

4. 全文索引（Fulltext Index）

   • 用于全文搜索，适用于 CHAR、VARCHAR 和 TEXT 类型的列。
   • 主要用于 MATCH AGAINST 查询。

5. 组合索引（Composite Index）

   • 在多个列上创建的索引。
   • 查询时遵循最左前缀原则（Leftmost Prefix Principle），即查询条件必须包含组合索引的最左列，否则索引可能失效。

6. 前缀索引（Prefix Index）

   • 对列的前缀部分创建索引，适用于较长的字符串列。
   • 可以节省索引空间，但可能会影响查询精度。

7. 空间索引（Spatial Index）

   • 用于地理空间数据类型（如 GEOMETRY）。
   • 支持空间查询操作。

索引失效的常见场景

1. 未使用最左前缀原则

   • 对于组合索引，查询条件必须包含索引的最左列，否则索引失效。
   • 例如，组合索引为 (A, B, C)，查询条件为 B = 1 AND C = 2，索引将失效。

2. 使用函数或表达式

   • 在查询条件中对索引列使用函数或表达式，索引将失效。
   • 例如，WHERE YEAR(create_time) = 2023，索引 create_time 将失效。

3. 类型转换

   • 查询条件中的数据类型与索引列的数据类型不一致，导致索引失效。
   • 例如，索引列为 VARCHAR，查询条件为 WHERE column = 123，索引将失效。

4. 使用 OR 条件

   • 查询条件中使用 OR 连接多个条件，且其中一个条件未使用索引，索引将失效。
   • 例如，WHERE A = 1 OR B = 2，如果 B 列没有索引，索引 A 将失效。

5. LIKE 查询以通配符开头

   • 使用 LIKE 查询时，如果通配符 % 或 _ 出现在开头，索引将失效。
   • 例如，WHERE column LIKE '%abc'，索引将失效。

6. NOT、!=、<> 操作符

   • 使用 NOT、!= 或 <> 操作符时，索引可能失效。
   • 例如，WHERE column != 1，索引将失效。

7. 数据分布不均匀

   • 如果索引列的数据分布非常不均匀（如大部分值为 NULL 或重复值），MySQL 可能选择不使用索引。
   • 例如，WHERE column IS NULL，如果大部分值为 NULL，索引可能失效。

8. 全表扫描更快

   • 当 MySQL 优化器认为全表扫描比使用索引更快时，索引将失效。
   • 通常发生在数据量较小或索引选择性较低的情况下。

9. 索引列参与计算

   • 查询条件中索引列参与计算，索引将失效。
   • 例如，WHERE column + 1 = 2，索引将失效。

10. 使用 ORDER BY 和 GROUP BY 时索引失效

    • 如果 ORDER BY 或 GROUP BY 的列与索引列不一致，索引可能失效。
    • 例如，索引为 (A, B)，但 ORDER BY B, A，索引将失效。