mysql里hdr是什么的缩写_使用innodb_ruby分析InnoDb索引文件

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代码进行开发

解析通用的页面结构

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