Mysql table optimization, analysis, inspection and repair methods detailed _mysql

This article describes the MySQL table optimization, analysis, inspection and repair methods. Share to everyone for your reference, specific as follows:

This article describes the general management of the database as preventative maintenance and fixes to the problems that arise.

Inspection and repair typically have four primary tasks:

1. Optimize the table

2. Analysis of tables (analyze and store the distribution of keys in MyISAM and BDB tables)

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

4. To 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 Mysqlcheck tool (the server is running), or the 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 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 Name Table name-uroot-p111111 (a table)
# mysqlcheck-o database Name Table name 1 Table name 2-uroot-p111111 (multiple tables)
# mysqlcheck-o number According to the library name-uroot-p111111 (to 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 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 there will be a failure. 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 connecting to MySQL, use the Analyze table statement

2. Using 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 be running, or there is no interoperability 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 profiling (such as InnoDB), the operation cannot be performed

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

For more information about MySQL interested readers can view the site topics: "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL common function large summary"

I hope this article will help you with the MySQL database meter.