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

(I) Optimize

Optimize can recycle space, reduce fragments, and increase I/O
Currently, the following storage engines are supported: InnoDB, myasim, and archive.

If it is a replication environment, add no_write_to_binlog (or local, the meaning is exactly the same), for example:
Optimize local table table_name;

The following is a simple test:

[MySQL @ odd employees] $ LS-alh t. IBD-RW ---- 1 MySQL DBA 24 m 05-22 t. 24 mmysql> 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. 14 Mb after IBD optimize

(Ii) Check

Check table or view errors
Table engines supported: InnoDB and MyISAM

A test is simulated below:

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: checkmsg_type: errormsg_text: incorrect information in file :'. /employees/t. frm' *************************** 2. row *************************** table: employees. T op: checkmsg_type: errormsg_text: corrupt2 rows in SET (0.00 Sec) -- error reported

(3) analyze

Used to collect optimizer statistics, and tuning-related,
This command is useful for tables of the MyISAM, bdb, and InnoDB Storage engines.
If you do not want to record BINLOG, you can add the keyword local or another

mysql> analyze table t\G;*************************** 1. row ***************************   Table: employees.t      Op: analyzeMsg_type: ErrorMsg_text: Incorrect information in file: './employees/t.frm'*************************** 2. row ***************************   Table: employees.t      Op: analyzeMsg_type: errorMsg_text: Corrupt2 rows in set (0.00 sec)