完美的世界 1993 电影,择天记

主頁(yè) > 知識(shí)庫(kù) > MySQL Innodb 存儲(chǔ)結(jié)構(gòu) 和存儲(chǔ)Null值用法詳解

MySQL Innodb 存儲(chǔ)結(jié)構(gòu) 和存儲(chǔ)Null值用法詳解

背景:

表空間：INNODB 所有數(shù)據(jù)都存在表空間當(dāng)中（共享表空間），要是開(kāi)啟innodb_file_per_table，則每張表的數(shù)據(jù)會(huì)存到單獨(dú)的一個(gè)表空間內(nèi)（獨(dú)享表空間）。
獨(dú)享表空間包括：數(shù)據(jù)，索引，插入緩存，數(shù)據(jù)字典。共享表空間包括：Undo信息（不會(huì)回收物理空間上>），雙寫(xiě)緩存信息，事務(wù)信息等。
段（segment）：組成表空間，有區(qū)組成。
區(qū)（extent）：有64個(gè)連續(xù)的頁(yè)組成。每個(gè)頁(yè)16K，總共1M。對(duì)于大的數(shù)據(jù)段，每次最后可申請(qǐng)4個(gè)區(qū)。
頁(yè)（page）：是INNODB 磁盤(pán)管理的單位，有行組成。
行（row）：包括事務(wù)ID，回滾指針，列信息等。

目的1：
了解表空間各個(gè)頁(yè)的信息和溢出行數(shù)據(jù)存儲(chǔ)的信息。通過(guò)該書(shū)作者蔣承堯編寫(xiě)的工具：http://code.google.com/p/david-mysql-tools/source/browse/trunk/py_innodb_page_type/
3個(gè)腳本：

py_innodb_page_info.py

View Code 

#! /usr/bin/env python 
#encoding=utf-8
import mylib
from sys import argv
from mylib import myargv

if __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
import os
import include
from include import *

TABLESPACE_NAME='D:\\mysql_data\\test\\t.ibd'
VARIABLE_FIELD_COUNT = 1
NULL_FIELD_COUNT = 0

class myargv(object):
 def __init__(self, argv):
  self.argv = argv
  self.parms = {}
  self.tablespace = ''

 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')

def get_innodb_page_type(myargv):
 f=file(myargv.tablespace,'rb')
 fsize = os.path.getsize(f.name)/INNODB_PAGE_SIZE
 ret = {}
 for i in range(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 %s>, page level %s>"%(page_offset,innodb_page_type[page_type],page_level)
   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
 print "Total number of page: %d:"%fsize
 for type in ret:
  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 = 38


FIL_PAGE_OFFSET = 4 # page offset inside space
FIL_PAGE_TYPE = 24 # File page type

# Types of an undo log segment */
TRX_UNDO_INSERT = 1
TRX_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'擴(kuò)展描述頁(yè)',
 '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': 'Page Same Rec',
 '0004': 'Page Same Page',
 '0005': 'Page No Direction',
 'ffff': 'Unkown2(0xffff)'
}


INNODB_PAGE_SIZE=1024*16 # InnoDB Page 16K

測(cè)試1：

root@localhost : test 02:26:13>create table tt(id int auto_increment,name varchar(10),age int,address varchar(20),primary key (id))engine=innodb;
Query OK, 0 rows affected (0.17 sec)
root@zhoujy:/var/lib/mysql/test# ls -lh tt.ibd 
-rw-rw---- 1 mysql mysql 96K 2012-10-17 14:26 tt.ibd

查看ibd：

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/tt.ibd -v
page offset 00000000, page type File Space Header>
page offset 00000001, page type Insert Buffer Bitmap>
page offset 00000002, page type File Segment inode>
page offset 00000003, page type B-tree Node>, page level 0000> ---葉子節(jié)點(diǎn)
page offset 00000000, page type Freshly Allocated Page>
page offset 00000000, page type Freshly Allocated Page>
Total number of page: 6: 
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

解釋?zhuān)?br /> Total number of page: 總頁(yè)數(shù)
Freshly Allocated Page:可用頁(yè)
Insert Buffer Bitmap:插入緩存位圖頁(yè)
Insert Buffer Free List:插入緩存空閑列表頁(yè)
B-tree Node:數(shù)據(jù)頁(yè)
Uncompressed BLOB Page:二進(jìn)制大對(duì)象頁(yè)，存放溢出行的頁(yè)，即溢出頁(yè)
上面得到的信息是表初始化大小為96K，他是有 Total number of page * 16 得來(lái)的。1個(gè)數(shù)據(jù)頁(yè)，2個(gè)可用頁(yè)面。

root@localhost : test 02:42:58>insert into tt values(name,age,address) values('aaa',23,'HZZZ');

疑惑：為什么沒(méi)有申請(qǐng)區(qū)？區(qū)是64個(gè)連續(xù)的頁(yè)，大小1M。那么表大小也應(yīng)該是至少1M。但是現(xiàn)在只有96K（默認(rèn)）。原因是因?yàn)槊總€(gè)段開(kāi)始的時(shí)候，先有32個(gè)頁(yè)大小的碎片頁(yè)存放數(shù)據(jù)，使用
完之后才是64頁(yè)的連續(xù)申請(qǐng)，最多每次可以申請(qǐng)4個(gè)區(qū)，保證數(shù)據(jù)的順序。這里看出表大小增加是按照至少64頁(yè)的大小的空間來(lái)增加的，即1M增加。
驗(yàn)證：
填充數(shù)據(jù)，寫(xiě)滿(mǎn)這32個(gè)碎片頁(yè)，32*16 = 512K?？纯词欠衲苌暾?qǐng)大于1M的空間。

View Code 

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# ls -lh /var/lib/mysql/test/tt.ibd 
-rw-rw---- 1 mysql mysql 576K 2012-10-17 15:30 /var/lib/mysql/test/tt.ibd
root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/tt.ibd -v
page offset 00000000, page type File Space Header>
page offset 00000001, page type Insert Buffer Bitmap>
page offset 00000002, page type File Segment inode>
page offset 00000003, page type B-tree Node>, page level 0001>
page offset 00000004, page type B-tree Node>, page level 0000>
page offset 00000005, page type B-tree Node>, page level 0000>
page offset 00000006, page type B-tree Node>, page level 0000>
page offset 00000007, page type B-tree Node>, page level 0000>
page offset 00000008, page type B-tree Node>, page level 0000>
page offset 00000009, page type B-tree Node>, page level 0000>
page offset 0000000a, page type B-tree Node>, page level 0000>
page offset 0000000b, page type B-tree Node>, page level 0000>
page offset 0000000c, page type B-tree Node>, page level 0000>
page offset 0000000d, page type B-tree Node>, page level 0000>
page offset 0000000e, page type B-tree Node>, page level 0000>
page offset 0000000f, page type B-tree Node>, page level 0000>
page offset 00000010, page type B-tree Node>, page level 0000>
page offset 00000011, page type B-tree Node>, page level 0000>
page offset 00000012, page type B-tree Node>, page level 0000>
page offset 00000013, page type B-tree Node>, page level 0000>
page offset 00000014, page type B-tree Node>, page level 0000>
page offset 00000015, page type B-tree Node>, page level 0000>
page offset 00000016, page type B-tree Node>, page level 0000>
page offset 00000017, page type B-tree Node>, page level 0000>
page offset 00000018, page type B-tree Node>, page level 0000>
page offset 00000019, page type B-tree Node>, page level 0000>
page offset 0000001a, page type B-tree Node>, page level 0000>
page offset 0000001b, page type B-tree Node>, page level 0000>
page offset 0000001c, page type B-tree Node>, page level 0000>
page offset 0000001d, page type B-tree Node>, page level 0000>
page offset 0000001e, page type B-tree Node>, page level 0000>
page offset 0000001f, page type B-tree Node>, page level 0000>
page offset 00000020, page type B-tree Node>, page level 0000>
page offset 00000021, page type B-tree Node>, page level 0000>
page offset 00000022, page type B-tree Node>, page level 0000>
page offset 00000023, page type B-tree Node>, page level 0000>
Total number of page: 36:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 33
File Segment inode: 1

"額外"頁(yè)：4個(gè)
page offset 00000000, page type File Space Header> :文件頭空間頁(yè)
page offset 00000001, page type Insert Buffer Bitmap>：插入緩存位圖頁(yè)
page offset 00000002, page type File Segment inode>：文件段節(jié)點(diǎn)
page offset 00000003, page type B-tree Node>, page level 0001>：根頁(yè)
碎片頁(yè)：32個(gè)
page type B-tree Node>, page level 0000>
總共36個(gè)頁(yè)，ibd大小 576K的由來(lái)：32*16=512K（碎片頁(yè)）+ 4*16=64（額外頁(yè)），這里開(kāi)始要是再插入的話(huà)，應(yīng)該申請(qǐng)最少1M的頁(yè)：

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# ls -lh /var/lib/mysql/test/tt.ibd 
-rw-rw---- 1 mysql mysql 2.0M 2012-10-17 16:10 /var/lib/mysql/test/tt.ibd
root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/tt.ibd
Total number of page: 128:
Freshly Allocated Page: 91
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 34
File Segment inode: 1

頁(yè)從36跳到了128，因?yàn)橐呀?jīng)用完了32個(gè)碎片頁(yè)，新的頁(yè)會(huì)采用區(qū)的方式進(jìn)行空間申請(qǐng)。信息中看到有很多可用頁(yè)，正好說(shuō)明這點(diǎn)。

▲溢出行數(shù)據(jù)存放：INNODB存儲(chǔ)引擎是索引組織的，即每頁(yè)中至少有兩行記錄，因此如果頁(yè)中只能存放一行記錄，INNODB會(huì)自動(dòng)將行數(shù)據(jù)放到溢出頁(yè)中。當(dāng)發(fā)生溢出行的時(shí)候，實(shí)際數(shù)據(jù)保存在BLOB頁(yè)中，數(shù)據(jù)頁(yè)只保存數(shù)據(jù)的前768字節(jié)（老的文件格式），新的文件格式（Barracuda）采用完全行溢出的方式，數(shù)據(jù)頁(yè)只保存20個(gè)字節(jié)的指針，BLOB也保存所有數(shù)據(jù)。如何查看表中有溢出行數(shù)據(jù)呢？

root@localhost : test 04:52:34>create table t1 (id int,name varchar(10),memo varchar(8000))engine =innodb default charset utf8;
Query OK, 0 rows affected (0.16 sec)

root@localhost : test 04:53:10>insert into t1 values(1,'zjy',repeat('我',8000));
Query OK, 1 row affected (0.00 sec)

查看ibd：

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/t1.ibd -v
page offset 00000000, page type File Space Header>
page offset 00000001, page type Insert Buffer Bitmap>
page offset 00000002, page type File Segment inode>
page offset 00000003, page type B-tree Node>, page level 0000>
page offset 00000004, page type Uncompressed BLOB Page>
page offset 00000005, page type Uncompressed BLOB Page>
Total number of page: 6:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 2
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

從信息中看到，剛才插入的一行記錄，已經(jīng)溢出了，保存到了2個(gè)BLOB頁(yè)中（Uncompressed BLOB Page>）。因?yàn)?頁(yè)只有16K，又要存2行數(shù)據(jù)，所以每行記錄最好小于8K，而上面的遠(yuǎn)遠(yuǎn)大于8K，所以被溢出了。當(dāng)然這個(gè)也不是包括特大字段，要是一張表里面有5個(gè)字段都是varchar(512)【多個(gè)varchar的總和大于8K就可以】，也會(huì)溢出：

root@localhost : test 05:08:39>create table t2 (id int,name varchar(1000),address varchar(512),company varchar(200),xx varchar(512),memo varchar(512),dem varchar(1000))engine =innodb default charset utf8;
Query OK, 0 rows affected (0.17 sec)
root@localhost : test 05:08:43>insert into t2 values(1,repeat('周',1000),repeat('我',500),repeat('丁',500),repeat('啊',500),repeat('噢',500),repeat('阿a',500));

1000+500+500+500+500+500=3500*3>8000字節(jié)；行會(huì)被溢出：

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/t2.ibd -v
page offset 00000000, page type File Space Header>
page offset 00000001, page type Insert Buffer Bitmap>
page offset 00000002, page type File Segment inode>
page offset 00000003, page type B-tree Node>, page level 0000>
page offset 00000004, page type Uncompressed BLOB Page>
page offset 00000000, page type Freshly Allocated Page>
Total number of page: 6:
Insert Buffer Bitmap: 1
Freshly Allocated Page: 1
File Segment inode: 1
B-tree Node: 1
File Space Header: 1
Uncompressed BLOB Page: 1

Uncompressed BLOB Page> 頁(yè)存放真正的數(shù)據(jù)，那數(shù)據(jù)頁(yè)到底存放什么？用hexdump查看：

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# hexdump -C -v /var/lib/mysql/test/t1.ibd > t1.txt

查看ibd：

View Code 

3082 0000c090 00 32 01 10 80 00 00 01 7a 6a 79 e6 88 91 e6 88 |.2......zjy.....|
3083 0000c0a0 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3084 0000c0b0 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3085 0000c0c0 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3086 0000c0d0 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3087 0000c0e0 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3088 0000c0f0 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3089 0000c100 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3090 0000c110 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3091 0000c120 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3092 0000c130 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3093 0000c140 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3094 0000c150 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3095 0000c160 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3096 0000c170 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3097 0000c180 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3098 0000c190 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3099 0000c1a0 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3100 0000c1b0 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3101 0000c1c0 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3102 0000c1d0 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3103 0000c1e0 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3104 0000c1f0 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3105 0000c200 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3106 0000c210 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3107 0000c220 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3108 0000c230 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3109 0000c240 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3110 0000c250 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3111 0000c260 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3112 0000c270 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3113 0000c280 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3114 0000c290 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3115 0000c2a0 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3116 0000c2b0 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3117 0000c2c0 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3118 0000c2d0 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3119 0000c2e0 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3120 0000c2f0 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3121 0000c300 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3122 0000c310 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3123 0000c320 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3124 0000c330 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3125 0000c340 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3126 0000c350 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3127 0000c360 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 |................|
3128 0000c370 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 |................|
3129 0000c380 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 88 91 e6 |................|
3130 0000c390 88 91 e6 88 91 e6 88 91 e6 88 91 00 00 02 1c 00 |................|

文本中剛好是48行，每行16字節(jié)。48*16=768字節(jié)，剛好驗(yàn)證了之前說(shuō)的：數(shù)據(jù)頁(yè)只保存數(shù)據(jù)的前768字節(jié)（老的文件格式）。

總結(jié)1:
通過(guò)上面的信息，可以能清楚的知道ibd表空間各個(gè)頁(yè)的分布和利用信息以及表空間大小增加的步長(zhǎng)；特別注意的是溢出行，一個(gè)頁(yè)中至少包含2行數(shù)據(jù)，如果頁(yè)中存放的行數(shù)越多，性能就越好。

************************************
************************************

目的2：
了解表空間如何存儲(chǔ)數(shù)據(jù)，以及對(duì)NULL值的存儲(chǔ)。

測(cè)試2：
在測(cè)試前先了解INNODB的存儲(chǔ)格式（row_format）。老格式（Antelope）：Compact默認(rèn)>，Redumdant；新格式（Barracuda）：Compressed，Dynamic。
這里測(cè)試指針對(duì)默認(rèn)的存儲(chǔ)格式。
Compact行記錄方式如下:

 |變長(zhǎng)字段長(zhǎng)度列表（1~2字節(jié)）|NULL標(biāo)志位（1字節(jié)）|記錄頭信息（5字節(jié)）|RowID（6字節(jié)）|事務(wù)ID（6字節(jié)）|回滾指針（7字節(jié)）|

上面信息除了 "NULL標(biāo)志位"[表中所有字段都定義為NOT NULL]，"RowID"[表中有主鍵] ，"變長(zhǎng)字段長(zhǎng)度列表" [沒(méi)有變長(zhǎng)字段] 可能不存在外，其他信息都會(huì)出現(xiàn)。所以一行數(shù)據(jù)除了列數(shù)據(jù)所占用的字段外，還需要額外18字節(jié)。

一:字段全NULL

mysql> create table mytest(t1 varchar(10),t2 varchar(10),t3 varchar(10) ,t4 varchar(10))engine=innodb charset = latin1 row_format=compact;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into mytest values('a','bb','bb','ccc');
Query OK, 1 row affected (0.02 sec)

mysql> insert into mytest values('a','ee','ee','fff');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mytest values('a',NULL,NULL,'fff');
Query OK, 1 row affected (0.00 sec)

測(cè)試數(shù)據(jù)準(zhǔn)備完之后，執(zhí)行shell命令：

root@zhoujy:/usr/local/mysql/test# hexdump -C -v mytest.ibd > /home/zhoujy/mytest.txt

打開(kāi)mytest.txt文件找到supremum這一行：

0000c070 73 75 70 72 65 6d 75 6d 03 02 02 01 00 00 00 10 |supremum........| ----------->一行，16字節(jié)
0000c080 00 25 00 00 00 03 b9 00 00 00 00 02 49 01 82 00 |.%..........I...|
0000c090 00 01 4a 01 10 61 62 62 62 62 63 63 63 03 02 02 |..J..abbbbccc...|
0000c0a0 01 00 00 00 18 00 23 00 00 00 03 b9 01 00 00 00 |......#.........|
0000c0b0 02 49 02 83 00 00 01 4b 01 10 61 65 65 65 65 66 |.I.....K..aeeeef|
0000c0c0 66 66 03 01 06 00 00 20 ff a6 00 00 00 03 b9 02 |ff..... ........|
0000c0d0 00 00 00 02 49 03 84 00 00 01 4c 01 10 61 66 66 |....I.....L..aff|
0000c0e0 66 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |f...............|

解釋?zhuān)?br /> 第一行數(shù)據(jù):
03 02 02 01/*變長(zhǎng)字段*/ ---- 表中4個(gè)字段類(lèi)型為varchar，并且沒(méi)有NULL數(shù)據(jù)，而且每個(gè)字段君小于255。
00 /*NULL標(biāo)志位，第一行沒(méi)有null的數(shù)據(jù)*/
00 00 10 00 25 /*記錄頭信息,固定5個(gè)字節(jié)*/
00 00 00 03 b9 00/*RowID,固定6個(gè)字節(jié),表沒(méi)有主鍵*/
00 00 00 02 49 01 /*事務(wù)ID,固定6個(gè)字節(jié)*/
82 00 00 01 4a 01 10 /*回滾指針,固定7個(gè)字節(jié)*/
61 62 62 62 62 63 63 63/*列的數(shù)據(jù)*/
第二行數(shù)據(jù)和第一行數(shù)據(jù)一樣（顏色匹配）。
第三行數(shù)據(jù)（有NULL值）和第一行的解釋的顏色對(duì)應(yīng)起來(lái)比較差別:

03 02 02 01 VS 03 01 ----------當(dāng)值為NULL時(shí)，變長(zhǎng)字段列表不會(huì)占用存儲(chǔ)空間。
61 62 62 62 62 63 63 63 VS 61 66 66 66 --------- NULL值沒(méi)有存儲(chǔ)，不占空間

結(jié)論：當(dāng)值為NULL時(shí)，變長(zhǎng)字段列表不會(huì)占用存儲(chǔ)空間。NULL值沒(méi)有存儲(chǔ)，不占空間，但是需要一個(gè)標(biāo)志位（一行一個(gè)）。

二:字段全NOT NULL

mysql> create table mytest(t1 varchar(10) NOT NULL,t2 varchar(10) NOT NULL,t3 varchar(10) NOT NULL,t4 varchar(10) NOT NULL)engine=innodb charset = latin1 row_format=compact;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into mytest values('a','bb','bb','ccc');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mytest values('a','ee','ee','fff');
Query OK, 1 row affected (0.01 sec)

mysql> insert into mytest values('a',NULL,NULL,'fff');
ERROR 1048 (23000): Column 't2' cannot be null

步驟和上面一樣，得到的ibd的結(jié)果是：

0000c070 73 75 70 72 65 6d 75 6d 03 02 02 01 00 00 10 00 |supremum........|
0000c080 24 00 00 00 03 b9 03 00 00 00 02 49 07 87 00 00 |$..........I....|
0000c090 01 4f 01 10 61 62 62 62 62 63 63 63 03 02 02 01 |.O..abbbbccc....|
0000c0a0 00 00 18 ff cb 00 00 00 03 b9 04 00 00 00 02 49 |...............I|
0000c0b0 08 88 00 00 01 50 01 10 61 65 65 65 65 66 66 66 |.....P..aeeeefff|

和上面比較,發(fā)現(xiàn)少了NULL的標(biāo)志位信息。
結(jié)論: NULL值會(huì)有額外的空間來(lái)存儲(chǔ)，即每行1字節(jié)的大小。對(duì)于相同數(shù)據(jù)的表，字段中有NULL值的表比NOT NULL的大。

三:1個(gè)NULL，和1個(gè)''的數(shù)據(jù):

mysql> create table mytest(t1 varchar(10) NOT NULL,t2 varchar(10) NOT NULL DEFAULT '',t3 varchar(10) NOT NULL ,t4 varchar(10))engine=innodb charset = latin1 row_format=compact;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into mytest(t1,t2) values('A','BB');
Query OK, 1 row affected, 1 warning (0.01 sec)

步驟和上面一樣，得到的ibd的結(jié)果是：

0000c070 73 75 70 72 65 6d 75 6d 00 02 01 01 00 00 10 ff |supremum........|
0000c080 ef 00 00 00 43 b9 03 00 00 00 02 4a 15 90 00 00 |....C......J....|
0000c090 01 c2 01 10 41 42 42 00 00 00 00 00 00 00 00 00 |....ABB.........|

和上面2個(gè)區(qū)別主要在于變長(zhǎng)列表和列數(shù)據(jù)這里。

結(jié)論：列數(shù)據(jù)信息里表明了 NULL數(shù)據(jù)和''數(shù)據(jù)都不占用任何空間，對(duì)于變長(zhǎng)字段列表的信息，和一對(duì)比得出：‘'數(shù)據(jù)雖然不需要占用任何存儲(chǔ)空間，但是在變長(zhǎng)字段列表里面還是需要占用一個(gè)字節(jié)畢竟還是一個(gè)‘'值>，NULL值不需要占用”，只是NULL會(huì)有額外的一個(gè)標(biāo)志位，所以能有個(gè)優(yōu)化的說(shuō)法：“數(shù)據(jù)庫(kù)表中能設(shè)置NOT NULL的就盡量設(shè)置為NOT NULL，除非確實(shí)需要NULL值得?！?在此得到了證明。

上面的測(cè)試都是針對(duì)VARCHAR的變長(zhǎng)類(lèi)型，那對(duì)于CHAR呢？

CHAR 測(cè)試：

root@localhost : test 10:33:35>create table mytest(t1 char(10),t2 char(10),t3 char(10) ,t4 char(10))engine=innodb charset = latin1 row_format=compact;Query OK, 0 rows affected (0.16 sec)

root@localhost : test 10:33:59>insert into mytest values('a','bb','bb','ccc');
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:34:09>insert into mytest values('a','ee','ee','fff');
Query OK, 1 row affected (0.00 sec)

root@localhost : test 10:34:19>insert into mytest values('a',NULL,NULL,'fff');
Query OK, 1 row affected (0.00 sec)

打開(kāi)ibd生成的文件：

0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 00 00 00 10 00 41 00 00 |supremum.....A..|
0000c080 00 0a f5 00 00 00 00 81 2d 07 80 00 00 00 32 01 |........-.....2.|
0000c090 10 61 20 20 20 20 20 20 20 20 20 62 62 20 20 20 |.a   bb |
0000c0a0 20 20 20 20 20 62 62 20 20 20 20 20 20 20 20 63 |  bb  c|
0000c0b0 63 63 20 20 20 20 20 20 20 00 00 00 18 00 41 00 |cc  .....A.|
0000c0c0 00 00 0a f5 01 00 00 00 81 2d 08 80 00 00 00 32 |.........-.....2|
0000c0d0 01 10 61 20 20 20 20 20 20 20 20 20 65 65 20 20 |..a   ee |
0000c0e0 20 20 20 20 20 20 65 65 20 20 20 20 20 20 20 20 |  ee  |
0000c0f0 66 66 66 20 20 20 20 20 20 20 06 00 00 20 ff 70 |fff  ... .p|
0000c100 00 00 00 0a f5 02 00 00 00 81 2d 09 80 00 00 00 |..........-.....|
0000c110 32 01 10 61 20 20 20 20 20 20 20 20 20 66 66 66 |2..a   fff|
0000c120 20 20 20 20 20 20 20 00 00 00 00 00 00 00 00 00 |  .........|

和一的varchar比較發(fā)現(xiàn)：少了變長(zhǎng)字段列表，但是對(duì)于char來(lái)講，需要固定長(zhǎng)度來(lái)存儲(chǔ)的，存不到固定長(zhǎng)度，也會(huì)被填充滿(mǎn)。如：20；并且NULL值也不需要占用存儲(chǔ)空間。

混合（varchar,char）:

root@localhost : test 11:21:48>create table mytest(t1 int,t2 char(10),t3 varchar(10) ,t4 char(10))engine=innodb charset = latin1 row_format=compact;
Query OK, 0 rows affected (0.17 sec)

root@localhost : test 11:21:50>insert into mytest values(1,'a','b','c');
Query OK, 1 row affected (0.00 sec)

root@localhost : test 11:22:06>insert into mytest values(11,'aa','bb','cc');
Query OK, 1 row affected (0.00 sec)

從上面的表結(jié)構(gòu)中看出：
1，變長(zhǎng)字段列表長(zhǎng)度：1
2，NULL標(biāo)志位：1
3，記錄頭信息：5
4，RowID：6
5，事務(wù)ID：6
6，回滾指針：7

idb的信息：

0000c070 73 75 70 72 65 6d 75 6d 01 00 00 00 10 00 33 00 |supremum......3.| 
0000c080 00 00 0a f5 07 00 00 00 81 2d 1a 80 00 00 00 32 |.........-.....2|
0000c090 01 10 80 00 00 01 61 20 20 20 20 20 20 20 20 20 |......a   |
0000c0a0 62 63 20 20 20 20 20 20 20 20 20 02 00 00 00 18 |bc   .....|
0000c0b0 ff be 00 00 00 0a f5 08 00 00 00 81 2d 1b 80 00 |............-...|
0000c0c0 00 00 32 01 10 80 00 00 0b 61 61 20 20 20 20 20 |..2......aa  |
0000c0d0 20 20 20 62 62 63 63 20 20 20 20 20 20 20 20 00 | bbcc  .|

從上信息得出和之前預(yù)料的一樣：因?yàn)楸碇兄挥幸粋€(gè)varchar字段，所以，變長(zhǎng)列表長(zhǎng)度就只有：01
特別注意的是：各個(gè)列數(shù)據(jù)存儲(chǔ)的信息：t1字段為int 類(lèi)型，占用4個(gè)字節(jié)的大小。第一行：80 00 00 01 就是表示 1 數(shù)字；第二行：80 00 00 0b 表示了11的數(shù)字。[select hex(11) == B ]，其他的和上面的例子一樣。

上面都是latin1單字節(jié)字符集的說(shuō)明，那對(duì)于多字節(jié)字符集的情況怎么樣？

root@localhost : test 11:52:10>create table mytest(id int auto_increment,t2 varchar(10),t3 varchar(10) ,t4 char(10),primary key(id))engine=innodb charset = utf8 row_format=compact;
Query OK, 0 rows affected (0.17 sec)

root@localhost : test 11:52:11>insert into mytest(t2,t3,t4) values('bb','bb','ccc');
Query OK, 1 row affected (0.00 sec)

root@localhost : test 11:55:34>insert into mytest(t2,t3,t4) values('我們','他們','我們的');
Query OK, 1 row affected (0.00 sec)

ibd信息如下：

0000c070 73 75 70 72 65 6d 75 6d 0a 02 02 00 00 00 10 00 |supremum........|
0000c080 28 80 00 00 01 00 00 00 81 2d 27 80 00 00 00 32 |(........-'....2|
0000c090 01 10 62 62 62 62 63 63 63 20 20 20 20 20 20 20 |..bbbbccc  |
0000c0a0 0a 06 06 00 00 00 18 ff c7 80 00 00 02 00 00 00 |................|
0000c0b0 81 2d 28 80 00 00 00 32 01 10 e6 88 91 e4 bb ac |.-(....2........|
0000c0c0 e4 bb 96 e4 bb ac e6 88 91 e4 bb ac e7 9a 84 20 |............... |

因?yàn)楸碛辛酥麈I，所以ROWID（6字節(jié)）不見(jiàn)了。
特別注意的是：變長(zhǎng)字段列表是3？表里面的varchar類(lèi)型的列只有2個(gè)啊。經(jīng)測(cè)試得出：在多字節(jié)字符集的條件下，char類(lèi)型被當(dāng)成可變長(zhǎng)度的類(lèi)型來(lái)處理，他們的行存儲(chǔ)基本沒(méi)有區(qū)別，所以這個(gè)就出現(xiàn)變長(zhǎng)列表是3了，因?yàn)槭莡tf8字符集，占用三個(gè)字節(jié)。所以一個(gè)漢字均占用了一個(gè)頁(yè)中3個(gè)字節(jié)的空間(”我們“ ：e6 88 91 e4 bb ac)。
數(shù)據(jù)列的信息：
id列的1值，應(yīng)該是 80 00 00 01，為什么這個(gè)顯示00 32 01 10，而且所有的id都是00 32 01 10。測(cè)試發(fā)現(xiàn)，id為自增主鍵的時(shí)候，id的4個(gè)字節(jié)長(zhǎng)度都是以00 32 01 10 表示。否則和前面一個(gè)例子里說(shuō)的，用select HEX（X）表示。

總結(jié)2:
上面的測(cè)試都是基于COMPACT存儲(chǔ)格式的，不管是varchar還是char，NULL值是不需要占用存儲(chǔ)空間的；特別需要注意的是Redumdant的記錄頭信息需要6個(gè)固定字節(jié)；在多字節(jié)字符集的條件下，CHAR和VARCHAR的行存儲(chǔ)基本是沒(méi)有區(qū)別的。

到此這篇關(guān)于MySQL Innodb 存儲(chǔ)結(jié)構(gòu) 和存儲(chǔ)Null值用法詳解的文章就介紹到這了,更多相關(guān)MySQL Innodb 存儲(chǔ)結(jié)構(gòu) 存儲(chǔ)Null值內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL InnoDB ReplicaSet(副本集)簡(jiǎn)單介紹
詳解MySQL InnoDB存儲(chǔ)引擎的內(nèi)存管理
MySQL Innodb關(guān)鍵特性之插入緩沖(insert buffer)
MySQL InnoDB 鎖的相關(guān)總結(jié)
修改MySQL數(shù)據(jù)庫(kù)引擎為InnoDB的操作
MySQL創(chuàng)建數(shù)據(jù)表時(shí)設(shè)定引擎MyISAM/InnoDB操作
MySQL InnoDB表空間加密示例詳解
MySQL InnoDB架構(gòu)的相關(guān)總結(jié)

標(biāo)簽：阜新鷹潭貴州鎮(zhèn)江日照北京臺(tái)灣合肥

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL Innodb 存儲(chǔ)結(jié)構(gòu) 和存儲(chǔ)Null值用法詳解》，本文關(guān)鍵詞 MySQL,Innodb,存儲(chǔ),結(jié)構(gòu),和,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。