Innodb表空间辅助工具使用

本文主要是介绍Innodb表空间辅助工具使用，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

Innodb表空间辅助工具使用

工具1 py_innodb_page_info,msyql技术内幕存储引擎-Innodb技术内幕一书中使用的工具,能够分析ibd文件 github地址:https://github.com/happieme/py_innodb_page_info
工具2 innodb_space 功能更强大,以更直观的方式分析表空间文件,github地址:https://github.com/jeremycole/innodb_ruby

概念说明:

表/表空间:在开启了innodb_file_per_table=1后,每张表对应一个独立的表空间文件，

查询具体的表所属的表空间Id可以执行

SELECT * FROM information_schema.innodb_sys_tablespaces WHERE name LIKE '%user_info%';

输出

+-------+-----------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME            | FLAG | FILE_FORMAT | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-----------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+
|    33 | test/user_info  |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 |    114688 |         114688 |
|    34 | test/user_info2 |   33 | Barracuda   | Dynamic    |     16384 |             0 | Single     |          4096 | 360710144 |      370147328 |
+-------+-----------------+------+-------------+------------+-----------+---------------+------------+---------------+-----------+----------------+

输出中的space列即为对应表空间ID

表ID、索引ID: 表是一个应用存储逻辑数据的基本单位,innodb是索引组织表,数据即索引,索引也是数据的一部分,一张表从逻辑上可以划分为由多个索引构成,系统为每个表、每个表中的索引生成一个唯一的ID

查看表ID:

SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE '%user_info%';

输出

+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
| TABLE_ID | NAME            | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+
|       55 | test/user_info  |   33 |      9 |    33 | Barracuda   | Dynamic    |             0 | Single     |
|       56 | test/user_info2 |   33 |      9 |    34 | Barracuda   | Dynamic    |             0 | Single     |
+----------+-----------------+------+--------+-------+-------------+------------+---------------+------------+

table_id即为表ID,可以看到输出列中还有一个space字段,此此段即为数据表对应的表空间ID

查看索引ID

SELECT * FROM information_schema.innodb_sys_indexes WHERE table_id=55;

输出

+----------+-----------+----------+------+----------+---------+-------+-----------------+
| INDEX_ID | NAME      | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+-----------+----------+------+----------+---------+-------+-----------------+
|       44 | PRIMARY   |       55 |    3 |        1 |       3 |    33 |              50 |
|       50 | idx_phone |       55 |    0 |        1 |       4 |    33 |              50 |
+----------+-----------+----------+------+----------+---------+-------+-----------------+

index_id 即为索引ID,table_id、space为对应的表ID、表空间ID

测试表建表语句

表1 user_info

CREATE TABLE `user_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(20) NOT NULL COMMENT '名称',
  `phone` varchar(30) NOT NULL COMMENT '手机',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '邮箱',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表2 user_info2

CREATE TABLE `user_info2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(20) NOT NULL COMMENT '名称',
  `phone` varchar(30) NOT NULL COMMENT '手机',
  `email` varchar(50) NOT NULL DEFAULT '' COMMENT '邮箱',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=2484014 DEFAULT CHARSET=utf8mb4;

表1与表2结构一样,不同的是两表数据量不一样,本地表2数据量在240w左右,为后续内容作准备

生成测试数据的mysql函数/存储过程

随机数函数

drop function if exists `rand_number`;
delimiter $$
create function `rand_number`(n int) returns varchar(255) charset utf8mb4 no sql
begin        
	declare chars_str varchar(100)  default "0123456789";
	declare return_str varchar(255) default "";        
	declare i int default 0;
	while i < n do        
	set return_str=concat(return_str,substring(chars_str,floor(1+rand()*10),1));
	set i= i+1;        
	end while;        
  return return_str;    
end $$
delimiter ;

生成随机字符串函数

drop function if exists `rand_string`;
delimiter $$
create function `rand_string`(n int) returns varchar(255) charset utf8mb4 no sql
begin        
  declare chars_str varchar(100) 
  default "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz0123456789";
  declare return_str varchar(255) default "";        
  declare i int default 0;
  while i < n do        
      set return_str=concat(return_str,substring(chars_str,floor(1+rand()*62),1));
      set i= i+1;        
  end while;        
  return return_str;    
end $$
delimiter ;

生成测试数据的存储过程

drop procedure if exists `insert_user_info_data`;
delimiter $$
create  procedure `insert_user_info_data`(in n int) deterministic
begin  
	declare i int default 1;
	declare start_create_time datetime default now();
	declare sec int default -30*24*3600;

	set autocommit = 0;
	while (i <= n) do
		set sec=sec+2;
		insert into user_info 
		(`name`,`phone`,`email`,`create_time`,`update_time`) 
		values 
		(rand_string(20),rand_number(11),rand_string(20) ,date_add(start_create_time,interval sec second),date_add(start_create_time,interval sec second)) ;
		if i%100=0 then
			-- 500条提交一次
			commit;
		end if;
		set i=i+1 ;
	end while ;
	-- 提交剩余的
	commit ;
	set autocommit = 1;
end $$
delimiter ;

py_innodb_page_info使用方法

命令别名
```
export PYTHONPATH=/yourpath/py_innodb_page_info/
alias mysql_page_info="python /yourpath/py_innodb_page_info/py_innodb_page_info.py"
```
需要本地有python环境,我这本地做了一个alias的别名处理，方便后续不用敲全名,将对应的路径替换为各自的安装路径即可,执行上述存储过程call insert_user_info_data(5)生成5条测试数据

基本用法

mysql_page_info test/user_info.ibd

输出

Total number of page: 7:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 4
File Segment inode: 1

释义

Total number of page: 当前表空间数据页数量
Insert Buffer Bitmap: 插入缓冲位图数据页数量
File Space Header: 文件头数据页数量
B-tree Node: B树节点数据页数量
File Segment inode: 段描述页数量

mysql_page_info test/user_info.ibd -v

输出

page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
Total number of page: 7
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 4
File Segment inode: 1

可以看到添加-v参数后打印出了数据页的编号以及每个数据的描述信息

innodb_space常用命令

下面对innodb_space工具常用的功能做一个说明(详细用法可参见官方wiki页面,链接见头部github仓库地址)

列出数据表的索引信息

innodb_space -s ibdata1 -T test/user_info space-indexes

输出

id          name                            root        fseg        fseg_id     used        allocated   fill_factor
44          PRIMARY                         3           internal    1           1           1           100.00%
44          PRIMARY                         3           leaf        2           0           0           0.00%
50          idx_phone                       4           internal    3           1           1           100.00%
50          idx_phone                       4           leaf        4           0           0           0.00%

释义

id:索引ID列,参见上述索引ID
name 索引名称列
root 索引根节点位于的数据页编号(数据页概念后续会详细说明)
fseg 索引类型(internal为非叶节点,leaf为叶节点)
fseg_id 索引所属段ID(段的概念后续会说明)
used 使用的page页数量
allocated 申请的数据页数量
fill_factor used/allocated 索引页使用百分比

统计表空间中各页类型占比

innodb_space -s ibdata1 -T test/user_info space-page-type-summary

输出

type                count       percent     description
INDEX               4           57.14       B+Tree index
FSP_HDR             1           14.29       File space header
IBUF_BITMAP         1           14.29       Insert buffer bitmap
INODE               1           14.29       File segment inode

释义

type 页类型
count 数量
percent 占比
description 页的基本描述信息

dump page数据页详细信息

innodb_space -s ibdata1 -T test/user_info -p 3 page-dump

输出内容较多,此处只粘贴部分输出

 fil header:
 {:checksum=>1959780131,
  :offset=>3,
  :prev=>nil,
  :next=>nil,
  :lsn=>20426510,
  :type=>:INDEX,
  :flush_lsn=>0,
  :space_id=>33}

 fil trailer:
 {:checksum=>1959780131, :lsn_low32=>20426510}

 page header:
 {:n_dir_slots=>2,
  :heap_top=>565,
  :garbage_offset=>0,
  :garbage_size=>0,
  :last_insert_offset=>484,
  :direction=>:right,
  :n_direction=>4,
  :n_recs=>5,
  :max_trx_id=>0,
  :level=>0,
  :index_id=>44,
  :n_heap=>7,
  :format=>:compact}

 fseg header:
 {:leaf=>
   <Innodb::Inode space=<Innodb::Space file="test/user_info.ibd", page_size=16384, pages=7>, fseg=2>,
  :internal=>
   <Innodb::Inode space=<Innodb::Space file="test/user_info.ibd", page_size=16384, pages=7>, fseg=1>}

 sizes:
   header           120
   trailer            8
   directory          4
   free           15807
   used             577
   record           445
   per record     89.00

 page directory:
 [99, 112]
 
 system records:
 {:offset=>99,
  :header=>
   {:next=>128,
    :type=>:infimum,
    :heap_number=>0,
    :n_owned=>1,
    :min_rec=>false,
    :deleted=>false,
    :length=>5},
  :next=>128,
  :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:
 {:format=>:compact,
  :offset=>128,
  :header=>
   {:next=>217,
    :type=>:conventional,
    :heap_number=>2,
    :n_owned=>0,
    :min_rec=>false,
    :deleted=>false,
    :nulls=>[],
    :lengths=>{"name"=>20, "phone"=>11, "email"=>20},
    :externs=>[],
    :length=>8},
  :next=>217,
  :type=>:clustered,
  :key=>[{:name=>"id", :type=>"BIGINT UNSIGNED", :value=>1}],
  :row=>
   [{:name=>"name", :type=>"VARCHAR(80)", :value=>"3i9izcwibfprwwkien9d"},
    {:name=>"phone", :type=>"VARCHAR(120)", :value=>"33810132397"},
    {:name=>"email", :type=>"VARCHAR(200)", :value=>"8gr4furgtsusl1g9zehy"},
    {:name=>"create_time",
     :type=>"DATETIME",
     :value=>"184913550-53-57 17:81:33"},
    {:name=>"update_time", :type=>"TIMESTAMP", :value=>"2003-01-02 09:49:08"}],
  :sys=>
   [{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>6660},
    {:name=>"DB_ROLL_PTR",
     :type=>"ROLL_PTR",
     :value=>
      {:is_insert=>true, :rseg_id=>88, :undo_log=>{:page=>363, :offset=>272}}}],
  :length=>84,
  :transaction_id=>6660,
  :roll_pointer=>
   {:is_insert=>true, :rseg_id=>88, :undo_log=>{:page=>363, :offset=>272}}}

输出内容只粘贴了一条记录,亦即id=1的数据记录,具体数据页格式可以参考后续介绍

dump页面中数据部分

innodb_space -s ibdata1 -T test/user_info -p 3 page-records

输出

Record 128: (id=1) → (name="3i9izcwibfprwwkien9d", phone="33810132397", email="8gr4furgtsusl1g9zehy", create_time="184913550-53-57 17:81:33", update_time="2003-01-02 09:49:08")
Record 217: (id=2) → (name="kvgmqze5wbesshybyto6", phone="04089308321", email="29usioj4yxcjygajkmqs", create_time="184913550-53-90 73:25:65", update_time="2004-01-25 18:29:40")
Record 306: (id=3) → (name="x00q4cllpawhlauwzcul", phone="79622320564", email="yn9cpfqkwaxto6egwpke", create_time="184913550-54-24 28:69:97", update_time="2005-02-17 03:10:12")
Record 395: (id=4) → (name="3nytya2vhkh5aryjglxs", phone="38082638449", email="rnvzgby9hq0jpxnqx3gy", create_time="184913550-54-57 84:14:29", update_time="2006-03-12 11:50:44")
Record 484: (id=5) → (name="utsj9ng19wzg8mj27j38", phone="22472851323", email="w5rlnpmv2t997zr2lv6r", create_time="184913550-54-91 39:58:61", update_time="2007-03-20 15:38:40")

遍历索引(索引扫描)

主索引

innodb_space -s ibdata1 -T test/user_info -I PRIMARY index-recurse

输出

RECORD: (id=1) → (name="3i9izcwibfprwwkien9d", phone="33810132397", email="8gr4furgtsusl1g9zehy", create_time="184913550-53-57 17:81:33", update_time="2003-01-02 09:49:08")
RECORD: (id=2) → (name="kvgmqze5wbesshybyto6", phone="04089308321", email="29usioj4yxcjygajkmqs", create_time="184913550-53-90 73:25:65", update_time="2004-01-25 18:29:40")
RECORD: (id=3) → (name="x00q4cllpawhlauwzcul", phone="79622320564", email="yn9cpfqkwaxto6egwpke", create_time="184913550-54-24 28:69:97", update_time="2005-02-17 03:10:12")
RECORD: (id=4) → (name="3nytya2vhkh5aryjglxs", phone="38082638449", email="rnvzgby9hq0jpxnqx3gy", create_time="184913550-54-57 84:14:29", update_time="2006-03-12 11:50:44")
RECORD: (id=5) → (name="utsj9ng19wzg8mj27j38", phone="22472851323", email="w5rlnpmv2t997zr2lv6r", create_time="184913550-54-91 39:58:61", update_time="2007-03-20 15:38:40")

可以看到遍历主索引即是扫表了

辅助索引

innodb_space -s ibdata1 -T test/user_info -I idx_phone index-recurse

输出

RECORD: (phone="04089308321") → (id=2)
RECORD: (phone="22472851323") → (id=5)
RECORD: (phone="33810132397") → (id=1)
RECORD: (phone="38082638449") → (id=4)
RECORD: (phone="79622320564") → (id=3)

查看索引层级数据信息

innodb_space -s ibdata1 -T test/user_info2 -I PRIMARY -l 1 index-level-summary

输出

page  index   level   data    free    records min_key
133     51      1       7888    8132    464     id=1
134     51      1       15776   14      928     id=78796
135     51      1       15776   14      928     id=236556
138     51      1       15776   14      928     id=394316
141     51      1       15776   14      928     id=552076
142     51      1       15776   14      928     id=709836
143     51      1       15776   14      928     id=867596
145     51      1       15776   14      928     id=1025356
149     51      1       15776   14      928     id=1183116
150     51      1       15776   14      928     id=1340876
151     51      1       15776   14      928     id=1498636
152     51      1       15776   14      928     id=1656396
154     51      1       15776   14      928     id=1814156
156     51      1       15776   14      928     id=1971916
161     51      1       15776   14      928     id=2129676
164     51      1       15776   14      928     id=2287436
165     51      1       3893    12247   229     id=2445196

释义

page:页号
index:索引ID
level:索引层级
data:数据key
free:当前页剩余空间
records:当前页记录数
min_key:当前页的最小key值

这篇关于Innodb表空间辅助工具使用的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

Java教程