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值