the use of MySQL data files is only extended and not recycled. After you perform a delete on a table, the data files on the disk do not shrink. the usual practice is to logically export and then truncate the original table (or delete the Rebuild) before importing. another way to do this is to optimize table, but there are certain limitations. My colleague asked me on the InnoDB on a 200g+ table, using the Optimize table to defragment, reclaim space, the execution of fast 3 hours, not only the space is not reduced, but also increased by more than 200 g, disk space is exhausted. Ask me what to do with it. This problem has not been met before. So today I studied and sorted out the next.about the effect of optimize table reclaim space here is no demo, a bunch of online.
1. Scope of applicationOPTIMIZE table works only on MyISAM, BDB, and InnoDB tables. three different storage engines, the processing logic of optimize table is not the same. The most critical of this is the processing logic:
MyISAM:1. If the table has been deleted or the row is broken down, repair the table. 2. If the index pages are not categorized, categorize them. 3. If the statistics for the table are not updated (and cannot be repaired by classifying the indexes), then the updates are made.
BDB:The OPTIMIZE table is currently mapped to the Analyze table.
INNODB:OPTIMIZE table is mapped to ALTER TABLE ... Force, which will rebuild the table. The Rebuild operation can update the index statistics and free unused space in the clustered index. before 5.6.17, optimize table locks tables, starting with 5.6.17, optimize table becomes an online operation, which only occurs in two stages of parsing and committing, and these two processes are extremely short and can be ignored. In other words, the DML operation does not cause clogging. MySQL uses the Copy method to copy data when the following two conditions are met (copy is faster than insert):
- Open mysqld
--skip-new 选项
- old_alter_table system variable set to enable state
Note that if the table contains a fulltext index, the Optimize table operation uses the Copy method instead of the online way
2. Do not record optimize table operation in Binlog OPTIMIZE no_write_to_binlog TABLE
3. Problems encounteredIf you encounter the following error when using optimize:[email protected]: Test 22:10:22> optimize Table tab;+----------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+----------+----------+----------+-------------------------------------------------------------------+| test.tab | optimize | Table does not support optimize, doing recreate + analyze instead || test.tab | optimize | status | OK |+----------+----------+----------+-------------------------------------------------------------------+2 rows in Set (29.88 sec)use the--skip-new or--safe-mode option to start mysqld. By starting mysqld, you can make optimize table work for other table types.
MySQL optimize table
