InnoDB Row Record format

InnoDB storage engine As with most databases, records are stored as rows. This means that the page holds data for a row of rows in the table. Before the InnoDB 1.0.x version, the InnoDB storage engine
The compact and redundant two formats are provided to store row-record data.
The redundant format is reserved for compatibility with previous versions, and if you read the source code of the InnoDB, you will find that the source code uses the physical record (NEW style) and the physical record (the old style) to differentiate between the two formats.
In the MySQL 5.1 version, the default is the compact line format. The following command allows users to view the row format used by the table, where the Row_format property represents the row record structure type that is currently being used.

Mysql> Show Table status like ' Test ' \g
*************************** 1. Row ***************************
           Name: Test
         engine:innodb
        version:10
     row_format:compact
           rows:1
 avg_row_length:16384
    Data_ length:16384
max_data_length:0
   index_length:0
      data_free:5242880
 auto_increment:null
    create_time:2016-07-20 16:13:43
    update_time:null
     check_time:null
      collation:utf8_general_ci
       Checksum:null
 create_options: 
        Comment: 
1 row in Set (0.00 sec)

You can see that the test table is the row format of the compact. One of the functions of a database instance is to read the row records stored in the page. If the user knows the organization rules for the Row Records in the page, they can also read the records in the way they write the tools themselves. 1, compact line record format

The compact line record was introduced in MySQL 5.0 and was designed to efficiently store data. In simple terms, the more rows of data are stored in a page, the higher the performance. The following is how the compact line records are stored:

The header of the compact line record format is a list of non-null variable length field lengths, which are placed in reverse order of the columns, in length:
(1) If the length of the column is less than 255 bytes, expressed in 1 bytes;
(2) If it is greater than 255 bytes, expressed in 2 bytes;

A variable length field can be up to 2 bytes long, because the maximum length of the varchar type in MySQL data is limited to 65535.
The second part of the variable-length field is the null flag bit, which indicates whether there is a null value in the row data, and a 1 representation. This portion of the byte should be 1 bytes.
The next section is the header information (the record header), which occupies 5 bytes (40 bits), each meaning as shown in the following illustration:

The final section is the data that actually stores each column. Null does not occupy any space in this part, that is, NULL in addition to the location of the null flag, the actual storage does not occupy any space.
In addition, each row contains two hidden columns, a transaction ID column, and a rollback pointer column, in addition to the user-defined columns, of 6 bytes and 7 bytes respectively. If the InnoDB table does not have a primary key defined, each row also adds a 6-byte rowid column.

Next, use a specific example to analyze the internal structure of the compact line record:

Mysql> CREATE TABLE mytest (-> t1 varchar (),-> T2, varchar (a),-> T3
    char (a),-> T4
    va Rchar (a)
    ->) Engine=innodb charset=latin1 row_format=compact;
Query OK, 0 rows affected (0.09 sec)

Insert Data below:

mysql> INSERT into mytest values (' A ', ' BB ', ' BB ', ' CCC '); 
Query OK, 1 row affected (0.02 sec)

mysql> insert INTO mytest values (' d ', ' ee ', ' ee ', ' fff ');
Query OK, 1 row affected (0.01 sec)

mysql> insert INTO mytest values (' d ', null, NULL, ' FFF ');
Query OK, 1 row affected (0.01 sec)

Data in the query table:

Mysql> SELECT * from Mytest\g
*************************** 1. Row ***************************
t1:a T2
: BB
t3:bb
t4:ccc
*************************** 2. Row ***************************
t1:d
t2:ee
t3:ee
t4:fff
*************************** 3. Row ***************************
t1:d
t2:null< C13/>t3:null
t4:fff
3 rows in Set (0.00 sec)

If parameter innodb_file_per_table is enabled, the Tablespace file mytest.ibd opens. Otherwise, open the default shared table space file ibdata1, which does not have the parameter innodb_file_per_table enabled in this article, and all open file ibdata1.

Under Linux, the location of the Ibdata1 file is first found.

root@tryhard:~# Find/-name ibdata1;
/var/lib/mysql/ibdata1

Then, redirect the binary file ibdata1 to the Mytest.txt file, and then open the Mytest.txt file.

root@tryhard:~# hexdump-c-v/var/lib/mysql/ibdata1 > Mytest.txt

Find out what the following illustration shows:

The following is an analysis of the first line of content:
03 02 01 is a variable length list of field lengths, the T1,t2,t3 field is a variable-length field, and the length of these three fields is displayed in reverse order. The T3 value in the inserted data is 3 bytes, the T2 value is 2 bytes, and the T1 value is 1 bytes.
00 is a null flag bit, and 0 indicates that the first row has no null value.
2c is the record Header with a fixed 5-byte length.
00 00 00 00 09 00 is the rowid,6 byte length InnoDB automatically created.
Two hidden columns, transaction ID columns, and rollback pointer columns, respectively 6-byte and 7-byte size.
1e 09 is the Transaction ID column, 6 byte length.
8a 3d 01 10 is a rollback pointer column, 7 byte length.
61 is the T1 column of data ' a ';
62 62 is the T2 column of data ' BB ';
62 62 20 20 20 20 20 20 20 is the data ' BB ' of the T3 column, T2 is a fixed-length char field and is populated with 0x20 if the length space occupied by it is not exhausted.
63 63 63 is T4 column of data ' CCC ';

The value of the record header is 2c, and the last two bytes are 2c, which represents the offset next_recorder,0x2c represents the next record, the position of the current recording plus the offset 0x2c is the starting position of the next record. So the InnoDB storage engine concatenates each row record within the page through a list of linked lists.

The second row is similar to the first line.
Here is the third line:
03 01 Variable Long field length list, reverse order; T1 length is 1,t4 length is 3,t2 and T3 is null.
The null flag bit and the third row has a null value.
FF 98 is a record Header;
00 00 00 00 09 02 are rowid,6 bytes;
1e 0b is the transaction id column, 6 bytes;
8c 1d 01 10 is a rollback pointer column, 7 bytes.
64 is the value of the T1 field ' d ';
66 66 66 is the value of the T4 field ' f ';

The third row has a null value, because the NULL flag bit is no longer 00 but 06, converted to binary 00000110, and 1 to the values of columns 2nd and 3rd for NULL. The portion of the column data that was subsequently stored, without storing the null column, and storing only the non-null values of columns 1th and 4th, also illustrates that null values in the compact format do not occupy any storage space , regardless of the char or varchar type. 2, redundant line record format

Redundant is the InnoDB row record storage method before MySQL version 5.0, and MySQL 5.0 supports redundant to be compatible with previous versions of the page format. Redundant row records are stored in the way shown in the following illustration.

The header of the redundant row record format is a field length offset list, which is also stored in reverse order of the installation column. If the length of the column is less than 255 bytes, it is represented by 1 bytes, or 2 bytes if it is greater than 255 bytes. The second section, which is the record header, differs from the compact row record format in that the record header in the redundant row record format occupies 6 bytes, and the meaning of each digit is shown in the following illustration:

Where the N_fields value represents the number of columns in a row, occupies 10 bits, so the maximum value is 1023, which explains why the maximum number of columns supported by the MySQL database line is 1023. Another value to note is 1byte_offs_flag, which defines whether the offset list occupies 1 bytes or 2 bytes.
The last part is the data for each column that is actually stored.

Create a redundant table Mytest2:

Mysql> CREATE TABLE Mytest2  engine=innodb row_format=redundant as  select * from MyTest;
Query OK, 3 rows affected (0.04 sec)
records:3  duplicates:0  warnings:0

View Table Mytest2 Information:

Mysql> Show Table status like ' Mytest2 ' \g
*************************** 1. Row ***************************
           Name:mytest2
         engine:innodb
        version:10
     row_format:redundant
           rows:3
    avg_row_length:5461 data_length:16384
max_data_length:0
   index_length:0
      data_free:5242880
 auto_increment:null
    create_time:2017-03-20 15:46:57
    Update_time:null
     check_time:null
      collation:utf8_general_ci
       checksum:null
 create_options: Row_format=redundant
        Comment: 
1 row in Set (0.00 sec)

View data:

Mysql> SELECT * from Mytest2\g                       
*************************** 1. Row ***************************
t1:a T2
: BB
t3:bb
t4:ccc
*************************** 2. Row ***************************
t1:d
t2:ee
t3:ee
t4:fff
*************************** 3. Row ***************************
t1:d
t2:null< C13/>t3:null
t4:fff
3 rows in Set (0.00 sec)

REDIRECT the binary file ibdata1 to the Mytest.txt file, and then open the Mytest.txt file.

root@tryhard:~# hexdump-c-v/var/lib/mysql/ibdata1 > Mytest.txt

First line of data:

0c 06 is a length offset list, in reverse order.
0f BA is the record Header, fixed 6 bytes;
The 0c is rowid column, 6 bytes.
1e 1b is the transaction ID column, 6 bytes.
9a 4b 01 10 is a rollback pointer column, 7 bytes.

61 is T1 column data ' a ';
62 62 is T2 column data ' BB ';
62 62 20 20 20 20 20 20 20 is T3 column data ' BB ', char type;
63 63 63 is T4 column data ' CCC ';
0c 06 is inverted to 06,0c,13,14,16,20,23, representing the length of the first column as 6, the second column length 6 (6+6 = 0x0c), the third column length 7 (6+6+7=0x13), and the fourth column length 1 (6+6+7+1=0x14), The fifth column length is 2 (6+6+7+1+2=0x16), the sixth column length is ten (6+6+7+1+2+10=0x20), and the seventh column length is 3 (6+6+7+1+2+10+3=0x23).

The 1th column refers to the ROWID column, the 2nd column refers to the transaction ID column, and the 3rd column refers to the rollback pointer column, and the 4th column refers to the T1,......
In the next header information (record header) You should note that the 48-bit 第22-32位 is 0000000111, representing a total of 7 columns (containing the hidden 3 columns), followed by a 33rd digit 1, and a large table offset list of one byte.

The following information is the actual data stored.

Third row data:

9e 0c 06 Length offset list, reverse order;
0f 00 74 is a record Header with a fixed length of 6 bytes;
0e is rowid;
1e 1b is a transaction id,6 byte;
9a 4b/2e is the rollback pointer column, 7 bytes;
64 is the data of the column T1 ' d ';
The T2 type is varchar and the value is null, so the T2 value does not occupy storage space.
00 00 00 00 00 00 00 00 00 is T3 data null,t3 is char type;
66 66 66 is a column of T4 data ' FFF ';

After the length offset list is in reverse order, it is understood that the first 4 values are 21, 0c, 9e,.
The 5th null value becomes 94 (the column is a T2 field), and the 6th char type has a null value of 9e (94+10=0x9e) (the column is a T3 field), followed by the 21 large table (14+3 = 0x21). You can see that for null values of the varchar type, the redundant row record format also does not occupy any storage space, while the null value of the char type takes up space.

The character set for the current table Mytest2 is Latin1 and takes up to 1 bytes per character. If the user converts the character set of the table Mytest2 to UTF8, the third column char fixed-length type is no longer occupied with 10 bytes, but 10*3 = 30 bytes.