innodb_ruby 用途：主要可查看innodb数据库数据表的各种存储，解析innodb的文件，用于学习数据库底层的一些存储。
在debian系统安装innodb_ruby
1、sudo apt install ruby-dev
2、sudo gem install innodb_ruby
3、sudo gem install --user-install innodb_ruby
4、以上已经安装完成，现在我们来使用innodb_ruby。
mysql -uroot -p
学习innodb_ruby基本使用：
从命令行使用innodb_space的基本知识。有两种方法来启动innodb_space。
针对单个文件(ibdata or .ibd):
Option | Parameters | Description |
-f | <filename> | Load the tablespace file (system or table) |
针对一个系统表空间，它将自动加载每表文件的表空间文件。
Option | Parameters | Description |
-s | <filename> | Load the system tablespace file (e.g. ibdata1) |
-T | <table name> | Use the given table name. |
-I | <index name> | Use the given index name |
系统文件结构
- 列出所有物理对象的数量。
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 system-spaces
name pages indexes
(system) 8832 204
test/innodb_ruby 2
...
复制代码
- 列出表空间中所有索引统计信息（系统空间或每个文件表空间）
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-indexes
id name root fseg used allocated fill_factor
94684 PRIMARY 3 internal 1 1 100.00%
94684 PRIMARY 3 leaf 3 3 100.00%
94685 idx 4 internal 1 1 100.00%
94685 idx 4 leaf 0 0 0.00%
复制代码
id：此索引的id
name：索引名称。PRIMARY代表的就是聚集索引，因为InnoDB表是聚集索引组织表，行记录就是聚集索引；idx就是辅助索引的名称。
root：索引中根节点的page号。
fseg：page类型。internal非叶子节点；leaf叶子节点
used：索引使用的page页。
allocated：索引分配的page页。
fill_factor：索引使用的百分比
- 统计每个类型的页共占用了多少页
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-page-type-regions
start end count type
0 0 1 FSP_HDR
1 1 1 IBUF_BITMAP
2 2 1 INODE
3 7 5 INDEX
8 8 1 FREE (ALLOCATED)
复制代码
- 打印每个类型总计页数
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-page-type-summary
type count percent description
INDEX 5 55.56 B+Tree index
FSP_HDR 1 11.11 File space header
IBUF_BITMAP 1 11.11 Insert buffer bitmap
INODE 1 11.11 File segment inode
ALLOCATED 1 11.11 Freshly allocated
复制代码
- 统计所有的页在表空间的饱和度信息，每个页面显示彩色块（按index/purpose着色），根据页面中的数据量调整大小。
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-extents-illustrate
复制代码
- 统计所有的页在表空间的饱和度信息，每个页面显示彩色块 (按页面修改LSN的年龄着色)。
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-extents-illustrate
复制代码
页文件结构
- 解释单个页面用途
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 3 page-account
Accounting for page 3:
Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as used in extent descriptor.
Extent is in free_frag list of space.
Page is in fragment array of fseg 1.
Fseg is in internal fseg of index 94684.
Index root is page 3.
Index is test/innodb_ruby.PRIMARY.
复制代码
- 打印页结构信息
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 3 page-dump
#<Innodb::Page::Index:0x0000000000ef65a0>:
fil header:
{:checksum=>3506321879,
:offset=>3,
:prev=>nil,
:next=>nil,
:lsn=>25811855781,
:type=>:INDEX,
:flush_lsn=>0,
:space_id=>2456}
fil trailer:
{:checksum=>1027768186, :lsn_low32=>42052005}
page header:
{:n_dir_slots=>2,
:heap_top=>162,
:garbage_offset=>0,
:garbage_size=>0,
:last_insert_offset=>154,
:direction=>:right,
:n_direction=>2,
:n_recs=>3,
:max_trx_id=>0,
:level=>1,
:index_id=>94684,
:n_heap=>5,
:format=>:compact}
fseg header:
{:leaf=>
<Innodb::Inode space=<Innodb::Space file="test/innodb_ruby.ibd", page_size=16384, pages=9>, fseg=2>,
:internal=>
<Innodb::Inode space=<Innodb::Space file="test/innodb_ruby.ibd", page_size=16384, pages=9>, fseg=1>}
sizes:
header 120
trailer 8
directory 4
free 16210
used 174
record 42
per record 14.00
page directory:
[99, 112]
system records:
{:offset=>99,
:header=>
{:next=>126,
:type=>:infimum,
:heap_number=>0,
:n_owned=>1,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>126,
:data=>"infimum\x00",
:length=>8}
{:offset=>112,
:header=>
{:next=>112,
:type=>:supremum,
:heap_number=>1,
:n_owned=>4,
:min_rec=>false,
:deleted=>false,
:length=>5},
:next=>112,
:data=>"supremum",
:length=>8}
garbage records:
records:
{:format=>:compact,
:offset=>126,
:header=>
{:next=>140,
:type=>:node_pointer,
:heap_number=>2,
:n_owned=>0,
:min_rec=>true,
:deleted=>false,
:nulls=>[],
:lengths=>{},
:externs=>[],
:length=>5},
:next=>140,
:type=>:clustered,
:key=>[{:name=>"c1", :type=>"INT", :value=>1}],
:row=>[],
:sys=>[],
:child_page_number=>5,
:length=>8}
{:format=>:compact,
:offset=>140,
:header=>
{:next=>154,
:type=>:node_pointer,
:heap_number=>3,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{},
:externs=>[],
:length=>5},
:next=>154,
:type=>:clustered,
:key=>[{:name=>"c1", :type=>"INT", :value=>2}],
:row=>[],
:sys=>[],
:child_page_number=>6,
:length=>8}
{:format=>:compact,
:offset=>154,
:header=>
{:next=>112,
:type=>:node_pointer,
:heap_number=>4,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{},
:externs=>[],
:length=>5},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"c1", :type=>"INT", :value=>4}],
:row=>[],
:sys=>[],
:child_page_number=>7,
:length=>8}
复制代码
- 汇总页面记录
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 3 page-records
Record 126: (c1=1) → #5
Record 140: (c1=2) → #6
Record 154: (c1=4) → #7
复制代码
每行代表一页数据，c1=1代表c1的最小值为1，#5代表是第5页。
- 查看页目录记录
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 7 page-directory-summary
slot offset type owned key
0 99 infimum 1
1 112 supremum 5
复制代码
数据太小了，再插点数据吧
mysql> insert into innodb_ruby values(5,500,'eeee'), (6,600,'ffff'), (7, 700, 'gggg'), (8, 800, 'hhhh'), (9, 900, 'iiii'), (10, 1000, 'jjjj'), (11, 1100, 'kkkk'), (12, 1200, 'llll'), (13, 1300, 'mmmm'), (14, 1400, 'nnnn'), (15, 1500, 'oooo'), (16, 1600, 'pppp');
复制代码
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 7 page-directory-summary
slot offset type owned key
0 99 infimum 1
1 7220 conventional 4 (c1=7)
2 7348 conventional 4 (c1=11)
3 112 supremum 6
复制代码
infimum最小值，supremum最大值，1~8 其他数据为4~8
- 详细说明一个页的内容
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 7 page-illustrate
复制代码
索引结构
- 遍历整个B+树扫描索引
主键索引
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY index-recurse
ROOT NODE #3: 3 records, 42 bytes
NODE POINTER RECORD ≥ (c1=1) → #5
LEAF NODE #5: 1 records, 7029 bytes
RECORD: (c1=1) → (c2=1, c3="aaa...")
NODE POINTER RECORD ≥ (c1=2) → #6
LEAF NODE #6: 2 records, 14058 bytes
RECORD: (c1=2) → (c2=2, c3="bbb...")
RECORD: (c1=3) → (c2=3, c3="ccc...")
NODE POINTER RECORD ≥ (c1=4) → #7
LEAF NODE #7: 13 records, 7413 bytes
RECORD: (c1=4) → (c2=4, c3="ddd...")
RECORD: (c1=5) → (c2=500, c3="eeee")
RECORD: (c1=6) → (c2=600, c3="ffff")
RECORD: (c1=7) → (c2=700, c3="gggg")
RECORD: (c1=8) → (c2=800, c3="hhhh")
RECORD: (c1=9) → (c2=900, c3="iiii")
RECORD: (c1=10) → (c2=1000, c3="jjjj")
RECORD: (c1=11) → (c2=1100, c3="kkkk")
RECORD: (c1=12) → (c2=1200, c3="llll")
RECORD: (c1=13) → (c2=1300, c3="mmmm")
RECORD: (c1=14) → (c2=1400, c3="nnnn")
RECORD: (c1=15) → (c2=1500, c3="oooo")
RECORD: (c1=16) → (c2=1600, c3="pppp")
复制代码
普通索引
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I idx index-recurse
ROOT NODE #4: 16 records, 224 bytes
RECORD: (c2=1) → (c1=1)
RECORD: (c2=2) → (c1=2)
RECORD: (c2=3) → (c1=3)
RECORD: (c2=4) → (c1=4)
RECORD: (c2=500) → (c1=5)
RECORD: (c2=600) → (c1=6)
RECORD: (c2=700) → (c1=7)
RECORD: (c2=800) → (c1=8)
RECORD: (c2=900) → (c1=9)
RECORD: (c2=1000) → (c1=10)
RECORD: (c2=1100) → (c1=11)
RECORD: (c2=1200) → (c1=12)
RECORD: (c2=1300) → (c1=13)
RECORD: (c2=1400) → (c1=14)
RECORD: (c2=1500) → (c1=15)
RECORD: (c2=1600) → (c1=16)
复制代码
可看出普通索引其实是直接指向了主键索引的
- 在索引页中打印每条记录的偏移量
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY index-record-offsets
page_offset record_offset
5 128
6 128
6 7157
7 128
7 7156
7 7188
7 7220
7 7252
7 7284
7 7316
7 7348
7 7380
7 7412
7 7444
7 7476
7 7508
复制代码
- 打印给定级别所有索引页面的摘要信息
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY -l 0 index-level-summary
page index level data free records min_key
5 94684 0 7029 9223 1 c1=1
6 94684 0 14058 2194 2 c1=2
7 94684 0 7413 8835 13 c1=4
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY -l 1 index-level-summary
page index level data free records min_key
3 94684 1 42 16210 3 c1=1
复制代码
记录结构
- 给定记录偏移量，打印记录的详细说明及包含的数据
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 5 -R 128 record-dump
Record at offset 128
Header:
Next record offset : 112
Heap number : 2
Type : conventional
Deleted : false
Length : 8
System fields:
Transaction ID: 2860579
Roll Pointer:
Undo Log: page 897, offset 272
Rollback Segment ID: 34
Insert: true
Key fields:
c1: 1
Non-key fields:
c2: 1
c3: "aaaa..."
复制代码
记录历史
- 显示给定偏移量的undo日志
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 5 -R 128 record-history
Transaction Type Undo record
(n/a) insert (c1=1) → ()
复制代码
- 显示所有变动过的undo记录
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 undo-history-summary
Page Offset Transaction Type Table
572 10435 2848487 delete
...
复制代码
额外探索
- 表空间统计信息
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 space-lists
name length f_page f_offset l_page l_offset
free 53 0 3318 0 3278
free_frag 19 0 398 0 1238
full_frag 3 0 158 0 2238
full_inodes 1 2 38 2 38
free_inodes 13 1789 38 595 38
参考文档：https://juejin.cn/post/6844903844107780103