InnoDB 表空间可视化工具innodb

innodb_ruby是jeremycole的一个用于分析Innodb相关结构的一个程序，也是非常方便我们研究Innodb的结构的工具**。

1. 工具安装**

1.1 安装ruby

操作系统版本：CentOS Linux release 7.6.1810 (Core)，默认的yum源安装后ruby的版本是2.0 ,而innodb_ruby需要2.2及以上版本，因此修改yum源，再安装指定高版本

yum install  -y  centos-release-scl-rh
复制代码

会在/etc/yum.repos.d/目录下多出一个CentOS-SCLo-scl-rh.repo源,然后安装2.3版本

yum install rh-ruby27 rh-ruby27-ruby-devel -y
复制代码

完成安装后切换版本，如果之前安装了2.2以下版本，此步骤必须做，以免默认使用的依旧是低版本的

 scl  enable  rh-ruby27 bash
复制代码

完成后检查一下版本

\# ruby --versionruby 2.7.1p83 (2020-03-31 revision a0c7c23c9c) \[x86\_64-linux\]# gem --version3.1.2

复制代码

1.2 安装innodb_ruby

安装完ruby,再进行安装即可

\# gem install innodb\_rubyFetching bindata-1.8.3.gemSuccessfully installed bindata-1.8.3Fetching rake-13.0.3.gemSuccessfully installed rake-13.0.3Fetching digest-crc-0.6.3.gemBuilding native extensions. This could take a while...Successfully installed digest-crc-0.6.3Fetching innodb\_ruby-0.9.16.gemSuccessfully installed innodb\_ruby-0.9.16Parsing documentation for bindata-1.8.3Installing ri documentation for bindata-1.8.3Parsing documentation for rake-13.0.3Installing ri documentation for rake-13.0.3Parsing documentation for digest-crc-0.6.3Installing ri documentation for digest-crc-0.6.3Parsing documentation for innodb\_ruby-0.9.16Installing ri documentation for innodb\_ruby-0.9.16Done installing documentation for bindata, rake, digest-crc, innodb\_ruby after 4 seconds4 gems installed

复制代码

安装完毕可以查看帮助

\# innodb\_space --helpUsage: innodb\_space <options> <mode>

复制代码

1.3 常见错误

错误1:

\# gem install innodb\_rubyFetching: bindata-1.8.3.gem (100%)Successfully installed bindata-1.8.3Fetching: rake-13.0.3.gem (100%)ERROR:  Error installing innodb\_ruby:    rake requires Ruby version >= 2.2.

复制代码

此报错就是ruby版本低所致，安装前面的方式处理即可

错误2：

ERROR:  Error installing innodb\_ruby:
    ERROR: Failed to build gem native extension.

    current directory: /opt/rh/rh-ruby23/root/usr/local/share/gems/gems/digest-crc-0.6.3/ext/digest
/opt/rh/rh-ruby23/root/usr/bin/ruby -rubygems /opt/rh/rh-ruby23/root/usr/local/share/gems/gems/rake-13.0.3/exe/rake RUBYARCHDIR=/opt/rh/rh-ruby23/root/usr/local/lib64/gems/ruby/digest-crc-0.6.3 RUBYLIBDIR=/opt/rh/rh-ruby23/root/usr/local/lib64/gems/ruby/digest-crc-0.6.3
mkmf.rb can't find header files for ruby at /opt/rh/rh-ruby23/root/usr/share/include/ruby.h

rake failed, exit code 1

Gem files will remain installed in /opt/rh/rh-ruby23/root/usr/local/share/gems/gems/digest-crc-0.6.3 for inspection.
Results logged to /opt/rh/rh-ruby23/root/usr/local/lib64/gems/ruby/digest-crc-0.6.3/gem\_make.out
复制代码

是因为ruby-devel未安装导致，安装对应版本的即可  

复制代码

yum -y install ruby rubygems


**2\. 工具使用**


================

**2.1 功能介绍**


----------------

innodb\_space包含较多选项，可通过innodb\_space  --help命令查看具体内容，主要几个参数如下：

复制代码

–system-space-file, -s Load the system tablespace file or files : Either a single file e.g. “ibdata1”, a comma-delimited list of files e.g. “ibdata1,ibdata1”, or a directory name. If a directory name is provided, it will be scanned for all files named “ibdata?” which will then be sorted alphabetically and used to load the system tablespace. –table-name, -T Use the table name . –index-name, -I Use the index name . –space-file, -f Load the tablespace file .


**2.2 实操**


--------------

先创建一个测试环境，创建一个库及表

复制代码

mysql> create database testdb;Query OK, 1 row affected (0.01 sec)mysql> use testdb;Database changedmysql> create table test1(id int primary key auto_increment,c1 varchar(10),dt datetime ,key c1(c1));Query OK, 0 rows affected (0.04 sec)mysql> insert into test1 values(1,’abc’,now());Query OK, 1 row affected (0.03 sec)


### 

**2.2.1  列出所有物理对象的数量**

复制代码

— 查看数据目录mysql> show variables like ‘%datadir%’;+—————+—————————–+| Variable_name | Value |+—————+—————————–+| datadir | /data/mysql/mysql3306/data/ |+—————+—————————–+1 row in set (0.03 sec)mysql> exitBye– 在数据目录下操作# cd /data/mysql/mysql3306/data/# innodb_space -s ibdata1 system-spacesname pages indexes (system) 768 10 mysql/engine_cost 6 1 mysql/gtid_executed 6 1 mysql/help_category 7 2 mysql/help_keyword 15 2 mysql/help_relation 8 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 testdb/test1 7 2


### 

**2.2.2 查看索引信息**

因为创建的测试表包含主键及c1字段的，结果如下

复制代码

# innodb_space -s ibdata1 -T testdb/test1 space-indexesid name root fseg fseg_id used allocated fill_factor 44 PRIMARY 3 internal 1 1 1 100.00% 44 PRIMARY 3 leaf 2 0 0 0.00% 45 c1 4 internal 3 1 1 100.00% 45 c1 4 leaf 4
0 0 0.00%


对应内容简述  

**列名  
**

**说明  
**

id  

索引id

name

索引名称，PRIMARY代表主键索引（聚集索引），因为InnoDB表是聚集索引组织表，行记录就是聚集索引

root  

索引中根节点的page号

fseg

page类型：internal非叶子节点；leaf叶子节点

used

该索引使用的page页

allocated

该索引分配的page页

fill\_factor

该索引使用百分比

结果可以与mysql.innodb\_index\_stats表对应上。

复制代码

mysql> select * from mysql.innodb_index_stats where database_name=’testdb’ and table_name=’test1′;+—————+————+————+———————+————–+————+————-+———————————–+| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |+—————+————+————+———————+————–+————+————-+———————————–+| testdb | test1 | PRIMARY | 2021-04-25 09:56:47 | n_diff_pfx01 | 0 | 1 | id || testdb | test1 | PRIMARY | 2021-04-25 09:56:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index || testdb | test1 | PRIMARY | 2021-04-25 09:56:47 | size | 1 | NULL | Number of pages in the index || testdb | test1 | c1 | 2021-04-25 09:56:47 | n_diff_pfx01 | 0 | 1 | c1 || testdb | test1 | c1 | 2021-04-25 09:56:47 | n_diff_pfx02 | 0 | 1 | c1,id || testdb | test1 | c1 | 2021-04-25 09:56:47 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index || testdb | test1 | c1 | 2021-04-25 09:56:47 | size | 1 | NULL | Number of pages in the index |+—————+————+————+———————+————–+————+————-+———————————–+7 rows in set (0.01 sec)


### 

**2.2.3 统计每个类型的页占用页的数量**

复制代码

# innodb_space -s ibdata1 -T testdb/test1 space-page-type-regionsstart end count type 0 0 1 FSP_HDR 1 1 1 IBUF_BITMAP 2 2 1 INODE 3 4 2 INDEX 5 6 2 FREE (ALLOCATED)


### 

**2.2.4  每个类型页数总计**

复制代码

# innodb_space -s ibdata1 -T testdb/test1 space-page-type-summarytype count percent description INDEX 2 28.57 B+Tree index ALLOCATED 2 28.57 Freshly allocated FSP_HDR 1 14.29 File space header IBUF_BITMAP 1 14.29 Insert buffer bitmapINODE 1 14.29 File segment inode


### 

**2.2.5 统计所有的页在表空间的饱和度信息**

每个页面显示彩色块（按index/purpose着色），根据页面中的数据量调整大小，可以多操作后再查看

复制代码

innodb_space -s ibdata1 -T testdb/test1 space-extents-illustrat


![图片](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a312443083684d359877172e63e1fbd3~tplv-k3u1fbpfcp-zoom-1.image)  

**2.2.6   统计所有的页在表空间的饱和度信息**

每个页面显示彩色块 (按页面修改LSN的年龄着色)

复制代码

innodb_space -s ibdata1 -T testdb/test1 space-lsn-age-illustrate


![图片](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4e02e2458f26414b85087c33503779da~tplv-k3u1fbpfcp-zoom-1.image)

### 

**2.2.7  查看指定页面的信息**

参考中2.2.2中page号（root值），查看对应页面的信息，可以查询具体的结果说明

复制代码

# innodb_space -s ibdata1 -T testdb/test1 -p 3 page-accountAccounting 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 44. Index root is page 3. Index is testdb/test1.PRIMARY. # innodb_space -s ibdata1 -T testdb/test1 -p 4 page-accountAccounting 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 45. Index root is page 4. Index is testdb/test1.c1.


### 

**2.2.8  查看页结构信息**

查看指定页信息，本次只查看主键页的信息，这样包含了所有字段的内容，内容在type=>:clustered 部分，例如：

复制代码

# innodb_space -s ibdata1 -T testdb/test1 -p 3 page-dump#Innodb::Page::Index:0x0000000002d848c8:fil header:{:checksum=>1296112206, :offset=>3, :prev=>nil, :next=>nil, :lsn=>2535779, :type=>:INDEX, :flush_lsn=>0, :space_id=>23}fil trailer:{:checksum=>1296112206, :lsn_low32=>2535779}page header:{:n_dir_slots=>2, :heap_top=>152, :garbage_offset=>0, :garbage_size=>0, :last_insert_offset=>127, :direction=>:no_direction, :n_direction=>0, :n_recs=>1, :max_trx_id=>0, :level=>0, :index_id=>44, :n_heap=>3, :format=>:compact}fseg header:{:leaf=> <Innodb::Inode space=<Innodb::Space file=”testdb/test1.ibd”, page_size=16384, pages=7>, fseg=2>, :internal=> <Innodb::Inode space=<Innodb::Space file=”testdb/test1.ibd”, page_size=16384, pages=7>, fseg=1>}sizes: header 120 trailer 8 directory 4 free 16220 used 164 record 32 per record 32.00page directory:[99, 112]system records:{:offset=>99, :header=> {:next=>127, :type=>:infimum, :heap_number=>0, :n_owned=>1, :min_rec=>false, :deleted=>false, :length=>5}, :next=>127, :data=>”infimum\x00″, :length=>8}{:offset=>112, :header=> {:next=>112, :type=>:supremum, :heap_number=>1, :n_owned=>2, :min_rec=>false, :deleted=>false, :length=>5}, :next=>112, :data=>”supremum”, :length=>8}garbage records:records:{:format=>:compact, :offset=>127, :header=> {:next=>112, :type=>:conventional, :heap_number=>2, :n_owned=>0, :min_rec=>false, :deleted=>false, :nulls=>[], :lengths=>{“c1″=>3}, :externs=>[], :length=>7}, :next=>112, :type=>:clustered, :key=>[{:name=>”id”, :type=>”INT”, :value=>1}], :row=> [{:name=>”c1″, :type=>”VARCHAR(30)”, :value=>”abc”}, {:name=>”dt”, :type=>”DATETIME”, :value=>”184913516-11-99 82:08:00″}], :sys=> [{:name=>”DB_TRX_ID”, :type=>”TRX_ID”, :value=>1287}, {:name=>”DB_ROLL_PTR”, :type=>”ROLL_PTR”, :value=> {:is_insert=>true, :rseg_id=>39, :undo_log=>{:page=>286, :offset=>272}}}], :length=>28, :transaction_id=>1287, :roll_pointer=> {:is_insert=>true, :rseg_id=>39, :undo_log=>{:page=>286, :offset=>272}}}


### 

**2.2.9  会送一个页面的所有记录**

先多插入一些记录，看起来更全面一些

复制代码

mysql> insert into test1 values(2,’cbd’,’2020-01-01′),(10,’item’,’2021-01-01′),(1000,’i1000′,now());Query OK, 3 rows affected (0.50 sec)Records: 3 Duplicates: 0 Warnings: 0


再查看内容

复制代码

# innodb_space -s ibdata1 -T testdb/test1 -p 3 page-recordsRecord 127: (id=1) → (c1=”abc”, dt=”184913516-12-00 01:74:08″)Record 159: (id=2) → (c1=”cbd”, dt=”184795578-98-73 15:20:00″)Record 191: (id=10) → (c1=”item”, dt=”184887058-35-47 52:71:68″)Record 224: (id=1000) → (c1=”i1000″, dt=”184913538-66-52 04:94:08″)


可见，该主键索引的所有内容每页就是所有记录内容。

在看一下二级索引c1的内容，也便于理解二级索引，会有主键id的信息

复制代码

# innodb_space -s ibdata1 -T testdb/test1 -p 4 page-recordsRecord 127: (c1=”abc”) → (id=1)Record 141: (c1=”cbd”) → (id=2)Record 170: (c1=”i1000″) → (id=1000)Record 155: (c1=”item”) → (id=10)


其他的内容也很多，例如可以查具体的索引，记录结构等，本次只是抛砖引玉，小伙伴们可以自行探索，后续有机会也会结合实例继续学习。

  

![图片](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/d404ee248e8546f98ca46c773ba1ab9b~tplv-k3u1fbpfcp-zoom-1.image)

往期精彩回顾

1. [MySQL高可用之MHA集群部署](http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247484084&idx=1&sn=45a157c10972cbde7788ccbd2f37a2ff&chksm=97b5627aa0c2eb6cc3db070230cb38fa3a075aeb86cbdd4ef2d1e9bd8fd4f1e2d9ea735317bd&scene=21#wechat_redirect)

2. [mysql8.0新增用户及加密规则修改的那些事](http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483672&idx=1&sn=f86d339d8c871d9087a70b4518f5e305&chksm=97b561d6a0c2e8c0d9e313ae88c3d0970d6620e378a26c9785a962a4f689029297626996757c&scene=21#wechat_redirect)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

3.  比hive快10倍的大数据查询利器-- presto
------------------------------

[4\.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库](http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483770&idx=1&sn=997833e9868ff44bd475ab49db20a136&chksm=97b561b4a0c2e8a2dc8bf4059cd2b3f57adb848f8f5ab08dabb128221faf40067c1ad866653f&scene=21#wechat_redirect)

5.  PostgreSQL主从复制--物理复制

6.  MySQL传统点位复制在线转为GTID模式复制  

7[.  MySQL敏感数据加密及解密](http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483933&idx=1&sn=98b9e7e39ca47ca8565fe09ca60f4bc3&chksm=97b562d3a0c2ebc5b72687d66bbb1f147b9ba899130db73c341f656cbd659a8c274869b509d6&scene=21#wechat_redirect)

8[.  MySQL数据备份及还原（一）](http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483939&idx=1&sn=073125f1ed17465cfcd471f81937988e&chksm=97b562eda0c2ebfbf7e9271eebcca0f3ff319656179840370b2c089ccadb80fbfc0001e11991&scene=21#wechat_redirect)

9[.  MySQL数据备份及还原（二）](http://mp.weixin.qq.com/s?__biz=MzIxMzYxMjk2MQ==&mid=2247483944&idx=1&sn=eb134e16e4b8cd1a5655d6168f535172&chksm=97b562e6a0c2ebf0d26679a44b834027ba942fa747805b289dd8bf9edcb02317018e36012a7b&scene=21#wechat_redirect)

![图片](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/c719810dae904e93ab7ebbe77c594f13~tplv-k3u1fbpfcp-zoom-1.image)

扫码关注       

![图片](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/9c1e170767c845e0af288991ea512de8~tplv-k3u1fbpfcp-zoom-1.image)

![图片](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/fa705397c2924c5eb97ae9a319942e39~tplv-k3u1fbpfcp-zoom-1.image)

![图片](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a7df0a71f3e040e19dd11203f83d68b3~tplv-k3u1fbpfcp-zoom-1.image)
复制代码

文章版权归作者所有，未经允许请勿转载。

THE END

后端