MySQL table maintenance in the production environment: check, optimize, and analyze

Production Environment MySQL table maintenance: check, optimize and analyze (I) optimize can recycle space, reduce fragments, and improve I/O currently supports the following storage engines: if InnoDB, MyASIM, and ARCHIVE are in a Replication environment, they can add NO_WRITE_TO_BINLOG (or LOCAL, Which means exactly the same), for example, optimize local table table_name. The following is a simple test: [plain] [mysql @ odd employees] $ ls-alh t. ibd-rw ---- 1 mysql dba 24 M 05-22 t. 24 M mysql> optimize table t before ibd does not optimize; + ------------- + ---------- + upper + | Table | Op | Msg_type | Msg_text | + --------------- + ---------- + lower + | employees. t | optimize | note | Table does not support optimize, doing recreate + analyze instead | employees. t | optimize | status | OK | + --------------- + ---------- + --------------------------------------------------------------- + 2 rows in set (3.82 sec) -- no error is reported for InnoDB tables and the above content. MySQL will help you map to: alter table table_name engine = 'innodb '; -- MyISAM does not have this situation [mysql @ odd employees] $ ls-alh t. ibd-rw ---- 1 mysql dba 14 M 05-22 t. after ibd optimize, there are 14 M (2) check checklists or views left. The supported table engines are: InnoDB and MyISAM. A simple test is simulated: [plain] mysql> check table t; + ------------- + ------- + ---------- + | Table | Op | Msg_type | Msg_text | + --------------- + ------- + ---------- + | employees. t | check | status | OK | + ------------- + ------- + ---------- + 1 row in set (0.63 sec) -- this is the case if there is no error -- use vim to open t. frm arbitrarily edits two mysql> check table t \ G; * *************************** 1. row *************************** Table: employees. t Op: check Msg_type: Error Msg_text: Incorrect information in file :'. /employees/t. frm' *************************** 2. row *************************** Table: employees. t Op: check Msg_type: error Msg_text: sums upt 2 rows in set (0.00 sec) -- an error is reported. (3) analyze is used to collect statistics of optimizer and is related to tuning. This command is useful for tables of MyISAM, BDB, and InnoDB Storage engine. If you do not want to record binlog, you can also add the keyword local. or another [plain] mysql> analyze table t \ G; * *************************** 1. row *************************** Table: employees. t Op: analyze Msg_type: Error Msg_text: Incorrect information in file :'. /employees/t. frm' *************************** 2. row *************************** Table: employees. t Op: analyze Msg_type: error Msg_text: sums upt 2 rows in set (0.00 sec)