数据库--索引

一、什么是索引？

索引是数据库中一种快速查找数据的数据结构，类似于书籍的目录。它通过特定的算法（如B+树、哈希表等）将数据表中的一列或多列的值进行排序和组织，从而加速查询效率。

二、索引的类型

1. 按数据结构分类：

   • B+树索引（默认）：支持范围查询和排序，适用于大多数场景。

   • 哈希索引：仅支持精确匹配（如=），适用于内存表（如MEMORY引擎）。

   • 全文索引（FULLTEXT）：用于文本内容的模糊搜索（如MATCH AGAINST）。

   • 空间索引（SPATIAL）：用于地理空间数据（如GIS数据类型）。

2. 按照物理存储分类：

   • 聚集索引：数据行按照聚集索引键的值在磁盘上进行排序和存储

   • 非聚集索引：也称为辅助索引，它不决定数据的物理存储顺序。

3. 按逻辑功能分类：

   • 主键索引（PRIMARY KEY）：唯一且非空，一个表只能有一个。

   • 唯一索引（UNIQUE）：列值唯一，允许NULL。

   • 普通索引（INDEX）：无唯一性约束。

   • 联合索引（复合索引）：基于多个列组合的索引（如INDEX (col1, col2)）。

三、索引的作用

1. 加速查询：减少全表扫描，提升SELECT效率。

2. 保证唯一性：唯一索引和主键索引约束列值的唯一性。

3. 优化排序与分组：索引已排序，可加速ORDER BY和GROUP BY。

4. 覆盖索引：直接从索引中获取数据，避免回表（减少磁盘IO）。

四、索引失效的常见场景

1. 违反最左前缀原则：联合索引未从最左列开始使用（如索引(a, b)，但查询条件仅使用b）。

2. 对列进行运算或函数操作：如WHERE YEAR(create_time) = 2023。

3. 使用通配符%开头：如WHERE name LIKE '%abc'。

4. 类型转换：如字符串列使用数字查询（WHERE id = '123'可能失效）。

5. OR条件不当：若OR两侧的列不全有索引，可能全表扫描。

6. 数据量过少：优化器可能认为全表扫描更快。

7. 使用!=或NOT IN：某些情况下无法利用索引。

五、底层原理（B+树）

• B+树结构：

  • 非叶子节点仅存键值（索引列）和子节点指针。

  • 叶子节点存储数据（InnoDB中存主键值或完整数据行）。

  • 叶子节点通过双向链表连接，支持高效范围查询。

• 优势：

  • 树高度低：减少磁盘IO次数。

  • 范围查询高效：顺序访问叶子节点。

  • 数据有序：天然支持排序。

六、何时使用索引？

1. 适合场景：

   • 高频查询的列（如用户ID、订单号）。

   • 高基数（Cardinality）列（值唯一或接近唯一）。

   • 联表查询的关联列（如JOIN条件）。

   • 频繁排序或分组的列。

   • 覆盖索引优化查询性能。

2. 避免使用索引的场景：

   • 数据量极小的表（全表扫描更快）。

   • 频繁更新的列（维护索引的代价高）。

   • 低基数列（如性别、状态等重复值多的列）。

   • WHERE条件中几乎不使用的列。

   • 大文本或二进制字段（可用前缀索引或全文索引）。

七、使用索引的核心原则

• 使用索引的核心原则：以空间换时间，权衡查询性能与写入开销。

• 优化建议：

  • 使用EXPLAIN分析查询执行计划。

  • 避免过度索引，定期清理无用索引。

  • 优先选择联合索引，而非单列索引。

八、为什么优先选择联合索引

在 MySQL 中，优先选择联合索引（复合索引）而非单列索引的核心原因是联合索引能更高效地覆盖更多查询场景，减少磁盘 I/O 次数，提升查询性能。

联合索引的本质与优势

1. 索引覆盖范围更广

联合索引是将多个列按顺序组合成一个索引（如 (col1, col2, col3)），其底层结构（B + 树）会按列顺序排序。

• 优势：
  • 可直接命中 WHERE 条件中包含索引前列的查询（如 WHERE col1=? AND col2=?），无需回表（即无需通过聚集索引二次查询数据行）。
  • 若查询字段全在索引列中（覆盖索引），则直接从索引树获取结果，效率极高。

示例：

CREATE TABLE orders (id INT, user_id INT, order_time DATETIME, amount DECIMAL);

• 单列索引 INDEX idx_user (user_id)：仅支持 WHERE user_id=? 的查询。
• 联合索引 INDEX idx_user_time_amount (user_id, order_time, amount)：
  • 支持 WHERE user_id=?（用第 1 列）、WHERE user_id=? AND order_time=?（用前 2 列）、WHERE user_id=? AND order_time=? AND amount=?（用全部列）。
  • 若查询为 SELECT order_time, amount FROM orders WHERE user_id=123，联合索引可直接返回结果（覆盖索引），无需访问数据行。

2. 减少索引数量，降低维护成本

• 单列索引需要为每个字段单独创建索引（如 user_id、order_time、amount 各建一个索引），而联合索引仅需一个索引即可覆盖多个字段的组合查询。
• 维护成本：
  • 每个索引都会占用额外的磁盘空间。
  • 插入 / 更新 / 删除数据时，所有相关索引都需要同步更新。联合索引数量更少，可减少维护开销。

3. 利用索引最左匹配原则

联合索引遵循 最左匹配原则：查询条件必须包含索引的最左前列，才能触发索引。

• 例如，索引 (col1, col2, col3) 可匹配以下查询：
  • WHERE col1=?
  • WHERE col1=? AND col2=?
  • WHERE col1=? AND col2=? AND col3=?
• 单列索引无法组合多个条件触发索引，而联合索引可通过一次扫描满足多种组合查询。

单列索引的局限性

1. 无法优化组合查询

单列索引仅针对单个字段生效，若查询涉及多个字段（如 WHERE col1=? AND col2=?），数据库可能：

• 无法使用任何索引，只能全表扫描。
• 尝试使用多个单列索引（如 col1 和 col2 的索引），但 MySQL 5.6 之前不支持对多个单列索引做联合扫描（5.6 之后支持索引下推，但效率仍低于联合索引）。

2. 可能导致回表次数增加

• 若查询需要返回非索引列的数据，单列索引需先通过索引找到数据行的主键，再回表查询聚集索引获取完整数据。
• 联合索引若包含查询所需的所有列（覆盖索引），则无需回表，减少 I/O 次数。

3. 索引碎片化更严重

多个单列索引会导致索引文件碎片化更严重，尤其是在频繁写入的表中，可能影响查询性能。

何时选择联合索引？

1. 查询条件包含多个字段

• 常见场景：WHERE 子句包含 AND 连接的多个列（如 user_id 和 order_time）。
• 原则：将查询条件中最常用、过滤性最强的列放在联合索引的最左侧（遵循 索引选择性原则）。

2. 需要覆盖索引查询

若查询语句只需要索引列的数据（如 SELECT col2, col3 FROM table WHERE col1=?），联合索引可直接返回结果，避免回表。

3. 排序或分组操作依赖索引列

• 若查询包含 ORDER BY col1, col2 或 GROUP BY col1, col2，且 col1、col2 是联合索引的前两列，则数据库可直接利用索引的有序性完成排序 / 分组，避免额外的文件排序。

四、何时使用单列索引？

虽然联合索引更优，但以下场景适合单列索引：

1. 查询条件仅涉及单个字段

• 例如，高频查询 WHERE email=?，此时单列索引 INDEX idx_email (email) 更高效。

2. 字段过滤性极高

• 若某个字段的唯一值比例极高（如主键、唯一索引），单列索引足以快速定位数据，无需联合索引。

3. 避免索引宽度过大

• 联合索引的列数越多，索引文件越大。若某列数据类型较长（如 TEXT），加入联合索引会导致索引页存储的条目减少，降低索引效率。此时可拆分为单列索引或较短的联合索引。

五、总结：联合索引 vs 单列索引