Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to optimize the storage efficiency of the BLOB,TEXT column of the InnoDB table, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

First, let's introduce a few key points about the storage format of the MySQL InnoDB engine:

1. InnoDB can choose to use shared tablespaces or independent tablespaces. It is recommended to use independent tablespaces for easy management and maintenance. Enable the innodb_file_per_table option, which can be dynamically modified online after 5.5. and execute ALTER TABLE xx ENGINE = InnoDB to convert the existing table to an independent tablespace, which is earlier than 5.5. after you modify this option, you need to restart to take effect.

2. InnoDB data page default 16KB data page version 5.6, the new option innodb_page_size can be modified. In versions prior to 5.6, only source code can be modified and recompiled, but it is not recommended to modify this configuration unless you are very clear about its advantages and disadvantages.

3. When the data page of InnoDB has new data to write, it will reserve space of 1x16, which can be used for subsequent new record writing to reduce the overhead of frequent new data page.

4. Each data page needs to store at least 2 rows of records. So in theory the maximum uplink record length is 8KB, but in fact it should be smaller because there are still some InnoDB internal data structures to store.

5. Limited by the InnoDB storage method, if the data is written sequentially, ideally, the filling rate of data page is 15amp 16, but generally there is no way to guarantee a complete sequential write. Therefore, the fill rate of data page is generally 1max 2 to 15max 16. Therefore, it is best for each InnoDB table to have a self-incrementing column as the primary key so that new records are written as sequentially as possible.

6. When the filling rate of data page is less than 1 / 2, InnoDB will contract to release free space.

7. MySQL version 5.6 InnoDB engine currently supports four formats: COMPACT, REDUNDANT, DYNAMIC and COMPRESSED. The default format is COMPACT. COMPRESSED is rarely used and is not recommended (see the next section). If you need to use compression features, you can directly consider the TokuDB engine.

8. COMPACT line format saves about 20% of storage space compared to REDUNDANT, and COMPRESSED saves about 50% of storage space compared to COMPACT, but results in a 90% reduction in TPS. Therefore, the COMPRESSED line format is strongly deprecated.

9, when the row format for DYNAMIC or COMPRESSED, long columns such as TEXT/BLOB (long column, may also be other longer columns, not necessarily only the TEXT/BLOB type, depending on the situation) will be completely stored in a separate data page, clustered index pages only use 20 bytes of the pointer to the new page, this is the so-called off-page, similar to ORACLE row migration, disk space waste is more serious, and IPUP O performance is poor. Therefore, BLOB, TEXT, and VARCHAR column types longer than 255are strongly discouraged.

10, when the InnoDB file format (innodb_file_format) is set to Antelope, and the row format is COMPACT or REDUNDANT, BLOB, TEXT or long VARCHAR columns will only store the first 768 bytes in the clustered index page (the maximum of 768 bytes is used to facilitate the creation of a prefix index / prefix index), and the rest of the content will be stored in additional page, even if only one more byte. Therefore, the shorter the length of all columns, the better.

11. The page of BLOB, TEXT or long VARCHAR columns stored in off-page is exclusive and cannot be shared. Therefore, it is strongly discouraged to use multiple long columns in a table.

To sum up, if you do need to store BLOB, TEXT, and long VARCHAR columns in an InnoDB table in a real business, here are some suggestions:

1. After all the data is serialized and compressed as much as possible, it is stored in the same column to avoid multiple off-page.

2. If the actual maximum storage length is less than 255, change it to VARCHAR or CHAR type (if it is variable length data, there is no difference between them, if it is fixed length data, use CHAR type).

3. If it is impossible to integrate all the columns into one column, you can choose the second choice. After permutation and combination according to the maximum length of each column, the total row length of each child table is less than 8KB as far as possible to reduce the frequency of off-page.

4. Under the premise that data page is the default 16KB, if you modify it to 8KB or other sizes, please test it according to the above theory to find the most appropriate value.

5. When the character column length is less than 255, no matter using CHAR or VARCHAR to store it, or defining the VARCHAR column length as 255, it will not cause the actual tablespace to increase.

Thank you for reading this article carefully. I hope the article "how to optimize the storage efficiency of BLOB,TEXT columns in InnoDB table" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.