MySQL索引与执行计划

索引的定义

索引是存储引擎用于快速寻找记录的一种数据结构

• 索引本质是一种数据结构（一定要记住）
• 索引作用于存储引擎层
• 索引的效果是用来提高查询效率

索引的优点和缺点

1. 减少了服务器需要扫面的数据量
2. 帮助服务器避免排序和临时表
3. 索引可以将随机IO转换成顺序IO

索引分类

基于数据结构分类

• B树
• R树
• 哈希
• FullText （全文索引，效果不咋地一般用ES代替）
• GIS地图索引

基于功能分类

1. 辅助索引（二级索引）
2. 聚集索引（聚簇索引）

---

索引的数据结构基础

有序数组

以数组作为索引的数据结构

排序成功的数组，性能很优秀，等值查询，范围查询等性能相当不俗，

但是因为更新数据的时候，为了维护数组的有序性，必须移动后面的所有记录，成本太高

只适合存储静态数据（不用更新的数据，比如去年人口中数这一类不会更新的数据）

哈希结构

图解：

原理：

1. 对索引的键进行哈希运算，得到的哈希值存在key中，value存放指向数据行地址的指针
2. 如果多个键进行哈希运算得到的值发生冲突，此时会生成一个列表，需要对列表进行顺序遍历

特点：

• 哈希值不是递增的，所以不需要维护，新的值插入速度很快，无脑追加

• 哈希索引只包含哈希值和行指针，不存放数据

• 无序状态，区间范围查询速度很慢（需要全表扫面）

• 等值查询很快（=，in() ,<=>）

• 如果哈希冲突太多的话，维护代价很高

在mysql中的应用

InnoDB有一个特殊的功能“自适应哈希索引”

当InnoDB注意到某一些索引值被使用得十分频繁，InnoDB会在内存中基于B+树索引之上再创建一个哈希索引，这样会同时具备哈希索引的优点（完全内部行为，用户无法控制或者配置）

B树

B树的特点：

数据分布在整棵树的所有节点上

所有性能等价于全集的二分查找

搜索有可能在枝节点结束查找

所有叶子节点位于同一层

B+树

 

实例

# 以为查找53为例子
# 1.去根节点寻找 因为53大于28小于65 所以命中第二的数据
# 2.第二步 去第二层寻找因为53>50 所以去50指针指向的下一层寻找
# 3. 寻找到53这个关键字 查询完成

B+树的特点

• 所有数据都会在叶子节点出现
• 所有叶子节点都已经排序
• 上层节点保存下层节点的最小值
• 所有枝节点相当于叶子节点的稀疏索引
• 不可能在非叶子节点命中
• 叶子节点新增指向兄弟的双向指针（提高范围查询的效率）
• 叶子节点保存关键字和指向数据行的指针

B*树

特点：

在B+树的基础上，所有的枝节点增加了指向兄弟节点的双向指针（是双向指针，画图没画好）

所以现在InnoDB默认使用B*树索引

B*树在MySQL中索引的应用

因为mysql中存储模型为 一个表就是一个段，一个段有多个分区组成，而一个分区是有64个连续的页组成，

而对于索引，一页的默认大小为16k，

所以假设一个id自增主键bigint占8个字节

指针占6个字节，一个节点的内存大概是14个字节，所以，一个数据页能存储的最多的叶子节点数量大约为1100左右

按照mysql官方指定的一个b+树最好是3层

数据量大概为1170*117*1170 约等于16亿数据 但是尽量不要存这么多

---

辅助索引

辅助索引是如何生成的呢

1. 管理员选择一个字段（列）创建一个辅助索引
2. MySQL会自动将此列的值取出来
3. MySQL会对此列的值进行自动排序
4. 按照从小到大的顺序均匀的存储到B*树索引的叶子节点（叶子节点的值都会保存对应主键索引的id）
5. 生成枝节点和根节点

是如何查找数据的呢？

1. 通过索引找到关键字和对应数据行的指针（页码）
2. 回表，找到对应的数据行
3. 但是如果数据行没有排序成功呢（页码没有排序）

聚簇索引（Mysql自己维护）

一般是主键列

1. MySQL一般会选择主键列作为聚集索引列，如果没有主键会选择唯一键，实现不行会生成隐藏唯一键（>5.6)
2. mysql存储数据时候，会按照聚集索引列的值，有序存储数据行
3. 聚集索引直接将原表的数据页作为叶子节点，提取聚集索引列的最下值向上生成枝节点和根节点
4. （聚集索引的叶子节点保存的就是数据行）

所以聚集索引和辅助索引的最大区别就是叶子节点不一样

覆盖索引（最完美的索引，不需要回表）

聚簇索引和辅助索引的区别

聚簇索引是InnoDB默认创建的，一般情况下只有一个，而辅助索引可以有多个

聚簇索引的叶子节点直接存放整行数据内容，而辅助索引的叶子节点存放的是有序值和主键索引的地址

辅助索引细分类

单列索引，联合索引，唯一索引