Verify a small problem

A question was mentioned in the previous article, and many articles on the Internet also said the same. A few days ago, someone put forward a slightly different opinion on this question, and conducted a test with a cautious attitude.

The question is this: In COMPACT format, does a list of NULL values necessarily occupy a byte of space?

For this question, my answer is the same as many answers on the Internet. If it is NOT NULL, there will be no NULL value list, so it will not be used, otherwise it will be used.

Today, let's do a verification on this problem.

storage

Review the previous knowledge first.

A row in the database is also stored in the final disk file. For InnoDB, there are 4 row storage formats: REDUNDANT , COMPACT , DYNAMIC and COMPRESSED .

The default row storage format of InnoDB is COMPACT , the storage format is as follows, the dotted line may not necessarily exist.

Variable-length field length list: If there are multiple fields, they are stored in reverse order. We only have one field, so we don’t consider so much. The storage format is hexadecimal. If there is no variable-length field, this part is not needed.

NULL value list: used to store the NULL value in our record. If there are multiple NULL values, they are also stored in reverse order, and must be an integer multiple of 8 bits. If there are not enough 8 bits, the upper bits are filled with 0. 1 means NULL, 0 Represents not NULL. If all are NOT NULL, then this exists, and every 8 NULLs will occupy an extra byte of space.

ROW_ID: The unique flag of a row of records, the ROW_ID automatically generated when the primary key is not specified is used as the primary key.

TRX_ID: Transaction ID.

ROLL_PRT: Rollback pointer.

The last is the value of each column.

In order to clarify the problem of this storage format, I made a table to test, this table only has c1 field is NOT NULL, others can be NULL.

Variable field length list : c1 and c3 field value lengths are 1 and 2 respectively, so the length converted to hexadecimal is 0x01 0x02 0x02 0x01 after the reverse order 0x02 0x01 .

NULL value list : Because there are columns that allow NULL, c2,c3,c4 are 010 respectively. After the reverse order, they are still the same. At the same time, the high order is filled with 0 and 8 bits. The result is 00000010 .

We don't care about other fields for the time being. The result of the last record is the result of the first record. Of course, we don't consider the result after encoding here.

This is the format of a complete data row data. On the contrary, if we set all fields to NOT NULL and insert a piece of data a,bb,ccc,dddd , the storage format should be like this:

test

There is a little problem here. First of all, I saw the test and description in Ali's database monthly report.

From this code, we can see the previous conjecture, that is, it is not that the Null flag bit only occupies 1 byte ==, but the unit is 8. If there are 8 null fields, there will be 1 more byte, and if it is less than 8 Occupies 1 byte, high bits are filled with 0

What he means is that it will occupy a byte anyway, but after reading his test, he found that his table allows NULL, so his test cannot explain the problem we want to verify.

According to the plan given by the online boss, create a table, and then insert test data, there are NULL values in the database.

 CREATE TABLE test ( c1 VARCHAR ( 32 ),
   c2 VARCHAR ( 32 ),
   c3 VARCHAR ( 32 ),
   c4 VARCHAR ( 32 ) ) ENGINE = INNODB row_format = compact;

Use the command SHOW VARIABLES LIKE 'datadir' to find the ibd file location.

Use the command to convert the ibd file to a txt file.

 hexdump -C -v test.ibd > /Users/irving/test-null.txt

Open the file to find the supremum section.

Don't look at that much, just look at a part:

03 02 02 01 is the variable-length field length list mentioned above, thinking we have 4 fields, so 4 bytes.

00 is the NULL flag

00 00 10 00 25 is the data header 5 bytes

This is definitely no problem, then create a table again, this time the fields are NOT NULL, and then execute the command again.

 CREATE TABLE test ( c1 VARCHAR ( 32 ) NOT NULL,
   c2 VARCHAR ( 32 ) NOT NULL,
   c3 VARCHAR ( 32 ) NOT NULL,
   c4 VARCHAR ( 32 ) NOT NULL ) ENGINE = INNODB row_format = compact;

Get another ibd file.

The comparison is actually very clear to find the problem. At this time, there is no flag for the NULL value list.

SO, this test result proves that if there is any NULL value, the NULL value list occupies at least one byte of space, and every 8 NULL values in the future will occupy an extra byte. If all are NOT NULL, there will be no NULL value list. markup, does not take up space.

Shoulders of Giants:

http://mysql.taobao.org/monthly/2016/08/07/

https://www.cnblogs.com/zhoujinyi/archive/2012/10/17/2726462.html