MySQL table Optimization

Author: skate
Time: 2012/02/29

 

MySQL table Optimization

If the table data is greatly changed, or the table containing variable-length rows (tables with varchar, blob, or text columns) is greatly changed, the table should be optimized. After optimize table is used, the deleted records are kept in the Link List, and subsequent insert operations will re-use the old record bit, so that unused space can be reused, and sort the data file fragments.

 

Generally, you do not need to run optimize table. If tables with variable length are updated frequently and do not need to run frequently, you can perform the operation once a week or every month on a specific table.

 

Optimize table only applies to MyISAM, bdb, and InnoDB tables.

For MyISAM tables, optimize tables are operated as follows:
1. If the table has been deleted or broken down, the table will be repaired.
2. If the index page is not classified, the page is classified.
3. If the statistical data of the table is not updated (and cannot be repaired by classifying the index ),

 

Example:
Mysql> optimize table T1;

Note: During the optimize table operation, MySQL locks the table. Therefore, you must be cautious when using the service, and choose low-cost business maintenance.

 

If MySQL is a master-slave environment, remember to use no_write_to_binlog, so that the operation will not be passed to the slave server, as shown below:

Mysql> optimize no_write_to_binlog table T1;

In addition to optimize, myisamchk can optimize tables, but it only works for MyISAM tables. For example:

 

Myisamchk table Optimization
Space wasted to combine fragment records and delete or update records
Shell> myisamchk-r tbl_name

Sort all indexes to search for key values more quickly
# Myisamchk-s tablename

 

Sorts the specified index to search for key values more quickly.
Mysql> show index from tablename;
# Myisamch-R 1 tablename

For more details, see the official documentation.

 

 

 

 

------------- End --------------