Method for regular MySQL analysis and table Optimization _ MySQL

Summary of regular MySQL analysis and table optimization methods Periodic Analysis Table

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]

This statement is used to analyze and store the table's keyword distribution. During the analysis, a read lock is used to lock the table. This applies to MyISAM, BDB, and InnoDB tables. For MyISAM tables, this statement is equivalent to myisamchk-.

MySQL uses the distribution of stored keywords to determine the order in which tables are joined when you perform union on objects other than constants.

Mysql> analyze table;
+ -------- + --------- + ---------- + ----------------------------- +
| Table | Op | Msg_type | Msg_text |
+ -------- + --------- + ---------- + ----------------------------- +
| Test. a | analyze | status | Table is already up to date |
+ -------- + --------- + ---------- + ----------------------------- +
1 row in set (0.00 sec)

Regular checklist

Check table tbl_name [, tbl_name] [option]

Option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
Check whether one or more tables have errors. Check table is useful for MyISAM and InnoDB tables. For MyISAM tables, keyword statistics are updated.

Mysql> check table;
+ -------- + ------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ -------- + ------- + ---------- +
| Test. a | check | status | OK |
+ -------- + ------- + ---------- +
1 row in set (0.00 sec)
Check table can also CHECK whether the view has an error. for example, the referenced TABLE in the view definition does not exist.
We create a view for table a above.

Mysql> create view a_view as select * from;
Query OK, 0 rows affected (0.02 sec)

CHECK the view and CHECK whether the view is correct.

Mysql> check table a_view;
+ ------------- + ------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ ------------- + ------- + ---------- +
| Test. a_view | check | status | OK |
+ ------------- + ------- + ---------- +
1 row in set (0.00 sec)
Delete the table on which the view depends

Mysql> drop table;
Query OK, 0 rows affected (0.01 sec)
CHECK the view and find an error.

Mysql> check table a_view/G;
* *************************** 1. row ***************************
Table: test. a_view
Op: check
Msg_type: Error
Msg_text: Table 'test. a' doesn' t exist
* *************************** 2. row ***************************
Table: test. a_view
Op: check
Msg_type: Error
Msg_text: View 'test. a_view' references invalid table (s) or column (s) or function (s) or definer/invoker of view lack rights to use them
* *************************** 3. row ***************************
Table: test. a_view
Op: check
Msg_type: error
Msg_text: UPT
3 rows in set (0.00 sec)

ERROR:
No query specified
Regular optimization table

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
If you have deleted a majority of the tables, or if you have made many changes to the tables that contain variable-length rows (tables that contain VARCHAR, BLOB, or TEXT columns, optimize table should be used. The deleted records are kept in the link list. Subsequent INSERT operations will reuse the old record location. You can use optimize table to reuse unused space and organize data file fragments.
In most settings, you do not need to run optimize table. Even if you have made a large number of updates to a variable-length row, you do not need to run it frequently, once a week or once a month, and only run on a specific table.
Optimize table only applies to MyISAM, BDB, and InnoDB tables.
For MyISAM tables, OPTIMIZE tables are operated as follows:
If the table has been deleted or broken down, the table is repaired.
If the index page is not classified, the page is classified.
If the statistical data of the table is not updated (and cannot be repaired by classifying the index), update the table.

Mysql> OPTIMIZE table;
+ -------- + ---------- + ----------------------------- +
| Table | Op | Msg_type | Msg_text |
+ -------- + ---------- + ----------------------------- +
| Test. a | optimize | status | Table is already up to date |
+ -------- + ---------- + ----------------------------- +
1 row in set (0.00 sec)

****
Some of the above paragraphs are taken directly from the MySQL Chinese manual. for details, you can directly view the MySQL help manual. here we just briefly point out several regular optimization methods. Note that whether it is ANALYZE, CHECK or OPTIMIZE will lock the table during execution, so please note that these operations should be executed when the database is not busy

****
Reference
MySQL 5.1 Reference Manual

By Chen Yu