MySQL回表

一code当先

已于 2023-03-24 15:37:40 修改

阅读量291

点赞数

分类专栏：数据库文章标签： mysql java 数据结构

于 2022-12-08 11:46:48 首次发布

本文链接：https://blog.csdn.net/xp_xpxp/article/details/128234192

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.索引结构

1.1.B-Tree(B树)和B+Tree(B+树)

在这里插入图片描述

前面是B-Tree,后面是B+Tree,两者的区别在于:

B-Tree中,所有的节点都会带有指向具体记录的指针;B+Tree中只有叶子节点才会带有指向具体记录的指针;
B-Tree中,不同的叶子之间没有连在一起;B+Tree中所有的叶子节点通过指针连接在一起;
B-Tree中,可能在非叶子节点就拿到了指向具体记录的指针,搜索效率不稳定;B+Tree中,一定要到叶子节点才可以获取到指向具体记录的指针,搜索效率稳定;

基于上述几点分析,可以得出如下结论:

B+Tree中,由于非叶子节点不带有指向具体记录的指针,所以非叶子节点中可以存放更多的索引项,这样就可以有效降低树的高度,进而提高搜索的效率;

B+Tree中,叶子节点通过指针连接在一起,这样如果有范围扫描的需求,那么实现起来非常容易,而对于B-Tree,范围扫描则需要不停的在叶子节点和非叶子节点之间移动;

一棵B+Tree到底可以存放多少条数据呢?以主键索引的B+Tree为例(二级索引存储数据量的计算原理类似,但是叶子节点和非叶子节点上存储的数据格式略有差异),我们可以简单算一下:

①.计算机在存储数据的时候,最小的存储单元是扇区,一个扇区的大小是512字节,而文件系统(例如XFS,EXT4)最小单元是块,一个块的大小是4KB.INNODB引擎存储数据的时候,是以页为单位的,每个数据页的大小默认是16KB,即四个块;

②.假设数据库中一条记录是1KB,那么一个页就可以存放16条数据(叶子结点);对于非叶子节点存储的则是主键值+指针,在InnoDB中,一个指针的大小是6个字节,假设我们的主键是bigint,那么主键占8个字节(当然还有其他一些头信息也会占用字节我们这里就不考虑了),大概算一下:

16*1024/(8+6) = 1170

即一个非叶子节点可以指向1170个页,那么一个三层的B+Tree可以存储的数据量为:

1170*1170*16 = 21902400 //可以存放2100万条数据

③.在InnoDB存储引擎中,B+Tree的高度一般为2-4层,这就可以满足千万级数据的存储,查找数据的时候,一次页的查找代表一次IO,那我们通过主键索引查询的时候,其实最多只需要2-4次IO操作就可以了;

2.两类索引

1.MySQL中的索引有很多种不同的分类方式,可以按照数据结构分,可以按照逻辑角度分,也可以按照物理存储分,其中按照物理存储方式,可以分为聚簇索引和非聚簇索引;

2.我们日常所说的主键索引,其实就是聚簇索引(Clustered Index);主键索引之外,其他的都是称之为非主键索引,非主键索引也被称为二级索引(Secondary Index),或者叫辅助索引;

3.对于主键索引和非主键索引,使用的数据结构都是B+Tree,唯一的区别在于叶子节点中存储的内容不同(最大的区别):

①.主键索引的叶子节点存储的是一行完整的数据;

②.非主键索引的叶子节点存储的则是主键值;

4.当我们进行查询的时候:

①.如果是通过主键索引来查询数据,例如"select * from user where id = 100",那么此时只需要搜索主键索引的B+Tree就可以找到数据;

②.如果是通过非主键索引来查询数据,例如"select * from user where username=‘zhangsan’",那么此时需要先搜索username这一列索引的B+Tree,搜索完成之后得到主键值,然后再去搜索主键索引的B+Tree,才可以取到一行完整的数据;

对于第二种查询方式而言,一共搜索了两棵B+Tree,第一次搜索非主键索引B+Tree,获取主键值之后再去搜索主键索引的B+Tree,这个过程就是所谓的回表;