Details about how to clear table space fragments in MySQL.

Details about how to clear table space fragments in MySQL.

How to clear table space fragments in MySQL

Causes of fragmentation

(1) the storage of a table is fragmented. When a row of content is deleted, the space in the row is blank and left blank. A large number of delete operations are performed within a period of time, this will make the blank space more space than the storage list content;

(2) When the insert operation is executed, MySQL will try to use a blank space. However, if a blank space has not been occupied by suitable data, it cannot be completely occupied, fragments are formed;

(3) when MySQL scans data, the object it scans is actually the capacity requirement limit of the list, that is, the part of the area where data is written in the peak position;

For example:

A table has 10 thousand rows and each row has 10 bytes, occupying 0.1 million bytes of storage space. To delete a table, only one row is left, and only 10 bytes are left. However, when MySQL reads data, it is still regarded as a 0.1 million-byte table for processing. Therefore, the more fragments, the more query performance will be affected.

View table Fragment Size

(1) view the shard size of a table

Mysql> show table status like 'table name ';

The value of the 'data _ free' column in the result is the shard size.

(2) list all tables with shards generated

mysql> select table_schema db, table_name, data_free, engine   from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;

Clear table fragments

(1) MyISAM table

Mysql> optimize table name

(2) InnoDB table

Mysql> alter table name engine = InnoDB

Different engines have different OPTIMIZE operations. Because the index and data are separated, OPTIMIZE can sort the data files and rearrange the indexes.

The OPTIMIZE operation temporarily locks the table, and the larger the data volume, the longer the time consumed. It is not a simple query operation after all. therefore, it is inappropriate to place the Optimize command in the program. No matter how low the hit rate is, the overall hit rate will also increase when the access volume increases, this will certainly have a great impact on the program running efficiency. the better way is to make a shell, regularly check the information_schema.TABLES field in mysql, and view the DATA_FREE field. If it is greater than 0, it indicates that there are fragments.

Suggestions

The clear fragmentation operation will temporarily lock the table. The larger the data volume, the longer the time it takes. You can make a script and execute it regularly at the low access point. For example, check the DATA_FREE field every Wednesday morning, if the value is greater than the warning value, it will be cleared once.

If you have any questions, please leave a message or go to the community on this site for discussion. Thank you for reading this article. Thank you for your support!