MySQL神器之一索引_索引指向的是页-CSDN博客

本文链接：https://blog.csdn.net/Deronn/article/details/123720682

1.1 索引的基础知识

MySQL的基础存储结构——页。
索引(index)是帮助MySQL高效获取数据的数据结构。索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。（索引类似于字典中的目录，其实简单来说，索引就是一个排好序的数据结构）
在这里插入图片描述

插入记录

在这里插入图片描述

各个数据页可以组成一个双向链表
而每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(非主键，无索引)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。所以说，如果我们写select * from user where username = ‘Java’ 这样没有进行任何优化的sql语句，默认会这样做：1）.定位到记录所在的页,需要遍历双向链表，找到所在的页. 2).从所在的页内中查找相应的记录,由于不是根据主键查询，只能遍历所在页的单链表了.(很明显，在数据量很大的情况下这样查找会很慢！)

1.2 页的结构

File Header
存储上一页和下一页指针(FIL_PAGE_PREV,FIL_PAGE_NEXT)，指针实际上是页在磁盘上的偏移量。此外还有其他数据。
Page Haeder
Infimum & Supremum Records
Infimun & Supremum Records会记录当前页的最小记录和最大记录的开区间，因为实际上Infimum Records会比当前页中的最小值还要小，而Supremum Records会比当前页中的最大值还要大。
User Records
存储真正数据的地方，随着系统运行，数据产生，User Records中的数据会不断膨胀，相应Free Space空间会慢慢变小。
Free Space
插入数据到User Records时，需要向Free Space中申请空间，当Free Space没空间了，会申请一个新的页。
Page Directory
即使知道了要找的数据在当前页，也要一个个遍历才能找到查找的数据。为了解决在页内挨个遍历效率低下的问题，MySQL引入Page Directory。Page Directory是个目录，里面有很多槽位(Slots),每条槽位指向了一条User Records中记录。在一个完整的页中，每隔6条数据就会有一个Slot。MySQL 会在新增数据的时候就将对应的 Slot 创建好，有了 Page Directory ，就可以对一张页的数据进行粗略的二分查找。至于为什么是粗略，毕竟 Page Directory 中不是完整的数据，二分查找出来的结果只能是个大概的位置，找到了这个大概的位置之后，还需要回到 User Records 中继续的进行挨个遍历匹配。
File Trailer
这块主要是为了防止页在刷入磁盘的过程中，由于极端的意外情况（网络问题、火灾、自然灾害）导致失败，而造成数据不一致的情况，也就是说形成了脏页。里面有只有一个组成部分：

1.3 索引原理

索引把无序的数据变成有序的(相对有序)
在这里插入图片描述

要找到id为8的记录简要步骤：

在这里插入图片描述

没有用索引我们是需要遍历双向链表来定位对应的页，使用索引可以通过"目录"快速定位到对应的页，再通过Page Directory快速定位到相应的记录。
其实底层结构是B+树，B+树作为树的一种实现，能够让我们很快的查找到对应的页。

1.4 B树(B-树)和B+树

B树
B树就是常说的"B-树",又名平衡多路查找树(即不止两个子树)。
他跟平衡二叉树的不同点：
1). 平衡二叉树节点最多有两个子树，而 B 树每个节点可以有多个子树，M 阶 B 树表示该树每个节点最多有 M 个子树
2). B 树的所有叶子节点都在同一层
3). B树的每个节点可以表示的信息更多，因此整个树更加"矮胖",这在从磁盘中查找数据(先读取到内存、后查找)的过程中，可以减少磁盘IO的次数，从而提升查找速度。

B树中的每个节点由两部分组成：
1). 关键字（可以理解为数据）
2). 指向孩子节点的指针

B+树
了解了B树后再来了解下它的变形版：B+树，它比B树的查询性能更高。
一颗B+树需要满足以下条件：
1). 节点的子树数和关键字数相同（B 树是关键字数比子树数少一）
2). 节点的关键字表示的是子树中的最大数，在子树中同样含有这个数据
3). 叶子节点包含了全部数据，同时符合从左到右顺序排序的原则

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vE3oZQa7-1648124998125)(https://note.youdao.com/yws/res/3612/WEBRESOURCE397d64199d35f7efae7549163b501a6a)]

简单概括下 B+ 树的三个特点：

1). 关键字数和子树相同
2). 非叶子节点仅用作索引，它的关键字和子节点有重复元素
3). 叶子节点用指针连在一起

B树的优点：
1). 层级更低，IO 次数更少。
2). 每次都需要查询到叶子节点，所以每次查找次数都相同，查询性能稳定。
3). B+树叶子节点的关键字从小到大有序排序，左边结尾数据都会保存右边节点开始数据的指针，叶子节点形成有序链表，范围查询方便。
4). B+树全节点遍历更快，B+树遍历整颗树只需要遍历所有的叶子节点即可(B树需要对每一层进行遍历)，有利于做全表扫描。

一颗B+树：
在这里插入图片描述

1.5 聚簇索引和非聚簇索引

InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。
聚簇索引也叫聚集索引。
每个InnoDB表都有一个特殊的索引，称为聚簇索引，用于存储行数据。创建的聚簇索引的逻辑：

如果创建了一个主键，InnoDB会将其用作聚簇索引（最好是单调递增的）
如果没有为表创建主键，则MySQL会选择一个唯一的有索引的列作为聚簇索引
如果表没有PRIMARY KEY或合适的UNIQUE索引，则InnoDB在包含行ID值的合成列上内部生成一个名为GEN_CLUST_INDEX的隐藏的聚集索引（不出现在desc table中,行ID是一个6字节的字段，随着插入新行而单调增加）

聚簇索引在叶子节点中同时存放了数据和索引。(如下图)
在这里插入图片描述

非聚簇索引又称二级索引，在叶子节点不存放数据，存放了主键，查找时需要通过索引找到主键，再通过主键找到数据(回表)。普通的B+树索引、唯一索引、联合索引都是非聚簇索引。(如下图)
在这里插入图片描述

可以看到聚簇索引后面是直接跟着的数据,而非聚簇索引指向的是聚簇索引的key值. 因此非聚簇索引查询数据需要先查到聚簇索引的key,然后用这个key去查询真正的数据(这个过程称为回表). 也就是说非聚簇索引是需要查询两次如图:
在这里插入图片描述

索引覆盖
如果是非聚簇索引的话会需要回表,查询两次,但是如果要查询得字段,数据直接就在索引上是可以不需要回表的，比如这张表：

create table test(
id int primary key,
age int not null,
name varchar(16),
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE,
KEY `idx_name` (`name`) USING BTREE,
)engine=InnoDB;

查询语句为：select id,age,name from test where age = 13;
直接查询的话,会根据age的索引找到id的key,然后再用id去查询出数据. 但是如果我们创建一个(age,name)的联合索引,情况就不一样了.因为要返回的值,id在联合索引指向的key上,age和name共同组成了联合索引, 因此数据都在(age,name)的联合索引上,并不需要回表在去查询一次,可以大大提高查询得效率.

注:还有在某些count聚合函数使用的时候可以使用覆盖索引来优化count,比如说select count(age) from test.因为age是有索引了,直接使用到的也是age,所以覆盖索引了,无需回表

聚簇索引优点

当你需要取出一定范围内的数据时，用聚簇索引比用非聚簇索引好。
当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。

聚簇索引缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

1.6 索引种类

mysql有主键索引、唯一索引、单列索引、组合索引、全文索引等常用索引。

联合索引（复合索引）的最左前缀原则：
如果你创建一个联合索引, 那这个索引的任何前缀都会用于查询, (col1, col2, col3)这个联合索引的所有前缀就是(col1), (col1, col2), (col1, col2, col3), 包含这些列的查询都会启用索引查询；
其他所有不在最左前缀里的列都不会启用索引, 即使包含了联合索引里的部分列也不行. 即上述中的(col2), (col3), (col2, col3) 都不会启用索引去查询。

按照存储类型，MySQL中有两种存储类型：BTREE和HASH,具体和表的存储引擎相关：
MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。