【MySQL】——索引底层结构

最新推荐文章于 2022-05-25 07:21:31 发布

给你一半糖

最新推荐文章于 2022-05-25 07:21:31 发布

阅读量305

点赞数

分类专栏： MySQL 文章标签： mysql b树数据结构

本文链接：https://blog.csdn.net/fjj15732621696/article/details/121758736

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

索引

索引其实也是一种数据结构
索引是帮助MySQL搞笑获取数据的排好序的数据结构

索引数据结构

● 二叉树
● 红黑树
● Hash表
● B-Tree

B-Tree结构

● 叶节点具有相同的深度，叶节点的指针为空
● 所有索引元素不重复
● 节点中的数据索引从左到右递增排序

B+Tree结构

● 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
● 叶子节点包含所有索引字段
● 叶子节点用指针链接，提高区间访问性能

一个索引页MySQL推荐的大小是16kb，大于可以存储1170个数据 1170117016

Hash索引

特点
● 对索引的key进行一次hash计算就可以定位出数据存储的位置
● 很多时候Hash索引要比B+Tree数索引更搞笑
● 仅能瞒住“=”，“IN”，不支持范围查询
● hash冲突问题

MySQL选择B+Tree的原因
● 非叶子节点存储更多的元素，树的高度会越小，性能越高

索引存储位置
不修改的情况下，就是在data下面存储的

存储引擎

MyISAM

● 索引文件和数据文件是分离的（非聚集）
● 叶子节点只是存储了索引的位置，
存储结构
● frm 表结构信息
● MYD MY是MyISAM的缩写D是data，存储的数据
● MYI 存储的索引文件 Index

selcet * from user where user_id=123
// 其中user_id是索引 MYI文件中会按照B+Tree中存储在其中
索引中存放的是数据所在文件的磁盘地址，可以去MYD文件中去定位到所在的位置

InnoDB

特点
● 表数据文件本身就是按照B+Tree组织的一个索引结构文件
● 聚集索引-叶节点包含了完整的数据记录
● 主键索引就是一个聚集索引
● 整张表只有一个聚集索引，就是主键索引

存储结构
● frm表结构信息
● ibd 数据和索引
问题
● 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
○ ibd文件必须要用一个B+Tree来进行组织，如果有主键则使用主键。
○ 假如没有建立主键，则会选择一列数据全都不相等的来组织B+Tree，如果没有选到，MySQL会去建立一个隐藏列，来进行维护B+Tree
○ B+Tree是来进行比对大小，所以整型自带比较，
○ 占用的空间小，节约资源
○ 如果不是自增节点的时候，有的时候需要分裂和平衡
● 为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省存储空间）
○ 叶子节点存放的就是聚集索引的索引值，存放的主键
● 聚集索引和非聚集索引那个查找速度快
○ 聚集索引比较快，不需要跨文件查找，找到叶子节点就能够拿到数据

联合索引

联合索引的存储结构是什么样子？
● 索引最左前缀原理

Explain

explain中的列

EXPLAIN SELECT * FROM des_design WHERE id=2
//其中id是主键

explain select(select 1 from actor where id = 1) from (select * from film where id = 1) der;
在这里插入图片描述

select_type列

primary：复杂查询中的最外层的select
subquery：包含在select中的子查询（不在from子句中）
derived：包含在from子句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

id列

id的值越大越后执行

type列

一种sql的关联类型或者访问类型，就是MySQL中如何查找表中的行，查找数据行记录的大概范围
优先级：
system>const>eq_ref>ref>range>index>ALL 效率逐层递减
一般来说，最好保证查询达到range级别，最好达到ref
const 有疑问？
查询的条件只有一条数据
EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM material_fixed_part WHERE id=2) tmp;

在这里插入图片描述

eq_ref

EXPLAIN SELECT * FROM des_design LEFT JOIN des_design_folder ON des_design.folder_id =des_design_folder.folder_id

在这里插入图片描述

primary key或者unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在const之外最好的类型了

ref类型

可能会有多条结果信息，不是使用了唯一索引，而是使用了普通的索引，或者唯一性索引的部分前缀
EXPLAIN SELECT * FROM des_design WHERE folder_id=123
在这里插入图片描述

range类型范围查找

还是走了索引但是是范围的查找，范围扫描，通常出现在in(),between，><=等操作中，使用一个索引来检索给定的范围的行

EXPLAIN SELECT * FROM des_design WHERE id>45
在这里插入图片描述

index类型

扫描全索引可以拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度比较快。这种查询一般为使用覆盖索引，二级索引一般比较小（没有全表信息），所以一般比all快一点。
这种情况虽然走了索引但是效率并不高，他其实是全部遍历了二级索引的叶子节点，全部取出。

All

全表扫描，扫描聚簇索引，扫描所有的叶子结点。读取的磁盘IO会比较大
EXPLAIN SELECT * FROM test
在这里插入图片描述

key_len

代表使用到的联合索引里面的那些字段
在这里插入图片描述

key_len的计算规则
● 字符串
○ char(n)：n字节长度
○ varchar(n)：如果是utf-8，则长度3N+2，加的2用来存储字符串的长度
● 数值类型
○ tinyint：1字节
○ smallint：2字节
○ int：4字节
○ bigint：8字节
● 时间类型
○ date：3字节
○ timestanp：4字节
○ datetime：8字节
● 如果字段允许为NULL，需要1字节来记录是否为NULL
索引最大长度是768字节，当字符串过长时，MySQL会做一个类似最左前缀索引的处理，将前半部分的字符提取出来做索引