# 解析输入参数 def parse_cmdline(self): argv self.argv if len(argv) 1: print'Usage: python py_innodb_page_info.py [OPTIONS] tablespace_file' print'For more options, use python py_innodb_page_info.py -h' return 0 while argv: if argv[0][0] '-': if argv[0][1] 'h': self.parms[argv[0]] '' argv argv[1:] break if argv[0][1] 'v': self.parms[argv[0]] '' argv argv[1:] else: self.parms[argv[0]] argv[1] argv argv[2:] else: self.tablespace argv[0] argv argv[1:] if self.parms.has_key('-h'): print'Get InnoDB Page Info' print'Usage: python py_innodb_page_info.py [OPTIONS] tablespace_file\n' print'The following options may be given as the first argument:' print'-h help ' print'-o output put the result to file' print'-t number thread to anayle the tablespace file' print'-v verbose mode' return 0 return 1
def mach_read_from_n(page,start_offset,length): ret page[start_offset:start_offset length] return ret.encode('hex')
# main方法 def get_innodb_page_type(myargv): # 读取 .ibd 文件,存入 f f file(myargv.tablespace,'rb') # INNODB_PAGE_SIZE 16KB,f 一定是16KB的整数倍,一页大小是16KB # 使用 f 的字节总数除以 16KB,得到的 fsize 表示页数 fsize os.path.getsize(f.name)/INNODB_PAGE_SIZE ret {} # 枚举每一页 for i in range(fsize): # 读取16KB数据,存入page,是第i的数据 # page 是二进制文件 page f.read(INNODB_PAGE_SIZE) # page_offset 是 page[4, 8) 字符所表示的数据 page_offset mach_read_from_n(page,FIL_PAGE_OFFSET,4) # page_type 是 page[24, 26) 字符所表示的数据 page_type mach_read_from_n(page,FIL_PAGE_TYPE,2) # 如果加了参数 -v,表示输出详细数据 if myargv.parms.has_key('-v'): # page_type 为 45bf,表示 B-tree Node,输出它的信息 if page_type '45bf': # page_level 是 page[64, 66) 字符所表示的数据 page_level mach_read_from_n(page,FIL_PAGE_DATA PAGE_LEVEL,2) print page offset %s, page type <%s>, page level <%s> %(page_offset,innodb_page_type[page_type],page_level) # page_type 对应 include.py 中的 innodb_page_type,输出它的信息 else: print page offset %s, page type <%s> %(page_offset,innodb_page_type[page_type]) # 统计数据 if not ret.has_key(page_type): ret[page_type] 1 else: ret[page_type] ret[page_type] 1 # 输出fsize,表示有多少页 print Total number of page: %d: %fsize # 输出统计数据 fortypein ret: print %s: %s %(innodb_page_type[type],ret[type])
py_innodb_page_info.py
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
#! /usr/bin/env python #encoding utf-8 import mylib from sys import argv from mylib import myargv
# main函数 if __name__ '__main__': myargv myargv(argv) if myargv.parse_cmdline() 0: pass else: # 执行main方法 mylib.get_innodb_page_type(myargv)
示例
先创建个example_table空表,看下example_table.ibd,可以看到总共分配了11page Total number of page: 11 ,但是为啥空闲的只有6page B-tree Node: 6 ,这里就不得不提到之前说到的预留空间了
1 2 3 4 5 6 7 8 9 10 11 12 13 14
CREATE TABLE example_table( id INT AUTO_INCREMENT, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100), created_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP, status ENUM('active', 'inactive') DEFAULT'active', PRIMARY KEY (id), INDEX idx_last_name (last_name), FULLTEXT INDEX ftx_first_name (first_name), INDEX idx_status (status), INDEX idx_created_at (created_at), INDEX idx_first_name_last_name (first_name, last_name) );
CREATEPROCEDURE InsertExampleData() BEGIN DECLARE i INTDEFAULT1; DECLARE maxEmail INTDEFAULT1000; DECLARE firstName VARCHAR(50); DECLARE lastName VARCHAR(50); DECLARE email VARCHAR(100); DECLARE status ENUM('active', 'inactive'); DECLAREtimestamp DATETIME;
WHILE i <10000 DO SET firstName CONCAT('Name', LPAD(i, 5, '0')); SET lastName CONCAT('Surname', LPAD(i, 5, '0')); SET email CONCAT(firstName, '_', lastName, '_', i, ' example.com'); SET status IF(i %20, 'active', 'inactive'); SETtimestamp DATE_ADD(NOW(), INTERVALFLOOR(RAND() *365) DAY);
