mysql optimize table

標籤：bsp   建立   負載   ORC   儲存空間   測試   線上   片段   索引   

文法結構：

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]    TABLE tbl_name [, tbl_name] ...

optimize table 會重組表資料和索引的實體儲存體，減少對儲存空間使用和提升訪問表時io效率。optimize table後，表的變化和儲存引擎也有關。

以下情境使用 optimize table，和表的類型有關：
1.innodb儲存引擎+獨立資料表空間，optimize table會重組表和索引資料，磁碟空間被回收。
2.插入、更新、刪除的列是innodb表中fulltext索引的列，要先設定innodb_optimize_fulltext_only=1。
為了將對索引的維護設定在一個合理的時間內，可以設定innodb_ft_num_word_optimize指定一次處理多少個words，執行多次optimize table操作直到索引更新結束。
3.刪除myisam、archive儲存引擎表中大量資料，或者對myisam、archive表變長的行做了大量修改。刪除的行被為維護在一個鏈表中，之後的insert可以重用這些位置。
可以使用optimize TABLE來回收未使用的空間並整理資料檔案。在對錶進行了大量更改之後，該語句還可以提高使用該表的語句的效能，有時效果顯著。

optimize table需要對錶具有select、insert許可權。

optimize table支援innodb、myisam、archive儲存引擎的表。

預設是不支援其他儲存引擎的表，會返回不支援的提示資訊。要想支援其他儲存引擎，可以使用mysqld --skip-new選項開啟對其他儲存引擎的支援。使用--skip-new選項後，optimize table僅僅被映射成alter table操作。

不支援對view進行optimize。

optimize table支援分區表。

預設，optimize table被記入二進位日誌，並複製到slave節點。可以使用no_write_binlog或local取消對二進位日誌的寫入。

 

對innodb表執行optimize table

對於innodb表，optimize table被映射成alter table ... force，重建表和更新索引統計資訊並釋放空間。
對innodb執行optimize table操作，輸出類似下面的結果：

> OPTIMIZE TABLE foo;+----------+----------+----------+-------------------------------------------------------------------+| Table    | Op       | Msg_type | Msg_text                                                          |+----------+----------+----------+-------------------------------------------------------------------+| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead || test.foo | optimize | status   | OK                                                                |+----------+----------+----------+-------------------------------------------------------------------+

optimize table對innodb常規表、分區表執行線上ddl，減少了並發dml操作的宕機時間。

optimize table 觸發表重建，內部執行alter table ... force操作。
在prepare階段和commit階段會對錶加上獨佔鎖定。在prepare階段會更新中繼資料並建立一個內部中間表，在commit階段提交對中繼資料的修改。

 

optimize table在以下情境使用資料拷貝的方式進行重建表：
1.開啟了old_alter_table系統變數
2.使用了mysqld --skip-new選項啟動資料庫

不是所有的情境都可以使用線上ddl。如果innodb表包含fulltext索引，是不支援optimize table的線上ddl的。只能使用資料拷貝的方式。

alter table使用的是資料copy；alter table ... force使用的是線上ddl。

InnoDB使用頁面分配方法儲存資料，不像傳統儲存引擎(比如MyISAM)那樣存在片段問題。執行optimize table之前要考慮事務的負載大小：
·有些層級的片段是系統預期的。innodb只會插入資料到頁面的93%，剩下的空間是給update預留的，以免出現頁的分裂
·刪除操作可能會留下很多空隙，執行optimize table是有意義的
·更新通常是在相同的頁中重寫資料，空間是否充足取決於行格式和列的資料類型
·由於innodb的mvcc機制，高並發環境可能會導致索引產生空隙

 

對myisam表執行optimize table
對於myisam表，optimize table操作執行以下工作：
1.如果表含有刪除的列、分列的列，optimize table會修複表
2.如果索引頁沒有排序，optimize table會將索引頁進行排序
3.如果表的統計資訊不是最新的，optimize table會更新索引資訊

 

補充：

對innodb表執行optimize table操作的時候，會報"Table does not support optimize, doing recreate + analyze instead"提示。有人直接說optimize table不支援innodb表。其實並不能這麼理解。

下面是測試結果：

Server version: 5.7.19 MySQL Community Server (GPL)CREATE TABLE m_test(id int unsigned, body text) engine=myisam charset=utf8;CREATE TABLE i_test(id int unsigned, body text) engine=innodb charset=utf8;INSERT INTO m_test VALUES(1, ‘AAAAA‘);INSERT INTO m_test VALUES(2, ‘BBBBB‘);INSERT INTO m_test VALUES(3, ‘CCCCC‘);INSERT INTO m_test VALUES(4, ‘DDDDD‘);INSERT INTO m_test VALUES(5, ‘EEEEE‘);INSERT INTO i_test VALUES(1, ‘AAAAA‘);INSERT INTO i_test VALUES(2, ‘BBBBB‘);INSERT INTO i_test VALUES(3, ‘CCCCC‘);INSERT INTO i_test VALUES(4, ‘DDDDD‘);INSERT INTO i_test VALUES(5, ‘EEEEE‘);#多次插入相同的資料：INSERT INTO m_test SELECT id, body FROM m_test;INSERT INTO i_test SELECT id, body FROM i_test;...

查看錶和檔案的大小：

ysql> select count(*) from m_test;+----------+| count(*) |+----------+| 83886080 |+----------+1 row in set (0.00 sec)mysql> select count(*) from i_test;+----------+| count(*) |+----------+| 83886080 |+----------+1 row in set (36.04 sec) #明顯慢很多mysql> # ll |grep test-rw-r----- 1 mysql mysql       8586 Sep  2 10:03 i_test.frm-rw-r----- 1 mysql mysql 3267362816 Sep  2 10:30 i_test.ibd-rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm-rw-r----- 1 mysql mysql 1677721600 Sep  2 10:26 m_test.MYD-rw-r----- 1 mysql mysql       1024 Sep  2 10:26 m_test.MYI

刪除一定資料後查看錶和檔案的大小：

mysql> DELETE FROM i_test WHERE id < 3;mysql> DELETE FROM m_test WHERE id < 3;# ll |grep test-rw-r----- 1 mysql mysql       8586 Sep  2 10:03 i_test.frm-rw-r----- 1 mysql mysql 3267362816 Sep  2 10:39 i_test.ibd-rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm-rw-r----- 1 mysql mysql 1677721600 Sep  2 10:37 m_test.MYD-rw-r----- 1 mysql mysql       1024 Sep  2 10:37 m_test.MYImysql> select count(*) from m_test;+----------+| count(*) |+----------+| 50331648 |+----------+1 row in set (0.00 sec)mysql> select count(*) from i_test;+----------+| count(*) |+----------+| 50331648 |+----------+1 row in set (52.91 sec)

分別執行optimize table操作：

mysql> optimize table m_test;+-------------+----------+----------+----------+| Table       | Op       | Msg_type | Msg_text |+-------------+----------+----------+----------+| test.m_test | optimize | status   | OK       |+-------------+----------+----------+----------+1 row in set (8.49 sec)mysql> optimize table i_test;+-------------+----------+----------+-------------------------------------------------------------------+| Table       | Op       | Msg_type | Msg_text                                                          |+-------------+----------+----------+-------------------------------------------------------------------+| test.i_test | optimize | note     | Table does not support optimize, doing recreate + analyze instead || test.i_test | optimize | status   | OK                                                                |+-------------+----------+----------+-------------------------------------------------------------------+2 rows in set (47.73 sec)mysql> # ll |grep test-rw-r----- 1 mysql mysql       8586 Sep  2 10:42 i_test.frm-rw-r----- 1 mysql mysql 2243952640 Sep  2 10:43 i_test.ibd-rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm-rw-r----- 1 mysql mysql 1006632960 Sep  2 10:41 m_test.MYD-rw-r----- 1 mysql mysql       1024 Sep  2 10:41 m_test.MYI

從上面的結果可以看到，無論是innodb、還是myisam表都被收縮了。

 

使用mysqld --skip-new重啟mysqlserver後，再次對innodb表執行optimize table操作：

mysql> optimize table i_test;Query OK, 50331648 rows affected (3 min 10.82 sec)Records: 50331648  Duplicates: 0  Warnings: 0mysql> # ll |grep test-rw-r----- 1 mysql mysql       8586 Sep  2 10:47 i_test.frm-rw-r----- 1 mysql mysql 1962934272 Sep  2 10:50 i_test.ibd-rw-r----- 1 mysql mysql       8586 Sep  2 10:03 m_test.frm-rw-r----- 1 mysql mysql 1006632960 Sep  2 10:41 m_test.MYD-rw-r----- 1 mysql mysql       1024 Sep  2 10:41 m_test.MYI

可以看到，innodb表再次發生了收縮。

 

結論：
"Table does not support optimize, doing recreate + analyze instead"並不是說innodb不支援optimize table。
因為對於innodb表，optimize table被映射成alter table ... force，重建表和更新索引統計資訊並釋放空間。

 

mysql optimize table