Mysql Data Sorting command AnalyzeTable (index repair) MySQL Optimizer (Optimization Component) needs to collect relevant information when optimizing SQL statements, this includes the cardinality of the table (which can be translated as "hash degree"), which indicates the number of different values in the column corresponding to an index-If cardinality is much less
Mysql Data Sorting command Analyze Table (index repair) MySQL Optimizer (Optimization Component) needs to collect relevant information when optimizing SQL statements, this includes the cardinality of the table (which can be translated as "hash degree"), which indicates the number of different values in the column corresponding to an index-If cardinality is much less
Mysql Data Sorting command
Analyze Table (index repair)
When optimizing SQL statements, MySQL Optimizer must first collect related information, including the cardinality of the table (which can be translated as "hash degree "), it indicates the number of different values in the column corresponding to an index. If cardinality is much less than the actual hash degree of the data, the index will basically become invalid.
We can use the show index statement to view the degree of hash of the INDEX:
Show index from players;
TABLE KEY_NAME COLUMN_NAME CARDINALITY
-------------------------------------
Players primary playerno 14
In this case, the number of PLAYERNO entries in the PLAYER table is much more than 14, and the index is basically invalid.
Here we use the Analyze Table statement to repair the index:
Analyze table players;
Show index from players;
The result is:
TABLE KEY_NAME COLUMN_NAME CARDINALITY
-------------------------------------
Players primary playerno 1000
At this time, the index has been repaired, greatly improving the query efficiency.
Note that if binlog is enabled, the Analyze Table result will also be written to binlog. we can add the keyword "local" between analyze and table to cancel writing.
Checksum Table)
Data may change during transmission or be damaged due to other reasons. To ensure data consistency, we can calculate the checksum (check value ).
Tables using the MyISAM engine store checksum, which is called live checksum. When data changes, checksum changes accordingly.
When executing the Checksum Table, you can specify the option qiuck or extended at the end; quick indicates that the stored checksum value is returned, and extended recalculates the checksum. If no option is specified, extended is used by default.
Optimize Table (like organizing disk fragments)
Disks that frequently update data need to be fragmented. The same is true for databases. The Optimize Table statement is valid for both MyISAM and InnoDB tables.
If the Table is updated frequently, the Optimize Table statement should be run regularly to ensure efficiency.
Like Analyze Table, Optimize Table can also use local to cancel binlog writing.
Check Table (Check for repair errors)
Database errors may often occur, such as errors when data is written to the disk, or the index is not synchronously updated, or the database stops without shutting down MySQL.
Data errors may occur in these situations:
Incorrect key file for table: ''. Try to repair it.
In this case, we can use the Check Table statement to Check the Table and its corresponding indexes.
For example, we run
Check table players;
The result is
Table op MSG_TYPE MSG_TEXT
-----------------------------------
TENNIS. PLAYERS check status OK
MySQL saves the time of the last table check. This information is stored every time a check table is run:
Run
SELECT TABLE_NAME, CHECK_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'players'
AND TABLE_SCHEMA = 'tennis ';/* TENNIS is the database name */
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 created in earlier MySQL versions are compatible with the current version.
QUICK: the fastest option. When you check the data in each column, the link is not checked for correctness. If there is no problem, you can use this option.
FAST: Check whether the system is shut down normally. if the system does not encounter any serious problems after power loss, you can use this option.
CHANGED: only the data updated after the last check time is checked.
MEDIUM: the default option, which checks whether the link between the index file and the data file is correct.
EXTENDED: the slowest option, which performs a full check.
Repair Table (index Repair)
Used to repair tables. It is only valid for tables of the MyISAM and ARCHIVE types.
This statement can also specify options:
QUICK: the fastest option. Only the index tree is repaired.
EXTENDED: the slowest option. You need to rebuild the index row by row.
USE_FRM: This option is used only when the MYI file is lost to completely reconstruct the entire index.
Like Analyze Table, Repair Table can also use local to cancel writing to binlog.
