MYSQL中OPTIMIZE TABLE优化使用方法

Optimize Table是mysql中一个可以及回收更多的空间、减少“碎片” （defragment）命令,但有些朋友说可以提升mysql性能这个我具体不清楚,下面找了一些相关文章大家参考一下.

OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。

多数时间并不需要运行OPTIMIZE TABLE,只需在批量删除数据行之后,或定期,每周一次或每月一次,进行一次数据表优化操作即可,只对那些特定的表运行.

先了解一下OPTIMIZE TABLE对InnoDB 和 MyISAM相关知识

1. InnoDB 和 MyISAM

目前支持optimize命令的引擎有 MyISAM, InnoDB, and ARCHIVE，对于InnoDB，会将optimize命令映射为ALTER TABLE命令，该命令会重建数据表，更新索引统计信息、回收主键索引中空间。

2. InnoDB 和 MyISAM

如果你的MySQL是有备库的,如果你只希望在主库上执行的话,那么可以加上关键字NO_WRITE_TO_BINLOG,或者LOCAL,意思完全相同.

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE

这对于MM结构的MySQL数据库尤为重要,因为很多时候,你只是想在备库上执行,而不希望影响主库.

在mysql命令方式下使用如下代码:

#ls -lah users_0.ibd -rwxr-xr-x 1 mysql dba 736M May 6 09:50 users_0.ibd root@test 10:10:53>optimize table users_0

测试实例

1、先来看看多次删除插入操作后的表索引情况,代码如下:

1. mysql> SHOW INDEX FROM `tbl_name`; 
2. +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
3. | Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
4. +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
5. | tbl_name |          0 | PRIMARY    |            1 | StepID      | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
6. | tbl_name |          1 | FlowID     |            1 | FlowID      | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
7. | tbl_name |          1 | WagerCount |            1 | WagerCount  | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
8. | tbl_name |          1 | WagerID_3  |            1 | WagerID     | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
9. | tbl_name |          1 | WagerID_3  |            2 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
10. | tbl_name |          1 | WagerID_3  |            3 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         | 
11. | tbl_name |          1 | StepType_2 |            1 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
12. | tbl_name |          1 | StepType_2 |            2 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         | 
13. | tbl_name |          1 | WagerID_2  |            1 | WagerID     | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
14. | tbl_name |          1 | WagerID_2  |            2 | StepType    | A         |           1 |     NULL | NULL   |      | BTREE      |         | 
15. | tbl_name |          1 | WagerID_2  |            3 | ParamResult | A         |           1 |      255 | NULL   |      | BTREE      |         | 
16. +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
17. 11 rows in set (0.01 sec)  --Vevb.com 

2、优化表,代码如下:

1. mysql> optimize table tbl_name; 
2. +---------------+----------+----------+----------+ 
3. | Table         | Op       | Msg_type | Msg_text | 
4. +---------------+----------+----------+----------+ 
5. | test.tbl_name | optimize | status   | OK       | 
6. +---------------+----------+----------+----------+ 
7. 1 row in set (40.60 sec) 

3、再来看看优化后的效果,代码如下:

1. mysql> SHOW INDEX FROM `tbl_name`; 
2. +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
3. | Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | 
4. +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 
5. | tbl_name |          0 | PRIMARY    |            1 | StepID      | A         |      172462 |     NULL | NULL   |      | BTREE      |         | 
6. | tbl_name |          1 | FlowID     |            1 | FlowID      | A         |       86231 |     NULL | NULL   |      | BTREE      |         | 
7. | tbl_name |          1 | WagerCount |            1 | WagerCount  | A         |        4311 |     NULL | NULL   |      | BTREE      |         | 
8. | tbl_name |          1 | WagerID_3  |            1 | WagerID     | A         |       86231 |     NULL | NULL   |      | BTREE      |         | 
9. | tbl_name |          1 | WagerID_3  |            2 | StepType    | A         |      172462 |     NULL | NULL   |      | BTREE      |         | 
10. | tbl_name |          1 | WagerID_3  |            3 | ParamResult | A         |      172462 |      255 | NULL   |      | BTREE      |         | 
11. | tbl_name |          1 | StepType_2 |            1 | StepType    | A         |           9 |     NULL | NULL   |      | BTREE      |         | 
12. | tbl_name |          1 | StepType_2 |            2 | ParamResult | A         |       86231 |      255 | NULL   |      | BTREE      |         | 
13. | tbl_name |          1 | WagerID_2  |            1 | WagerID     | A         |       86231 |     NULL | NULL   |      | BTREE      |         | 
14. | tbl_name |          1 | WagerID_2  |            2 | StepType    | A         |      172462 |     NULL | NULL   |      | BTREE      |         | 
15. | tbl_name |          1 | WagerID_2  |            3 | ParamResult | A         |      172462 |      255 | NULL   |      | BTREE      |         | 
16. +----------+------------+------------+--------------+-------------+-----------+-------------+--- 

如果在php中使用,PHP程序,代码如下:

1. header("Content-type: text/html; charset=utf-8"); 
2. set_time_limit(0); 
3.  echo date('Y-m-d H:i:s').'Begin<br/>'; 
4.  /* 
5.  *使用OPTIMIZE TABLE 优化表空间 
6.  *回收空间，减少碎片 
7.  */ 
8.  mysql_connect('localhost', 'root', '123456') or die('数据库连接失败'.mysql_error()); 
9.  mysql_query('SET NAME UTF8'); 
10.  $database = 'db3'; 
11.  mysql_select_db($database); 
12.  $res = mysql_query('SHOW TABLES FROM '.$database); 
13.  while($row = mysql_fetch_row($res)) 
14.  { 
15.   $table = $row[0]; 
16.   $sql = sprintf('OPTIMIZE TABLE %s.%s', $database, $table); 
17.   if(mysql_query($sql)) 
18.   { 
19.    echo '优化表'.$table.'完成。<br>'; 
20.   } 
21.   else 
22.   { 
23.    echo '优化表'.$table.'失败。'.mysql_error(); 
24.    exit; 
25.   } 
26.  } 
27.  echo date('Y-m-d H:i:s').'End<br/>'; 

看完文章后理解.

作用:回收空间，减少碎片

方法:OPTIMIZE TABLE tablename

情景:磁盘耗尽、InnoDB Tablespaces用完,先用OPTIMIZE TABLE 命令优化,再考虑扩容.

注意:OPTIMIZE 命令支持的引擎MyIsam,InnoDB,ARCHVE,对于InnoDB,它会重建数据表、更新索引统计信息、回收主键索引空间.