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 autoload filepertable 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
page-illustrate
- innodb_space -s ibdata1 -T plat/sys_dict -p 3 page-illustrate
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
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于