Detailed explanation of MySQL data rows and row overflow mechanism

1. What are the formats of lines?

You can see your MySQL row format settings like this.

In fact, MySQL data rows have two formats, one is the Compact format shown in the figure, and the other is the Redundant format.

Compact is a compact row format, which is designed to store more data rows in one data page.

Think about it, it is so exciting to be able to store more data rows in a data page. MySQL reads data from the disk in data pages. If we can make more rows in a data page, wouldn’t it use less space and increase the overall efficiency?

Official website introduction: Compact can save 20% storage compared to Redundant format.

Compact was introduced in MySQL 5.0. After MySQL 5.1, the row format is set to Compact by default. So this article also describes the Compact format.

2. What does the compact row format look like?

You must know that some columns in the table allow null, and some columns are variable-length varchar types.

How does the Compact row format organize and describe this information? As shown below:

Each part may contain more data than the 1, 2, and 3 I marked above.

In order to give you a more intuitive feeling and understanding, I only picked out a part to show you.

3. How much data can a single MySQL row store?

In MySQL settings, a single row of data can store a maximum of 65535 bytes of data (note that it is bytes, not characters)

But an error occurs when you create a data table like this:

MySQL does not allow you to create a column with a length of 65535 bytes because each row in the data page has the hidden column mentioned in the figure above.

Therefore, reduce the length of varchar to 65532 bytes to successfully create the table.

Note that 65535 here refers to bytes, not characters.

So if you change the charset to utf8, the N in varchar(N) actually refers to N characters, not N bytes. So if you create a table like below you will get an error.

If encode=utf8, three bytes represent one character. Then 65535 / 3 = 21845 characters.

4. How is the Compact format so compact?

MySQL performs random IO reads each time

By default, the data page size is 16KB. A data page stores several rows.

Does that mean that the more data rows can be stored in a data page, the fewer IO times MySQL will perform overall? The faster the performance?

The implementation idea of ​​the Compact format is: when the column type is VARCHAR, VARBINARY, BLOB, or TEXT, the data in the column that exceeds 768 bytes is placed in other data pages.

As shown below:

Is the whole story clear to you now?

By doing this, MySQL effectively prevents the situation where a single varchar column or text column is too large, resulting in too few row records stored in a single data page, causing IO to soar and occupying memory.

5. What is row overflow?

So what is line overflow?

If the default data page size is 16KB, converted to bytes: 16*1024 = 16384 bytes

Have you noticed that the 16384 bytes that a single page can store is several times the maximum of 65535 bytes that a single row can store?

In other words, if the data row you want to store is larger than 65532 bytes, you cannot write it. If the single row of data you want to store is less than 65535 bytes but greater than 16384 bytes, you can successfully insert it, but one data page cannot store the data you inserted. At this point there will definitely be an overflow!

In fact, in the MySQL settings, row overflow does not occur only when the size reaches 16384 bytes.

For rows of type varchar, text, etc. When the length of such column storage reaches several hundred bytes, row overflow will occur.

6. How do rows overflow?

Or look at this picture:

In MySQL settings, when the length of a varchar column reaches 768 bytes, the first 768 bytes of the column are stored in the row as a prefix, and the excess data is stored in the overflow page, and then the two are linked through an offset pointer. This is the row overflow mechanism.

7. Think about a question

I wonder if you have ever thought about this question:

First of all, you must know that MySQL uses the clustered index of B+Tree. In this B+Tree, non-leaf nodes only store indexes but not data, while leaf nodes store real data. At the same time, the leaf nodes point to the data pages.

So when a single row cannot be stored, why not store it in two data pages? Just like the picture below.

Under single node storage, I will use multiple nodes to store the head office! Maybe my B+Tee will grow bigger and taller (this is actually a wrong idea)

The brain map corresponding to this error description is as follows:

The reasons why MySQL doesn't do this are as follows:

If MySQL wants to store more data rows in one data page, it must store at least two rows of data. Otherwise, the meaning of B+Tree is lost. B+Tree also degenerates into an inefficient linked list.

You can appreciate this blue sentence. What he meant by "each data page must store at least two rows of data" is not that a data page cannot store only one row. You can literally just write one line of data into it, then go have a meal or do something else. There is always only one row of data in this data page.

What this means is that when you write a row of data into this data page, even if it is very large and reaches the limit of the data page, it will be overflowed through the row overflow mechanism. It can still ensure that your next piece of data can be written into this data page.

The correct mind map is as follows:

refer to:

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

Author: Give Me Daydreams

Source: https://www.cnblogs.com/ZhuChangwu/p/14035330.html

The above is a detailed explanation of MySQL data rows and row overflow mechanism. For more information about MySQL data rows and row overflow, please pay attention to other related articles on 123WORDPRESS.COM!