mysql中的索引

最近有点摆烂，两门考试和六级压力有点大。没怎么更新，唉，还有就是女人（）。

胡乱投了简历，也约上面试了，开始女娲补天呜呜呜。公司是图数据库方面的，所以主播恶补一下mysql。

众所周知，数据库中的数据是按照表的形式存储的，可以想象成excel表，那么为了方便查询，我们给我们要查询的列，叫做索引，常见的索引一般是id,比如我们就查询id为1的人的姓名，年龄等，比较方便，更可以将id设置为主键（唯一且不为空）

1. 聚簇索引 (Clustered Index)

• 定义：聚簇索引是表数据的物理存储顺序与索引顺序一致的索引。每个表只能有一个聚簇索引，通常由主键定义。
• 就是按照索引的顺序存放数据，id从0到1000，数据就这么存放，不看其他的列
• 特点：
  • 表数据按聚簇索引的键值物理排序存储。
  • 数据和索引存储在同一结构中（通常是B+树）。
  • 查询效率高，尤其是范围查询和主键查找。
• 用途：用于快速检索数据，适合频繁查询的列（如主键）。
• 示例：

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

  • 说明：id列的索引是聚簇索引，表数据按id排序存储。
• 注意：InnoDB引擎默认创建聚簇索引（基于主键）；若无主键，则选择第一个非空唯一索引，或生成隐式RowID。

---

2. 主键索引 (Primary Key Index)

• 定义：主键索引是定义在表的主键列上的特殊索引，确保每行数据的唯一性，且不能为NULL。
• 其实就是聚簇索引，不过要求索引是主键
• 特点：
  • 自动创建聚簇索引（在InnoDB中）。
  • 每表只能有一个主键索引。
  • 强制唯一性和非空约束。
• 用途：保证数据唯一性，加速主键查询。
• 示例：

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

  • 说明：id列的主键索引确保每个id唯一，且数据按id物理排序。
• 注意：主键索引是聚簇索引的子集，但术语上常分开讨论。

---

3. 二级索引 (Secondary Index)

• 定义：二级索引是非聚簇索引，存储索引键值和指向表数据的指针（通常是主键值）。

• 单独给某一列再设一个索引，方便where,group by等命令的优化，mysql自动调用

• 特点：

  • 与聚簇索引分离，数据存储顺序与索引无关。
  • 查询可能需要“回表”（通过主键查找实际数据）。
  • 允许多个二级索引。

• 用途：优化非主键列的查询，如WHERE、JOIN条件中的列。

• 示例：

  CREATE INDEX idx_name ON users(name);

  • 说明：name列上的二级索引加速WHERE name = 'Alice'查询。

• 注意：二级索引占用额外存储空间，更新数据时需同步维护。

---

4. 普通索引 (Normal Index)

• 定义：普通索引是基本的非唯一索引，用于加速查询，不强制唯一性或非空约束。
• 特点：
  • 属于二级索引，存储键值和主键指针。
  • 不限制列值重复或NULL。
• 用途：提高常用查询列的性能，如频繁出现在WHERE或ORDER BY中的列。
• 示例：

  CREATE INDEX idx_age ON users(age);

  • 说明：age列的普通索引优化SELECT * FROM users WHERE age > 30。
• 注意：普通索引适合高选择性的列（重复值少）。

---

5. 唯一索引 (Unique Index)

• 定义：唯一索引确保索引列的值唯一，允许NULL（在InnoDB中，NULL视为不同值）。
• 特点：
  • 属于二级索引，但强制唯一性约束。
  • 可包含NULL，但每个NULL值视为唯一。
  • 每表可有多个唯一索引。
• 用途：确保数据唯一性（如邮箱、身份证号），并加速查询。
• 示例：

  CREATE UNIQUE INDEX idx_email ON users(email);

  • 说明：email列的唯一索引防止重复邮箱值。
• 注意：与主键索引不同，唯一索引允许NULL，且不一定是聚簇索引。

---

6. Hash索引

• 定义：Hash索引基于哈希表实现，通过哈希函数将键值映射到固定位置。
• 特点：
  • 仅支持等值查询（如=、IN），不支持范围查询（如>、<）。
  • 查找速度极快（O(1)复杂度），但不适合排序或范围操作。
  • Memory引擎支持Hash索引，InnoDB仅在特定场景（如自适应哈希索引）使用。
• 用途：适合高频等值查询的场景，如内存表中的键值查找。
• 示例：

  CREATE TABLE mem_table (id INT, name VARCHAR(50), INDEX USING HASH (name))
  ENGINE = MEMORY;

  • 说明：name列使用Hash索引，优化SELECT * FROM mem_table WHERE name = 'Alice'。
• 注意：Hash索引占用空间较小，但功能受限，InnoDB不默认支持。

---

7. B+树索引

• 定义：B+树索引是MySQL中最常用的索引结构，基于B+树数据结构存储索引。
• 特点：
  • 所有非叶子节点仅存储键值，叶子节点存储键值和数据指针（或数据本身）。
  • 支持等值查询、范围查询、排序和分组操作。
  • InnoDB和MyISAM引擎默认使用B+树索引。
  • 叶子节点通过双向链表连接，适合顺序扫描。
• 用途：广泛用于主键索引、唯一索引、普通索引和二级索引，适合大多数查询场景。
• 示例：

  CREATE INDEX idx_city ON users(city);

  • 说明：city列的B+树索引优化SELECT * FROM users WHERE city = 'Beijing' ORDER BY city。
• 注意：B+树索引适合高选择性列，范围查询效率高，但插入/更新可能导致树分裂，影响性能。

---

8. 区别与联系

索引类型	是否聚簇	唯一性	允许NULL	支持查询类型	存储结构	适用引擎
聚簇索引	是	是	否	等值、范围、排序	B+树	InnoDB
主键索引	是	是	否	等值、范围、排序	B+树	InnoDB, MyISAM
二级索引	否	可选	是	等值、范围、排序	B+树	InnoDB, MyISAM
普通索引	否	否	是	等值、范围、排序	B+树	InnoDB, MyISAM
唯一索引	否	是	是	等值、范围、排序	B+树	InnoDB, MyISAM
Hash索引	否	可选	是	等值	哈希表	Memory, InnoDB*
B+树索引	可选	可选	可选	等值、范围、排序	B+树	InnoDB, MyISAM

*注：InnoDB的自适应哈希索引由引擎自动管理，用户无法直接创建。

---

9. 使用场景与优化建议

• 聚簇索引：优先用于主键或高频查询的列，保持主键递增（如自增ID）以减少分裂。
• 主键索引：选择短且唯一的列（如INT型ID），避免过长字符串。
• 二级索引：为WHERE、JOIN、ORDER BY中常用的列创建，注意覆盖索引优化回表。
• 普通索引：适合高选择性列，避免在低选择性列（如性别）上创建。
• 唯一索引：用于业务上要求唯一性的列，如用户名或邮箱。
• Hash索引：适合内存表或等值查询场景，避免范围查询。
• B+树索引：通用选择，适合大多数查询场景，定期优化索引（OPTIMIZE TABLE）。

---

10. 注意事项

• 索引维护成本：添加索引会增加存储空间和更新开销，需权衡查询与写入性能。
• 覆盖索引：通过索引直接返回所需列，避免回表：

  SELECT name FROM users WHERE name = 'Alice';

  • 若name有索引，可直接从索引获取数据。
• 索引选择性：高选择性列（重复值少）更适合建索引。
• 分析性能：使用EXPLAIN检查索引使用情况：

  EXPLAIN SELECT * FROM users WHERE city = 'Beijing';

• 碎片整理：定期运行OPTIMIZE TABLE清理索引碎片。

---

11. 总结

MySQL的索引类型各有特点，聚簇索引和主键索引决定数据存储方式，二级索引和普通索引优化查询，唯一索引保证数据约束，Hash索引适合等值查询，B+树索引是默认高效结构。选择合适的索引类型需根据查询模式、数据分布和引擎特性综合考虑。

如果您需要更具体的索引创建示例、优化案例或对某类索引的深入分析，请告诉我！

参考文献：

• MySQL官方文档（索引部分）
• InnoDB存储引擎索引实现
• B+树与Hash索引的性能对比