MySQL索引详解

---

1. 索引是什么？为什么需要索引？

1.1 索引是什么？

索引就像书的目录。你想查一本书中的内容时，不会一页页翻，而是直接通过目录定位到具体的章节。这就是索引的作用——加速查询。

在数据库中，索引是一种特殊的数据结构，能快速定位数据，而不用逐行扫描整个表。

---

1.2 为什么需要索引？

性能提升。假设有 1 亿条数据，如果没有索引，查询的时间复杂度是 O(n)，需要扫描所有行。如果有索引，复杂度可以降为 O(log n) 或更低。

举例：

• 查找学生信息：
  • 无索引：翻阅所有学生数据，类似“找针的过程”。
  • 有索引：直接找到针的位置。

---

2. 索引的优缺点

2.1 优点

1. 查询更快：通过索引可以快速定位数据，减少 I/O 操作。
   • 举例：查找 ID 为 100 的用户，索引会直接定位到存储位置，而不需要全表扫描。
2. 支持排序：索引本质上是有序的，能高效完成 ORDER BY、GROUP BY 操作。
3. 提高 JOIN 查询效率：在关联查询中，通过索引能快速匹配数据。

---

2.2 缺点

1. 写性能下降：
   • 原因：插入、更新或删除数据时，需要同时维护索引。
   • 举例：新增数据时，需要更新索引中对应的树结构。
2. 占用空间：
   • 每个索引都会额外占用存储空间，索引多了会增加存储成本。
3. 索引可能失效：
   • 如果 SQL 写法不当，可能导致索引失效，无法发挥作用。

---

3. 索引底层数据结构

数据库索引的核心在于底层数据结构，常见的有 Hash 表 和 B+ 树。

3.1 Hash 表

• 特点：通过哈希函数快速定位。
• 优点：速度快，适合等值查询（=）。
• 缺点：
  • 无法排序：BETWEEN 和 ORDER BY 无法使用。
  • 哈希冲突：可能出现性能下降。

3.2 B+ 树（MySQL 索引的核心结构）

• 特点：
  • 叶子节点存储所有数据，非叶子节点存储索引。
  • 所有叶子节点通过链表连接，方便范围查询。
• 优点：
  1. 支持排序：从叶子节点的链表中可以直接按顺序访问数据。
  2. 范围查询效率高：BETWEEN、>、< 等条件查询非常高效。
• 应用：
  • MySQL 大多数存储引擎（如 InnoDB）都基于 B+ 树实现。

---

4. 索引类型总结

4.1 主键索引

• 主键索引是聚簇索引，数据物理存储顺序和索引顺序一致。
• 特点：每张表只能有一个主键索引。
• 示例：

  CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(50)
  );

---

4.2 二级索引

• 普通索引，也叫非聚簇索引，存储的是字段值和主键值。
• 特点：查询时需要通过主键回表。
• 示例：

  CREATE INDEX idx_name ON users (name);

---

4.3 覆盖索引

• 覆盖索引指查询字段只需要从索引中获取，而无需回表。
• 示例：

  SELECT name FROM users WHERE name = 'John';

  如果 name 有索引，查询时不会回表，效率更高。

---

4.4 联合索引

• 联合索引是由多个字段组成的复合索引。
• 最左前缀原则：
  • 查询条件必须从索引的最左字段开始匹配。
  • 示例：联合索引 (name, age)：
    • 可用：WHERE name = 'John' 或 WHERE name = 'John' AND age = 25
    • 不可用：WHERE age = 25

---

4.5 聚簇索引与非聚簇索引

聚簇索引

• 数据的存储顺序与索引一致，查询时直接找到数据。
• 适用场景：主键查询或范围查询。
• 缺点：更新和插入的性能较差。

非聚簇索引

• 索引存储和数据存储分开，需要回表查询数据。
• 适用场景：辅助索引。

---

5. 正确使用索引的一些建议

5.1 选择合适的字段创建索引

• 索引字段应满足以下条件：
  • 经常出现在 WHERE 或 JOIN 的条件中。
  • 字段值的区分度高（基数大）。

5.2 被频繁更新的字段应慎重建立索引

• 索引的维护成本高，频繁更新的字段可能导致写性能下降。

5.3 限制每张表的索引数量

• 每张表的索引不宜超过 5 个。
• 原因：
  • 索引过多会影响插入、更新性能。
  • 查询优化器在选择索引时也会耗费更多时间。

5.4 联合索引优于单列索引

• 单列索引效率低，优先考虑联合索引。
• 示例：在联合索引 (name, age) 下，以下查询都能利用索引：

  SELECT * FROM users WHERE name = 'John';
  SELECT * FROM users WHERE name = 'John' AND age = 25;

---

6. 避免索引失效的常见问题

6.1 在 WHERE 子句中对索引字段进行函数转换

• 错误：

  SELECT * FROM users WHERE DATE(created_at) = '2023-01-01';

  • 索引失效，因为函数会导致索引被整体扫描。
• 正确：

  SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02';

---

6.2 数据类型不一致

• 错误：

  SELECT * FROM users WHERE phone_number = 1234567890; -- phone_number 是字符串类型

  • 索引失效，因为类型不一致。
• 正确：

  SELECT * FROM users WHERE phone_number = '1234567890';

---

7. 如何分析 SQL 是否使用索引

7.1 使用 EXPLAIN 查看执行计划

• 示例：

  EXPLAIN SELECT * FROM users WHERE name = 'John';

• 关键字段解析：
  • type：查询类型，最好为 index 或 ref。
  • key：使用的索引名称。
  • rows：扫描的行数，越少越好。

---

8. 实际应用案例总结

1. 覆盖索引优化查询

   • 查询字段和索引字段一致，无需回表，减少 I/O。
   • 示例：SELECT name FROM users WHERE name = 'John';

2. 联合索引代替多个单列索引

   • 优化：将 name 和 age 建成联合索引 (name, age)。

3. 避免无效的条件导致索引失效

   • 函数转换、数据类型不一致等都会导致索引失效。

---