MySQL optimize table

Syntax structure:

[no_write_to_binlog | LOCAL]    TABLE[, Tbl_name] ...

Optimize table reorganizes the physical storage of tables data and indexes, reducing the IO efficiency when using storage space and improving access to tables. Optimize table changes are also related to the storage engine.

The following scenarios use Optimize table, which is related to the type of table:
1.innodb Storage Engine + stand-alone tablespace, optimize table reorganizes tables and index data, and disk space is recycled.
2. Insert, update, and delete columns are the columns of the Fulltext index in the InnoDB table, set Innodb_optimize_fulltext_only=1 first.
To set the maintenance of the index in a reasonable amount of time, you can set Innodb_ft_num_word_optimize to specify how many words are processed at a time, and perform multiple optimize table operations until the end of the index update.
3. Delete large amounts of data from the MyISAM, archive storage engine tables, or make a number of changes to the rows MyISAM, archive tables grow. Deleted rows are maintained in a linked list, and subsequent inserts can be reused for those locations.
You can use optimize table to reclaim unused space and organize your data files. After making a large number of changes to the table, the statement can also improve the performance of the statements that use the table, sometimes with significant results.

Optimize table needs to have SELECT, insert permissions on the tables.

Optimize table supports the InnoDB, MyISAM, archive storage engine tables.

By default, tables that do not support other storage engines will return unsupported prompts. To support other storage engines, you can use the mysqld--skip-new option to turn on support for other storage engines. With the--skip-new option, the Optimize table is only mapped to the ALTER TABLE operation.

Optimize the view is not supported.

Optimize table supports partitioned tables.

By default, optimize table is logged into the binary log and copied to the slave node. You can use No_write_binlog or local to cancel writing to the binary log.

Perform optimize table on InnoDB tables

For the InnoDB table, optimize table is mapped to ALTER TABLE ... force, rebuilds the table and updates the index statistics and frees up space.
Perform a optimize table operation on InnoDB, and the output resembles the following result:

> 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 performs an online DDL on InnoDB regular tables, partitioned tables, reducing the downtime of concurrent DML operations.

Optimize table Touch release rebuild, internal execution ALTER TABLE ... force operation.
An exclusive lock is added to the table during the prepare and commit phases. In the prepare phase, the metadata is updated and an internal intermediate table is created to commit changes to the metadata during the commit phase.

Optimize table rebuilds tables in the following scenarios using data copies:
1. old_alter_table system variable is turned on
2. Start the database using the mysqld--skip-new option

Not all scenarios can use the online DDL. If the InnoDB table contains an fulltext index, the online DDL for optimize table is not supported. You can only use the data copy method.

ALTER TABLE uses the Data copy;alter table ... force is using an online DDL.

InnoDB uses page allocation methods to store data, unlike a traditional storage engine (such as MyISAM) that has fragmentation problems. Consider the load size of the transaction before executing optimize table:
• Some levels of fragmentation are expected by the system. InnoDB will only insert data into the page 93%, the rest of the space is reserved for update, in case the page splits
• Delete operation may leave a lot of gaps, it makes sense to execute optimize table
• Updates are typically rewritten on the same page, with sufficient space depending on the row format and column data type
• Due to the MVCC mechanism of the innodb, high concurrency environments can cause gaps in the index

Perform optimize table on MyISAM tables
For the MyISAM table, the Optimize table operation performs the following tasks:
1. If the table contains deleted columns and columns, optimize table will fix the
2. If the index page is not sorted, optimize table will sort the index pages
3. If the statistics for the table are not up-to-date, optimize table updates the index information

Add:

When performing optimize table operations on the InnoDB table, the "table does not support optimize, doing recreate + analyze instead" prompt is reported. Someone directly said optimize table does not support InnoDB tables. In fact, this is not the way to understand.

Here are the test results:

Server version:5.7.19 MySQL Community Server (GPL) CREATE TABLE m_test (id int unsigned, body text) Engine=myisam Charset=u Tf8; CREATE TABLE i_test (id int unsigned, body text) Engine=innodb Charset=utf8;insert into M_test VALUES (1, ' AAAAA '); To M_test values (2, ' bbbbb '); insert into m_test values (3, ' CCCCC '); insert into m_test values (4, ' ddddd '); INSERT INTO m_tes T VALUES (5, ' eeeee '); insert into i_test values (1, ' AAAAA '); Insert to I_test values (2, ' bbbbb '); insert into i_test values (3, ' CCCCC '); insert into i_test values (4, ' ddddd '); Insert to I_test values (5, ' eeeee '); #多次插入相同的数据: INSERT INTO M_test SEL ECT ID, body from M_test;insert to i_test SELECT ID, body from i_test; ....

To view the size of the table and file:

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 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  2 10:26 m_test. MYI

View the size of the table and file after you delete certain data:

Mysql> Delete from i_test where ID < 3;mysql> delete from M_test where ID < 3;# ll |grep test-rw-r-----1 mysq L 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 mysq L 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  2 10:37 m_test. Myimysql> 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)

Perform optimize table operations separately:

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 2 10:41 m_test. MYI

As you can see from the above results, both the InnoDB and the MyISAM tables are shrunk.

After restarting MySQLServer with Mysqld--skip-new, perform the Optimize table operation again on the InnoDB tables:

mysql> optimize table i_test; Query OK, 50331648 rows affected (3 min 10.82 sec) records:50331648  duplicates:0  warnings:0mysql> # 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  2 10:41 m_test. MYI

As you can see, the InnoDB table shrinks again.

Conclusion:
"Table does not supported optimize, doing recreate + analyze instead" is not to say InnoDB does not support optimize Table.
Because for the InnoDB table, optimize table is mapped to ALTER TABLE ... force, rebuilds the table and updates the index statistics and frees up space.

MySQL optimize table