Discuss the function of optimize table in Mysql _mysql

After you delete a large amount of data in your library, you may find that the data file size does not decrease. This is caused by the fragmentation of the data file after the delete operation. discuz! In the system Number setting interface provides the function of the data table optimization, can remove the data file fragment after the deletion operation, reduces the file size, speeds up the future reading and writing operation. You only have to do the bulk deletion, or periodically (such as every two months) to do a data table optimization operation can be.

OPTIMIZE table works by making a temporary vice of the original table

OPTIMIZE table Syntax
OPTIMIZE [Local | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

If you have deleted a large portion of the table, or if you have made many changes to a table containing a variable-length row (a table that contains VARCHAR, blobs, or text columns), you should use optimize table. The deleted record is kept in the linked list, and subsequent inserts reuse the old record location. You can use the Optimize table to reuse unused space and defragment the data file.

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

OPTIMIZE table works only for MyISAM, BDB, and InnoDB tables.

For MyISAM tables, OPTIMIZE table operates as follows:

1. If the table has been deleted or exploded, the table is repaired.
2. If the index pages are not categorized, categorize them.
3. Update if the table's statistics are not updated (and repair is not possible by classifying the indexes).

For BDB tables, OPTIMIZE table is currently mapped to the Analyze table. For the InnoDB table, the OPTIMIZE table is mapped to the ALTER TABLE, which rebuilds the table. The Rebuild operation can update the index statistics and release unused space in the clustered index. See section 13.5.2.1, "ANALYZE table syntax."

Use the-skip-new or-safe-mode option to start the mysqld. By starting mysqld, you can make optimize table work with other table types.

Note that MySQL locks the table while the optimize table is running.

The OPTIMIZE table statement is written to the binary log unless you use the No_write_to_binlog keyword (or its alias local) of your choice. Already done, so the Optimize table command for the MySQL server acts as a replication master server, and by default these commands are replicated to the replication subordinate server.