Comment lire le format de ligne InnoDB à partir du contenu binaire dans MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement des problèmes liés au format de ligne InnoDB à partir du contenu binaire. InnoDB est un moteur de stockage qui stocke les données dans des tables sur le disque, donc même après l'arrêt et le redémarrage, nos données seront toujours conservées. existe. J’espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo mysql

InnoDB est un moteur de stockage qui stocke les données dans des tables sur le disque, de sorte que nos données existent toujours même après l'arrêt et le redémarrage. Le processus réel de traitement des données se produit en mémoire, de sorte que les données du disque doivent être chargées dans la mémoire. S'il traite une demande d'écriture ou de modification, le contenu de la mémoire doit également être actualisé sur le disque. Et nous savons que la vitesse de lecture et d'écriture sur le disque est très lente, ce qui est plusieurs ordres de grandeur différents de la lecture et de l'écriture en mémoire. Ainsi, lorsque nous voulons obtenir certains enregistrements de la table, le moteur de stockage InnoDB doit-il lire. les enregistrements du disque un par un ?

La méthode adoptée par InnoDB consiste à diviser les données en plusieurs pages et à utiliser les pages comme unité de base d'interaction entre le disque et la mémoire. La taille d'une page dans InnoDB est généralement de 16 Ko. Autrement dit, dans des circonstances normales, au moins 16 Ko de contenu sont lus du disque vers la mémoire à la fois, et au moins 16 Ko du contenu de la mémoire sont actualisés sur le disque à la fois.

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

Nous insérons généralement les données dans le tableau en unités d'enregistrements. La façon dont ces enregistrements sont stockés sur le disque est également appelée format de ligne ou format d'enregistrement. Le moteur de stockage InnoDB a conçu quatre types différents de formats de lignes, à savoir les formats de lignes Compact, Redondant, Dynamique et Compressé.

Classification et introduction des formats d'enregistrement de ligne

Dans les premières versions d'InnoDB, comme il n'y avait qu'un seul format de fichier, il n'était pas nécessaire de nommer ce format de fichier. À mesure que le moteur InnoDB évolue, de nouveaux formats de fichiers incompatibles avec les versions antérieures sont développés pour prendre en charge de nouvelles fonctionnalités. Pour aider à gérer la compatibilité du système dans les situations de mise à niveau et de rétrogradation, et lors de l'exécution de différentes versions de MySQL, InnoDB a commencé à utiliser un format de fichier nommé.

Dans msyql 5.7.9 et versions ultérieures, le format de ligne par défaut est déterminé par la variable innodb_default_row_format, et sa valeur par défaut est dynamique :

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)

Affichez le format de ligne utilisé par la table actuelle :

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)

Spécifiez le format de ligne du tableau :

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

Si vous souhaitez modifier le mode de ligne d'une table existante en compressé ou dynamique, vous devez d'abord définir le format de fichier sur Barracuda : set global innodb_file_format=Barracuda;, puis utiliser ALTER TABLE tablename ROW_FORMAT=COMPRESSED; pour rendre la modification effective.

Format de ligne

COMPACT

Liste de champs de longueur variable

MySQL prend en charge certains types de données de longueur variable, tels que VARCHAR(M), VARBINARY(M), divers types de TEXTE, divers types de BLOB, nous les colonnes avec ces types de données peuvent également être appelés champs de longueur variable. Le nombre d'octets de données stockés dans un champ de longueur variable n'est pas fixe, donc lorsque nous stockons des données réelles, nous devons également stocker le nombre d'octets occupés par ces données. se lever. Si le nombre maximum d'octets autorisés à être stockés dans le champ variable (M × W) dépasse 255 octets et que le nombre réel d'octets stockés (L) dépasse 127 octets, utilisez 2 octets pour enregistrer, sinon utilisez 1 octet d'enregistrement.

Question 1 : Alors pourquoi 128 est-il utilisé comme ligne de démarcation ? Un octet peut représenter jusqu'à 255, mais lorsque MySQL a conçu la représentation de la longueur, afin de distinguer s'il s'agit d'un octet qui représente la longueur, il est stipulé que si le bit le plus élevé est 1, alors deux octets représentent la longueur, sinon il est un octet. Par exemple, 01111111, cela signifie que la longueur est de 127, et si la longueur est de 128, deux octets sont nécessaires, soit 1 000 000 1 000 000. Le bit le plus élevé du premier octet est 1, alors c'est le début des deux octets représentant le longueur. Le deuxième octet peut utiliser tous les bits pour représenter la longueur, et il convient de noter que MySQL adopte la méthode de comptage Little Endian, avec le bit faible en premier et le bit élevé en dernier, donc 129 vaut 10000001 10000000. Dans le même temps, la longueur maximale de cette méthode d'identification est de 2 ^ 15-1 = 32 767, soit 32 Ko.

Question 2 : Que dois-je faire si deux octets ne suffisent pas pour représenter la longueur ? La taille de page innoDB par défaut est de 16 Ko. Pour certains champs qui occupent un très grand nombre d'octets, par exemple, la longueur d'un champ est supérieure à 16 Ko. Si l'enregistrement ne peut pas être stocké sur une seule page, InnoDB stockera une partie de l'enregistrement. données dans ce qu'on appelle le débordement. Dans la page, seule la longueur restante dans cette page est stockée dans la liste de longueur de champ de longueur variable, elle peut donc être stockée en utilisant deux octets. Ce mécanisme de page de débordement fait référence au débordement de données ultérieurement.

Liste des valeurs NULL

Certaines colonnes du tableau peuvent stocker des valeurs NULL Si ces valeurs NULL sont stockées dans les données réelles de l'enregistrement, cela prendra beaucoup de place. Par conséquent, le format de ligne Compact gère ces colonnes. avec des valeurs NULL de manière unifiée, stockés dans une liste de valeurs NULL. Chaque colonne autorisée à stocker NULL correspond à un bit binaire. Lorsque la valeur du bit binaire est 1, cela signifie que la valeur de la colonne est NULL. Lorsque la valeur du bit binaire est 0, cela signifie que la valeur de la colonne n'est pas NULL.

Informations d'en-tête d'enregistrement

Les informations d'en-tête d'enregistrement utilisées pour décrire l'enregistrement, qui sont composées de 5 octets fixes. 5 octets correspondent à 40 bits binaires et différents bits représentent des significations différentes.

Champ	Longueur (bit)	Description
Bit réservé 1	1	Non utilisé
Bit réservé 2	1	Non utilisé
delete_mask	1	marque si l'enregistrement est supprimé
min_rec_mask	1	Le plus petit enregistrement dans le nœud non-feuille de chaque couche de l'arbre B+ ajoutera cette marque
n_owned	4	signifie Le nombre d'enregistrements détenus par l'enregistrement actuel
heap_no	13	représente les informations de position de l'enregistrement actuel sur la page
record_type	3	représente le type de l'enregistrement actuel, 0 représente un enregistrement normal, 1 représente un enregistrement de nœud non-feuille d'arbre B+, 2 représente l'enregistrement minimum, 3 représente l'enregistrement maximum
next_record	16	représente la position relative de l'enregistrement suivant

隐藏列

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!