MySQL資料碎片的整理和分析

MySQL具有相當多不同種類的儲存引擎來實現列表中的資料儲存功能。每當MySQL從你的列表中刪除了一行內容，該段空間就會被留空。而在一段時間內的大量刪除操作，會使這種留空的空間變得比儲存列表內容所使用的空間更大。當MySQL對資料進行掃描時，它掃描的物件實際是列表的容量需求上限，也就是資料被寫入的區域中處於峰值位置的部分。如果進行新的插入操作，MySQL將嘗試利用這些留空的區域，但仍然無法將其徹底佔用。這種額外的破碎的儲存空間在讀取效率方面比正常佔用的空間要低得多。
以下實驗舉例說明:

C:\Users\duansf>mysql -uroot -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.13 MySQL Community Server (GPL)


Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 


建立一個測試庫:


mysql> create database frag_test;
Query OK, 1 row affected (0.01 sec)


mysql> use frag_test;
Database changed
mysql> create table frag_test (c1 varchar(64));
Query OK, 0 rows affected (0.27 sec)


插入幾行資料:
mysql> insert into frag_test values ('this is row 1');
Query OK, 1 row affected (0.21 sec)


mysql> insert into frag_test values ('this is row 2');
Query OK, 1 row affected (0.05 sec)


mysql> insert into frag_test values ('this is row 3');
Query OK, 1 row affected (0.03 sec)




現在我們進行碎片檢視:
mysql> show table status from frag_test\G;
*************************** 1. row ***************************
           Name: frag_test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 10485760
 Auto_increment: NULL
    Create_time: 2016-03-17 16:36:04
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)


ERROR:
No query specified


刪除一行，並再次檢測:


mysql> delete from frag_test where c1 = 'this is row 2';
Query OK, 1 row affected (0.07 sec)


mysql> show table status from frag_test\G;
*************************** 1. row ***************************
           Name: frag_test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 10485760
 Auto_increment: NULL
    Create_time: 2016-03-17 16:36:04
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)


Data_free: 10485760 我們看到Data_free的值並沒有減小


清理碎片試試:


mysql> optimize table frag_test;
+---------------------+----------+----------+-----------------------------------
--------------------------------+
| Table               | Op       | Msg_type | Msg_text
                                |
+---------------------+----------+----------+-----------------------------------
--------------------------------+
| frag_test.frag_test | optimize | note     | Table does not support optimize, d
oing recreate + analyze instead |
| frag_test.frag_test | optimize | status   | OK
                                |
+---------------------+----------+----------+-----------------------------------
--------------------------------+
2 rows in set (0.66 sec)


mysql> show table status from frag_test\G;
*************************** 1. row ***************************
           Name: frag_test
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 9437184
 Auto_increment: NULL
    Create_time: 2016-03-17 16:36:04
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Data_free: 9437184 清理碎片後，Data_free減小了。

“data_free”一欄顯示出了我們刪除第二行後所產生的留空空間。想象一下如果你有兩萬行指令的話，結果是什麼樣的。
以此推算，它們將耗費四十萬位元組的儲存空間。現在如果你將兩萬條命令列刪到只剩一行，列表中有用的內容將只佔二十位元組，
但MySQL在讀取中會仍然將其視同於一個容量為四十萬位元組的列表進行處理，並且除二十位元組以外，其它空間都被白白浪費了。

備註：
1.MySQL官方建議不要經常(每小時或每天)進行碎片整理，一般根據實際情況，只需要每週或者每月整理一次即可。
2.OPTIMIZE TABLE只對MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最為明顯。此外，並不是所有表都需要進行碎片整理，
一般只需要對包含可變長度的文字資料型別的表進行整理即可。
3.在OPTIMIZE TABLE執行過程中，MySQL會鎖定表。
4.預設情況下，直接對InnoDB引擎的資料表使用OPTIMIZE TABLE，可能會顯示「 Table does not support optimize, doing recreate + analyze instead」的提示資訊。
這個時候，我們可以用mysqld --skip-new或者mysqld --safe-mode命令來重啟MySQL，以便於讓其他引擎支援OPTIMIZE TABLE。