内容简介：innodb_space 的git网址：安装完成后，执行如下命令验证innodb_ruby是否安装成功：# innodb_space --helpinnodb_file_per_table=ON，innodb_file_format=Barracuda, innodb_file_format_max=Barracuda
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 name
4.常用的用法
虽然每个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=10016 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 ztjy]# 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 ztjy/aa 9 3 ztjy/aaa 8 3 ztjy/dim_library_phoneno_fei 4608 4 ztjy/info_activity 7 2 ztjy/info_area 8 3 ztjy/info_banners 7 2 ztjy/ttt 6 1 ztjy/wk_account 8 3 ztjy/wk_account_tmp 8 3 ztjy/wk_account_tmptmp 8 3 ztjy/wk_auth_quota 8 3
4.1.2 space-page-type-regions //查看数据分布
innodb_space -s ibdata1 -T ztjy/aa space-page-type-regions
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztjy/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/chj_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 | 27 | 3 | 11 | 3 |
| opsdb/myapp_db_instance | PRIMARY | 47 | 3 | 18 | 3 |
| ztjy/aa | UNQ_RID | 51 | 2 | 19 | 4 |
| ztjy/aa | IDX_PID | 62 | 0 | 19 | 6 |
| ztjy/aaa | PRIMARY | 63 | 3 | 25 | 3 |
| ztjy/aaa | UNQ_RID | 64 | 2 | 25 | 4 |
| ztjy/aaa | IDX_PID | 65 | 0 | 25 | 5 |
| ztjy/info_area | PRIMARY | 41 | 3 | 16 | 3 |
| ztjy/info_area | UNQ_RID | 42 | 2 | 16 | 4 |
| ztjy/info_area | IDX_PID | 43 | 0 | 16 | 5 |
| ztjy/info_banners | PRIMARY | 55 | 3 | 21 | 3 |
| ztjy/info_banners | IND_BANNER_POS | 56 | 0 | 21 | 4 |
| ztjy/ttt | GEN_CLUST_INDEX | 60 | 1 | 23 | 3 |
+------------------------------+-------------------------------------------+----------+------+-------+---------+
4.1.3 space-indexes //索引结构、数据分配情况
innodb_space -s ibdata1 -T ztjy/aa space-indexes
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztjy/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 ztjy/aa space-page-type-summary
[root@vm-test01 ztjy]# innodb_space -s ../ibdata1 -T ztjy/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 header
4.1.5 space-index-pages-summary //查看表的索引分布在pages的情况
innodb_space -s ibdata1 -T ztjy/aaa space-index-pages-summar |head -n 10
innodb_space -f ztjy/aaa.ibd space-index-pages-summary |head -n 10
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztjy/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 ztjy]# innodb_space -f dim_library_phoneno_fei.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 ztjy]# innodb_space -s ../ibdata1 -T ztjy/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 ztjy/aa.PRIMARY. [root@vm-test01 ztjy]# [root@vm-test01 ztjy]# innodb_space -s ../ibdata1 -T ztjy/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 ztjy/aa.UNQ_RID.
4.2.2 page-records //查看辅助索引存储信息
innodb_space -s ibdata1 -T ztjy/aa -p 4 page-records
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztjy/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 ztjy/aa -p 5 page-records [root@vm-test01 data]# innodb_space -s ibdata1 -T ztjy/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 ztjy/aa -p 3 page-records
[root@vm-test01 data]# innodb_space -s ibdata1 -T ztjy/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 ztjy/aaa -I PRIMARY index-recurse
[root@vm-test01 ztjy]# innodb_space -s ../ibdata1 -T ztjy/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 ztjy/aaa -I PRIMARY index-record-offsets
[root@vm-test01 ztjy]# innodb_space -s ../ibdata1 -T ztjy/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 ztjy/aaa -I PRIMARY index-recurse
[root@vm-test01 ztjy]# innodb_space -s ../ibdata1 -T ztjy/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，逻辑上也是有序
的。
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
计算机程序设计艺术(第3卷)-排序和查找(英文影印版)
（美）Donald E.Knuth / 清华大学出版社 / 2002-9 / 85.00元
《计算机程序设计艺术排序和查找(第3卷)(第2版)》内容简介：这是对第3卷的头一次修订，不仅是对经典计算机排序和查找技术的最全面介绍，而且还对第1卷中的数据结构处理技术作了进一步的扩充，通盘考虑了将大小型数据库和内外存储器。它遴选了一些经过反复检验的计算机方法，并对其效率做了定量分析。第3卷的突出特点是对“最优排序”一节作了修订，对排列论原理与通用散列法作了全新讨论。一起来看看 《计算机程序设计艺术(第3卷)-排序和查找(英文影印版)》 这本书的介绍吧!
