OPTIMIZE TABLE 小解

最新推荐文章于 2021-05-13 14:14:40 发布

weixin_30762087

最新推荐文章于 2021-05-13 14:14:40 发布

阅读量191

点赞数

文章标签：数据库操作系统

原文链接：http://www.cnblogs.com/shengdimaya/p/5920310.html

版权

首先看一下语法:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...

我们知道mysql存储引擎里面的数据和索引数据都是物理存储的,所以说为了减少空间使用和访问表的时候能有更好的IO表现,所以说当表执行OPTIMIZE TABLE的时候,是会发生切实的变化的.

一般以下集中情况下,我们会使用OPTIMIZE TABLE来进行优化:

1:在大量的插入,更新,或者删除INNODB表以后,我们再执行是很有必要的.此时如果执行OPTIMIZE TABLE的话,整表和表上所有的索引都会重组,而且不使用的表空间会被回收给操作系统.

2:在大量的插入,更新,或者删除有全文索引的INNODB表的字段以后,不过要首先设置innodb_optimize_fulltext_only=1,也可以通过指定innodb_ft_num_word_optimize 的值来指定能在索引里面更新多少记录.记录数满的时候就要通过OPTIMIZE TABLE来优化表.

3:在 MyISAM和 ARCHIVE 表做大量的删除的时候就需要执行OPTIMIZE TABLE,或者是 MyISAM和 ARCHIVE 表做了字段长度的更改,如果此时表中有大字段的话,就更需要执行OPTIMIZE TABLE 来优化表了.因为被删除的记录的位置还是会保存,并不会进行回收,等待新纪录插入,或者OPTIMIZE TABLE 进行重组才回回收.而且最重要的一点是OPTIMIZE TABLE以后性能会得到很大的提升,特别是表做过较大的变更以后在执行,性能提升会非常的明显.

OPTIMEZE TABLE 需要的权限:

对表insert 和 select权限.

而且OPTIMIZE TABLE 对分区表也是支持的,具体的语法是

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

从上面就可以看出来OPTIMEZE TABLE对于INNODB,MyISAM和 ARCHIVE 的表都是适用的,对于其他引擎的数据表都是不适用的,如果想要使用OPTIMIZE TABLE 优化表,就要在启动mysqld的时候使用--skip-new参数启动.

OPTIMIZE TABLE 可以在线DDL,通过指定(ALGORITHM=INPLACE) ,不过这个只支持INNODB表,普通表和分区表都是支持的.表重建的方式有两种,OPTIMIZE TABLE 和 ALTER TABLE ... FORCE两种方式都可以触发表重建.

OPTIMIZE TABLE 也有适用于 ALGORITHM=COPY的一些条件:

1:当old_alter_table参数设置为ON的时候,OPTIMIZE并不会重建,而是使用一个临时表先把记录插进去来做处理.这个时候就要使用ALGORITHM=COPY来处理了.

2:当mysqld以--skip-new 为方式启动的时候

3:当INNODB表含有全文索引的时候 ,就只能够使用ALGORITHM=COPY来进行优化了

INNODB的数据存储是按照page-allocation的方式存储的,这和MYASIM引擎是不相同的,所以当我们考虑要OPTIMEZE一张表的时候,一定要先考虑以下的几个问题:

1:INNODB表的索引碎片是有所保留的,一般情况下是最多只能够占用93%的,剩余的一些是要保留起来用于update更新时候的页分裂来分配空间