optimize在mysql中的用法



http://jingyan.baidu.com/article/6fb756eca47c6b241858fba3.html

optimize命令是mysql的常用优化命令，但是在InnoDB与MyISAM这两个存储引擎中却有很大的分别。本文将对这两个常用的存储引擎进行讨论

百度经验:jingyan.baidu.com

工具/原料

• mysql，linux

百度经验:jingyan.baidu.com

方法/步骤

1. 当对表有大量的增删改操作时，需要用optimize对表进行优化。可以减少空间与提高I/O性能，命令optimize table tablename;假如有foo表且存储引擎为MyISAM。

   mysql>optimize table foo;

   +------------+----------+----------+----------+

   | Table      | Op       | Msg_type | Msg_text |

   +------------+----------+----------+----------+

   | test.foo| optimize | status   | OK       |

   +------------+----------+----------+----------+

2. 如果是InnoDB引擎，首先查看innodb_file_per_table（是否独享表空间）。

   mysql>show variables like 'innodb_file_per_table';

   +-----------------------+-------+

   | Variable_name         | Value |

   +-----------------------+-------+

   | innodb_file_per_table | OFF   |

   +-----------------------+-------+

   OFF代表开启共享表空间没有打开，即采用的是默认的共享表空间。这个时候可以在mysql的datadir路径下看到一个非常大的文件ibdata1，这个文件存储了所有InnoDB表的数据与索引。

   
   

3. 如果foo是InnoDB，执行如下命令

   mysql>optimeze table foo;

   会返回如图信息，最后的一条Table does not support optimize, doing recreate + analyze instead，即代表optimize无法优化表。

   这个时候使用如下命令优化表

   mysql>alter table foo ENGINE = 'InnoDB';

   mysql>analyze table foo;

   返回如下信息

                          

   +------------------------+---------+----------+----------+

   | Table                  | Op      | Msg_type | Msg_text |

   +------------------------+---------+----------+----------+

   | test.foo | analyze | status   | OK       |

   +------------------------+---------+----------+----------+

   即可优化该表                             

   

4. 如果开启了独享表空间，即每张表都有ibdfile。这个时候如果删除了大量的行，索引会重组并且会释放相应的空间因此不必优化

5. 由于共享表空间所有表的数据与索引都存放于ibddata1文件中，随着数据量的增长会导致该文件越来越大。超过10G的时候查询速度就非常慢，因此在编译的时候最好开启独享表空间。因为mysql默认是关闭了独享表空间，下面有两个解决方案

6. 方案一：先逻辑备份所有的数据库，将配置文件中innodb_file_per_table参数=1，再将备份导入

7. 方案二：只要修改innodb_file_per_table参数，然后将需要修改的所有innodb的表都运行一遍 alter table table_name engine=innodb;即可使用第二种方式修改后，原来库中的表中的数据会继续存放于ibdata1中，新建的表才会使用独立表空间