前言
很久之前了解到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)索引结构
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/