首页 > 数据库 > MySQL

【Mysql】innodb_space 的使用介绍

来源：SegmentFault

时间：2023-01-13 09:31:03 228浏览收藏

对于一个数据库开发者来说，牢固扎实的基础是十分重要的，golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《【Mysql】innodb_space 的使用介绍》，主要介绍了MySQL，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！

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  Load the tablespace file (system or table)
Against a system tablespace which will auto-load file-per-table tablespace files:
Option Parameters Description
-s  Load the system tablespace file (e.g. ibdata1)
-T  Use the given table name.
-I  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=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 header

4.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，逻辑上也是有序
的。
今天关于《【Mysql】innodb_space 的使用介绍》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！

mysql MySQL

声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除