Mysql底层索引算法数据结构

SsflzZ02

已于 2022-04-03 16:27:40 修改

阅读量632

点赞数 1

文章标签： mysql sql 数据库

于 2022-04-01 23:28:31 首次发布

本文链接：https://blog.csdn.net/czh451/article/details/123907537

版权

学习目标：

1.掌握索引结构红黑树、hash、B++树
2.掌握千万级别表如何B++树快速查找索引
3.掌握聚集索引、聚簇索引、稀疏索引是什么
4.掌握为什么DBA推荐用自增主键做索引
5.掌握联合索引底层数据结构又是怎么样
6.掌握Mysql最左前缀原则是怎么回事

学习内容：

mysql官网: 索引是帮助Mysql高效获取数据的排好序的数据结构

索引数据结构：

二叉树
红黑树
Hash表
B-Tree

两列七行的表
对上图如
select * from t where t.col2=89;
一般情况下如果没有索引的话那么将会从Col1的第一行开始找数据
0x07->…0x77（第六行，有可能还会继续去找）
即使如此上述只是理论情况下，实际数据是存放在系统磁盘上的，并且有可能是不规则存放(如0x07插入之后，有可能过了几天之后才插入0x56)，因此数据表在磁盘上是随机分布的，不一定是挨在一起的。
因此为了拿一条数据，我们需要跟磁盘进行交互(I/O)，所以我们需要减少跟磁盘交互的次数，因此诞生了索引。
上图索引为二叉树(右边元素大于左边元素key为Col2的值,value为地址(0x07等)，过程:拿到34与89对比，89>34走右边那到89比对符合直接获取值)

在这里插入图片描述

二叉树(mysql不选择，原因是1234那么都会插入右边与上图无差别)

在这里插入图片描述

红黑树(又称为二叉平衡树，但是当数据量大的时候树的高度会非常的高MySQL不选择，如500万行。。。假设树的高度h=20之后数据在叶子节点，
那么查询数据就需要至少20次的磁盘I/O效率也会低下)
因此我们只要限制住树的高度即可(控制在可以接受的I/O次数之内)

在这里插入图片描述

因此只要把多个索引都放在如上图的01内部即可
B-Tree
叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列

mysql底层选用B+tree
在这里插入图片描述
B+树的叶子节点存放数据(有序依次递增)，根节点存放key并且包含了整张表所有的索引元素（冗余索引）。与B树的区别(相邻两个区间有一根指针)
查询:例如查询5
将从根节点开始查找，5会load到内存(RAM)里面去进行比对
如上图所示 5会放置到内存中比对查到7这一页，然后把7底下的 6 8、9都load到内存里面比对拿到6这一页，再将6load到内存里面比对发现5然后取出5的id，去表中查询到id为5的记录

在这里插入图片描述

在mysql中叶节点是分配了16KB(不推荐修改，mysql默认)，
查询语句:SHOW GLOBAL STATUS like ‘Innodb_page_size’;

为什么mysql要设置为16KB?
假设表用bigint做主键
bigint在mysql磁盘空间占8个字节然后对应的页的地址占6个字节
因此能够存放的索引个数为16KB/(8+6)B≈1170个
一行记录最大大概1KB左右的大小因此能够存放16个元素左右
当B+树被撑满之后存放的元素约 1170117016≈2000W+数据(且树的高度为3，经过三次的磁盘I/O就搞定)，并且通常mysql还把根节点直接常驻内存因此只要两次磁盘I/O

问题:B树和B+树为什么mysql要选择B+树
分析:由于一颗B+树放满之后能存放2000W条数据，B树与B+树的不同就是B树的根节点上面也存放数据，因此一个根节点存放记录的话就是1KB
数据放满B树161616(假设树的高度为3)
那么如果要放两千条数据就是 16的h次方=2000W h远大于3
那么在查找的时候就要经过h次的I/O效率远远低于mysql使用B+树

myisam存储引擎叶子节点上存的是数据的地址，遍历B+树之后拿到地址
再去文件里面根据地址拿到数据记录
innoDB存储引擎叶子节点上存的整行的记录，

聚集索引(聚簇索引):叶子节点包含了完整的数据记录(整行记录)
非聚集索引(myisam引擎就是)索引的叶子节点里面只有记录的部分

为什么建议innoDB表必须建主键,并且推荐建整形自增主键？
分析:如果没有建主键，那么mysql会将数据里面一个没有重复数据的列去构建B+树，如果都没有不相同的数据的列的话，mysql会自己奖励一个隐藏的row去构建B+tree，综上所述mysql的资源比较宝贵，这些我们都可以自己去建而不是让mysql去帮我们建立
自增整型主键与UUID主键的比较:UUID比大小是通过AscII码去比对(逐个字符去)而自增整型主键比较大小会远远快于UUID的比较，整形还比UUID一长串字符串大小会小很多，节约资源空间，

然而为什么主键索引要设置自动递增呢?
在B+tree插入元素的时候，假设不是递增的索引树会先去排序然后再去进行平衡最后达到升序的效果。假设树已经有20个元素分别是1-20 这时候假设插入一个1-20之间的数，那么排序的时候会把树先去分裂，然后插入值再进行树的平衡，效率会降低

为什么选用B+tree索引不使用hash索引
分析：对于hash索引的存储是通过一个hash桶(0-10) 然后对数据进行hash运算放入桶中，如hash(张三)=2 hash(李四)=4 hash(王五)=2那么存储的样子是
hash2->张三(记录)->王五(记录)
hash4->李四(记录)
因此在某种情况下有可能hash索引更快于B+tree索引
但是hash的缺陷在于hash运算之后是等值判断不支持范围查询
如= in
那么引出问题B+tree索引就支持范围查找么？
B+tree的叶子节点存在的指针，可以直接通过指针走，不需要一直扫B+tree索引如 >20 <50的数据可以直接通过20->…->50结束

聚集索引与非聚集索引在查询速度上哪个快(单从索引上来说)?
聚集索引扫完索引直接拿到数据，而非聚集索引扫描完还得回表去拿到数据
因此聚集索引查找更快

二级索引中为什么只存放主键索引的id
为了保证数据的一致性与节约空间

在这里插入图片描述

联合索引的数据结构如上
通常工作中不推荐建单独索引，尽量建联合索引(col1,col2,col3)
假设联合索引想成钱 col1，col2，col3 分别类比成百十个
现在钱分别有
472元、257元、223元、137元

索引树里面的排序就是 137->223->257->472
假设这时候有一个查询十位=3、百位=1、个位大于1(不符合最左前缀原则)
mysql自动调整为百位=1、十位=3、个位大于1
索引中百位排序为 1、2、2、4 拿到两个1
索引中十位排序为 2、3(5、7在拿千位的时候已经排除) 拿到一个2
个位索引7、(3、7、2已排除)