MySQL笔记-索引

发布于 2022-4-24 09:26
浏览
0收藏

作者 |jaxer
来源 | WriteOnRead(ID:WriteOnRead)

概述

简单来说,索引的出现是为了提高查询效率,就像书的目录一样。MySQL 的索引是在「存储引擎」层实现的,因此没有统一的标准,同一种类型的索引,在不同存储引擎之间实现可能也不同。本文主要分析 InnoDB 存储引擎的索引结构。

索引模型

索引模型就是索引的实现形式(也可以理解为索引的数据结构),常见的索引模型有下面三种:

1. 哈希表(散列表)

键值对形式(类似 Java 中的 HashMap)

  • 优点:新增速度快;
  • 缺点:无序,区间查询速度很慢(全表扫描)。
  • 适用场景:只有等值查询的情况(例如 Memcached 等一些 NoSQL 引擎)。

2. 有序数组

  • 优点:等值查询和范围查询速度都很快。
  • 缺点:更新成本太高(插入的记录在中间时,需要移动后面的所有记录,可类比在数组中间位置插入元素的操作)。
  • 适用场景:静态存储引擎(比如不再修改的历史数据)。

3. 搜索树(N 叉树)

  • 优点:读写快,适配磁盘的访问模式。
  • B+ 树就是其中的一种,也是 InnoDB 存储引擎的索引模型。

InnoDB 记录的存储结构

数据页

在 InnoDB 引擎中,会将数据划分为若干个「页」,「页」是磁盘和内存之间交互的基本单位,页的大小一般为 16KB。即:一般情况下,一次最少从磁盘中读取 16KB 的数据到内存中,一次至少把内存中 16KB 的数据刷新到磁盘中。

向一个数据页中插入记录的过程如图所示:MySQL笔记-索引-开源基础软件社区

数据页中分为几个部分,其中 User Records 部分为存储记录的空间(其他部分存储数据页的其他信息,这里暂不详述),插入过程大致如下:

1. 未插入记录时,User Records 部分不存在;

2. 当插入记录时,会从 Free Space 部分划分出空间存储记录;

3. 当 Free Space 空间用完时,也就是该数据页的空间用完了,需要分配新的数据页存储(页分裂)。

记录的结构

在 InnoDB 引擎中,一条记录的存储结构如图所示:MySQL笔记-索引-开源基础软件社区

PS: 其中橙色部分 (c1, c2, c3) 是表中的列,且 c1 为主键,下图亦是如此。

也就是说,数据页中记录的数据,除了一条记录本身,还有变长字段列表、NULL 值列表、记录头信息等其他信息,这样才是在数据页中的一条完整记录。

数据页中多条记录之间的关系示意图:MySQL笔记-索引-开源基础软件社区

即,每个页中保存了许多条记录,并且每条记录指向下一条记录(根据主键顺序,类似单链表结构)。此外还记录了该页中的最小和最大记录(也是根据主键顺序)。

不仅如此,这些记录还会几条(1~8)分为一个组,并且把组内最大的主键值提取到一个槽(slot)中,用来实现快速(二分)查找,示意图如下:MySQL笔记-索引-开源基础软件社区

页内查找记录

以上面的数据页为例,若要查找主键值为 5 的记录,过程如下(二分查找):

1. 计算中间槽的位置:(0+4)/2=2,因此查找槽 2,而它对应记录的主键为 8,5<8,重新计算;

2. 重新计算,(0+2)/2=1,查找槽 1,对应记录的主键值为 4,5>4,因此查找的记录在槽 2 中;

3. 遍历槽 2 对应的分组,查找主键为 5 的记录。

因此在一个数据页中查找指定主键值的记录过程大致分为两步:

1. 通过二分查找确定记录所在的槽;

2. 遍历该槽所在组中的各个记录(通过记录的 next_record)。

由于槽内数据很少(不超过 8 条),因此遍历的成本较低。

聚簇索引&二级索引

根据叶子节点的内容,索引类型可分为「聚簇索引」(Clustered Index)和「二级索引」(Secondary Index)。

1. 聚簇索引

在 InnoDB 存储引擎中,聚簇索引也称为「主键索引」,表都是根据主键顺序组织存放的,这种存储方式的表称为索引组织(Index Organized Table)表(索引即数据,数据即索引)。一张表只能有一个主键索引。

聚簇索引的示意图如下(该结构就是一棵 B+ 树):MySQL笔记-索引-开源基础软件社区

图中结构分为三层,其中上面的两层(即非叶子节点,页 33、页 30 和页 32)为索引层,保存的是索引信息;第三层(叶子节点)为数据层。在主键索引中,叶子节点保存的是完整的记录(以数据页为单位)。

PS: 存储节点的空间可能是不连续的,但是,同一层的节点是有前后顺序的,它们之间以「双向链表」的形式连接。

在索引树中查找一条记录的大致过程如下(仍以查找主键值为 5 的记录为例):

1. 先查找根节点,即页 33,页 30 中的主键范围是 [1, 320),而页 32 中主键大于等于 320,因此定位到 页 30;

2. 再查找页 30,同样的方法定位到页 28;

3. 根据上面「页内查找记录」的方式在页 28 中查找。

2. 二级索引

InnoDB 中,二级索引的叶子节点存储的是主键的值。二级索引也称为「非聚簇索引」、「非主键索引」。一张表可以有多个二级索引。其中,以单列作为二级索引的又称「单列索引」,以多列作为索引的又称「联合索引」或者「组合索引」。

二级索引的示意图如下:MySQL笔记-索引-开源基础软件社区

该结构与聚簇索引类似,也是一棵 B+ 树。

与聚簇索引的不同之处主要在于第三层,也就是叶子节点,在二级索引中,叶子节点保存的是主键的值。

二级索引中的查找过程与聚簇索引中查找类似。

不同的是,由于二级索引保存的是索引列和主键列,若查找的数据包含索引和主键之外的内容,则需要先找出主键值,然后再根据主键的值到聚簇索引中查找完整记录,该过程称为「回表」。

值得注意的是,上述查找都是在有索引的情况下进行的,如果没有索引呢?则会进行全表扫描,这样当数据量较大时,效率会非常低。这也是索引出现的主要原因。

区别与联系(InnoDB 存储引擎)

1. 聚簇索引和二级索引都需要占用磁盘空间,每一个索引都对应一棵索引树;

2. 二者都是 B+ 树结构,数据都存储在叶子节点(非叶子节点不保存数据);

3. 聚簇索引的叶子节点保存的是完整记录,二级索引保存的是主键的值;

4. 在一张表中,聚簇索引只能有一个,二级索引可以有多个(即多个索引树)。

根据这几点比较也可以发现,索引虽然可以提高查找效率,但也有缺点。如果有多个索引,当修改数据时索引也要同步进行更新,这样会降低操作的效率;而且索引也会占用磁盘空间。因此,索引并非越多越好。

InnoDB 引擎主键选择

在 InnoDB 中,每张表都有个主键(Primary Key),如果在建表时没有显式地定义主键,则 InnoDB 引擎会按照如下方式选择或创建主键:

1. 首先判断表中是否有非空的唯一索引(Unique NOT NULL),若有,则该列即为主键(当表中有多个非空唯一索引时,InnoDB 存储引擎将选择建表时第一个定义的非空唯一索引为主键);

2. 若不符合上述条件,InnoDB 存储引擎自动创建一个 6 字节大小的隐藏列(row_id)作为主键。

因此,建表时最好显式指定主键。

索引优缺点

主要优缺点如下(可通过上述存储结构分析理解):

优点

1. 可以提高数据检索效率,降低数据库 IO 成本;

2. 对记录进行排序,降低 CPU 消耗(被索引的列会自动进行排序),可以提高排序和分组查询的效率。

缺点

1. 索引会占用磁盘空间;

2. 降低更新效率(更新操作会同步更新索引)。

索引使用场景

需要创建索引的场景

1. 主键自动建立唯一索引;

2. 频繁作为查询条件的字段应该创建索引;

3. 多表关联查询中,关联字段应该创建索引(ON 两边都要创建);

4. 查询中排序的字段,应该创建索引;

5. 统计或者分组。

不需要使用索引的场景

1. 表记录太少;

2. 频繁更新;

3. 查询字段使用频率不高。

PS: 这里只是概括了一些常见的优缺点和使用场景,可以根据前面对索引的结构和特点的分析对比理解。

小结

简单来说,索引可以理解为书的目录。

索引的主要作用是为了提高查找效率;但索引也有缺点,并非越多越好,需要根据实际情况决定如何创建合适的索引。

收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐