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

This article mainly introduces the detailed methods of removing MySQL table space debris, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the detailed method of clearing MySQL table space debris.

A table has 10, 000 rows of 10 bytes each, which takes up 100000 bytes of storage space. Delete operation leaves only one row, and the actual content is only 10 bytes. However, when MySQL reads, it is still treated as a 100000-byte table, so the more fragments it has, the more it will affect the query performance.

View table fragment size

(1) View the fragment size of a table

Mysql > SHOW TABLE STATUS LIKE 'Table name'

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

(2) list all tables that have been fragmented

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 table name

(2) InnoDB table

Mysql > alter table table name engine=InnoDB

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

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

Suggestion

The defragmentation operation will temporarily lock the table. The larger the amount of data, the longer the time. You can make a script that is executed regularly during the access trough. For example, check the DATA_FREE field in the early hours of every Wednesday morning, and clean it up if it is greater than the warning value you think.

After reading the above detailed methods for removing MySQL table space debris, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information section.

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.