MySQL數(shù)據(jù)碎片的整理和分析

MySQL數(shù)據(jù)碎片的整理和分析，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。

MySQL具有相當(dāng)多不同種類的存儲(chǔ)引擎來(lái)實(shí)現(xiàn)列表中的數(shù)據(jù)存儲(chǔ)功能。每當(dāng)MySQL從你的列表中刪除了一行內(nèi)容，該段空間就會(huì)被留空。而在一段時(shí)間內(nèi)的大量刪除操作，會(huì)使這種留空的空間變得比存儲(chǔ)列表內(nèi)容所使用的空間更大。當(dāng)MySQL對(duì)數(shù)據(jù)進(jìn)行掃描時(shí)，它掃描的對(duì)象實(shí)際是列表的容量需求上限，也就是數(shù)據(jù)被寫入的區(qū)域中處于峰值位置的部分。如果進(jìn)行新的插入操作，MySQL將嘗試?yán)眠@些留空的區(qū)域，但仍然無(wú)法將其徹底占用。這種額外的破碎的存儲(chǔ)空間在讀取效率方面比正常占用的空間要低得多。
以下實(shí)驗(yàn)舉例說(shuō)明:

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> 


創(chuàng)建一個(gè)測(cè)試庫(kù):


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)


插入幾行數(shù)據(jù):
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)




現(xiàn)在我們進(jìn)行碎片查看:
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


刪除一行，并再次檢測(cè):


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的值并沒(méi)有減小


清理碎片試試:


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”一欄顯示出了我們刪除第二行后所產(chǎn)生的留空空間。想象一下如果你有兩萬(wàn)行指令的話，結(jié)果是什么樣的。
以此推算，它們將耗費(fèi)四十萬(wàn)字節(jié)的存儲(chǔ)空間。現(xiàn)在如果你將兩萬(wàn)條命令行刪到只剩一行，列表中有用的內(nèi)容將只占二十字節(jié)，
但MySQL在讀取中會(huì)仍然將其視同于一個(gè)容量為四十萬(wàn)字節(jié)的列表進(jìn)行處理，并且除二十字節(jié)以外，其它空間都被白白浪費(fèi)了。

備注：
1.MySQL官方建議不要經(jīng)常(每小時(shí)或每天)進(jìn)行碎片整理，一般根據(jù)實(shí)際情況，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只對(duì)MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最為明顯。此外，并不是所有表都需要進(jìn)行碎片整理，
一般只需要對(duì)包含可變長(zhǎng)度的文本數(shù)據(jù)類型的表進(jìn)行整理即可。
3.在OPTIMIZE TABLE運(yùn)行過(guò)程中，MySQL會(huì)鎖定表。
4.默認(rèn)情況下，直接對(duì)InnoDB引擎的數(shù)據(jù)表使用OPTIMIZE TABLE，可能會(huì)顯示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。
這個(gè)時(shí)候，我們可以用mysqld --skip-new或者mysqld --safe-mode命令來(lái)重啟MySQL，以便于讓其他引擎支持OPTIMIZE TABLE。

關(guān)于MySQL數(shù)據(jù)碎片的整理和分析問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。