一文搞懂MySQL的索引

MySQL的索引

一、索引的作用：
索引，是存储引擎用于快速找到记录的一种数据结构。
（注意上面我加粗的这两个部分！前者我会在后面介绍索引类型时着重提及，后者表明了索引归根结底是一种数据结构！）

索引的工作方式举例：
如同一本书中的目录一样，你可以根据目录所列信息找到你所感兴趣的内容。

SELECT name FROM tb_person WHERE person_id = 5;
如果在person_id列上建有索引，则MySQL将使用索引找到person_id为5的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

二、创建索引的方式：
1.ALTER TABLE 表名 ADD INDEX 索引名（列名）
2.CREATE INDEX 索引名 ON 表名（列名）

三、索引的类型：
说在最前面（我在上面第一节中加粗的第一个词语：存储引擎！）：在MySQL中，索引是在存储引擎层而不是服务器层实现的。所以并没有统一的索引标准！不同的存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引！即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同！下面仅重点介绍最常用的InnoDB引擎所支持的索引类型（可能会简略的穿插其它引擎的索引类型介绍，如MyISAM引擎，Memory引擎）

i）我们首先基于数据结构这种比较底层的角度来看待不同索引类型：
InnoDB支持的索引类型：B+ Tree索引，哈希索引，全文索引（曾经只有MyISAM引擎支持，自从MySQL5.6.4版本开始，InnoDB引擎也支持了全文索引）

接下来我们分别介绍这几种索引

1.B+ Tree索引：
注：B-Tree就是B Tree（B树），而不是什么B减树…本文接下来介绍的B树统一由B Tree来表示。另外，本文介绍B Tree和B+Tree并非十分详细，目的只是为了更好的引入所要介绍的索引类型，如果对这两种数据结构有疑惑或感兴趣的读者请自行查阅相关资料。

在介绍B+Tree索引之前，先简单的说一下B Tree和B+Tree这两种数据结构，B Tree和B+Tree也都是一棵查找树。B Tree的特征是“矮胖”！ B Tree的B是Balanced的意思而不是Binary（B树可以是多叉的）， Balanced意味着B树是绝对平衡的，它的所有叶子节点都在同一层上，并且树的高度往往不是很高，一般在2~4层。对于树来说， IO次数就是树的高度，也就是说查找某一键值的行记录时最多只需要2到4次IO，所以不难看出如此设计的意义在于减少磁盘IO次数！从而提升查询性能。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。 B+Tree与B Tree最大的区别就是B+ Tree在非叶子节点中并不保存实际的数据，而B Tree的非叶子节点中也会保存实际的数据。 现实使用过程中几乎已经没有使用B Tree的情况了。

在 B+ Tree 中，一个节点中的 key 从左到右非递减排列。如下图所示（注意我上面所说的B+ Tree只有叶子节点保存data）：

关于B+ Tree相比于B Tree的查询优势：
1）B+ Tree的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”，所以IO次数相较于B Tree更少。
2）B+ Tree查询必须查找到叶子节点，B Tree只要匹配到即可不用管元素位置（由于非叶子节点保存了实际的数据），因此B+ Tree查找更稳定（但并不慢）
3）对于范围查找来说，B+ Tree只需遍历叶子节点链表即可（由于B+ Tree的叶子结点使用顺序指针相连），B Tree却需要重复地中序遍历。

关于对B+Tree的操作：
进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
插入删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

关于B+Tree与红黑树等其他平衡查找树的比较：
红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，主要有以下两个原因：
1）更少的查找次数：
平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(logdN)，其中 d 为每个节点的出度。
红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。
2）利用磁盘预读特性：
为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入。

有了上面的预备知识，接下来可以开始正式的介绍B+ Tree索引了：
InnoDB默认的索引就是B+ Tree索引（看到好多文章的说法是MySQL默认的索引是B+ Tree索引，我个人认为这种说法并不严谨，正如我们前文所提到的，索引是在存储引擎层而不是服务器层实现的，所以说起索引类型，更严谨起见，我们要指定我们所使用的存储引擎，尽管对于大多数存储引擎，默认的索引类型就B+Tree索引，但也有些例外，比如Memory引擎的默认索引就不是B+ Tree索引，而是Hash索引），所以对于InnoDB存储引擎（MyISAM引擎也是）只要用户不显式的指定索引的类型，那么索引就是B+Tree索引，换一个说法就是，对于InnoDB引擎，B+Tree索引就是传统意义上的索引。使用B+Tree索引，查询数据时，就不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。并且因为 B+Tree 的有序性，除了用于查找，我们还可以用于排序和分组。

InnoDB的B+Tree索引还可以细分为：
1）聚簇索引 （clustered index）：
它是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能有一个聚簇索引。在多数情况下，查询优化器倾向于采用聚簇索引。因为聚簇索引能够在B+树索引的叶子节点上直接找到数据。如下图

2）辅助索引 （secondary index）
辅助索引的叶子节点不保存行记录的完整数据，它的叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个“书签”，这个“书签”就是相应行数据的聚簇索引键（一般就是主键）！每张表中可以存在多个辅助索引。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向聚簇索引的主键，然后再通过聚簇索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚簇索引树的高度同样为3，那么还需要对聚簇索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

二者的关系/区别梳理：
1)聚簇索引的叶节点就是数据节点，而非聚簇索引的叶节点仍然是索引节点。
2)聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。
3)聚簇索引的空间占用比辅助索引大的多，因为叶子节点保存着完整的数据记录。
4)每张表都会有且仅有一个聚簇索引，如果创建表时您指定了主键，那么该主键索引就是聚簇索引。如果你不定义为您的表的主键，InnoDB会取第一个唯一索引（unique）并且必须是非空的（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。除此之外的表上的每个索引（即index/unique这种索引，不包括fulltext全文索引等其它数据结构类型的索引）都是辅助索引（secondary indexes）。

关于B+Tree索引最后再介绍几种它所支持的查询类型：
1）全键值： SELECT * FROM tb_person WHERE age = 20;(age列设置了索引)
2）键值范围：SELECT * FROM tb_person WHERE age > 20;(age列设置了索引)
3）键前缀查找（仅适用于根据最左前缀的查找，即使用索引的第一列）：如果一个索引是由多个列所建立的，这里注意区分不是多个列对应多个索引的情况，而是多个列指定一个索引，这种情况下的键前缀查找，仅支持使用定义索引的第一个列来进行查找。

2.哈希索引：
InnoDB引擎的哈希索引叫做“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B+Tree索引之上再创建一个哈希索引，这样就让B+Tree索引也具有了哈希索引的一些优点，比如，快速的哈希查找。不过InnoDB引擎的哈希索引，不允许用户手动干预，这是一个完全自动的内部的行为，用户无法控制。
只有Memory引擎显式的支持哈希索引，同时这也是它的默认索引类型。注意，哈希索引只适用于精确查找，而不适用于范围查找，因为哈希表是无序的数据结构。

3.全文索引：
它更类似于搜索引擎所做的事情，比如查找文本中的关键词，在相同的列上同时创建全文索引和B+Tree索引不会产生冲突，全文索引适用于MATCH AGAINST 操作，而不是普通的WHERE条件匹配。

ii）接下来我们从使用的角度来看下各种索引的类型：

1.PRIMARY 主键索引: 它是一种特殊的唯一索引，不允许有空值。 根据我们上面所介绍的，它就是B+ Tree索引，且是B+Tree索引中的聚簇索引！
2.UNIQUE 唯一索引：索引列的值必须唯一，但允许有空值。（是B+Tree索引中的辅助索引）
3.INDEX (普通)索引：最基本的索引，没有任何限制。（是B+Tree索引中的辅助索引）
4.FULLTEXT 全文索引：参见上文中所介绍的全文索引
5.SPATIAL 空间数据索引：MyISAM 存储引擎支持空间数据索引（R-Tree），注意，InnoDB存储引擎不支持这个索引类型，SPATIAL可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据。

四、索引的优点：
索引可以让服务器快速地定位到我们要查找的数据位置，但这并不是索引的唯一作用！下面是索引的一些优点：
1.大大减少了服务器需要扫描的数据行数。
2.帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
3.将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

五、索引的缺点：
1.表很大的时候，对于表的修改的同时还需要去维护索引，（比如我们需要去维护B+ Tree的这种绝对平衡性）数据量很大的时候，这个维护代价会很高。
2.它增加了数据库的存储空间

六、索引的使用原则：
1.对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；
2.对于中到大型的表，索引就非常有效；
3.但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

七、约束：
很多人看到Primary Key和Unique Key的约束，就会想到，这不就是通常创建索引的方式吗，那么约束和索引又有什么区别呢？的确，当用户创建了一个唯一索引就相应的创建了一个唯一约束。但是约束和索引的概念还是有所不同的，约束更是一个逻辑上的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

1.约束声明方式：CONSTRAINT 约束名 约束类型 （列名）
2.约束的种类：
1）主键约束（是一种特殊类型的唯一约束），声明了主键约束后，MySQL会自动生成一个唯一索引。
2）外键约束：只有InnoDB引擎支持，声明外键约束时，MySQL会自动生成一个索引。
3）唯一约束：MySQL会自动生成一个索引。

参考资料：
1.《高性能MySQL》
2.《MySQL技术内幕:InnoDB存储引擎》
3.《SQL学习指南》
4. GitHub CyC2018/CS-Notes