mysql数据存储结构分析

最新推荐文章于 2025-06-07 17:40:08 发布

ljt-tiger

最新推荐文章于 2025-06-07 17:40:08 发布

阅读量938

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 数据结构和算法文章标签： mysql 算法数据结构

本文链接：https://blog.youkuaiyun.com/qq_36336332/article/details/119010251

mysql 同时被 2 个专栏收录

22 篇文章

订阅专栏

数据结构和算法

21 篇文章

订阅专栏

二分查找算法

折半查找

考虑用有序数组作为索引的数据结构
有序数组的等值查询和比较查询效率非常高，但是更新数据时可能要挪动大量的数据(index)，所以只适合存储静态的数据
为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高
…

二叉查找树

BST(Binary Search Tree) BTREE

特点：左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表

左子树节点 < 父节点
右子树节点 > 父节点
二叉查找树既能够实现快速查找，又能够实现快速插入
查找耗时是和树的深度相关，在最坏的情况下时间复杂度会退化成O(n)，插入顺序刚好是顺序的时候，变成斜树，和顺序查找效率是没有区别的

动图演示网址

https://www.cs.usfca.edu/~galles/visualization/BST.html

AVL树平衡二叉查找树

Balanced Binary Search Tree

特点：左右子树深度差绝对值不能超过 1

右右型 --》左旋

左左型 --》右旋
AVL 树在插入和更新数据的时候执行了一系列的计算和调整的操作

动图演示网址

https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

为什么不能直接用平衡二叉查找数来作为索引的存储结构呢？

索引元素的基本构成包含：【索引键值、真实数据地址、左右节点的指针】，这些数据是存储在磁盘上，由于数据从磁盘加载数据到内存，最小的单位Page，占16kb大小(16384bytes)，加载一个树的节点，就会和磁盘发生一次I/O操作，因此一个树的节点只放一个索引元素是不科学的，一个索引元素大小远远达不到一个page大小。由此，我们希望一个树的节点能存储尽可能多的索引元素，
…

B树多路平衡查找树

B Tree(分裂、合并)，不再是二叉，解决了AVL树一个节点没有存满数据，导致数的深度过大，频繁I/O操作的问题

分叉数(路数)永远比关键字数多1【分叉数 = 关键字+1】，比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点

节点拥有的子树数量称为度（Degree）

定义：如果关键字为N，那么就会有Degree = N+1

那么到底可存放多少关键字？

与索引数据类型大小有关，mysql键值自增，int类型的能存放更多，推荐使用自增
比如 Max Degree（路数）是 3 的时候，我们插入数据 1、2、3，在插入 3 的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有 4 个指针，子节点会变成 4 路，所以这个时候必须进行分裂。把中间的数据 2 提上去，把 1 和 3 变成 2 的子节点。如果删除节点，会有相反的合并的操作
从这个里面我们也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么我们【不要在频繁更新的列上建索引】，或者为什么【不要更新主键】

动图演示网址

https://www.cs.usfca.edu/~galles/visualization/BTree.html

但实际上，mysql索引的存储结构不是B树
…

B+ 树

加强版多路平衡查找树，

关键字数量 = 路数
B+Tree 的内节点（根节点和枝节点）中都不会存储数据，只有叶子节点才存储数据；可以理解为内节点是叶子节点的索引。搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点
它是 B Tree 的变种，B Tree 能解决的问题，它都能解决
扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据）
B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）
在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构
根据左闭右开的区间 [ ) 来检索数据

动图演示网址

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

操作系统与磁盘交互

一个树的节点就是一个page的大小，存储（键值、数据磁盘地址、子节点引用），存储的数据太少，会增加I/O次数
…

InnoDB 的存储结构

MySQL InnoDB 的存储结构分为 5 级：表空间、段、簇、页、行

表空间(table space)：系统表空间、独占表空间、通用表空间、临时表空间、Undo 表空间
段(segment)：表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等，段是一个逻辑的概念。一个 ibd 文件（独立表空间文件）里面会由很多个段组成。创建一个索引会创建两个段，一个是索引段：leaf node segment，一个是数据段：non-leaf node segment。索引段管理非叶子节点的数据。数据段管理叶子节点的数据。也就是说，一个表的段数，就是索引的个数乘以 2
区/簇(extent)：一个段（Segment）又由很多的簇（也可以叫区）组成，每个区的大小是 1MB（64个连续的页）
页/块(page)：一个簇中有 64 个连续的页(1MB／16KB=64)。这些页面在物理上和逻辑上都是连续的,是存储引擎磁盘管理的最小单位，通过 innodb_page_size 设置。一个表空间最多拥有 2^32 个页，默认情况下一个页的大小为 16KB，也就是说一个表空间最多存储 64TB 的数据
```
SHOW VARIABLES LIKE 'innodb_page_size';
```
行(row)：InnoDB 存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。hbase是列存放
当我们用树的结构来存储索引的时候，访问一个节点就要跟磁盘之间发生一次I/O。InnoDB 操作磁盘的最小的单位是page(磁盘块)，大小是 16K(16384 字节)。那么，一个树的节点就是 16K 的大小。如果我们一个节点只存一个【键值+数据+引用】，例如整形的字段，可能只用了十几个或者几十个字节，它远远达不到 16K 的容量，所以访问一个树节点，进行一次 IO 的时候，浪费了大量的空间
向表中插入数据，是存储在page的逻辑单位里边，假设一行数据大小为 1k，那么一页可以放16行数据

查询某张表所使用的磁盘空间大小

select  
CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len,
CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') AS index_len
from information_schema.`TABLES`
where table_schema = 'innodb_test' and table_name = 'user_innodb'