【Mysql】innodb_space 的使用介绍
innodb_space 的git网址：https://github.com/jeremycole...
1、安装
# yum -y install ruby
# gem install innodb_ruby安装完成后，执行如下命令验证innodb_ruby是否安装成功：# innodb_space --help
2、mysql环境
innodb_file_per_table=ON，innodb_file_format=Barracuda, innodb_file_format_max=Barracuda
3.基本用法
Against a single space file (ibdata or .ibd):
Option Parameters Description
-f <filename> Load the tablespace file (system or table)
Against a system tablespace which will auto-load file-per-table tablespace files:
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 name4.常用的用法
虽然每个innodb表都有frm文件，不过innodb在系统表空间中ibdata1也维护着innodb表的元数据，所以直接分析ibdata1文件即可了解某个表的相关存储信息 表结构：
CREATE TABLE `aa` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地区ID',
`name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称',
`rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT '关系ID',
`pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父ID',
`level` int(11) NOT NULL DEFAULT '0' COMMENT '类别，1、省份 2、市 3、区 4、县',
PRIMARY KEY (`id`),
UNIQUE KEY `UNQ_RID` (`rel_id`) USING BTREE,
KEY `IDX_PID` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='地区表'
1 row in set (0.00 sec)表信息：
4.1 space 相关
4.1.1 system-spaces
innodb_space -s ibdata1 system-spaces //列出系统中可用的所有表空间，包括一些基本的统计信息。这基本上是一个表的列表: innodb_space -s ibdata1 system-spaces
[root@vm-test01 ztj]# innodb_space -s ../ibdata1 system-spaces
name pages indexes
(system) 4864 6
monitor/monitor 6 1
monitor/db_monitor 6 1
monitor/monitor 6 1
mysql/innodb_index_stats 6 1
mysql/innodb_table_stats 6 1
mysql/slave_master_info 6 1
mysql/slave_relay_log_info 6 1
mysql/slave_worker_info 6 1
opsdb/myapp_db_instance 8 1
test/aaa 6 1
test/area 8 3
ztj/aa 9 3
ztj/aaa 8 3
ztj/dim_library 4608 4 4.1.2 space-page-type-regions
//查看数据分布 innodb_space -s ibdata1 -T ztj/aa space-page-type-regions
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztj/aa space-page-type-regions
start end count type
0 0 1 FSP_HDR
1 1 1 IBUF_BITMAP
2 2 1 INODE
3 5 3 INDEX //索引分布在page：3/4/5，其中3保存Primary信息，4保存UNQ_RID信息，5保存IDX_PID信息
6 7 2 FREE (ALLOCATED)对于表的索引的名称，也可以通过information_schema库表innodb_sys_indexes、innodb_sys_tables查看：
mysql> SELECT
-> b.name, a.name, index_id, type, a.space, a.PAGE_NO
-> FROM
-> information_schema.INNODB_SYS_INDEXES a,
-> information_schema.INNODB_SYS_TABLES b
-> WHERE
-> a.table_id = b.table_id AND a.space <> 0;
+------------------------------+-------------------------------------------+----------+------+-------+---------+
| name | name | index_id | type | space | PAGE_NO |
+------------------------------+-------------------------------------------+----------+------+-------+---------+
| monitor/ll_monitor | GEN_CLUST_INDEX | 21 | 1 | 5 | 3 |
| monitor/db_monitor | GEN_CLUST_INDEX | 22 | 1 | 6 | 3 |
| monitor/monitor | GEN_CLUST_INDEX | 20 | 1 | 4 | 3 |
| mysql/innodb_index_stats | PRIMARY | 23 | 3 | 7 | 3 |
| mysql/innodb_table_stats | PRIMARY | 24 | 3 | 8 | 3 |
| mysql/slave_master_info | PRIMARY | 25 | 3 | 9 | 3 |
| mysql/slave_relay_log_info | PRIMARY | 26 | 3 | 10 | 3 |
| mysql/slave_worker_info | PRIMARY | 47 | 3 | 18 | 3 |
| ztj/aa | UNQ_RID | 51 | 2 | 19 | 4 |
| ztj/aa | IDX_PID | 62 | 0 | 19 | 6 |
| ztj/aaa | PRIMARY | 63 | 3 | 25 | 3 |
| ztj/aaa | UNQ_RID | 64 | 2 | 25 | 4 |
| ztj/aaa | IDX_PID | 65 | 0 | 25 | 5 |
+------------------------------+-------------------------------------------+----------+------+-------+---------+4.1.3 space-indexes
//索引结构、数据分配情况 innodb_space -s ibdata1 -T ztj/aa space-indexes
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztj/aa space-indexes
id name root fseg used allocated fill_factor
50 PRIMARY 3 internal 1 1 100.00%
50 PRIMARY 3 leaf 0 0 0.00%
51 UNQ_RID 4 internal 1 1 100.00%
51 UNQ_RID 4 leaf 0 0 0.00%
52 IDX_PID 5 internal 1 1 100.00%
52 IDX_PID 5 leaf 0 0 0.00% 4.1.4 space-page-type-summary
//查看表pages的占比 innodb_space -s ../ibdata1 -T ztj/aa space-page-type-summary
[root@vm-test01 ztj]# innodb_space -s ../ibdata1 -T ztj/aa space-page-type-summary
type count percent description
INDEX 3 37.50 B+Tree index
ALLOCATED 2 25.00 Freshly allocated
INODE 1 12.50 File segment inode
IBUF_BITMAP 1 12.50 Insert buffer bitmap
FSP_HDR 1 12.50 File space header4.1.5 space-index-pages-summary
//查看表的索引分布在pages的情况 innodb_space -s ibdata1 -T ztj/aaa space-index-pages-summar |head -n 10 innodb_space -f ztj/aaa.ibd space-index-pages-summary |head -n 10
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztj/aaa space-index-pages-summary |head -n 10
page index level data free records
3 63 0 948 15298 16 //主键B+树的高度为page level+1=1
4 64 0 380 15866 16
5 65 0 208 16040 16
6 0 0 0 16384 0
7 0 0 0 16384 0
[root@vm-test01 ztj]# innodb_space -f dim_library.ibd space-index-pages-summary |head -n 10
page index level data free records
3 28 2 45 16207 3 //主键B+树索引的高度为page level+1=3
4 29 1 8470 7598 385
5 30 1 11487 4575 547
6 31 1 7980 8122 380
7 28 0 7526 8690 72
8 28 0 15137 1045 144
9 28 0 15074 1108 144
10 28 0 15110 1072 144
11 28 0 15104 1080 143 4.2 page 相关
4.2.1 page-account
//查看该page的说明信息
[root@vm-test01 ztj]# innodb_space -s ../ibdata1 -T ztj/aa -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 63.
Index root is page 3.
Index is ztj /aa.PRIMARY.
[root@vm-test01 ztj]#
[root@vm-test01 ztj]# innodb_space -s ../ibdata1 -T ztj/aa -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 free_frag list of space.
Page is in fragment array of fseg 3.
Fseg is in internal fseg of index 64.
Index root is page 4.
Index is ztj/aa.UNQ_RID.4.2.2 page-records
//查看辅助索引存储信息 innodb_space -s ibdata1 -T ztj/aa -p 4 page-records
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztj/aa -p 4 page-records
Record 126: (rel_id="10000") → (id=10000)
Record 141: (rel_id="1000010001") → (id=10001)
Record 161: (rel_id="100001000110002") → (id=10002)
Record 186: (rel_id="100001000110003") → (id=10003)
Record 211: (rel_id="100001000110004") → (id=10004)
Record 236: (rel_id="100001000110005") → (id=10005)
Record 261: (rel_id="100001000110006") → (id=10006)
Record 286: (rel_id="100001000110007") → (id=10007)
Record 311: (rel_id="100001000110008") → (id=10008)
Record 336: (rel_id="1000010009") → (id=10009)
Record 356: (rel_id="100001000910010") → (id=10010)
Record 381: (rel_id="100001000910011") → (id=10011)
Record 406: (rel_id="100001000910012") → (id=10012)
Record 431: (rel_id="100001000910013") → (id=10013)
Record 456: (rel_id="100001000910014") → (id=10014)
Record 481: (rel_id="100001000910015") → (id=10015)
innodb_space -s ibdata1 -T ztj/aa -p 5 page-records
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztj/aa -p 5 page-records
Record 125: (pid=0) → (id=10000)
Record 138: (pid=10000) → (id=10001)
Record 151: (pid=10000) → (id=10009)
Record 164: (pid=10001) → (id=10002)
Record 177: (pid=10001) → (id=10003)
Record 190: (pid=10001) → (id=10004)
Record 203: (pid=10001) → (id=10005)
Record 216: (pid=10001) → (id=10006)
Record 229: (pid=10001) → (id=10007)
Record 242: (pid=10001) → (id=10008)
Record 255: (pid=10009) → (id=10010)
Record 268: (pid=10009) → (id=10011)
Record 281: (pid=10009) → (id=10012)
Record 294: (pid=10009) → (id=10013)
Record 307: (pid=10009) → (id=10014)
Record 320: (pid=10009) → (id=10015)4.2.3 page-records
//主键索引的存储信息 innodb_space -s ibdata1 -T ztj/aa -p 3 page-records
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztj/aa -p 3 page-records
Record 127: (id=10000) → (name="\xE5\x8C\x97\xE4\xBA\xAC", rel_id="10000", pid=0, level=1)
Record 170: (id=10001) → (name="\xE6\x9C\x9D\xE9\x98\xB3\xE5\x8C\xBA", rel_id="1000010001", pid=10000, level=2)
Record 221: (id=10002) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000110002", pid=10001, level=3)
Record 280: (id=10003) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110003", pid=10001, level=3)
Record 348: (id=10004) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110004", pid=10001, level=3)
Record 416: (id=10005) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110005", pid=10001, level=3)
Record 484: (id=10006) → (name="\xE7\xAE\xA1\xE5\xBA\x84", rel_id="100001000110006", pid=10001, level=3)
Record 537: (id=10007) → (name="\xE5\x8C\x97\xE8\x8B\x91", rel_id="100001000110007", pid=10001, level=3)
Record 590: (id=10008) → (name="\xE5\xAE\x9A\xE7\xA6\x8F\xE5\xBA\x84", rel_id="100001000110008", pid=10001, level=3)
Record 646: (id=10009) → (name="\xE6\xB5\xB7\xE6\xB7\x80\xE5\x8C\xBA", rel_id="1000010009", pid=10000, level=2)
Record 697: (id=10010) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000910010", pid=10009, level=3)
Record 756: (id=10011) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910011", pid=10009, level=3)
Record 824: (id=10012) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910012", pid=10009, level=3)
Record 892: (id=10013) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910013", pid=10009, level=3)
Record 960: (id=10014) → (name="\xE5\x85\xAD\xE7\x8E\xAF\xE4\xBB\xA5\xE5\xA4\x96", rel_id="100001000910014", pid=10009, level=3)
Record 1019: (id=10015) → (name="\xE8\xA5\xBF\xE4\xB8\x89\xE6\x97\x97", rel_id="100001000910015", pid=10009, level=3)结论：辅助索引存储了主键值。主键page存储了所有数据
4.3 index相关
4.3.1 index-recurse
//递归整个B+树，扫描所有页面 innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-recurse
[root@vm-test01 ztj]# innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-recurse
ROOT NODE #3: 16 records, 948 bytes
RECORD: (id=10000) → (name="\xE5\x8C\x97\xE4\xBA\xAC", rel_id="10000", pid=0, level=1)
RECORD: (id=10001) → (name="\xE6\x9C\x9D\xE9\x98\xB3\xE5\x8C\xBA", rel_id="1000010001", pid=10000, level=2)
RECORD: (id=10002) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000110002", pid=10001, level=3)
RECORD: (id=10003) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110003", pid=10001, level=3)
RECORD: (id=10004) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110004", pid=10001, level=3)
RECORD: (id=10005) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000110005", pid=10001, level=3)
RECORD: (id=10006) → (name="\xE7\xAE\xA1\xE5\xBA\x84", rel_id="100001000110006", pid=10001, level=3)
RECORD: (id=10007) → (name="\xE5\x8C\x97\xE8\x8B\x91", rel_id="100001000110007", pid=10001, level=3)
RECORD: (id=10008) → (name="\xE5\xAE\x9A\xE7\xA6\x8F\xE5\xBA\x84", rel_id="100001000110008", pid=10001, level=3)
RECORD: (id=10009) → (name="\xE6\xB5\xB7\xE6\xB7\x80\xE5\x8C\xBA", rel_id="1000010009", pid=10000, level=2)
RECORD: (id=10010) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE4\xBB\xA5\xE5\x86\x85", rel_id="100001000910010", pid=10009, level=3)
RECORD: (id=10011) → (name="\xE4\xB8\x89\xE7\x8E\xAF\xE5\x88\xB0\xE5\x9B\x9B\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910011", pid=10009, level=3)
RECORD: (id=10012) → (name="\xE5\x9B\x9B\xE7\x8E\xAF\xE5\x88\xB0\xE4\xBA\x94\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910012", pid=10009, level=3)
RECORD: (id=10013) → (name="\xE4\xBA\x94\xE7\x8E\xAF\xE5\x88\xB0\xE5\x85\xAD\xE7\x8E\xAF\xE4\xB9\x8B\xE9\x97\xB4", rel_id="100001000910013", pid=10009, level=3)
RECORD: (id=10014) → (name="\xE5\x85\xAD\xE7\x8E\xAF\xE4\xBB\xA5\xE5\xA4\x96", rel_id="100001000910014", pid=10009, level=3)
RECORD: (id=10015) → (name="\xE8\xA5\xBF\xE4\xB8\x89\xE6\x97\x97", rel_id="100001000910015", pid=10009, level=3)4.3.2 index-record-offsets
//像index-record一样递归整个索引树，不过只打印出index page的偏移量 innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-record-offsets
[root@vm-test01 ztj]# innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-record-offsets
page_offset record_offset
3 127
3 170
3 221
3 280
3 348
3 416
3 484
3 537
3 590
3 646
3 697
3 756
3 824
3 892
3 960
3 1019 4.4 record相关
4.4.1 record-history
//递归整个B+树，扫描所有页面 innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY index-recurse
[root@vm-test01 ztj]# innodb_space -s ../ibdata1 -T ztj/aaa -I PRIMARY -p 3 -R 127 record-history
Transaction Type Undo record
(n/a) insert (id=10000) → ()5、索引是物理有序的还是逻辑有序的？
page内的数据：逻辑上有序的，物理磁盘上⼀个page内是⽆序的，以指针的形式连接 数据，达到逻辑有序 【正常1, 2, 3, 4, 5. 存储是：1， 3 ，2， 5， 4。1链接2链接3链 接4链接5】⻚和⻚之间也是：逻辑上有序，物理上⽆序。 ⾃增ID，逻辑上也是有序 的。
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
对象存储
对象存储（Cloud Object Storage，COS）是由腾讯云推出的无目录层次结构、无数据格式限制，可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限，无需分区管理，适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。