MySql analysis command

The Optimizer (Optimization Component) of MySql, the Analyze Table MySQL Command, needs to collect related information when optimizing SQL statements, this includes 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 because the number of PLAYERNO in the player table is far greater than 14, and the index is basically invalid. The following Table uses 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, query efficiency is greatly improved. 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. When the Checksum Table data is transmitted, it may change 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. The disk where Optimize Table updates data frequently needs 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 databases often encounter errors, such as errors when data is written to the disk, or the indexes are not synchronously updated, or the database is stopped without shutting down MySQL. In these cases, the data may have an error: Incorrect key file for table: ''. Try to repair it. At this time, we can use the Check Table statement to Check the table and its corresponding indexes. For example, run check table players. The result is table op MSG_TYPE MSG_TEXT -------------- -------- TENNIS. PLAYERS check status OK MySQL will save the time of the last table check. This information is stored every time you run check table: Execute SELECT TABLE_NAME, CHECK_TIMEFROM INFORMATION_SCHEMA.TABLESWHERE 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 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. The Repair Table is used to Repair tables. It is only valid for tables of the MyISAM and ARCHIVE types. This statement can also specify the option: QUICK: the fastest option to fix only the index tree. 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.