Shulou(Shulou.com)06/01 Report--

This article is about what MySQL's data rows and row overflow mechanism means. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

1. What are the formats of the line?

You can look at your MySQL line formatting like this.

In fact, there are two formats for MySQL data rows, one is the Compact format in the diagram, and the other is Redundant format.

Compact is a compact row format designed so that more rows of data can be stored in a data page.

How exciting it is to store more rows in a data page. MySQL reads data from disk in data pages. If you can make more rows in a data page, doesn't it use less space and the overall efficiency soars?

According to the official website: Compact can save 20% more storage than Redundant format.

Compact was introduced from MySQL5.0, and after MySQL5.1, the line format is set to Compact by default. So this article also describes the Compact format.

What does a compact line format look like?

You must know that some columns in the table are allowed to be null, and some columns are of variable length varchar type.

So how does the Compact line format organize and describe this information? As shown below:

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

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

Third, how much data can be stored in a single row of MySQL?

In the setting of MySQL, single-line data can store maximum 65535byte data (note that it is byte, not characters).

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

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

So reduce the length of varchar to 65532byte to create the table successfully.

Note that 65535 here refers to bytes, not characters.

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

If three byte represent one character when encode=utf8. So 65535 / 3 = 21845 characters.

4. How is the Compact format compact?

MySQL does random IO reading each time.

By default, the size of the data page is 16KB. Several rows are stored in the data page.

Does that mean that the more data rows can be stored in a data page, the less IO will be performed by MySQL as a whole? The faster the performance?

The implementation idea of Compact format is that when the type of column is VARCHAR, VARBINARY, BLOB, TEXT, the data that exceeds 768byte is put into other data pages.

As shown below:

Is it very clear to see the context here?

In doing so, MySQL effectively prevents a single varchar column or Text column from being too large, resulting in too few rows stored in a single data page and causing IO to soar and take up memory.

5. What is line spillover?

So what is line spillover?

If the default size of the data page is 16KB, convert it to byte: 16 pages 1024 = 16384 byte

Have you found that there is a several-fold difference between the 16384byte that can be stored on a single page and the 65535byte that can be stored in a single row?

In other words, if you want to store more rows of data than 65532byte, then you can't write into it. If you want to store a single row of data that is smaller than 65535byte but larger than 16384byte, you can successfully insert, but a data page cannot store the data you insert. At this time, it is sure to overflow!

In fact, in the MySQL setting, a row overflow does not occur until it reaches the edge of the 16384byte.

For rows of varchar, text, and so on. A row overflow occurs when the length of this column store reaches several hundred byte.

6. How does the line overflow?

Or look at this picture:

In the MySQL setting, when the length of the varchar column reaches 768byte, the previous 768byte of the column will be stored in the row as prefix, and the extra data overflow will be stored in the overflow page, and then the two will be associated with an offset pointer, which is the row overflow mechanism.

7. Think about a question

I don't know if you've ever thought about this question:

First of all, you must know that MySQL uses B+Tree 's clustered index. In this B+Tree, non-leaf nodes store only indexes and no data, while leaf nodes store real data. At the same time the leaf node points to the data page.

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

Under the storage of a single node, I will use multiple nodes to save the total line. Maybe my B+Tee will grow bigger and taller in this way (this is actually a wrong idea)

The description of this error corresponds to a brain map as follows:

The reasons why MySQL does not do this are as follows:

MySQL wants to store more rows of data in a data page, at least two rows of data. Otherwise, the meaning of B+Tree will be lost. B+Tree has also degenerated into an inefficient linked list.

You can taste this blue sentence. When he said that each data page should hold at least two rows of data, he didn't mean that the data page could not store only one row. You can really just write a line of data in it, and then go to dinner and do something else. Keep this data page with only one row of data.

This means that when you write a row of data to this data page, even if it reaches the limit of the data page, through the row overflow mechanism. You can still ensure that your next piece of data can be written to this data page.

The correct brain map is as follows:

Thank you for reading! On the MySQL data rows and row overflow mechanism means to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.