面试题：MySQL 的索引类型有哪些？

MySQL 支持多种索引类型，每种索引类型适用于不同的场景和查询需求。以下是 MySQL 中常见的索引类型及其特点：

---

1. 普通索引（INDEX）

• 定义：最基本的索引类型，没有任何约束。
• 特点：
  • 适用于大多数查询场景。
  • 允许重复值和空值。
• 创建语法：CREATE INDEX index_name ON table_name (column_name);
• 示例：CREATE INDEX idx_name ON users (name);

---

2. 唯一索引（UNIQUE INDEX）

• 定义：索引列的值必须唯一，不允许重复。
• 特点：
  • 适用于需要唯一性约束的列（如用户名、邮箱等）。
  • 允许空值（但只能有一个空值）。
• 创建语法：CREATE UNIQUE INDEX index_name ON table_name (column_name);
• 示例：CREATE UNIQUE INDEX idx_email ON users (email);

---

3. 主键索引（PRIMARY KEY）

• 定义：一种特殊的唯一索引，用于唯一标识表中的每一行。
• 特点：
  • 每张表只能有一个主键索引。
  • 主键列的值不能为空（NOT NULL），且必须唯一。
  • 在 InnoDB 中，主键索引是聚簇索引，决定了数据的物理存储顺序。
• 创建语法：ALTER TABLE table_name ADD PRIMARY KEY (column_name);
• 示例：ALTER TABLE users ADD PRIMARY KEY (id);

---

4. 全文索引（FULLTEXT INDEX）

• 定义：用于全文搜索的索引，支持对文本内容进行高效的关键词搜索。
• 特点：
  • 适用于 CHAR、VARCHAR 和 TEXT 类型的列。
  • 支持自然语言搜索和布尔搜索。
  • 只能用于 InnoDB 和 MyISAM 存储引擎。
• 创建语法：CREATE FULLTEXT INDEX index_name ON table_name (column_name);
• 示例：CREATE FULLTEXT INDEX idx_content ON articles (content);
• 使用示例：SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL');

---

5. 空间索引（SPATIAL INDEX）

• 定义：用于地理空间数据类型的索引（如 GEOMETRY、POINT、LINESTRING 等）。
• 特点：
  • 适用于存储地理空间数据的列。
  • 只能用于 MyISAM 和 InnoDB（MySQL 5.7+）存储引擎。
• 创建语法：CREATE SPATIAL INDEX index_name ON table_name (column_name);
• 示例：CREATE SPATIAL INDEX idx_location ON places (location);

---

6. 前缀索引（Prefix Index）

• 定义：只对列的前缀部分创建索引，以减少索引大小。
• 特点：
  • 适用于长字符串列（如 VARCHAR(255)）。
  • 需要指定前缀长度，长度过短可能导致索引选择性降低。
• 创建语法：CREATE INDEX index_name ON table_name (column_name(length));
• 示例：CREATE INDEX idx_name_prefix ON users (name(10));

---

7. 复合索引（Composite Index）

• 定义：对多个列组合创建的索引。
• 特点：
  • 适用于多列查询条件。
  • 遵循最左前缀匹配原则，查询条件必须从索引的最左列开始。
• 创建语法：CREATE INDEX index_name ON table_name (column1, column2, ...);
• 示例：CREATE INDEX idx_name_age ON users (name, age);

---

8. 哈希索引（Hash Index）

• 定义：基于哈希表实现的索引，适用于等值查询。
• 特点：
  • 适用于精确匹配查询（如 =、IN）。
  • 不支持范围查询（如 >、<、BETWEEN）。
  • 只有 Memory 存储引擎支持哈希索引。
• 创建语法：CREATE INDEX index_name ON table_name (column_name) USING HASH;
• 示例：CREATE INDEX idx_email_hash ON users (email) USING HASH;

---

9. 覆盖索引（Covering Index）

• 定义：索引包含了查询所需的所有列，查询可以直接从索引中获取数据，而不需要回表。
• 特点：
  • 减少 I/O 操作，提升查询性能。
  • 适用于查询只涉及索引列的场景。
• 创建语法：CREATE INDEX index_name ON table_name (column1, column2, ...);
• 示例：CREATE INDEX idx_name_age ON users (name, age);
• 使用示例：SELECT name, age FROM users WHERE name = 'Alice';

---

10. 自适应哈希索引（Adaptive Hash Index）

• 定义：InnoDB 存储引擎自动为频繁访问的索引页创建的哈希索引。
• 特点：
  • 由 InnoDB 自动管理，用户无法手动创建或删除。
  • 提升等值查询的性能。
• 适用场景：
  • 适用于频繁的等值查询。

---

总结

MySQL 支持多种索引类型，每种索引类型适用于不同的场景和查询需求。以下是常见索引类型的适用场景：

索引类型	适用场景
普通索引（INDEX）	大多数查询场景。
唯一索引（UNIQUE）	需要唯一性约束的列。
主键索引（PRIMARY KEY）	唯一标识表中的每一行。
全文索引（FULLTEXT）	文本内容的全文搜索。
空间索引（SPATIAL）	地理空间数据的查询。
前缀索引（Prefix）	长字符串列的部分索引。
复合索引（Composite）	多列查询条件。
哈希索引（Hash）	精确匹配查询（仅 Memory 存储引擎支持）。
覆盖索引（Covering）	查询只涉及索引列的场景。
自适应哈希索引（Adaptive Hash）	频繁的等值查询（InnoDB 自动管理）。

通过合理选择和使用索引类型，可以显著提升 MySQL 的查询性能。