求教 InnoDB 索引查找方式 , 在线等 .

[zkylm]

抱歉 , 这个帖子会比较长 , 务必请高人解答一下小生的困惑 !
这样的 , 小生非常好奇 innodb 中索引的实现 , 所以今天动手实践了一下 INNODB 索引的结构 , 非常困惑其实怎么搜索的 , 小生列出自己的分析过程 , 请高手对对这个过程给我讲解下 , 不甚感激 !!!


MySQL 版本 : 5.5.30

创建表的语句 :
CREATE TABLE `b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`p` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32753 DEFAULT CHARSET=utf8

另外 , 我打开了 innodb_file_per_table ( 方便取 ibd ) :
innodb_file_per_table ON

另外目前内有数据 :
mysql> select count(*) from b;
+----------+
| count(*) |
+----------+
| 16384 |
+----------+
1 row in set (0.00 sec)

-------------------------- 万能的分割线 --------------------------

使用 py_innodb_page_info.py 对其分析 :
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 <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
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>
Total number of page: 34:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 31
File Segment inode: 1

有 31 个 B-tree Node , 有一个 page level 0001
page offset 00000003, page type <B-tree Node>, page level <0001>
在此小生特意请教第一个问题 : 这个 page level 最高的页是否 B-Tree 的根节点 ? 因为假如只有 10 条数据 , 总大小不超过 16 KB 的话那么它肯定只有一个 B-Tree Node ( 假设 innodb_page_size 为 16KB ) , 这时的 page level 为 0000 . 那么它的索引呢 ? 还是说直接去这个叶子页就可以了？

先放下这个问题 , 将该数据库使用 hexdump 导出为文件具体分析之 :
hexdump -C -v b.ibd>/tmp/b.txt

首先由于好奇 , 我查看了第一个数据页 , 也就是 :
page offset 00000004, page type <B-tree Node>, page level <0000>
根据其 OFFSET 计算其起始位置应为 10000 , 我列出它的 File Header 和 Page Header 部分 , 看一下这个数据页存了多少行记录 :

00010000 35 64 18 57 00 00 00 04 ff ff ff ff 00 00 00 05 |5d.W............|
00010010 00 00 00 00 4d 15 d0 b3 45 bf 00 00 00 00 00 00 |....M...E.......|
00010020 00 00 00 00 00 02 00 49 3a c4 82 40 1d a3 1d 26 |.......I:..@...&|
00010030 00 00 00 05 00 00 01 1f 00 00 00 00 00 00 00 00 |................|
00010040 00 00 00 00 00 00 00 00 00 34 00 00 00 00 00 00 |.........4......|
00010050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 |................|

最后两位不是 , 最后两位是伪记录 infimum 的 Record Header 的前两位 .
根据 Page Header 中的 PAGE_N_RECS = 011f , 得出 , 该页中共有 287 行记录 .
这里提出第二个疑问 : innodb 的叶子节点的单个节点最多不是可以存放 200 行记录吗 ? 最少 2 行吗 ( 低于两条成单链表了 , 这个我能理解 ) ? 但是最多不是 200 行吗 ? 这里为什么是 287 行 ? 版本 ? 还是我的这个认知错误 , 其实可以容纳更多 ?

OK , 废话不多说 , 转到 page level 为 0001 的数据页 , 也就是索引页 . 我列出其 File Header 和 Page Header 以及其前两条记录 , 想通过前两条记录保存的值来请教大湿它是如何搜索的 . 谢谢 .

0000c000 f7 6d 37 26 00 00 00 03 ff ff ff ff ff ff ff ff |.m7&............|
0000c010 00 00 00 00 4d 22 54 59 45 bf 00 00 00 00 00 00 |....M"TYE.......|
0000c020 00 00 00 00 00 02 00 08 01 fe 80 20 00 00 00 00 |........... ....|
0000c030 01 f6 00 02 00 1d 00 1e 00 00 00 00 00 00 00 00 |................|
0000c040 00 01 00 00 00 00 00 00 00 34 00 00 00 02 00 00 |.........4......|
0000c050 00 02 00 f2 00 00 00 02 00 00 00 02 00 32 01 00 |.............2..|
0000c060 02 00 1a 69 6e 66 69 6d 75 6d 00 07 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 10 00 11 00 0d 80 00 00 |supremum........|
0000c080 01 00 00 00 04 00 00 19 00 0d 80 00 02 16 00 00 |................|
0000c090 00 05 00 00 21 00 0d 80 00 05 53 00 00 00 06 04 |....!.....S.....|

当然 , 这里根据 offset 3 得到从 c000 开始 , 所以我截取了该索引页的前两条记录 .
这里提出第三个疑问 : 索引页的 File Header 中的 FIL_PAGE_TYPE , 不应该是 00 03 吗 ? 为什么这里赫然写着 45 bf ( 数据页 ) ?

接下来 , 看到第一条记录 , 根据定义 , File Header + Page Header 共用 38+56=94 字节 , 而其后紧随两条未记录 infimun , 这两条未记录没有 变长列标示也没有 NULL 位标示 , 所以直接为 recoder header + 本生 , 所以 :
01 00 02 00 1a
即为 infimun 的 recoder header , 根据最后两位 , 得到第一条记录为 :
80 00 00 01 00 00 00 04
存储了两个东西 , 主键 , 键值为 1 , 以及所在数据页的 offset 为 00 00 00 04 ( 也就是 10000 ) .
当然根据该条记录的 Recoder Header 的最后两位得出第二条记录为 :
80 00 02 16 00 00 00 05
存储了两个东西主键 , 键值为 : 534 , offset 为 00 00 00 05 .
这里插一个题外话 , 我开始以为我弄错了 , 因为我前面不是得出第一个页面的数据页记录为 287 个嘛 , 因为我的 id 是 auto_increment 的嘛 , 所以我以为这个应该记录的是 288 而不是 534 , 但随后我验证了一下 :

mysql> select count(*) from b where id<534;
+----------+
| count(*) |
+----------+
| 287 |
+----------+
1 row in set (0.00 sec)

验证了得出结论是对的可能因为什么设置导致了 ID 不连续 , 暂且不提 .

那么这里提出最后一个问题 , 也是主题问题 : 索引的结构都看到了 1 , 534 , 如果值位于 1 和 534 之间那么进入 offset 为 00 00 00 04 的页面也就是第一个数据页 , 否则进入第二个 . 首先我这么理解啊对 ?
其次 , 也看到了 , 我 1 万多条记录只有一个索引页 , 这还真的是体现了 B-Tree 在数据库中的高扇出性 . 就我这个例子来说 , 假设索引上有 500 、600 个节点的值 , 那么这个时候搜索某个值是否使用 2 分查找来增加速度 ?

非常感谢各位老湿倾囊相授 ！小生在线等 !

[zkylm]

非常感谢各位老湿 , 是否小生这个问题太小白了 , 各位老湿都觉得太简单了所以不屑回答 ? 好吧 , 小生就当原因如上了 . 经过一天的分析 , 终于 , 楼上所提最后一个问题 , 也是主要问题 , 小生有了自己的解答 . 但是还有那么一丁点一丁丁点的不明之处 . 还请各位老湿不吝赐教 , 不能再像上面一样 , 因为小生的问题太小白就不予作答 . 这是不对的行为 . -------------------------- 万能的分割线 -------------------------- 其实这里犯了一个错误 , 思想上的错误 . 其实各位大湿同小生都知道 , InnoDB 的 B-Tree 索引指向的是数据页而不是行 , 这一点从小生楼上的分析中也可以看出来 . 它的存储类似于 80 00 00 01 00 00 00 04 也就是一个区间的值在某个页中 . 随后进入这个页进行搜索 . 其实搜索某个索引值的过程等同于去叶子节点搜索某行的过程一样 . 下面小生就贴出这个过程来 . 希望各位大湿说错的地方要毫不留情的批评 , 这样才能让知识更彻底 , 更清楚 . 还是拿 page offset 00000003, page type <B-tree Node>, page level <0001> 这个索引页来说吧 , 因为要分析的也就是索引 . PS : 如果大湿功底较好 , 那么紫色部分为废话 , 为一些小白准备的 . 讲的是页的 Page Directory 和行的 Recorder Header 的 n_owned . 我当然知道各位大湿都知道搜索某个具体的值是怎么进行的 , 但是为了避免一小部分比小生还菜的小白 , 这里还是说下比较好 . InnoDB 页的最后两个部分存储的是 Page Directory , 和 File Trailer . 其中 File Trailer 固定占用 8 个字节 , 记录了 checksum 和 FIL_PAGE_LSN 此与小生该贴话题无关 , 不表 . 重要的是 Page Directory . 它存放了记录的相对位置 ( 相对页的位置 , 用页 OFFSET + 这个值便是该记录的位置 ) . 一般称这些记录为 " 槽 " ( Slots ) . 不过 InnoDB 的存储引擎的槽是一个稀疏目录 . 也就是一个槽可能包含多个记录 . 但伪记录 Infimum 的 n_owned 总是唯 1 . 也就代表着它只有一个记录没有范围 . 搜索时 , 数据库先将页载入到内存 , 然后通过 Page Directory 进行二叉查找 . 找到 Slot 的地址之后还需要根据行的 Recorder Header 的 n_owned ( 该槽中有几条记录 ) 进行进一步的定位 . 那么 page offset 00000003, page type <B-tree Node>, page level <0001> 此页的 Page Directory 应该如下所示 ( 红色部分 ) 并且贴出 10000 ( OFFSET : 4 ) 的位置作为参照 : 0000ffe0 00 00 00 00 00 00 00 00 00 70 01 a8 01 74 01 40 |.........p...t.@| 0000fff0 01 0c 00 d8 00 a4 00 63 91 01 93 ec 4d 22 54 59 |.......c....M"TY| 00010000 35 64 18 57 00 00 00 04 ff ff ff ff 00 00 00 05 |5d.W............| 为了更方便读取 , 截取前两条记录作为翻译 ( 请注意 Page Directory 是逆序的 , 且首尾的位置是两条伪记录的位置 ) 00 63 ( c063 ) => 伪记录 infimum ( Recorder Header : 01 0002 00 1a ) 00 a4 ( c0a4 ) => 80 00 09 90 00 00 00 07 ( Recorder Header : 04 00 29 00 0d ) 00 d8 ( c0d8 ) => 80 00 16 87 00 00 00 0b ( Recorder Header : 04 00 49 00 0d ) 也就是说前两条存的分别为主键 2448 , 和主键 5767 . 这里我有一点不解的是 , 这个 n_owned 居然是从 Slot 开始往前的 n_owned 条 . 就拿 c0a4 来说 , 它所指记录的 n_owned 为 4 . 也就意味着这个槽中包括它自己供有 4 条记录 . 那么我列出来 : 0000c070 73 75 70 72 65 6d 75 6d 10 00 11 00 0d 80 00 00 |supremum........| 0000c080 01 00 00 00 04 00 00 19 00 0d 80 00 02 16 00 00 |................| 0000c090 00 05 00 00 21 00 0d 80 00 05 53 00 00 00 06 04 |....!.....S.....| 0000c0a0 00 29 00 0d 80 00 09 90 00 00 00 07 00 00 31 00 |.)............1.| 从 c07d 开始 到 c0a4 , 便是这四条记录 . 分别是 : 1 , offset 4 534 , offset 5 1363 , offset 6 2448 , offset 7 那这里我就好奇了 , 假设说搜索一个 id 为 288 的记录 . 那么 , 根据在此节点 ( 根节点 ) 的 Page Directory 中搜索出应该进入槽 c0a4 处 这里提出第一个疑问 , 也是唯一一个疑问 : 请问大湿到底是进入 00 63 ( 伪记录 ) 还是 c0a4 ( 主键 2448 ) ? 因为我这里还不确定所以假定进入的是 2448 ( 这也是非常合理的解释毕竟 , 00 63 是伪记录 ) 查得该记录为 80 00 09 90 00 00 00 07 ( Recorder Header : 04 00 29 00 0d ) 又因其 Recorder Header 中的 n_owned 4 得出 , 该槽有 4 条记录 . 再次提出上一个疑问 , 因为 Recorder Header 中的最后两位是下一条记录的地址而不是上一条的 , 所以就我这个搜索而言岂不是首先要获取伪记录的地址然后根据它的 Recorder Header 中的最后两位连续向后搜寻 4 位 ? 这不是麻烦了一点 ? 这只是个疑问 , 但我确信这种方式绝对没错 . 那么根据伪记录 Recorder Header 中的最后两位得出下一条记录为 : 80 00 00 01 00 00 00 04 ( 主键 1 , OFFSET 4 , Recorder Header : 10 00 11 00 0d ) 又根据它的 Recorder Header 中最后两位获得下一条记录为 : 80 00 02 16 00 00 00 05 ( 主键 534 , OFFSET 5 ) 显然 , ID 为 288 的记录应该进入第一条记录索引的 00 00 04 中 . 最后一步 , 找到 10000 ( offset 00 04 ) 的数据页再根据上面的过程完整的来一遍找到 288 的记录 . 至此全部完成 . 我的过程可能比较冗长 , 希望各位大湿不厌其烦的帮我梳理下 , 一定要帮小生我解答这个疑问 , 不甚感激！ 其实也不叫疑问我自己已经差不多确定了 , 只是这么做有点反常 , 我想请大湿给一点点的解释已经一点点的指导就 OK , 我清楚 30 个字就足以解答小生的疑惑了 . 非常感谢 !!!

[zkylm]

我自己跟一贴回复 , 和帖子无关 . 就我看到 ( 我这个例子的字段很小 两个 int 字段而已 ) 就我管擦根索引页只有 29 条记录而已 ( 树高 2 ) , 29 条记录一次随机 IO ( 未热机情况下 , 热了机 0 次 . ) 即可随意取到 16384 条记录中的任意一条记录这还真是不实践不知道一实践吓一跳 , 那我可不可以推断 ( 其实和 ext 的文件结构非常相像 , 我觉得 ) , 100 万条记录的树高最多 3 ( 这个不用推断会乘法的都算得出来 ) , 如上我分析了它详细进入某个页查询的过程 , 所有的页查找方法都是一样的 , 所以是不是树高 3 的话 , 也只是比树高 2 多做了一次这样的查找 , 所以可不可以断 : 在据主键查找上 1 万条记录 和 100 万条记录的时间应该无差距 , 因为树高只高了一层意味着只多了一次随机 IO ( 假设 innodb_ buffer_pool_size 并不能将所有的页都装入内存 ) 和一次根据 Page Directory 的二分查找 . 是否可以这么推断 ? 根据这个高扇出性的特性 , 单表 1KW 在优化得当的情况下性能应该也是很不错的了 ? 请大湿指点

[rucypli]

不是不回答你 是确实需要回去翻下姜成尧那本innodb存储引擎才能回答你

[zkylm]

引用 3 楼 rucypli 的回复:

不是不回答你 是确实需要回去翻下姜成尧那本innodb存储引擎才能回答你

他的书回答不了 , 我第二贴的疑问就是来自他那本书 , 如果可以我都想直接问他 , 可惜我没他联系方式 . 你看一下第二版的第四章就知道了 , 按照他的说法 , 查找到 Page Directory 之后 , 数据应该是向后而不是向前查找的 . 而根据我自己实测来看 , 并不是这样的 , 刚好相反 , 而且我确定我没错 , 所以如果有他的联系方式 , 我都想直接联系他 . 这不没办法嘛 . 在这里发了一贴

[zkylm]

引用 3 楼 rucypli 的回复:

不是不回答你 是确实需要回去翻下姜成尧那本innodb存储引擎才能回答你

二一个他书中的索引 ( 不过是辅助索引 ) 的 File Header 总的 FIL_PAGE_TYPE 是 0003 , 不过我加了 10000 多条之后 , 查看聚集索引的 FIL_PAGE_TYPE , 是 45 bf . 所以这里我就有疑问了 , 他书中并没有将主索引分析到这么详细 , 所以我不知道这是不是我弄错了 .

[zkylm]

引用 3 楼 rucypli 的回复:

不是不回答你 是确实需要回去翻下姜成尧那本innodb存储引擎才能回答你

对于 4.4.5 章 ( 第二版 ) 的解释 , 跟我自己实测的结果刚好相反 , 而我反复测试结果确实我梳理的思路是对的 , 正式基于此点 , 我才不厌其烦的发了这么长的帖子希望有人能解答 , 到底是我弄错了 , 还是版本不一样了 , 所以算法也不一样了 , 或者说还是姜承尧大湿写书的时候有那么一点点的疏漏 .

[a441209821]

引用 3 楼 rucypli 的回复:

不是不回答你 是确实需要回去翻下姜成尧那本innodb存储引擎才能回答你

引用 3 楼 rucypli 的回复:

不是不回答你 是确实需要回去翻下姜成尧那本innodb存储引擎才能回答你

我上面提出的第二个疑问 : 这里提出第二个疑问 : innodb 的叶子节点的单个节点最多不是可以存放 200 行记录吗 ? 最少 2 行吗 ( 低于两条成单链表了 , 这个我能理解 ) ? 但是最多不是 200 行吗 ? 这里为什么是 287 行 ? 版本 ? 还是我的这个认知错误 , 其实可以容纳更多 ? 我记得书中有讲过 , 一行可以存 2 - 200 行记录 , 2 我说过了我可以理解 , 要是 1 的话这玩意就成单链表了 . 《InnoDB 技术内幕》第二版 , 4.2.5 章节中详细提到过 : 每个页的行记录也是有硬性规定的 , 最多允许存放 16KB / 2 - 200 行记录 , 即 7992 行记录 . 我这个问题正是对这句话提出来的 , 首先 既然最多是 200 行记录 , 那我实得 287 行是？ 第二个 7992 行记录这没有任何解释真心让小生我困惑啊 , 大湿 . 所以提出了这个疑问 , 再看第三个疑问 这里提出第三个疑问 : 索引页的 File Header 中的 FIL_PAGE_TYPE , 不应该是 00 03 吗 ? 为什么这里赫然写着 45 bf ( 数据页 ) ? 根据姜大湿所讲 , 索引节点的 FIL_PAGE_TYPE 应该是 00 03 叶子节点 ( innodb 嘛 , 叶子肯定是数据了 ) 才是 45 BF , 那么我的这个 page level 为 <0001> 的这个节点毫无疑问是索引节点 , 它存储的两个字段也确实是一个主键值和一个指向叶 OFFSET 的指针 . 那么 , 这里是版本变了还是小生我理解有问题 ? 故此提出了这个疑问 . 最后一个疑问嘛 , 我已经详细描述了 , 对 Page Directory 的检索方式提出的 , 因为书中的检索方式我实践 ... 不是那么回事 ... 许是我理解的有问题 , 正如我所提 : 这里提出第一个疑问 , 也是唯一一个疑问 : 请问大湿到底是进入 00 63 ( 伪记录 ) 还是 c0a4 ( 主键 2448 ) ? 因为我这里还不确定所以假定进入的是 2448 ( 这也是非常合理的解释毕竟 , 00 63 是伪记录 ) 按照书中的理解应该是 00 63 , 比较贴切点 . 顺着 00 63 往后查嘛 . 可确实不是 小生对这三个问题找不到答案 , 只能求救 CSDN 了 . 如果姜老湿能看到这贴 , 为小生我一一解答我自然再欢喜不过了 . 奶奶的 , 可惜 1 天了就版主老湿一个回答 . 擦

[rucypli]

呵呵 前言里面有他的邮箱啊 [e-mail]或者微博@insidemysql于他联系

[ACMAIN_CHM]

太长了。 估计能耐心看的人不多。 包括我自己都只扫了前两句

[a441209821]

引用 8 楼 rucypli 的回复:

呵呵 前言里面有他的邮箱啊 [e-mail]或者微博@insidemysql于他联系

额 , 谢谢 , 邮箱 ... 我不知道要等多长时间 .... 我不玩微博 ... 总之感谢亲了 , 我把这个地址发到他邮箱 . 希望他来看看 , 给我解个惑 . 不过我真心没底要等多长时间 .

[a441209821]

引用 8 楼 rucypli 的回复:

呵呵 前言里面有他的邮箱啊 [e-mail]或者微博@insidemysql于他联系

KO 了 , 我已经把这个地址发给姜大湿了 , 不过不知道大湿啥时候能看到 ....

[a441209821]

引用 9 楼 ACMAIN_CHM 的回复:

太长了。 估计能耐心看的人不多。 包括我自己都只扫了前两句

版主大淫 , 您的耐心也太 ... 前两句是废话 ... 分割线上面都是扫一眼的 ...

[qlks]

多谢你对MySQL技术内幕的支持 我做了些回复，有些问题看不太懂 在此小生特意请教第一个问题 : 这个 page level 最高的页是否 B-Tree 的根节点 ? 是root页 ------------------------ 因为假如只有 10 条数据 , 总大小不超过 16 KB 的话那么它肯定只有一个 B-Tree Node ( 假设 innodb_page_size 为 16KB ) , 这时的 page level 为 0000 . 那么它的索引呢 ? 还是说直接去这个叶子页就可以了？ root页就是叶子页 ------------------------ 这里提出第二个疑问 : innodb 的叶子节点的单个节点最多不是可以存放 200 行记录吗 ? 最少 2 行吗 ( 低于两条成单链表了 , 这个我能理解 ) ? 但是最多不是 200 行吗 ? 这里为什么是 287 行 ? 版本 ? 还是我的这个认知错误 , 其实可以容纳更多 ? 每个页最多7992行 ------------------------- 这里提出第三个疑问 : 索引页的 File Header 中的 FIL_PAGE_TYPE , 不应该是 00 03 吗 ? 为什么这里赫然写着 45 bf ( 数据页 ) ? root页就是索引页，因此也是45bf -------------------------

[liuxingzhongxia]

InnoDB现在默认为compact行格式。在compact行格式下，每行记录（除了infimum和supremum）都包含5个字节的record header，6个字节的trx id，7个字节的回滚指针，这些加起来就5+6+7=18个字节了，试问一个16K的页如何能放得下7992条记录呢？？？？？？？？？？？