AI 助理
备案控制台
开发者社区 数据库 文章 正文

浅析InnoDB Record Header及page overflow（2）

2022-08-06 81
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 浅析InnoDB Record Header及page overflow

3、案例测试验证过程

我们以场景18为例做测试。测试表结构见下（横屏观看，下同）


[root@yejr.me]> CREATE TABLE `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(10) DEFAULT NULL,
  `c2` varchar(10) DEFAULT NULL,
  `c3` varchar(10) DEFAULT NULL,
  `c4` varchar(10) DEFAULT NULL,
  `c5` varchar(10) DEFAULT NULL,
  `c6` varchar(10) DEFAULT NULL,
  `c7` varchar(10) DEFAULT NULL,
  `c8` varchar(10) DEFAULT NULL,
  `c9` varchar(10) DEFAULT NULL,
  `c10` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;


插入两条数据

[root@yejr.me]>insert into t1 values(1,'a','a','a','a','a','a','a','a','a','a');

[root@yejr.me]>insert into t1 values(2,'b','b','b','b','b','b','b','b','b','b');


用innblock工具查看数据结构：

[root@yejr.me]# innblock innodb/t2_varchar.ibd 3 16
...
-----Total used rows:4 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 3,delflag:N minflag:0 rectype:3
(3) normal record offset:137 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:181 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0


可以看到一条物理记录的长度是 181-137=44字节，分别由以下几部分组成：

  • record header：17字节
    • 其中基础RH=5
    • 共10个varchar列均允许为NULL因此需要额外10bit折算成2字节
    • 另外每个varchar列最大定义存储长度10*4=40，没超过256字节，每个varchar列需要额外1个字节，共10字节
    • 那么5+2+10=17字节
  • db_trx_id: 6字节
  • db_roll_ptr: 7字节
  • id列: 4字节
  • c1 ~ c10列：10字节

我们再用innodb_ruby工具验证一下：


[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-dump
...
records:
{:format=>:compact,
 :offset=>137,
 :header=>
  {:next=>181,
   :type=>:conventional,
   :heap_number=>2,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :nulls=>[],
   :lengths=>
    {"c1"=>1,
     "c2"=>1,
     "c3"=>1,
     "c4"=>1,
     "c5"=>1,
     "c6"=>1,
     "c7"=>1,
     "c8"=>1,
     "c9"=>1,
     "c10"=>1},
   :externs=>[],
   :length=>17}, #所有record header总大小是17字节
 :next=>181,
 :type=>:clustered,
 :key=>[{:name=>"id", :type=>"INT UNSIGNED", :value=>1}],
 :row=>
  [{:name=>"c1", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c2", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c3", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c4", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c5", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c6", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c7", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c8", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c9", :type=>"VARCHAR(40)", :value=>"a"},
   {:name=>"c10", :type=>"VARCHAR(40)", :value=>"a"}],
 :sys=>
  [{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>10518},
   {:name=>"DB_ROLL_PTR",
    :type=>"ROLL_PTR",
    :value=>
     {:is_insert=>true,
      :rseg_id=>100,
      :undo_log=>{:page=>491, :offset=>272}}}],
 :length=>27, #row data总大小是27字节，加上RH的17字节，总共是44字节，和推测结果一致
 :transaction_id=>10518,
 :roll_pointer=>
  {:is_insert=>true, :rseg_id=>100, :undo_log=>{:page=>491, :offset=>272}}}
...



4、什么时候发生page overflow

我们大概知道，一条记录由于有较多的变长列（varchar/text/blob类型），当长度约为page size的一半时，就会发生overflow，会把最长的那个列存储在独立的page中，聚集索引中用20字节的指针指向那个page（dynamic row format时是这么做的，不同row format处理方式也不同）。文档中是这么说的

Variable-length columns are an exception to the rule that column values are stored in B-tree index nodes. Variable-length columns that are too long to fit on a B-tree page are stored on separately allocated disk pages called overflow pages. Such columns are referred to as off-page columns. The values of off-page columns are stored in singly-linked lists of overflow pages, with each such column having its own list of one or more overflow pages. Depending on column length, all or a prefix of variable-length column values are stored in the B-tree to avoid wasting storage and having to read a separate page.

When a table is created with ROW_FORMAT=DYNAMIC, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page. Fixed-length fields greater than or equal to 768 bytes are encoded as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.


Whether columns are stored off-page depends on the page size and the total size of the row. When a row is too long, the longest columns are chosen for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are stored in line.

我们来测试下，一条记录长度到底是多少时，会发生overflow。测试表结构

[root@yejr.me]> CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



我们先进行反推，计算每条记录最长能存储多少字节：

  • 默认的page size是16KB，也就是16384字节
  • FIL Header消耗38字节
  • Index Header消耗36字节
  • File Segment Header消耗20字节
  • Infimum & Supremum 两条虚拟记录共消耗26字节
  • FIL Trailer消耗8字节
  • 由于此时page中最多只有两条物理记录，所以Page Directory消耗4字节
  • 每条记录还需要额外消耗DB_TRX_ID(6B)、DB_ROLL_PTR(7B)共13字节
  • 上述测试表的record header需要消耗7字节
  • 最后可算得每条记录中，text列最多不可超过(16384-38-36-20-26-8-4)/2-13-7-4=8102字节
  • 经过实测，text列最多可存储8101字节，一旦超过该值，就会发生overflow
  • 如果不预设表中各个列数据类型的话，当一条记录超过8125字节（含可变长度的record header，以及DB_TRX_ID、DB_ROLL_PTR的13字节在内）时，就会发生overflow

插入两条测试数据

[root@yejr.me]> insert into t1 select 1,repeat('a',8102);
[root@yejr.me]> insert into t1 select 2,repeat('a',8101);

用innblock工具解析

[root@yejr.me]# innblock innodb/t1.ibd 3 16
...
-----Total used rows:4 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 3,delflag:N minflag:0 rectype:3
(3) normal record offset:127 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:171 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
...


注意到第一条记录的长度是 171-127=44字节，可见的确是做overflow处理了。再用innodb_ruby工具解析整个page


[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-dump
...
records:
{:format=>:compact,
 :offset=>127,
 :header=>
  {:next=>171,
...
 :key=>[{:name=>"id", :type=>"INT", :value=>1}],
 :row=>
  [{:name=>"c1",
    :type=>"BLOB",
    :value=>"",
    :extern=>{:space_id=>214, :page_number=>4, :offset=>38, :length=>8102}}],
...

{:format=>:compact,
 :offset=>171,
 :header=>
  {:next=>112,
   :type=>:conventional,
...
 :key=>[{:name=>"id", :type=>"INT", :value=>2}],
 :row=>
  [{:name=>"c1",
    :type=>"BLOB",
    :value=>
     "aaaaaaa...a"}],
 :sys=>
  [{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>10619},
   {:name=>"DB_ROLL_PTR",
    :type=>"ROLL_PTR",
    :value=>
     {:is_insert=>true, :rseg_id=>36, :undo_log=>{:page=>466, :offset=>272}}}],
 :length=>8118,
 :transaction_id=>10619,
 :roll_pointer=>
  {:is_insert=>true, :rseg_id=>36, :undo_log=>{:page=>466, :offset=>272}}}
...

从page dump的结果能看到，第一条记录溢出存储在另一个page（pageno=4），溢出的字节数是8102，也就是全部放在独立的page里存储了，聚集索引中只保留了20字节的指针。

我们继续测试当表里有两个text列的情况

[root@yejr.me]> CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` text NOT NULL,
  `c2` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[root@yejr.me]> insert into t1 select 1,repeat('a',100),repeat('a',8001);
[root@yejr.me]> insert into t1 select 2,repeat('a',100),repeat('a',8000);



用innblock工具解析


[root@yejr.me]# innblock innodb/t1.ibd 3 16
...
-----Total used rows:4 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 3,delflag:N minflag:0 rectype:3
(3) normal record offset:128 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0
(4) normal record offset:273 heapno:3 n_owned 0,delflag:N minflag:0 rectype:0
...


注意到第一条记录的长度是 273-128=145字节，可见的确是做overflow处理了。再用innodb_ruby工具解析整个page

[root@yejr.me]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-dump
...
{:format=>:compact,
 :offset=>128,
 :header=>
  {:next=>273,
...
:key=>[{:name=>"id", :type=>"INT", :value=>1}],
 :row=>
  [{:name=>"c1",
    :type=>"BLOB",
    :value=>
     "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
   {:name=>"c2",
    :type=>"BLOB",
    :value=>"",
    :extern=>{:space_id=>215, :page_number=>4, :offset=>38, :length=>8001}}],
...
{:format=>:compact,
 :offset=>273,
 :header=>
  {:next=>112,
...
 :key=>[{:name=>"id", :type=>"INT", :value=>2}],
 :row=>
  [{:name=>"c1",
    :type=>"BLOB",
    :value=>
     "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"},
   {:name=>"c2",
    :type=>"BLOB",
    :value=>
     "a..."}],
 :sys=>
  [{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>10686},
   {:name=>"DB_ROLL_PTR",
    :type=>"ROLL_PTR",
    :value=>
     {:is_insert=>true, :rseg_id=>78, :undo_log=>{:page=>478, :offset=>272}}}],
 :length=>8117,
 :transaction_id=>10686,
 :roll_pointer=>
  {:is_insert=>true, :rseg_id=>78, :undo_log=>{:page=>478, :offset=>272}}}


从page dump的结果能看到，第一条记录溢出存储在另一个page（pageno=4），溢出的字节数是8001，也是全部放在独立的page里存储了，聚集索引中只保留了20字节的指针。

好吧，测试案例就介绍到这里，更多的场景请自行测试。

P.S，如果想偷懒不自己测试，也可以看看我的 InnoDB Record Header消耗测试过程实录（文末提供链接）。

P.P.S，我不是源码级MySQL内核开发者，水平有限，文中难免有误之处，还请多指教。

Enjoy MySQL :)

            </div>
文章标签：
云解析DNS
关系型数据库
NoSQL
索引
MySQL
存储
开发者
xaubllxwtvaqiu
目录
相关文章
5i77ajz5u7ji6
|
机器学习/深度学习 自动驾驶 计算机视觉
目标检测落地必备Trick | 结构化知识蒸馏让RetinaNet再涨4个点
目标检测落地必备Trick | 结构化知识蒸馏让RetinaNet再涨4个点
5i77ajz5u7ji6
693 0
土木林森
|
机器学习/深度学习 TensorFlow API
机器学习实战：TensorFlow在图像识别中的应用探索
【10月更文挑战第28天】随着深度学习技术的发展，图像识别取得了显著进步。TensorFlow作为Google开源的机器学习框架，凭借其强大的功能和灵活的API，在图像识别任务中广泛应用。本文通过实战案例，探讨TensorFlow在图像识别中的优势与挑战，展示如何使用TensorFlow构建和训练卷积神经网络（CNN），并评估模型的性能。尽管面临学习曲线和资源消耗等挑战，TensorFlow仍展现出广阔的应用前景。
土木林森
367 5
dulu~dulu
|
存储
原码，补码的除法
原码，补码的除法
dulu~dulu
551 1
人人都是码农
|
11月前
|
JavaScript 前端开发 jenkins
抛弃node和vscode，如何用记事本开发出一个完整的vue前端项目
本文探讨了在不依赖Node和VSCode的情况下，仅使用记事本和浏览器开发一个完整的Vue3前端项目的方法。通过CDN引入Vue、Vue Router、Element-UI等库，直接编写HTML文件实现页面功能，展示了前端开发的本质是生成HTML。虽然日常开发离不开现代工具，但掌握这种基础方法有助于快速实现想法或应对特殊环境限制。文章还介绍了如何用Node简单部署HTML文件到服务器，提供了一种高效、轻量的开发思路。
人人都是码农
282 10
米果粒
|
人工智能 IDE 开发工具
Python AI 编程助手
Python AI 编程助手。
米果粒
324 5
BetterBench
|
机器学习/深度学习 数据采集 数据挖掘
【2023年第十一届泰迪杯数据挖掘挑战赛】B题：产品订单的数据分析与需求预测 建模及python代码详解 问题二
本文提供了第十一届泰迪杯数据挖掘挑战赛B题问题二的详细解题步骤，包括时间序列预测模型的建立、多元输入时间预测问题的分析、时间序列预测的建模步骤、改进模型的方法，以及使用Python进行SARIMA模型拟合和预测的具体实现过程。
BetterBench
356 1
测个der
Pyside6-第三篇-QToolButton一个的按钮
Pyside6-第三篇-QToolButton一个的按钮
测个der
680 0
愿天堂没有BUG（公众号同名）
|
SQL 安全 网络安全
GitHub点赞飙升！电信大牛的Python渗透测试实战指南
在网络安全领域，会不会编程，是区分“脚本小子”和真正黑客的关键。实际的渗透测试中会遇到各种复杂的网络环境，常用工具不一定能满足需求，这时就需要对现有工具进行扩展，或者编写符合要求的工具、自动化脚本，这都需要一定的编程能力。在分秒必争的 CTF 竞赛中，想要高效地使用自制脚本工具来达成各种目的，更是需要有编程能力。 Python 这两年越来越火！除了语法简单、开发效率高以外，Python 最大的优势就是拥有超多第三方库。很多有名的网络安全工具和安全系统框架都是用 Python 开发的！所以，掌握 Python 编程已经成为网络安全从业者的必备技能之一。如果你想成为一名合格的安全从业者，就不能只会
愿天堂没有BUG（公众号同名）
545 3
土木林森
|
Rust 安全 图形学
Rust图形革新：2D与3D编程的全新体验，它能否颠覆传统？
【8月更文挑战第31天】随着Rust语言的日益成熟，其在图形编程领域的应用逐渐增多。本文将探讨Rust在图形编程中的表现，从2D扩展至3D。通过使用`pixman`库处理2D图形，以及借助`naga`库实现3D渲染，展示了Rust在图形编程中的潜力。尽管与C++相比，Rust的生态仍在发展中，但其安全性与性能使其成为图形编程的重要工具之一，值得开发者关注和学习。
土木林森
581 0
中间件小哥
|
运维 自然语言处理 Kubernetes
如何在 ACK 中使用 MSE Ingress
本文将为大家分享一下 Ingress 标准 和 实现的趋势，介绍一下 MSE Ingress 在这个趋势下的优势和实践，为大家做关键入口选择多一些参考。
中间件小哥
813 99
如何在 ACK 中使用 MSE Ingress

热门文章

最新文章

  • 1
    【YOLOv8训练结果评估】YOLOv8如何使用训练好的模型对验证集进行评估及评估参数详解
  • 2
    Android ADB命令大全(通过ADB命令查看wifi密码、MAC地址、设备信息、操作文件、查看文件、日志信息、卸载、启动和安装APK等)
  • 3
    datagrip2022最新版安装破解与激活教程，亲测可用
  • 4
    ETL工具 kettle
  • 5
    漂亮又好用的Redis可视化客户端汇总
  • 6
    MySQL数据库基础：JSON函数各类操作一文详解
  • 7
    JDK5.0新特性系列---8.泛型编程
  • 8
    html常见兼容性问题？
  • 9
    公路（最小生成树）
  • 10
    具备这3种特质 才能称的上是一个好的 Exploit Kit
  • 1
    一个 .NET 开源免费、功能强大的 UI 自动化库
    34
  • 2
    国产芯片生态：从设计到量产，到底难在哪？
    40
  • 3
    ASP.NET Core 依赖注入的三种服务生命周期
    32
  • 4
    灰度不是赌命：我为什么开始用 AI 帮我“决定要不要继续发版”
    52
  • 5
    别再把 K8s 当大号 Docker 了：我用 Kubernetes 跑数据任务踩过的那些坑
    37
  • 6
    阿里云无影云手机快速入门及使用流程（图文教程）
    38
  • 7
    基于 YOLOv8 的智能火灾识别系统设计与实现— 从数据集训练到 PyQt5 可视化部署的完整工程实践
    49
  • 8
    机器学习时间特征处理：循环编码（Cyclical Encoding）与其在预测模型中的应用
    33
  • 9
    《告别无效等待：大规模第三方库项目的快速增量构建指南》
    19
  • 10
    《从视觉到听觉：游戏状态信息的屏幕阅读器适配底层逻辑》
    22

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    基于Python+Vue开发的口腔牙科预约管理系统