「MySQL高级篇」MySQL索引入门

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

大家好，我是Zhan，一名个人练习时长一年半的大二后台练习生，最近在学MySQL高级篇，欢迎各路大佬一起交流讨论

👉本篇速览

索引，好像从基础篇开始到后续项目中都会提到，留给我的印象就是，提高数据读取速度，但是索引底层具体怎么去实现的，又有哪些索引的种类，索引怎么去配置……而本篇将从以下七点，带你逐一攻破Ta：

1️⃣首先第一个方面首先介绍一下：什么是索引，索引有什么作用以及索引的优缺点
2️⃣接下来介绍一下索引的结构
3️⃣然后就是索引的分类，索引有哪些种类
4️⃣第四个方面就是索引的语法，如何创建、查询、删除索引
5️⃣SQL性能分析的一些手段和工具
6️⃣索引的使用原则
7️⃣索引的设计原则

1️⃣ 索引概述

索引，英文名为Index，官方给出的定义为：帮助MySQL高效获取数据的数据结构（有序）。也就是说，数据库在保存原始数据之外，还维护着索引这种满足特定查找算法的数据结构，而这些数据结构以某种特定的方式指向数据，这样就可以通过索引使用高级的搜索算法更快的查找数据。

🍁索引示例

可能说完这些还是比较抽象，对于索引没有一个概念，特别是在知道索引是一种数据结构后似乎对索引的认知更模糊了，这里我们不妨拿索引的使用举个例子：对于这张表，如果需要查询 SELECT * FROM table WHERE age = 45

那么实际上进行的是全表扫描，也就是从第一条记录扫描到最后一条记录，而且中间不会停下（即使找到灭绝那条记录），因为要查出所有age = 45的记录，也就是说一个指针从头到尾扫描一遍age是否与目标值相等

而如果有了索引呢：此处我们暂时不去想索引是何种数据结构，我们以二叉搜索树作为索引的实现方式，我们就可以把原本的数据转化为：此时我们要去找45，此时的效率就比全表扫描更高，同时也不会出现全部都要扫描一遍，这就是索引的作用，索引实际上就是维护了一张表，一张：指向对应数据记录物理地址的指针 与 索引键值 的表，这样就能通过这张中间表，从树 -> 索引表 -> 数据表，这样就能更快的找到目标数据

当然，此处的二叉树并不是真实的索引结构，只是用于引入索引这个概念

🌺索引优缺点

其实有了上面的例子，我们最直观的感受就是，索引会大大提高查询的效率，但是如果索引只有这样的优点，它就不是可以配置，而是所有表都会使用了，这里就来说一说索引的优缺点：

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引在上文中提到了，它也是一张表，因此它也是占空间的
由于索引为了方便查找数据效率更高，它内部的算法会对数据进行一定的“排序”，那么通过索引对数据进行排序，就会降低数据排序的成本，降低CPU的损耗的	索引尽管大大提高了查找的效率，但是也伴随着一个问题，数据的增删改的同时，索引这张表也需要增删改，也就是说加快了查询的效率，但是降低了修改的效率

2️⃣ 索引的结构

我们在上面的示例中假设了索引为二叉搜索树这种数据结构，但是也如上面所说，这只是方便理解而提出的一种假设，实际上索引是何种数据结构呢？索引又有哪些种类呢？下文中我们一同来探究

📑索引种类

在上文讲存储引擎中我们就有提到，索引的实现方式取决于存储引擎，不同的存储引擎有不同的结构，主要包含以下几种索引：

B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
Hash索引：底层结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引)：主要用于MyISAM存储引擎中，主要的用途就输存储地理空间数据类型
Full-text(全文索引)：通过建立倒排索引，快速匹配文档的方式
上述四种索引，使用频率比较高的是B+Tree索引 Hash索引

📜 索引 & 存储引擎

我们除了要了解上述的四种索引，同时也应该知道索引受哪些存储引擎支持，这样我们才能使用的时候搭配合适的存储引擎，以下介绍了三种常见的存储引擎的支持索引的情况：

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6以后支持	支持	不支持

这里需要提到的一点是：如果没有特别指明，索引也会有默认的实现，也就是最常见的B+Tree索引

💎 B+Tree索引

上面的讲述其实不难发现，就如同常用的存储引擎是InnoDB，最常用的索引是B+Tree索引，但是B+Tree索引 的结构究竟是什么样的呢，让我们一起来探讨一下：

🍀 B树 & 二叉树

在讲B+Tree之前，我们不妨先谈谈B-Tree，在讲B-Tree之前，我们不妨先谈谈二叉搜索树（无限套娃/doge），可能有了上面的讲述，会感觉：二叉搜索树不是很方便吗？但实际上MySQL使用的索引却不是二叉搜索树，为什么呢？我们不妨从下面这个二叉树说起：

上图是二叉搜索树的理想情况，现在我们假设两种情况来说明它的弊端：

随着数据量的增大，二叉搜索树的深度也会变大，那么此时检索的速度就会降低
因为插入顺序的随机性，二叉树的高度不稳定，对于顺序插入的数据这种极端情况，它会形成一个单向链表，那么也就成为了线性查询，即二叉搜索树的查询效率不稳定。
1. 但其实也有解决方案：红黑树，红黑树是一个自平衡的二叉树。
2. 红黑树仍然是二叉树，无法解决随着二叉树的深度变大检索速度变慢的问题

🌴 B-Tree 示例

有了上面二叉搜索树存在的问题，自然就需要我们今天的主角的配角B-Tree：B-Tree中文名为多路平衡查找树

我们不妨以一个5叉查找树作为示例：不难发现，尽管我们现在把它拓展成了五叉的形态，但是它的原理还是类似的，利用五叉查找树这个数据结构，把数据分成下面红字那些一个个更小的区间：

通过这个图，我们不难得出，一个N叉查找树，它的关键字个数最多为（N-1）个，分叉的个数为N个

图中的紫色结点被称为失败结点或者说叶子结点，此处的结点即代表不存在，与其说它是一个结点，不如说是NULL。叶子结点上面的最后一层结点，我们称它为终端结点。

问题一：如果每个结点中都只放一个关键字，其实就会发现，B树变成了二叉搜索树，还是之前的问题，树的高度会越来越高，查询的层数也越来越多，效率自然而然就变低了

因此，我们规定：m叉查找树中，除了根节点，所有的结点至少有floor(m/2)个分叉和floor(m/2)-1个关键字 这样就保证了查找树的层数不会太高

问题二：如果插入顺序的随机性，五叉查找树的高度也会不稳定，导致出现一个“单向链表”的情况，和上述二叉树的问题很相似

也就是说它也有可能出现不平衡的情况，因此我们也需要维护它的平衡性：让它的所有子树高度相同

在解决了上述两个问题后，我们就得到了一个五路平衡查找树，也就是一个五阶B树

上面只是讲了B树的基本概念，至于B树如何去插入，删除，查询，打印，有兴趣的大家可以去这个可视化网站去了解

🌳 B+Tree

而在InnoDB存储引擎中实际使用的数据结构为B+Tree，B+Tree和B-Tree又有何种区别和变动呢，下文中会以一颗四阶B+Tree做演示：

其实B+Tree可以理解为B-Tree的一个变种，它在B树的基础上做了一些改进与变动，我们以一颗4阶B+Tree做示例：我们从图中能看出，B+Tree和B-Tree的区别：

所有的分支结点中仅仅包含它的各个子节点中关键字的最大值以及指向子节点的指针，如红色框和黄色框的，也就是说，子节点中除了本来要存的数据，还有父节点的值作为最大值
叶子结点包含全部关键字，因为父节点的结点会一步一步往下传，因此在叶子结点能看到全部关键字
紫色边框框起的指针p，把关键字按从小到大排序，并且相邻的叶子结点按照大小顺序连接起来了，也就是说可以支持顺序查找
还有就是结点的子树个数和关键字的个数相等
在B+树中，无论能否查找到，都会走到叶子结点，因为只有叶子结点指向了记录，所有的分支结点仅仅起到索引的作用，每个索引项都只含有对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址

🌲 B-Tree & B+Tree

在介绍完B-Tree和B+Tree后，对于二者的区别有了一定的了解，但是为什么InnoDB存储引擎选择使用B+Tree索引结构呢？

对于B+Tree，只有叶子结点会存放行数据，上面的结点仅仅起到索引的作用。对于B-Tree，叶子结点和非叶子结点都会存放行数据，一个结点是存放在一个磁盘块中的，一个磁盘块的大小是固定的16K，如果是B+树，我们会发现，如果不存放数据，一个块中能够存放的Key和指针就增多了，那么相同的数据量的情况下，它的层级就会变小，查询的效率自然就变高了
同时，对于B+Tree来说，不管查找哪一个数据，都是要到叶子结点才能找到，也就是说搜索的效率稳定。
而且在B+Tree的结构中，叶子结点形成了一个双向链表，这便于我们范围搜索和排序

📌 Hash索引

Hash索引其实有点类似于HashMap，采用一定的Hash算法，把键值换算成新的Hash值，存储到一张新的Hash表中

我们以下面这张表为例子：

给name字段加上一个Hash索引，那么这一列的值分别通过Hash算法得到一个“槽位”，也就是把数据放在这个“槽位”上，其实和哈希表很像，然后存放在这个“槽位”的值就是索引值，以及整行数据的hash值，用于指引到这一行数据。

如果出现了Hash冲突，就采用和HashMap最基础的方法，以链表的方式去连接。

🧩 Hash索引特点

在讲存储引擎的时候我们就简单提到过，Hash索引不支持范围查询，比如between, < , >……，现在我们就知道了原因，因为hash值无法做大小判断，因此Hash索引只能用于对等比较
Hash索引不能利用索引完成排序操作，与之对应的是B+索引可以通过叶节点快速进行排序操作
Hash索引查询效率很高，通常只需要一次检索（说“通常”是因为，如果有Hash冲突，就需要继续往下去查找），它的效率通常比B+索引要高

⚙ 存储引擎

在之前的讲解存储引擎的时候，我们给出的结论是：在MySQL中，只有Memory引擎支持Hash索引。

但是有一点我们没有说的是：InnoDB中具有自适应Hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。此处不多做介绍，后续在讲InnoDB引擎的时候会具体介绍。

3️⃣ 索引的分类

可能看到这个标题会有一点疑惑，那索引的分类不就是：B+索引、Hash索引、Full-Text索引……吗，实则不然，那些可以说是索引的具体实现，但是索引还是有它的种类的，有哪些具体的种类，又有哪些特点呢，我们下面一起来探究一下：

索引的常规分类主要分为以下四类：

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引，也就是说，如果我们在设计表中的时候设计了主键，那么会自动给主键这一列创建一个主键索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复，在我们为某一个字段加上一个UNIQUE约束的时候，它会自动给这一列创建一个唯一索引	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键字，而不是比较索引中的值	可以有多个	FULLTEXT

而在InnoDB存储引擎中，根据索引的存储形式，我们可以把索引又分为两类：

分类	含义	特点
聚集索引	将数据存储和索引放在一块，索引结构的叶子结点保存了行数据	必须有，而且只有一个
二级索引（辅助索引）	将数据的存储和索引分开，索引结构的叶子结点关联的是对应的主键	可以存在多个

这里提到，聚集索引必须有，而且只有一个，但是我们平时在创建表的时候好像也没有指定哪个是聚集索引，这又是为什么呢？这就不得不提到聚集索引的选取规则

聚集索引的选取规则：

在有主键的情况下，默认主键索引就是聚集索引
在没有主键的情况下，那么使用第一个唯一索引作为聚集索引
如果没有主键也没有唯一索引，此时InnoDB存储引擎会自动生成一个row_id作为隐藏的聚集索引

可能说完这些还是比较抽象，对于聚集索引和二级索引的概念并不明确，我们以一张简单的表来看看聚集索引和二级索引的具体实现：

首先选取聚集索引，由于该数据库表存在主键，就以主键作为聚集索引，在构建聚集索引的时候，其结构是B+Tree结构

然后选取二级索引，二级索引的叶子结点并不存储真正的行数据，因为如果存储真正的行数据就会造成存储时的大量数据冗余，因此，二级索引的叶子结点存储的是聚集索引的值，这里也就是存储了主键的ID

InnoDB主键索引的B+Tree高度为多高呢？

假设：一行数据大小为1K，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。

那么对于一个结点，它的数据的存储的大小固定为16K，假设有n个关键字，那么就有n+1个指针：

 8n + 6(n+1) = 16 × 1024 ====> n = 1170

也就是说，一个结点可以散发出1171条指针出去，那么每个指针都存放16个数据：

对于高度为2的B+Tree，它能存储的数据条数就是： 1171 × 16 = 18736
对于高度为3的B+Tree，它能存储的数据条数就是： 1171 × 1171 × 16 = 21939856

其实我们不难发现,对于一颗B+Tree，在高度为3的时候，就能存储2200万条数据，那么在查询的时候它的效率就相对于二叉搜索树高很多

4️⃣ 索引的语法

刚刚讨论了这么多的索引相关的知识，但是如何在数据库中操作索引，创建、查看、删除索引呢？在下面会以例子的形式展示关于索引的语法：

⛏ 创建索引

基础语法

	CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name,...)

⚒ 查看索引

基础语法

	SHOW INDEX FROM table_name

🛠 删除索引

基础语法

	DROP INDEX index_name ON table_name;

时间有限，今天只学了索引的前四点，明天再补充后续的知识点：慢查询、最左前缀法则、索引失效、覆盖索引、回表查询、单列索引、设计原则……，尽情期待

📢参考文献

黑马程序员MySQL高级篇
王道计算机考研数据结构

✒写在最后

都看到这里啦~，给个点赞再走呗~，也欢迎各位大佬指正，在评论区一起交流，共同进步！也欢迎加微信一起交流：Goldfish7710。咱们明天见~

求赞.jpeg