MySQL's optimize table command

Sometimes, when using MySQL, you may find that even though a table deletes a lot of data, the data and index files of this table are surprisingly small. This is because when MySQL deletes data (especially text and blobs), it leaves a lot of data holes that occupy the space of the original data, so the size of the file does not change. These voids may be re-used when inserting data later on, and of course it may be there. This void not only adds additional storage costs, but also reduces the efficiency of the table's scanning because of fragmentation of data.

MySQL provides a way to solve this problem: the Optimize table table_name command. The command will defragment the table to remove the holes. View before and after effects can use the Show Table Status command, such as show table status from [database] LIKE ' [table_name] '; Returns the data_free in the result is the storage space occupied by the hole. When you finish executing the Optimize table command, the value of the field will be 0.

A table of type InnoDB cannot be used with the Optimize table command. Forced use will return the following results:

However, using ALTER TABLE table_name ENGINE=INNODB can also achieve the same effect of releasing voids. This is because when the data engine is converted (even if there is no real conversion), MySQL reads the data from the table and then writes it back, and in this process, the void is naturally lost.

Optimze table is very fast in the case of a table with a small amount of data, but it is tragic to have a table with a large number of data optimze, because the execution time will be long and the table will be locked. At this time, consideration should be given to the use of some operational measures to avoid the impact of existing network services.