使用innodb_ruby来理解innodb内部结构

来源：互联网发布：linux ftp权限设置编辑：程序博客网时间：2024/05/23 14:55

1. 前言

    最近发现了一款由Jeremy Cole带来的数据库innodb格式化分析工具,此工具的强大之处在于可以将innodb内部存储的信息格式化的解释给大家,让人更清晰的理解INNODB的数据结构.    它是一款非常轻量级的应用,同时存在bug风险,不建议给生产环境使用.但是我们在测试环境就可以很好的了解该工具和该工具带来的便利.    README中是这样说的:    1.  一款学习工具,让你很轻松的理解innodb架构    2.  一款教学工具,通过ruby的irb的交互式界面,可以看到innodb的架构与发生的变化    3.  一款调查工具,InnoDB引擎不会告诉你它在磁盘正在做那些任务或者做过那些任务,例如,我们可以获取页的饱和度,每页具体含有多少记录,B+Tree的具体架构等详细内容    4.  一款调试工具,当一些行为或操作会影响到数据结构的时候,可以通过innodb_ruby工具直观的审查,确保与期望的结果相同

2.安装

2.1
基础环境配置

shell# yum install \glibc-headers \autoconf \gcc-c++ \glibc-devel \readline-devel \zlib-devel \libffi-devel \openssl-devel \automake \libtool \bison \sqlite-devel \ftp://195.220.108.108/linux/centos/6.8/os/x86_64/Packages/libyaml-devel-0.1.3-4.el6_6.x86_64.rpm \ftp://195.220.108.108/linux/centos/6.8/os/x86_64/Packages/libyaml-0.1.3-4.el6_6.x86_64.rpmshell# curl -L get.rvm.io | bash -s stableshell# source ~/.bashrcshell# source ~/.bash_profileshell# rvm list knownshell# rvm install 2.2.0

2.2
以root身份运行:

shell# sudo gem install innodb_ruby

以用户身份运行(也可使用root):

shell# gem install --user-install innodb_ruby

或者直接从github上拷贝(不同版本在 https://rubygems.org/gems/innodb_ruby 获取)

shell# git clone https://github.com/jeremycole/innodb_ruby.gitshell# cd innodb_rubyshell# ruby -r rubygems -I lib bin/innodb_space ...

3.参数

首先,我们shell># innodb_space --help |more 可以看到有以下两行样例:innodb_space -s ibdata1 [-T tname [-I iname]] [options]innodb_space -f tname.ibd [-r ./desc.rb -d DescClass] [options] <mode>其中:    "-s"    表示系统表空间名;    "-T"    表示给定的表名;    "-I"    表示给定的索引;    "-f"    表示给定的表空间文件接下来,我们创建一张表并看几条事例mysql> show full columns from db.test;+--------+-------------+-------------------+------+-----+---------+--------------+| Field  | Type        | Collation         | Null | Key | Default | Comment      |+--------+-------------+-------------------+------+-----+---------+--------------+| id     | int(11)     | NULL              | NO   | PRI | NULL    | 自增主键      || rannum | varchar(20) | latin1_swedish_ci | YES  |     | NULL    | 随机码        |+--------+-------------+-------------------+------+-----+---------+--------------+2 rows in set (0.00 sec)mysql> select count(*) from db.test;+----------+| count(*) |+----------+|    24997 |+----------+

3.1 Space File Structure

system-spaces

shell# innodb_space -s ibdata1 system-spaces    name                            pages       indexes         (system)                        13056       7               db/store                        6           1               db/test                         768         1               ...    其中,system-spaces是模式名,在该模式下,innodb_space列出所有物理对象的数量,innodb_space 有很多的模式以供选择,我们挑几个主要进行简单说明

space-indexes

shell# innodb_space -s ibdata1 -T db/testspace-indexes       id          name            root        fseg        used        allocated   fill_factor     1198        PRIMARY         3           internal    1           1           100.00%         1198        PRIMARY         3           leaf        61          270         22.59%    其中,space-indexes列出了tb表的索引统计信息

space-page-type-regions

shell# innodb_space -s ibdata1 -T db/test space-page-type-regions    start       end         count       type                               0           0           1           FSP_HDR                 1           1           1           IBUF_BITMAP             2           2           1           INODE                   3           3           1           INDEX                   4           4           1           FREE (INDEX)                 ..                   237         237         1           INDEX                   238         254         17          FREE (INDEX)            255         275         21          INDEX                   276         383         108         FREE (ALLOCATED)      其中,space-page-type-regions列出的是innodb_space遍历所有的页后,对同类型的页的合并成的一个个"区域"信息

space-page-type-summary

shell# innodb_space -s ibdata1 -T db/test space-page-type-summarytype                count       percent     description         ALLOCATED           520         67.71       Freshly allocated   INDEX               245         31.90       B+Tree index        INODE               1           0.13        File segment inode  IBUF_BITMAP         1           0.13        Insert buffer bitmapFSP_HDR             1           0.13        File space header       其中,space-page-type-summary列出的是innodb_space遍历所有的页后,按照页的类型进行的一个统计和

space-extents-illustrate

shell# innodb_space -s ibdata1 -T db/test space-extents-illustrate

space-extents-illustrate

 其中,图示表示所有的页在空间的饱和度信息

space-lsn-age-illustrate

 shell# innodb_space -s ibdata1 -T db/test space-lsn-age-illustrate

space-lsn-age-illustrate

其中,图示表示的是所有的页在空间的时间标识,通过下面的顺序条判断数据的新旧程度

3.2 Page Structure

page-account

 shell# innodb_space -s ibdata1 -T db/test -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 free_frag list of space.      Page is in fragment array of fseg 1.      Fseg is in internal fseg of index 1198.      Index root is page 3.      Index is db/test.PRIMARY.  这里的page-account为单独某个页的统计信息,用来告诉你该页面的作用

page-dump

 shell# innodb_space -s ibdata1 -T db/test -p 3 page-dump     #<Innodb::Page::Index:0x000000020c8e68>:    fil header:    {:checksum=>1030305634,     :offset=>3,     :prev=>nil,     :next=>nil,     :lsn=>1492774578,     :type=>:INDEX,     :flush_lsn=>0,     :space_id=>511}    fil trailer:    {:checksum=>1030305634, :lsn_low32=>1492774578}    page header:    {:n_dir_slots=>14,     :heap_top=>3298,     :garbage_offset=>812,     :garbage_size=>2324,     :last_insert_offset=>546,     :direction=>:left,     :n_direction=>20,     :n_recs=>61,     :max_trx_id=>0,     :level=>1,     :index_id=>1198,     :n_heap=>229,     :format=>:compact}    fseg header:    {:leaf=>      <Innodb::Inode space=<Innodb::Space file="db/test.ibd", page_size=16384, pages=768>, fseg=2>,     :internal=>      <Innodb::Inode space=<Innodb::Space file="db/test.ibd", page_size=16384, pages=768>, fseg=1>}    sizes:      header           120      trailer            8      directory         28      free           15374      used            1010      record           854      per record     14.00    page directory:    [99, 210, 322, 434, 700, 1176, 1736, 2408, 2786, 168, 140, 448, 308, 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=>5,       :min_rec=>false,       :deleted=>false,       :length=>5},     :next=>112,     :data=>"supremum",     :length=>8}    garbage records:    {:format=>:compact,     :offset=>812,     :header=>      {:next=>574,       :type=>:node_pointer,       :heap_number=>51,       :n_owned=>0,       :min_rec=>false,       :deleted=>false,       :nulls=>[],       :lengths=>{},       :externs=>[],       :length=>5},     :next=>574,     :type=>:clustered,     :key=>[{:name=>"id", :type=>"INT", :value=>27834}],     :row=>[],     :sys=>[],     :child_page_number=>83,     :length=>8}    {:format=>:compact,     :offset=>574,     :header=>      {:next=>602,       :type=>:node_pointer,       :heap_number=>34,       :n_owned=>0,       :min_rec=>false,       :deleted=>false,       :nulls=>[],       :lengths=>{},       :externs=>[],       :length=>5},     :next=>602,     :type=>:clustered,     :key=>[{:name=>"id", :type=>"INT", :value=>13931}],     :row=>[],     :sys=>[],     :child_page_number=>64,     :length=>8}     ...从dump的结果来看,索引页一共包括:(以下信息较难理解,推荐看一些索引数据的结构)

名称大小注释作用 fil header 38 文件头信息用来记录它的上(下)索引页节点 fil trailer 8 文件尾信息记录校验值和日志序列号 page header 36 索引页头信息记录索引内真实数据的动态信息 fseg header 20 文件段信息文件段信息 sizes 26 尺寸记录数据的空间分布 page directory 页目录页目录指针 system records 用户数据用户实际存储的数据

page-records

shell# innodb_space -s ibdata1 -T db/test -p 10 page-records    Record 127: (id=2432) → (rannum="0.64899111")    Record 161: (id=2433) → (rannum="0.79129054")    Record 195: (id=2434) → (rannum="0.69663955")    Record 229: (id=2435) → (rannum="0.80999563")    Record 263: (id=2436) → (rannum="0.71295627")    Record 297: (id=2437) → (rannum="0.82195458")    ...很明显,page-records列出的是该页所有的记录

page-directory-summary

[shell]# innodb_space -s ibdata1 -T db/test -p 10 page-directory-summary    slot    offset  type          owned   key    0       99      infimum       1           1       229     conventional  4       (id=2435)    2       364     conventional  4       (id=2439)    3       635     conventional  5       (id=2447)    4       906     conventional  5       (id=2455)    5       1143    conventional  6       (id=2462)    ...    49      14862   conventional  7       (id=2867)    50      112     supremum      2page-directory-summary在这里罗列了他的字典记录,从owned可以看出,每个字典一般包含4~8个数据记录,这里的infimum和supremum是page的下(上)确界.

page-directory-summary

[shell]# innodb_space -s ibdata1 -T db/test -p 10 page-illustrate

page-directory-summary

上图是该页的完整分布图以及每个类所占的字节数(从图示可以看出,每个页的字典记录是从下往上写的)

3.3 Index Structure

index-recurse

 [shell]# innodb_space -s ibdata1 -T db/test -I PRIMARY index-recurse    ROOT NODE #3: 61 records, 854 bytes        NODE POINTER RECORD ≥ (id=1) → #5        LEAF NODE #5: 336 records, 11381 bytes          RECORD: (id=2) → (rannum="0.95285024")          RECORD: (id=3) → (rannum="0.92228166")          RECORD: (id=5) → (rannum="0.98971622")          RECORD: (id=6) → (rannum="0.73422374")          RECORD: (id=8) → (rannum="0.98337568")          RECORD: (id=11) → (rannum="0.92850563")          RECORD: (id=13) → (rannum="0.72389883")          RECORD: (id=14) → (rannum="0.55342402")          RECORD: (id=15) → (rannum="0.98521288")          ...          RECORD: (id=20784742) → (rannum="3.72474e-5")          RECORD: (id=20784743) → (rannum="3.6611e-7")          RECORD: (id=20784744) → (rannum="6.41086e-5")          RECORD: (id=20784745) → (rannum="6.76047e-5")          RECORD: (id=20784746) → (rannum="6.18684e-7")          RECORD: (id=20784747) → (rannum="2.09508e-6")          RECORD: (id=20784748) → (rannum="4.56242e-9")          RECORD: (id=20784749) → (rannum="1.86251e-6")          RECORD: (id=20784750) → (rannum="6.86121e-6")          ... 这里做了一次全表索引递归,可以看到主键(这里是id)与记录的对应关系

为了更清晰的认识索引,我们在test表上创建一个辅助索引,来观察辅助索引的对应关系

mysql> alter table db.test add index idx_rad(rannum);然后继续执行[shell]# innodb_space -s ibdata1 -T db/test -I idx_rad index-recurse    ROOT NODE #4: 33 records, 819 bytes      NODE POINTER RECORD ≥ (rannum="0.001") → #6      LEAF NODE #6: 759 records, 15861 bytes        RECORD: (rannum="0.001") → (id=20776852)        RECORD: (rannum="0.0010027") → (id=20777785)        RECORD: (rannum="0.00100492") → (id=20777501)        RECORD: (rannum="0.00100629") → (id=20783325)        RECORD: (rannum="0.00100675") → (id=20782820)        RECORD: (rannum="0.00101203") → (id=20779200)        RECORD: (rannum="0.00101874") → (id=20775399)        RECORD: (rannum="0.00102316") → (id=20781666)        RECORD: (rannum="0.00102493") → (id=20778600)        RECORD: (rannum="0.00102554") → (id=20779055)        ...这样,我们就可以清楚的认识到,辅助索引的执行策略,它并非是索引自己的记录,而是直接索引他对应的主键id

index-record-offsets

[shell]# innodb_space -s ibdata1 -T db/test -I PRIMARY index-record-offsets    page_offset         record_offset    5                   9478    5                   9512    5                   9580    5                   9614    5                   9648    ...    256                 4158    256                 4192    256                 4226    256                 4260index-record-offsets在这里同样做了一次全表索引递归,但它记录的却是页与记录在该页的偏移量

index-level-summary

[shell]]# innodb_space -s ibdata1 -T db/test -I PRIMARY -l 0 index-level-summary    page    index   level   data    free    records min_key     5       1198    0       11381   4749    336     id=2    7       1198    0       14802   1252    437     id=664    9       1198    0       14913   1135    440     id=1549    ...    192     1198    0       14972   1104    442     id=20783868    256     1198    0       14952   1116    441     id=20784310这里的"-l"参数需要指定要显示的页的级别,也就是我们说的深度

3.4 Record Structure

record-dump

[shell]# innodb_space -s ibdata1 -T db/test -p 3 -R 111 record-dump Record at offset 111Header:  Next record offset  : 2927  Heap number         : 160  Type                : conventional  Deleted             : false  Length              : 5Key fields:  id: -2139916944Child page number: 1919249781

这里我们通过指定数据纪录的偏移量”-R”,可以将该记录的详细信息打印出来.

3.5 Record History

record-history

[shell]# innodb_space -s ibdata1 -T db/test -p 7 -R 111 record-history    Transaction   Type                Undo record    (n/a)                             (id=-1583871008) → ()record-history为给定记录的undo记录

undo-history-summary

[shell]# innodb_space -s ibdata1 undo-history-summary    Page    Offset  Transaction   Type                Table    405     7206    177636        delete              mysql/innodb_table_stats    405     7269    177686        update_deleted      mysql/innodb_table_stats    501     12011   177638        delete              mysql/innodb_index_stats    501     12118   177688        update_deleted      mysql/innodb_index_stats    ...    列出所有变动过的记录

undo-record-dump

[shell]# innodb_space -s ibdata1 -p 405 -R 7206 undo-record-dump    Undo record at offset 7206    Header:      Previous record offset   : 0      Next record offset       : 7269      Type                     : delete    System fields:      Transaction ID: 177636      Roll Pointer:        Undo Log: page 383, offset 1673        Rollback Segment ID: 125    Key fields:      database_name: "lepus"      table_name: "mysql_status"    Non-key fields:显示指定记录的undo信息

以上就是innodb_space的主要参数与使用.谢谢!!

参考资料: https://github.com/jeremycole/innodb_ruby/wiki#page-dump

1 0