Detailed example of clearing table space fragmentation in MySQL

Cause of fragmentation

The storage of the table will be fragmented. Whenever a row of content is deleted, the space of this segment will become Blank, left blank, and a large number of deletions over a period of time will make this blank space larger than the space used to store the contents of the list; when performing an insertion operation, MySQL will try to use the blank space, However, if a certain empty space has not been occupied by data of appropriate size, and it still cannot be completely occupied, it will become fragmented; when MySQL scans the data, the object it scans is actually the upper limit of the capacity requirement of the list, that is, the data The part of the written area that is at the peak position; in this article, we mainly share with you the relevant information on MySQL clearing table space fragmentation examples. We hope that this article can help everyone, and friends in need can refer to it.

Recommended related mysql video tutorials: "mysql tutorial"

For example:

A table has 10,000 rows, each row has 10 words section will occupy 100,000 bytes of storage space. If the delete operation is performed, only one row will be left. The actual content will only be 10 bytes. However, when MySQL reads it, it will still treat it as a 100,000-byte table for processing. Therefore, The more fragmentation there is, the more it will affect query performance.

Check the fragment size of the table

(1) Check the fragment size of a certain table

mysql> SHOW TABLE STATUS LIKE '表名';

The value of the 'Data_free' column in the result is the fragment size

(2) List all fragmented tables

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 表名

(2) InnoDB table

mysql> alter table 表名 engine=InnoDB

Engine is different, and the operation of OPTIMIZE is also different. MyISAM because the index and data are separated, OPTIMIZE can organize the data files and rearrange the index.

The OPTIMIZE operation will temporarily lock the table, and the larger the amount of data, the longer it will take. After all, it is not a simple query operation. Therefore, it is inappropriate to put the Optimize command in the program. No matter how low the hit rate is, when When the number of visits increases, the overall hit rate will also increase, which will definitely have a great impact on the running efficiency of the program. A better way is to be a shell and regularly check the information_schema.TABLES field in mysql , check the DATA_FREE field. If it is greater than 0, it means there is fragmentation.

Recommendation

Clearing the fragmentation operation will temporarily lock the table. The larger the amount of data, the longer it will take. , you can make a script and execute it regularly during low access times. For example, in the early morning of every Wednesday, check the DATA_FREE field. If it is greater than the warning value you think, clean it once.

Related recommendations:

mysql-MySQL clear table space problem (version 5.6)

Mysql query cache fragmentation, Cache hit rate and Nagios monitoring

Analyzing the causes of fragmentation in the mysql table and cleaning up_PHP tutorial

The above is the detailed content of Detailed example of clearing table space fragmentation in MySQL. For more information, please follow other related articles on the PHP Chinese website!