MySQL Innodb ストレージ構造と Null 値の保存の詳細な説明

MySQL Innodb ストレージ構造と Null 値の保存の詳細な説明

背景：

テーブルスペース: すべての INNODB データはテーブルスペース (共有テーブルスペース) に保存されます。innodb_file_per_table がオンになっている場合、各テーブルのデータは個別のテーブルスペース (排他テーブルスペース) に保存されます。
排他的表領域には、データ、インデックス、挿入キャッシュ、データ ディクショナリが含まれます。共有テーブルスペースには、UNDO 情報 (<物理スペース> は再利用されません)、二重書き込みキャッシュ情報、トランザクション情報などが含まれます。
セグメント: テーブル スペースを構成し、ゾーンで構成されます。
範囲: 連続した 64 ページで構成されます。各ページは16K、合計1Mです。大きなデータ セグメントの場合、1 回につき 4 つのゾーンを適用できます。
ページ: INNODB ディスク管理の単位であり、行で構成されます。
行: トランザクション ID、ロールバック ポインター、列情報などが含まれます。

目的1:
表スペースの各ページの情報とオーバーフロー行データの格納について理解します。この本の著者である Jiang Chengyao が作成したツールを通じて: http://code.google.com/p/david-mysql-tools/source/browse/trunk/py_innodb_page_type/
3 つのスクリプト:

py_innodb_page_info.py

コードを表示 

#!/usr/bin/env python 
#エンコーディング=utf-8
mylibをインポートする
sysからargvをインポート
mylibからmyargvをインポートする

__name__ == '__main__' の場合:
 myargv = myargv(argv)
 myargv.parse_cmdline() == 0 の場合:
  合格
 それ以外：
  mylib.get_innodb_page_type(myargv)

pylib.py のサンプル

コードを表示 

エンコーディング=utf-8
インポートOS
インポートを含める
インポートから含める*

テーブルスペース名='D:\\mysql_data\\test\\t.ibd'
変数フィールド数 = 1
NULL_フィールド_カウント = 0

クラスmyargv(オブジェクト):
 def __init__(self, argv):
  自己.argv = argv
  自己.parms = {}
  自己.テーブルスペース = ''

 def parse_cmdline(self):
  argv = 自己.argv
  len(argv) == 1の場合:
   '使用方法: python py_innodb_page_info.py [OPTIONS] tablespace_file' を印刷します
   'その他のオプションについては、python py_innodb_page_info.py -h を使用してください' と出力します
   0を返す
  引数:
   argv[0][0] == '-'の場合:
    argv[0][1] == 'h'の場合:
     self.parms[argv[0]] = ''
     argv = argv[1:]
     壊す
    argv[0][1] == 'v'の場合:
     self.parms[argv[0]] = ''
     argv = argv[1:]
    それ以外：
     自己.parms[argv[0]] = argv[1]
     argv = argv[2:]
   それ以外：
    自己.テーブルスペース = argv[0]
    argv = argv[1:]
  self.parms.has_key('-h')の場合:
   「InnoDB ページ情報を取得」を印刷します
   '使用方法: python py_innodb_page_info.py [OPTIONS] tablespace_file\n' を印刷します
   print '次のオプションを最初の引数として指定できます:'
   '-h help ' を印刷する
   '-o output 結果をファイルに出力' を印刷します
   '-t 表領域ファイルを分析するスレッド番号' を印刷します
   '-v 詳細モード' を印刷する
   0を返す
  戻り値 1

mach_read_from_n(ページ、開始オフセット、長さ)を定義します:
 ret = ページ[開始オフセット:開始オフセット+長さ]
 ret.encode('hex') を返す

get_innodb_page_type(myargv)を定義します。
 f = ファイル(myargv.tablespace、'rb')
 fsize = os.path.getsize(f.name)/INNODB_PAGE_SIZE
 戻り値 = {}
 i が範囲内(fsize)の場合:
  ページ = f.read(INNODB_PAGE_SIZE)
  ページオフセット = mach_read_from_n(ページ、FIL_PAGE_OFFSET、4)
  ページタイプ = mach_read_from_n(ページ、FIL_PAGE_TYPE、2)
  myargv.parms.has_key('-v')の場合:
   page_type == '45bf'の場合:
    ページレベル = mach_read_from_n(ページ、FIL_PAGE_DATA+PAGE_LEVEL、2)
    「ページ オフセット %s、ページ タイプ <%s>、ページ レベル <%s>」%(page_offset、innodb_page_type[page_type]、page_level) を出力します。
   それ以外：
    「ページオフセット %s、ページタイプ <%s>」%(page_offset,innodb_page_type[page_type]) を出力します
  そうでない場合 ret.has_key(page_type):
   ret[ページタイプ] = 1
  それ以外：
   ret[ページタイプ] = ret[ページタイプ] + 1
 印刷 "ページ総数: %d:"%fsize
 retに入力する場合:
  "%s: %s"%(innodb_page_type[type],ret[type]) を印刷します

含める

コードを表示 

#エンコーディング=utf-8
INNODB_ページサイズ = 16*1024*1024

# ページ上のデータの開始
FIL_ページデータ = 38


FIL_PAGE_OFFSET = 4 # スペース内のページオフセット
FIL_PAGE_TYPE = 24 # ファイルページタイプ

# UNDO ログ セグメントの種類 */
TRX_UNDO_INSERT = 1
TRX_UNDO_UPDATE = 2

# 任意のファイルセグメントのページでは、このオフセットからデータが置かれる可能性があります
FSEG_PAGE_DATA = FIL_PAGE_DATA

# UNDOログのページ上のUNDOログページヘッダーのオフセット
TRX_UNDO_PAGE_HDR = FSEG_PAGE_DATA

PAGE_LEVEL = 26 #インデックスツリー内のノードのレベル。リーフレベルはレベル0です */

innodb_page_type={
 '0000':u'新しく割り当てられたページ',
 '0002':u'元に戻すログページ',
 '0003':u'ファイルセグメント inode',
 '0004':u'バッファ空きリストを挿入',
 '0005':u'バッファビットマップを挿入',
 '0006':u'システムページ',
 '0007':u'トランザクションシステムページ',
 '0008':u'ファイル スペース ヘッダー',
 '0009':u'拡張説明ページ',
 '000a':u'非圧縮BLOBページ',
 '000b':u'最初の圧縮BLOBページ',
 '000c':u'後続の圧縮BLOBページ',
 '45bf':u'Bツリーノード'
 }

innodb_page_direction={
 '0000': '不明(0x0000)',
 '0001': 'ページ左'、
 '0002': 'ページ右'、
 '0003': 'ページ同一記録',
 '0004': 'ページが同じページ'、
 '0005': 'ページ方向なし',
 'ffff': '不明2(0xffff)'
}


INNODB_PAGE_SIZE=1024*16 # InnoDB ページ 16K

テスト1:

root@localhost : テスト 02:26:13>create table tt(id int auto_increment,name varchar(10),age int,address varchar(20),primary key (id))engine=innodb;
クエリは正常、影響を受けた行は 0 行 (0.17 秒)
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
ページ オフセット 00000000、ページ タイプ <ファイル スペース ヘッダー>
ページ オフセット 00000001、ページ タイプ <バッファー ビットマップの挿入>
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0000> --- リーフ ノード ページ オフセット 00000000、ページ タイプ <新しく割り当てられたページ>
ページ オフセット 00000000、ページ タイプ <新しく割り当てられたページ>
総ページ数: 6: 
新しく割り当てられたページ: 2
バッファビットマップを挿入: 1
ファイル スペース ヘッダー: 1
Bツリーノード: 1
ファイルセグメント inode: 1

説明する：
総ページ数: 総ページ数
新しく割り当てられたページ: 利用可能なページ
バッファビットマップの挿入: キャッシュビットマップページを挿入
バッファ空きリストの挿入: キャッシュ空きリストページを挿入
Bツリーノード: データページ
非圧縮 BLOB ページ: バイナリ ラージ オブジェクト ページ。オーバーフロー行を格納するページです。つまり、オーバーフロー ページで取得される情報は、テーブル初期化サイズが 96K であり、これはページの合計数 * 16 で取得されるということです。データページ 1 ページ、フリーページ 2 ページ。

root@localhost : test 02:42:58>tt に値 (名前、年齢、住所) を挿入します。値 ('aaa'、23、'HZZZ');

質問：なぜ応用分野がないのですか？ゾーンは 64 個の連続したページで構成され、サイズは 1 MB です。その場合、テーブルのサイズも少なくとも 1M である必要があります。しかし、現在は 96K (デフォルト) のみです。その理由は、各セグメントの先頭に、データを格納するための断片化されたページが 32 ページあるためです。これらが使い果たされると、64 ページが連続して適用されます。データの順序を保証するために、一度に最大 4 つの領域を適用できます。ここでは、テーブル サイズが少なくとも 64 ページ分のスペース、つまり 1M 増加していることがわかります。
確認する：
32 個のフラグメント ページにデータを入力します (32*16 = 512K)。 1M を超えるスペースを申請できるかどうかを確認してください。

コードを表示 

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
ページ オフセット 00000000、ページ タイプ <ファイル スペース ヘッダー>
ページ オフセット 00000001、ページ タイプ <バッファー ビットマップの挿入>
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0001>
ページ オフセット 00000004、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000005、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000006、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000007、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000008、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000009、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000000a、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000000b、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000000c、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000000d、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000000e、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000000f、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000010、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000011、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000012、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000013、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000014、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000015、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000016、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000017、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000018、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000019、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000001a、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000001b、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000001c、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000001d、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000001e、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 0000001f、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000020、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000021、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000022、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000023、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
総ページ数: 36:
バッファビットマップを挿入: 1
ファイル スペース ヘッダー: 1
Bツリーノード: 33
ファイルセグメント inode: 1

「追加」ページ: 4
ページオフセット 00000000、ページタイプ <ファイルスペースヘッダー> : ファイルヘッダースペースページ
ページオフセット 00000001、ページタイプ <バッファビットマップの挿入>: バッファビットマップページの挿入
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>: ファイル セグメント ノード
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0001>: ルート ページ断片化ページ: 32
ページ タイプ <B ツリー ノード>、ページ レベル <0000>
合計 36 ページあります。ibd サイズ 576K の由来は、32*16=512K (断片化されたページ) + 4*16=64 (追加ページ) です。さらに挿入する場合は、少なくとも 1M ページを申請する必要があります。

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
総ページ数: 128:
新しく割り当てられたページ: 91
バッファビットマップを挿入: 1
ファイル スペース ヘッダー: 1
Bツリーノード: 34
ファイルセグメント inode: 1

ページ サイズは 36 から 128 に増加します。32 の断片化されたページが使用されているため、新しいページではゾーン方式を使用してスペースを申請します。情報に利用可能なページが多数あるという事実は、まさにこの点を証明しています。

▲ オーバーフロー行データ ストレージ: INNODB ストレージ エンジンはインデックス編成されており、各ページには少なくとも 2 行のレコードがあります。したがって、1 ページに 1 行のレコードしか格納できない場合、INNODB は自動的に行データをオーバーフロー ページに配置します。オーバーフロー行が発生すると、実際のデータは BLOB ページに格納され、データ ページにはデータの最初の 768 バイトのみが格納されます (古いファイル形式)。新しいファイル形式 (Barracuda) では、完全な行オーバーフロー方式が採用されており、データ ページには 20 バイトのポインターのみが格納され、BLOB にもすべてのデータが格納されます。テーブルにオーバーフロー行データがあるかどうかを確認するにはどうすればよいでしょうか?

root@localhost : test 04:52:34>create table t1 (id int,name varchar(10),memo varchar(8000))engine = innodb default charset utf8;
クエリは正常、影響を受けた行は 0 行 (0.16 秒)

root@localhost : test 04:53:10>t1 に挿入 values(1,'zjy',repeat('我',8000));
クエリは正常、1 行が影響を受けました (0.00 秒)

ibdを表示:

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/t1.ibd -v
ページ オフセット 00000000、ページ タイプ <ファイル スペース ヘッダー>
ページ オフセット 00000001、ページ タイプ <バッファー ビットマップの挿入>
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000004、ページ タイプ <非圧縮 BLOB ページ>
ページ オフセット 00000005、ページ タイプ <非圧縮 BLOB ページ>
総ページ数: 6:
バッファビットマップを挿入: 1
非圧縮 BLOB ページ: 2
ファイル スペース ヘッダー: 1
Bツリーノード: 1
ファイルセグメント inode: 1

この情報から、挿入されたレコードの行がオーバーフローし、2 つの BLOB ページ (<非圧縮 BLOB ページ>) に保存されていることがわかります。 1 ページは 16K のみで、2 行のデータを格納する必要があるため、レコードの各行は 8K 未満である必要があります。ただし、上記は 8K よりもはるかに大きいため、オーバーフローします。もちろん、これには非常に大きなフィールドは含まれません。テーブルに 5 つのフィールドがあり、そのすべてが varchar(512) である場合 (複数の varchar の合計が 8K を超える場合)、オーバーフローが発生します。

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;
クエリは正常、影響を受けた行は 0 行 (0.17 秒)
root@localhost : test 05:08:43>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 バイト; 行がオーバーフローします:

root@zhoujy:/home/zhoujy/jiaoben/read_ibd# python py_innodb_page_info.py /var/lib/mysql/test/t2.ibd -v
ページ オフセット 00000000、ページ タイプ <ファイル スペース ヘッダー>
ページ オフセット 00000001、ページ タイプ <バッファー ビットマップの挿入>
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000004、ページ タイプ <非圧縮 BLOB ページ>
ページ オフセット 00000000、ページ タイプ <新しく割り当てられたページ>
総ページ数: 6:
バッファビットマップを挿入: 1
新しく割り当てられたページ: 1
ファイルセグメント inode: 1
Bツリーノード: 1
ファイル スペース ヘッダー: 1
非圧縮 BLOB ページ: 1

<非圧縮 BLOB ページ> ページには実際のデータが格納されますが、データ ページには何が保存されるのでしょうか。 hexdump で表示:

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

ibdを表示:

コードを表示 

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 88 91 e6 88 91 e6 |................|
3085 0000c0c0 88 91 e6 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 e6 88 91 |................|
3087 0000c0e0 e6 88 91 e6 88 91 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 91 e6 88 |................|
3089 0000c100 91 e6 88 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 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 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 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 88 91 e6 88 91 e6 |................|
3100 0000c1b0 88 91 e6 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 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 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 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 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 91 e6 88 |................|
3113 0000c280 91 e6 88 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 88 91 e6 |................|
3115 0000c2a0 88 91 e6 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 88 91 e6 88 91 e6 |................|
3118 0000c2d0 88 91 e6 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 e6 88 91 |................|
3120 0000c2f0 e6 88 91 e6 88 91 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 91 e6 88 |................|
3122 0000c310 91 e6 88 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 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 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 バイトです。 48*16=768 バイト。これは、前に述べたことを裏付けるものです。データ ページには、データの最初の 768 バイトのみが格納されます (古いファイル形式)。

要約1:
上記の情報から、ibd テーブルスペース内の各ページの分布と使用率、およびテーブルスペース サイズの増加のステップ サイズが明確にわかります。オーバーフロー行には特に注意してください。ページには少なくとも 2 行のデータが含まれます。ページに格納される行数が多いほど、パフォーマンスは向上します。

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

目的2:
テーブルスペースがデータを格納する方法と NULL 値を格納する方法を理解します。

テスト2:
テストする前に、INNODB のストレージ形式 (row_format) を理解してください。古い形式 (Antelope): コンパクト <デフォルト>、冗長; 新しい形式 (Barracuda): 圧縮、動的。
これはデフォルトのストレージ形式をテストします。
コンパクト行の記録は次のとおりです。

 |可変長フィールド長リスト (1~2 バイト) |NULL フラグ (1 バイト) |レコード ヘッダー情報 (5 バイト) |RowID (6 バイト) |トランザクション ID (6 バイト) |ロールバック ポインター (7 バイト) |

「NULL フラグ」 [テーブル内のすべてのフィールドが NOT NULL として定義されている]、「RowID」 [テーブルに主キーがある]、「可変長フィールド長リスト」 [可変長フィールドがない] は存在しない可能性がありますが、それ以外のすべての情報が表示されます。したがって、データ行には、列データが占めるフィールドに加えて、さらに 18 バイトが必要になります。

1: すべてのフィールドがNULLです

mysql> テーブル mytest(t1 varchar(10),t2 varchar(10),t3 varchar(10) ,t4 varchar(10)) を作成します。engine=innodb charset=latin1 row_format=compact;
クエリは正常、影響を受けた行は 0 行 (0.08 秒)

mysql> mytest に値 ('a'、'bb'、'bb'、'ccc') を挿入します。
クエリは正常、1 行が影響を受けました (0.02 秒)

mysql> mytest に値 ('a'、'ee'、'ee'、'fff') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> mytest に値を挿入します ('a'、NULL、NULL、'fff')。
クエリは正常、1 行が影響を受けました (0.00 秒)

テスト データが準備できたら、次のシェル コマンドを実行します。

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

mytest.txt ファイルを開き、supremum の行を見つけます。

0000c070 73 75 70 72 65 6d 75 6d 03 02 02 01 00 00 00 10 |supremum......| -----------> 1 行、16 バイト 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 00 00 00 |f.............|

説明する：
データの最初の行:
03 02 02 01/*可変長フィールド*/ ---- テーブル内の 4 つのフィールドの型は varchar であり、NULL データはなく、各フィールドは 255 未満です。
00 /*NULLフラグ、最初の行にはNULLデータはありません*/
00 00 10 00 25 /*レコードヘッダー情報、5バイト固定*/
00 00 00 03 b9 00/*RowID、6バイト固定、テーブルに主キーはありません*/
00 00 00 02 49 01 /*トランザクションID、6バイト固定*/
82 00 00 01 4a 01 10 /*ロールバックポインタ、7バイト固定*/
61 62 62 62 62 63 63 63/* 列データ*/
2 行目のデータは 1 行目と同じです (色が一致します)。
3 行目のデータ (NULL 値を含む) と 1 行目の説明の色はまったく異なります。

03 02 02 01 VS 03 01 ----------値が NULL の場合、可変長フィールド リストはストレージ スペースを占有しません。
61 62 62 62 62 63 63 63 VS 61 66 66 66 --------- NULL値は保存されず、スペースを占有しません

結論: 値が NULL の場合、可変長フィールド リストはストレージ スペースを占有しません。 NULL 値は保存されず、スペースを占有しませんが、フラグ ビット (行ごとに 1 つ) が必要です。

2: すべてのフィールドがNULLではない

mysql> テーブル 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;
クエリは正常、影響を受けた行は 0 行 (0.03 秒)

mysql> mytest に値 ('a'、'bb'、'bb'、'ccc') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> mytest に値 ('a'、'ee'、'ee'、'fff') を挿入します。
クエリは正常、1 行が影響を受けました (0.01 秒)

mysql> mytest に値を挿入します ('a'、NULL、NULL、'fff')。
エラー 1048 (23000): 列 't2' は null にできません

手順は上記と同じで、ibd の結果は次のようになります。

0000c070 73 75 70 72 65 6d 75 6d 03 02 02 01 00 00 10 00 |最高......|
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|

上記と比較すると、NULLフラグ情報が欠落していることがわかります。
結論: NULL 値には、保存するための追加のスペース (つまり、行ごとに 1 バイト) が必要になります。同じデータを持つテーブルの場合、フィールドに NULL 値を持つテーブルは NOT NULL 値を持つテーブルよりも大きくなります。

3: NULL 1 つと '' データ 1 つ:

mysql> テーブル 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;
クエリは正常、影響を受けた行は 0 行 (0.02 秒)
mysql> mytest(t1,t2) に値 ('A','BB') を挿入します。
クエリは正常、1 行が影響を受け、1 つの警告 (0.01 秒)

手順は上記と同じで、ibd の結果は次のようになります。

0000c070 73 75 70 72 65 6d 75 6d 00 02 01 01 00 00 10 ff |最高......|
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 つとの主な違いは、可変長リストと列データにあります。

結論: 列データ情報から、NULL データと '' データはスペースを占有しないことがわかります。可変長フィールド リストの情報を比較すると、'' データはストレージ スペースを占有する必要はありませんが、可変長フィールド リストで 1 バイトを占有する必要があります <結局のところ、それはまだ '' 値です>。また、NULL 値は " を占有する必要はありませんが、NULL には追加のフラグ ビットがあるため、最適化ステートメントがあります: "データベース テーブルを NOT NULL に設定できる場合は、NULL が本当に必要な場合を除き、NOT NULL を設定するようにしてください。 ” がここで証明されます。

上記のテストはすべて VARCHAR 可変長型用ですが、CHAR の場合はどうでしょうか?

CHAR テスト:

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; クエリは正常、0 行が影響を受けました (0.16 秒)

root@localhost : test 10:33:59>mytest に値を挿入します ('a','bb','bb','ccc');
クエリは正常、1 行が影響を受けました (0.00 秒)

root@localhost : test 10:34:09>mytest に値を挿入します ('a','ee','ee','fff');
クエリは正常、1 行が影響を受けました (0.00 秒)

root@localhost : test 10:34:19>mytest に値を挿入します ('a'、NULL、NULL、'fff');
クエリは正常、1 行が影響を受けました (0.00 秒)

ibd によって生成されたファイルを開きます:

0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...最小値......|
0000c070 73 75 70 72 65 6d 75 6d 00 00 00 10 00 41 00 00 |最高.....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 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 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 20 66 66 66 |2..a fff|
0000c120 20 20 20 20 20 20 20 00 00 00 00 00 00 00 00 | .........|

varchar と比較すると、可変長フィールドリストは少ないですが、char の場合は保存する固定長が必要であり、固定長が保存できない場合は埋められることがわかります。たとえば、20; や NULL 値はストレージ スペースを占有する必要がありません。

混合 (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;
クエリは正常、影響を受けた行は 0 行 (0.17 秒)

root@localhost : test 11:21:50>mytest に値を挿入します (1,'a','b','c');
クエリは正常、1 行が影響を受けました (0.00 秒)

root@localhost : test 11:22:06>mytest に値を挿入します (11,'aa','bb','cc');
クエリは正常、1 行が影響を受けました (0.00 秒)

上記の表の構造から、次のことがわかります。
1. 可変長フィールドリストの長さ: 1
2. NULLフラグ: 1
3. レコードヘッダー情報: 5
4、行ID: 6
5. 取引ID: 6
6. ロールバックポインタ: 7

IDB情報:

0000c070 73 75 70 72 65 6d 75 6d 01 00 00 00 10 00 33 00 |最高......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 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 .|

上記の情報から、予想どおりであると結論付けることができます。テーブルにはvarcharフィールドが1つしかないため、可変長リストの長さは01のみです。
各列のデータ ストレージに関する次の情報に特に注意してください。t1 フィールドは int 型で、4 バイトを占めます。最初の行: 80 00 00 01 は数字 1 を表し、2 番目の行: 80 00 00 0b は数字 11 を表します。 [select hex(11) == B ]、残りは上記と同じです。

上記は latin1 シングルバイト文字セットの説明ですが、マルチバイト文字セットについてはどうでしょうか?

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;
クエリは正常、影響を受けた行は 0 行 (0.17 秒)

root@localhost : test 11:52:11>mytest(t2,t3,t4) に値を挿入します('bb','bb','ccc');
クエリは正常、1 行が影響を受けました (0.00 秒)

root@localhost : test 11:55:34>mytest(t2,t3,t4) に値 ('we','they','our') を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

ibd情報は次のとおりです。

0000c070 73 75 70 72 65 6d 75 6d 0a 02 02 00 00 00 10 00 |最高......|
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 |............. |

テーブルには主キーがあるため、ROWID (6 バイト) はなくなります。
特に注意してください: 可変長フィールド リストは 3 ですか?テーブルには varchar 列が 2 つだけあります。テストでは、マルチバイト文字セットの条件下では、char 型は可変長型として扱われ、行の格納に基本的に違いがないため、可変長リストは 3 であることがわかりました。これは、3 バイトを占める utf8 文字セットであるためです。したがって、1 つの漢字は 1 ページで 3 バイトのスペースを占有します (「we」: e6 88 91 e4 bb ac)。
データ列情報:
id 列の値 1 は 80 00 00 01 であるはずですが、なぜ 00 32 01 10 と表示され、すべての id が 00 32 01 10 であるのでしょうか。テストの結果、ID が自動増分主キーの場合、ID の 4 バイトの長さは 00 32 01 10 で表されることがわかりました。それ以外の場合は、前の例と同様に、select HEX (X) を使用してそれを表します。

要約2:
上記のテストはすべてCOMPACTストレージ形式に基づいています。varcharであってもcharであっても、NULL値はストレージスペースを占有しません。特に、Redumdantのレコードヘッダー情報には6つの固定バイトが必要であることに注意してください。マルチバイト文字セットの条件下では、CHARとVARCHARの行ストレージに基本的に違いはありません。

これで、MySQL Innodb ストレージ構造と Null 値のストレージに関するこの記事は終了です。MySQL Innodb ストレージ構造と Null 値のストレージの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL の 2 つのテーブル ストレージ構造 MyISAM と InnoDB のパフォーマンス比較テスト
  • InnoDB タイプの MySql によるテーブル構造とデータの復元
  • MySQL InnoDBエンジンのインデックスとストレージ構造の詳細な説明
  • MySQLのInnoDBストレージエンジンのデータページ構造の詳細な説明
  • MySQL InnoDB メモリ構造の詳細

<<:  Docker ベースの Jenkins のデプロイに関する詳細なチュートリアル

>>:  JavaScriptは入力ボックスコンポーネントを実装します

推薦する

JavaScript offsetParent のケーススタディ

1. offsetParentの定義: offsetParentは子要素に最も近い位置に配置された親...

指定したディレクトリに nginx をインストールする方法の例

会社の要件により、異なる場所にある 2 つの nginx サーバーを同じマシンにインストールする必要...

Linux システムで Tomcat のポート 80 を使用する方法

アプリケーションシナリオ多くの場合、Linux サーバーに tomcat や nginx などのソフ...

HTML で選択ドロップダウン ボックスのコンテンツが不完全に表示され、部分的にカバーされる問題の解決策

今日、問題が発生しました。クエリ バーのドロップダウン ボックスの内容が長すぎて、一部が隠れてしまっ...

Ubuntu 20.04 中国語入力方法のインストール手順

この記事では、Google 入力方法をインストールします。実は以前はSogou入力方式を使っていたの...

Vue.js プロジェクトの開始方法

目次1. Node.jsとVue 2. ローカル開発環境でフロントエンドのVueプロジェクトを実行す...

MySQLの手順を完全に削除する

目次1. まずMySQLサーバーを停止する2. MySQLサーバーをアンインストールする3. MyS...

React + Threejs + Swiper パノラマ効果を実現するための完全なコード

パノラマビュー効果を見てみましょう: 住所を表示スクリーンショット: 体験してみると、周囲の環境がぐ...

コンパイル、インストールから設定ファイルの説明まで、中国語でnginxの詳細な説明

この記事では、コンパイルとインストールから設定ファイルの説明まで、Nginx について詳しく紹介しま...

Windows に Docker をインストールする詳細なチュートリアル

ローカルの MySQL バージョンが比較的低いため、最近 MySQL のバージョンをアップグレードす...

ネイティブ js カスタム右クリック メニュー

この記事の例では、参考のためにjsカスタム右クリックメニューの具体的なコードを共有しています。具体的...

Centos での TCPWrappers アクセス制御の実装

1. TCP ラッパーの概要TCP Wrappers は TCP サービス プログラムを「ラップ」し...

MySQL 4 の一般的なマスタースレーブレプリケーションアーキテクチャ

目次1つのマスターと複数のスレーブのレプリケーションアーキテクチャマルチレベルレプリケーションアーキ...

このポイントのJavaScriptの基本

目次これ方法オブジェクト内これを隠した厳密モード要約するJavaScript の this も不思議...

インデックスを設計する際の原則は何ですか? インデックスの障害を回避するにはどうすればよいでしょうか?

目次主キーインデックス頻繁にクエリされるフィールドのインデックスを作成する大きなフィールドのインデッ...