MySQL のバイナリコンテンツから InnoDB 行形式を読み取る方法-mysql チュートリアル-php.cn

この記事では、mysql に関する関連知識を提供します。主に、バイナリコンテンツからの InnoDB 行フォーマットに関する関連問題を紹介します。InnoDB は、テーブル内のデータをディスクに保存するシステムです。ストレージエンジンなので、データがシャットダウンして再起動してもまだ存在します。

推奨学習: mysql ビデオチュートリアル

InnoDB はディスク上のテーブルにデータを保存するストレージエンジンであるため、シャットダウンした後でもダウンして再起動しても、データはまだ存在します。データ処理の実際のプロセスはメモリ内で発生するため、ディスク内のデータをメモリにロードする必要があります。書き込みまたは変更要求を処理している場合は、メモリ内の内容もディスクに更新する必要があります。また、ディスクへの読み取りおよび書き込みの速度は非常に遅いことがわかっており、これはメモリ内での読み取りおよび書き込みとは数桁異なります。したがって、テーブルから特定のレコードを取得したい場合、InnoDB ストレージエンジンは読み取りを行う必要がありますか?ディスクからレコードを 1 つずつ取り出しますか?

InnoDB で採用されている方法は、データを複数のページに分割し、ページをディスクとメモリ間の対話の基本単位として使用することです。InnoDB のページのサイズは通常 16KB です。つまり、通常の状況では、一度に少なくとも 16 KB のコンテンツがディスクからメモリに読み取られ、メモリ内の少なくとも 16 KB のコンテンツが一度にディスクにリフレッシュされます。

mysql> show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

ログイン後にコピー

通常、データはレコード単位でテーブルに挿入されます。これらのレコードがディスク上に格納される方法は、行形式またはレコード形式とも呼ばれます。 InnoDB ストレージエンジンは、コンパクト、冗長、動的、圧縮行フォーマットという 4 つの異なるタイプの行フォーマットを設計しました。

行レコード形式の分類と導入

初期の InnoDB バージョンでは、ファイル形式が 1 つしかなかったため、このファイル形式に名前を付ける必要はありませんでした。 InnoDB エンジンが進化するにつれて、新機能をサポートするために、以前のバージョンと互換性のない新しいファイル形式が開発されています。アップグレードおよびダウングレードの状況でのシステム互換性の管理、および異なる MySQL バージョンの実行を支援するために、InnoDB は名前付きファイル形式の使用を開始しました。

msyql 5.7.9 以降のバージョンでは、デフォルトの行形式は innodb_default_row_format 変数によって決定され、そのデフォルト値は動的です:

mysql> show variables like "innodb_file_format";
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| innodb_file_format | Barracuda |
+--------------------+-----------+
1 row in set (0.01 sec)

mysql> show variables like "innodb_default_row_format";
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)

ログイン後にコピー

View行形式を使用する現在のテーブル:

mysql> show table status like 'dept_emp'\G*************************** 1. row ***************************
           Name: dept_emp         Engine: InnoDB
        Version: 10
     Row_format: Dynamic           Rows: 331570
 Avg_row_length: 36
    Data_length: 12075008Max_data_length: 0
   Index_length: 5783552
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-08-11 09:04:36
    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_FORMAT=行格式名称ALTER TABLE 表名 ROW_FORMAT=行格式名称;

ログイン後にコピー

既存のテーブルの行モードを圧縮または動的に変更する場合は、最初にファイル形式を Barracuda に設定する必要があります: set global innodb_file_format=Barracuda; 次に、ALTER TABLE tablename ROW_FORMAT=COMPRESSED; を使用して変更して有効にします。

#行形式

COMPACT

可変長フィールドリスト

MySQL はいくつかの可変長データ型をサポートしています, VARCHAR(M)、VARBINARY(M)、さまざまな TEXT 型、さまざまな BLOB 型などです。これらのデータ型を持つ列を可変長フィールドと呼ぶこともできます。可変長フィールドには何バイトのデータが格納されますか?は固定されていないため、実際のデータを保存するときは、これらのデータが占めるバイト数を保存する必要があります。変数フィールドに格納できる最大バイト数 (M × W) が 255 バイトを超え、実際に格納されるバイト数 (L) が 127 バイトを超える場合は、2 バイトを使用して記録し、それ以外の場合は 1 バイトのレコードを使用します。

質問 1: では、なぜ分割線として 128 を使用するのでしょうか? 1 バイトで 255 まで表現できますが、MySQL が長さ表現を設計する際、長さを表すバイトであるかどうかを区別するために、最上位ビットが 1 の場合は 2 バイトで長さを表し、それ以外の場合は 2 バイトで長さを表すと規定されています。は 1 バイトです。たとえば、01111111 の場合、これは長さが 127 であることを意味し、長さが 128 の場合は 2 バイト (10000000 10000000) が必要になります。最初のバイトの最上位ビットは 1 であり、これはデータを表す 2 バイトの始まりです。 2 番目のバイトは長さを表すためにすべてのビットを使用できますが、MySQL はリトルエンディアンのカウント方法を採用しており、下位ビットが最初で上位ビットが最後であるため、129 は 10000001 10000000 となります。同時に、この識別方法の最大長は 2^15-1=32767、つまり 32KB です。

質問 2: 長さを表すのに 2 バイトでは不十分な場合はどうすればよいですか?デフォルトの innoDB ページサイズは 16 KB です。たとえば、非常に多くのバイトを占める一部のフィールドの場合、フィールドの長さは 16 KB を超えます。レコードを 1 つのページに格納できない場合、InnoDB はレコードの一部を格納します。ページ内では、このページに残った長さだけが可変長フィールド長リストに格納されるため、2バイトで格納できます。このオーバーフローページメカニズムは、後でデータオーバーフローを指します。

NULL 値リスト

テーブル内の一部の列には NULL 値が格納される場合があります。これらの NULL 値がレコードの実データに格納されると、多くのスペースを占有することになるため、Compact行形式では、これらの値を NULL 列として均一に管理し、NULL 値リストに格納します。 NULL を格納できる各カラムはバイナリビットに対応しており、バイナリビットの値が 1 の場合、そのカラムの値は NULL であることを意味します。バイナリビット値が 0 の場合、列の値が NULL ではないことを意味します。

レコードヘッダー情報

レコードを説明するために使用されるレコードヘッダー情報。固定の 5 バイトで構成されます。 5 バイトは 40 バイナリビットであり、異なるビットは異なる意味を表します。

#フィールド長さ (ビット)説明予約ビット 11未使用予約ビット 21未使用使用#delete_maskmin_rec_mask##n_owned 4カレントレコードが所有するレコード数を示しますheap_no13カレントレコードの位置情報を示しますページ上のレコード record_type3 は現在のレコードのタイプを表し、0 は通常のレコードを表し、1 は B ツリーを表します非リーフノードレコード。2 は最小レコードを表し、3 は最大レコードを表します。next_record16次のレコードの相対位置を示します。記録＃＃＃＃＃＃＃＃＃＃＃＃

隐藏列

记录的真实数据除了我们自己定义的列的数据以外，MySQL会为每个记录默认的添加一些列（也称为隐藏列），包括：

DB_ROW_ID(row_id)：非必须，6字节，表示行ID，唯一标识一条记录
DB_TRX_ID：必须，6字节，表示事务ID
DB_ROLL_PTR：必须，7字节，表示回滚指针

InnoDB表对主键的生成策略是：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键，如果表中连Unique 键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。

DB_TRX_ID（也可以称为trx_id）和DB_ROLL_PTR（也可以称为roll_ptr）这两个列是必有的，但是row_id是可选的（在没有自定义主键以及Unique 键的情况下才会添加该列）。

其他的行格式和Compact行格式差别不大。

Redundant行格式

Redundant行格式是MySQL5.0之前用的一种行格式，不予深究。

Dynamic行格式

MySQL5.7的默认行格式就是Dynamic，Dynamic行格式和Compact行格式挺像，只不过在处理行溢出数据时有所不同。

Compressed行格式

Compressed行格式在Dynamic行格式的基础上会采用压缩算法对页面进行压缩，以节省空间。以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度数据能够进行有效的存储（减少40%，但对CPU要求更高）。

数据溢出

如果我们定义一个表，表中只有一个VARCHAR字段，如下：

CREATE TABLE test_varchar( c VARCHAR(60000))

ログイン後にコピー

然后往这个字段插入60000个字符，会发生什么？前边说过，MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的情况。

在Compact和Redundant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的该列的前768个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用20个字节（768字节后20个字节）存储指向这些页的地址。这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

Dynamic和Compressed行格式，不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

实战分析行格式

准备表及数据：

create table row_test (
　　t1 varchar(10),
　　t2 varchar(10),
　　t3 char(10),
　　t4 varchar(10)
) engine=innodb charset=latin1 row_format=compact;

insert into row_test values('a','bb','bb','ccc'); 
insert into row_test values('d','ee','ee','fff'); 
insert into row_test values('d',NULL,NULL,'fff');

ログイン後にコピー

在Linux环境下，使用hexdump -C -v mytest.ibd＞mytest.txt，打开mytest.txt文件，找到如下内容：

0000c070  73 75 70 72 65 6d 75 6d  03 02 01 00 00 00 10 00  |supremum........|
0000c080  2c 00 00 00 00 02 00 00  00 00 00 0f 61 c8 00 00  |,...........a...|
0000c090  01 d4 01 10 61 62 62 62  62 20 20 20 20 20 20 20  |....abbbb       |
0000c0a0  20 63 63 63 03 02 01 00  00 00 18 00 2b 00 00 00  | ccc........+...|
0000c0b0  00 02 01 00 00 00 00 0f  62 c9 00 00 01 b2 01 10  |........b.......|
0000c0c0  64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  |deeee        fff|
0000c0d0  03 01 06 00 00 20 ff 98  00 00 00 00 02 02 00 00  |..... ..........|
0000c0e0  00 00 0f 67 cc 00 00 01  b6 01 10 64 66 66 66 00  |...g.......dfff.|

ログイン後にコピー

该行记录从0000c078开始，第一行整理如下：

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第一行没有NULL值
00 00 10 00 2c // 记录头信息，固定5字节长度
00 00 00 2b 68 00 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 06 05 // 事务ID，固定6个字节80 00 00 00 32 01 10 // 回滚指针，固定7个字节61 
// t1数据'a'62 62 
// t2'bb'62 62 20 20 20 20 20 20 20 20 // t3数据'bb'63 63 63 // t4数据'ccc'

ログイン後にコピー

第二行整理如下：

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第二行没有NULL值
00 00 18 00 2b // 记录头信息，固定5字节长度
00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 62 // 事务ID，固定6个字节
c9 00 00 01 b2 01 10 // 回滚指针，固定7个字节64 // t1数据'd'65 65 
// t2数据'ee'65 65 20 20 20 20 20 20 20 20 // t3数据'ee'66 66 66 
// t4数据'fff'

ログイン後にコピー

第三行整理如下：

03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1
06 // 00000110 NULL标志位，t2和t3列为空
00 00 20 ff 98  // 记录头信息，固定5字节长度
00 00 00 00 02 02 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 67 // 事务ID，固定6个字节
cc 00 00 01 b6 01 10 // 回滚指针，固定7个字节64 // t1数据'd'66 66 66 // t4数据'fff'

ログイン後にコピー

接下来更新下数据：

mysql> update row_test set t2=null where t1='a';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from row_test where t2='ee';
Query OK, 1 row affected (0.01 sec)

ログイン後にコピー

查看二进制内容（需要等一会，有可能只写入了缓存，磁盘上的文件并没有更新）：

0000c070  73 75 70 72 65 6d 75 6d  03 01 02 00 00 10 00 58  |supremum.......X|
0000c080  00 00 00 00 02 00 00 00  00 00 0f 68 4d 00 00 01  |...........hM...|
0000c090  9e 04 a9 61 62 62 20 20  20 20 20 20 20 20 63 63  |...abb        cc|
0000c0a0  63 63 63 63 03 02 01 00  20 00 18 00 00 00 00 00  |cccc.... .......|
0000c0b0  00 02 01 00 00 00 00 0f  6a 4e 00 00 01 9f 10 c0  |........jN......|
0000c0c0  64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  |deeee        fff|
0000c0d0  03 01 06 00 00 20 ff 98  00 00 00 00 02 02 00 00  |..... ..........|
0000c0e0  00 00 0f 67 cc 00 00 01  b6 01 10 64 66 66 66 00  |...g.......dfff.|

ログイン後にコピー

该行记录从0000c078开始，第一行整理如下：

03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1
02 // 0000 0010 NULL标志位，表示t2为null
00 00 10 00 58 // 记录头信息，固定5字节长度
00 00 00 00 02 00 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 68 // 事务ID，固定6个字节
4d 00 00 01 9e 04 a9 // 回滚指针，固定7个字节61 // t1数据'a'62 62 20 20 20 20 20 20 20 20 // t3数据'bb'63 63 63 // t4数据'ccc'

ログイン後にコピー

第二行整理如下：

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第二行没有NULL值20 00 18 00 00 // 0010 delete_mask=1 标记该记录是否被删除  记录头信息，固定5字节长度
00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 6a // 事务ID，固定6个字节
4e 00 00 01 9f 10 c0 // 回滚指针，固定7个字节64 // t1数据'd'65 65 // t2数据'ee'65 65 20 20 20 20 20 20 20 20 // t3数据'ee'66 66 66 // t4数据'fff'

ログイン後にコピー

第三行数据未发生变化。

推荐学习：mysql视频教程




1	レコードが削除されたかどうかをマークします
1	B ツリーの各層の非リーフノードの最小レコードがこのマークで追加されます