焱老师带你学习MYSQL系列第三篇 (MYSQL单表访问方法)-CSDN博客

本文链接：https://blog.csdn.net/weixin_44669461/article/details/126022387

前记

小明问 : SQL 执行慢我该如何处理 , 以及大家常说的索引究竟是什么 ?

1 . 索引简介

MYSQL 中所有索引都是B+树索引 .

1 . 1 b+树

B+树特点 :

在这里插入图片描述
同一层级下从左向右依次变大
非叶子节点 (不是最后一层节点) 不存储数据 .

1 . 2 索引分类

索引作用 : 为了查询主键字段更快
分类主要为聚簇索引以及二级索引 .

1 . 2 . 1 聚簇索引

特点 :
页内记录(包括叶子跟非叶子节点)按照主键大小顺序排成一个单向链表
各个存放用户记录的页按照主键大小构成双向链表
存放目录项的页按照不同层级 , 同一层级按照逐渐大小构成双向链表
叶子节点是完整的用户记录

1 . 2 . 2 二级索引

特点 :
与聚簇索引类似
二级索引叶子节点存储出的是主键值 + 页号
目录项为被索引列 + 页号 + 主键值 (最小)
排序方式 : 相同则按照主键从小到大排序

1 . 3 索引创建过程

当创建索引时 , 哪怕表内没有数据也会创建一个根页面 , 当根页面用完之后 , 会把所有的数据复制到新页 , 对整个页进行页分裂 , 同时根页面升级为目录项页 , 并把跟页的页号记录在某个地方 , 方便使用这个索引时直接访问根页面

1 . 4 聚簇索引如何通过主键在页中查找数据 (!!重要)

mysql会将一个页内的数据进行分类比如该页有100条数据 . 会近似平均取其中10条(作为槽 (槽内数据主键id排序永远 > 该槽)) , 该10条记录会在页中以双向链表的形式出现 .
当有查询发生时 , 会拿着主键 id从头开始进行比较 , 当前槽的主键id < 该主键id , 并且满足下一个槽的id > 该主键 id , 则说明该id在这个槽内 . 循环该槽的数据 , 找到对应的记录 .

1 . 5 索引形成过程

1 . 6 索引下推

当前的索引虽然不能是所有判断条件全部命中索引 , 但条件判断可以在索引里面进行判断 .
例子 : 有一个联合索引 key1_key2_key3 , 对于以下sql可以实现索引下推
select * from a where key1 = 1 and key3<100; 虽然条件没有去全部命中 , 但减少了回表次数

1 . 7 索引下推

1 . 8 索引合并

在单次查询中可能会使用多个索引来完成一个查询

1 . 8 . 1 intersection索引合并

当有a , b 索引都满足查询SQL 时 , 会同时走 a , b 索引 , 并且两个索引间使用and连接对走两次结果的id 取交集 .

1 . 8 . 2 union索引合并

当有a , b 索引都满足查询SQL 时 , 会同时走 a , b 索引 , 并且两个索引间使用or连接对走两次结果的id 取并集 .

1 . 9 索引访问方式

1 . 如何访问 :
在正常的select语句前 + explain 关键字
2 . 查询索引分类
1 . const (通过主键或唯一二级索引与常数等值比较)
2 . ref (普通的二级索引列与常数进行等值比较且每获取到一条二级索引记录立刻回表 )
3 . range (对应的扫描区间为若干单点扫描区间或者范围扫描区间)
4 . index(仅仅需要扫描索引列不需要回表就可以拿到想要的信息)
5 . all(全表扫描聚簇索引记录)