Function of optimize table in MySQL

After a large amount of data is deleted from your database, you may find that the size of the data file is not reduced. This is because fragments are left in the data file after the delete operation. Discuz! The data table optimization function is provided on the system number settings page to remove the data file fragments left after the delete operation, reduce the file size, and accelerate future read/write operations. You only need to delete data tables in batches or optimize data tables on a regular basis (such as every one or two months.

Optimize table works by making a temporary copy of the original table

Optimize table syntax

Optimize [local | no_write_to_binlog] Table tbl_name [, tbl_name]...

If you have deleted a majority of the tables, or if you have made many changes to the tables that contain variable-length rows (tables that contain varchar, blob, or text columns, optimize table should be used. The deleted records are kept in the Link List. Subsequent insert operations will reuse the old record location. You can use optimize table to reuse unused space and organize data file fragments.

In most settings, you do not need to run optimize table. Even if you have made a large number of updates to a variable-length row, you do not need to run it frequently, once a week or once a month, and only run on a specific table.

Optimize table only applies to MyISAM, bdb, and InnoDB tables.

For MyISAM tables, optimize tables are operated as follows:

1. If the table has been deleted or broken down, the table will be repaired.

2. If the index page is not classified, the page is classified.

3. If the statistical data of the table is not updated (and cannot be repaired by classifying the index), update the data.

For bdb tables, optimize table is currently mapped to analyze table. For InnoDB tables, optimize table is mapped to alter table, which will recreate the table. The rebuilding operation can update index statistics and release unused space in the Cluster Index. See section 13.5.2.1 "analyze table Syntax ".

Use the-Skip-new or-safe-mode option to start mysqld. By starting mysqld, you can enable optimize table to work for other table types.

Note: During the optimize table operation, MySQL locks the table.

The optimize TABLE statement is written to the binary log unless the optional no_write_to_binlog keyword (or its alias is local) is used ). Therefore, the optimize table command for MySQL Server serves as a replication master server. By default, these commands will be copied to the replication slave server.