OPTIMIZE TABLE语句
OPTIMIZE [NO_WRITE_TO_BINLOG |LOCAL ]TABLE tbl_name [, tbl_name] ...
OPTIMIZE TABLE
重新组织表数据和关联索引数据的物理存储,以减少存储空间并提高访问表时的I / O效率。对每个表所做的确切更改取决于该表使用的存储引擎。
OPTIMIZE TABLE
在以下情况下使用,取决于表的类型:
- 对
InnoDB
具有自己的.ibd文件的表进行实质性的插入,更新或删除操作后,因为该文件是在innodb_file_per_table
启用了该选项的情况下创建的。重组表和索引,并可以回收磁盘空间以供操作系统使用。 - 对表中作为
FULLTEXT
索引一部分的列进行大量的插入,更新或删除操作之后InnoDB
。首先设置配置选项innodb_optimize_fulltext_only=1
。为了将索引维护期保持在合理的时间,请设置innodb_ft_num_word_optimize
选项以指定要在搜索索引中更新的单词数,并运行一系列OPTIMIZE TABLE
语句,直到完全更新搜索索引。 - 删除的大部分后
MyISAM
或ARCHIVE
表,或者使许多变化为MyISAM
或ARCHIVE
具有可变长度的行表(表具有VARCHAR
,VARBINARY
,BLOB
,或TEXT
列)。删除的行将保留在链接列表中,后续INSERT
操作将重用旧的行位置。您可以OPTIMIZE TABLE
用来回收未使用的空间和对数据文件进行碎片整理。在对表进行大量更改之后,该语句还可以提高使用该表的语句的性能,有时甚至可以显着提高性能。
该声明要求SELECT
和INSERT
对表的权限。
OPTIMIZE TABLE
工程InnoDB
,MyISAM
和ARCHIVE
表。OPTIMIZE TABLE
内存NDB
表中的动态列也受支持。它不适用于内存表的固定宽度列,也不适用于磁盘数据表。OPTIMIZE
可以使用来调整NDB群集表上的性能--ndb-optimization-delay
,它控制通过来处理批处理行之间的等待时间OPTIMIZE TABLE
。有关更多信息,请参见“ NDB Cluster 8.0中解决的先前的NDB Cluster问题”。
对于NDB群集表,OPTIMIZE TABLE
可以通过(例如)杀死执行该OPTIMIZE
操作的SQL线程来中断。
默认情况下,OPTIMIZE TABLE
它不适用于使用任何其他存储引擎创建的表,并返回指示缺少此支持的结果。您可以OPTIMIZE TABLE
通过使用该选项启动mysqld来使其他存储引擎工作--skip-new
。在这种情况下,OPTIMIZE TABLE
仅映射到ALTER TABLE
。
该语句不适用于视图。
OPTIMIZE TABLE
支持分区表。有关将此语句与分区表和表分区一起使用的信息,请参见“分区维护”。
默认情况下,服务器将OPTIMIZE TABLE
语句写入二进制日志,以便它们复制到复制从属服务器。要禁止记录日志,请指定可选NO_WRITE_TO_BINLOG
关键字或其别名LOCAL
。
- 优化表输出
- InnoDB详细信息
- MyISAM详细信息
- 其他注意事项
优化表输出
OPTIMIZE TABLE
返回具有下表所示列的结果集。
柱 | 值 |
---|---|
Table | 表名 |
Op | 总是optimize |
Msg_type | status ,error ,info ,note ,或warning |
Msg_text | 信息性消息 |
OPTIMIZE TABLE
表捕获并引发将表统计信息从旧文件复制到新创建的文件时发生的任何错误。例如。如果.MYD
或.MYI
文件所有者的用户ID与mysqld进程的用户ID不同,则OPTIMIZE TABLE
除非用户启动mysqld,否则将生成“无法更改文件所有权”错误root
。
InnoDB详细信息
对于InnoDB
表,OPTIMIZE TABLE
映射到ALTER TABLE ... FORCE
,从而重建表以更新索引统计信息并释放聚集索引中的未使用空间。OPTIMIZE TABLE
在InnoDB
表上运行它时,它会显示在输出中,如下所示:
mysql>OPTIMIZE TABLE foo; +---------- +---------- +---------- +------------------------------------------------------------------- + | Table | Op | Msg_type | Msg_text | +---------- +---------- +---------- +------------------------------------------------------------------- + | test.foo | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.foo | optimize | status | OK | +---------- +---------- +---------- +------------------------------------------------------------------- +
OPTIMIZE TABLE
对常规表和分区表使用在线DDLInnoDB
,从而减少了并发DML操作的停机时间。由OPTIMIZE TABLE
覆盖触发并执行的表重建ALTER TABLE ... FORCE
已就位。排他表锁定仅在操作的准备阶段和提交阶段短暂进行。在准备阶段,将更新元数据并创建一个中间表。在提交阶段,将提交表元数据更改。
OPTIMIZE TABLE
在以下情况下,使用表复制方法重建表:
- 当
old_alter_table
系统变量启用。 - 使用该
--skip-new
选项启动服务器时。
OPTIMIZE TABLE
包含索引的表不支持使用联机DDL。而是使用表复制方法。InnoDB
FULLTEXT
InnoDB
使用页面分配方法存储数据,并且不会像传统存储引擎(如MyISAM
)那样遭受碎片化的困扰。在考虑是否运行优化时,请考虑服务器将处理的事务工作量:
- 预计会有一些碎片。
InnoDB
只填满 93%的页面,为更新留出空间,而不必拆分页面。 - 删除操作可能会留下空白,从而使页面无法充满所需的空间,这可能值得优化表。
- 当有足够的空间可用时,对行的更新通常会重写同一页中的数据,具体取决于数据类型和行格式。请参见“ InnoDB表的压缩方式”和“ InnoDB行格式”。
- 高并发工作负载可能会随着时间的流逝而在索引中留下空白,因为
InnoDB
由于其MVCC机制而保留了同一数据的多个版本。请参见“ InnoDB多版本”。
MyISAM详细信息
对于MyISAM
表,其OPTIMIZE TABLE
工作方式如下:
- 如果表已删除或拆分行,请修复该表。
- 如果索引页未排序,请对其进行排序。
- 如果表的统计信息不是最新的(并且无法通过对索引进行排序来修复),请对其进行更新。
其他注意事项
OPTIMIZE TABLE
在线执行常规InnoDB
表和分区表。否则,MySQL 将在OPTIMIZE TABLE
运行期间锁定表。
OPTIMIZE TABLE
不对R树索引(例如POINT
列上的空间索引)进行排序。错误23578)