MYSQL Analysis tables, checklists, and tuning tables

1. Optimize the table (the main function of the optimization table is to eliminate the space waste caused by the deletion or update)

2. Analyze the table (analyze the distribution of keywords, analyze and store the distribution of keys in MyISAM and BDB tables)

3. Check the table (check the table for errors, and update the statistics for the MyISAM key)

4. Repairing the table (repairing the corrupted MyISAM table)

1. Analysis Table

MySQL uses the Analyze table statement to parse the table, which has the following basic syntax:

ANALYZE table name 1 [, table Name 2 ...];
The database system adds a read-only lock to the table while using analyze table to parse the tables. During parsing, only records in the table can be read, and records cannot be updated and inserted. The ANALYZE table statement can parse tables of type InnoDB and MyISAM.

Under example 18-8, the score table is parsed using the Analyze table statement, and the results are as follows:

mysql> ANALYZE TABLE score;
+-------------+-----------+--------------+---------------+
| Table | Op | Msg_type | Msg_text |
+-------------+-----------+--------------+---------------+
| Test.score | Analyze | Status | OK |
+-------------+-----------+--------------+---------------+
1 row in Set (0.05 sec)
The above results show 4 columns of information, which are described in detail below:

Table: Represents the name of the tables;

Op: Represents the action performed. Analyze represents the parse operation. A check indicates that checks are found. Optimize means to optimize operation;

Msg_type: Represents the type of information, and its display is usually one of the four states, warnings, errors, and information;

Msg_text: Displays the information.

The 4 column information also appears after the checklist and the refinement table.

Periodic analysis of a table can improve performance and should be part of regular maintenance work. Database performance is improved because the table is parsed by updating the index information of the table.

There are three ways to analyze a table:

1. When connecting to MySQL, use the Analyze table statement

2. Use the Mysqlcheck command-line tool (the server needs to run and only works on the MyISAM table)

3. Use the MYISAMCHK command-line tool (the server should not run, or do not interoperate with the table being manipulated)

# ANALYZE table name;

# MYSQLCHECK-A database Name Table name-uroot-p111111

# MYSQLCHECK-A database Name Table name 1 table name 2-uroot-p111111

If you attempt to parse a table that does not support parsing operations (such as InnoDB), the operation cannot be

# myisamchk-a/usr/local/mysql/data/Database/table name

2. Check table

The Check table statement is used in MySQL to examine the tables. The Check table statement can check for InnoDB and MyISAM types of tables for errors. Also, the statement can check whether the view has errors. The basic syntax for this statement is as follows:

CHECK table name 1 [, table Name 2 ...] [option];
The option parameter has 5 parameters, namely quick, FAST, CHANGED, Medium, and extended. The execution efficiency of these 5 parameters is reduced in turn. Option options are valid only for tables of type MyISAM and are not valid for tables of type InnoDB. The CHECK Table statement also adds a read-only lock to the table during execution.

3. Optimizing tables

MySQL uses the Optimize table statement to optimize the table. This statement is valid for tables of type InnoDB and MyISAM. However, the Optilmize table statement can only optimize fields of the varchar, blob, or text type in the table. The basic syntax for the Optilmize table statement is as follows:

OPTIMIZE table name 1 [, table Name 2 ...];
By optimize table statements, you can eliminate disk fragmentation caused by deletions and updates, thereby reducing wasted space. The OPTIMIZE Table statement also adds a read-only lock to the tables during execution.

Note: If a table uses a data type such as text or blob, then updates, deletions, and so on, can result in wasted disk space. Because the previously allocated disk space is not automatically reclaimed after the update and delete operations. These disk fragments can be defragmented using the Optimize table statement for later reuse.

There are many ways to optimize a table: The OPTIMIZE table statement, the Mysqlcheck tool (the server is running), or MYISAMCHK (the server is not running or there is no interaction in the table)

Why optimization? With MySQL, tables that include blobs and varchar bytes become cumbersome, because these fields are of different lengths, and when you insert, update, or delete records, the records become fragmented and leave free space. Like a fragmented disk, it degrades performance and needs to be collated, so it is optimized.

1. Optimize the table with the Optimize statement

# mysql>optimize Table Name

This optimizes the table name.

2. Optimize the table with Mysqlcheck

The Mysqlcheck can be optimized and can perform a number of inspection and repair tasks.

# Mysqlcheck-o database Name Table name-uroot-p111111 (one table)

# Mysqlcheck-o database Name Table name 1 table name 2-uroot-p111111 (more than one table)

# Mysqlcheck-o Database name-uroot-p111111 (for the entire database)

3. Optimize the table with Myisamchk

# myisamchk--quick--check-only-changed--sort-index--analyze table name

# myisamchk-r Table Name (parameter-r means repairing the table and also deleting wasted space)

# myisamchk-r/usr/local/mysql/data/testblog/article (Specifies the path where the table is located)

You can use the MYISAMCHK command-line tool when the server is down or is not interoperable with the server (if the server is running, use mysqladmin flush-tables to refresh the table before running the statement.) You need to make sure that the server is not interoperating with the table or it will fail. Myisamchk is the oldest method. You must run Myisamchk in the correct location, or specify the path where the table is located.

Note: During the optimization process, the table is locked, so do not optimize the operation when busy. Again, there is plenty of room to optimize TABLE. If there is no disk space, MySQL will not be optimized and the table cannot be used.

Optimization is an important part of regular management transactions for databases that contain MyISAM tables and should be performed on a regular basis.