innodb_ruby 分析 Innodb 引擎数据结构

本贴最后更新于 1785 天前,其中的信息可能已经时异事殊

06.png

图片来源: https://toutiao.io/posts/nxgjik/preview

Install And Run

sudo gem install innodb_ruby

innodb_space --h

Basics of using innodb_space from the command line
There are two ways to start innodb_space.

Against a single space file (ibdata or .ibd):
-f = tablespace file name (system or table)

Against a system tablespace which will auto­load file­per­table tablespace files:
-s = system tablespace file name
-T = table name
-I = index name

space-indexes

表索引列表 可以看出每一个 B+ Tree 索引分成内节点和叶子节点两个 “段”

  • innodb_space -s ibdata1 -T plat/sys_dict space-indexes

     id      name                root    fseg        used    allocated   fill_factor 
     429     PRIMARY             3       internal    1       1           100.00%     
     429     PRIMARY             3       leaf        0       0           0.00%       
     430     sys_dict_value      4       internal    1       1           100.00%     
     430     sys_dict_value      4       leaf        0       0           0.00%       
     431     sys_dict_label      5       internal    1       1           100.00%     
     431     sys_dict_label      5       leaf        0       0           0.00%       
     432     sys_dict_del_flag   6       internal    1       1           100.00%     
     432     sys_dict_del_flag   6       leaf        0       0           0.00%  
    

index-recurse

很容易看出 聚簇索引和二级索引的数据结构

  • innodb_space -s ibdata1 -T plat/sys_dict -I sys_dict_label index-recurse

     RECORD: (label="datetime") → (id="876ba46701b9449799589b42b34fda6c")
     RECORD: (label="decimal") → (id="cfeeb3a8be494a44a17f36f678f1187f")
     RECORD: (label="Double") → (id="99")
     RECORD: (label="double") → (id="a10a7a87ee094dbba27f375ed944254f")
     RECORD: (label="float") → (id="cd5e612f36fa4ffb9a42cc3819f764ab")
     RECORD: (label="int") → (id="321ac9f242ed4d7694f4a42abbb3c10d")
     RECORD: (label="integer") → (id="2f13e80d5dd843fea52c54f72c8845c6")
     RECORD: (label="Integer") → (id="98")
     RECORD: (label="Left Like") → (id="82")
     RECORD: (label="Like") → (id="81")
     RECORD: (label="Long") → (id="94")
    
  • innodb_space -s ibdata1 -T plat/sys_dict -I PRIMARY index-recurse

       RECORD: (id="cd5e612f36fa4ffb9a42cc3819f764ab") → (value="float", label="float", type="column_type_mysql", description="jdbcType", sort="\x80\x00\x00\x00F", parent_id="0", create_by="1", create_date="184692543-30-93 85:56:49", update_by="\xA4", update_date="579618550-12-78 99:46:89", remarks="", del_flag="\b")
       RECORD: (id="cfeeb3a8be494a44a17f36f678f1187f") → (value="decimal", label="decimal", type="column_type_mysql", description="jdbcType", sort="\x80\x00\x00\x00<", parent_id="0", create_by="1", create_date="184692543-32-78 40:50:25", update_by="\xA4", update_date="579393370-14-65 30:94:41", remarks="", del_flag="\x0F")
       RECORD: (id="d3ba328b22654f9f888eb8272571c23f") → (value="LONG", label="LONG", type="column_type_oracle", description="Oracle jdbcType", sort="\x80\x00\x00\x00(", parent_id="0", create_by="1", create_date="184693853-00-09 42:22:41", update_by="\xA4", update_date="569006943-50-59 07:73:77", remarks="", del_flag="\f")
       RECORD: (id="e01f916fb4e04fc68163c502f05800e5") → (value="0209", label="\xE5\xB7\xB2\xE5\x90\x88\xE5\xB9\xB6", type="ajzt", description="\xE6\xA1\x88\xE4\xBB\xB6\xE7\x8A\xB6\xE6\x80\x81", sort="\x80\x00\x00\x00P", parent_id="0", create_by="1", create_date="184694512-32-87 53:80:81", update_by="\xAA", update_date="562561166-53-92 33:71:53", remarks="", del_flag="\b")
       RECORD: (id="e25cc014c4ab4b8e8389af2d60a702bf") → (value="tinyint", label="tinyint", type="column_type_mysql", description="jdbcType", sort="\x80\x00\x00\x00\xBE", parent_id="0", create_by="1", create_date="184692543-19-69 78:21:77", update_by="\xA4", update_date="582377004-89-96 63:89:77", remarks="", del_flag="\x16")
    

space-page-type-regions

索引数据页的页类型,可以看出从第四页开始,才是记录数据页

  • innodb_space -s ibdata1 -T plat/sys_dict 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           8           2           FREE (ALLOCATED)  
    

space-page-type-summary

  • innodb_space -s ibdata1 -T plat/sys_log space-page-type-summary

     type            count       percent     description         
     INDEX           1087        58.57       B+Tree index        
     ALLOCATED       679         36.58       Freshly allocated   
     BLOB            87          4.69        Uncompressed BLOB   
     INODE           1           0.05        File segment inode  
     IBUF_BITMAP     1           0.05        Insert buffer bitmap
     FSP_HDR         1           0.05        File space header
    

page-account

  • innodb_space -s ibdata1 -T plat/sys_log -p 0 page-account

     Accounting for page 0: 
       Page type is FSP_HDR (File space header, header page (page 0) for each tablespace file).
       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 full_frag list of space.
    
     Accounting for page 1:
       Page type is IBUF_BITMAP (Insert buffer bitmap, bookkeeping for insert buffer writes to be merged).
       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 full_frag list of space.
    
     Accounting for page 2:
       Page type is INODE (File segment inode, bookkeeping for file segments).
       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 full_frag list of space.
    
     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 full_frag list of space.
       Page is in fragment array of fseg 1.
       Fseg is in internal fseg of index 267.
       Index root is page 3.
       Index is plat/sys_log.PRIMARY.
    

page-dump

页的详细信息

  • innodb_space -s ibdata1 -T plat/sys_dict -p 3 page-dump

     fil header:
     {:checksum=>4020280542,
      :offset=>3,
      :prev=>nil,
      :next=>nil,
      :lsn=>115919729,
      :type=>:INDEX,
      :flush_lsn=>0,
      :space_id=>181}
    
     fil trailer:
     {:checksum=>4020280542, :lsn_low32=>115919729}
    
     page header:
     {:n_dir_slots=>35,
      :heap_top=>14248,
      :garbage_offset=>0,
      :garbage_size=>0,
      :last_insert_offset=>14143,
      :direction=>:right,
      :n_direction=>136,
      :n_recs=>137,
      :max_trx_id=>0,
      :level=>0,
      :index_id=>429,
      :n_heap=>139,
      :format=>:compact}
    
     fseg header:
     {:leaf=>
       <Innodb::Inode space=<Innodb::Space file="plat/sys_dict.ibd", page_size=16384, pages=9>, fseg=2>,
      :internal=>
       <Innodb::Inode space=<Innodb::Space file="plat/sys_dict.ibd", page_size=16384, pages=9>, fseg=1>}
    
     sizes:
       header           120
       trailer            8
       directory         70
       free            2058
       used           14326
       record         14128
       per record     103.00
    
     # 页目录
     page directory:
     [
      99,447,913,1270,1713,2035,2404,2807,3219,3611,3997,4380,4771,5185,
      5586,6027,6450,6816,7193,7627,7950,8395,8787,9171,9626,10082,10528,
      10912, 11211,11678,12123,12611,13075,13543,112
     ]
    
     system records:
     ## 标识的最小记录和最大记录
     {:offset=>99,
      :header=>
       {:next=>136,
        :type=>:infimum,
        :heap_number=>0,
        :n_owned=>1,
        :min_rec=>false,
        :deleted=>false,
        :length=>5},
      :next=>136,
      :data=>"infimum\x00",
      :length=>8}
     {:offset=>112,
      :header=>
       {:next=>112,
        :type=>:supremum,
        :heap_number=>1,
        :n_owned=>6,
        :min_rec=>false,
        :deleted=>false,
        :length=>5},
      :next=>112,
      :data=>"supremum",
      :length=>8}
    
     garbage records:
    
     records:
     # 第三页第一条记录 :infimum:next=>136
     {:format=>:compact,
      :offset=>136,
      :header=> # 记录头信息
       {:next=>257,
        :type=>:conventional,
        :heap_number=>2,
        :n_owned=>0, # 表示当前记录拥有的记录数,4个为一组
        :min_rec=>false,
        :deleted=>false,
        :nulls=>[],
        :lengths=>
         {"id"=>32,
          "value"=>4,
          "label"=>4,
          "type"=>18,
          "description"=>15,
          "parent_id"=>1,
          "create_by"=>1,
          "update_by"=>1,
          "remarks"=>0,
          "del_flag"=>1},
        :externs=>[],
        :length=>16},
      :next=>257, # 示下一条记录的相对位置
      :type=>:clustered,
      :key=>
       [{:name=>"id",
         :type=>"VARCHAR(256)",
         :value=>"05c63590cab94402a109002eff6966ad"}],
      :row=> # 记录的真实数据
       [{:name=>"value", :type=>"VARCHAR(400)", :value=>"DATE"},
        {:name=>"label", :type=>"VARCHAR(400)", :value=>"DATE"},
        {:name=>"type", :type=>"VARCHAR(400)", :value=>"column_type_oracle"},
        {:name=>"description", :type=>"VARCHAR(400)", :value=>"Oracle jdbcType"},
        {:name=>"sort", :type=>"CHAR(5)", :value=>"\x80\x00\x00\x00\x19"},
        {:name=>"parent_id", :type=>"VARCHAR(256)", :value=>"0"},
        {:name=>"create_by", :type=>"VARCHAR(256)", :value=>"1"},
        {:name=>"create_date",:type=>"DATETIME",:value=>"184693853-58-98 22:50:57"},
        {:name=>"update_by", :type=>"VARCHAR(256)", :value=>"\xA4"},
        {:name=>"update_date",:type=>"DATETIME",:value=>"577197865-32-81 87:82:73"},
        {:name=>"remarks", :type=>"VARCHAR(1020)", :value=>""},
        {:name=>"del_flag", :type=>"VARCHAR(4)", :value=>"\x0F"}],
      :sys=>
       [{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>24347},
        {:name=>"DB_ROLL_PTR",
         :type=>"ROLL_PTR",
         :value=>
          {:is_insert=>true, :rseg_id=>47, :undo_log=>{:page=>291, :offset=>272}}}],
      :length=>111,
      :transaction_id=>24347,  # 事务ID
      :roll_pointer=> # 回滚指针
       {:is_insert=>true, :rseg_id=>47, :undo_log=>{:page=>291, :offset=>272}}}
    
     ......省略
     # 第三页最后一天记录 next=>112
     {:format=>:compact,
      :offset=>14143,
      :header=>
       {:next=>112,
        :type=>:conventional,
        :heap_number=>138,
        :n_owned=>0,
        :min_rec=>false,
        :deleted=>false,
        :nulls=>[],
        :lengths=>
         {"id"=>32,
          "value"=>8,
          "label"=>8,
          "type"=>17,
          "description"=>8,
          "parent_id"=>1,
          "create_by"=>1,
          "update_by"=>1,
          "remarks"=>0,
          "del_flag"=>1},
        :externs=>[],
        :length=>16},
      :next=>112,
      :type=>:clustered,
      :key=>
       [{:name=>"id",
         :type=>"VARCHAR(256)",
         :value=>"fe9dda8abee2460a86f67da4bc0cd192"}],
      :row=>
       [{:name=>"value", :type=>"VARCHAR(400)", :value=>"longblob"},
        {:name=>"label", :type=>"VARCHAR(400)", :value=>"longblob"},
        {:name=>"type", :type=>"VARCHAR(400)", :value=>"column_type_mysql"},
        {:name=>"description", :type=>"VARCHAR(400)", :value=>"jdbcType"},
        {:name=>"sort", :type=>"CHAR(5)", :value=>"\x80\x00\x00\x00\x96"},
        {:name=>"parent_id", :type=>"VARCHAR(256)", :value=>"0"},
        {:name=>"create_by", :type=>"VARCHAR(256)", :value=>"1"},
        {:name=>"create_date",
         :type=>"DATETIME",
         :value=>"184692543-46-03 80:50:89"},
        {:name=>"update_by", :type=>"VARCHAR(256)", :value=>"\xA4"},
        {:name=>"update_date",
         :type=>"DATETIME",
         :value=>"581560727-03-76 99:68:64"},
        {:name=>"remarks", :type=>"VARCHAR(1020)", :value=>""},
        {:name=>"del_flag", :type=>"VARCHAR(4)", :value=>"\x00"}],
      :sys=>
       [{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>24347},
        {:name=>"DB_ROLL_PTR",
         :type=>"ROLL_PTR",
         :value=>
          {:is_insert=>true,
           :rseg_id=>47,
           :undo_log=>{:page=>291, :offset=>3785}}}],
      :length=>111,
      :transaction_id=>24347,
      :roll_pointer=>
       {:is_insert=>true, :rseg_id=>47, :undo_log=>{:page=>291, :offset=>3785}}}
    

page-directory-summary

  • innodb_space -s ibdata1 -T plat/sys_dict -p 3 page-directory-summary

     slot    offset  type          owned   key
     0       99      infimum       1       
     1       447     conventional  4       (id="100")
     2       913     conventional  4       (id="104")
     3       1270    conventional  4       (id="187a6de0e3944a88bf9dfab6373c2a7d")
     4       1713    conventional  4       (id="2")
     5       2035    conventional  4       (id="23")
     6       2404    conventional  4       (id="26")
     7       2807    conventional  4       (id="29")
     8       3219    conventional  4       (id="2d3e25153f3048edb27c440e9945bd63")
     9       3611    conventional  4       (id="31")
     10      3997    conventional  4       (id="34")
     11      4380    conventional  4       (id="37c5564517474d9d8518febc7661f1a5")
     12      4771    conventional  4       (id="3b15ca59c4ea4d92a95e409b8da6bbe8")
     13      5185    conventional  4       (id="41")
     14      5586    conventional  4       (id="5")
     15      6027    conventional  4       (id="5fa34d2a6243418a9161c2f25a249451")
     16      6450    conventional  4       (id="67")
     17      6816    conventional  4       (id="7011fdb515e5489ebda516acae1f65a7")
     18      7193    conventional  4       (id="735b51cd9c3144a7ac3fac5c60416905")
     19      7627    conventional  4       (id="76")
     20      7950    conventional  4       (id="7b3bc1f2c82a494f870dcd0ba9f28251")
     21      8395    conventional  4       (id="81")
     22      8787    conventional  4       (id="84")
     23      9171    conventional  4       (id="876ba46701b9449799589b42b34fda6c")
     24      9626    conventional  4       (id="8b52e1c3f2784b4384b11bd8087335d0")
     25      10082   conventional  4       (id="91")
     26      10528   conventional  4       (id="924b57d4e40e4b0f9eb5b2269aca5eb4")
     27      10912   conventional  4       (id="96")
     28      11211   conventional  4       (id="9ae9b4d54f054ec2b9bf9a619751be56")
     29      11678   conventional  4       (id="b85b4b9dcccd43c7afb9df1589952a68")
     30      12123   conventional  4       (id="be04fd586e984ee49c5c1830f8ee3e84")
     31      12611   conventional  4       (id="c789b942d9b84ba8a131d573c22ab98d")
     32      13075   conventional  4       (id="e01f916fb4e04fc68163c502f05800e5")
     33      13543   conventional  4       (id="ec3bd58eedeb4b16acd648b130cb0f33")
     34      112     supremum      6       
    

page-records

  • innodb_space -s ibdata1 -T plat/sys_dict -p 3 page-records

     Record 136: (id="05c63590cab94402a109002eff6966ad") → (value="DATE", label="DATE", type="column_type_oracle", description="Oracle jdbcType", sort="\x80\x00\x00\x00\x19", parent_id="0", create_by="1", create_date="184693853-58-98 22:50:57", update_by="\xA4", update_date="577197865-32-81 87:82:73", remarks="", del_flag="\x0F")
    
     ......省略
    
  • innodb_space -s ibdata1 -T plat/sys_dict -p 3 -R 136 record-dump

     Record at offset 136
     Header:
       Next record offset  : 257
       Heap number         : 2
       Type                : conventional
       Deleted             : false
       Length              : 16
    
     System fields:
       Transaction ID: 24347
       Roll Pointer:
         Undo Log: page 291, offset 272
         Rollback Segment ID: 47
         Insert: true
    
     Key fields:
       id: "05c63590cab94402a109002eff6966ad"
    
     Non-key fields:
       value: "DATE"
       label: "DATE"
       type: "column_type_oracle"
       description: "Oracle jdbcType"
       sort: "\x80\x00\x00\x00\x19"
       parent_id: "0"
       create_by: "1"
       create_date: "184693853-58-98 22:50:57"
       update_by: "\xA4"
       update_date: "577197865-32-81 87:82:73"
       remarks: ""
       del_flag: "\x0F"
    

space-extents-illustrate

  • innodb_space -s ibdata1 -T plat/sys_log space-extents-illustrate
    spaceextentsillustrate.png

page-illustrate

  • innodb_space -s ibdata1 -T plat/sys_dict -p 3 page-illustrate
    03.png
    04.png

space-extents

  • innodb_space -s ibdata1 -T plat/sys_log space-extents

     start_page  page_used_bitmap                                                
     0           ################################################################
     64          ################################################################
     128         ################################################################
     192         ################################################################
     256         ################################################################
     320         ################################################################
     384         ################################################################
     448         ################################################################
     512         ################################################################
     576         ################################################################
     640         ################################################################
     704         ####............................................................
     768         #...............................................................
     832         ############################################.###################
     896         ###############################################.................
     960         ################################################################
     1024        ################################................................
     1088        #############.##############################....................
     1152        ###############################################.................
     1216        #####################################################...........
     1280        ................................................................
     1344        ................................................................
    

space-lists

  • innodb_space -s ibdata1 -T plat/sys_log space-lists

     name                length      f_page      f_offset    l_page      l_offset    
     free                2           0           958         0           998         
     free_frag           2           0           838         0           678         
     full_frag           1           0           158         0           158         
     full_inodes         0           0           0           0           0           
     free_inodes         1           2           38          2           38 
    

space-inodes-summary

  • innodb_space -s ibdata1 -T plat/sys_log space-inodes-summary

     INODE fseg_id=1, pages=3, frag=3, full=0, not_full=0, free=0
     INODE fseg_id=2, pages=800, frag=32, full=10, not_full=2, free=0
     INODE fseg_id=3, pages=1, frag=1, full=0, not_full=0, free=0
     INODE fseg_id=4, pages=96, frag=32, full=0, not_full=1, free=0
     INODE fseg_id=5, pages=1, frag=1, full=0, not_full=0, free=0
     INODE fseg_id=6, pages=160, frag=32, full=1, not_full=1, free=0
     INODE fseg_id=7, pages=1, frag=1, full=0, not_full=0, free=0
     INODE fseg_id=8, pages=96, frag=32, full=0, not_full=1, free=0
     INODE fseg_id=9, pages=1, frag=1, full=0, not_full=0, free=0
     INODE fseg_id=10, pages=96, frag=32, full=0, not_full=1, free=0
    

space-extents-illustrate

  • innodb_space -s ibdata1 -T plat/sys_log space-extents-illustrate
    05.png
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    673 引用 • 535 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...