MySQL database optimization, analysis, inspection and repair

1, to optimize the table

2, Analysis of the table (analysis and storage of MyISAM and BDB table in the distribution of keys)

3, check the table (check the table error, and update the key for the MyISAM statistics)

4, repair the table (repair the damaged MyISAM table)

First, optimize the table

There are many ways to implement an optimization table: The Optimize table statement, the MySQL tutorial Check tool (the server is running), or MYISAMCHK (the server is not running or there is no interaction in the table)

Why optimization? As MySQL is used, tables that include blobs and varchar bytes will become cumbersome because these fields are different in length, so that when you insert, update, or delete records, they will occupy different sizes of space, and the records will become fragmented and leave free space. Like a disk with fragmentation, it degrades performance and needs to be sorted out and therefore optimized.

1, using the Optimize statement to optimize the table

# mysql>optimize Table Name

This optimizes the table name.

2, using Mysqlcheck to optimize the table

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

# Mysqlcheck-o Database Tutorial Name Table name-uroot-p111111 (a table)

# Mysqlcheck-o database Name Table name 1 Table name 2-uroot-p111111 (multiple tables)

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

3, using MYISAMCHK to optimize the table

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

# myisamchk-r Table Name (parameter-r indicates that the table is repaired and the wasted space is deleted)

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

You can use the Myisamchk command-line tool when the server is down or not interoperating with the server (if the server is running, then use mysqladmin flush-tables to refresh the table before running this statement) ; Make sure that the server does not interoperate with the table, or a failure occurs. Myisamchk is the oldest method. You must run Myisamchk on the correct location, or specify the path where the table resides.

Note: During the optimization process, the table is locked, so do not optimize at busy time. Similarly, there is a need for sufficient space to perform 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 the regular management of a database containing MyISAM tables, and should be done on a regular basis.

Second, analysis of the table

Periodic analysis of a table can improve performance and should be part of routine maintenance efforts. Because the table is analyzed by updating the index information of the table, the database performance can be improved.

There are three ways to analyze a table:

1, when connected to MySQL, use the Analyze table statement

2, the use of Mysqlcheck command-line tools (the server needs to run, and only for the MyISAM table function)

3. Use the Myisamchk command-line tool (the server should not run or interoperate with the tables 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 will not work.

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