Understanding of MySQL's data rows and row overflow mechanisms

mysql video tutorial column introduces the data row and row overflow mechanism.

##Related free learning recommendations:

mysql video tutorial

1. What are the formats of lines?

You can take a look at your MySQL row format settings as follows.

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

Compact is a compact row format, designed to allow more data rows to be stored in one data page.

You have a taste, how exciting it is to allow more data rows to be stored in one data page. MySQL reads data from the disk in units of data pages. If it can be done to allow one data page If there are more rows in it, wouldn't it mean that less space is used, and the overall efficiency soars?

Official website introduction: Compact can save 20% of storage than Redundant format.

Compact was introduced from MySQL5.0. After MySQL5.1, the row format is set to Compact by default. Therefore, what this article describes is also the Compact format.

2. What does the compact line format look like?

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

How does the Compact line 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 just selected a part to show you.

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

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

But when you create a data table like the following An error occurred:

MySQL does not allow the creation of a column with a length of 65535 bytes, because each row in the data page has the hidden column we mentioned above.

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

Note that 65535 here refers to bytes, not characters.

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

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

4. How does the Compact format become compact?

MySQL performs random IO reads every time

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

That means that the more data rows that can be stored in a data page, the fewer IO times MySQL will perform as a whole? The performance is faster?

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

As shown below:

Do you see the whole story clearly?

MySQL does this to effectively prevent a single varchar column or Text column from being too large, resulting in too few row records stored in a single data page, causing IO to soar and occupying memory.

5. What is line overflow?

So what is line overflow?

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

Have you noticed that there is a difference between the 16384bytes that can be stored in a single page and the maximum 65535bytes that can be stored in a single row? How many times?

In other words, if the data row you want to store exceeds 65532byte, you will not be able to write it. If the single row of data you want to store is less than 65535 bytes but greater than 16384 bytes, you can insert successfully, but a data page cannot store the data you inserted. At this time, the line will definitely overflow!

In fact, in the MySQL settings, row overflow does not occur until the 16384byte edge is reached.

For lines of varchar, text and other types. Row overflow occurs when the length of such column storage reaches several hundred bytes.

6. Line How to overflow?

Still look at this picture:

In the MySQL setting, when the length of the varchar column reaches 768 bytes, the first 768 bytes of the column will be treated as prefix Stored in rows, the excess data overflows and is stored in the overflow page, and then the two are associated through an offset pointer. This is the row overflow mechanism.

7. Think about a question

I don’t know if you have ever thought about such a question:

First of all, you must know that MySQL uses B Tree's clustered index, in this B Tree, the non-leaf nodes only store indexes but not data, and the leaf nodes store real data. At the same time, the leaf nodes point to the data page.

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

Single node storage, I use multiple nodes to store the head office! Maybe my B Tee can grow bigger and taller in this way (this is actually a wrong idea)

The corresponding brain map of this wrong description is as follows:

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

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

You can taste this blue sentence. When he says that each data page must store at least two rows of data, he does not mean that the data page cannot store only one row. You can really just write a line of data into it, and then go have a meal and do something else. There is always only one row of data in this data page.

What this sentence means is that when you write a row of data to this data page, even if it is very large, it will reach the limit of the data page, but through the row overflow mechanism. It is still guaranteed that your next piece of data can be written to this data page.

The correct brain map is as follows:

The above is the detailed content of Understanding of MySQL's data rows and row overflow mechanisms. For more information, please follow other related articles on the PHP Chinese website!