Home >Database >Mysql Tutorial >Understanding of MySQL's data rows and row overflow mechanisms
##Related free learning recommendations:
1. What are the formats of lines?
You can take a look at your MySQL row format settings as follows.2. What does the compact line format look like?
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:4. How does the Compact format become compact?
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 byteHave 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!