使用innodb_ruby分析InnoDb索引文件

2020-09-03  本文已影响0人  捞月亮的阿汤哥

innodb_ruby工具的安装

前置条件:

#检查ruby和gem
gem -v
ruby -v

安装命令

gem install --user-install innodb_ruby

查看mysql数据目录

show variables like 'datadir'

页面概览

使用

Space File Structure

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

Page Structure

Index Structure

Record Structure

Record History

使用innodb_ruby的ruby代码进行开发

  1. 下载源码 https://github.com/jeremycole/innodb_ruby
  2. 使用rubymine打开
  3. 使用ruby调用innodb_ruby的代码

解析通用的页面结构

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

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
上一篇 下一篇

猜你喜欢

热点阅读