深入理解mysql----innodb_ruby工具

栏目: Ruby · 发布时间: 4年前

内容简介：innodb_ruby 主要可查看innodb数据库数据表的各种存储，解析innodb的文件，用于学习数据库底层的一些存储。

github使用教程

innodb_ruby 主要可查看innodb数据库数据表的各种存储，解析innodb的文件，用于学习数据库底层的一些存储。

安装过程

sudo gem install innodb_ruby

gem install --user-install innodb_ruby

innodb_space --help # 测试一下可以显示一堆帮助不

try see see

1. 查看你数据库的存储文件在哪里

mysql> show global variables like "%datadir%";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)
复制代码

2. 进入数据库目录

因为之后的很多命令需要填写数据表文件路径，每次都要写好长，我觉得浪费时间，因此先进入了数据库目录。你可以忽略这步，在之后需要填写路径的时候，填写绝对路径就ok了。

cd /var/lib/mysql/
复制代码

3. 准备好

重新建个数据库吧

mysql -uroot -p
复制代码

mysql> create database test
复制代码

mysql> use test
复制代码

4. 各种命令各种有

选项

-f 加载表空间，如ibd文件
-s 加载系统表空间，如ibd
-T 指定表名
-I 指定索引名
复制代码

先创建一个简单的表

mysql> CREATE TABLE innodb_ruby(
    c1 INT,
    c2 INT,
    c3 VARCHAR(10000),
    PRIMARY KEY (c1), 
    key idx(c2)
) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.36 sec)
复制代码

插入数据

mysql> INSERT INTO innodb_ruby VALUES(1, 1, repeat('a', 7000)), (2, 2, repeat('b', 7000)), (3, 3, repeat('c', 7000)), (4, 4, repeat('d', 7000));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
复制代码

系统文件结构

列出所有物理对象的数量。

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 system-spaces
name                            pages       indexes     
(system)                        8832        204
test/innodb_ruby                              2
...
复制代码

列出表空间中所有索引统计信息（系统空间或每个文件表空间）

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-indexes
id          name                            root        fseg        used        allocated   fill_factor 
94684       PRIMARY                         3           internal    1           1           100.00%     
94684       PRIMARY                         3           leaf        3           3           100.00%     
94685       idx                             4           internal    1           1           100.00%     
94685       idx                             4           leaf        0           0           0.00%  
复制代码

id：此索引的id

name：索引名称。PRIMARY代表的就是聚集索引，因为InnoDB表是聚集索引组织表，行记录就是聚集索引；idx就是辅助索引的名称。

root：索引中根节点的page号。

fseg：page类型。internal非叶子节点；leaf叶子节点

used：索引使用的page页。

allocated：索引分配的page页。

fill_factor：索引使用的百分比

统计每个类型的页共占用了多少页

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-page-type-regions
start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3           7           5           INDEX               
8           8           1           FREE (ALLOCATED)
复制代码

打印每个类型总计页数

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-page-type-summary
type                count       percent     description         
INDEX               5           55.56       B+Tree index        
FSP_HDR             1           11.11       File space header   
IBUF_BITMAP         1           11.11       Insert buffer bitmap
INODE               1           11.11       File segment inode  
ALLOCATED           1           11.11       Freshly allocated
复制代码

统计所有的页在表空间的饱和度信息，每个页面显示彩色块（按index/purpose着色），根据页面中的数据量调整大小。

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-extents-illustrate
复制代码

统计所有的页在表空间的饱和度信息，每个页面显示彩色块 (按页面修改LSN的年龄着色)。

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby space-extents-illustrate
复制代码

页文件结构

解释单个页面用途

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -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 94684.
  Index root is page 3.
  Index is test/innodb_ruby.PRIMARY.
复制代码

打印页结构信息

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 3 page-dump
#<Innodb::Page::Index:0x0000000000ef65a0>:

fil header:
{:checksum=>3506321879,
 :offset=>3,
 :prev=>nil,
 :next=>nil,
 :lsn=>25811855781,
 :type=>:INDEX,
 :flush_lsn=>0,
 :space_id=>2456}

fil trailer:
{:checksum=>1027768186, :lsn_low32=>42052005}

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=>94684,
 :n_heap=>5,
 :format=>:compact}

fseg header:
{:leaf=>
  <Innodb::Inode space=<Innodb::Space file="test/innodb_ruby.ibd", page_size=16384, pages=9>, fseg=2>,
 :internal=>
  <Innodb::Inode space=<Innodb::Space file="test/innodb_ruby.ibd", page_size=16384, pages=9>, 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,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>140,
 :type=>:clustered,
 :key=>[{:name=>"c1", :type=>"INT", :value=>1}],
 :row=>[],
 :sys=>[],
 :child_page_number=>5,
 :length=>8}

{:format=>:compact,
 :offset=>140,
 :header=>
  {:next=>154,
   :type=>:node_pointer,
   :heap_number=>3,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>154,
 :type=>:clustered,
 :key=>[{:name=>"c1", :type=>"INT", :value=>2}],
 :row=>[],
 :sys=>[],
 :child_page_number=>6,
 :length=>8}

{:format=>:compact,
 :offset=>154,
 :header=>
  {:next=>112,
   :type=>:node_pointer,
   :heap_number=>4,
   :n_owned=>0,
   :min_rec=>false,
   :deleted=>false,
   :nulls=>[],
   :lengths=>{},
   :externs=>[],
   :length=>5},
 :next=>112,
 :type=>:clustered,
 :key=>[{:name=>"c1", :type=>"INT", :value=>4}],
 :row=>[],
 :sys=>[],
 :child_page_number=>7,
 :length=>8}
复制代码

汇总页面记录

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 3 page-records
Record 126: (c1=1) → #5
Record 140: (c1=2) → #6
Record 154: (c1=4) → #7
复制代码

每行代表一页数据，c1=1代表c1的最小值为1，#5代表是第5页。

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 7 page-directory-summary
slot    offset  type          owned   key
0       99      infimum       1       
1       112     supremum      5       
复制代码

数据太小了，再插点数据吧

mysql> insert into innodb_ruby values(5,500,'eeee'), (6,600,'ffff'), (7, 700, 'gggg'), (8, 800, 'hhhh'), (9, 900, 'iiii'), (10, 1000, 'jjjj'), (11, 1100, 'kkkk'), (12, 1200, 'llll'), (13, 1300, 'mmmm'), (14, 1400, 'nnnn'), (15, 1500, 'oooo'), (16, 1600, 'pppp');
复制代码

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 7 page-directory-summary
slot    offset  type          owned   key
0       99      infimum       1       
1       7220    conventional  4       (c1=7)
2       7348    conventional  4       (c1=11)
3       112     supremum      6
复制代码

infimum最小值，supremum最大值，1~8 其他数据为4~8

详细说明一个页的内容

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 7 page-illustrate
复制代码

索引结构

遍历整个B+树扫描索引

主键索引

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY index-recurse
ROOT NODE #3: 3 records, 42 bytes
  NODE POINTER RECORD ≥ (c1=1) → #5
  LEAF NODE #5: 1 records, 7029 bytes
    RECORD: (c1=1) → (c2=1, c3="aaa...")
  NODE POINTER RECORD ≥ (c1=2) → #6
  LEAF NODE #6: 2 records, 14058 bytes
    RECORD: (c1=2) → (c2=2, c3="bbb...")
    RECORD: (c1=3) → (c2=3, c3="ccc...")
  NODE POINTER RECORD ≥ (c1=4) → #7
  LEAF NODE #7: 13 records, 7413 bytes
    RECORD: (c1=4) → (c2=4, c3="ddd...")
    RECORD: (c1=5) → (c2=500, c3="eeee")
    RECORD: (c1=6) → (c2=600, c3="ffff")
    RECORD: (c1=7) → (c2=700, c3="gggg")
    RECORD: (c1=8) → (c2=800, c3="hhhh")
    RECORD: (c1=9) → (c2=900, c3="iiii")
    RECORD: (c1=10) → (c2=1000, c3="jjjj")
    RECORD: (c1=11) → (c2=1100, c3="kkkk")
    RECORD: (c1=12) → (c2=1200, c3="llll")
    RECORD: (c1=13) → (c2=1300, c3="mmmm")
    RECORD: (c1=14) → (c2=1400, c3="nnnn")
    RECORD: (c1=15) → (c2=1500, c3="oooo")
    RECORD: (c1=16) → (c2=1600, c3="pppp")
复制代码

普通索引

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I idx index-recurse
ROOT NODE #4: 16 records, 224 bytes
  RECORD: (c2=1) → (c1=1)
  RECORD: (c2=2) → (c1=2)
  RECORD: (c2=3) → (c1=3)
  RECORD: (c2=4) → (c1=4)
  RECORD: (c2=500) → (c1=5)
  RECORD: (c2=600) → (c1=6)
  RECORD: (c2=700) → (c1=7)
  RECORD: (c2=800) → (c1=8)
  RECORD: (c2=900) → (c1=9)
  RECORD: (c2=1000) → (c1=10)
  RECORD: (c2=1100) → (c1=11)
  RECORD: (c2=1200) → (c1=12)
  RECORD: (c2=1300) → (c1=13)
  RECORD: (c2=1400) → (c1=14)
  RECORD: (c2=1500) → (c1=15)
  RECORD: (c2=1600) → (c1=16)
复制代码

可看出普通索引其实是直接指向了主键索引的

在索引页中打印每条记录的偏移量

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY index-record-offsets
page_offset         record_offset       
5                   128                 
6                   128                 
6                   7157                
7                   128                 
7                   7156                
7                   7188                
7                   7220                
7                   7252                
7                   7284                
7                   7316                
7                   7348                
7                   7380                
7                   7412                
7                   7444                
7                   7476                
7                   7508  
复制代码

打印给定级别所有索引页面的摘要信息

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY -l 0 index-level-summary
page    index   level   data    free    records min_key 
5       94684   0       7029    9223    1       c1=1
6       94684   0       14058   2194    2       c1=2
7       94684   0       7413    8835    13      c1=4
root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -I PRIMARY -l 1 index-level-summary
page    index   level   data    free    records min_key 
3       94684   1       42      16210   3       c1=1
复制代码

记录结构

给定记录偏移量，打印记录的详细说明及包含的数据

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 5 -R 128 record-dump
Record at offset 128

Header:
  Next record offset  : 112
  Heap number         : 2
  Type                : conventional
  Deleted             : false
  Length              : 8

System fields:
  Transaction ID: 2860579
  Roll Pointer:
    Undo Log: page 897, offset 272
    Rollback Segment ID: 34
    Insert: true

Key fields:
  c1: 1

Non-key fields:
  c2: 1
  c3: "aaaa..."
复制代码

记录历史

显示给定偏移量的undo日志

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 -T test/innodb_ruby -p 5 -R 128 record-history
Transaction   Type                Undo record
(n/a)         insert              (c1=1) → ()
复制代码

显示所有变动过的undo记录

root@localhost:/var/lib/mysql$ innodb_space -s ibdata1 undo-history-summary
Page    Offset  Transaction   Type                Table
572     10435   2848487       delete              
...

复制代码

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

Spark SQL内核剖析

朱锋、张韶全、黄明 / 电子工业出版社 / 2018-8 / 69.00元

Spark SQL 是 Spark 技术体系中较有影响力的应用（Killer application），也是 SQL-on-Hadoop 解决方案中举足轻重的产品。《Spark SQL内核剖析》由 11 章构成，从源码层面深入介绍 Spark SQL 内部实现机制，以及在实际业务场景中的开发实践，其中包括 SQL 编译实现、逻辑计划的生成与优化、物理计划的生成与优化、Aggregation 算......一起来看看《Spark SQL内核剖析》这本书的介绍吧!

码农工具