萬盛學電腦網 >> 數據庫 >> mysql教程 >> MYSQL中OPTIMIZE TABLE優化使用方法
MYSQL中OPTIMIZE TABLE優化使用方法
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`;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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 | |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
11 rows in set (0.01 sec)
2、優化表
代碼如下 復制代碼mysql> optimize table tbl_name;
+---------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+----------+
| test.tbl_name | optimize | status | OK |
+---------------+----------+----------+----------+
1 row in set (40.60 sec)
3、再來看看優化後的效果
代碼如下 復制代碼mysql> SHOW INDEX FROM `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);
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/>';
看完文章後理解
作用：回收空間，減少碎片
方法：OPTIMIZE TABLE tablename
情景：磁盤耗盡、InnoDB Tablespaces用完。先用OPTIMIZE TABLE 命令優化，再考慮擴容。
注意：OPTIMIZE 命令支持的引擎MyIsam, InnoDB, ARCHVE
對於InnoDB，它會重建數據表、更新索引統計信息、回收主鍵索引空間
