innodb结构解析工具---innodb_ruby

1.下载ruby并安装ruby:

　　ftp://ftp.ruby-lang.org/pub/ruby/

　　ftp://ftp.ruby-lang.org/pub/ruby/ruby-2.3-stable.tar.gz

　　tar -xvf ruby-2.3-stable.tar.gz

　　cd ruby-2.3.1

　　./configure

　　make

　　make install

2.下载rubygems：

　　https://rubygems.global.ssl.fastly.net/rubygems/rubygems-2.6.4.zip （到https://rubygems.org/ 首页找的rubygems下载地址）

cd rubygems-2.6.4

ruby setup.rb

3.下载并安装innodb_ruby

　　https://rubygems.org/gems/innodb_ruby （进入rubygems官网去找下载链接)

　　https://rubygems.global.ssl.fastly.net/gems/innodb_ruby-0.9.13.gem (浏览器下载）

wget https://rubygems.global.ssl.fastly.net/rubygems/rubygems-2.6.4.zip --no-check-certificate (wget下载)

　　gem install innodb_ruby-0.9.13.gem

  [root@localhost ~]# gem -v
　　2.6.4

　　[root@localhost ~]# gem list |grep innodb

　　 innodb_ruby (0.9.13)

4.innodb_ruby使用文档:

　　https://github.com/jeremycole/innodb_ruby/wiki

　　https://blog.jcole.us/2013/01/03/a-quick-introduction-to-innodb-ruby/

https://blog.jcole.us/2014/10/02/visualizing-the-impact-of-ordered-vs-random-index-insertion-in-innodb/

http://luodw.cc/2016/03/15/innodb03/

5.实例:

进入到mysql数据目录：/data
[root@localhost data]# innodb_space -s ibdata1 system-spaces                        
name                            pages       indexes     
(system)                        4864        6           
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           
test/t1                         8           1           
test/test1                      6           1           
test/test_auto_increment        6           1           
test/test_char                  6           1           
test/test_ci                    7           2           
test/test_int_n                 6           1           
test/test_time                  6           1           
test/test_unsigned              6           1  

查看表t1索引
[root@localhost data]# innodb_space -s ibdata1 -T test/t1 space-indexes        
id          name                            root        fseg        used        allocated   fill_factor 
58          PRIMARY                         3           internal    1           1           100.00%     
58          PRIMARY                         3           leaf        3           3           100.00%     

[root@localhost data]# innodb_space -s ibdata1 -T test/t1 space-indexes        
id          name                            root        fseg        used        allocated   fill_factor 
58          PRIMARY                         3           internal    1           1           100.00%     
58          PRIMARY                         3           leaf        3           3           100.00%     
You have new mail in /var/spool/mail/root
[root@localhost data]# innodb_space -s ibdata1 -T test/t1 space-lsn-age-illustrate            

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███████ │
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
       Min LSN <─────────────────────────────────────────> Max LSN     
       2059083 ███████████████████████████████████████████ 2119310  

$ irb -r innodb
> sys = Innodb::System.new("ibdata1")
> idx = sys.index_by_name("test/t1", "PRIMARY")
> rec = idx.binary_search([1])

[root@localhost data]# innodb_space -s ibdata1 -T test/test1 space-extents-illustrate

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▁??                                                          │
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3   50.00%
  █ Index 62 (test/test1.GEN_CLUST_INDEX)                               1   16.67%
  ? Free space                                                          2   33.33%

[root@localhost data]# innodb_space -f test/t1.ibd space-page-type-regions
start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3           6           4           INDEX               
7           7           1           FREE (ALLOCATED)    

[root@localhost data]# innodb_space -f test/t1.ibd -p 3 page-dump
#<Innodb::Page::Index:0x007f798307d970>:

fil header:
{:checksum=>2022539217,
 :offset=>3,
 :prev=>nil,
 :next=>nil,
 :lsn=>2119310,
 :type=>:INDEX,
 :flush_lsn=>0,
 :space_id=>35}

page header:
{:n_dir_slots=>2,
 :heap_top=>162,
 :garbage_offset=>0,
 :garbage_size=>0,
 :last_insert_offset=>154,
 :direction=>:right,
 :n_direction=>2,
 :n_recs=>3,
 :max_trx_id=>0,
 :level=>1,
 :index_id=>58,
 :n_heap=>5,
 :format=>:compact}

fseg header:
{:leaf=>
  <Innodb::Inode space=<Innodb::Space file="test/t1.ibd", page_size=16384, pages=8>, fseg=2>,
 :internal=>
  <Innodb::Inode space=<Innodb::Space file="test/t1.ibd", page_size=16384, pages=8>, fseg=1>}

sizes:
  header           120
  trailer            8
  directory          4
  free           16210
  used             174
  record            42
  per record     14.00

page directory:
[99, 112]

system records:
{:offset=>99,
 :header=>
  {:next=>126,
   :type=>:infimum,
   :heap_number=>0,
   :n_owned=>1,
   :min_rec=>false,
   :deleted=>false,
   :length=>5},
 :next=>126,
 :data=>"infimum\x00",
 :length=>8}
{:offset=>112,
 :header=>
  {:next=>112,
   :type=>:supremum,
   :heap_number=>1,
   :n_owned=>4,
   :min_rec=>false,
   :deleted=>false,
   :length=>5},
 :next=>112,
 :data=>"supremum",
 :length=>8}

garbage records:

records:
{:format=>:compact,
 :offset=>126,
 :header=>
  {:next=>140,
   :type=>:node_pointer,
   :heap_number=>2,
   :n_owned=>0,
   :min_rec=>true,
   :deleted=>false,
   :length=>5},
 :next=>140}

{:format=>:compact,
 :offset=>140,
 :header=>
  {:next=>154,
   :type=>:node_pointer,
   :heap_number=>3,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :length=>5},
 :next=>154}

{:format=>:compact,
 :offset=>154,
 :header=>
  {:next=>112,
   :type=>:node_pointer,
   :heap_number=>4,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :length=>5},
 :next=>112}

[root@localhost data]# innodb_space -f test/t1.ibd space-index-pages-summary
page        index   level   data    free    records 
3           58      1       42      16210   3       
4           58      0       7025    9227    1       
5           58      0       14050   2202    2       
6           58      0       7025    9227    1       
7           0       0       0       16384   0