mysql数据库存储null_MySQL Innodb 存储结构 & 存储Null值 解析

背景:

表空间：INNODB 所有数据都存在表空间当中(共享表空间)，要是开启innodb_file_per_table，则每张表的数据会存到单独的一个表空间内(独享表空间)。

独享表空间包括：数据，索引，插入缓存，数据字典。共享表空间包括：Undo信息(不会回收)，双写缓存信息，事务信息等。

段(segment)：组成表空间，有区组成。

区(extent)：有64个连续的页组成。每个页16K，总共1M。对于大的数据段，每次最后可申请4个区。

页(page)：是INNODB 磁盘管理的单位，有行组成。

行(row)：包括事务ID，回滚指针，列信息等。

View Code

#! /usr/bin/env python#encoding=utf-8

importmylibfrom sys importargvfrom mylib importmyargvif __name__ == '__main__':

myargv=myargv(argv)if myargv.parse_cmdline() ==0:pass

else:

mylib.get_innodb_page_type(myargv)

mylib.py

View Code

encoding=utf-8

importosimportincludefrom include import *TABLESPACE_NAME='D:\\mysql_data\\test\\t.ibd'VARIABLE_FIELD_COUNT= 1NULL_FIELD_COUNT=0classmyargv(object):def __init__(self, argv):

self.argv=argv

self.parms={}

self.tablespace= ''

defparse_cmdline(self):

argv=self.argvif 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'

return0whileargv: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'

return0return 1

defmach_read_from_n(page,start_offset,length):

ret= page[start_offset:start_offset+length]return ret.encode('hex')defget_innodb_page_type(myargv):

f=file(myargv.tablespace,'rb')

fsize= os.path.getsize(f.name)/INNODB_PAGE_SIZE

ret={}for i inrange(fsize):

page=f.read(INNODB_PAGE_SIZE)

page_offset= mach_read_from_n(page,FIL_PAGE_OFFSET,4)

page_type= mach_read_from_n(page,FIL_PAGE_TYPE,2)if myargv.parms.has_key('-v'):if page_type == '45bf':

page_level= mach_read_from_n(page,FIL_PAGE_DATA+PAGE_LEVEL,2)print "page offset %s, page type , page level "%(page_offset,innodb_page_type[page_type],page_level)else:print "page offset %s, page type "%(page_offset,innodb_page_type[page_type])if notret.has_key(page_type):

ret[page_type]= 1

else:

ret[page_type]= ret[page_type] + 1

print "Total number of page: %d:"%fsizefor type inret:print "%s: %s"%(innodb_page_type[type],ret[type])

include.py

View Code

#encoding=utf-8

INNODB_PAGE_SIZE = 16*1024*1024

#Start of the data on the page

FIL_PAGE_DATA = 38FIL_PAGE_OFFSET= 4 #page offset inside space

FIL_PAGE_TYPE = 24 #File page type

#Types of an undo log segment */

TRX_UNDO_INSERT = 1TRX_UNDO_UPDATE= 2

#On a page of any file segment, data may be put starting from this offset

FSEG_PAGE_DATA =FIL_PAGE_DATA#The offset of the undo log page header on pages of the undo log

TRX_UNDO_PAGE_HDR =FSEG_PAGE_DATA

PAGE_LEVEL= 26 #level of the node in an index tree; the leaf level is the level 0 */

innodb_page_type={'0000':u'Freshly Allocated Page','0002':u'Undo Log Page','0003':u'File Segment inode','0004':u'Insert Buffer Free List','0005':u'Insert Buffer Bitmap','0006':u'System Page','0007':u'Transaction system Page','0008':u'File Space Header','0009':u'扩展描述页','000a':u'Uncompressed BLOB Page','000b':u'1st compressed BLOB Page','000c':u'Subsequent compressed BLOB Page','45bf':u'B-tree Node'}

innodb_page_direction={'0000': 'Unknown(0x0000)','0001': 'Page Left','0002': 'Page Right','0003': &