Bagaimana untuk membaca format baris InnoDB daripada kandungan binari dalam MySQL

Artikel ini membawakan anda pengetahuan yang berkaitan tentang mysql, yang terutamanya memperkenalkan isu berkaitan tentang format baris InnoDB daripada kandungan binari InnoDB ialah sistem yang menyimpan data dalam jadual ke enjin storan, jadi data kami akan tetap wujud walaupun selepas ditutup dan dimulakan semula. Saya harap ia akan membantu semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

InnoDB ialah enjin storan yang menyimpan data dalam jadual pada cakera, jadi Walaupun selepas ditutup turun dan dimulakan semula, data kami masih wujud. Proses sebenar pemprosesan data berlaku dalam ingatan, jadi data dalam cakera perlu dimuatkan ke dalam memori Jika ia memproses permintaan tulis atau pengubahsuaian, kandungan dalam memori juga perlu dimuat semula ke cakera. Dan kita tahu bahawa kelajuan membaca dan menulis ke cakera adalah sangat perlahan, iaitu beberapa urutan magnitud yang berbeza daripada membaca dan menulis dalam ingatan Jadi apabila kita ingin mendapatkan rekod tertentu dari jadual, adakah enjin storan InnoDB perlu membaca rekod dari cakera satu demi satu?

Kaedah yang diguna pakai oleh InnoDB adalah untuk membahagikan data kepada beberapa halaman, menggunakan halaman sebagai unit asas interaksi antara cakera dan memori Saiz halaman dalam InnoDB biasanya 16KB. Maksudnya, dalam keadaan biasa, sekurang-kurangnya 16KB kandungan dibaca dari cakera ke memori pada satu masa, dan sekurang-kurangnya 16KB kandungan dalam memori dimuat semula ke cakera pada satu masa.

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

Kami biasanya memasukkan data ke dalam jadual dalam unit rekod. Cara rekod ini disimpan pada cakera juga dipanggil format baris atau format rekod. Enjin storan InnoDB telah mereka bentuk empat jenis format baris yang berbeza, iaitu format baris Padat, Lebihan, Dinamik dan Mampat.

Klasifikasi dan pengenalan format rekod baris

Dalam versi awal InnoDB, memandangkan hanya terdapat satu format fail, tidak perlu menamakan format fail ini. Apabila enjin InnoDB berkembang, format fail baharu yang tidak serasi dengan versi terdahulu dibangunkan untuk menyokong ciri baharu. Untuk membantu mengurus keserasian sistem dalam situasi naik taraf dan turun taraf, dan apabila menjalankan versi MySQL yang berbeza, InnoDB mula menggunakan format fail bernama.

Dalam msyql 5.7.9 dan versi yang lebih baru, format baris lalai ditentukan oleh pembolehubah innodb_default_row_format dan nilai lalainya adalah dinamik:

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)

Lihat format Baris semasa yang digunakan oleh jadual:

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)

Nyatakan format baris jadual:

CREATE TABLE 表名(列的信息) ROW_FORMAT=行格式名称ALTER TABLE 表名 ROW_FORMAT=行格式名称;

Jika anda ingin mengubah suai mod baris yang sedia ada jadual kepada dimampatkan atau dinamik, anda mesti terlebih dahulu Tetapkan format fail kepada Barracuda: set global innodb_file_format=Barracuda;, dan kemudian gunakan ALTER TABLE nama jadual ROW_FORMAT=COMPRESSED untuk mengubah suainya supaya berkesan.

Format baris

COMPACT

Senarai medan panjang boleh ubah

MySQL menyokong beberapa Jenis data panjang berubah , seperti VARCHAR(M), VARBINARY(M), pelbagai jenis TEKS, dan pelbagai jenis BLOB Kami juga boleh memanggil lajur dengan jenis data medan panjang boleh ubah ini tidak tetap, jadi apabila kita menyimpan data sebenar, kita perlu menyimpan bilangan bait yang diduduki oleh data ini. Jika bilangan bait maksimum yang dibenarkan untuk disimpan dalam medan pembolehubah (M × W) melebihi 255 bait dan bilangan bait sebenar yang disimpan (L) melebihi 127 bait, gunakan 2 bait untuk merakam, jika tidak gunakan 1 bait Rekod.

Soalan 1: Jadi mengapa 128 digunakan sebagai garis pemisah? Satu bait boleh mewakili sehingga 255, tetapi apabila MySQL mereka bentuk perwakilan panjang, untuk membezakan sama ada ia adalah bait yang mewakili panjang, ia ditetapkan bahawa jika bit tertinggi ialah 1, maka dua bait mewakili panjang, sebaliknya ia ialah satu bait. Sebagai contoh, 01111111, ini bermakna panjang ialah 127, dan jika panjang ialah 128, dua bait diperlukan, iaitu 10000000 10000000. Bit tertinggi bagi bait pertama ialah 1, maka ini adalah permulaan bagi dua bait yang mewakili panjang. Bait kedua boleh menggunakan semua bit untuk mewakili panjang, dan perlu diperhatikan bahawa MySQL menggunakan kaedah pengiraan Little Endian, dengan bit rendah dahulu dan bit tinggi terakhir, jadi 129 ialah 10000001 10000000. Pada masa yang sama, panjang maksimum kaedah pengenalan ini ialah 2^15-1=32767, iaitu 32KB.

Soalan 2: Apakah yang perlu saya lakukan jika dua bait tidak mencukupi untuk mewakili panjang? Saiz halaman lalai innoDB ialah 16KB Untuk sesetengah medan yang menduduki banyak bait, contohnya, panjang medan lebih besar daripada 16KB Jika rekod tidak boleh disimpan dalam satu halaman, InnoDB akan menyimpan sebahagian daripada data dalam apa yang dipanggil limpahan. Dalam halaman, hanya panjang yang tinggal dalam halaman ini disimpan dalam senarai panjang medan panjang berubah, jadi ia boleh disimpan menggunakan dua bait. Mekanisme halaman limpahan ini merujuk kepada limpahan data kemudian.

Senarai nilai NULL

Sesetengah lajur dalam jadual mungkin menyimpan nilai NULL Jika nilai NULL ini disimpan dalam data sebenar rekod, ia akan mengambil banyak ruang, jadi Compact. format baris menyimpan nilai ini kerana lajur NULL diuruskan secara seragam dan disimpan dalam senarai nilai NULL. Setiap lajur yang dibenarkan untuk menyimpan NULL sepadan dengan bit binari Apabila nilai bit binari ialah 1, ia bermakna nilai lajur adalah NULL. Apabila nilai bit binari ialah 0, ini bermakna nilai lajur itu bukan NULL.

Maklumat pengepala rekod

digunakan untuk menerangkan maklumat pengepala rekod rekod, yang terdiri daripada 5 bait tetap. 5 bait ialah 40 bit binari, dan bit yang berbeza mewakili makna yang berbeza.

字段	长度（bit）	说明
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B 树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在页的位置信息
record_type	3	表示当前记录的类型，0 表示普通记录，1 表示B 树非叶子节点记录，2 表示最小记录，3 表示最大记录
next_record	16	表示下一条记录的相对位置

隐藏列

记录的真实数据除了我们自己定义的列的数据以外，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视频教程

Atas ialah kandungan terperinci Bagaimana untuk membaca format baris InnoDB daripada kandungan binari dalam MySQL. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!