深圳住建局官方网站,镇江网站排名公司,wordpress timeline,装修网页设计网站前言
最近看到了 何登成 大佬的 深入MySQL源码 -- Step By Step 的 pdf 呵呵 似乎是找到了一些 方向
之前对于 mysql 方面的东西, 更多的仅仅是简单的使用[业务中的各种增删改查], 以及一些面试题的背诵
这里会参照 MySQL Internals Manual 来大致的看一下 i…前言
最近看到了 何登成 大佬的 深入MySQL源码 -- Step By Step 的 pdf 呵呵 似乎是找到了一些 方向
之前对于 mysql 方面的东西, 更多的仅仅是简单的使用[业务中的各种增删改查], 以及一些面试题的背诵
这里会参照 MySQL Internals Manual 来大致的看一下 innodb 里面的 page 的存储相关, 这些是深入了解 mysql 的基础
本文内容对应的是 mysql innodb record 里面的 查看一下 rec 的数据信息, 除了 rec 之外还有那些信息呢 ? MySQL Internals Manual - 22.2 InnoDB Page Structure
以下截图参照自 MySQL Internals Manual
总的来说 Page 拆分成 FileHeader, PageHeader, Infimum Supremum, UserRecords, FreeSpace, PageDirectory, FileTrailer
FileHeader 里面主要是包含了当前文件的相关信息, 这里面的我们需要关注的就 FIL_PAGE_OFFSET, FIL_PAGE_PREV, FIL_PAGE_NEXT
PageHeader 里面主要包含的是当前 page 的元数据信息 PAGE_N_DIR_SLOTS : 当前 page pageDirectory 的数量 PAGE_HEAP_TOP : 当前 FreeSpace 的开始的偏移 PAGE_N_HEAP : 当前 page 记录的数量(UserRecords Infimum Supremum) PAGE_FREE : 当前 page 被删除的记录数量 PAGE_FREE : 当前 page 被删除的记录占用的字节数 PAGE_LAST_INSERT : 当前 page 最后一个新增的记录的偏移 PAGE_DIRECTION : 当前 page 最后一个新增的记录的偏移 PAGE_N_DIRECTION : 当前 page 最后一个新增的记录的偏移 PAGE_N_RECS : UserRecords 记录数量 PAGE_MAX_TRX_ID : 最大的 trxId PAGE_LEVEL : pageLevel PAGE_INDEX_ID : pageIndexId PAGE_BTR_SEG_LEAF : btrSegLeaf PAGE_BTR_SEG_TOP : btrSegTop
Infimum Supremum : Page 里面两条预制的记录 infimum 和 supremum, 分别表示 record 链表逻辑上的 head 和 tail
UserRecords : 各个用户的记录信息, 可以参见 mysql innodb record
FreeSpace : 当前 page 未使用的空间
PageDirectory : key order 排序的 稀疏 record指针 列表
FilTrailer : page 的 checksum FileHeader 的 FIL_PAGE_LSN 后四字节
22.2.2 Example 里面介绍了一个实例的案例, 剖析一个实际的记录在内存中的数据分布情况, 以及拆解每一个字节的逻辑意义, 可以移步文档看一下, 这里就不截图了, 请自行查阅文档 Page 实际案例剖析
我们这里需要着重注意的是 PageHeader 的这部分的数据信息 PAGE_N_DIR_SLOTS : 当前 page pageDirectory 的数量 PAGE_HEAP_TOP : 当前 FreeSpace 的开始的偏移 PAGE_N_HEAP : 当前 page 记录的数量(UserRecords Infimum Supremum) PAGE_FREE : 当前 page 被删除的记录数量 PAGE_FREE : 当前 page 被删除的记录占用的字节数 PAGE_LAST_INSERT : 当前 page 最后一个新增的记录的偏移 PAGE_DIRECTION : 当前 page 最后一个新增的记录的偏移 PAGE_N_DIRECTION : 当前 page 最后一个新增的记录的偏移 PAGE_N_RECS : UserRecords 记录数量 PAGE_MAX_TRX_ID : 最大的 trxId PAGE_LEVEL : pageLevel PAGE_INDEX_ID : pageIndexId PAGE_BTR_SEG_LEAF : btrSegLeaf PAGE_BTR_SEG_TOP : btrSegTop
# user 对应的数据 当前页的数据信息, 拆解
(lldb) x 0x1286cc000 -c 0x120
0x1286cc000: 10 aa fb 30 00 00 00 03 ff ff ff ff ff ff ff ff .0....
0x1286cc010: 00 00 00 00 00 1a a2 7d 45 bf 00 00 00 00 00 00 ......}E......
0x1286cc020: 00 00 00 00 00 06 00 02 00 b9 80 04 00 00 00 00 ...............
0x1286cc030: 00 a0 00 02 00 01 00 02 00 00 00 00 00 00 00 00 ...............
0x1286cc040: 00 00 00 00 00 00 00 00 00 16 00 00 00 06 00 00 ................
0x1286cc050: 00 02 00 f2 00 00 00 06 00 00 00 02 00 32 01 00 ............2..
0x1286cc060: 02 00 1c 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 ...infimum......
0x1286cc070: 73 75 70 72 65 6d 75 6d 05 00 00 00 10 00 21 80 supremum......!.
0x1286cc080: 00 00 01 00 00 00 00 2b 07 04 00 00 01 56 04 7b ............V.{
0x1286cc090: 80 00 00 1c 6a 65 72 72 79 04 00 00 00 18 ff d0 ....jerry.....
0x1286cc0a0: 80 00 00 02 00 00 00 00 35 04 83 00 00 01 36 01 ........5.....6.
0x1286cc0b0: 10 80 00 00 16 6c 75 63 79 00 00 00 00 00 00 00 .....lucy.......
0x1286cc0c0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1286cc0d0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1286cc0e0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1286cc0f0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1286cc100: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1286cc110: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................# page 的记录信息
0x1286cc000: 10 aa fb 30 00 00 00 03 ff ff ff ff ff ff ff ff .0....
0x1286cc010: 00 00 00 00 00 1a a2 7d 45 bf 00 00 00 00 00 00 ......}E......
0x1286cc020: 00 00 00 00 00 06 00 02 00 b9 80 04 00 00 00 00 ...............
0x1286cc030: 00 a0 00 02 00 01 00 02 00 00 00 00 00 00 00 00 ...............
0x1286cc040: 00 00 00 00 00 00 00 00 00 16 00 00 00 06 00 00 ................
0x1286cc050: 00 02 00 f2 00 00 00 06 00 00 00 02 00 32 01 00 ............2..# FIL HEADERR : 38 bytes
FIL_PAGE_SPACE 10 aa fb 30
FIL_PAGE_OFFSET 00 00 00 03
FIL_PAGE_PREV ff ff ff ff
FIL_PAGE_NEXT ff ff ff ff
FIL_PAGE_LSN 00 00 00 00 00 1a c4 cb
FIL_PAGE_TYPE 45 bf
FIL_PAGE_FILE_FLUSH_LSN 00 00 00 00 00 00 00 00
FIL_PAGE_ARCH_LOG_NO 00 00 00 06# PAGE_HEADER : 56 byte
PAGE_N_DIR_SLOTS 00 02
PAGE_HEAP_TOP 00 b9
PAGE_N_HEAP 80 04
PAGE_FREE 00 00
PAGE_GARBAGE 00 00
PAGE_LAST_INSERT 00 a0
PAGE_DIRECTION 00 02
PAGE_N_DIRECTION 00 01
PAGE_N_RECS 00 02
PAGE_MAX_TRX_ID 00 00 00 00 00 00 00 00
PAGE_LEVEL 00 00
PAGE_INDEX_ID 00 00 00 00 00 00 00 16
PAGE_BTR_SEG_LEAF 00 00 00 06 00 00 00 02 00 f2
PAGE_BTR_SEG_TOP 00 00 00 06 00 00 00 02 00 32# Page Directory Fil Trailer, 地址不一样是因为是不同的进程里面 inspect 的, 但是不影响数据
(lldb) x 0x12663bfe0 -c 0x20
0x12663bfe0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663bff0: 00 00 00 00 00 70 00 63 ac c3 1c c6 00 1a c4 cb .....p.c...PAGE_N_DIR_SLOTS [0x0070, 0x0063]
FIL_PAGE_END_LSN 63 ac c3 1c c6 00 1a c4 cb
CHECK_SUM ac c3 1c c6
FIL_PAGE_LSN 00 1a c4 cbUserRecords 的组织
从下面的 step2 的数据组织上来看, 我们这里的 record 列表物理上是需要 record 在 FreeSpace 里面申请 record 的空间, 但是逻辑 [以 next offset 来路由]来说 是根据 id 排序的
jerry - lucy - jerry5 - jerry7 - jerry8 - jerry9
# 这里是 原有数据 jerry, lucy
# 物理上不是按照 id 进行排序的阿, 但是逻辑上来说 是按照 key order 排序的[根据 next 单链表]
# 1. 先新增了 jerry7, jerry8, jerry9
INSERT INTO test.user(id, age, name) VALUES (7, 22, jerry7);
INSERT INTO test.user(id, age, name) VALUES (8, 22, jerry8);
INSERT INTO test.user(id, age, name) VALUES (9, 22, jerry9);
# 2. 先新增了 jerry5
INSERT INTO test.user(id, age, name) VALUES (5, 22, jerry5);# step1
(lldb) x 0x12663807f -c 0x120
0x12663807f: 80 00 00 01 00 00 00 00 2b 07 04 00 00 01 56 04 .............V.
0x12663808f: 7b 80 00 00 1c 6a 65 72 72 79 04 00 00 00 18 00 {....jerry......
0x12663809f: 20 80 00 00 02 00 00 00 00 35 04 83 00 00 01 36 ........5.....6
0x1266380af: 01 10 80 00 00 16 6c 75 63 79 06 00 00 00 20 00 ......lucy.... .
0x1266380bf: 22 80 00 00 07 00 00 00 00 3d 0b 84 00 00 01 37 .............7
0x1266380cf: 01 10 80 00 00 16 6a 65 72 72 79 37 06 00 00 00 ......jerry7....
0x1266380df: 28 00 22 80 00 00 08 00 00 00 00 3d 0c 85 00 00 (.............
0x1266380ef: 01 38 01 10 80 00 00 16 6a 65 72 72 79 38 06 00 .8......jerry8..
0x1266380ff: 00 00 30 ff 6c 80 00 00 09 00 00 00 00 3d 0d 86 ..0l..........
0x12663810f: 00 00 01 39 01 10 80 00 00 16 6a 65 72 72 79 39 ...9......jerry9
0x12663811f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663812f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663813f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663814f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663815f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663816f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663817f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663818f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
# step2
(lldb) x 0x12663807f -c 0x120
0x12663807f: 80 00 00 01 00 00 00 00 2b 07 04 00 00 01 56 04 .............V.
0x12663808f: 7b 80 00 00 1c 6a 65 72 72 79 04 00 00 00 18 00 {....jerry......
0x12663809f: 86 80 00 00 02 00 00 00 00 35 04 83 00 00 01 36 .........5.....6
0x1266380af: 01 10 80 00 00 16 6c 75 63 79 06 00 00 00 20 00 ......lucy.... .
0x1266380bf: 22 80 00 00 07 00 00 00 00 3d 0b 84 00 00 01 37 .............7
0x1266380cf: 01 10 80 00 00 16 6a 65 72 72 79 37 06 00 00 00 ......jerry7....
0x1266380df: 28 00 22 80 00 00 08 00 00 00 00 3d 0c 85 00 00 (.............
0x1266380ef: 01 38 01 10 80 00 00 16 6a 65 72 72 79 38 06 00 .8......jerry8..
0x1266380ff: 00 00 30 ff 6c 80 00 00 09 00 00 00 00 3d 0d 86 ..0l..........
0x12663810f: 00 00 01 39 01 10 80 00 00 16 6a 65 72 72 79 39 ...9......jerry9
0x12663811f: 06 00 00 00 38 ff 9a 80 00 00 05 00 00 00 00 3d ....8.........
0x12663812f: 14 8a 00 00 01 91 01 10 80 00 00 16 6a 65 72 72 ............jerr
0x12663813f: 79 35 00 00 00 00 00 00 00 00 00 00 00 00 00 00 y5..............
0x12663814f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663815f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663816f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663817f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x12663818f: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................参考
MySQL Internals Manual
mysql innodb record 文章转载自： http://www.morning.nqbkb.cn.gov.cn.nqbkb.cn http://www.morning.fsrtm.cn.gov.cn.fsrtm.cn http://www.morning.dhdzz.cn.gov.cn.dhdzz.cn http://www.morning.kdtdh.cn.gov.cn.kdtdh.cn http://www.morning.haibuli.com.gov.cn.haibuli.com http://www.morning.bloao.com.gov.cn.bloao.com http://www.morning.kfstq.cn.gov.cn.kfstq.cn http://www.morning.fnpyk.cn.gov.cn.fnpyk.cn http://www.morning.pzcjq.cn.gov.cn.pzcjq.cn http://www.morning.pnntx.cn.gov.cn.pnntx.cn http://www.morning.prgdy.cn.gov.cn.prgdy.cn http://www.morning.hdzty.cn.gov.cn.hdzty.cn http://www.morning.qyqdz.cn.gov.cn.qyqdz.cn http://www.morning.pwwjs.cn.gov.cn.pwwjs.cn http://www.morning.24vy.com.gov.cn.24vy.com http://www.morning.rysmn.cn.gov.cn.rysmn.cn http://www.morning.nhrkl.cn.gov.cn.nhrkl.cn http://www.morning.ndltr.cn.gov.cn.ndltr.cn http://www.morning.lgznc.cn.gov.cn.lgznc.cn http://www.morning.qgjgsds.com.cn.gov.cn.qgjgsds.com.cn http://www.morning.bfmrq.cn.gov.cn.bfmrq.cn http://www.morning.fnywn.cn.gov.cn.fnywn.cn http://www.morning.nkqxb.cn.gov.cn.nkqxb.cn http://www.morning.lekbiao.com.gov.cn.lekbiao.com http://www.morning.hnhkz.cn.gov.cn.hnhkz.cn http://www.morning.dpwcl.cn.gov.cn.dpwcl.cn http://www.morning.nmfxs.cn.gov.cn.nmfxs.cn http://www.morning.lyjwb.cn.gov.cn.lyjwb.cn http://www.morning.cbnlg.cn.gov.cn.cbnlg.cn http://www.morning.ltkms.cn.gov.cn.ltkms.cn http://www.morning.mqnbm.cn.gov.cn.mqnbm.cn http://www.morning.lthtp.cn.gov.cn.lthtp.cn http://www.morning.xsszn.cn.gov.cn.xsszn.cn http://www.morning.rkkpr.cn.gov.cn.rkkpr.cn http://www.morning.grwgw.cn.gov.cn.grwgw.cn http://www.morning.mbfkt.cn.gov.cn.mbfkt.cn http://www.morning.dzdtj.cn.gov.cn.dzdtj.cn http://www.morning.xpzgg.cn.gov.cn.xpzgg.cn http://www.morning.gyfhk.cn.gov.cn.gyfhk.cn http://www.morning.qglqb.cn.gov.cn.qglqb.cn http://www.morning.fwkpp.cn.gov.cn.fwkpp.cn http://www.morning.qmqgx.cn.gov.cn.qmqgx.cn http://www.morning.wcqxj.cn.gov.cn.wcqxj.cn http://www.morning.rrhfy.cn.gov.cn.rrhfy.cn http://www.morning.ldgqh.cn.gov.cn.ldgqh.cn http://www.morning.sjftk.cn.gov.cn.sjftk.cn http://www.morning.knlbg.cn.gov.cn.knlbg.cn http://www.morning.dzdtj.cn.gov.cn.dzdtj.cn http://www.morning.kfjnx.cn.gov.cn.kfjnx.cn http://www.morning.yxbdl.cn.gov.cn.yxbdl.cn http://www.morning.tfgkq.cn.gov.cn.tfgkq.cn http://www.morning.rkrl.cn.gov.cn.rkrl.cn http://www.morning.pdxqk.cn.gov.cn.pdxqk.cn http://www.morning.lgnbr.cn.gov.cn.lgnbr.cn http://www.morning.lthtp.cn.gov.cn.lthtp.cn http://www.morning.phjny.cn.gov.cn.phjny.cn http://www.morning.ysybx.cn.gov.cn.ysybx.cn http://www.morning.ryywf.cn.gov.cn.ryywf.cn http://www.morning.alive-8.com.gov.cn.alive-8.com http://www.morning.lbcbq.cn.gov.cn.lbcbq.cn http://www.morning.qdlr.cn.gov.cn.qdlr.cn http://www.morning.qxjck.cn.gov.cn.qxjck.cn http://www.morning.kmlmf.cn.gov.cn.kmlmf.cn http://www.morning.kpzrf.cn.gov.cn.kpzrf.cn http://www.morning.yfffg.cn.gov.cn.yfffg.cn http://www.morning.jpbky.cn.gov.cn.jpbky.cn http://www.morning.nfbnl.cn.gov.cn.nfbnl.cn http://www.morning.tkgxg.cn.gov.cn.tkgxg.cn http://www.morning.pwgzh.cn.gov.cn.pwgzh.cn http://www.morning.ttrdr.cn.gov.cn.ttrdr.cn http://www.morning.gblrn.cn.gov.cn.gblrn.cn http://www.morning.nnqrb.cn.gov.cn.nnqrb.cn http://www.morning.mlcnh.cn.gov.cn.mlcnh.cn http://www.morning.qypjk.cn.gov.cn.qypjk.cn http://www.morning.zrgx.cn.gov.cn.zrgx.cn http://www.morning.zdxinxi.com.gov.cn.zdxinxi.com http://www.morning.wbxtx.cn.gov.cn.wbxtx.cn http://www.morning.smmrm.cn.gov.cn.smmrm.cn http://www.morning.kntbk.cn.gov.cn.kntbk.cn http://www.morning.rcrfz.cn.gov.cn.rcrfz.cn
