MYSQL中OPTIMIZE TABLE优化使用方法

数据库

MYSQL中OPTIMIZE TABLE优化使用方法

2019/10/10/17:36:09 阅读：3351 来源：谷歌SEO算法标签： SEO技术

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>SHOWINDEXFROM`tbl_name`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|tbl_name|0|PRIMARY|1|StepID|A|1|NULL|NULL||BTREE||
|tbl_name|1|FlowID|1|FlowID|A|1|NULL|NULL||BTREE||
|tbl_name|1|WagerCount|1|WagerCount|A|1|NULL|NULL||BTREE||
|tbl_name|1|WagerID_3|1|WagerID|A|1|NULL|NULL||BTREE||
|tbl_name|1|WagerID_3|2|StepType|A|1|NULL|NULL||BTREE||
|tbl_name|1|WagerID_3|3|ParamResult|A|1|255|NULL||BTREE||
|tbl_name|1|StepType_2|1|StepType|A|1|NULL|NULL||BTREE||
|tbl_name|1|StepType_2|2|ParamResult|A|1|255|NULL||BTREE||
|tbl_name|1|WagerID_2|1|WagerID|A|1|NULL|NULL||BTREE||
|tbl_name|1|WagerID_2|2|StepType|A|1|NULL|NULL||BTREE||
|tbl_name|1|WagerID_2|3|ParamResult|A|1|255|NULL||BTREE||
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
11rowsinset(0.01sec)--phpfensi.com

2、优化表,代码如下:

mysql>optimizetabletbl_name;
+---------------+----------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+---------------+----------+----------+----------+
|test.tbl_name|optimize|status|OK|
+---------------+----------+----------+----------+
1rowinset(40.60sec)

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

mysql>SHOWINDEXFROM`tbl_name`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
|tbl_name|0|PRIMARY|1|StepID|A|172462|NULL|NULL||BTREE||
|tbl_name|1|FlowID|1|FlowID|A|86231|NULL|NULL||BTREE||
|tbl_name|1|WagerCount|1|WagerCount|A|4311|NULL|NULL||BTREE||
|tbl_name|1|WagerID_3|1|WagerID|A|86231|NULL|NULL||BTREE||
|tbl_name|1|WagerID_3|2|StepType|A|172462|NULL|NULL||BTREE||
|tbl_name|1|WagerID_3|3|ParamResult|A|172462|255|NULL||BTREE||
|tbl_name|1|StepType_2|1|StepType|A|9|NULL|NULL||BTREE||
|tbl_name|1|StepType_2|2|ParamResult|A|86231|255|NULL||BTREE||
|tbl_name|1|WagerID_2|1|WagerID|A|86231|NULL|NULL||BTREE||
|tbl_name|1|WagerID_2|2|StepType|A|172462|NULL|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);
echodate('Y-m-dH:i:s').'Begin<br/>';
/*
*使用OPTIMIZETABLE优化表空间
*回收空间，减少碎片
*/
mysql_connect('localhost','root','123456')ordie('数据库连接失败'.mysql_error());
mysql_query('SETNAMEUTF8');
$database='db3';
mysql_select_db($database);
$res=mysql_query('SHOWTABLESFROM'.$database);
while($row=mysql_fetch_row($res))
{
$table=$row[0];
$sql=sprintf('OPTIMIZETABLE%s.%s',$database,$table);
if(mysql_query($sql))
{
echo'优化表'.$table.'完成。<br>';
}
else
{
echo'优化表'.$table.'失败。'.mysql_error();
exit;
}
}
echodate('Y-m-dH:i:s').'End<br/>';