Performance analysis

ZZHHWW
86 阅读9分钟

表空间

juejin.cn/post/698879…

页结构&行结构

www.processon.com/diagraming/…

fileHeader,pageHeader存储内容简介

juejin.cn/post/706891…

除了 User Records和Free Space 以外所占用的内存是 38+56+26+8=128 字节，每一页留给用户数据的空间就还剩 16×1024−128=15232 字节（保留了1/16）当然这是 没有算页目录的大小也就是最小值

存储的记录会按照我们指定的行格式存储到User Records部分

插入数据的过程:

  • 一开始生成页的时候是没有 User Records的
  • 当我们每次插入一条记录的时候会从空闲空间Free Space 申请一块空间到User Records来进行用户数据的存储
  • 直到freeSpace 没有剩余空间的时候当前页就满了

什么是页目录

在了解页目录之前我们首先要知道数据是如何在页中存储的？

www.processon.com/diagraming/…

记录头信息

名称大小（bit）描述
预留位11没有使用
预留位21没有使用
delete_mask1标记当前记录有没有删除
min_rec_mask1B+树每层非叶子节点中的最小值记录都会添加该标记
n_owned4表示当前记录拥有的记录数
heap_no13标识当前记录在记录堆中的位置信息
record_type3表示当前的记录的类型 0:表示普通记录 1 表示B+树非叶子节点记录 2 表示最小记录 3:表示最大记录
next_record16表示下一条记录的对应位置

创建一张表

mysql> CREATE TABLE demo_test(     
     c1 INT,    
     c2 INT,    
     c3 VARCHAR(10000),     
       PRIMARY KEY (c1)     
   ) CHARSET=ascii ROW_FORMAT=Compact;

为了方便起见我们在下面画图的过程中将不展示出null值列表,变长字段长度列表 下面在计算的时候会单独分析

  • 插入数据：
  • INSERT INTO demo_testVALUES(1, 100, 'aaaa'), (2, 200, 'bbbb'), (3, 300, 'cccc'), (4, 400, 'dddd')

插入数据后的UserRecords部分:

  • delete_mask这个属性标记当前记录是不是被删除 占用1bit 为0代表当前记录没有被删除 1的时候代表当前记录被删除了 但是当前记录并不会立即被删除而是会将所有的被删除的记录组成一个垃圾链表 那么这些垃圾连表组成的空间也就是可重用的空间之后有新的记录插入的时候就可以使用这部分空间
  • min_rec_mask B+树的每层非叶子节点中的最小记录都会添加该标记
  • n_owned 表示当前分组下记录的总数
  • heap_no 这个属性表示当前记录在本页中的位置，从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。少了 0 和 1 mysql会每个页里边儿加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录 记录是可用比大小的，比较的是主键 不管我们向页中插入了多少自己的记录，Mysql都会自动生成两条伪记录分别为最小记录与最大记录。这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的，如图所示

  • 这两条记录并不属于userRecords记录中所以被单独放在了infimum+supremum 空间 占用26字节
  • 这两条记录代表的heap_no 就是0和1 这也就是为什么我们的插入的数据的heap_no 会从2开始计数了
  • record_type 这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。从图中我们也可以看出来，我们自己插入的记录就是普通记录，它们的record_type值都是0，而最小记录和最大记录的record_type值分别为2和3
  • next_record 它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量 最大记录的next_record的值为0

于是我们上面的图就变成了这个样子 以及删除一条数据的变化:

www.processon.com/diagraming/…

那么在了解了这些之后我们需要思考的问题是: 当我们 执行 sql语句 select * rom test_demo where c1=3 的时候,在数据量大的时候我们即使通过索引页将数据锁定在某一个或者几个数据页之上的时候如果只是通过上面的单链表的数据结构，在数据量大的时候我们可能还是需要遍历整个链表进行数据查找 这样做的效率往往是很低下的,那么mysql是怎么优化的呢？

优化思路: 既然我们的数据是已经是有序的了 那么我们是不是可以使用二分查找法进行查找呢？

  • Mysql 将当前页的所有记录划分到若干槽之中并且将每个分组中的最大值单独提出来【为了二分查找使用】

www.processon.com/diagraming/…

  • 并有如下规定：

  1. 最小记录所在的分组只能包含他自己

  2. 最大记录所在的分组 数据条数在1-8之间

  3. 其他分组的数据条数在4-8之间

所以在有了所谓的页目录的时候mysql页的查找就可以是如下的方式:

  • step1:定义两个指针分别为low=0 high=槽的总个数
  • step2:二分查找 取到中间的槽 并和这个槽对应的最大值进行比较如果max>cur 则high=mid low 不变
  • ....

上面这个过程也就是mysql在页内进行查找的过程 tips:每个槽占用2个字节的长度

估算项目表的存储瓶颈

有了我们上面呢的内容分析我们可以尝试着对自己的业务表进行一个数据瓶颈的估算:【此次估算的前置条件是我们认为3层B+树是性能最佳的】

  • step1: 计算三层B+树并且主键为bigInt类型的时候所拥有的叶子结点的个数

mysql索引页的存储结构:

每一条索引记录当中都包含了当前索引的值 、 一个 5 字节的行标头，一个 6字节 的指针信息 用来指向下一层数据页的指针。

假设我们的主键id为 bigint 型，也就是8个字节，那索引页中每行数据占用的空间就等于 8+6+5=19 字节。每页可以存 15232÷19≈801条索引数据

算上页目录 每个槽按照6条记录计算的 大约需要801/6≈134个槽 也就是268字节的存储空间

把存数据的空间分一点给槽的话 大约可以存787条索引数据【假设主键是int类型的话则可以存储的数据更多大约993条索引数据】

在 B+ 树当中，当一个节点索引记录为 N 条时，它就会有 N 个子节点。由于我们 3 层B+树的前两层都是索引记录，第一层根节点有 N 条索引记录，那第二层就会有 N 个节点，每个节点数据类型与根节点一致，仍然可以再存 NN条记录，第三层的节点个数就会等于 N^2

如下图：

则有：

  • 主键为 bigint 的表可以存放 7872=6193697872=619369 个叶子节点

  • 主键为 int 的表可以存

  • 放 9932=9860499932=986049 个叶子节点

  • step2:

表结构:

开始计算:

1.事务ID+回滚指针 =13 字节

2.可变长度的字段列表 【未超过255字节用一字节表示超过用2字节表示】 4字节

3.null值列表 因为图中有4个可为null的值 所以用4bit表示 由于没有0.5byte所以用1字节表示

4.业务数据: ticketCode varchar(64) --可以 select length(xx),count(1) from table where is_delete=0 group by length(xx) 查看当前字段的长度取个中位数（基数足够大的情况下比较准） 【20字节左右】

opr_date datetime 【8字节】 opr_type int 【4字节】 sub_type int 【4字节】 supplier_id varchar(32) 【20字节左右】 supplier_name varchar(128) 【20字节左右】 opr_user_id varchar(32) 【20字节左右】......

通过如上方法可以计算出我们的表中一行记录所占用的总字节数 约为134字节 通过我们上面的分析我们可以计算出一页可存储的数据条数为 15232/134≈113条记录 我们还是假设6个数据一个槽 则有18个槽 也就是占用36字节 那么在刨除这36个字节之后我们大约可以存储 15232-36/134≈113 条数据

再根据我们上面说的bigInt为主键的三层B+树所能存储的最大叶子结点的个数可以得出数据总量：619369 *113=69,988,697

这个也就是我们这张表大概可以存储的数据的极限了【性能最佳】

优化方案

冷热数据分离

参考书籍： 《mysql是怎样运行的》

avatar
文章
阅读
粉丝