MySQL數據碎片的整理和分析

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。

關于MySQL數據碎片的整理和分析問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。