不会MySQL索引，面试官让回家等通知！

【.com原创稿件】你是索试官不是对于 MySQL 索引的知识点一直都像大杂烩，好像什么都知道，引面如果进行深究的通知话可能一个也答不上来。

 

图片来自 Pexels

假如你去面试，索试官面试官让你聊一下对索引的引面理解，然而你对索引的通知理解仅限于，检索数据就是索试官快，是引面一种数据结构这个层面，那你就只能回家等通知了。通知

为了避免这种尴尬的索试官事情发生，咔咔用时两天将索引的引面内容在自己理解的范围内进行了整理，如有整理不全面的通知地方可以在评论区进行补充和提建议。

MySQL 索引到底是索试官什么

相信大多数伙伴都买过技术类的书籍，看完没看完不知道，引面但是通知目录肯定看的次数最多。

看目录有没有自己目前的痛点，如果有就会根据目录对应的云服务器提供商页码用最快的速度翻阅到相应内容位置。

那么在 MySQL 中同样也是这样的一个道理，MySQL 的索引就是存储引擎为了快速找到数据的一种数据结构。

同样在 MySQL 索引中又分了几种类型，分别为：

B-tree 索引 哈希索引 空间索引 全文索引

下文所有内容均在 InnoDB 的基础上讨论。

为什么要使用索引

①索引可以加快数据检索速度，这也是使用的索引的最主要原因。

②索引本身具有顺序性，在进行范围查询时，获取的数据已经排好了序，从而避免服务器再次排序和建立临时表的问题。

③索引的底层实现本身具有顺序性，通过磁盘预读使得在磁盘上对数据的访问大致呈顺序的寻址，也就是将随机的 I/O 变为顺序 I/O。

这几点不理解就暂时先放着，继续看下文即可，高防服务器会给你一个满意的解释。

任何事物都存在双面性，既然能提供性能的提升，自然在其他方面也会付出额外的代价：

索引是跟数据共存，因此会占用额外的存储空间。 索引创建和维护需要时间成本，这个成本随着数据量的增大而增大。 索引创建会降低数据的增、删、改的性能，因为在修改数据的同时还需要修改索引数据。

InnoDB 为什么使用 B+Tree 而不使用 BTree

聊到这个问题那就必须得分清楚 BTree、B+tree 的区别，首先来看一下 BTree。

Btree 解析

先来看一下 BTree 的数据结构是怎么样的，这里咔咔给提供一个网站地址，可以看到关于数据结构的一些实现过程：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

先来看 BTree 的数据结构，下图是云服务器咔咔已经将数据填充进去的：

 

这里有一个陌生区关于 Max. Degree，这个你可以理解为阶，也可以理解为度。

例如现在这个值设置的是 4，那么在一个节点中最多就可以存储 3 条数据，设置为 5那就可以最多放 4 条记录。

现在可以看到目前只插入了 3 条数据：

那么再加一条数据，节点就会进行分裂，这个也就验证了当阶设置为 n 时，一个节点可存 n-1 条数据。

 

那接着再来插入几条数据看看：

 

想要达到快速检索数据，那就需要满足俩个特性，一个是有序，另一个就是平衡。

从下图中可以看到 BTree 是有一定的顺序性的，平衡性更满足，可以看上文中生成的第一张图。

 

那么在 BTree 中找一个值是怎么找呢?例如现在要找一个值 9，看一下寻找过程。

首先看到的数据是 4，9 是大于 4 的，所以会往 4 的右节点寻找。继续找到范围在 6 到 8 的节点，9 又大于 8，所以还需要往右节点寻找。

最有一步就找到了数据 9，这个过程就是 BTree 数据结构查找数据的执行过程。

 

了解到了 BTree 的数据结构后，我们在来看看在 MySQL 中关于 BTree 是如何存储的。

在下图中 P 代表的是指针，指向的是下一个磁盘块。在第一个节点中的 16、24 就是代表我们的 key 值是什么。date 就是这个 key 值对应的这一行记录是什么。

 

那么此时想要寻找 key 为 33 的这条记录应该怎么找。33 在 16 和 34 中间，所以会去磁盘 3 进行寻找。

在磁盘 3 中进行判断，指针指向磁盘 8。在磁盘 8 中即可获取到数据 33，然后将 data 返回。

那么在这个过程中到底读取了多少条数据呢?在计算之前需要先了解一些知识点。

从 MySQL 5.7 开始，存储引擎默认为 innodb，并且 innodb 存储引擎用于管理数据的最小磁盘单位就是页。

这个页的类型也分为好几种，分别为数据页，Undo 页，系统页，事物数据页。

一般说到的页都是数据页。默认的页面大小为16kb，每个页中至少存储2条或以上的行记录。

那么根据 BTree 数据查找的过程中可以得知一共读取了三个磁盘，那么每个磁盘的大小就是 16kb。

而目前的给的案例寻找了三层，那么三层存储的数据就是：16kb*16kb*16kb=4096kb。

如果按照一条记录所需内存 1kb，那么这三层的 BTree 就可以存储 4096 条记录。

各位数据库的数据少则几百万，多则几千万数据，那么 BTree 的层级就会越来越深，相对的查询效率也会越来越慢。

这个时候是不是应该思考一个问题，那就是为什么在 Btree 中 48kb 的内存怎么就只能存储 4000 多条记录?

问题就出现在 data 上，要知道在计算数据大小时指针地址和 key 的内存都是没有计算在内的，单单就计算了 data 的内存。

因为在 BTree 结构中，节点中不仅存储的有 key、指针地址还有对应的数据，所以就会造成单个磁盘存储的数据相对很少的原因。

为了解决单个节点存储数据量小的问题，于是就演变出另一种结构，也就是下文提到了 B+Tree。

B+Tree 解析

依然如初看一下 B+Tree 的数据结构。为了方便对比，将 BTree 和 B+Tree 的数据结构放到了一起。

 

那么可以看到在 B+Tree 中叶子节点是存放了全量的数据，而非叶子节点只存储了 key 值。

咦!这不是就很好的解决了 BTree 带来的问题吗?可以让每个节点存储更多的数据。每个节点存储的数据越多，那么相对的就是树的深度就不会过深。

了解到了 B+Tree 的数据结构后，我们在来看看在 MySQL 中关于 B+Tree 是如何存储的。

 

从上图很明显就可以看到两点不同：

第一点：B+Tree 所有的数据都存储在叶子节点上。 第二点：B+Tree 所有的叶子节点之间是一种链式环结构。

那么在这个过程中到底读取了多少条数据呢?

如果说 B+Tree 读取数据的深度跟 B-Tree 的深度一样，都是三层，那么同样的道理每个磁盘的大小为 16kb。

那在 B+Tree 中非叶子节点可以存储多少数据呢!一般来说我们每个表都会存在一个主键。

根据三层来计算，第一层跟第二层存储的是 key 值，也就是主键值。

都知道 int 类型所占的内存时 4Byte(字节)，指针的存储就给个 6Byte，一共就是 10Tybe，那么第一层节点就可以存储 16*1000/10=1600。

同理第二层每个节点也是可以存储 1600 个 key。

第三层是叶子节点，每个磁盘存储大小同样安装 BTree 的计算一样，每条数据占 1kb。

那么在 B+Tree 中三层可以存储的数据就是 1600*1600*16=40960000。

从这点来看 B+Tree 存储的数据跟 BTree 存储的数据根本就不是一个级别。

所以可以得出结论：

B+Tree 能保证检索的数据量相对 BTree 是最多的，而且存储的数据量也是最多的。 B+Tree 选择索引时尽量选择所占内存空间小的类型，比如 int 类型。 key 所占内存越小，在节点中存储的范围就越多。

Hash 索引

先来创建一个 hash 索引：

alter table user add index hash_gender using hash(gender); 

存储引擎使用的是 innodb：

 

会发现 name 的索引类型还是为 Btree，在 innodb 上创建哈希索引，被称之为伪哈希索引，和真正的哈希索引不是一回事的，这点一定要明白。

在 Innodb 存储引擎中有一个特殊的功能叫做，自适应哈希索引，当索引值被使用的非常频繁时，它会在内存中基于 BTree 索引之上再创建一个哈希索引，那么就拥有了哈希索引的一些特点，比如快速查找。

哈希索引就是基于哈希表实现的，假设对 name 建立了哈希索引，则查找过程如下图所示，哈希表是根据键值对进行访问的数据结构，它让检索的数据经过哈希函数映射到散列表的对应位置，查找效率非常高。

 

哈希索引存储的是哈希值和行指针，没有存储 key 值、字段值，但哈希索引多数是在内存完成的，检索数据是非常快的，所以对性能影响不大：

哈希索引不是按照索引值排序的，所以也就无法排序。 哈希索引只支持等值操作，不支持范围查找，在 MySQL 中只能只用 =、in 、<>。 哈希索引在任何时候都不能避免表扫描。 哈希索引在遇到大量哈希冲突时，存储引擎必须遍历链表的所有行指针，逐行比较。

B+Tree 跟 BTree 区别

经过了特别漫长的计算、画图现在基本对俩者的区别有一定认识了吧!

咔咔在这里进行总结一下：

B+Tree 叶子节点上存储的是全量数据(key+data)，而非叶子节点只存储 key。 B+Tree 在同样的深度下存储的数据是远远大于 BTree 的。 B+Tree 每个叶子节点都有指向下一个叶子节点的链接。这样的好处在于，我们可以从任意一个叶子节点开始遍历，获取接下来所有的数据。

B+Tree 适合做索引的原因

B+Tree 树非叶子节点只存储 key 值，因此相对于 BTree 节点可以存储更多的数据，每次读入内存的 key 值就更多，相对来说 I/O 就降低。

B+Tree 树查询效率稳定，任何数据的查找都是必须从叶子节点到非叶子节点，所以说每个数据查找的效率几乎都是相同的。

B+Tree 树的叶子节点存储的是全量数据，并且是有序的，所以说只需要遍历叶子节点就可以对所有的 key 进行扫描，在范围查找时效率更高。

以上就是关于 InnoDB 存储引擎为什么使用 B+Tree 作为索引的解析。

聚簇索引、非聚簇索引区别

聚簇索引、非聚簇索引也被称之为主索引、二级索引。那么如何区分聚簇索引和非聚簇索引呢?

首先看一下 InnoDB 引擎下，创建表生成的文件，可以看到有两个 ibd 文件。

 

看到这里不知道大家有没有疑问：为什么看有的文章中也会有 frm 文件呢?但是在这里怎么没有呢?

原因是在 MySQL 8.0 之后将源数据都存储到了表空间中，所以也就不存在 frm 文件喽!

都知道这个 idb 文件会存储数据信息和索引信息。那再来看一下 Myisam 存储引擎创建表生产的文件。

 

从图中可以看到创建一个表会生成三个文件，扩展名分别为 MYD、MYI、sdi：

MYD：是表数据文件(保存数据的文件) MYI：是表索引文件(保存索引的文件)

那么就可以得出一个结论：只要数据跟索引存储在一个文件里，那就是聚簇索引，否则就是非聚簇索引。

这个时候就会有人问了，表中有主键的时候，idb 文件中存储的是主键+数据，那么当没有设置主键时怎么办呢?

记住这一句话，在 InnoDB 中，数据插入时必须跟一个索引值进行绑定，如果没有主键那就选择唯一索引，如果没有唯一索引就会选择一个 6Byte 的 rowid。

表中存在多个索引数据是如何存储的

看了上文的解释，有没有产生过一丝疑问，在 InnoDB 存储引擎下，如果存在多个索引，是不是会产生多个 idb 文件。

在 InnoDB 中数据只会保存一份，如果有多个索引，会维护多个 B+Tree，例如：表字段 id，name，age，sex。

id 设置为主键索引(聚簇索引)，name 设置为普通索引，那么数据到底会存储几份呢?

不管一个表中设置多少个索引，数据只会存储一份，但是这张表会维护多个 B+Tree。

按照这个案例中 id 为主键索引，name 为普通索引，那么在这张表中就会维护俩颗 B+Tree。

id 主键索引跟数据存储在一起，name 索引所在的 B+Tree 中叶子节点存储的是主键 id 的值。

对应的图就是以下两幅图，可以好好的看一下： 

最后给大家总结一个点：在 InnoDB 中，一定有聚簇索引，其它索引都是非聚簇索引。

这里简单提一下：Myisam 中只有非聚簇索引。

索引的几个技术名词

在面试中往往会问这几个关键词，分别为回表、覆盖索引、最左侧原则、索引下推，一定要知道哈!

回表

网上对回表的解释各种各样，咔咔给你说种简单易懂的，但前提是你需要把聚簇索引、非聚簇索引区分清楚。

还是用上边的案例，id 为主键索引，name 为普通索引。此时查询语句为：

select id,name,age from table where name = kaka 

那么这条语句会先在 name 的这颗 B+Tree 中寻找到主键 id，然后在根据主键 id 的索引获取到数据并且返回。

其实这个过程就是从非聚簇索引跳转到聚簇索引中查找数据，被称为回表，也就是说当你查询的字段为非聚簇索引，但是非聚簇索引中没有将需要查询的字段全部包含就是回表。

在这个案例中，非聚簇索引 name 的叶子节点只有 id，并没有 age，所以会跳转到聚簇索引中，根据 id 在查询整条记录返回需要的字段数据。

覆盖索引

覆盖索引，根据名字都能理解的差不多，就是查询的所有字段都创建了索引!

此时查询语句为：

select id,name from table where name = kaka 

那么这条语句就是使用了覆盖索引，因为 id 和 name 都为索引字段，查询的字段也是这俩个字段，所以被称为索引覆盖。

也就是说当非覆盖索引的叶子节点中包含了需要查询的字段时就被称为覆盖索引。

最左匹配

最左匹配原则是在组合索引中存在的。还是用之前表信息：表字段 id，name，age，sex。此时给 name，age 设置成组合索引。

以下语句中那个不符合最左侧原则：

select * from table where name = ? and age = ? select * from table where name = ? select * from table where age = ? select * from table where age= ? and name= ? 

可以自行做一下测验哈!是只有第三条语句不会用到索引，其他的三条语句都会符合最左侧原则。

关于这个最左侧原则远远不止这么简单的，一试就是一个坑，关于这部分内容咔咔后期会在优化文章中提到。

索引下推

还是使用这条 sql 语句：

select * from table where name = ? and age = ? 

索引下推是在 MySQL 5.6 及以后的版本出现的。之前的查询过程是，先根据 name 在存储引擎中获取数据，然后在根据 age 在 server 层进行过滤。

在有了索引下推之后，查询过程是根据 name、age 在存储引擎获取数据，返回对应的数据，不再到 server 层进行过滤。

当你使用 Explain 分析 SQL 语句时，如果出现了 Using index condition 那就是使用了索引下推，索引下推是在组合索引的情况出现几率最大的。

索引存储在什么地方

索引的数据文件是存储在磁盘中的，也是需要进行持久化操作。但是当使用索引时会把数据从磁盘读取到内存中，读取方式为分块读取。

这时就要涉及到操作系统的概念，操作系统在磁盘中获取数据，假设现在要取的数据大小是 1kb，但操作系统并不会只取出你需要的这 1kb，而是会取出 4kb 的数据。

为什么会是 4kb，因为在操作系统中一页的数据就是 4kb。那又为什么只需要 1kb 而取出整页的数据呢?

那就又会涉及到另一个概念那就是局部性原理：数据和程序都有聚集成群的倾向，在访问了一条数据之后，在之后有极大的可能再次访问这条数据和这条数据的相邻数据。

所以说 MySQL 的 InnoDB 存储引擎，在读取数据时也会采取这种局部性原理，每次读取的数据是 16kb。

在 InnoDB 存储引擎下每页的大小默认为 16kb，这个参数也可以进行调整，参数为 innodb_page_size。

最后一点：既然标题问的是索引数据存储在什么地方，在第一句就直接回答了索引是存储在磁盘中，并且以页为单位进行从磁盘往内存读取。

那为什么不直接存储在内存中呢?你有没有这个疑问呢?

如果索引数据只存储在内存中，那么当电脑关机，服务器宕机之后，就需要重新生成索引，这种的效率是十分低的。

总结

以上就是咔咔对索引的理解，在尽最大的可能将知识点说全面。如果还有遗漏，或者文章中有错误的地方还请各位能给出提议。

作者：咔咔

编辑：陶家龙 

征稿：有投稿、寻求报道意向技术人请添加小编微信 gordonlonglong

【原创稿件，合作站点转载请注明原文作者和出处为.com】