用工具分析MySQL存储文件

准备工作

工具安装

更新 gcc

1
2
3
4
5


sudo yum install centos-release-scl
sudo yum install devtoolset-11-gcc*
scl enable devtoolset-11 bash
which gcc
gcc --version

安装 ruby ，去【官网】下载
使用的版本为：

1

ruby 3.1.3p185 (2022-11-24 revision 1a6b16756e) [x86_64-linux]

安装 innodb_ruby ，安装需要连接外网

1

gem install innodb_ruby

安装完后检查

1

innodb_space --help

准备数据

脚本如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


--Create Database
CREATE DATABASE test_innodb_ruby;

--Create Table
USE test_innodb_ruby;

CREATE TABLE `words` (    
    `id` INT(11) NOT NULL AUTO_INCREMENT,    
	`word` VARCHAR(64) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB;

--Create Procedure
DELIMITER ;;
CREATE PROCEDURE idata()BEGIN    
    DECLARE i INT;    
    SET i=0;    
    WHILE i<100000 DO        
        INSERT INTO words(word) VALUES(SUBSTRING(MD5(RAND()),1,5));        
        SET i=i+1;    
    END WHILE;END;;
DELIMITER ;

--Call Procedure
CALL idata();

--Query Data
SELECT COUNT(*) FROM test_innodb_ruby.words;
SELECT * FROM test_innodb_ruby.words LIMIT 20;

获取系统表空间 ibdata1，以及 words.ibd

分析

系统表空间统计

查看表空间分布

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


innodb_space -s ibdata1 system-spaces

name                            pages       indexes     
(system)                        768         7           
test_innodb_ruby/words          704         1           
mysql/engine_cost                           (orphan/tmp)
mysql/gtid_executed                         (orphan/tmp)
mysql/help_category                         (orphan/tmp)
mysql/help_keyword                          (orphan/tmp)
mysql/help_relation                         (orphan/tmp)
mysql/help_topic                            (orphan/tmp)
mysql/innodb_index_stats                    (orphan/tmp)
mysql/innodb_table_stats                    (orphan/tmp)
mysql/plugin                                (orphan/tmp)
mysql/server_cost                           (orphan/tmp)
mysql/servers                               (orphan/tmp)
mysql/slave_master_info                     (orphan/tmp)
mysql/slave_relay_log_info                  (orphan/tmp)
mysql/slave_worker_info                     (orphan/tmp)
mysql/time_zone                             (orphan/tmp)
mysql/time_zone_leap_second                 (orphan/tmp)
mysql/time_zone_name                        (orphan/tmp)
mysql/time_zone_transition                  (orphan/tmp)
mysql/time_zone_transition_type             (orphan/tmp)
sys/sys_config                              (orphan/tmp)

系统表空间所有类型page统计，会很多

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28


innodb_space -s ibdata1 space-summary | vim -

page        type                prev        next        lsn
0           FSP_HDR             0           0           11510020
1           IBUF_BITMAP         0           0           1199510
2           INODE               0           0           2773491
3           SYS                 0           0           11196
4           INDEX               0           0           11196
5           TRX_SYS             0           0           12400970
6           SYS                 0           0           1196046
7           SYS                 0           0           2756260
8           INDEX               0           0           2755837
9           INDEX               0           0           2755895
10          INDEX               0           0           2756077
11          INDEX               0           0           2759341
12          INDEX               0           0           2756412
13          ALLOCATED           0           0           46663
14          ALLOCATED           0           0           46663
。。。。。。
45          SYS                 0           0           1203535
46          SYS                 0           0           1207944
47          SYS                 0           0           1211249
。。。。。。
64          UNDO_LOG            0           0           12419803
65          UNDO_LOG            0           0           12419881
66          SYS                 0           0           12419803
67          SYS                 0           0           12419881
68          UNDO_LOG            0           0           12420288

表空间page汇总

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


innodb_space -s ibdata1 space-page-type-summary

type                count       percent     description         
ALLOCATED           418         54.43       Freshly allocated   
UNDO_LOG            125         16.28       Undo log            
SYS                 113         14.71       System internal     
INDEX               104         13.54       B+Tree index        
INODE               3           0.39        File segment inode  
FSP_HDR             2           0.26        File space header   
TRX_SYS             2           0.26        Transaction system header
IBUF_BITMAP         1           0.13        Insert buffer bitmap

系统表空间链表统计

1
2
3
4
5
6
7
8


innodb_space -s ibdata1  space-extents 

name                length      f_page      f_offset    l_page      l_offset    
free                2           0           438         0           478         
free_frag           1           0           398         0           398         
full_frag           4           0           158         0           358         
full_inodes         2           2           38          243         38          
free_inodes         1           322         38          322         38 

系统表空间区信息统计

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


innodb_space -s ibdata1  space-extents 

start_page  page_used_bitmap                                                
0           ################################################################
64          ################################################################
128         ################################################################
192         ################################################################
256         ################################################################
320         ################################################################
384         ######..........................................................
448         ................................................................
512         ................................................................

更详细的图表展示：
innodb_space -s ibdata1 space-extents-illustrate

表空间索引统计，也是会有很多

1
2
3
4
5
6
7
8
9


innodb_space -f test_innodb_ruby/words.ibd space-index-pages-summary | vim -

page        index   level   data    free    records
3           41      1       2730    13428   195
4           41      0       7482    8642    258
5           41      0       14964   1032    516
6           41      0       14964   1032    516
7           41      0       14964   1032    516
。。。。。。

4个核心的SYS表

SYS_TABLES表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


innodb_space -s ibdata1 data-dictionary-tables

name                            id          n_cols      type        mix_id      mix_len     cluster_name   space       
SYS_DATAFILES                   14          2           1           0           64                         0           
SYS_FOREIGN                     11          4           1           0           64                         0           
SYS_FOREIGN_COLS                12          4           1           0           64                         0           
SYS_TABLESPACES                 13          3           1           0           64                         0           
SYS_VIRTUAL                     15          3           1           0           64                         0           
mysql/engine_cost               34          2147483654  33          0           80                         20          
mysql/gtid_executed             32          2147483651  33          0           80                         18          
mysql/help_category             36          2147483652  33          0           80                         5           
mysql/help_keyword              37          2147483650  33          0           80                         7           
mysql/help_relation             38          2147483650  33          0           80                         6           
mysql/help_topic                35          2147483654  33          0           80                         4           
mysql/innodb_index_stats        28          2147483656  33          0           80                         14          
mysql/innodb_table_stats        27          2147483654  33          0           80                         13          
mysql/plugin                    16          2147483650  33          0           80                         2           
mysql/server_cost               33          2147483652  33          0           80                         19          
mysql/servers                   17          2147483657  33          0           80                         3           
mysql/slave_master_info         30          2147483673  33          0           80                         16          
mysql/slave_relay_log_info      29          2147483657  33          0           80                         15          
mysql/slave_worker_info         31          2147483661  33          0           80                         17          
mysql/time_zone                 23          2147483650  33          0           80                         9           
mysql/time_zone_leap_second     26          2147483650  33          0           80                         12          
mysql/time_zone_name            22          2147483650  33          0           80                         8           
mysql/time_zone_transition      24          2147483651  33          0           80                         10          
mysql/time_zone_transition_type 25          2147483653  33          0           80                         11          
sys/sys_config                  39          2147483652  33          0           80                         21          
test_innodb_ruby/words          40          2147483650  33          0           80                         24  

SYS_COLUMNS

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


innodb_space -s ibdata1 data-dictionary-columns | tail

37          0     help_keyword_id                 6           1795        4     0     
37          1     name                            13          2163198     192   0     
38          0     help_topic_id                   6           1795        4     0     
38          1     help_keyword_id                 6           1795        4     0     
39          0     variable                        12          2167055     384   0     
39          1     value                           12          2166799     384   0     
39          2     set_time                        3           525575      4     0     
39          3     set_by                          12          2166799     384   0     
40          0     id                              6           1283        4     0     
40          1     word                            1           524303      64    0

SYS_INDEXES

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


innodb_space -s ibdata1 data-dictionary-indexes

table_id    id          name                            n_fields  type  space       page_no     
11          11          ID_IND                          1         3     0           270         
11          12          FOR_IND                         1         0     0           271         
11          13          REF_IND                         1         0     0           272         
12          14          ID_IND                          2         3     0           273         
13          15          SYS_TABLESPACES_SPACE           1         3     0           275         
14          16          SYS_DATAFILES_SPACE             1         3     0           276         
15          17          BASE_IDX                        3         3     0           278         
16          18          PRIMARY                         1         3     2           3           
17          19          PRIMARY                         1         3     3           3           
22          27          PRIMARY                         1         3     8           3           
23          28          PRIMARY                         1         3     9           3           
24          29          PRIMARY                         2         3     10          3           
25          30          PRIMARY                         2         3     11          3           
26          31          PRIMARY                         1         3     12          3           
27          32          PRIMARY                         2         3     13          3           
28          33          PRIMARY                         4         3     14          3           
29          34          PRIMARY                         1         3     15          3           
30          35          PRIMARY                         1         3     16          3           
31          36          PRIMARY                         2         3     17          3           
32          37          PRIMARY                         2         3     18          3           
33          38          PRIMARY                         1         3     19          3           
34          39          PRIMARY                         3         3     20          3           
35          20          PRIMARY                         1         3     4           3           
35          21          name                            1         2     4           4           
36          22          PRIMARY                         1         3     5           3           
36          23          name                            1         2     5           4           
37          25          PRIMARY                         1         3     7           3           
37          26          name                            1         2     7           4           
38          24          PRIMARY                         2         3     6           3           
39          40          PRIMARY                         1         3     21          3           
40          41          PRIMARY                         1         3     24          3 

SYS_FIELDS

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


innodb_space -s ibdata1 data-dictionary-fields

index_id    pos         col_name                        
11          0           ID                              
12          0           FOR_NAME                        
13          0           REF_NAME                        
14          0           ID                              
14          1           POS                             
15          0           SPACE                           
16          0           SPACE                           
17          0           TABLE_ID                        
17          1           POS                             
17          2           BASE_POS                        
18          0           name                            
19          0           Server_name                     
20          0           help_topic_id                   
21          0           name                            
22          0           help_category_id                
23          0           name                            
24          0           help_keyword_id                 
24          1           help_topic_id                   
25          0           help_keyword_id                 
26          0           name                            
27          0           Name                            
28          0           Time_zone_id                    
29          0           Time_zone_id                    
29          1           Transition_time                 
30          0           Time_zone_id                    
30          1           Transition_type_id              
31          0           Transition_time                 
32          0           database_name                   
32          1           table_name                      
33          0           database_name                   
33          1           table_name                      
33          2           index_name                      
33          3           stat_name                       
34          0           Channel_name                    
35          0           Channel_name                    
36          0           Channel_name                    
36          1           Id                              
37          0           source_uuid                     
37          1           interval_start                  
38          0           cost_name                       
39          0           cost_name                       
39          1           engine_name                     
39          2           device_type                     
40          0           variable                        
41          0           id                              

普通表空间

words表空间page汇总

1
2
3
4
5
6
7
8


innodb_space -f test_innodb_ruby/words.ibd space-page-type-summary

type                count       percent     description         
ALLOCATED           505         71.73       Freshly allocated   
INDEX               196         27.84       B+Tree index        
FSP_HDR             1           0.14        File space header   
IBUF_BITMAP         1           0.14        Insert buffer bitmap
INODE               1           0.14        File segment inode  

words 表空间所有page统计，会很多

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


innodb_space -f test_innodb_ruby/words.ibd space-summary | vim -

page        type                prev        next        lsn
0           FSP_HDR             0           0           12404823
1           IBUF_BITMAP         0           0           2755256
2           INODE               0           0           12404823
3           INDEX               0           0           12404823
4           INDEX               0           5           2901870
5           INDEX               4           6           2951007
6           INDEX               5           7           3000144
7           INDEX               6           8           3049281
8           INDEX               7           9           3098418
9           INDEX               8           10          3147555
10          INDEX               9           11          3196692
11          INDEX               10          12          3245829
。。。。。。

查看 words 表的数据分布

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


innodb_space -s ibdata1 --table-name test_innodb_ruby/words space-page-type-regions

start       end         count       type                
0           0           1           FSP_HDR             
1           1           1           IBUF_BITMAP         
2           2           1           INODE               
3           35          33          INDEX               
36          63          28          FREE (ALLOCATED)    
64          226         163         INDEX               
227         383         157         FREE (ALLOCATED)  

表空间区信息统计

1
2
3
4
5
6
7
8
9


innodb_space -f test_innodb_ruby/words.ibd  space-extents

start_page  page_used_bitmap                                                
0           ####################################............................
64          ################################################################
128         ################################################################
192         ###################################.............................
256         ................................................................
320         ................................................................

图表展示：
innodb_space -f test_innodb_ruby/words.ibd space-extents-illustrate

表空间 inode 统计

1
2
3
4


innodb_space -f test_innodb_ruby/words.ibd  space-inodes-summary

INODE fseg_id=1, pages=1, frag=1, full=0, not_full=0, free=0
INODE fseg_id=2, pages=224, frag=32, full=2, not_full=1, free=0

表空间 inode 更详细的统计信息

1
2
3
4


innodb_space -f test_innodb_ruby/words.ibd  space-inodes-detail

INODE fseg_id=1, pages=1, frag=1 pages (3), full=0 extents (), not_full=0 extents () (0/0 pages used), free=0 extents ()
INODE fseg_id=2, pages=224, frag=32 pages (4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35), full=2 extents (64-127, 128-191), not_full=1 extents (192-255) (35/64 pages used), free=0 extents ()

表空间中链表信息统计

1
2
3
4
5
6
7
8


innodb_space -f test_innodb_ruby/words.ibd  space-lists

name                length      f_page      f_offset    l_page      l_offset    
free                2           0           318         0           358         
free_frag           1           0           158         0           158         
full_frag           0           0           0           0           0           
full_inodes         0           0           0           0           0           
free_inodes         1           2           38          2           38  

索引占用统计

1
2
3
4
5


innodb_space -f test_innodb_ruby/words.ibd space-indexes

id          name                            root        fseg        fseg_id     used        allocated   fill_factor 
41                                          3           internal    1           1           1           100.00%     
41                                          3           leaf        2           195         224         87.05%  

索引按层级显示，第一层

1
2
3
4


innodb_space -s ibdata1 -T test_innodb_ruby/words index-level-summary -I PRIMARY -l 1

page    index   level   data    free    records min_key 
3       41      1       2730    13428   195     id=1

第0层

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


innodb_space -s ibdata1 -T test_innodb_ruby/words index-level-summary -I PRIMARY -l 0 | head

page    index   level   data    free    records min_key 
4       41      0       7482    8642    258     id=1
5       41      0       14964   1032    516     id=259
6       41      0       14964   1032    516     id=775
7       41      0       14964   1032    516     id=1291
8       41      0       14964   1032    516     id=1807
9       41      0       14964   1032    516     id=2323
10      41      0       14964   1032    516     id=2839
11      41      0       14964   1032    516     id=3355
12      41      0       14964   1032    516     id=3871

索引图信息，很多有点乱

1

innodb_space -s ibdata1 -T test_innodb_ruby/words index-digraph -I PRIMARY

表空间 page-directory 总结
非叶子索引：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


innodb_space -s ibdata1 -T test_innodb_ruby/words -p 3 page-directory-summary  | head

slot    offset  type          owned   key
0       99      infimum       1       
1       168     node_pointer  4       (id=1291)
2       224     node_pointer  4       (id=3355)
3       280     node_pointer  4       (id=5419)
4       336     node_pointer  4       (id=7483)
5       392     node_pointer  4       (id=9547)
6       448     node_pointer  4       (id=11611)
7       504     node_pointer  4       (id=13675)
8       560     node_pointer  4       (id=15739)

叶子节点：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


innodb_space -s ibdata1 -T test_innodb_ruby/words -p 4 page-directory-summary  | head 

slot    offset  type          owned   key
0       99      infimum       1       
1       214     conventional  4       (id=4)
2       330     conventional  4       (id=8)
3       446     conventional  4       (id=12)
4       562     conventional  4       (id=16)
5       678     conventional  4       (id=20)
6       794     conventional  4       (id=24)
7       910     conventional  4       (id=28)
8       1026    conventional  4       (id=32)

访问各种page，查看page的介绍

1
2
3
4


innodb_space -f test_innodb_ruby/words.ibd   page-account -p 0   
innodb_space -f test_innodb_ruby/words.ibd   page-account -p 1
innodb_space -f test_innodb_ruby/words.ibd   page-account -p 2
innodb_space -f test_innodb_ruby/words.ibd   page-account -p 3

通过系统页访问 page

1

innodb_space -f test_innodb_ruby/words.ibd -p 3 page-account

page的记录信息
非叶子节点，指向的是页号

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


innodb_space -s ibdata1 -T test_innodb_ruby/words -p 3 page-records | head -n 10 

Record 126: (id=1) → #4
Record 140: (id=259) → #5
Record 154: (id=775) → #6
Record 168: (id=1291) → #7
Record 182: (id=1807) → #8
Record 196: (id=2323) → #9
Record 210: (id=2839) → #10
Record 224: (id=3355) → #11
Record 238: (id=3871) → #12
Record 252: (id=4387) → #1

叶子节点，指向的是数据：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


innodb_space -s ibdata1 -T test_innodb_ruby/words -p 4 page-records | head -n 10 

Record 127: (id=1) → (word="75104")
Record 156: (id=2) → (word="929ed")
Record 185: (id=3) → (word="27997")
Record 214: (id=4) → (word="faa9c")
Record 243: (id=5) → (word="b47f2")
Record 272: (id=6) → (word="a94b7")
Record 301: (id=7) → (word="cbcf6")
Record 330: (id=8) → (word="6c8d1")
Record 359: (id=9) → (word="37edd")
Record 388: (id=10) → (word="26d82"

以图表方式显示

显示 words 表空间的 page 图表信息

1
2
3
4


innodb_space -f test_innodb_ruby/words.ibd   page-illustrate -p 0
innodb_space -f test_innodb_ruby/words.ibd   page-illustrate -p 1
innodb_space -f test_innodb_ruby/words.ibd   page-illustrate -p 2
innodb_space -f test_innodb_ruby/words.ibd   page-illustrate -p 3

显示系统表空间图表信息

1
2
3
4


innodb_space -s ibdata1 page-illustrate -p 0
innodb_space -s ibdata1 page-illustrate -p 1
innodb_space -s ibdata1 page-illustrate -p 3
innodb_space -s ibdata1 page-illustrate -p 64

展示区的图表信息

1
2


innodb_space -s ibdata1 space-extents-illustrate
innodb_space -f test_innodb_ruby/words.ibd space-extents-illustrate

dump 页的全部内容

会比较大

1
2
3


innodb_space -f test_innodb_ruby/words.ibd  page-dump -p 0
innodb_space -f test_innodb_ruby/words.ibd  page-dump -p 1
innodb_space -f test_innodb_ruby/words.ibd  page-dump -p 3

LSN 相关

系统表空间 LSN 热力图

1

innodb_space -s ibdata1  space-lsn-age-illustrate

普通表空间 LSN 热力图

1

innodb_space -f test_innodb_ruby/words.ibd  space-lsn-age-illustrate

分区表

创建分区表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


CREATE TABLE `tr` (
     `id` INT, 
     `name` VARCHAR(50), 
     `purchased` DATE
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE( YEAR(purchased) ) (
     PARTITION p0 VALUES LESS THAN (1990),
     PARTITION p1 VALUES LESS THAN (1995),
     PARTITION p2 VALUES LESS THAN (2000),
     PARTITION p3 VALUES LESS THAN (2005),
     PARTITION p4 VALUES LESS THAN (2010),
     PARTITION p5 VALUES LESS THAN (2015)
);


INSERT INTO `tr` VALUES
 (1, 'desk organiser', '2003-10-15'),
 (2, 'alarm clock', '1997-11-05'),
 (3, 'chair', '2009-03-10'),
 (4, 'bookcase', '1989-01-10'),
 (5, 'exercise bike', '2014-05-09'),
 (6, 'sofa', '1987-06-05'),
 (7, 'espresso maker', '2011-11-22'),
 (8, 'aquarium', '1992-08-04'),
 (9, 'study desk', '2006-09-16'),
 (10, 'lava lamp', '1998-12-25');

去服务器端看看真实的数据存储情况：

1
2
3
4
5
6
7


ll
-rw-r----- 1 mysql mysql 98304 Dec 13 20:04 tr#P#p0.ibd
-rw-r----- 1 mysql mysql 98304 Dec 13 20:04 tr#P#p1.ibd
-rw-r----- 1 mysql mysql 98304 Dec 13 20:04 tr#P#p2.ibd
-rw-r----- 1 mysql mysql 98304 Dec 13 20:04 tr#P#p3.ibd
-rw-r----- 1 mysql mysql 98304 Dec 13 20:04 tr#P#p4.ibd
-rw-r----- 1 mysql mysql 98304 Dec 13 20:04 tr#P#p5.ibd

每个文件都对应一个表空间，是一个独立的文件，一共被分成了 6个独立的文件