MySQL maintenance statement

Analyze table

When optimizing SQL statements, the optimizer of MySQL needs to collect related information, including the cardinality of the table.
Hash degree), which 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
, Then the result of analyze tableBINLOG is also written.
You can add keywords between analyze and table.Local
Cancel writing.

 

 

Therefore, cardinality has the following meanings:

1. The column value represents the number of unique values stored in this column (if this column is primary key, the value is the number of rows in the record)
2. The column value is only an estimate and is not accurate.
3. Column ValueNoAutomatically updated


You must use analyze table to update a table or mysqlcheck-AA to update the entire database.
4. The size of column values affects whether to use this index for join operations.
5. When creating an index, the cardinality value of MyISAM table is null, And the cardinality value of InnoDB table is roughly the number of rows.

6. MyISAM and InnoDB have different calculation methods for cardinality.

InnoDB

In what calculation method will be used? I will try it when I come home.

This articleArticleDetailed introduction:

Http://www.mysqlperformanceblog.com/2008/09/03/analyze-myisam-vs-InnoDB

/

 

Optimize table

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

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 ';

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

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.

 

 

An error occurred while indexing the table (Table engine: MyISAM


)

Myisamchk -- safe-Recover/usr/local/MySQL/data/ename_news/dede_arccache

Repair table customerquestion;

Error Infos: Table './ename_news/dede_arccache' is marked as crashed and shoshould be retried red
Myisamchk-r Data/ename_news/dede_arccache