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. This statement does not work with views.
- Usage: 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.
- OPTIMIZE TABLE is also supported for partitioned tables
- OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables.
- 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:
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 |
+----------+----------+----------+-------------------------------------------------------------------+
- Prior to Mysql 5.7.4, OPTIMIZE TABLE does not use online DDL (ALGORITHM=INPLACE). Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, i.e. the table is locked. Also, secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key.
- As of 5.7.4, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for both regular and partitioned InnoDB tables. The table rebuild, triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE, is now performed using online DDL (ALGORITHM=INPLACE) and only locks the table for a brief interval, which reduces downtime for concurrent DML operations.
Analyze table
------------------
- ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables.
- It gather current stats of table and update information_schema accordingly.
mysql> analyze table foo;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| test.foo | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0.16 sec)
Note: For both operations, INSERT & SELECT privilege required.
References:
http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
http://dev.mysql.com/doc/refman/5.7/en/analyze-table.html
-------------------
- 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. This statement does not work with views.
- Usage: 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.
- OPTIMIZE TABLE is also supported for partitioned tables
- OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables.
- 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:
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 |
+----------+----------+----------+-------------------------------------------------------------------+
- Prior to Mysql 5.7.4, OPTIMIZE TABLE does not use online DDL (ALGORITHM=INPLACE). Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, i.e. the table is locked. Also, secondary indexes are not created as efficiently because keys are inserted in the order they appeared in the primary key.
- As of 5.7.4, OPTIMIZE TABLE uses online DDL (ALGORITHM=INPLACE) for both regular and partitioned InnoDB tables. The table rebuild, triggered by OPTIMIZE TABLE and performed under the cover by ALTER TABLE ... FORCE, is now performed using online DDL (ALGORITHM=INPLACE) and only locks the table for a brief interval, which reduces downtime for concurrent DML operations.
Analyze table
------------------
- ANALYZE TABLE analyzes and stores the key distribution for a table. During the analysis, the table is locked with a read lock for InnoDB and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables.
- It gather current stats of table and update information_schema accordingly.
mysql> analyze table foo;
+---------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| test.foo | analyze | status | OK |
+---------------------+---------+----------+----------+
1 row in set (0.16 sec)
Note: For both operations, INSERT & SELECT privilege required.
References:
http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
http://dev.mysql.com/doc/refman/5.7/en/analyze-table.html
No comments:
Post a Comment