MySQL optimization three (two simple and practical optimization methods)

I. Periodic checklists and Analysis tables

The syntax for the parse table is as follows:

ANALYZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

This statement is used to analyze and store the keyword distribution of the table, and the results of the analysis will enable the system to get accurate statistics so that SQL can generate the correct execution plan. If the user feels that the actual execution plan is not the expected execution plan, executing the analysis table may solve the problem. During parsing, the table is locked with a read lock. This is useful for MyISAM, BDB, and InnoDB tables. For the MyISAM table, this statement is equivalent to the use of myisamchk-a, and the following example makes a table analysis of table sales:

mysql> Analyze table sales\g;

1. Row ***************************

Table:test1.sales

Op:analyze

Msg_type:status

Msg_text:ok

1 row in Set (0.01 sec)

The syntax for the checklist is as follows:

CHECK TABLE tbl_name [, Tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

The purpose of the checklist is to check whether one or more tables have errors. CHECK table is useful for MyISAM and InnoDB tables. For MyISAM tables, the keyword statistics are updated, for example:

mysql> Check table sales\g;

1. Row ***************************

Table:test1.sales

Op:check

Msg_type:status

Msg_text:ok

1 row in Set (0.11 sec)

Second, the regular optimization of the table

The syntax for tuning tables is as follows:

If you have deleted a large part of the table, or if you have made many changes to a table with variable-length rows (a table with a VARCHAR, BLOB, or TEXT column), you should use the OPTIMIZE Table command for table optimization. This command merges the space fragments in the table and eliminates the wasted space caused by the deletion or update, but the OPTIMIZE Table command only works on MyISAM, BDB, and InnoDB tables.

The following example shows the process of optimizing the table sales:

mysql> optimize table sales\g;

1. Row ***************************

Table:test1.sales

Op:optimize

Msg_type:status

Msg_text:ok

2 rows in Set (0.40 sec)

Note: The table will be locked during ANALYZE, CHECK, and OPTIMIZE execution, so be sure to take the relevant action when the database is not busy.

MySQL optimization three (two simple and practical optimization methods)