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

Physical storage structure of MySQL

(1)。 The organizational form of data-- index

(2)。 Row storage of data

Compact

Storage of variable length fields:

Variable length columns also take into account the number of bytes of the actual length of the storage column when evaluating the field size. For example, the VARCHAR (255th) CHARACTER SET UTF8 column requires two additional bytes to store value length information, so the column requires up to 767 bytes of storage, which can actually store up to 65533 bytes and the remaining two bytes store length information.

Handling of row overflows:

The data table Row_format is Compact, and the default approach storage format of innodb stores the first 864 bytes of each blob field in page, so if the blob exceeds a certain number, the single row size will exceed 8k, so an error will be reported. This corollary is also applied by comparing the success and failure of the SQL, so how to solve this problem now?

Business split table, large fields for sub-table storage to solve the problem by solving the storage mode of Row_format

Because the number of storage entries in the business single table is not large, and the business logic is not suitable for splitting, we need to solve this problem on Row_format.

If the blob column value is 768 bytes long, the first 768 bytes are still on the data page, while the rest are placed on the overflow page (off-page)

Therefore, the unique value index length of this format cannot exceed 767

Barracuda

Under the Barracuda file format, there are two new row record formats, Compressed and Dynamic. The new two formats use a complete row overflow mode for storing BLOB data, and only 20 bytes of pointers are stored in the data page, and the actual data are stored in BLOB Page. Another feature of the Compressed line record format is that the data stored in it is compressed using zlib's algorithm.

Dynamic row format, whether the column is stored in the off-page page depends mainly on the row size. It will put the longest column in the row to the off-page until the data page can store the next two rows. TEXT/BLOB column

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.