MySQL 索引（二）

• 什么是主键索引？
• 什么是辅助索引？
• 什么是唯一索引？
• 回表是什么意思？

在上一篇文章中，我们讲的概念比较多，讲了很多个索引的数据结构，总体上比较偏理论。那这一篇文章，我们会讲一些比较偏实战性的内容。

在 MySQL 中，索引是在存储引擎层实现的，所以不同的存储引擎中，索引的工作方式也是不一样的。并且同一类型的索引在不同的存储引擎中的底层实现也可能是不一样的。因为我们最常用的存储引擎是 InnoDB 存储引擎，所以下面的内容，我们就以 InnoDB 为例，来继续分析索引。

主键索引

在讲具体的概念之前，我们先来看一个例子。

假设有一个用户表 users ：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
) ENGINE=InnoDB;

表中有以下数据：

id 是主键，所以数据按照 id 的顺序存储在 B+ 树的叶子节点中。

然后我们来画一下这棵 B+ 树：

在上篇文章中，我们提到，在 B+ 树中，叶子节点存储了完整的行记录，因此通过主键查询可以直接获取完整的数据。实际上，我们之前提到的这种非叶子节点存储索引、叶子节点存储完整数据的 B+ 树，就是依据主键索引来构造的，主键索引又叫聚簇索引。

主键索引的叶子节点不仅存储了索引值，还存储了完整的行数据，如上图所示。

所以基于这个特点，通过主键查询数据时速度非常快，因为直接读取索引叶子节点就可以获得完整的数据。

然后我们再来往下看，如果我想往这个表中插入一条数据： id=5, name=孙七, age=40, city=杭州。

这是一种顺序插入的情况：id=5 的数据直接插入到主键为 4 的后面，不需要调整聚簇索引树的结构，而且整体效率较高。

插入后的结构是这样的，非常简单：

但是如果我想插入 id = 2.5, name = 周八, age = 28, city = 南京 这条数据呢？

那这样就是非顺序插入的情况了，那么就会发生这样的复杂过程：

id=2.5 的数据需要插入到 id=2 和 id=3 之间。由于聚簇索引要求主键有序，所以在插入的时候，MySQL需要先通过聚簇索引树的结构，定位到 id=2 和 id=3 之间的插入点，然后数据页需要为 id=2.5 的这条数据腾出空间，这种操作可能就会导致 页分裂。

页分裂的过程是这样的，先申请一个新的数据页，然后把一部分数据挪动到这个新的数据页中。页分裂的操作，影响了数据页的利用率，因为之前存储在一个数据页中的数据现在需要存储到两个数据页中。

有页分裂，也就会有页合并。如果相邻的两个数据页中删除了部分数据，为了提高数据页的利用率，就会产生页合并。页合并的过程实际上就是页分裂的逆过程。

调整之后的聚簇索引树就是这样的了（省略了数据）：

比较直观的是，树的结构发生了复杂的变化。

所以当在聚簇索引树中执行非顺序插入的时候，会有以下这三个缺点：

1. 导致页分裂：页分裂是指当前数据页没有足够的空间存储新数据时，需要将一部分数据移动到新的页中，从而保持主键的有序性。页分裂会影响数据页的利用率。
2. 数据需要重新排列：聚簇索引中，所有行数据都是按主键顺序存储的，因此新数据插入后，可能需要重新排列数据，增加了插入成本。
3. 磁盘 I/O 开销高：页分裂和数据重排会涉及较多的磁盘 I/O 操作，尤其在数据量较大的情况下，性能下降会更明显。

那我们应该如何提高插入数据的效率，从而来避免不必要的页分裂呢？

答案就是，使用自增主键，让主键值自增，保证插入数据的顺序性，避免页分裂的发生。

因为自增主键的值是从小到大递增的，而且每次插入新数据，主键的值都会大于之前的值。所以每次插入的数据都会直接追加到索引的最后一个叶子节点，这样做的结果，就是不需要调整原有数据的顺序，不会导致索引树的复杂变化。

这一章节，我们主要讲了主键索引的概念，并且结合具体的场景，了解了主键索引树。同时还学习了页分裂的概念，以及如何避免不必要的页分裂。

辅助索引

看完上文的描述之后，相信你已经对主键索引有了一个整体的了解了。

那么既然有主键索引，也就会有辅助索引。下面我们再来学习一下辅助索引的内容。

辅助索引是与聚簇索引相对的一种索引结构。辅助索引的特点是 索引和数据是分离存储的，索引的叶子节点只存储索引字段和主键值，需要通过主键值去查找完整的行数据。

在 MySQL 中，辅助索引也被称为 二级索引。

同样的，我们还是结合具体的例子来讲解辅助索引的有关知识。

假设我们有一个用户表 users ，结构如下：

此时，对应的聚簇索引树是这样的：

每个叶子节点都存储了完整的数据，我们在上个章节已经提到过了。

然后我们在 name 列上创建一个辅助索引：

CREATE INDEX idx_name ON users(name);

当我们创建这个辅助索引之后，同时一棵辅助索引树也会被创建出来，辅助索引树是这样的：

在这棵树中，我们可以看到，索引键是 name ，叶子节点并没有存储完整的数据，而是仅存储了 user_id 。

现在，我们想要查询有关用户 Bob 的所有信息：

SELECT * FROM users WHERE name = 'Bob';

那么查询过程是这样的，首先在辅助索引树中进行查询，通过辅助索引 idx_name 查找到 Bob 的主键 user_id = 2 。然后使用 user_id = 2 去聚簇索引树中定位实际的数据行，从而获取完整的数据。

非常明显的是，这个过程涉及到了两个索引树的查询，分别是辅助索引树和聚簇索引树。查询的时候，需要先到辅助索引树中拿到主键，然后再用这个主键去聚簇索引树中进行二次查询。实际上，这个两次查询的过程，就叫做回表。

下面，我们再来看一种情况。

如果我们只想查询 Bob 的 user_id：

SELECT user_id FROM users WHERE name = 'Bob';

此时又会发生什么呢？

同样，首先还是去辅助索引树中进行查询，但是与之前不同的是，user_id 这个字段恰好可以在辅助索引树中查找到，所以就不需要再去聚簇索引中查询了。

基于我们列举的这个例子，我们来总结一下辅助索引的特点：

1. 叶子节点不包含实际数据：叶子节点存储的是数据位置（如主键值），而不是数据本身。
2. 减少部分查询的回表：如果查询的字段在辅助索引树中，就不需要回表。
3. 查找效率依赖于回表：如果查询的字段不在辅助索引树中，查询需要通过辅助索引找到主键值后再访问表中的实际数据，这个过程称为“回表”。

好啦，关于辅助索引的知识，我们就先讲到这里。在这个章节中，我们结合一个案例，讲了辅助索引的概念，以及辅助索引树是怎样的、什么是回表，还总结了辅助索引的特点。

唯一索引

现在，相信你对主键索引和辅助索引都已经有了全新的认识。下面呢，我们再来讲一个概念，它叫做唯一索引。

唯一索引 是一种特殊类型的索引，它要求索引列中的值必须唯一，这意味着在同一个表中，索引列的每个值都不能重复（NULL 除外，允许多个 NULL 值）。

下面是创建唯一索引的语句：

如果我们想在单列上创建一个唯一索引，MySQL 语句是这样的：

CREATE UNIQUE INDEX idx_email ON users(email);

如果我们想在多列上创建多个唯一索引，那么 MySQL 语句是这样的：

CREATE UNIQUE INDEX idx_user_name ON users(first_name, last_name);

或者，我们也可以在建表的时候，通过定义表结构来创建唯一索引：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE, -- 创建唯一索引
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    UNIQUE (first_name, last_name) -- 多列唯一索引
);

我们还是围绕一个具体的例子展开，这样的话，印象就会比较深刻一些。

假设我们有一个 users 表，其中 email 列创建了唯一索引，表结构是这样的：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE, -- 唯一索引
    name VARCHAR(50)
);

表中现在有以下数据：

当我们正常插入一条数据的时候，结果就是插入成功。

INSERT INTO users (email, name) VALUES ('charlie@example.com', 'Charlie');

但是，如果我们想要插入这条数据，结果就会报错。这是因为我们在 email 列上创建了唯一索引，索引列已经存在了相同的值。

INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice2');

错误提示是这样的：

Duplicate entry 'alice@example.com' for key 'email'

如果我们想在索引列上插入多个 NULL 值，也是可以插入的。

INSERT INTO users (email, name) VALUES (NULL, 'David');
INSERT INTO users (email, name) VALUES (NULL, 'Eve');

那么最终的数据表就是这样的：

好，看完了这个例子，我们再来总结一下唯一索引的特点。

1. 首先，唯一索引可以建立在单列或多列上。
2. 其次，唯一索引确保列的值在表中是唯一的。如果尝试在唯一索引列中插入重复的值，会报错。
3. 然后，在 MySQL 中，唯一索引允许多个 NULL 值存在，因为 NULL 被认为是未知值，无法比较是否相等。
4. 最后，唯一索引不仅可以确保数据的唯一性，还能加速查询操作，有点类似于普通索引的性能优化。

通过我们对唯一索引的特点的总结，我们也就可以推断出唯一索引的应用场景了。

唯一索引一般用在需要确保唯一性的字段上，比如邮箱、手机号、身份证。

至此，我们已经学习了主键索引、辅助索引和唯一索引。这三种索引也算是比较重要的索引了，而且在面试中也会经常被问到，所以我们一定要对它们有一个清晰的认知。

思考问题

在学习完主键索引、辅助索引和唯一索引的概念之后，我们再来思考下面这个问题：

如果从存储空间的角度来看，假设一张表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

我就直接说答案了。

使用自增字段作为主键，同时将身份证号作为一个普通字段创建唯一索引。

在第一个章节中，，我们已经学习了 MySQL InnoDB 引擎中主键索引存储的特点：数据以主键的顺序存储，并且辅助索引会引用主键值来定位对应的数据行。

因此，主键的大小会直接影响：

1. 数据页中存储的行数，因为行大小的增加会占用更多的存储空间。
2. 辅助索引的大小，因为辅助索引需要存储主键值作为指针。

我们假设表结构是这样的：

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键
    id_card CHAR(18) NOT NULL UNIQUE,      -- 身份证号
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB;

如果我们使用身份证号作为主键，因为身份证号的长度为 18 个字符，使用 CHAR(18)，每个字符占用 1 字节，所以主键大小就是18 字节。另外，从辅助索引存储的开销来看，由于辅助索引也会存储主键值，所以它们会额外存储 18 字节的主键值。

如果我们使用自增主键，自增主键的长度为 BIGINT 类型，所以占用 8 字节，所以主键大小为 8 字节。从辅助索引存储的开销来看，每个辅助索引只需要存储 8 字节的主键值。

因此，使用身份证号作为主键时，不仅主键索引本身占用更多空间，每个辅助索引的存储开销也更高。

我们再来从页存储利用率的角度来看，InnoDB 的数据页大小通常为 16 KB。如果主键值占用的空间较大，比如身份证号 18 字节，那么每页能存储的记录数会减少。如果使用自增主键，那么每页就可以存储更多的记录。所以，在使用自增主键的情况下吗，页利用率更高。页利用率更高也就意味着更少的磁盘页被占用，节省了存储空间，同时也意味着会有更少的磁盘 I/O，查询性能也会更高。

最后，我们再来从索引的更新和维护成本来分析，在实际应用中，身份证号作为主键可能需要频繁地参与各种操作，比如索引的更新。如果主键较大，也就意味着更新索引时需要操作更多的数据，就会会带来额外的性能开销。

综上所述，我们分别从存储空间、页利用率以及更新和维护成本的角度进行了一个全面的分析，结论就是使用自增字段来作为主键。

总结

在这一篇文章中，我们基于 InnoDB 引擎介绍了主键索引、辅助索引以及唯一索引的概念，并且我们还了解了主键索引树和辅助索引树，知道了什么是回表。在文章的末尾，我们还思考了一个问题：在一张表中有唯一字段的时候，应该采用自增字段作为主键还是采用唯一字段作为主键。

通过我们的详细解读，相信你也收获了不少，在下一篇文章中，我还会继续讲解一些关于索引的知识，这篇文章就先介绍到这里啦。

帅地永久会员

打赏 收藏 海报挣佣金 推广链接