1.索引分类

1.1 主键索引（Primary Key Index）

• 特点：
  • 自动为主键列创建的唯一索引。
  • 不允许为空（NOT NULL）且保证值唯一。
  • 主键可以是联合主键，由多个字段共同组成主键。
  • 每个表有且只能有一个主键索引，若没有显示创建，mysql会自动创建隐式主键。
• 作用：
  • 唯一标识每一行数据，常用作二级索引的回表目标。
• 存储引擎差异：
  • 在 InnoDB 中，主键索引是一种聚簇索引，存储完整的行数据。

1.2 唯一索引（Unique Index）

• 特点：
  • 保证索引列中的值唯一，可以有多个。
  • 唯一索引也可以由多个字段共同组成。
  • 可以包含 NULL 值，但多个 NULL 不冲突。
• 作用：
  • 用于约束和优化查询性能，例如邮箱、用户名字段等。

1.3 普通索引（Secondary Index 或 Non-Unique Index）

• 特点：
  • 最常见的索引类型，没有唯一性约束。
  • 可以包含重复值和 NULL。
• 作用：
  • 加速常规查询，用于非唯一列的查找。

1.4 组合索引（Composite Index 或 Multi-Column Index）

• 特点：
  • 在多个列上建立的索引。
  • 查询时可利用索引的 最左前缀原则，即从索引定义的最左列开始依次匹配。
• 作用：
  • 用于经常同时查询多个列的场景，减少多个单列索引的开销。

1.5 全文索引（Full-Text Index）

• 特点：
  • 专用于全文搜索，适合大文本字段的查找。
  • 常用于 CHAR、VARCHAR 和 TEXT 类型的列。
  • InnoDB 从 MySQL 5.6 开始支持全文索引。
• 作用：
  • 快速进行模糊匹配、关键词查询。

1.6 空间索引（Spatial Index）

• 特点：
  • 用于地理空间数据（GEOMETRY 类型）的快速查询。
  • 仅支持 MyISAM 存储引擎（InnoDB 仅支持部分功能）。
• 作用：
  • 提高地理信息系统（GIS）相关查询的性能。

1.7 聚簇索引（Clustered Index）

• 特点：
  • 数据行的物理存储顺序与索引的顺序一致。
  • 在 InnoDB 中，主键索引是聚簇索引。
  • 每个表只能有一个聚簇索引。
• 作用：
  • 提高基于主键的查询性能。

1.8 非聚簇索引（Non-Clustered Index）

• 特点：
  • 索引和数据存储分离，叶子节点存储的是主键或指针。
  • 二级索引（Secondary Index）是非聚簇索引的典型实现。
• 作用：
  • 提高非主键列的查询性能。

1.9 哈希索引（Hash Index）

• 特点：
  • 使用哈希表存储键值对，仅支持精确匹配查询。
  • 不支持范围查询、排序。
  • Memory 存储引擎支持显式哈希索引。
• 作用：
  • 提供高效的等值查询性能。

1.10 覆盖索引（Covering Index）

• 特点：
  • 查询所需的所有列都可以直接从索引中获取，无需回表。
  • 并不是一种单独的索引类型，而是查询优化的一种结果。
• 作用：
  • 提高查询性能，减少回表操作。

1.11 前缀索引（Prefix Index）

• 特点：
  • 针对字符串列的前部分字符建立的索引，减少索引大小。
  • 适合长字符串字段。
• 作用：
  • 节约索引存储空间，适用于前缀唯一性较高的场景。

2.为什么 MySQL 一定要有主键？

在 MySQL 中，主键是表的一个重要特性，它用于唯一标识每一行数据。即使在创建表时没有显式指定主键，MySQL 依然会为表生成一个隐式主键。以下是一些关键点，解释了为什么 MySQL 需要主键，以及隐式主键的作用：

2.1 主键的作用

• 唯一标识：主键保证了每一行数据的唯一性，确保没有两行数据具有相同的主键值。
• 索引：主键会自动为其创建一个唯一索引，这样可以加快基于主键的查询和操作。
• 数据完整性：主键约束防止了重复数据的插入，从而维护了数据的一致性和完整性。

2.2 隐式主键的生成

• 没有显式主键时：如果在创建表时没有指定主键，MySQL 会为每个表自动创建一个隐式主键。这个隐式主键通常是一个名为 PRIMARY 的隐藏列，该列的数据类型是 BIGINT，并且会自动递增。
• 隐式主键的用途：它用于唯一标识每一行数据，尤其是在没有其他显式唯一标识符时。如果你在查询或更新数据时没有明确指定主键，MySQL 会利用这个隐式主键来进行操作。

2.3 隐式主键的好处

• 避免数据重复：即使你没有手动创建主键，隐式主键依然能防止插入重复数据。
• 提高查询效率：隐式主键会为每一行数据创建一个索引，从而加快基于该主键的查询速度。
• 数据一致性：隐式主键在数据库内部用于维护数据的唯一性和一致性，即使用户没有显式指定主键。

2.4 隐式主键的限制

• 不适合所有场景：隐式主键的自动创建有时不如显式主键更具灵活性。如果表中已有其他列是唯一标识符，显式创建主键可以更清晰地表达意图。
• 无法被更改：隐式主键的生成方式是固定的，用户无法直接修改或删除它。

3. 二级索引的查找是否依赖于主键索引？

是的，二级索引的查找通常是依赖于主键索引的。以下是一些详细的解释：

3.1 二级索引的定义

在 MySQL 中，二级索引（或称为非主键索引）是指在表中除了主键索引之外的其他索引。它们可以帮助加快基于某些列的查询速度，但在查找数据时，二级索引本身不能直接定位到数据行的具体位置。

3.2 二级索引如何工作

当 MySQL 使用二级索引查找数据时，它会经历两个步骤：

• 查找二级索引：首先，通过二级索引查找到满足条件的索引条目，并获取相关的主键值（例如，索引中的“键值”）。
• 使用主键索引查找数据：然后，使用主键索引来查找具体的数据行，因为主键索引包含了行数据的实际位置（即聚集索引）。这个步骤被称为回表操作，因为它需要根据二级索引中存储的主键值，再次访问主键索引来检索完整的数据。

3.3 为什么二级索引需要依赖主键索引

主键索引是聚集索引，这意味着表中的数据行是按照主键值存储的。二级索引不直接存储数据行，而是存储主键值和索引列的关系。因此，二级索引查找数据时必须通过主键索引来获取完整的行数据。

这种依赖关系的工作流程如下：

1. 查询条件匹配：二级索引帮助快速定位符合查询条件的主键值。
2. 回表查询：通过主键索引，MySQL 使用这些主键值来回到实际数据行，完成数据的检索。

4. 如何解决二级索引“回表”的问题

4.1 使用覆盖索引

覆盖索引（Covering Index）是一种索引，它包含了查询所需的所有列数据。这样，在查询时，MySQL 可以直接从索引中获取结果，而无需访问主键索引，从而避免了回表操作。通过创建覆盖索引，你可以显著减少回表操作的次数，提高查询性能。

示例： 假设有一个表 users，结构如下：

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

如果你查询 name 和 email 列，可以直接使用覆盖索引：

SELECT name, email FROM users WHERE name = 'Alice';

此查询会直接从 name, email 索引中获取数据，而无需回表。