解读InnodDB页面--普通页结构

很久之前了解到innodb_space，觉得很强大，可视化了InnoDB页面，但是没有形成文档，总结出来，有一些经常被问到的东东可以用这个查看了。

环境

centos 7
Linux 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)

页结构

page-account

[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 1 page-account
Accounting for page 1:
  Page type is IBUF_BITMAP (Insert buffer bitmap, bookkeeping for insert buffer writes to be merged).
  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 full_frag list of space.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 2 page-account
Accounting for page 2:
  Page type is INODE (File segment inode, bookkeeping for file segments).
  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 full_frag list of space.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -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 full_frag list of space.
  Page is in fragment array of fseg 1.
  Fseg is in internal fseg of index 74.
  Index root is page 3.
  Index is test/sbtest1.PRIMARY.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -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 full_frag list of space.
  Page is in fragment array of fseg 3.
  Fseg is in internal fseg of index 75.
  Index root is page 4.
  Index is test/sbtest1.k_1.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 5 page-account
Accounting for page 5:
  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 full_frag list of space.
  Page is in fragment array of fseg 5.
  Fseg is in internal fseg of index 76.
  Index root is page 5.
  Index is test/sbtest1.idx_is_used.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 6 page-account
Accounting for page 6:
  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 full_frag list of space.
  Page is in fragment array of fseg 7.
  Fseg is in internal fseg of index 77.
  Index root is page 6.
  Index is test/sbtest1.idx_gmt_create.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 7 page-account
Accounting for page 7:
  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 full_frag list of space.
  Page is in fragment array of fseg 2.
  Fseg is in leaf fseg of index 74.
  Index root is page 3.
  Index is test/sbtest1.PRIMARY.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 8 page-account
Accounting for page 8:
  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 full_frag list of space.
  Page is in fragment array of fseg 2.
  Fseg is in leaf fseg of index 74.
  Index root is page 3.
  Index is test/sbtest1.PRIMARY.
[root@localhost mysql]# innodb_space -s ibdata1 -T test/sbtest1 -p 9 page-account
Accounting for page 9:
  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 full_frag list of space.
  Page is in fragment array of fseg 1.
  Fseg is in internal fseg of index 74.
  Index root is page 3.

Index is test/sbtest1.PRIMARY

阅读全文请点击