Is it necessary to split the TEXT field into an independent table? _ MySQL

Is it necessary to split the TEXT field into an independent table? BitsCN.com

I have been asked more than once:

Is it necessary to split a wide table containing TEXT fields into independent tables to improve performance?

My personal opinion is as follows:In general, it is quite advantageous to split the TEXT field from a table with frequent operations and become an independent table with a Key-Value structure..

Its advantages are mainly reflected in the following three aspects:

PS: The following discussions are based on the Innodb engine.

1. easy O & M

Currently, Innodb-plugin has TABLE-LOCK for most DDL statements. This means that the longer the DDL time for a table, the longer the service downtime.

The two key factors that determine the execution duration of a DDL command are the number of table rows and the physical file size of the table.

The split of the TEXT field is independent, which can effectively reduce the size of the physical file of the master table.

It is not difficult to see that such splitting can greatly reduce O & M costs for a table that is very important to the business or frequently accessed.

2. facilitate the implementation of cache solutions and data product migration

The Key-volume Value data type is not a strong point for MySQL.

After the TEXT is split into a table with a simple structure such as K-V, it is very convenient to realize the migration of data products by modifying less code.

Whether it's Mongo's _ id: value, redis's string, or memcached's key-value, you can easily import data.

In addition, aside from the cache scheme, you can also separately configure row-format = compressed innodb compression parameters for the split Independent table based on the considerations of saving MySQL disk space. Reduces the volume of physical files and increases the content that can be stored on a single data page, which improves QPS to a certain extent.

3. improve query performance

As mentioned above, after the compression option is added after the split, the QPS of the K-V table will be improved compared with the previous.

In addition, this scheme improves the query performance of the main table in the Antelope file format (the Barracuda file format is no different ).

Because of the Compact and Redundant file formats of Antelope, the leftmost 786 bytes of long fields are stored in the data page of the Primary Key.

In the file format of Barracuda, all TEXT fields are stored in off-page, while the data page of Primary Key stores only one 20-byte pointer.

Splitting can save the data page space of 786B for the former, while that for the latter is only 20 B. This is why the performance improvement of the former is even greater.

BitsCN.com