innodb_ruby工具的安装
前置条件:
- 安装ruby和gem
- mysql的版本5.7(mysql8可能不支持)
#检查ruby和gem
gem -v
ruby -v
安装命令
gem install --user-install innodb_ruby
查看mysql数据目录
show variables like 'datadir'
页面概览
- jeremycole的一个项目github地址
image.png
使用
- jeremycole写的wiki
- 首先需要进入你的mysql数据目录
- 注意test/t对应的是我的test数据库的t表，根据你的实际情况替换
- -s ibdata1 ; -T 表空间(数据库/表名) ; -I 索引名
Space File Structure
-
innodb_space -s ibdata1 system-spaces
展示的是系统表空间的信息
image.png
-
innodb_space -s ibdata1 -T test/t space-indexes
primary是聚簇索引，key_a是我建的二级索引
image.png
- innodb_space -s ibdata1 -T test/t space-page-type-regions
innodb每页16kb，每个页的类型如下, 索引页type是17855
PAGE_TYPE = {
ALLOCATED: {
value: 0,
description: 'Freshly allocated',
usage: 'page type field has not been initialized',
},
UNDO_LOG: {
value: 2,
description: 'Undo log',
usage: 'stores previous values of modified records',
},
INODE: {
value: 3,
description: 'File segment inode',
usage: 'bookkeeping for file segments',
},
IBUF_FREE_LIST: {
value: 4,
description: 'Insert buffer free list',
usage: 'bookkeeping for insert buffer free space management',
},
IBUF_BITMAP: {
value: 5,
description: 'Insert buffer bitmap',
usage: 'bookkeeping for insert buffer writes to be merged',
},
SYS: {
value: 6,
description: 'System internal',
usage: 'used for various purposes in the system tablespace',
},
TRX_SYS: {
value: 7,
description: 'Transaction system header',
usage: 'bookkeeping for the transaction system in system tablespace',
},
FSP_HDR: {
value: 8,
description: 'File space header',
usage: 'header page (page 0) for each tablespace file',
},
XDES: {
value: 9,
description: 'Extent descriptor',
usage: 'header page for subsequent blocks of 16,384 pages',
},
BLOB: {
value: 10,
description: 'Uncompressed BLOB',
usage: 'externally-stored uncompressed BLOB column data',
},
ZBLOB: {
value: 11,
description: 'First compressed BLOB',
usage: 'externally-stored compressed BLOB column data, first page',
},
ZBLOB2: {
value: 12,
description: 'Subsequent compressed BLOB',
usage: 'externally-stored compressed BLOB column data, subsequent page',
},
INDEX: {
value: 17_855,
description: 'B+Tree index',
usage: 'table and index data stored in B+Tree structure',
},
}.freeze
image.png
-
innodb_space -s ibdata1 -T test/t space-page-type-summary
image.png
-
innodb_space -s ibdata1 -T test/t space-extents-illustrate
image.png
-
innodb_space -s ibdata1 -T test/t space-lsn-age-illustrate
image.png
Page Structure
-
innodb_space -s ibdata1 -T test/t -p 3 page-account
image.png
- innodb_space -s ibdata1 -T test/t -p 3 page-dump
可以输出innodb_ruby分析出的数据结构 -
innodb_space -s ibdata1 -T test/t -p 3 page-records
image.png
-
innodb_space -s ibdata1 -T test/t -p 3 page-illustrate
输出每一部分的大小，单个页面总大小一定是16384bytes也就是16kb
image.png
Index Structure
-
innodb_space -s ibdata1 -T test/t -I PRIMARY index-recurse
image.png
-
innodb_space -s ibdata1 -T test/t -I PRIMARY index-record-offsets
image.png
-
innodb_space -s ibdata1 -T test/t -I PRIMARY -l 0 index-level-summary
image.png
Record Structure
-
innodb_space -s ibdata1 -T test/t -p 3 -R 2 record-dump
image.png
Record History
-
innodb_space -s ibdata1 -T test/t -p 3 -R 2 record-history
image.png
使用innodb_ruby的ruby代码进行开发
- 下载源码 https://github.com/jeremycole/innodb_ruby
- 使用rubymine打开
- 使用ruby调用innodb_ruby的代码
解析通用的页面结构
- innodb的页有两个通用的数据结构fil_header和fil_trailer
- 解析 fil_header
require 'innodb'
#页面通用数据结构 fil_header
def print_fil_header(page)
# flush_lsn page类没有提供父类的方法,FspHdrXdes也没实现
printf("fil_header ")
puts 'fil_header[ checksum:%s,offset:%s,prev:%s,next:%s,lsn:%s,type:%s,flush_lsn:%s,space_id:%s ]' %
[page.checksum, page.offset, page.prev, page.next, page.lsn, page.type, nil, page.space_id]
end
- 解析fil_trailer
require 'innodb'
#fil trailer
def print_fil_trailer(page)
#fil trailer
puts 'fil_trailer[ checksum:%s,lsn_low32:%s ]' % [page.checksum, page.lsn & 0xffffffff]
end
解析fsp_hdr
fsp_hdr是page=0，也就是space的第一个页面
FSP_HDR Page Overview.png
require 'innodb'
=begin
解析第一个页面 page=0
is_show_xdes_entry 是否输出xdes_entry 有256个数组元素
space_path 表空间地址 xxxx/ibdata1
table_name 数据库名/表名 比如test/t
=end
def get_fsp_hdr(is_show_xdes_entry = false, space_path, table_name)
innodb_system = Innodb::System.new(space_path)
space = innodb_system.space_by_table_name(table_name)
page_number = 0
puts "Accounting for page #{page_number}:"
if page_number > space.pages
puts ' Page does not exist.'
return
end
page = space.page(page_number)
page_type = Innodb::Page::PAGE_TYPE[page.type]
puts 'Page type is %s (%s, %s).' % [
page.type,
page_type[:description],
page_type[:usage],
]
print_fil_header(page)
#fsp_header
printf("fsp_header size:%d", page.size_fsp_header)
pp page.fsp_header
#xdes_entry 长度是256
printf("xdes_entry array size:%d", page.size_xdes_array)
xdes_array = page.each_xdes.to_a
xdes_array.each do |arr|
if is_show_xdes_entry
pp arr
end
end
printf("empty space:%d", (16384 - 38 - 8 - page.size_xdes_array - page.size_fsp_header))
print_fil_trailer(page)
end
解析IBUF_BITMAP
IBUF_BITMAP是第二页
IBUF_BITMAP Page Overview.png
require 'innodb'
def get_ibuf_bitmap(space_path, table_name)
innodb_system = Innodb::System.new(space_path)
space = innodb_system.space_by_table_name(table_name)
page_number = 1
page = space.page(page_number)
print_fil_header(page)
#具体的8192个数组不太好从外部获得
printf("change buffer bitmap size:%d bytes", page.size_ibuf_bitmap)
printf("empty space:%d", (16384 - 38 - 8 - page.size_ibuf_bitmap))
print_fil_trailer(page)
end
解析INODE页
INODE页是第三页
INODE Page Overview.png
require 'innodb'
def get_innode_page(space_path, table_name, show_frag_array = false)
innodb_system = Innodb::System.new(space_path)
space = innodb_system.space_by_table_name(table_name)
page_number = 2
page = space.page(page_number)
print_fil_header(page)
#list node for Inode page list 12 bytes size
printf("list entry size:%s, ", page.size_list_entry)
printf("list entry info[ prev_address:%s,next_address:%s]\n", page.prev_address, page.next_address)
#inode entry
printf("inode entry size:%d", page.size_inode_array)
inode_array = page.each_inode.to_a
inode_array.each do |inode|
puts 'fseg_id:%s, used_pages in not full list:%s,free_list:%s,not_full_list:%s,full_list:%s,magic_number:%s' % [
inode.fseg_id,
inode.not_full_n_used,
inode.free,
inode.not_full,
inode.full,
inode.magic_n
]
#输出frag array 每个数组就是一个数值
if show_frag_array
frag_array = inode.frag_array.to_a
frag_array.each do |frag|
pp frag
end
end
end
#empty space
printf("the empty space size: %d", 16384 - 38 - 8 - page.size_inode_array - page.size_list_entry)
print_fil_trailer(page)
end
解析index页
index页是第4页
INDEX Page Overview.png
require 'innodb'
#获取索引页的内容
def get_index_page(space_path, table_name, show_frag_array = false)
innodb_system = Innodb::System.new(space_path)
space = innodb_system.space_by_table_name(table_name)
page_number = 3
page = space.page(page_number)
print_fil_header(page)
#index header
puts
puts("...index header...")
printf("index header size:%d\n", page.size_index_header)
pp page.page_header
puts
puts
puts("...fseg header...")
pp page.fseg_header
puts
puts
puts("...system records...")
#pp page.system_record(0)
printf("infimum [")
pp page.infimum
printf(" ]\n")
printf("supremum [")
pp page.supremum
printf(" ]\n")
puts
#todo 分析具体的查找过程
puts
puts("...user records..")
#system records use 26 bytes
pp page.record(26)
puts
printf("free space size:%d bytes\n", page.free_space)
puts
puts("...page directory..")
pp page.directory
puts
print_fil_trailer(page)
end
#二分查找的方法 可以debug看下
def binary_search
space = "/Users/zihao/Library/Application Support/com.tinyapp.DBngin/Engines/mysql/0EE8D9C5-5A27-4B76-8075-2694FCB701F7/ibdata1"
table = "test/t"
sys = Innodb::System.new(space)
idx = sys.index_by_name(table, "PRIMARY")
rec = idx.binary_search([1])
rec_arr = rec.key[0]
printf("%s=%s", rec_arr[:name], rec_arr[:value])
printf("\n")
rec.row.each do |r|
printf("%s=%s\n", r[:name], r[:value])
end
end
网友评论