点击上方蓝字关注我

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.3mkmf.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 <arg>    Load the system tablespace file or files <arg>: 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 <name>    Use the table name <name>.
  --index-name, -I <name>    Use the index name <name>.
  --space-file, -f <file>    Load the tablespace file <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%

对应内容简述

结果可以与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

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

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

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

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.00
page 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)

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

往期精彩回顾

1.  MySQL高可用之MHA集群部署

2.  mysql8.0新增用户及加密规则修改的那些事

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

4.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

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

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

7.  MySQL敏感数据加密及解密

8.  MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫码关注