點擊上方藍(lán)字關(guān)注我

innodb_ruby是jeremycole的一個用于分析Innodb相關(guān)結(jié)構(gòu)的一個程序，也是非常方便我們研究Innodb的結(jié)構(gòu)的工具。

1. 工具安裝

1.1 安裝ruby

操作系統(tǒng)版本：CentOS Linux release 7.6.1810 (Core)，默認(rèn)的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以下版本，此步驟必須做，以免默認(rèn)使用的依舊是低版本的


 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,再進(jìn)行安裝即可


# 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未安裝導(dǎo)致，安裝對應(yīng)版本的即可


 yum -y install ruby rubygems

2. 工具使用

2.1 功能介紹

innodb_space包含較多選項，可通過innodb_space --help命令查看具體內(nèi)容，主要幾個參數(shù)如下：


--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 實操

先創(chuàng)建一個測試環(huán)境，創(chuàng)建一個庫及表


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 列出所有物理對象的數(shù)量


--  查看數(shù)據(jù)目錄mysql> show variables like '%datadir%';+---------------+-----------------------------+| Variable_name | Value                       |+---------------+-----------------------------+| datadir       | /data/mysql/mysql3306/data/ |+---------------+-----------------------------+1 row in set (0.03 sec)
mysql> exitBye
-- 在數(shù)據(jù)目錄下操作# 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 查看索引信息

因為創(chuàng)建的測試表包含主鍵及c1字段的，結(jié)果如下


# 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%

對應(yīng)內(nèi)容簡述

列名	說明
id	索引id
name	索引名稱，PRIMARY代表主鍵索引（聚集索引），因為InnoDB表是聚集索引組織表，行記錄就是聚集索引
root	索引中根節(jié)點的page號
fseg	page類型：internal非葉子節(jié)點；leaf葉子節(jié)點
used	該索引使用的page頁
allocated	該索引分配的page頁
fill_factor	該索引使用百分比

結(jié)果可以與mysql.innodb_index_stats表對應(yīng)上。


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 統(tǒng)計每個類型的頁占用頁的數(shù)量


# 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 每個類型頁數(shù)總計


# 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 統(tǒng)計所有的頁在表空間的飽和度信息

每個頁面顯示彩色塊（按index/purpose著色），根據(jù)頁面中的數(shù)據(jù)量調(diào)整大小，可以多操作后再查看


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

2.2.6 統(tǒng)計所有的頁在表空間的飽和度信息

每個頁面顯示彩色塊 (按頁面修改LSN的年齡著色)

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

2.2.7 查看指定頁面的信息

參考中2.2.2中page號（root值），查看對應(yīng)頁面的信息，可以查詢具體的結(jié)果說明


# 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 查看頁結(jié)構(gòu)信息

查看指定頁信息，本次只查看主鍵頁的信息，這樣包含了所有字段的內(nèi)容，內(nèi)容在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

再查看內(nèi)容


# 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")

可見，該主鍵索引的所有內(nèi)容每頁就是所有記錄內(nèi)容。

在看一下二級索引c1的內(nèi)容，也便于理解二級索引，會有主鍵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)