MySQL in Optimize optimization table

Objective

When the MySQL is a large number of additions and deletions to the operation, it is easy to produce some fragments, these fragments occupy space, so there may be a lot of data deleted after the data file size changes little. Of course, the newly inserted data will still take advantage of these fragments. However, too much fragmentation has a certain effect on the insertion of data, at which point we can optimize the table by optimize.
To see data fragmentation more visually, MySQL can use the following command to view
Show table status [like table_name]
Such as

The Data_free option represents data fragmentation.
For MySQL's different database storage engine, optimize uses clear fragmentation, reclaims unused database space, and fragmented the data and indexes of distributed storage (defragmentation), which is good for I/O speed.
Of course optimize in the operation of the table, it will be locked, so it is not appropriate to often call in the program.

MyISAM Storage Engine

For the MyISAM table, use the following command directly for optimization
Optimize table Table1[,table2][,table3]
If you optimize multiple tables simultaneously, you can use commas to separate them.
The following optimizes the Dede_member_vhistory table, as you can see, the optimized Data_free value is 0.

#InnoDB存储引擎
The InnoDB engine's table is divided into the exclusive table space and the table with the shared table space, and we can see if the exclusive tablespace is turned on by show variables like ' innodb_file_per_table '.

I have opened the exclusive table space locally. At this point, the table cannot be optimize operation, if the operation, will return information, the last table does not the support optimize, doing recreate + analyze instead. Because a large number of rows are deleted under the structure, the index is reorganized and the corresponding space is freed so that it does not have to be optimized.

MySQL in Optimize optimization table