前言
很久之前了解到innodb_space，觉得很强大，可视化了InnoDB页面，但是没有形成文档，总结出来，有一些经常被问到的东东可以用这个查看了。
环境
centos 7
Linux izhp3j8nb7nyq2g923r9fvz 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)系统文件结构
system-spaces
[root@localhost mysql]# innodb_space -s ibdata1 system-spaces
name pages indexes
(system) 45824 7
mysql/engine_cost 6 1
mysql/gtid_executed 6 1
mysql/help_category 7 2
mysql/help_keyword 15 2
mysql/help_relation 9 1
mysql/help_topic 576 2
mysql/innodb_index_stats 6 1
mysql/innodb_table_stats 6 1
mysql/plugin 6 1
mysql/server_cost 6 1
mysql/servers 6 1
mysql/slave_master_info 6 1
mysql/slave_relay_log_info 6 1
mysql/slave_worker_info 6 1
mysql/time_zone 6 1
mysql/time_zone_leap_second 6 1
mysql/time_zone_name 6 1
mysql/time_zone_transition 6 1
mysql/time_zone_transition_type 6 1
sys/sys_config 6 1
test/id2_d_license_device_key 6 1
test/sbtest1 529152 4
test/sbtest11 6 1
test/sbtest2 3135232 2space-indexes
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-indexes
id name root fseg used allocated fill_factor
74 PRIMARY 3 internal 370 479 77.24%
74 PRIMARY 3 leaf 386960 442272 87.49%
75 k_1 4 internal 25 25 100.00%
75 k_1 4 leaf 21874 25056 87.30%
76 idx_is_used 5 internal 28 28 100.00%
76 idx_is_used 5 leaf 23494 26912 87.30%
77 idx_gmt_create 6 internal 28 28 100.00%
77 idx_gmt_create 6 leaf 23494 26912 87.30%space-page-type-regions
遍历空间中的所有页面，并将页面类型的摘要合并到同类型页面的“区域”中。这里罗列了这棵树有多少个节点，包括已经分配但是未使用的节点。
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-page-type-regions
start end count type
0 0 1 FSP_HDR
1 1 1 IBUF_BITMAP
2 2 1 INODE
3 16383 16381 INDEX
16384 16384 1 XDES
16385 16385 1 IBUF_BITMAP
16386 16424 39 INDEX
16425 16425 1 FREE (INDEX)
16426 32767 16342 INDEX
32768 32768 1 XDES
32769 32769 1 IBUF_BITMAP
32770 49151 16382 INDEX
49152 49152 1 XDES
49153 49153 1 IBUF_BITMAP
49154 49184 31 INDEX
49185 49185 1 FREE (INDEX)
49186 49210 25 INDEX
49211 49215 5 FREE (ALLOCATED)
49216 65535 16320 INDEX
65536 65536 1 XDES
65537 65537 1 IBUF_BITMAP
65538 65599 62 FREE (ALLOCATED)
65600 81919 16320 INDEX
81920 81920 1 XDES
81921 81921 1 IBUF_BITMAP
81922 81983 62 FREE (ALLOCATED)
......
space-page-type-summary
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-page-type-summary
type count percent description
INDEX 456275 86.23 B+Tree index
ALLOCATED 72812 13.76 Freshly allocated
IBUF_BITMAP 32 0.01 Insert buffer bitmap
XDES 31 0.01 Extent descriptor
INODE 1 0.00 File segment inode
FSP_HDR 1 0.00 File space headerspace-index-pages-summary
[root@localhost mysql]# innodb_space -f test/sbtest1.ibd space-index-pages-summary | head -n 10
page index level data free records
3 74 2 5166 10904 369
4 75 2 408 15834 24
5 76 2 486 15756 27
6 77 2 486 15756 27
7 74 0 15028 1192 68
8 74 0 15028 1192 68
9 74 1 14700 1030 1050
10 74 0 15028 1192 68
11 74 0 15028 1192 68通过level列我们可以看到，这棵树高度是2。
space-extents-illustrate
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 space-extents-illustrate
....
space-lsn-age-illustrate
参考
https://github.com/jeremycole/innodb_ruby/wiki
开发原理
https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/
