MySQL Maintenance common commands _mysql

Analyze Table

MySQL's optimizer (optimization component) When optimizing SQL statements, you first need to gather some relevant information, including the cardinality of the table (which can be translated to "hash degree"), which indicates how many different values the columns of an index contain- If the cardinality is significantly less than the actual hash of the data, the index is largely invalidated.
We can use the show index statement to see how the index is hashed:

Show INDEX from players;

TABLE Key_name column_name Cardinality
------- -------- ----------- -----------
Players PRIMARY Playerno 14

Because the number of different playerno in the player table is much more than 14, the index basically fails.
Here we fix the index with the Analyze table statement:

ANALYZE TABLE players;
Show INDEX from players;
The result:
TABLE Key_name column_name Cardinality
------- -------- ----------- -----------
Players PRIMARY Playerno 1000

At this point the index has been repaired, query efficiency greatly improved.

Note that if Binlog is turned on, the results of analyze table are also written to Binlog, where we can add keyword local cancellation between analyze and table.

Checksum Table

The data may change during transmission, or it may be corrupted for other reasons, and we can calculate checksum (checksum value) to ensure the consistency of the data.
Tables using the MyISAM engine store checksum, called Live checksum, and checksum change as the data changes.
When you execute checksum table, you can specify either the option Qiuck or Extended;qiuck to return the stored checksum value, and extended will recalculate checksum, and extended is used by default if you do not specify an option.

Optimize Table

Disks that frequently update data need to be defragmented, as is the database, and the Optimize table statement is valid for tables of both MyISAM and InnoDB types.
If the table is frequently updated, you should run the Optimize Table statement periodically to ensure efficiency.
As with analyze table, Optimize table can also use local to cancel writing to Binlog.

Check Table

Databases can often encounter errors, such as errors when data is written to disk, or if the index is not synchronized, or the database is stopped when MySQL is not closed.
When these conditions are encountered, the data can be wrong:
Incorrect key file for table: '. Try to repair it.
At this point, we can use the Check Table statement to check the table and its corresponding index.
For example, we run
CHECK TABLE players;

The result is
TABLE OP Msg_type Msg_text
-------------- ----- -------- --------
Tennis. Players Check Status OK

MySQL saves the most recent check in the table, and stores this information each time you run check table:

Perform
SELECT table_name, Check_time
From INFORMATION_SCHEMA. TABLES
WHERE table_name = ' players '
and Table_schema = ' tennis ';

The result is

TABLE_NAME Check_time
---------- -------------------
Players 2006-08-21 16:44:25

Check table can also specify other options:
UPGRADE: Used to test whether tables established in earlier versions of MySQL are compatible with the current version.
Quick: The quickest option to check the data for each column does not check the link's correctness or not, and use this option if no problems are encountered.
FAST: Only check that the table is turned off properly, and use this option if you do not experience serious problems after the system has been powered down.
CHANGED: Only the data that was updated after the last check time is checked.
MEDIUM: The default option checks the correctness of links between the index file and the data file.
EXTENDED: The slowest option will be a full check.

Repair Table

Used to fix tables, only valid for tables of MyISAM and archive types.
This statement can also specify options:
Quick: The quickest option is to repair only the index tree.
EXTENDED: The slowest option, you need to rebuild the index line by row.
USE_FRM: Use this option only if the Myi file is lost and completely rebuild the entire index.

As with analyze table, Repair table can also use local to cancel writing to Binlog.