搞懂Mysql的索引（页、深度、数据量、IO次数）
作者：
可爱猪猪-帅锅一枚
作者的网名很阔爱，如果喜欢本文章一定要点 喜欢 或者 打赏，拜托~
作者一直在进步，需要你们的支持和鼓励，谢谢!
人生理想：在程序猿界混出点名堂！
为什么要去搞懂Mysql的索引呢？其实有两点：
- 一是如果不明白它，只能基于记忆性和经验性调优，无法明白它的原理，比如索引应该用什么样类型的字段？为什么选择自增字段做索引？什么情况下能使用到索引？如果不搞懂，可能只能机械的去记忆。
- 二面试官对于Mysql的面试，基本是必问知识点。
下面就开始我们的探索，长话短说篇
索引就是为了快速找到你想要的数据。那如何找到你想要的数据呢？
其实索引类型主要有两种HASH索引和B+树索引。
-
HASH索引
它不是我们今天的主角，简单说一下，它实际上是基于HASH的散列，了解过JAVA的HASHMAP基本就了解什么是HASH，通过HASH值能够快速定位到某条数据，它也有它的缺点：
比如不能用于范围查找，只能用于精确，再比如对联合索引做HASH，对于联合索引中的某个字段无法用到索引等。所以就有了我们要聊的B+树索引。
-
B+TREE索引
大多数情况我们采用B+树，那为什么采用B+树呢？既然这么问肯定就有所比对，为什么不采用二叉树及B-TREE呢？这个问题想必很多都知道，直接揭晓谜底
二叉树相对于B+或者B树，一个很大的优势就是二叉树的深度比B和B+树深，增加了磁盘IO，降低了查找性能。
B-树相对B树，B-树的各层节点要存储数据，导致每页能够容纳的节点就很少，直接导致树深度加大。
这里提到了页了概念，下面我就开始真正揭秘了。
知识普及-扇区、块、页
- 扇区是磁盘的最小存储单元
- 块是文件系统的最小存储单元,比如你保存一个记事本，即使只输入一个字符，也要占用4KB的存储，这就是最小存储的意思。
- 页呢？是B+树的最小存储单元
下面用表格总结一下
| 单元 | 谁的（归属） | 最小大小 |
|---|---|---|
| 扇区 | 磁盘 | 512B |
| 块 | 文件系统 | 4K |
| 页 | B+ | 16K |
想了解更多，可参考这篇文章https://www.cnblogs.com/tongongV/p/10952102.html
B+树的索引结构
想必很多人也了解，直接上图，直接盗取一张：
image.png
图上为id：1-12的12条数据，id为主键索引。白色区域就为一个页。上图画的其实存在一个问题，没有很好的展现B+树的特点，
页之间存在一个双向链表看图我们说一下B+树的特点：
- 非叶子节点不存储数据
- 每个页可以容纳更多的节点、直接减小树的深度，减小访问IO（和B-TREE作比较）
- 叶子节点存储数据（称这种索引为聚集或者聚簇索引）
上图我们看到是主键索引，而非主键索引叶子节点也不存储数据，而是存储的的主键。 - 叶子节点的页之间存在双向链表用于范围或者比较查找
关于IO
那上图查找6需要几次IO呢?2次。先从根节点查找，将页数据去到内存一次，然后查找到第2层的节点又是一次IO，所以两次。
基于B+TREE的特点，一般3层的树就可以存储约2000万条数据？那是如何计算的呢？
计算3层索引树能容纳的数据量
博主写着感觉就像在练乾坤大挪移...第一层...第二层...O(∩_∩)O哈哈~
首先两个假设：
- 主键id，我们采用bigint，8字节
- 一条数据大小1KB
- 第一层
一个页16K，每一个索引键的大小8字节（bigint）+6字节（指针大小），因此第一层可存储16*1024/14=1170个索引键。 - 第二层
第二层只存储索引键，能存储多少个索引键呢？1170（这么多个页，有第一层延伸的指针）1170(每页的索引键个数，跟第一步计算一致)=1368900
如果第二层存储数据呢？1170（这么多个页，有第一层延伸的指针）16(16KB的页大小/1KB的数据大小)=18720，也就是能存储一万多条数。 - 第三层
直接看三层能存储多少数据？1170*1170*16=21902400，是不是很强大，此处应该有掌声和鲜花，3次IO就可以查询到2千多万左右的数据，也就是这么大的数据量如果通过主键索引来查找是很快，这就是explain一个sql时，type=const为什么性能是最优的。
回表
简单说一下回表，就是根据非主键索引查找到根节点，拿到根节点后，再去主键索引中查找相应数据。
覆盖索引
覆盖索引也就是直接从索引上就可以获取到相应的数据，不需要回表。
比如两个SQL语句
select name from xxxtable where name='张三';
select * from xxxtable where name='张三';
xxxtable中主键索引为id、非主键索引为name
第一个SQL直接从索引树上获取到数据，
第二个SQL需要跟回到主键索引中获取所需的数据。
因此在遇到性能调优时可以考虑这一点优化。
