MySql索引

1. Mysql索引简介

1.1 索引概念

索引是一个单独的、存储在磁盘上的、排好序数据库结构,它们包含着对数据表里所有记录的引用指针。使用索引的功能在于快速找出在某个或多个列中有一特定值的行。

1.2 索引的分类

按数据结构分类可分为:B+tree索引、Hash索引、Full-text索引
按物理存储分类可分为:聚簇索引、二级索引
按字段特性分类可分为:主键索引、普通索引、前缀索引
按字段个数分类可分为:单列索引、联合索引

注:MySQL的索引类型由存储引擎决定,Mysql5.1之前的默认引擎是MyISAM,之后版本是InnoDB。而InnoDB你支持hash索引,但自适应Hash索引,即:InnoDB中Hash索引的创建由存储引擎引擎自动优化创建,不能人为干预是否为表创建Hash索引。因为hash索引只能精确匹配(如select * from user where name = xx),不支持范围查找。

2. B+树

2.1 B+树的特点

  • 在 B+ 树中,所有数据记录节点都是按照键值的大小存放在同一层的叶子节点上,而非叶子结点只存储key的信息,这样可以大大减少每个节点的存储的key的数量,降低B+ 树的高度

  • B+ 树叶子节点的关键字从小到大有序排列,左边结尾数据都会保存右边节点开始数据的指针。

  • B+ 树的层级更少:相较于 B 树, B+ 每个非叶子节点存储的关键字数更多,树的层级更少所以查询数据更快

  • B+ 树查询速度更稳定:B+ 所有关键字数据地址都存在叶子节点上,所以每次查找的次数都相同所以查询速度要比B树更稳定;

  • B+ 树天然具备排序功能:B+ 树所有的叶子节点数据构成了一个有序链表,在查询大小区间的数据时候更方便,数据紧密性很高,缓存的命中率也会比B树高。

  • B+ 树全节点遍历更快:B+ 树遍历整棵树只需要遍历所有的叶子节点即可,而不需要像 B 树一样需要对每一层进行遍历,这有利于数据库做全表扫描。

2.2 B+树的结构图

image-20220417092116463

Mysql存储的时候以数据页为最小单位,数据页与数据页之间通过双向链表关联,数据与数据页之间通过单项链表关联。

数据页中的数据是按照主键排序(没有主键是由 MySQL自己维护的 ROW_ID 来排序的),每一个数据页中的页号和最小主键构成主键目录

索引页+数据页组成的组成的B+树就是聚簇索引。聚簇索引是 MySQL 基于主键索引结构创建的。

模拟 MySQL 的查找过程,首先从最顶层的索引页开始查找,查找 id=37,因此定位到了索引页16,然后到索引页 16 中继续查找,此时同样能够定位到 id=37 在索引页 3 中,然后继续查找,最终能够定位到数据实在数据页 8 中,加入数据页中8的结构如下:

image-20220417100149016

完整的数据表:

image-20220417100006747

3. 非主键索引

假如现在对name+age(非主键)建立索引,那此时是存放的呢?此时 MySQL 根据会 name+age 维护一个单独的 B+ 树结构,数据依旧是存放在数据页中的,只不过是原来数据中的每条记录写的是 id=xx,现在写的是name=xx,age=xx,id=xx,不管怎么样,主键肯定会存放的。

image-20220417094545460

在插入数据的时候,MySQL 首先会根据 name 进行排序,如果 name 一样,就根据联合索引中的 age 去排序,如果还一样,那么就会根据 主键 字段去排序。插入的原理就是这样子的。

假设现在要根据 name 查找到该条记录,且查询的字段(即 select 后面的查询字段)也仅仅有 name(只要是在 name,age,id 这三个字段中都可以)这个时候是能够直接获取到最终的记录的。

但如果查找的sql语句如下:

1
SELECT * FROM student WHERE name='wx'

那这下子就完蛋了,因为现在虽然根据 name 很快的定位到了该条记录,但是因为 name+age 不是聚簇索引,此时的 B+ 树的数据页中存放的仅仅是自己关联的索引和主键索引字段,并不会存其他的字段,所以这个时候其他的属性值是获取不到的,这时候该怎么办?

这种情况下,MySQL 就需要进行回表查询了。此时 MySQL 就会根据定位到的某条记录中的 id 再次进行聚簇索引查找,也就是说会根据 id 去维护 id 的 B+ 树中查找。因为聚簇索引中数据页记录的是一条记录的完整的记录,这个过程就叫回表

回表的含义:根据非主键索引查询到的结果并没有查找的字段值,此时就需要再次根据主键从聚簇索引的根节点开始查找,这样再次查找到的记录才是完成的。