MySQL优化之OPTIMIZE TABLE

  |   0 评论   |   2,613 浏览

用途

OPTIMIZE TABLE语句整理表数据和相关索引数据的物理存储,以减少存储空间并提高访问表时的I / O效率。对每个表的变化取决于该表使用的存储引擎。

使用语法

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

适用场景

在下面这些情况下使用OPTIMIZE TABLE,具体取决于表的类型:

  • 在具有自己的.ibd文件的InnoDB表上进行大量插入、更新或删除操作之后,因为它是在启用了innodb_file_per_table选项的情况下创建的。表和索引被重新整理,磁盘空间可以回收以供操作系统使用。

  • 对InnoDB表中的FULLTEXT索引的一部分列进行大量插入,更新或删除操作之后。首先设置innodb_optimize_fulltext_only = 1。为了保持索引维护时间在合理的时间内,请设置innodb_ft_num_word_optimize选项,以指定要更新search index的字数,并运行一批OPTIMIZE TABLE语句,直到search index完全更新。

  • 删除大部分的MyISAM或ARCHIVE表之后,或对具有可变长度行(具有VARCHAR,VARBINARY,BLOB或TEXT列的表)的MyISAM或ARCHIVE表进行了许多更改。已删除的行被维护在链表中,后续的INSERT操作重用旧的行位置。您可以使用OPTIMIZE TABLE来回收未使用的空间并对数据文件进行碎片整理。在对表进行大量更改后,此语句还可能会提高使用表的语句的性能,有时效果会显着。

所需权限

此语句需要表的SELECT和INSERT权限。

其它

OPTIMIZE TABLE适用于InnoDB,MyISAM和ARCHIVE表。对于内存中NDB表的动态列,也支持OPTIMIZE TABLE。它不适用于固定宽度的内存表中的列,也不适用于磁盘数据表。可以使用-ndb_optimization_delay调整NDB上的OPTIMIZE的性能。可以使用--ndb_optimization_delay来调整集群表的性能,该对象控制通过OPTIMIZE TABLE处理批次行之间等待的时间长度。有关更多信息,请参阅NDB Cluster 7.3中解决的上一个NDB群集问题。

对于NDB群集表,可以通过(例如)中断OPTIMIZE TABLE来杀死执行OPTIMIZE操作的SQL线程。

默认情况下,OPTIMIZE TABLE对于使用任何其他存储引擎创建的表不起作用,并返回指示缺少支持的结果。您可以使用--skip-new选项启动mysqld,使其他存储引擎的OPTIMIZE TABLE工作。在这种情况下,OPTIMIZE TABLE刚刚映射到ALTER TABLE。

此声明不适用于观点。

分区表支持OPTIMIZE TABLE。有关将分区表和表分区使用此语句的信息,请参见第22.3.4节“分区维护”。

只操作主库

默认情况下,服务器将OPTIMIZE TABLE语句写入二进制日志,以便它们复制到复制从站。要禁止日志记录,请指定可选的NO_WRITE_TO_BINLOG或LOCAL。

实例

查看优化前空间占用

[root@gateway2 hera]# du -h -s hera/JOB_Task*
12K hera/JOB_Task.frm
228G hera/JOB_Task.ibd

执行优化

mysql> OPTIMIZE TABLE JOB_Task;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| hera.JOB_Task | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| hera.JOB_Task | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+


查看优化后空间占用

[root@gateway2 hera]# du -h -s hera/*
12K hera/JOB_Task.frm
1.8G hera/JOB_Task.ibd
mysql> show table status from hera;
+------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
| JOB_Task | InnoDB | 10 | Compact | 32854 | 52839 | 1735983104 | 0 | 4685824 | 4194304 | 122265 | 2017-09-15 17:21:36 | NULL | NULL | utf8_general_ci | NULL | | |

引用链接

OPTIMIZE TABLE Syntax

读后有收获可以支付宝请作者喝咖啡