MYSQL中OPTIMIZE TABLE优化使用方法-百分百源码网

MYSQL中OPTIMIZE TABLE优化使用方法

发布时间：01/15 来源：浏览：关键词：

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、先来看看多次删除插入操作后的表索引情况

代码如下

mysql> SHOW INDEX FROM `tbl_name`;

+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| tbl_name | 1 | WagerID_3 | 3 | ParamResult | A | 1 | 255 | NULL | | BTREE | |

| tbl_name | 1 | StepType_2 | 2 | ParamResult | A | 1 | 255 | NULL | | BTREE | |

| tbl_name | 1 | WagerID_2 | 3 | ParamResult | A | 1 | 255 | NULL | | BTREE | |

+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

11 rows in set (0.01 sec)

2、优化表

代码如下

mysql> optimize table tbl_name;

+---------------+----------+----------+----------+

+---------------+----------+----------+----------+

+---------------+----------+----------+----------+

1 row in set (40.60 sec)

3、再来看看优化后的效果

代码如下

mysql> SHOW INDEX FROM `tbl_name`;

+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| tbl_name | 1 | WagerID_3 | 3 | ParamResult | A | 172462 | 255 | NULL | | BTREE | |

| tbl_name | 1 | StepType_2 | 2 | ParamResult | A | 86231 | 255 | NULL | | BTREE | |

| tbl_name | 1 | WagerID_2 | 3 | ParamResult | A | 172462 | 255 | NULL | | BTREE | |

+----------+------------+------------+--------------+-------------+-----------+-------------+---

如果在php中使用

PHP程序：

代码如下

header("Content-type: text/html; charset=utf-8");

set_time_limit(0);
echo date('Y-m-d H:i:s').'Begin<br/>';
/*
*使用OPTIMIZE TABLE 优化表空间
*回收空间，减少碎片
*/
mysql_connect('localhost', 'root', '123456') or die('数据库连接失败'.mysql_error());
mysql_query('SET NAME UTF8');
$database = 'db3';
mysql_select_db($database);
$res = mysql_query('SHOW TABLES FROM '.$database);
while($row = mysql_fetch_row($res))
{
$table = $row[0];
$sql = sprintf('OPTIMIZE TABLE %s.%s', $database, $table);
if(mysql_query($sql))
{
   echo '优化表'.$table.'完成。<br>';
}
else
{
   echo '优化表'.$table.'失败。'.mysql_error();
   exit;
}
}
echo date('Y-m-d H:i:s').'End<br/>';