Hello, everyone. I’m warm-hearted big belly, Piggy.
In the previous article, we explained how COMPACT is stored, and we will continue to learn about the last three storage formats.
Line InnoDB storage
InnoDB rows can be stored in COMPACT, REDUNDANT, DYNAMIC, and COMPRESSED formats.
REDUNDANT
The following figure shows the storage information for each row.
List of field length offsets
The columns are stored in the list in hexadecimal reverse order, and the length of the column values is calculated by the offset. For example, if column 1 has length 1, column 2 has length 3, and column 3 has length 4, the list is displayed as 4, 7, 8, and the first offset of each column is marked as null.
Record header with real information
It is basically the same as COMPACT storage, so I won’t go into details.
Non-variable-length field
So how do you store non-variable-length fields? For example, char(10) uses the utF8 character set (10) and uses the utF8 character set (10-30 bytes).
The DYNAMIC, COMPRESSED,
The only difference between these two approaches and COMPACT storage is the handling of overflow columns (more on that later). COMPRESSED uses compression algorithms to compress the data on the page.
InnoDB row storage – Overflow columns
What is an overflow column? We mentioned earlier that mysql has a page size of 16KB, and column data will overflow if you exceed this size.
COMPACT, REDUNDANT
In the same way, only the first 768 bytes of recorded real data are stored, the rest of the data is distributed to different pages, and the address of the corresponding content is stored in 20 bytes of recorded real data.
The DYNAMIC, COMPRESSED,
In both cases, the real data store does not store any data, but all the data is scattered across the pages, and only the address of the corresponding content is stored in the real data. In fact, text, blob, etc. overflow rate is very high.
The critical point
The tipping point for overflow columns is shown below.
The overflow column occurs when the size exceeds the threshold of approximately 8KB.