Detailed methodology for mysql table optimization analysis inspection and repair

This article describes the mysql table optimization, analysis, inspection and repair methods. To share for your reference, the details are as follows:

The general rule for managing a database is to do preventive maintenance and fix things that go wrong.

Performing inspections and repairs typically has four main tasks:

1. Optimize the table

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

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

4. Repair the table (repair the broken MyISAM table)

1. Optimize the table

There are many ways to optimize a table: OPTIMIZE TABLE statements, mysqlcheck tools (the server is running), or myisamchk (the server is not running or there is no interaction in the table)

Why optimize? With the use of MySQL, tables containing the BLOB and VARCHAR bytes will become more cumbersome because the fields are of different lengths and the records will take up different sizes of space when inserted, updated, or deleted, leaving the records fragged and free space. Like a fragmented disk, which degrades performance and requires defragmentation, so optimize.

1. Optimize the table with OPTIMIZE statement

# mysql>OPTIMIZE TABLE  The name of the table 

This optimizes the table name.

2. Optimize the table with mysqlcheck

mysqlcheck can be optimized to perform a wide range of inspection and repair tasks.

# mysqlcheck -o  The database name   The name of the table  -uroot -p111111  ( 1 Table) 
# mysqlcheck -o  The database name   The name of the table 1  The name of the table 2 -uroot -p111111  (Multiple tables) 
# mysqlcheck -o  The database name  -uroot -p111111  (For the entire database) 

3. Optimize the table with myisamchk

# myisamchk --quick --check-only-changed --sort-index --analyze  The name of the table 
# myisamchk -r  The name of the table   (parameter -r Represents fixing the table while eliminating wasted space) 
# myisamchk -r /usr/local/mysql/data/testblog/article  (Specifies the path of the table) 

If the server is down or not interoperating with the server, you can use the myisamchk command line tool (if the server is running, use mysqladmin flush-ES53en to refresh the table before running this statement. Make sure that the server is not interoperating with the table, otherwise it will fail. myisamchk is the oldest method. You must either run myisamchk in the correct location or specify the path where the table resides.

Note: Tables are locked during optimization, so do not optimize while busy. Again, enough space is required to proceed with OPTIMIZE TABLE. Without disk space, MySQL will not be optimized and the tables will not be usable.

Optimization is an important part of the routine management of databases containing MyISAM tables and should be done periodically.

2. Analyze the table

Regular analysis of tables can improve performance and should be part of regular maintenance efforts. Because tables can be analyzed by updating their index information, database performance can be improved.

There are three ways to analyze a table:

1. When connecting to MySQL, use the statement ANALYZE TABLE

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 be running or interoperating with the tables it is operating on)

# ANALYZE TABLE  The name of the table ;
# mysqlcheck -a  The database name   The name of the table  -uroot -p111111
# mysqlcheck -a  The database name   The name of the table 1  The name of the table 2 -uroot -p111111

If you try to analyze a table that does not support an analysis operation (such as InnoDB), the operation will not take place

# myisamchk -a /usr/local/mysql/data/ The database / The name of the table 

For more information about MySQL, please refer to MySQL Transaction Operation Skills Summary, MySQL Stored Procedure Skills Collection, MySQL Database Locking Skills Summary and MySQL Common Functions Summary.

Hope this article has been helpful to you MySQL database.