OPTIMIZE TABLE

INNODB 不支持


mysql> OPTIMIZE TABLE t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |    //INNODB
| test.t | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.24 sec)

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

 

使 OPTIMIZE  table 对 innodb 支持

 mysqld --datadir=/data --basedir=/usr/local/mysql56 --user=mysql --gdb --skip-new

 

mysql> OPTIMIZE TABLE t;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

原理：映射为 alter table

By default, OPTIMIZE TABLE does not work for tables created using any other storage engine and returns a result indicating this lack of support.

You can make OPTIMIZTABLE work for other storage engines by starting mysqld with the --skip-new option. 

In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.

 

 

OPTIMIZE TABLE continues to use ALGORITHM=COPY under the following conditions:

When the old_alter_table system variable is turned ON.

set old_alter_table=on; //alter table 修改表采用 algorithm=copy 方式

When the mysqld --skip-new option is enabled.

 

 

 

 

google:

Everytime you do optimize MySQL, by using mysqlcheck -A -o or using ./mysql_optimize from here.

You may see the output
 
Table does not support optimize, doing recreate + analyze instead.
 
It is because the table that you are using is InnoDB.
 
You can optimize the InnoDB tables by using this.
 
ALTER TABLE table.name ENGINE='InnoDB';
 
This will create a copy of the original table, and drop the original table, and replace to the original place.
 
Although this is safe, but I suggest you do backup and test first before doing this.

 

mysql> ALTER TABLE t ENGINE='InnoDB';          
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t;
+---+
| a |
+---+
| 7 |
+---+
1 row in set (0.01 sec)

 

innodb的数据库不支持optimize，可以用ALTER TABLE table.name ENGINE='InnoDB';
该方法会对旧表以复制的方式新建一个新表，然后删除旧表。虽然这个过程是安全的,
但是在进行操作时还是先进行备份为好

 

 

 

MyISAM:正常

mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                        |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> OPTIMIZE TABLE t;
+--------+----------+----------+----------+
| Table  | Op       | Msg_type | Msg_text |
+--------+----------+----------+----------+
| test.t | optimize | status   | OK       |
+--------+----------+----------+----------+
1 row in set (0.00 sec)