Mysql table optimization, analysis, check, and repair methods, mysql details

Mysql table optimization, analysis, check, and repair methods, mysql details

This article describes how to optimize, analyze, check, and fix mysql tables. We will share this with you for your reference. The details are as follows:

Here, we will introduce the routine of database management, that is, preventive maintenance and fixing of problems.

There are usually four main tasks to check and repair:

1. Optimize the table

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

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

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

I. Table Optimization

There are many ways to OPTIMIZE a TABLE: optimize table statement, mysqlcheck tool (the server is running), or myisamchk (the server is not running or the TABLE is not interactive)

Why optimization? As MySQL is used, tables that contain BLOB and VARCHAR bytes will become redundant. Because these fields have different lengths, they occupy different sizes of space when inserting, updating, or deleting records, the record will become fragments and leave free space. Disks with fragments can reduce performance and need to be sorted out. Therefore, optimization is required.

1. OPTIMIZE the table using the OPTIMIZE statement

# Mysql> optimize table name

In this way, the table name is optimized.

2. Use mysqlcheck to optimize the table

In addition to optimization, mysqlcheck can execute a large number of check 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 (multiple tables) # mysqlcheck-o database name-uroot-p111111 (for the entire database)

3. Use 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, also deleted the wasted space) # myisamchk-r/usr/local/mysql/data/testblog/article (specify the path of the table)

The above operations must be performed when the server is closed or not interoperable with the server, you can use the myisamchk command line tool (if the server is running, use mysqladmin flush-tables to refresh the table before running this statement. Make sure that the server does not have table interoperability. Otherwise, a fault may occur ). Myisamchk is the oldest method. You must run myisamchk in the correct position or specify the path of the table.

Note: During the optimization process, the table is locked, so do not perform optimization operations when busy. Similarly, you must have enough space for optimize table. Without disk space, MySQL cannot be optimized and tables cannot be used.

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

Ii. Analysis of tables

Regular analysis of tables improves performance and should be part of routine maintenance. By updating the index information of a table, you can analyze the table to improve the database performance.

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 for the MyISAM table)

3. Use the myisamchk command line tool (the server should not run, or there is no interoperability with the operated tables)

# 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 try to analyze tables that do not support the analysis operation (such as InnoDB), the operation will not be performed.

# Myisamchk-a/usr/local/mysql/data/database/table name