MySQL regular analysis check and optimization table

MySQL regular analysis check and optimization TABLE regular 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 a; + -------- + --------- + ---------- + --------------------------- + | Table | Op | Msg_type | Msg_text | + -------- + --------- + ---------- + tests + |. a | analyze | status | Table is already up to date | + -------- + --------- + ---------- + ----------------------------- + 1 row in set (0.00 sec) Regular checklist TABLE tbl_name [, tbl_name] [option] option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} checks 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 a; + -------- + ------- + ---------- + | 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 mysql> create view a_view as select * from a; Query OK, 0 rows affected (0.02 sec) for table a above and CHECK this view, no problem found 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 tabl E a; Query OK, 0 rows affected (0.01 sec). CHECK the view and find that the mysql> check table a_view \ G is incorrect; * *************************** 1. row *************************** Table: test. a_view Op: checkMsg_type: ErrorMsg_text: Table 'test. a 'doesn' t exist **************************** 2. row *************************** Table: test. a_view Op: checkMsg_type: ErrorMsg_text: View 'test. a_view 'references invalid table (s) or co Lumn (s) or function (s) or definer/invoker of view lack rights to use them ***************************** 3. row *************************** Table: test. a_view Op: checkMsg_type: errorMsg_text: Corrupt3 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 tables, or if you have made many changes to a table containing variable-length rows (tables containing VARCHAR, BLOB, or TEXT columns, use the optimize tab. LE. 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 table performs the following operations: If the TABLE has been deleted or broken down rows, 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 a; + -------- + ---------- + ----------------------------- + | Table | Op | Msg_type | Msg_text | + -------- + ---------- + lead + | test. a | optimize | status | Table is already up to date | + -------- + ---------- + ------------------------------- + 1 row in set (0.00 sec) * ** note that the table is locked during execution of ANALYZE, CHECK, and OPTIMIZE. Therefore, note that these operations must be performed when the database is not busy ****