MySQLは長さの大きいvarcharとblobをどのように保存します
40839 ワード
最近、仕事中にMySQLで長さの長いフィールドタイプをどのように保存するかという問題に遭遇し、1週間以上時間をかけて勉強し、記録しました.
MySQLの大まかなロジックストレージ構造この記事では、基本概念としてInnoDBロジックストレージ構造について説明しています.
注意:本文で指すビッグデータは、varchar/varbinay/text/blobを含む長いデータフィールドを指します.
Compact行フォーマット
まず、大きなデータをどのように格納するかを見てみましょう.
テストテーブルを作成し、データを挿入します.
py_を使用しますinnodb_page_info.pyツールを使用して、表のページ分布を表示します.
4ページ目の
データ・ページにデータの一部が格納され、合計768バイトが計算され、残りの部分が外部ストレージ・ページに格納されることがわかります.では、データ・ページと外部ストレージ・ページ、外部ストレージ・ページと外部ストレージ・ページはどのように接続されているのでしょうか.
この行を観察します.
この行は、接頭辞768バイトの末尾です.最後の20バイトに注意:
00 00 00 02:4バイト、外部ストレージページが存在するspace id を表す
00 00 00 04:4バイト、最初の外部ページを表すPage no 00 00 00 00 26:4バイト、値38、blobページのheader を指す
00 00 00 00 00 00 fc fc:8バイトで、カラムの外部ストレージページの合計長さを表します.ここでの値は64764で、接頭辞768を加えるとちょうど65532です.(注意点として、BLOB長を表すのは8バイトですが、実際には4バイトしか使えません.すべてのBLOBフィールドについて、データを格納する最大長は4 GBです.)
次の外部ストレージ・ページのヘッダー情報を確認します.
最初の38バイトはFile Header(InnoDBデータページの詳細については『MySQLテクニカルインサイダーInnoDBストレージエンジン』4.4を参照)で、これは簡単に説明します.
cd c 3 b 6 8 e:4バイト、このページのchecksum.
00 00 00 04:4バイト、ページオフセット、このページは表領域の5ページ目です.
00 00 00 00:4バイト、現在のページの前のページ.このページは
00 00 00 00:4バイト、現在のページの次のページ.このページは
00 00 00 00 00 06 b 8 a 2:8バイト、このページの最後に変更されたログシーケンス位置LSN.
00 0 a:2バイト、ページタイプ、0 x 000 AはBLOBページを表します.
00 00 00 00 00 00 00:8バイト、省略します.
00 00 00 00 02:ページがどの表領域に属するか、ここでは表領域のIDが2であることを指す.
その後は4バイトの
最後の
最後のページの有効データサイズは0 x 00003 d 9 e=15774768+16330*3+15774=65532バイトで、初期挿入データのサイズに合致します.これが最後の
これにより、データページとBLOBページの接続関係が明確にわかります(淘宝データベースの月報の一枚の図を参照してください):
興味深い例をもう一つ見てみましょう.
11個のblobフィールドを持つ新しいテーブルを作成しました.それから各フィールドに1000バイトのデータを挿入して、MySQLは
InnoDBはB+ツリーでデータを整理しており、各行のデータがPageページ全体を占めている場合、B+ツリーは単一チェーンテーブルに劣化するため、InnoDBは1つのPageに2行のデータを含まなければならないことを規定している.つまり,1行のデータがPageに格納されるサイズは約8000バイトである.上の例では、1行のデータに1000バイトのデータが11個あるので、Pageレイヤは必ず置けないので、Pageレイヤに768*11=8448バイトを残して8000バイトを超えているので、MySQLは
では、上記の問題をどのように解決しますか?
行のフォーマットを、次に説明するDynamicフォーマットに変更します.このフォーマットは、20バイトのみで外部ストレージ領域を指します.
複数のblobフィールドを1つのblobフィールドに変換します.複数のフィールドを配列で格納し、json_encodeはblobにパッケージされます.
有効なレコードをテーブルに挿入します.
この行のデータには9つの外部ストレージページがあることがわかりますが、私たちは全部で9列のデータを挿入しました.各列のデータがpageページに置けない場合、単独で1つの外部ストレージページを申請し、互いに外部ストレージページを共有しないのではないでしょうか.ページの構造を見てみましょう.
前述の解析によれば、外部ストレージページは共有されておらず、1つのカラムのデータが1バイト増えても、このバイトは16 KB空間のサイズを独占しており、ストレージ空間を浪費していることがわかります.(もちろん、これは現代のコンピュータにとって問題ではないかもしれませんが、ほほほ).
これだけ言って、Compact形式でビッグデータを格納する欠点をまとめます.
Pageページに768バイトのプレフィックスが存在するため、1つのフィールドを定義でき、11000バイトを格納できますが、11のフィールドを定義することはできません.各フィールドには1000バイトの「バグ」が格納されます.
外部ストレージページは共有されず、1バイト余っても16 KBのページを独占します.
Dynamic行フォーマット
次に、まず、Dynamicがビッグデータをどのように格納しているかを見てみましょう.
compact形式と同じテーブルを作成します.
次のページの分布を見てください.
4ページ目はデータページ、5-9ページ目はバイナリページです.ディスクの4ページ目のデータを直接見てみましょう.
Compactフォーマットとは明らかに異なり、大きなデータがPageページに保存されない場合、Dynamic行フォーマットは768バイトをPageページに残さず、すべての大きなデータを外部ストレージページに配置します.特定のデータ・ページと外部ストレージ・ページの接続関係はCompact形式と同じです.
Dynamic形式の外部ストレージページが、各カラムが外部ストレージスペースを独占しているかどうか、またはCompact形式の実験手順と同じかどうかを見てみましょう.
外部ストレージページのデータを見てみましょう.
はい、他の列を下に見なくてもいいです.Dynamicの外部ストレージページも共有されていません.
しかし、MySQLはなぜこのように設計されているのでしょうか.簡単に実現するためか、チェーンテーブルに沿って有効なデータサイズでblobのすべてのデータを読み取ることができます.複数のフィールドのblobが混在していると、より複雑な設計になる可能性があり、各フィールドのオフセット量などを更新するには、更新するとページデータ管理も面倒になります.私の個人的な推測、ほほほ.
Dynamic形式でビッグデータを格納する特徴をまとめます.
データ・ページが置けない場合、MySQLはビッグ・データをすべて外部ストレージ・ページに配置し、データ・ページは外部ストレージ・ページへのポインタのみを残します.
外部ストレージページは共有されず、1バイト余っても16 KBのページを独占します.
外部ストレージ・ページに列を挿入する基準
1行のデータをデータ・ページに置くことができず、外部ストレージ・ページを申請する必要がある場合、MySQLはどの列のデータを外部ストレージ・ページに置くかを決定する必要があります.ルールは次のとおりです.
固定長フィールドは、外部記憶ページ(int,char(N)等)には配置する.
20バイト未満のフィールドは、外部ストレージページに配置されません.(外部ストレージページに配置すると、16 KBを単独で占有するだけでなく、20バイトのポインタも追加されます.必要ありません)CompactおよびREDUNDANT形式の行データの場合、768バイト未満のフィールドは外部ストレージページに配置されません.(その理由は明らかですが、768バイトの接頭辞が足りないので、無理をしてはいけないのではないでしょうか).
複数のビッグデータフィールドが上記の条件を満たしている場合、外部ストレージページに配置する必要がある場合(例えば7000バイト、6000バイト、外部ストレージページに配置する必要がある場合)、MySQLは、より多くのフィールドをデータページに配置できるように、より大きなフィールドを外部ストレージページに配置することを優先します.
多くの実験過程があるため、比較的に乱れているように見えて、この文章を見て人が自分で実践することを提案して、結局自分で手を出してもっと多くの問題と細部を考えることができて、理解するのも比較的に深くて、ハハハ.
参考資料:http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.htmlhttp://mysqlserverteam.com/externally-stored-fields-in-innodb/https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/
http://mysql.taobao.org/monthly/2016/02/01/
テキストリンク:https://github.com/zhangyachen/zhangyachen.github.io/issues/96
MySQLの大まかなロジックストレージ構造この記事では、基本概念としてInnoDBロジックストレージ構造について説明しています.
注意:本文で指すビッグデータは、varchar/varbinay/text/blobを含む長いデータフィールドを指します.
Compact行フォーマット
まず、大きなデータをどのように格納するかを見てみましょう.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.73 |
+-----------+
1 row in set (0.01 sec)
mysql> show table status like 'row'\G;
*************************** 1. row ***************************
Name: row
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 81920
Data_length: 81920
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-01-04 21:46:02
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)テストテーブルを作成し、データを挿入します.
CREATE TABLE `row` (
`content` varchar(65532) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> insert into row(content) select repeat('a',65532);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0py_を使用しますinnodb_page_info.pyツールを使用して、表のページ分布を表示します.
[root@localhost mysql]# python py_innodb_page_info.py -v com/row.ibd
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>
page offset 00000006, page type <Uncompressed BLOB Page>
page offset 00000007, page type <Uncompressed BLOB Page>
Total number of page: 8:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 4
File Space Header: 1
B-tree Node: 1
File Segment inode: 14ページ目の
, page level <0000> hexdump -Cv row.ibdを使用して、データ・ページ, page level <0000> 3073 0000c000 8c 25 17 57 00 00 00 03 ff ff ff ff ff ff ff ff |.%.W....????????|
3074 0000c010 00 00 00 00 00 07 3a b8 45 bf 00 00 00 00 00 00 |......:?E?......|
3075 0000c020 00 00 00 00 00 02 00 02 03 a6 80 03 00 00 00 00 |.........?......|
3076 0000c030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|
3077 0000c040 00 00 00 00 00 00 00 00 00 13 00 00 00 02 00 00 |................|
3078 0000c050 00 02 00 f2 00 00 00 02 00 00 00 02 00 32 01 00 |...?.........2..|
3079 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|
3080 0000c070 73 75 70 72 65 6d 75 6d 14 c3 00 00 10 ff f1 00 |supremum.?...??.|
3081 0000c080 00 00 00 04 03 00 00 00 00 13 12 80 00 00 00 2d |...............-|
3082 0000c090 01 10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |..aaaaaaaaaaaaaa|
3083 0000c0a0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3084 0000c0b0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3085 0000c0c0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
....
....
3128 0000c370 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3129 0000c380 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3130 0000c390 61 61 00 00 00 02 00 00 00 04 00 00 00 26 00 00 |aa...........&..|
3131 0000c3a0 00 00 00 00 fc fc 00 00 00 00 00 00 00 00 00 00 |....??..........|
3132 0000c3b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
3133 0000c3c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
3134 0000c3d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
...
...
4093 0000ffc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
4094 0000ffd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
4095 0000ffe0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
4096 0000fff0 00 00 00 00 00 70 00 63 01 a1 6c 2b 00 07 3a b8 |.....p.c.?l+..:?|
データ・ページにデータの一部が格納され、合計768バイトが計算され、残りの部分が外部ストレージ・ページに格納されることがわかります.では、データ・ページと外部ストレージ・ページ、外部ストレージ・ページと外部ストレージ・ページはどのように接続されているのでしょうか.
この行を観察します.
3130 0000c390 61 61 00 00 00 02 00 00 00 04 00 00 00 26 00 00 |aa...........&..|
3131 0000c3a0 00 00 00 00 fc fc 00 00 00 00 00 00 00 00 00 00 |................|
この行は、接頭辞768バイトの末尾です.最後の20バイトに注意:
00 00 00 02:4バイト、外部ストレージページが存在するspace id を表す
00 00 00 04:4バイト、最初の外部ページを表すPage no 00 00 00 00 26:4バイト、値38、blobページのheader を指す
00 00 00 00 00 00 fc fc:8バイトで、カラムの外部ストレージページの合計長さを表します.ここでの値は64764で、接頭辞768を加えるとちょうど65532です.(注意点として、BLOB長を表すのは8バイトですが、実際には4バイトしか使えません.すべてのBLOBフィールドについて、データを格納する最大長は4 GBです.)
次の外部ストレージ・ページのヘッダー情報を確認します.
4097 00010000 cd c3 b6 8e 00 00 00 04 00 00 00 00 00 00 00 00 |?ö.............|
4098 00010010 00 00 00 00 00 06 b8 a2 00 0a 00 00 00 00 00 00 |......??........|
4099 00010020 00 00 00 00 00 02 00 00 3f ca 00 00 00 05 61 61 |........??....aa|
4100 00010030 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
...
...
最初の38バイトはFile Header(InnoDBデータページの詳細については『MySQLテクニカルインサイダーInnoDBストレージエンジン』4.4を参照)で、これは簡単に説明します.
cd c 3 b 6 8 e:4バイト、このページのchecksum.
00 00 00 04:4バイト、ページオフセット、このページは表領域の5ページ目です.
00 00 00 00:4バイト、現在のページの前のページ.このページは
00 00 00 00:4バイト、現在のページの次のページ.このページは
00 00 00 00 00 06 b 8 a 2:8バイト、このページの最後に変更されたログシーケンス位置LSN.
00 0 a:2バイト、ページタイプ、0 x 000 AはBLOBページを表します.
00 00 00 00 00 00 00:8バイト、省略します.
00 00 00 00 02:ページがどの表領域に属するか、ここでは表領域のIDが2であることを指す.
その後は4バイトの
00 00 3f caで、ここでの値は16330で、このBLOBページの有効なデータのバイト数を表します.00 00 00 05は次のBLOBページのpage numberを表します.最後の
7169 0001c000 fa 78 9b 27 00 00 00 07 00 00 00 00 00 00 00 00 |?x.'............|
7170 0001c010 00 00 00 00 00 07 3a b8 00 0a 00 00 00 00 00 00 |......:?........|
7171 0001c020 00 00 00 00 00 02 00 00 3d 9e ff ff ff ff 61 61 |........=.????aa|
7172 0001c030 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
7173 0001c040 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
...
...
最後のページの有効データサイズは0 x 00003 d 9 e=15774768+16330*3+15774=65532バイトで、初期挿入データのサイズに合致します.これが最後の
これにより、データページとBLOBページの接続関係が明確にわかります(淘宝データベースの月報の一枚の図を参照してください):
興味深い例をもう一つ見てみましょう.
CREATE TABLE `testblob` (
`blob1` blob NOT NULL,
`blob2` blob NOT NULL,
`blob3` blob NOT NULL,
`blob4` blob NOT NULL,
`blob5` blob NOT NULL,
`blob6` blob NOT NULL,
`blob7` blob NOT NULL,
`blob8` blob NOT NULL,
`blob9` blob NOT NULL,
`blob10` blob NOT NULL,
`blob11` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> insert into testblob select repeat('a',1000),repeat('b',1000),repeat('c',1000),repeat('d',1000),repeat('e',1000),repeat('f',1000),repeat('g',1000),repeat('h',1000),repeat('i',1000),repeat('j',1000),repeat('k',1000);
ERROR 1030 (HY000): Got error 139 from storage engine11個のblobフィールドを持つ新しいテーブルを作成しました.それから各フィールドに1000バイトのデータを挿入して、MySQLは
ERROR 1030 (HY000): Got error 139 from storage engineを提示して、どういう意味ですか?InnoDBはB+ツリーでデータを整理しており、各行のデータがPageページ全体を占めている場合、B+ツリーは単一チェーンテーブルに劣化するため、InnoDBは1つのPageに2行のデータを含まなければならないことを規定している.つまり,1行のデータがPageに格納されるサイズは約8000バイトである.上の例では、1行のデータに1000バイトのデータが11個あるので、Pageレイヤは必ず置けないので、Pageレイヤに768*11=8448バイトを残して8000バイトを超えているので、MySQLは
ERROR 1030 (HY000): Got error 139 from storage engineを提示します.11000バイトを格納するフィールドを簡単に定義しますが、11フィールドに分けて格納することはできません.ちょっと面白いです.では、上記の問題をどのように解決しますか?
行のフォーマットを、次に説明するDynamicフォーマットに変更します.このフォーマットは、20バイトのみで外部ストレージ領域を指します.
複数のblobフィールドを1つのblobフィールドに変換します.複数のフィールドを配列で格納し、json_encodeはblobにパッケージされます.
有効なレコードをテーブルに挿入します.
mysql> insert into testblob(blob1,blob2,blob3,blob4,blob5,blob6,blob7,blob8,blob9) select repeat('a',8000),repeat('b',8000),repeat('c',8000),repeat('d',8000),repeat('e',8000),repeat('f',8000),repeat('g',8000),repeat('h',8000),repeat('i',8000);
Query OK, 1 row affected (0.12 sec)
Records: 1 Duplicates: 0 Warnings: 0[root@localhost mysql]# python py_innodb_page_info.py -v com/testblob.ibd
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>
page offset 00000006, page type <Uncompressed BLOB Page>
page offset 00000007, page type <Uncompressed BLOB Page>
page offset 00000008, page type <Uncompressed BLOB Page>
page offset 00000009, page type <Uncompressed BLOB Page>
page offset 0000000a, page type <Uncompressed BLOB Page>
page offset 0000000b, page type <Uncompressed BLOB Page>
page offset 0000000c, page type <Uncompressed BLOB Page>
Total number of page: 13:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 9
File Space Header: 1
B-tree Node: 1
File Segment inode: 1この行のデータには9つの外部ストレージページがあることがわかりますが、私たちは全部で9列のデータを挿入しました.各列のデータがpageページに置けない場合、単独で1つの外部ストレージページを申請し、互いに外部ストレージページを共有しないのではないでしょうか.ページの構造を見てみましょう.
3130 0000c390 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
3131 0000c3a0 61 61 61 61 00 00 00 05 00 00 00 04 00 00 00 26 |aaaa...........&|
...
...
3180 0000c6b0 62 62 62 62 62 62 62 62 00 00 00 05 00 00 00 05 |bbbbbbbb........|
3181 0000c6c0 00 00 00 26 00 00 00 00 00 00 1c 40 63 63 63 63 |...&.......@cccc|
...
...
3229 0000c9c0 63 63 63 63 63 63 63 63 63 63 63 63 00 00 00 05 |cccccccccccc....|
3230 0000c9d0 00 00 00 06 00 00 00 26 00 00 00 00 00 00 1c 40 |.......&.......@|
...
...
前述の解析によれば、外部ストレージページは共有されておらず、1つのカラムのデータが1バイト増えても、このバイトは16 KB空間のサイズを独占しており、ストレージ空間を浪費していることがわかります.(もちろん、これは現代のコンピュータにとって問題ではないかもしれませんが、ほほほ).
これだけ言って、Compact形式でビッグデータを格納する欠点をまとめます.
Pageページに768バイトのプレフィックスが存在するため、1つのフィールドを定義でき、11000バイトを格納できますが、11のフィールドを定義することはできません.各フィールドには1000バイトの「バグ」が格納されます.
外部ストレージページは共有されず、1バイト余っても16 KBのページを独占します.
Dynamic行フォーマット
次に、まず、Dynamicがビッグデータをどのように格納しているかを見てみましょう.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.14 |
+-----------+
1 row in set (0.00 sec)
mysql> show table status like 'row'\G;
*************************** 1. row ***************************
Name: row
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2017-01-03 22:45:16
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)compact形式と同じテーブルを作成します.
CREATE TABLE `row` (
`content` varchar(65532) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1
insert into row(content) select repeat('a',65532);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0次のページの分布を見てください.
[root@localhost mysql]# python py_innodb_page_info.py -v row.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0000>
page offset 00000004, page type
page offset 00000005, page type
page offset 00000006, page type
page offset 00000007, page type
page offset 00000008, page type
Total number of page: 9:
Insert Buffer Bitmap: 1
Uncompressed BLOB Page: 5
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
4ページ目はデータページ、5-9ページ目はバイナリページです.ディスクの4ページ目のデータを直接見てみましょう.
3073 0000c000 dc 2d b0 f5 00 00 00 03 ff ff ff ff ff ff ff ff |.-..............|
3074 0000c010 00 00 00 00 00 a3 4b 59 45 bf 00 00 00 00 00 00 |......KYE.......|
3075 0000c020 00 00 00 00 00 36 00 02 00 a6 80 03 00 00 00 00 |.....6..........|
3076 0000c030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|
3077 0000c040 00 00 00 00 00 00 00 00 00 64 00 00 00 36 00 00 |.........d...6..|
3078 0000c050 00 02 00 f2 00 00 00 36 00 00 00 02 00 32 01 00 |.......6.....2..|
3079 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|
3080 0000c070 73 75 70 72 65 6d 75 6d 14 c0 00 00 10 ff f1 00 |supremum........|
3081 0000c080 00 00 00 02 00 00 00 00 00 07 07 a7 00 00 01 1b |................|
3082 0000c090 01 10 00 00 00 36 00 00 00 04 00 00 00 26 00 00 |.....6.......&..|
3083 0000c0a0 00 00 00 00 ff fc 00 00 00 00 00 00 00 00 00 00 |................|
3084 0000c0b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
3085 0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
3086 0000c0d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
3087 0000c0e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
...
...
...
Compactフォーマットとは明らかに異なり、大きなデータがPageページに保存されない場合、Dynamic行フォーマットは768バイトをPageページに残さず、すべての大きなデータを外部ストレージページに配置します.特定のデータ・ページと外部ストレージ・ページの接続関係はCompact形式と同じです.
Dynamic形式の外部ストレージページが、各カラムが外部ストレージスペースを独占しているかどうか、またはCompact形式の実験手順と同じかどうかを見てみましょう.
CREATE TABLE `testblob` (
`blob1` blob NOT NULL,
`blob2` blob NOT NULL,
`blob3` blob NOT NULL,
`blob4` blob NOT NULL,
`blob5` blob NOT NULL,
`blob6` blob NOT NULL,
`blob7` blob NOT NULL,
`blob8` blob NOT NULL,
`blob9` blob NOT NULL,
`blob10` blob NOT NULL,
`blob11` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> insert into testblob(blob1,blob2,blob3,blob4,blob5,blob6,blob7,blob8,blob9,blob10,blob11) select repeat('a',8000),repeat('b',8000),repeat('c',8000),repeat('d',8000),repeat('e',8000),repeat('f',8000),repeat('g',8000),repeat('h',8000),repeat('i',8000),repeat('j',8000),repeat('k',8000);
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0外部ストレージページのデータを見てみましょう.
4599 00011f60 61 61 61 61 61 61 61 61 61 61 61 61 61 61 00 00 |aaaaaaaaaaaaaa..|
4600 00011f70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
はい、他の列を下に見なくてもいいです.Dynamicの外部ストレージページも共有されていません.
しかし、MySQLはなぜこのように設計されているのでしょうか.簡単に実現するためか、チェーンテーブルに沿って有効なデータサイズでblobのすべてのデータを読み取ることができます.複数のフィールドのblobが混在していると、より複雑な設計になる可能性があり、各フィールドのオフセット量などを更新するには、更新するとページデータ管理も面倒になります.私の個人的な推測、ほほほ.
Dynamic形式でビッグデータを格納する特徴をまとめます.
データ・ページが置けない場合、MySQLはビッグ・データをすべて外部ストレージ・ページに配置し、データ・ページは外部ストレージ・ページへのポインタのみを残します.
外部ストレージページは共有されず、1バイト余っても16 KBのページを独占します.
外部ストレージ・ページに列を挿入する基準
1行のデータをデータ・ページに置くことができず、外部ストレージ・ページを申請する必要がある場合、MySQLはどの列のデータを外部ストレージ・ページに置くかを決定する必要があります.ルールは次のとおりです.
固定長フィールドは、外部記憶ページ(int,char(N)等)には配置する.
20バイト未満のフィールドは、外部ストレージページに配置されません.(外部ストレージページに配置すると、16 KBを単独で占有するだけでなく、20バイトのポインタも追加されます.必要ありません)CompactおよびREDUNDANT形式の行データの場合、768バイト未満のフィールドは外部ストレージページに配置されません.(その理由は明らかですが、768バイトの接頭辞が足りないので、無理をしてはいけないのではないでしょうか).
複数のビッグデータフィールドが上記の条件を満たしている場合、外部ストレージページに配置する必要がある場合(例えば7000バイト、6000バイト、外部ストレージページに配置する必要がある場合)、MySQLは、より多くのフィールドをデータページに配置できるように、より大きなフィールドを外部ストレージページに配置することを優先します.
多くの実験過程があるため、比較的に乱れているように見えて、この文章を見て人が自分で実践することを提案して、結局自分で手を出してもっと多くの問題と細部を考えることができて、理解するのも比較的に深くて、ハハハ.
参考資料:http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.htmlhttp://mysqlserverteam.com/externally-stored-fields-in-innodb/https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/
http://mysql.taobao.org/monthly/2016/02/01/
テキストリンク:https://github.com/zhangyachen/zhangyachen.github.io/issues/96