数据库复习笔记5——MySQL索引

MySQL索引

什么是数据库索引

数据库索引，类似于书籍的目录，可以根据目录的某个页码立即找到对应的内容。很多存储数据的情况下都使用了索引结构，比如操作系统逻辑文件系统的索引文件，既保留了顺序文件的空间优点，也保留了直接文件的访问优点。

数据库只做两件事情：存储数据、检索数据。而索引是在你存储的数据之外，额外保存一些路标（一般是B+树），以减少检索数据的时间。所以索引是主数据衍生的附加结构。

一个索引是存储的表中一个或多个特定列的值的数据结构（最常见的是B+Tree和hash表）。索引是在表的列上创建。所以，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。请记住这一点：索引是一种数据结构 。

索引是一种典型的“以空间换时间”的优化方法，类似的方法还有Cache等。

SQL数据库的常用索引有：

• B+树结构
• Hash结构

MySQL的数据库存储结构

MySQL的基本存储结构：页，记录都存储在页里面。（类似于操作系统的概念）

• 各个数据页可以组成一个双向链表。
• 每个数据页中的行记录组成单向链表。
• 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键（聚集索引）查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
• 以其他列作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

MySQL的索引存储的是创建索引的列上的属性值和对应记录所在物理存储位置的指针。

什么时候使用索引

适合建立索引的数据表：

（1）频繁查询，很少修改：一张表可以建立任意多个索引，每个索引可以是任意多个字段的组合。索引可能会提高查询速度（避免全表扫描，如果查询时使用了索引），但一定会减慢写入速度，并会占用额外的空间，因为每次写入时都需要更新索引，所以索引只应该加在经常需要搜索的列上，不要加在写多读少的列上。

（2）中大规模：索引不应该用于小规模的表，当字段用于WHERE子句作为过滤器会返回表里的大部分记录时，该字段就不适合设置索引。小规模的表无需维护索引，直接全表扫描即可。超大规模的表，索引也会失效。

适合建立索引的字段：

• 在经常用作过滤器的字段上建立索引；
• 在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引；
• 在不同值较少的字段上不必要建立索引，如性别字段，性别字段无非男女两种值，区分度不好，建立索引效果不好，要选择区分度高的字段；
• 对于经常存取的列避免建立索引；
• 用于联接的列（主健/外健）上建立索引

索引的类型

聚集索引（主键索引）

聚集（clustered）索引，也叫聚簇索引。它的定义为：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引的定义看上去比较抽象，我们可以举个例子：

 一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面。

聚集索引可以根据主键的逻辑顺序快速地搜索到对应的记录。因为如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

通常来说，聚集索引在创建表的时候指定，MySQL默认指定主键为一张表的聚集索引。如果在表创建完成后指定聚集索引，那么将会根据指定的列的逻辑顺序调整数据行的物理位置，非常耗费时间。所以，聚集索引也会被叫做主键索引。

更详细内容参见：https://www.cnblogs.com/s-b-b/p/8334593.html。

• 创建表时直接指定主键，就等于指定聚集索引：

• 创建表后修改主键：

MySQL最常用的引擎InnoDB中，对于聚集索引的存储（B+ Tree）是这样的：

InnoDB使用B+树存储聚集索引，叶子节点就是对应的数据节点。正是因为聚集索引逻辑顺序和实际物理顺序一致的特点，所以才能直接用叶子节点存储记录。“聚集”就是指数据行和相邻的键值紧密地存储在一起。

而MySQL的MyISAM除外，此存储引擎的聚集索引和非聚集索引只多了个唯一约束，其他没什么区别。无论是否为聚集索引，MyISAM都只在B+树的叶子节点存放指向对应记录物理地址的指针。

所以，在InnoDB中使用聚集索引非常高效，因为可以直接获取想要记录，而如果使用非聚集索引查询对应的记录，只能获取对应记录的主键，还需要到主键索引B+树中进行二次查询，这一过程也被称作是“回表”。（PS：InnoDB的非聚集索引叶子结点存放的是对应记录的主键，而MyISAM无论是否聚集索引，存放的都是对应记录的指针）

非聚集索引（非主键索引）

与聚集索引相反，非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

按照定义，除了聚集索引以外的索引都是非聚集索引，非聚集索引可以细分为：

• UNIQUE（唯一索引）：不可以出现相同的值，可以有NULL值
• INDEX（普通索引）：允许出现相同的索引内容
• fulltext index（全文索引）：可以针对值中的某个单词，但效率不是很好

非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。

下图是MyISAM中对于非聚集索引的存储，它在B+树的叶子节点中存放了对应记录的指针。

正如我们之前所说的，非聚集索引在InnoDB引擎的B+树中只会存放所在记录的主键，查询完整的记录需要二次查询主键索引树（回表）。

但是，当需要查询的列位于索引中时，我们可以使用覆盖索引而无需回表：

关于覆盖索引的详细解释参见：https://juejin.im/post/6844903967365791752

总结一下：覆盖索引就是将要查询的字段（select后面的字段，加入到联合索引中，这样可以避免回表操作，提高数据查询的效率。）

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2key1,key2key1,key2。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

何时使用两类索引，有如下的一张表格：

联合索引

联合索引，顾名思义，实质上是将多个字段建到一个索引里，列值的组合必须唯一。

如果使用联合索引，那么使用过程中，MySQL会遵循最左匹配原则：

最左匹配原则（最左前缀法则）：指的是查询从联合索引的最左前列开始，且不能跳过索引中的列。

例如：我们有一个联合索引（name, status, address），在查询的时候，where name = '' and status = '' and address = ''这样是可以的，会走三个字段的索引；但是where status = '' and address = ''或者where status = ''或者 where address = ''均不会走索引；

where  address = '' and status = '' and name = ''会走三个字段的索引，即在where子句中的相对顺序不重要；

where name = '' and address = ''跳过了status字段，所以只会走name字段的索引，不会走address字段的索引。

详情参见：https://www.bilibili.com/video/av710427305?p=49

索引底层实现

FULLTEXT索引

全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。

RTREE索引

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

HASH索引

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位（哈希函数），不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

BTREE索引

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf（这里指的是B+树，常规B树的键值对存放在每一个节点中）这是MySQL里默认和最常用的索引类型。

B树简介

B+树是Mysql顶层最常用的索引数据结构，它是由B树演变而来的。

B树是一种多路平衡查找树。与常规的二叉查找树相比，它的优势在于尽可能少的磁盘 IO，加快了检索速度：

• 常规的二叉查找树，如：基础二叉查找树、AVL、RBT等，由于都是二叉的，也就是每个父节点最多只有两个孩子，所以通常是“高瘦”的，也就是层数较多。但我们知道，数据库中的数据大都存放在外存上。磁盘IO的一个特性是按照磁盘块进行IO，所以即使是访问一个节点，也需要读取一个完整的磁盘块。如果我们使用高瘦的二叉查找树，那么访问的层数较多，读取的磁盘块数目也较多，降低了时间效率。相反，B树的每个内部节点能够指向多个孩子节点，它的搜索过程是：从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的孩子结点；重复，直到所对应的孩子指针为空，或已经是叶子结点。
• 由于每一层的节点数增多，所以查找树的高度降低，也就是说平均每一次搜索需要读取的节点数减少，从而让磁盘IO次数减少，降低了时间消耗。（B树是“矮胖”的）
• 在数据较小，可以完全放到内存中时，RBT的时间复杂度比B树低（RBT是二叉的，直接根据当前节点值和目标值的大小关系即可决定进入哪个子树，而B-TREE是多叉的，在每一个节点内部需要进行二分查找，才能决定进入哪个子树）。反之，数据量较大，外存中占主要部分时，B树因其读磁盘次数少，而具有更快的速度。

下面是一个存储了 16 个数据的 二叉查找树，同样每个节点最多存储 2 个 key，查询 id=16 这个数据需要查询比较 4 个节点，也就是经过 4 次磁盘 IO：

                                                                                     二叉查找树

考虑到磁盘 IO 读同一块上的1个数据和读 100 个数据都需要读取完整的磁盘块，消耗的时间基本一致，那我们的优化思路就可以改为：尽可能在一次磁盘 IO 中多读一点数据到内存。这个直接反映到树的结构就是，每个节点能存储的 key 可以适当增加。我们可以使用一个6阶的B树进行检索，只需要进行 2 次磁盘 IO。

B树的特点是：

• 关键字集合分布在整个树中；
• 任何一个关键字出现且只出现在一个结点中；
• 搜索有可能在非叶子结点结束；
• 其搜索性能等价于在关键字全集内做一次二分查找；
• 自动层次控制；

它的定义为：

• 每个节点最多有m-1个关键字（可以存有的键值对）。
• 根节点最少可以只有1个关键字。
• 非根节点至少有m/2个关键字。
• 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。（方便二分查找）
• 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。（完全二叉树）
• 每个节点都存有索引和数据，也就是对应的key和value。（B树和B+树最大的区别）

                                                                                                      B树

B+树简介

上面介绍了B树的优势，实际上，我们可以对B树进行进一步改进，使其更适合数据库索引的需求。

与B树相比，B+树的特点在于：

• B 树的非叶子节点里既存索引也存数据，而 B+只存索引（地址），所以 B 树里非叶子节点存不了很多个键值对，但是 B+树一个非叶子节点能存很多索引，B+树将所有的数据存放在叶子节点中。
• B+树的叶子节点用了一个链表串联起来，便于范围查找。

                                                                                                       B+树

由于上述特点，B+树存在着以下的优势：

• 在单个节点存储容量有限的情况下，B+树的内部节点也能存储大量索引，使得整个 B+树高度进一步降低，进一步减少了磁盘 IO。
• B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。（InnoDB的B+树聚集索引的范围查找优势原因，最左前缀匹配原则的原理）
• B+树的查询效率更加稳定 。由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

为什么不使用哈希索引

• 哈希索引适合等值查询，可以直接使用哈希函数运算找到记录。但是无法进行范围查询 。
• 哈希索引没办法利用索引完成排序。
• 哈希索引不支持多列联合索引的最左匹配规则 。
• 如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

参考资料：

1、https://hillzhang1999.gitee.io/2020/05/29/shu-ju-ku-fu-xi-ji-yu-mysql/#toc-heading-76

2、https://www.bilibili.com/video/av710427305?p=7