解读InnoDB页面--系统文件结构

前言

很久之前了解到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

解读

总述

选项

1. -f 加载表空间，如ibd文件
2. -s 加载系统表空间，如ibd
3. -T 指定表名
4. -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     2

space-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 header

space-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/