精华内容
下载资源
问答
  • OPTIMIZE TABLE

    2020-12-12 11:16:51
    https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html 13.7.2.4 OPTIMIZE TABLE Syntax ...OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data,

    https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

    13.7.2.4 OPTIMIZE TABLE Syntax

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

    OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce
    storage space and improve I/O efficiency when accessing the table. The exact changes made to each
    table depend on the storage engine used by that table.

    优化表重新组织表数据和相关索引数据的物理存储,以减少存储空间和提高访问表时的I/O效率。对每一个表依赖于该表使用的存储引擎。

    Use OPTIMIZE TABLE in these cases, depending on the type of table:

    在这些情况下,请根据表的类型使用OPTIMIZE TABLE:

    After doing substantial insert, update, or delete operations on an InnoDB table that has its own
    .ibd file because it was created with the innodb_file_per_table option enabled. The table and
    indexes are reorganized, and disk space can be reclaimed for use by the operating system

    在对一个拥有自己的InnoDB表执行大量的insert、update或delete操作之后.ibd文件,因为它是在启用innodb_file_per_table选项的情况下创建的。table和

    索引被重新组织,磁盘空间可以被回收以供操作系统使用.

    After doing substantial insert, update, or delete operations on columns that
    are part of a FULLTEXT index in an InnoDB table. Set the configuration option
    innodb_optimize_fulltext_only=1 first. To keep the index maintenance period to a
    reasonable time, set the innodb_ft_num_word_optimize option to specify how many words to
    update in the search index, and run a sequence of OPTIMIZE TABLE statements until the search
    index is fully updated.

    在对要执行以下操作的列进行大量的插入,更新或删除操作之后是InnoDB表中FULLTEXT索引的一部分。 设置配置选项
    首先是innodb_optimize_fulltext_only = 1。 使索引维护期保持在在合理的时间,设置innodb_ft_num_word_optimize选项以指定要输入的单词数
    更新搜索索引,并运行一系列OPTIMIZE TABLE语句,直到进行搜索索引已完全更新。

    After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or
    ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT
    columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse
    old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment
    the data file. After extensive changes to a table, this statement may also improve performance of
    statements that use the table, sometimes significantly.

    删除MyISAM或ARCHIVE表的很大一部分,或对MyISAM或
    具有可变长度行的ARCHIVE表(具有VARCHAR,VARBINARY,BLOB或TEXT的表
    列)。 删除的行将保留在链接列表中,并且后续的INSERT操作可重复使用
    旧行位置。 您可以使用OPTIMIZE TABLE回收未使用的空间并进行碎片整理
    数据文件。 在对表进行大量更改之后,此语句还可以提高表的性能。
    使用该表的语句,有时很明显。

    This statement requires SELECT and INSERT privileges for the table.

     该语句需要对该表具有SELECT和INSERT权限。

    OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDB tables. It does not work for fixed-width columns of inmemory tables, nor does it work for Disk Data tables. The performance of OPTIMIZE on NDB Cluster tables can be tuned using --ndb-optimization-delay, which controls the length of time to wait between processing batches of rows by OPTIMIZE TABLE. For more information, see Previous NDB
    Cluster Issues Resolved in NDB Cluster 7.3.

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

    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 OPTIMIZE TABLE work for other storage
    engines by starting mysqld with the --skip-new option. In this case, OPTIMIZE TABLE is just mapped to ALTER TABLE.
    This statement does not work with views

    默认情况下,OPTIMIZE TABLE不适用于使用任何其他存储引擎创建的表,并返回指示缺少支持的结果。 您可以使OPTIMIZE TABLE用于其他存储
    通过使用--skip-new选项启动mysqld来启动引擎。 在这种情况下,OPTIMIZE TABLE仅映射到ALTER TABLE。
    该语句不适用于视图

    OPTIMIZE TABLE is supported for partitioned tables. For information about using this statement with
    partitioned tables and table partitions, see Section 22.3.4, “Maintenance of Partitions”.
    By default, the server writes OPTIMIZE TABLE statements to the binary log so that they replicate to
    replication slaves. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its
    alias LOCAL.

    分区表支持OPTIMIZE TABLE。 有关在以下情况下使用此语句的信息:
    分区表和表分区,请参见第22.3.4节“分区维护”。
    默认情况下,服务器将OPTIMIZE TABLE语句写入二进制日志,以便将它们复制到
    复制从属
    。 要禁止记录日志,请指定可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL。

    OPTIMIZE TABLE Output

    OPTIMIZE TABLE returns a result set with the columns shown in the following table.

    OPTIMIZE TABLE返回具有下表所示列的结果集。

     

    OPTIMIZE TABLE table catches and throws any errors that occur while copying table statistics from
    the old file to the newly created file. For example. if the user ID of the owner of the .frm, .MYD, or
    .MYI file is different from the user ID of the mysqld process, OPTIMIZE TABLE generates a "cannot
    change ownership of the file" error unless mysqld is started by the root user.

    从表复制表统计信息时,OPTIMIZE TABLE表会捕获并引发任何错误
    旧文件到新创建的文件。 例如。 .frm,.MYD或所有者的用户ID,或者
    .MYI文件不同于mysqld进程的用户ID,OPTIMIZE TABLE生成“无法
    除非root用户启动mysqld,否则更改文件所有权”错误。

    InnoDB Details

    For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the
    output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

    对于InnoDB表,OPTIMIZE TABLE映射到ALTER TABLE ... FORCE,这将重建表以更新索引统计信息并释放聚簇索引中未使用的空间。 这显示在
    在InnoDB表上运行OPTIMIZE TABLE的输出,如下所示:

    OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces
    downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE and
    performed under the cover by ALTER TABLE ... FORCE is completed in place. An exclusive table
    lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase,
    table metadata changes are committed.

    OPTIMIZE TABLE对常规和分区InnoDB表使用联机DDL,这将减少并发DML操作的停机时间。Table触发重建由OPTIMIZE TABLE和ALTER TABLE ... FORCE执行

    完全覆盖到位。独占锁只在操作的准备阶段和提交阶段短暂执行。在准备阶段,更新元数据并创建中间表。在提交阶段,

    表元数据更改已提交

    OPTIMIZE TABLE rebuilds the table using the table copy method under the following conditions:

    • When the old_alter_table system variable is enabled.
    • When the mysqld --skip-new option is enabled.

    在以下情况下,OPTIMIZE TABLE使用表复制方法重建表:
    • 启用old_alter_table系统变量时。
    • 启用mysqld --skip-new选项时。

    OPTIMIZE TABLE using online DDL is not supported for InnoDB tables that contain FULLTEXT
    indexes. The table copy method is used instead.

    包含全文的InnoDB表不支持使用联机DDL优化表索引。而是使用表复制方法。

    InnoDB stores data using a page-allocation method and does not suffer from fragmentation in the
    same way that legacy storage engines (such as MyISAM) will. When considering whether or not to run
    optimize, consider the workload of transactions that your server will process:

    InnoDB使用页面分配方法存储数据,并且在与传统存储引擎(如MyISAM)一样。在考虑是否参选时优化,请考虑服务器将处理的事务的工作负载:

    • Some level of fragmentation is expected. InnoDB only fills pages 93% full, to leave room for updates without having to split pages.

    预计会有一些碎片。 InnoDB只填满93%的页面,为更新留出空间,而不必拆分页面。

    • Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.

    Delete操作可能会留下空白,使页面的填充度低于预期,这可能使优化表变得有价值。

    • Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is available. See Section 14.9.1.5, “How Compression Works for

    InnoDB Tables” and Section 14.11, “InnoDB Row Formats”.

    当有足够的空间时,对行的更新通常会重写同一页中的数据,具体取决于数据类型和行格式。参见第14.9.1.5节,“压缩是如何工作的

    “InnoDB表”和第14.11节“InnoDB行格式”。

    •  High-concurrency workloads might leave gaps in indexes over time, as InnoDB retains multiple versions of the same data due through its MVCC mechanism. See Section 14.3, “InnoDB Multi-Versioning”.

    随着时间的推移,高并发工作负载可能会在索引中留下空白,因为InnoDB通过其MVCC机制保留了同一数据的多个版本。请参阅第14.3节“InnoDB多版本控制”。

     

    MyISAM Details

    For MyISAM tables, OPTIMIZE TABLE works as follows:
    1. If the table has deleted or split rows, repair the table.
    2. If the index pages are not sorted, sort them.
    3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

    对于MyISAM表,OPTIMIZE TABLE的工作方式如下:
    1.如果表已删除或拆分行,请修复该表。
    2.如果索引页未排序,请对其进行排序。
    3.如果表的统计信息不是最新的(并且无法通过对索引进行排序来修复),请对其进行更新。

     

    展开全文
  • optimize table

    2016-09-08 18:45:00
    optimize table在优化MySQL时很重要。所以对于写比较频烦的表,要定期进行optimize,如一个月一次。 注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。 转载于:...

    optimize table在优化MySQL时很重要。所以对于写比较频烦的表,要定期进行optimize,如一个月一次。

    注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表。

    转载于:https://www.cnblogs.com/loweringye/p/5854126.html

    展开全文
  • Mysql OPTIMIZE TABLE

    2021-01-08 23:08:45
    13.7.2.4 OPTIMIZE TABLE Syntax OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to ...

    13.7.2.4 OPTIMIZE TABLE Syntax

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

    OPTIMIZE TABLE reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table.

    optimize table 重新组织表数据和相关索引数据的物理存储,以减少存储空间,提高访问表时的I/O效率。对每个表所做的确切更改取决于该表使用的存储引擎。

     

    Use OPTIMIZE TABLE in these cases, depending on the type of table:

    在这些情况下,根据表的类型使用“optimize table”:

    • After doing substantial insert, update, or delete operations on an InnoDB table that has its own.ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.

    在一个innodb表上操作大量的增删改操作,这个表有自己的.ibd文件,因为innodb_file_per_table选择被启用了。表和索引被重新组织,磁盘空间可以被回收以供操作系统使用。

     

    • After doing substantial insert, update, or delete operations on columns that are part of a FULLTEXT index in an InnoDB table. Set the configuration option innodb_optimize_fulltext_only=1 first. To keep the index maintenance period to a reasonable time, set the innodb_ft_num_word_optimize option to specify how many words to update in the search index, and run a sequence of OPTIMIZE TABLE statements until the search index is fully updated.

    在对一个innodb表的全文索引列做大量的增删改操作后。首先设置配置选项innodb_optimize_fulltext_only=1。要使索引维护周期保持在合理的时间内,请设置innodb_ft_num_word_optimize选项以指定搜索索引中要更新的单词数,并运行一系列optimize TABLE语句,直到搜索索引完全更新。

     

    • After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

    删除MyISAM或存档表的大部分,或对具有可变长度行(具有VARCHAR、VARBINARY、BLOB或TEXT列的表)的MyISAM或存档表进行多次更改之后。删除的行保存在链表中,后续的插入操作重用旧的行位置。可以使用“OPTIMIZE TABLE”回收未使用的空间并对数据文件进行碎片整理。在对表进行了大量更改之后,此语句还可以提高使用该表的语句的性能,有时会显著提高。

     

    This statement requires SELECT and INSERT privileges for the table.

    此语句需要表的SELECT和INSERT权限。(指optimize table语句)

     

    OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDB tables. It does not work for fixed-width columns of in-memory tables, nor does it work for Disk Data tables. The performance of OPTIMIZE on NDB Cluster tables can be tuned using --ndb-optimization-delay, which controls the length of time to wait between processing batches of rows by OPTIMIZE TABLE. For more information, see Previous NDB Cluster Issues Resolved in NDB Cluster 7.3.

    optimize table被用于innodb、myisam和archive三种存储引擎的表。同样也支持基于动态列的内存NDB表。它不适用于内存表的固定宽度列,也不适用于磁盘数据表。可以使用--ndb-optimization-delay来优化NDB集群表上的OPTIMIZE性能,它通过OPTIMIZE TABLE控制处理批处理行之间的等待时间长度。For more information, see Previous NDB Cluster Issues Resolved in NDB Cluster 7.3.

     

    For NDB Cluster tables, OPTIMIZE TABLE can be interrupted by (for example) killing the SQL thread performing the OPTIMIZE operation.

    对于NDB Cluster表,可以通过终止SQL线程来中断来执行优化操作。

     

    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 OPTIMIZE TABLE 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不适用于使用任何其他存储引擎创建的表,并返回一个结果,表明缺少支持。通过使用--skip-new选项启动mysqld,可以使优化表对其他存储引擎起作用。在这种情况下,OPTIMIZE TABLE只是映射到ALTER TABLE。

     

    This statement does not work with views.

    此语句不适用于视图。

     

    OPTIMIZE TABLE is supported for partitioned tables. For information about using this statement with partitioned tables and table partitions, see Section 22.3.4, “Maintenance of Partitions”.

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

     

    By default, the server writes OPTIMIZE TABLE statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

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

     

    OPTIMIZE TABLE Output

     

    OPTIMIZE TABLE returns a result set with the columns shown in the following table.

    OPTIMIZE TABLE返回一个结果集,其列如下表所示。

    OPTIMIZE TABLE table catches and throws any errors that occur while copying table statistics from the old file to the newly created file. For example. if the user ID of the owner of the .frm, .MYD, or .MYI file is different from the user ID of the mysqld process, OPTIMIZE TABLE generates a "cannot change ownership of the file" error unless mysqld is started by the root user.

    OPTIMIZE TABLE TABLE捕获并抛出将表统计信息从旧文件复制到新创建的文件时发生的任何错误。例如。如果.frm、.MYD的所有者的用户ID,或.MYI文件与mysqld进程的用户ID不同,OPTIMIZE table 生成一个“cannot change ownership of the file“错误,除非mysqld由根用户启动。

     

    InnoDB Details

    For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE, which rebuilds the table to update index statistics and free unused space in the clustered index. This is displayed in the output of OPTIMIZE TABLE when you run it on an InnoDB table, as shown here:

    对于InnoDB表,OPTIMIZE TABLE映射到ALTER TABLE。。。FORCE,它重建表以更新索引统计信息并释放聚集索引中未使用的空间。这将显示在InnoDB表上运行OPTIMIZE表时的输出,如下所示:

     

    OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

    OPTIMIZE TABLE对常规和分区的InnoDB表使用在线DDL,这减少了并发DML操作的停机时间。由OPTIMIZE table触发的表重建,由ALTER TABLE ... FORCE 在后台执行。独占表锁仅在操作的准备阶段和提交阶段短暂使用。在准备阶段,更新元数据并创建中间表。在提交阶段,表元数据更改被提交。

     

    OPTIMIZE TABLE rebuilds the table using the table copy method under the following conditions:

    优化表在以下条件下使用table copy method重建表:


    • When the old_alter_table system variable is enabled.

    •当启用old_alter_table系统变量时。


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

    •当启用--skip-new选项时启动mysql时。

     

    OPTIMIZE TABLE using online DDL is not supported for InnoDB tables that contain FULLTEXT indexes. The table copy method is used instead.

    对于包含全文索引的InnoDB表,不支持使用在线DDL优化表。改为使用表复制方法。

     

    InnoDB stores data using a page-allocation method and does not suffer from fragmentation in the same way that legacy storage engines (such as MyISAM) will. When considering whether or not to run optimize, consider the workload of transactions that your server will process:

    InnoDB使用page-allocation方法存储数据,不会像传统存储引擎(如MyISAM)那样出现碎片。在考虑是否运行优化时,请考虑服务器将处理的事务的工作负载:

    • Some level of fragmentation is expected. InnoDB only fills pages 93% full, to leave room for updates without having to split pages.

    •预计会出现一定程度的碎片。InnoDB只填充93%的页面,为更新留出空间,而不必拆分页。


    • Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.

    •删除操作可能会留下gaps,使页面的填充量低于预期,这可能会使优化表变得有价值。


    • Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is available. See Section 14.9.1.5, “How Compression Works for InnoDB Tables” and Section 14.11, “InnoDB Row Formats”.

    •更新行通常会重写同一页中的数据,具体取决于数据类型和行格式,前提是有足够的空间可用。见第14.9.1.5节,“压缩工作原理”“InnoDB表”和第14.11节“InnoDB行格式”。

    • High-concurrency workloads might leave gaps in indexes over time, as InnoDB retains multiple versions of the same data due through its MVCC mechanism. See Section 14.3, “InnoDB Multi-Versioning”.

    •随着时间的推移,高并发性工作负载可能会在索引中留下gaps,因为InnoDB通过其MVCC机制保留同一数据的多个版本。见第14.3节,“InnoDB Multi-版本控制”。

     

     

    MyISAM Details

    For MyISAM tables, OPTIMIZE TABLE works as follows:
    1. If the table has deleted or split rows, repair the table.
    2. If the index pages are not sorted, sort them.
    3. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

    如果表的统计信息不是最新的(并且无法通过排序索引来完成修复),请更新它们。

     

    Other Considerations

    OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables. Otherwise, MySQL
    locks the table during the time OPTIMIZE TABLE is running.

    定期在线执行optimize table和分区InnoDB表。因为,MySQL在OPTIMIZE table运行期间锁定表。


    OPTIMIZE TABLE does not sort R-tree indexes, such as spatial indexes on POINT columns. (Bug
    #23578)

    optimize table不会排序R-tree索引,例如空间在point列上。(Bug#23578)

     

     

     

     

     

     

     

     

     

     

     

     

    展开全文
  • mysql optimize table

    2018-09-02 13:54:00
    语法结构: OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ... optimize table 会重组表数据和索引的物理存储,减少对存储空间...以下场景使用 optimize table,和表的类型有关:1...

    语法结构:

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

    optimize table 会重组表数据和索引的物理存储,减少对存储空间使用和提升访问表时io效率。optimize table后,表的变化和存储引擎也有关。

    以下场景使用 optimize table,和表的类型有关:
    1.innodb存储引擎+独立表空间,optimize table会重组表和索引数据,磁盘空间被回收。
    2.插入、更新、删除的列是innodb表中fulltext索引的列,要先设置innodb_optimize_fulltext_only=1。
    为了将对索引的维护设置在一个合理的时间内,可以设置innodb_ft_num_word_optimize指定一次处理多少个words,执行多次optimize table操作直到索引更新结束。
    3.删除myisam、archive存储引擎表中大量数据,或者对myisam、archive表变长的行做了大量修改。删除的行被为维护在一个链表中,之后的insert可以重用这些位置。
    可以使用optimize TABLE来回收未使用的空间并整理数据文件。在对表进行了大量更改之后,该语句还可以提高使用该表的语句的性能,有时效果显著。

    optimize table需要对表具有select、insert权限。


    optimize table支持innodb、myisam、archive存储引擎的表。


    默认是不支持其他存储引擎的表,会返回不支持的提示信息。要想支持其他存储引擎,可以使用mysqld --skip-new选项开启对其他存储引擎的支持。使用--skip-new选项后,optimize table仅仅被映射成alter table操作。


    不支持对view进行optimize。


    optimize table支持分区表。

    缺省,optimize table被记入二进制日志,并复制到slave节点。可以使用no_write_binlog或local取消对二进制日志的写入。

     

    对innodb表执行optimize table

    对于innodb表,optimize table被映射成alter table ... force,重建表和更新索引统计信息并释放空间。
    对innodb执行optimize table操作,输出类似下面的结果:

    > 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对innodb常规表、分区表执行在线ddl,减少了并发dml操作的宕机时间。

    optimize table 触发表重建,内部执行alter table ... force操作。
    在prepare阶段和commit阶段会对表加上排他锁。在prepare阶段会更新元数据并创建一个内部中间表,在commit阶段提交对元数据的修改。

     

    optimize table在以下场景使用数据拷贝的方式进行重建表:
    1.开启了old_alter_table系统变量
    2.使用了mysqld --skip-new选项启动数据库

    如果innodb表包含fulltext索引,是不支持optimize table使用在线ddl的。只能使用数据拷贝的方式。 

    InnoDB使用页面分配方法存储数据,不像传统存储引擎(比如MyISAM)那样存在碎片问题。执行optimize table之前要考虑事务的负载大小:
    ·有些级别的碎片是系统预期的。innodb只会插入数据到页面的93%,剩下的空间是给update预留的,以免出现页的分裂
    ·删除操作可能会留下很多空隙,执行optimize table是有意义的
    ·更新通常是在相同的页中重写数据,空间是否充足取决于行格式和列的数据类型
    ·由于innodb的mvcc机制,高并发环境可能会导致索引产生空隙

     

    对myisam表执行optimize table
    对于myisam表,optimize table操作执行以下工作:
    1.如果表含有删除的列、分列的列,optimize table会修复表
    2.如果索引页没有排序,optimize table会将索引页进行排序
    3.如果表的统计信息不是最新的,optimize table会更新索引信息

     

    补充:

    对innodb表执行optimize table操作的时候,会报"Table does not support optimize, doing recreate + analyze instead"提示。有人直接说optimize table不支持innodb表。其实并不能这么理解。

    下面是测试结果:

    Server version: 5.7.19 MySQL Community Server (GPL)
    
    CREATE TABLE m_test(id int unsigned, body text) engine=myisam charset=utf8;
    CREATE TABLE i_test(id int unsigned, body text) engine=innodb charset=utf8;
    
    INSERT INTO m_test VALUES(1, 'AAAAA');
    INSERT INTO m_test VALUES(2, 'BBBBB');
    INSERT INTO m_test VALUES(3, 'CCCCC');
    INSERT INTO m_test VALUES(4, 'DDDDD');
    INSERT INTO m_test VALUES(5, 'EEEEE');
    INSERT INTO i_test VALUES(1, 'AAAAA');
    INSERT INTO i_test VALUES(2, 'BBBBB');
    INSERT INTO i_test VALUES(3, 'CCCCC');
    INSERT INTO i_test VALUES(4, 'DDDDD');
    INSERT INTO i_test VALUES(5, 'EEEEE');
    
    #多次插入相同的数据:
    INSERT INTO m_test SELECT id, body FROM m_test;
    INSERT INTO i_test SELECT id, body FROM i_test;
    ...
    

    查看表和文件的大小:

    ysql> select count(*) from m_test;
    +----------+
    | count(*) |
    +----------+
    | 83886080 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> select count(*) from i_test;
    +----------+
    | count(*) |
    +----------+
    | 83886080 |
    +----------+
    1 row in set (36.04 sec) #明显慢很多
    mysql> 
    # ll |grep test
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:03 i_test.frm
    -rw-r----- 1 mysql mysql 3267362816 Sep  2 10:30 i_test.ibd
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm
    -rw-r----- 1 mysql mysql 1677721600 Sep  2 10:26 m_test.MYD
    -rw-r----- 1 mysql mysql       1024 Sep  2 10:26 m_test.MYI
    

    删除一定数据后查看表和文件的大小:

    mysql> DELETE FROM i_test WHERE id < 3;
    mysql> DELETE FROM m_test WHERE id < 3;
    # ll |grep test
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:03 i_test.frm
    -rw-r----- 1 mysql mysql 3267362816 Sep  2 10:39 i_test.ibd
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm
    -rw-r----- 1 mysql mysql 1677721600 Sep  2 10:37 m_test.MYD
    -rw-r----- 1 mysql mysql       1024 Sep  2 10:37 m_test.MYI
    mysql> select count(*) from m_test;
    +----------+
    | count(*) |
    +----------+
    | 50331648 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> select count(*) from i_test;
    +----------+
    | count(*) |
    +----------+
    | 50331648 |
    +----------+
    1 row in set (52.91 sec)
    

    分别执行optimize table操作:

    mysql> optimize table m_test;
    +-------------+----------+----------+----------+
    | Table       | Op       | Msg_type | Msg_text |
    +-------------+----------+----------+----------+
    | test.m_test | optimize | status   | OK       |
    +-------------+----------+----------+----------+
    1 row in set (8.49 sec)
    
    mysql> optimize table i_test;
    +-------------+----------+----------+-------------------------------------------------------------------+
    | Table       | Op       | Msg_type | Msg_text                                                          |
    +-------------+----------+----------+-------------------------------------------------------------------+
    | test.i_test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
    | test.i_test | optimize | status   | OK                                                                |
    +-------------+----------+----------+-------------------------------------------------------------------+
    2 rows in set (47.73 sec)
    
    mysql> 
    # ll |grep test
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:42 i_test.frm
    -rw-r----- 1 mysql mysql 2243952640 Sep  2 10:43 i_test.ibd
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm
    -rw-r----- 1 mysql mysql 1006632960 Sep  2 10:41 m_test.MYD
    -rw-r----- 1 mysql mysql       1024 Sep  2 10:41 m_test.MYI
    

    从上面的结果可以看到,无论是innodb、还是myisam表都被收缩了。

     

    使用mysqld --skip-new重启mysqlserver后,再次对innodb表执行optimize table操作:

    mysql> optimize table i_test;
    Query OK, 50331648 rows affected (3 min 10.82 sec)
    Records: 50331648  Duplicates: 0  Warnings: 0
    
    mysql> 
    # ll |grep test
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:47 i_test.frm
    -rw-r----- 1 mysql mysql 1962934272 Sep  2 10:50 i_test.ibd
    -rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm
    -rw-r----- 1 mysql mysql 1006632960 Sep  2 10:41 m_test.MYD
    -rw-r----- 1 mysql mysql       1024 Sep  2 10:41 m_test.MYI
    

    可以看到,innodb表再次发生了收缩。

     

    结论:
    "Table does not support optimize, doing recreate + analyze instead"并不是说innodb不支持optimize table。
    因为对于innodb表,optimize table被映射成alter table ... force,重建表和更新索引统计信息并释放空间。

     

    转载于:https://www.cnblogs.com/abclife/p/9573578.html

    展开全文
  • Which Locks Does OPTIMIZE TABLE Take? (文档 ID 1910515.1) 转到底部 APPLIES TO: MySQL Server - Version 4.0 and later Information in this document applies to any platform. ...
  • MySQL check table/optimize table/analyze table/REPAIR TABLE 关键词:optimize table,analyze table 转自:https://www.cnblogs.com/datastack/p/3826560.html 注意,这几个操作都会锁表,建议在业务空闲期...
  • OPTIMIZE TABLE MYSQL

    2018-11-17 14:08:39
    OPTIMIZE TABLE MYSQL   转载:https://segmentfault.com/a/1190000012568841 Mysql库中删除了大量的数据后,您可能会发现数据文件尺寸并没有减小。这是因为删除操作后在数据文件中留下碎片所致。OPTIMIZE TABLE...
  • optimize table tablename

    2016-05-02 23:25:00
    optimize 优化表OPTIMIZE 命令支持的引擎MyIsam, InnoDB, ARCHVE当对表有大量的增删改操作时,需要用optimize对表进行优化,可以减少空间与提高I/O性能,命令optimize table tablename; 1.假如有session表且存储引擎为...
  • OPTIMIZE TABLE `table_name` 优化表 REPAIR TABLE 用于修复被破坏的表。 OPTIMIZE TABLE 用于回收闲置的数据库空间, 当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用了OPTIMIZE TABLE命令后...
  • 查询mysql表是否被损坏命令,如下: ...OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用了OPTIMIZE TABLE命令后这些空间将被回收,并且对磁盘上的数据行
  • 本篇文章是对Mysql中OPTIMIZE TABLE的作用进行了详细的分析介绍,需要的朋友参考下
  • OPTIMIZE TABLE 小解

    2016-09-29 16:19:00
    我们知道mysql存储引擎里面的数据和索引数据都是物理存储的,所以说为了减少空间使用和访问表的时候能有更好的IO表现,所以说当表执行OPTIMIZE TABLE的时候,是会发生切实的变化的. 一般以下集中情况下,我们会...
  • 是因为MySQL带条件的话,将数据标识位删除,并没有整理数据文件,当插入新数据后,会再次使用这些被置为删除标识的记录空间,可以使用OPTIMIZE TABLE来回收未使用的空间,并整理数据文件的碎片。sql语句为OPTIMIZE ...
  • 当频繁的对(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,建议使用optimize table功能对这类表进行碎片整理,避免因为“空洞”导致性能问题。 OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用 注意,在...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 36,431
精华内容 14,572
关键字:

optimizetable影响