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`;
- +----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | 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) --Vevb.com
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,它會重建數據表、更新索引統計信息、回收主鍵索引空間.
新聞熱點
疑難解答
