前言
很久之前了解到innodb_space，觉得很强大，可视化了InnoDB页面，但是没有形成文档，总结出来，有一些经常被问到的东东可以用这个查看了。
环境
centos 7
Linux 3.10.0-693.2.2.el7.x86_64 #1 SMP Tue Sep 12 22:26:13 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
Server version: 5.7.20 MySQL Community Server (GPL)安装
sudo yum install rubygems ruby-devel
sudo gem install innodb_ruby解读
总述
选项
- -f 加载表空间，如ibd文件
- -s 加载系统表空间，如ibd
- -T 指定表名
- -I 指定索引名
继续拿这个表做例子:
mysql> show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gmt_create` datetime NOT NULL,
`gmt_modified` datetime NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` varchar(500) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`is_used` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `k_1` (`k`),
KEY `idx_is_used` (`is_used`),
KEY `idx_gmt_create` (`gmt_create`)
) ENGINE=InnoDB AUTO_INCREMENT=69313841 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)页结构
page-account
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 1 page-account
Accounting for page 1:
Page type is IBUF_BITMAP (Insert buffer bitmap, bookkeeping for insert buffer writes to be merged).
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 full_frag list of space.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 2 page-account
Accounting for page 2:
Page type is INODE (File segment inode, bookkeeping for file segments).
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 full_frag list of space.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -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 full_frag list of space.
Page is in fragment array of fseg 1.
Fseg is in internal fseg of index 74.
Index root is page 3.
Index is test/sbtest1.PRIMARY.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 4 page-account
Accounting for page 4:
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 full_frag list of space.
Page is in fragment array of fseg 3.
Fseg is in internal fseg of index 75.
Index root is page 4.
Index is test/sbtest1.k_1.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 5 page-account
Accounting for page 5:
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 full_frag list of space.
Page is in fragment array of fseg 5.
Fseg is in internal fseg of index 76.
Index root is page 5.
Index is test/sbtest1.idx_is_used.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 6 page-account
Accounting for page 6:
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 full_frag list of space.
Page is in fragment array of fseg 7.
Fseg is in internal fseg of index 77.
Index root is page 6.
Index is test/sbtest1.idx_gmt_create.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 page-account
Accounting for page 7:
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 full_frag list of space.
Page is in fragment array of fseg 2.
Fseg is in leaf fseg of index 74.
Index root is page 3.
Index is test/sbtest1.PRIMARY.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 8 page-account
Accounting for page 8:
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 full_frag list of space.
Page is in fragment array of fseg 2.
Fseg is in leaf fseg of index 74.
Index root is page 3.
Index is test/sbtest1.PRIMARY.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 9 page-account
Accounting for page 9:
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 full_frag list of space.
Page is in fragment array of fseg 1.
Fseg is in internal fseg of index 74.
Index root is page 3.
Index is test/sbtest1.PRIMARY.page-dump
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 1 page-dump | more
#<Innodb::Page::IbufBitmap:0x00000001a58678>:
fil header:
{:checksum=>2988774564,
:offset=>1,
:prev=>0,
:next=>0,
:lsn=>114063786483,
:type=>:IBUF_BITMAP,
:flush_lsn=>0,
:space_id=>45}
fil trailer:
{:checksum=>2988774564, :lsn_low32=>2394636787}
ibuf bitmap:
Page 0: {:free=>0, :buffered=>false, :ibuf=>false}
Page 1: {:free=>0, :buffered=>false, :ibuf=>false}
Page 2: {:free=>0, :buffered=>false, :ibuf=>false}
Page 3: {:free=>0, :buffered=>false, :ibuf=>false}
Page 4: {:free=>0, :buffered=>false, :ibuf=>false}
...[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 2 page-dump | more
#<Innodb::Page::Inode:0x00000001e4c450>:
fil header:
{:checksum=>2394379754,
:offset=>2,
:prev=>0,
:next=>0,
:lsn=>114066783250,
:type=>:INODE,
:flush_lsn=>0,
:space_id=>45}
fil trailer:
{:checksum=>2394379754, :lsn_low32=>2397633554}
list entry:
{:prev=>nil, :next=>nil}
inodes:
{:offset=>50,
:fseg_id=>1,
:not_full_n_used=>19,
:free=>
#<Innodb::List::Xdes:0x00000001e29108
@base={:length=>0, :first=>nil, :last=>nil},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:not_full=>
#<Innodb::List::Xdes:0x00000001e23be0
@base=
{:length=>2,
:first=>{:page=>360448, :offset=>6238},
:last=>{:page=>425984, :offset=>7478}},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:full=>
#<Innodb::List::Xdes:0x00000001e22290
@base=
{:length=>5,
:first=>{:page=>32768, :offset=>2318},
:last=>{:page=>294912, :offset=>4998}},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:magic_n=>97937874,
:frag_array=>
[3,
9,
40,
nil,
42,
43,
44,
...
61,
62,
63,
16386,
16387,
16388,
16389,
16390,
16391]}
{:offset=>242,
:fseg_id=>2,
:not_full_n_used=>880,
:free=>
#<Innodb::List::Xdes:0x00000001dd9fe0
@base={:length=>0, :first=>nil, :last=>nil},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:not_full=>
#<Innodb::List::Xdes:0x00000001dd8690
@base=
{:length=>878,
:first=>{:page=>376832, :offset=>7118},
:last=>{:page=>442368, :offset=>1438}},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:full=>
#<Innodb::List::Xdes:0x00000001dd30c8
@base=
{:length=>6032,
:first=>{:page=>0, :offset=>198},
:last=>{:page=>376832, :offset=>7078}},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:magic_n=>97937874,
:frag_array=>
[7,
8,
10,
11,
12,
13,
14,
...
35,
36,
37,
38,
39]}
{:offset=>434,
:fseg_id=>3,
:not_full_n_used=>0,
:free=>
#<Innodb::List::Xdes:0x00000001d8afd0
@base={:length=>0, :first=>nil, :last=>nil},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:not_full=>
#<Innodb::List::Xdes:0x00000001d896a8
@base={:length=>0, :first=>nil, :last=>nil},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:full=>
#<Innodb::List::Xdes:0x00000001d819f8
@base={:length=>0, :first=>nil, :last=>nil},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:magic_n=>97937874,
:frag_array=>
[4,
16393,
16424,
nil,
16426,
16427,
16428,
16440,
16441,
16442,
16443,
16444,
16445,
16446,
16447,
nil,
nil,
nil,
nil,
nil,
nil]}
{:offset=>626,
:fseg_id=>4,
:not_full_n_used=>82,
:free=>
#<Innodb::List::Xdes:0x00000001d43360
@base={:length=>0, :first=>nil, :last=>nil},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:not_full=>
#<Innodb::List::Xdes:0x00000001d419e8
@base=
{:length=>51,
:first=>{:page=>458752, :offset=>4878},
:last=>{:page=>458752, :offset=>6878}},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:full=>
#<Innodb::List::Xdes:0x00000001d40098
@base=
{:length=>340,
:first=>{:page=>442368, :offset=>1478},
:last=>{:page=>458752, :offset=>4838}},
@space=
<Innodb::Space file="test/sbtest1.ibd", page_size=16384, pages=529152>>,
:magic_n=>97937874,
:frag_array=>
[41,
16392,
16394,
.....page-records
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 3 page-records | more
Record 126: (id=43000569) → #9
Record 140: (id=43071969) → #40
Record 154: (id=43143369) → #42
Record 168: (id=43214769) → #43
Record 182: (id=43286169) → #44
Record 196: (id=43357569) → #45
Record 210: (id=43428969) → #46
Record 224: (id=43500369) → #47
Record 238: (id=43571769) → #48
Record 252: (id=43643169) → #49
Record 266: (id=43714569) → #50
Record 280: (id=43785969) → #51
Record 294: (id=43857369) → #52
Record 308: (id=43928769) → #53
Record 322: (id=44000169) → #54
Record 336: (id=44071569) → #55
Record 350: (id=44142969) → #56
Record 364: (id=44214369) → #57
Record 378: (id=44285769) → #58
Record 392: (id=44357169) → #59
Record 406: (id=44428569) → #60
Record 420: (id=44499969) → #61
Record 434: (id=44571369) → #62
Record 448: (id=44642769) → #63
Record 462: (id=44714169) → #16386
Record 476: (id=44785569) → #16387
Record 490: (id=44856969) → #16388
Record 504: (id=44928369) → #16389
Record 518: (id=44999769) → #16390
Record 532: (id=45071169) → #16391
Record 546: (id=45142569) → #36224
Record 560: (id=45213969) → #36225
Record 574: (id=45285369) → #36226
...page-directory-summary
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 5 page-directory-summary
slot offset type owned key
0 99 infimum 1
1 180 node_pointer 4 (is_used=0)
2 252 node_pointer 4 (is_used=0)
3 324 node_pointer 4 (is_used=0)
4 396 node_pointer 4 (is_used=0)
5 468 node_pointer 4 (is_used=0)
6 112 supremum 8
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 6 page-directory-summary
slot offset type owned key
0 99 infimum 1
1 179 node_pointer 4 (gmt_create="184616475-42-59 95:08:86")
2 251 node_pointer 4 (gmt_create="184616479-25-95 90:48:11")
3 323 node_pointer 4 (gmt_create="184616483-40-85 97:53:44")
4 395 node_pointer 4 (gmt_create="184616486-58-28 48:33:81")
5 467 node_pointer 4 (gmt_create="184616490-05-40 55:86:50")
6 112 supremum 8
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 page-directory-summary
slot offset type owned key
0 99 infimum 1
1 790 conventional 4 (id=43000572)
2 1674 conventional 4 (id=43000576)
3 2558 conventional 4 (id=43000580)
4 3442 conventional 4 (id=43000584)
5 4326 conventional 4 (id=43000588)
6 5210 conventional 4 (id=43000592)
7 6094 conventional 4 (id=43000596)
8 6978 conventional 4 (id=43000600)
9 7862 conventional 4 (id=43000604)
10 8746 conventional 4 (id=43000608)
11 9630 conventional 4 (id=43000612)
12 10514 conventional 4 (id=43000616)
13 11398 conventional 4 (id=43000620)
14 12282 conventional 4 (id=43000624)
15 13166 conventional 4 (id=43000628)
16 14050 conventional 4 (id=43000632)
17 112 supremum 5page-illustrate
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 6 page-illustrate
...
索引结构
index-recurse
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-recurse | more
ROOT NODE #3: 369 records, 5166 bytes
NODE POINTER RECORD ≥ (id=43000569) → #9
INTERNAL NODE #9: 1050 records, 14700 bytes
NODE POINTER RECORD ≥ (id=43000569) → #7
LEAF NODE #7: 68 records, 15028 bytes
RECORD: (id=43000569) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-11-75 00:49:77", k=-1271582413, c="73586-15688153734-79729593694-96509299839-83724898275-8671183
3539-78981337422-35049690573-51724173961-87474696253989966", pad="21624-36689827414-04092488557-09587706818-65008859162 \x80\x00\x00\x00w\x00", is_used=-2147477504)
RECORD: (id=43000570) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360231-94-84 49:64:33", k=-1271451079, c="70079-70972780322-70018558993-71769650003-09270326047-3241701
2031-10768856803-14235120402-93989080412-18690312264047768", pad="26683-45880822084-77922711547-29057964468-76514263618 \x80\x00\x00\x00w\x00", is_used=-2147475456)
RECORD: (id=43000571) → (gmt_create="184616470-38-96 24:80:37", gmt_modified="-891360229-49-27 20:33:85", k=-1288227272, c="27441-24903985029-56844662308-79012577859-40518387141-6058841
9212-24399130405-42612257832-29494881732-71506024440268430", pad="35807-96849339132-53943793991-69741192222-48634174017 \x80\x00\x00\x00w\x00", is_used=-2080364544)
...index-record-offsets
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY index-record-offsets | more
page_offset record_offset
7 127
7 348
7 569
7 790
7 1011
7 1232
7 1453
7 1674
7 1895
7 2116
...index-level-summary
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -I PRIMARY -l 0 index-level-summary | more
page index level data free records min_key
7 74 0 15028 1192 68 id=43000569
8 74 0 15028 1192 68 id=43000637
10 74 0 15028 1192 68 id=43000705
11 74 0 15028 1192 68 id=43000773
12 74 0 15028 1192 68 id=43000841
13 74 0 15028 1192 68 id=43000909
14 74 0 15028 1192 68 id=43000977
15 74 0 15028 1192 68 id=43001045
16 74 0 15028 1192 68 id=43001113
17 74 0 15028 1192 68 id=43001181
18 74 0 15028 1192 68 id=43001249
19 74 0 15028 1192 68 id=43001317
...记录结构
record-dump
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-dump
Record at offset 128
Header:
Next record offset : -8702
Heap number : 512
Type : conventional
Deleted : false
Length : 7
System fields:
Transaction ID: 38377940
Roll Pointer:
Undo Log: page 112897, offset 4249
Rollback Segment ID: 0
Insert: false
Key fields:
id: 270727424
Non-key fields:
gmt_create: "222619031-78-45 93:12:68"
gmt_modified: "-370929284-39-30 89:87:40"
k: -1255066825
c: ""
pad: "3586-15688153734-79729593694-96509299839-83724898275-8671183"
is_used: -1288359111记录历史
record-history
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 -R 128 record-history
Transaction Type Undo record
[root@localhost mysql]# 其他拓展
space-lists
[root@localhost mysql]# innodb_space -s ibdata1 space-lists
name length f_page f_offset l_page l_offset
free 619 32768 6918 0 438
free_frag 5 0 398 0 318
full_frag 2 0 158 0 278
full_inodes 1 2 38 2 38
free_inodes 2 322 38 243 38
[root@localhost mysql]#space-list-iterate
[root@localhost mysql]# innodb_space -s ibdata1 space-list-iterate -L free_frag
start_page page_used_bitmap
384 #############........###################################........
16384 ##..............................................................
32768 ##..............................................................
320 ####################################################.######.##..
256 #############################.##.#..##...###..#.######..####.##.
[root@localhost mysql]#space-inodes-summary
[root@localhost mysql]# innodb_space -s ibdata1 space-inodes-summary
INODE fseg_id=1, pages=3552, frag=32, full=47, not_full=8, free=0
INODE fseg_id=2, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=3, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=4, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=5, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=6, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=7, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=8, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=9, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=10, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=11, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=12, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=13, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=14, pages=0, frag=0, full=0, not_full=0, free=0
INODE fseg_id=15, pages=160, frag=32, full=2, not_full=0, free=0
INODE fseg_id=16, pages=1, frag=1, full=0, not_full=0, free=0
...undo-history-summary
[root@izhp3j8nb7nyq2g923r9fvz mysql]# innodb_space -s ibdata1 undo-history-summary
Page Offset Transaction Type Table
345 2452 162569 delete
345 2512 162600 delete
345 2598 162601 update_deleted
345 2675 162569 update_existing SYS_TABLESPACES
345 2716 162569 update_existing SYS_DATAFILES
345 2763 162600 update_existing SYS_TABLESPACES
345 2817 162600 update_existing SYS_DATAFILES
347 2859 162595 delete mysql/innodb_table_stats
284 2925 162581 delete mysql/innodb_index_stats
284 3020 162581 delete mysql/innodb_index_stats
284 3115 162581 delete mysql/innodb_index_stats
284 3194 162597 delete mysql/innodb_index_stats
284 3303 162597 delete mysql/innodb_index_stats
284 3412 162597 delete mysql/innodb_index_stats
284 3521 162597 delete mysql/innodb_index_stats
284 3614 162589 delete mysql/innodb_index_stats
284 3717 162589 delete mysql/innodb_index_stats
284 3820 162589 delete mysql/innodb_index_stats
284 3923 162589 delete mysql/innodb_index_stats
284 4010 162581 delete mysql/innodb_index_stats
284 4097 162581 delete mysql/innodb_index_stats
284 4184 162581 delete mysql/innodb_index_stats
284 4271 162581 delete mysql/innodb_index_stats
312 3626 162568 delete
312 3679 162568 delete
312 3740 162568 delete
...undo-record-dump
Print a detailed description of an undo record and the data it contains:
[root@localhost mysql]# innodb_space -s ibdata1 -p page -R offset undo-record-dump
/usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:1582:in `undo_record_dump': undefined method `root' for nil:NilClass (NoMethodError)
from /usr/local/share/gems/gems/innodb_ruby-0.9.15/bin/innodb_space:2055:in `<top (required)>'
from /usr/local/bin/innodb_space:23:in `load'
from /usr/local/bin/innodb_space:23:in `<main>'引用ruby库
$ irb -r innodb
> sys = Innodb::System.new("ibdata1")
> idx = sys.index_by_name("sakila/film", "PRIMARY")
> rec = idx.binary_search([1])
参考
https://github.com/jeremycole/innodb_ruby/wiki
开发原理
https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/
