MySQL
话题列表 社区 Wiki 优质外文 招聘求职 MySQL 实战教程 社区文档
登录
注册

表`t`的INODE结构文件分析

so_easy 的个人博客 / 5 / 0 / 创建于 3年前 / 更新于 3年前

系列文章：
InnoDB从内分析之Row（一） InnoDB从内分析之Page（二） InnoDB从内分析之区和段（三） INODE结构二进制页分析 表`t`的INODE结构文件分析 表`t`的XDES Entry结构文件分析 从理论去分析一张图

前言

表结构索引：4个索引，8个段–对应8个INode Entry。

  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_md5` (`md5`),
  KEY `idx_matchId_sourceName` (`matchId`,`sourceName`),
  KEY `idx_oddsId_changeTime` (`oddsId`,`changeTime`)

表结构分析：

page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0001>
page offset 00000005, page type <B-tree Node>, page level <0001>
page offset 00000006, page type <B-tree Node>, page level <0001>
page offset 00000007, page type <B-tree Node>, page level <0000>
page offset 00000008, page type <B-tree Node>, page level <0000>
page offset 00000009, page type <B-tree Node>, page level <0000>
page offset 0000000a, page type <B-tree Node>, page level <0000>
page offset 0000000b, page type <B-tree Node>, page level <0000>
page offset 0000000c, page type <B-tree Node>, page level <0000>
page offset 0000000d, page type <B-tree Node>, page level <0000>
page offset 0000000e, page type <B-tree Node>, page level <0000>
page offset 0000000f, page type <B-tree Node>, page level <0000>
page offset 00000010, page type <B-tree Node>, page level <0000>
page offset 00000011, page type <B-tree Node>, page level <0000>
page offset 00000012, page type <B-tree Node>, page level <0000>
page offset 00000013, page type <B-tree Node>, page level <0000>
page offset 00000014, page type <B-tree Node>, page level <0000>
page offset 00000015, page type <B-tree Node>, page level <0000>
page offset 00000016, page type <B-tree Node>, page level <0000>
page offset 00000017, page type <B-tree Node>, page level <0000>
page offset 00000018, page type <B-tree Node>, page level <0000>
page offset 00000019, page type <B-tree Node>, page level <0000>
page offset 0000001a, page type <B-tree Node>, page level <0000>
page offset 0000001b, page type <B-tree Node>, page level <0000>
page offset 0000001c, page type <B-tree Node>, page level <0000>
page offset 0000001d, page type <B-tree Node>, page level <0000>
page offset 0000001e, page type <B-tree Node>, page level <0000>
page offset 0000001f, page type <B-tree Node>, page level <0000>
page offset 00000020, page type <B-tree Node>, page level <0000>
page offset 00000021, page type <B-tree Node>, page level <0000>
page offset 00000022, page type <B-tree Node>, page level <0000>
page offset 00000023, page type <B-tree Node>, page level <0000>
page offset 00000024, page type <B-tree Node>, page level <0000>
page offset 00000025, page type <B-tree Node>, page level <0000>
page offset 00000026, page type <B-tree Node>, page level <0000>
page offset 00000027, page type <B-tree Node>, page level <0000>
page offset 00000028, page type <B-tree Node>, page level <0000>
page offset 00000029, page type <B-tree Node>, page level <0000>
page offset 0000002a, page type <B-tree Node>, page level <0000>
page offset 0000002b, page type <B-tree Node>, page level <0000>
page offset 0000002c, page type <B-tree Node>, page level <0000>
page offset 0000002d, page type <B-tree Node>, page level <0000>
page offset 0000002e, page type <B-tree Node>, page level <0000>
page offset 0000002f, page type <B-tree Node>, page level <0000>
page offset 00000030, page type <B-tree Node>, page level <0000>
page offset 00000031, page type <B-tree Node>, page level <0000>
page offset 00000032, page type <B-tree Node>, page level <0000>
page offset 00000033, page type <B-tree Node>, page level <0000>
page offset 00000034, page type <B-tree Node>, page level <0000>
page offset 00000035, page type <B-tree Node>, page level <0000>
page offset 00000036, page type <B-tree Node>, page level <0000>
page offset 00000037, page type <B-tree Node>, page level <0000>
page offset 00000038, page type <B-tree Node>, page level <0000>
page offset 00000039, page type <B-tree Node>, page level <0000>
page offset 0000003a, page type <B-tree Node>, page level <0000>
page offset 0000003b, page type <B-tree Node>, page level <0000>
page offset 0000003c, page type <B-tree Node>, page level <0000>
page offset 0000003d, page type <B-tree Node>, page level <0000>
page offset 0000003e, page type <B-tree Node>, page level <0000>
page offset 0000003f, page type <B-tree Node>, page level <0000>
page offset 00000040, page type <B-tree Node>, page level <0000>
page offset 00000041, page type <B-tree Node>, page level <0000>
page offset 00000042, page type <B-tree Node>, page level <0000>
page offset 00000043, page type <B-tree Node>, page level <0000>
page offset 00000044, page type <B-tree Node>, page level <0000>
page offset 00000045, page type <B-tree Node>, page level <0000>
page offset 00000046, page type <B-tree Node>, page level <0000>
page offset 00000047, page type <B-tree Node>, page level <0000>
page offset 00000048, page type <B-tree Node>, page level <0000>
... 55条：page offset 00000000, page type <Freshly Allocated Page>

page offset 00000080, page type <B-tree Node>, page level <0000>
page offset 00000081, page type <B-tree Node>, page level <0000>
page offset 00000082, page type <B-tree Node>, page level <0000>
page offset 00000083, page type <B-tree Node>, page level <0000>
page offset 00000084, page type <B-tree Node>, page level <0000>
page offset 00000085, page type <B-tree Node>, page level <0000>
page offset 00000086, page type <B-tree Node>, page level <0000>
page offset 00000087, page type <B-tree Node>, page level <0000>
page offset 00000088, page type <B-tree Node>, page level <0000>
page offset 00000089, page type <B-tree Node>, page level <0000>
page offset 0000008a, page type <B-tree Node>, page level <0000>
page offset 0000008b, page type <B-tree Node>, page level <0000>
... 500条：page offset 00000000, page type <Freshly Allocated Page>

Total number of page: 640:
Freshly Allocated Page: 555
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 82
File Segment inode: 1

结合下面的图感受分析：
表`t`的INODE结构二进制分析

开始分析

INode在第三页，即0x8000~0xbfff。

File Header(38B) + List Node(12B)

00008000: ‘`0A E5 97 54` `00 00 00 02` `00 00 00 00` `00 00 00 00`
00008010: 00 00 00 0F B6 D9 40 1F 00 03 00 00 00 00 00 00
00008020: 00 00 00 00 03 6E’ FF FF FF FF 00 00 FF FF FF FF
00008030: 00 00’ 00 00 00 00 00 00 00 01 00 00 00 00 00 00

- FIL_PAGE_OFFSET：页号，值为`0x02`。

当前该段属于哪个索引，在专门讲解Page的时候提到过。Page.PAGE_INDEX_ID索引ID–表示当前页属于哪个索引。
虽然找到了这个值：00 00 00 00 00 00 07 17，但是我该如何查找是哪个索引呢？

段01

00008030: 00 00 ‘`00 00 00 00 00 00 00 01` `00 00 00 00` `00 00
00008040: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `00 00
00008050: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `00 00
00008060: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `05 D6
00008070: 69 D2` `00 00 00 03` FF FF FF FF FF FF FF FF FF FF
00008080: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008090: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000080a0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000080b0: FF FF FF FF FF FF’ ‘FF FF FF FF FF FF FF FF FF FF
000080c0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000080d0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000080e0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000080f0: FF FF’ 00 00 00 00 00 00 00 02 00 00 00 0C 00 00

Fragment：页号0x03–索引页–非叶子节点–对应id主键。

段02

000080f0: FF FF ‘`00 00 00 00 00 00 00 02` `00 00 00 0C` `00 00
00008100: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `00 00
00008110: 00 01 00 00 00 00 00 EE 00 00 00 00 00 EE` `00 00
00008120: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `05 D6
00008130: 69 D2` 00 00 00 07 00 00 00 08 00 00 00 09 00 00
00008140: 00 0A 00 00 00 0B 00 00 00 0E 00 00 00 0F 00 00
00008150: 00 12 00 00 00 16 00 00 00 18 00 00 00 19 00 00
00008160: 00 1A 00 00 00 1D 00 00 00 1E 00 00 00 1F 00 00
00008170: 00 22 00 00 00 26 00 00 00 27 00 00 00 28 00 00
00008180: 00 2A 00 00 00 2C 00 00 00 2D 00 00 00 2E 00 00
00008190: 00 2F 00 00 00 30 00 00 00 31 00 00 00 34 00 00
000081a0: 00 36 00 00 00 38 00 00 00 3B 00 00 00 40 00 00
000081b0: 00 41’ 00 00 00 00 00 00 00 03 00 00 00 00 00 00

Segment ID：0x02
NOT_FULL_N_USED：值为0x0C。对应下面XDES Entry.Page State Bitmap，都是12。
Fragment：已写满–数据页–叶子节点–对应id主键。
FREE：默认
NOT_FULL：00 00 00 01 00 00 00 00 00 EE 00 00 00 00 00 EE
FULL：默认

分析NOT_FULL：
链表长度为1，值为00 00 00 01。
头尾指针都指向了00 00 00 00 00 EE。前4B表示页号：0x00，后2B表示偏移量0xEE。这是一个XDES Entry的List Node起始位置。部分数据如：

000000d0: 00 00 00 00 00 02 AA AA FE FF FF FF FF FF FF FF
000000e0: FF FF FF FF FF FF ‘`00 00 00 00 00 00 00 02` `FF FF
000000f0: FF FF 00 00 FF FF FF FF 00 00` `00 00 00 04` AA AA
00000100: AA FF FF FF FF FF FF FF FF FF FF FF FF FF’ 00 00

- `Segment ID`：0x02

段03

000081b0: 00 41 ‘`00 00 00 00 00 00 00 03` `00 00 00 00` `00 00
000081c0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `00 00
000081d0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `00 00
000081e0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00` `05 D6
000081f0: 69 D2` `00 00 00 04` FF FF FF FF FF FF FF FF FF FF
00008200: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008210: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008220: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008230: FF FF FF FF FF FF’ FF FF FF FF FF FF FF FF FF FF
00008240: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008250: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008260: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008270: FF FF’ 00 00 00 00 00 00 00 04 00 00 00 00 00 00

Fragment：页号0x04–索引页–非叶子节点–对应xx索引。

段04

00008270: FF FF ‘00 00 00 00 00 00 00 04 00 00 00 00 00 00
00008280: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
00008290: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
000082a0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 05 D6
000082b0: 69 D2 00 00 00 0C 00 00 00 0D 00 00 00 13 00 00
000082c0: 00 17 00 00 00 20 00 00 00 21 00 00 00 24 00 00
000082d0: 00 25 00 00 00 35 00 00 00 39 00 00 00 3C 00 00
000082e0: 00 3D 00 00 00 3E 00 00 00 42 00 00 00 43 00 00
000082f0: 00 44 FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008300: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008310: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008320: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008330: FF FF’ 00 00 00 00 00 00 00 05 00 00 00 00 00 00

Fragment：未写满–数据页–叶子节点–对应xx索引。

段05

00008330: FF FF ‘00 00 00 00 00 00 00 05 00 00 00 00 00 00
00008340: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
00008350: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
00008360: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 05 D6
00008370: 69 D2 00 00 00 05 FF FF FF FF FF FF FF FF FF FF
00008380: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008390: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000083a0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000083b0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000083c0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000083d0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000083e0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000083f0: FF FF’ 00 00 00 00 00 00 00 06 00 00 00 00 00 00

Fragment：页号0x05–索引页–非叶子节点–对应xx索引。

段06

000083f0: FF FF ‘00 00 00 00 00 00 00 06 00 00 00 00 00 00
00008400: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
00008410: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
00008420: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 05 D6
00008430: 69 D2 00 00 00 14 00 00 00 15 00 00 00 1C 00 00
00008440: 00 29 00 00 00 33 00 00 00 45 00 00 00 48 FF FF
00008450: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008460: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008470: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008480: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008490: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000084a0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000084b0: FF FF’ 00 00 00 00 00 00 00 07 00 00 00 00 00 00

Fragment：未写满–数据页–叶子节点–对应xx索引。

段07

000084b0: FF FF ‘00 00 00 00 00 00 00 07 00 00 00 00 00 00
000084c0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
000084d0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
000084e0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 05 D6
000084f0: 69 D2 00 00 00 06 FF FF FF FF FF FF FF FF FF FF
00008500: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008510: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008520: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008530: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008540: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008550: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008560: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008570: FF FF’ 00 00 00 00 00 00 00 08 00 00 00 00 00 00

Fragment：页号0x06–索引页–非叶子节点–对应xx索引。

段08

00008570: FF FF ‘00 00 00 00 00 00 00 08 00 00 00 00 00 00
00008580: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
00008590: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 00 00
000085a0: 00 00 FF FF FF FF 00 00 FF FF FF FF 00 00 05 D6
000085b0: 69 D2 00 00 00 10 00 00 00 11 00 00 00 1B 00 00
000085c0: 00 23 00 00 00 2B 00 00 00 32 00 00 00 37 00 00
000085d0: 00 3A 00 00 00 3F 00 00 00 46 00 00 00 47 FF FF
000085e0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
000085f0: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008600: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008610: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008620: FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF
00008630: FF FF’ 00 00 00 00 00 00 00 00 00 00 00 00 00 00

Fragment：未写满–数据页–叶子节点–对应xx索引。

参考

1、 blog.csdn.net/mysql_lover/article/...
2、mysqlserverteam.com/innodb-tablesp...
3、dev.mysql.com/doc/dev/mysql-server...
4、blog.jcole.us/2013/01/04/page-mana...

本作品采用《CC 协议》，转载必须注明作者和本文链接
so_easy
见习助教 285 声望
搬砖爱好者！
讨论数量: 0
排序：
时间 投票
(=￣ω￣=)··· 暂无内容！

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容，与人为善，比聪明更重要！
支持 MD 帮助
so_easy
未填写
文章
28
粉丝
16
喜欢
10
收藏
22
排名：1265
访问：3300
私信
所有博文
文章归档
1 篇 2022 年 6 月 2 篇 2022 年 3 月 1 篇 2021 年 9 月 1 篇 2021 年 3 月 1 篇 2021 年 2 月 1 篇 2021 年 1 月 3 篇 2020 年 11 月 1 篇 2020 年 10 月 14 篇 2020 年 9 月 3 篇 2020 年 8 月
最新文章 最受欢迎
1年前 DDD- 目录 - 极客搬运 2年前 sync.Once和自己加锁有什么区别吗？ 2年前 国际化思考 2年前 DD : Go/PHP 研发工程师/专家（国际化增长） 3年前 小白谈一谈：命令在redis中是怎么被执行的呢？
2 基于Yii2对RabbitMQ的基本用法封装及队列实现(一) 2 模拟supervisor的进程管理 1 InnoDB从内分析之Row（一） 1 搬砖PHP-FPM的默认配置 1 DDD- 目录 - 极客搬运
博客标签
rabbitmq
1
 yii2
1
社区赞助商
成为赞助商

关于 LearnKu

LearnKu 是终身编程者的修道场
做最专业、严肃的技术论坛
LearnKu 诞生的故事

资源推荐

  • 《社区使用指南》
  • 《文档撰写指南》
  • 《LearnKu 社区规范》
  • 《提问的智慧》

    • 服务提供商

    其他信息

  • 成为版主
  • 所有测验
  • 联系站长（反馈建议）

    • 粤ICP备18099781号-6 | 粤公网安备 44030502004330号 | 违法和不良信息举报

    由 Summer 设计和编码 ❤

    请登录

    内容举报
    匿名举报，为防止滥用，仅管理员可见举报者。

    我要举报该，理由是：

    取消