Test the optimize today, found good, the following is the test process:
create table ' Moe ' ( ' Phonenum ' varchar (one) NOT NULL, ' Citynum ' int (one) NOT NULL DEFAULT ' 0 ', ' phonetype ' int (one) default null, primary key (' Phonenum ', ' Citynum ')) load data infile '/ Tmp/hun.txt ' into table moe FIELDS TERMINATED BY ', ' enclosed by ' ' LINES TERMINATED BY ' \ n '; mysql> select count (*) from moe; +----------+| count (*) |+----------+| 4232081 |+----------+1 row in set (1.75 SEC) [[EMAIL PROTECTED] MOE]$ LL |GREP MOE-RW-RW---- 1 Mysql mysql 8644 9 Month 19 15:03 MOE.FRM-RW-RW---- 1 mysql mysql 310378496 9 Month 19 15:15 Moe.ibdmysql> show index from moe;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+- -------+------+------------+---------+---------------+| table | non_unique | key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | packed | null | index_type | comment | index_ comment |+-------+------------+----------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+| moe | 0 | PRIMARY | 1 | phonenum | A | 4219450 | null | null | | btree | | | | moe | 0 | primary | 2 | citynum | A | 4219450 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec) mysql> delete from moe limit 2100000; Query OK, 2100000 rows affected, 1 warning (18.06 sec) mysql> select count (*) from moe;+----------+| count (*) |+----------+| 2132081 |+----------+1 row in set (2.14 sec) mysql> show index from moe;+-------+------------+----------+--------------+-------------+-----------+-------------+-------- --+--------+------+------------+---------+---------------+| table | non_unique | key_ name | seq_in_index | column_name | collation | cardinality | Sub_part | Packed | Null | Index_type | Comment | index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+| moe | 0 | PRIMARY | 1 | phonenum | A | 2272822 | NULL | null | | btree | | | | moe | 0 | primary | 2 | citynum | a | 2272822 | null | null | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+--------- -+--------+------+------------+---------+---------------+2 rows in set (0.00 sec) Index reduced by almost half [[EMAIL PROTECTED] MOE]$ LL |GREP MOE-RW-RW---- 1 mysql Mysql 8644 9 month   19 15:03 MOE.FRM-RW-RW---- 1 mysql mysql 310378496 9 month   19 15:26 MOE.IBD table size unchanged optimize table moe;mysql> show index from moe;+-------+------------+----------+--------------+-------------+-----------+-- -----------+----------+--------+------+------------+---------+---------------+| table | non_ unique | key_name | seq_in_index | column_name | collation | Cardinality | Sub_part | Packed | Null | Index_type | comment | index_comment |+-------+------------+----------+--------------+-------------+-------- ---+-------------+----------+--------+------+------------+---------+---------------+| moe | 0 | PRIMARY | 1 | phonenum | a | 2126976 | &nBsp null | null | | btree | | | | moe | 0 | primary | 2 | citynum | A | 2126976 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 SEC) mysql> select count (*) from moe; +----------+| count ( *) |+----------+| 2132081 |+----------+1 row in set (0.84 sec) [[ EMAIL PROTECTED] MOE]$ LL |GREP MOE-RW-RW---- 1 mysql mysql 8644 9 Moon   19 15:27 MOE.FRM-RW-RW---- 1 mysql Mysql 92274688 9 Month   19 15:28 MOE.IBD
The official documents cited are as follows:
13.5.2.5. OPTIMIZE TABLE Syntax
OPTIMIZE [LOCAL |no_write_to_binlog] TABLE tbl_name [, Tbl_name] ...
If you have deleted a large part of the table, or if you have a table containing variable-length rows (containing VARCHAR, BLOB, or TEXT column.
table), you should use the OPTIMIZE TABLE. The deleted records are kept in the linked list, and subsequent INSERT operations
re-use the old record location. You can use OPTIMIZE TABLE to re-use unused space and defragment the data file.
in most settings, you don't need to run OPTIMIZE TABLE. Even if you have a large number of updates to variable-length lines, you do not need to
to run frequently, once a week or once a month, run only on specific tables.
optimizetable only to MyISAM, BDB and the InnoDB table works.
for InnoDB table,OPTIMIZE table is mapped to the ALTER tables, which rebuilds the sheet.
Note that the OPTIMIZE TABLE During the run, MySQL The table is locked.
The OPTIMIZE TABLE statement is written to the binary log unless you use the optional no_write_to_binlog keyword (or
name LOCAL). has already done so, the OPTIMIZE TABLE command for MySQL Server acts as a replication master service
By default, these commands will be copied to the replication subordinate server.
This article is from the "just out of the shell of the Birds" blog, please be sure to keep this source http://qhd2004.blog.51cto.com/629417/1555194
MySQL's optimize