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

147次閱讀

共計(jì) 3881 個(gè)字符，預(yù)計(jì)需要花費(fèi) 10 分鐘才能閱讀完成。

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

MySQL 具有相當(dāng)多不同種類(lèi)的存儲(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ù)被寫(xiě)入的區(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)

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 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ù)類(lèi)型的表進(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)注丸趣 TV 行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

正文完