MySQL「04」索引

Samson_bu
169 阅读6分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第04天，点击查看活动详情

索引的出现是为了提高数据查询的效率，它的作用就像是书籍中的目录一样。 之前我们在学习页的结构时也了解到，数据页（索引页）中存在一个 page directory 结构，主要为的是快速检索到目标记录。

三种常见的索引模型

  1. 哈希表，建立 key-value 映射关系。 插入、删除、等值查询等操作都是 O(1) 复杂度。 缺点是无序，不支持区间查询。
  2. 有序数组，在等值查询、范围查询上的效率都比较高。 缺点是插入、删除时的效率比较低，需要移动数据。 比较适合存储数据为静态数据（即不会改变的数据）的场景。
  3. 搜索树，B 树、B+ 树都是这种模型。 在插入、删除、查询上性能都非常优秀。InnoDB 采用的就是 B+ 树作为索引。

在 InnoDB 中，每个索引都是一个 B+ 树。 而且，默认会为每个表建立以主键为索引的 B+ 树，称为是聚簇索引。 对应地，使用 INDEX 关键字创建的索引，称为二级索引。

01-InnoDB 中的索引模型之聚簇索引

索引和数据（记录）都存储在类型为 FIL_PAGE_INDEX 的页中，称为索引页或数据页。 索引和记录并不会混合存储，即某个 FIL_PAGE_INDEX 页中，要么都是记录（后面我们称这样得页为数据页），要么都是索引（称索引页）。 在 InnoDB 记录行结构 中介绍过，记录头信息中有一个字段 record_type 用于区分记录（0）、索引（1）。

记录行和索引行相比，有如下的不同之处：

  • record_type 值不同
  • 行中的真实数据不同，记录行包括了所有的列，另外还有 InnoDB 插入的三个隐藏列； 索引行只包括主键、页号。
  • mini_rec_mask 值，只有索引页中主键最小的索引行的记录头信息中的 mini_rec_mask 值为1，其他所有的都为0。

这里我们借助《MySQL 是怎样运行的：从根儿上理解 MySQL》中的一幅图来加深对聚簇索引形成的 B+ 树的理解。

img_mysql_cluster-index.png

结合上图，所有非叶节点中，行记录中存储的是主键、页号信息：

  • 页号，B+ 树是一个树状数据结构，页号表示当前节点的孩子节点是。
  • 主键，为孩子节点的所有记录中（不包括 InnoDB 插入的最大、最小记录）主键最小的记录所拥有的主键值。

B+ 树分为多个层次，最上面一层只有一个节点，称为 Root 节点。 Root 节点一旦创建便不会再移动。 除了 Root 节点外，其他层每层都包括若干个节点。 每节点之间通过两个指针连接起来，形成了一个双向列表。 每个节点中的记录行之间也通过指针串联起来，形成按照主键递增的单向链表。

01.1-使用聚簇索引进行查找

通过前面的介绍，相信你对聚簇索引有了一定的理解。 解析来，我们将看一下在聚簇索引上根据主键是怎么查找对应数据的。 我们依然使用前面图中的那个聚簇索引作为示例，演示一下查找主键为20的记录的值。

  1. 首先，需要访问聚簇索引 Root 节点所在的页，在其 page directory 中查找主键为20的记录在页30中。
  2. 然后，页30被加载到内存中，重复步骤1中查找 page directory 的步骤，确定主键20的记录在页9中。
  3. 最后，页9被加载到内存中，重复步骤1中查找 page directory 的步骤，找到主键为20的记录。

02-InnoDB 中的索引之二级索引

二级索引与聚簇索引的不同之处，分两部分：

  1. B+ 树非叶节点，记录行中的内容为索引列、页号（索引列 + 页号有时不能保证唯一性，所以这部分的内容实际上是索引、主键、页号这三部分内容）。
  2. B+ 树叶子节点，记录行中的内容为索引列、主键列，而聚簇索引中叶子节点中存储的是完整的记录行数据。

而且，与聚簇索引中按照主键排序不同，二级索引形成的 B+ 树中，每个页中的记录行以索引列的顺序形成递增链表。

同样地，联合索引也会形成 B+ 树。 假设有联合索引 ALTER TABLE t_name ADD INDEX idx_x(k1, k2, k3) ，每个节点中的数据都是先按照 k1 排序，k1 相同的按照 k2 排序，以此类推。

02.1-使用二级索引进行查找

结构上，二级索引和聚簇索引是没有区别的，两者的区别在于节点中存储的内容。 使用二级索引进行数据查询时，其步骤与 01.1 节中介绍的使用聚簇索引查询的步骤基本一致，但有点细节上的区别：

  1. 当二级索引中的信息能够覆盖要查询的信息时（索引覆盖），二级索引查询步骤与前面介绍的聚簇索引查询步骤一模一样，没有差别。

什么是索引覆盖？ 我们知道，索引形成的 B+ 树中，叶子节点中存储的信息只包含对应的索引键、主键信息， 如果我们要查询的信息（即 SELECT 后面跟着的列信息）在上述索引、主键中能够全部找到，此时就成为索引覆盖（要查询的信息）。

  1. 二级索引无法覆盖要查询的信息时，这时仅查询索引对应的 B+ 树是得不到完整的信息的。 要获取完整信息，需要通过二级索引检索到的主键，再到聚簇索引中根据主键查找完整的记录信息（这个过程称为回表）。

这下你能理解为什么叫做二级索引了吧，即有时需要检索两个 B+ 树。 回表时查询效率比较低下，主要两方面：一是需要检索两棵树，二是回表是一个随机访问磁盘的过程，延迟比较大。

03-MySQL 中索引创建、删除语句

创建索引，有两种方式：

  1. 建表时创建，例如：
CREATE TABLE t_demo(
    id int AUTO_INCREMENT,
    c1 int NOT NULL ,
    c2 int NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_c1(c1) --- 等价于 key idx_c1(c1)
)
  1. 通过 ALTER 方式添加，例如：
ALTER TABLE t_demo ADD INDEX idx_c2(c2);

删除索引可以通过 ALTER，例如：

ALTER TABLE t_demo DROP INDEX idx_c2;
avatar
文章
阅读
粉丝