OPTIMIZE TABLE linked list 表优化原理 链表数据结构 空间再利用
小结：
1、加快读写；
2、对于InnoDB表，在一定条件下，通过复制旧表重建；
3、实践中，
3.1、show processlist;查看线程，发现，认为堵塞读请求；
3.2、数据长度空间不变，索引长度空间减小；历史的写操作是：url访问统计表，当天多次更新当天聚合结果数据的逻辑是“当天数据删除，再插入”（url-pv）；
MySQL :: MySQL 8.0 Reference Manual :: 13.7.3.4 OPTIMIZE TABLE Syntax https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html
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.
Use OPTIMIZE TABLE in these cases, depending on the type of table:
-
After doing substantial insert, update, or delete operations on an
InnoDBtable that has its own .ibd file because it was created with theinnodb_file_per_tableoption enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system. -
After doing substantial insert, update, or delete operations on columns that are part of a
FULLTEXTindex in anInnoDBtable. Set the configuration optioninnodb_optimize_fulltext_only=1first. To keep the index maintenance period to a reasonable time, set theinnodb_ft_num_word_optimizeoption to specify how many words to update in the search index, and run a sequence ofOPTIMIZE TABLEstatements until the search index is fully updated. -
After deleting a large part of a
MyISAMorARCHIVEtable, or making many changes to aMyISAMorARCHIVEtable with variable-length rows (tables that haveVARCHAR,VARBINARY,BLOB, orTEXTcolumns). Deleted rows are maintained in a linked list and subsequentINSERToperations reuse old row positions. You can useOPTIMIZE TABLEto 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.
This statement requires SELECT and INSERT privileges for the table.
OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDBtables. 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.
For NDB Cluster tables, OPTIMIZE TABLE can be interrupted by (for example) killing the SQL thread performing the OPTIMIZE operation.
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 is supported for partitioned tables. For information about using this statement with partitioned tables and table partitions, see Section 23.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 returns a result set with the columns shown in the following table.
| Column | Value |
|---|---|
Table |
The table name |
Op |
Always optimize |
Msg_type |
status, error, info, note, or warning |
Msg_text |
An informational message |
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 .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.
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:
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 |
+--------