MySQL 5.6 整理表的碎片

可以看到，當(dāng)前表的碎片率超高了，50.6%。

公司主營業(yè)務(wù)：成都網(wǎng)站設(shè)計、成都做網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)推出安定免費做網(wǎng)站回饋大家。

有三種辦法整理碎片

這三種操作都是先創(chuàng)建一個臨時表復(fù)制完成后再刪除舊表，所以在執(zhí)行操作的過程中磁盤會先增大。

會鎖表

會鎖表

pt-online-schema-change - ALTER tables 無需鎖表。

整理結(jié)果很明顯，整理后碎片率0.3%。

這里有幾個參數(shù)需要介紹一下：

--dry-run

這個參數(shù)不建立觸發(fā)器，不拷貝數(shù)據(jù)，也不會替換原表。只是創(chuàng)建和更改新表。

--execute

表明你已經(jīng)閱讀了文檔，并且確認(rèn)要 alter the table。你必須配置這個參數(shù)來 alter the table。如果你不配置，那么工具將只進(jìn)行一些安全檢查然后就退出了。這幫助確保你已經(jīng)閱讀了文檔，并且了解如何使用該工具。如果你沒有閱讀這些文檔，那么不會設(shè)置該參數(shù)。

--critical-load

每次chunk操作前后，會根據(jù)show global status統(tǒng)計指定的狀態(tài)量的變化，默認(rèn)是統(tǒng)計Thread_running。目的是為了安全，防止原始表上的觸發(fā)器引起負(fù)載過高。這也是為了防止在線DDL對線上的影響。超過設(shè)置的閥值，就會終止操作，在線DDL就會中斷。提示的異常如上報錯信息。

--max-lag

type: time; default: 1s

lag 滯后偏移

暫停數(shù)據(jù)拷貝，直到所有replicas的lag值低于該值。在每個 data-copy query (each chunk)后，工具會通過Seconds_Behind_Master查詢所有replica的 replication lag 。如果任何replica lag大于該值，那么工具會sleep --check-interval 秒，然后再次檢查所有replica。如果你指定 --check-slave-lag ，那么工具會檢查那臺server，而不是所有server。如果你想控制哪個提供工具的監(jiān)控，配置DSN值 --recursion-method 。

工具會等待直到replicas停止lagging。如果任一replica停止，工具會一直處于等待狀態(tài)直到該replica啟動。 在所有replicas運行并且lagging不大的情況下，數(shù)據(jù)拷貝繼續(xù)。

工具在等待的時候，會打印進(jìn)程報告。如果replica停止了，會立即打印進(jìn)程報告，然后在每個進(jìn)程報告期間重復(fù)。

--check-interval

type: time; default: 1

Sleep time between checks for --max-lag .

--max-load

選項定義一個閥值，在每次chunk操作后，查看show global status狀態(tài)值是否高于指定的閥值。該參數(shù)接受一個mysql status狀態(tài)變量以及一個閥值，如果沒有給定閥值，則定義一個閥值為為高于當(dāng)前值的20%。注意這個參數(shù)不會像--critical-load終止操作，而只是暫停操作。當(dāng)status值低于閥值時，則繼續(xù)往下操作。是暫停還是終止操作這是--max-load和--critical-load的差別。

--charset

簡寫: -A; type: string

設(shè)置默認(rèn)字符集。如果值為 utf8，設(shè)置 Perl’s binmode on STDOUT to utf8，傳送 mysql_enable_utf8 參數(shù)到 DBD::mysql，然后在連接到MySQL后運行 SET NAMES UTF8 。其他的值也是在STDOUT設(shè)置 binmode，然后在連到MySQL后運行 SET NAMES 。

--check-replication-filters

檢查復(fù)制中是否設(shè)置了過濾條件，如果設(shè)置了，程序?qū)⑼顺?/p>

--nocheck-replication-filters

不檢查復(fù)制中是否設(shè)置了過濾條件

--set-vars

設(shè)置mysql的變量值

--check-slave-lag

檢查主從延遲

--no-version-check

不檢查版本，在阿里云服務(wù)器中一般加入此參數(shù)，否則會報錯

數(shù)據(jù)庫存儲空間中碎片產(chǎn)生的原因　及如何回收碎片

以MySQL為例，碎片的存在十分影響性能

MySQL 的碎片是 MySQL 運維過程中比較常見的問題，碎片的存在十分影響數(shù)據(jù)庫的性能，本文將對 MySQL 碎片進(jìn)行一次講解。

判斷方法：

MySQL 的碎片是否產(chǎn)生，通過查看

show table status from table_nameG;

這個命令中 Data_free 字段，如果該字段不為 0，則產(chǎn)生了數(shù)據(jù)碎片。

產(chǎn)生的原因：

1. 經(jīng)常進(jìn)行 delete 操作

經(jīng)常進(jìn)行 delete 操作，產(chǎn)生空白空間，如果進(jìn)行新的插入操作，MySQL將嘗試?yán)眠@些留空的區(qū)域，但仍然無法將其徹底占用，久而久之就產(chǎn)生了碎片；

演示：

創(chuàng)建一張表，往里面插入數(shù)據(jù)，進(jìn)行一個帶有 where 條件或者 limit 的 delete 操作，刪除前后對比一下 Data_free 的變化。

刪除前：

刪除后：

Data_free 不為 0，說明有碎片；

2. update 更新

update 更新可變長度的字段(例如 varchar 類型)，將長的字符串更新成短的。之前存儲的內(nèi)容長，后來存儲是短的，即使后來插入新數(shù)據(jù)，那么有一些空白區(qū)域還是沒能有效利用的。

演示：

創(chuàng)建一張表，往里面插入一條數(shù)據(jù)，進(jìn)行一個 update 操作，前后對比一下 Data_free 的變化。

CREATE TABLE `t1` ( `k` varchar(3000) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

更新語句：update t1 set k='aaa';

更新前長度：223 Data_free：0

更新后長度：3 Data_free：204

Data_free 不為 0，說明有碎片；

產(chǎn)生影響：

1. 由于碎片空間是不連續(xù)的，導(dǎo)致這些空間不能充分被利用；

2. 由于碎片的存在，導(dǎo)致數(shù)據(jù)庫的磁盤 I/O 操作變成離散隨機讀寫，加重了磁盤 I/O 的負(fù)擔(dān)。

清理辦法：

MyISAM：optimize table 表名；（OPTIMIZE 可以整理數(shù)據(jù)文件,并重排索引）

Innodb：

1. ALTER TABLE tablename ENGINE=InnoDB；(重建表存儲引擎，重新組織數(shù)據(jù))

2. 進(jìn)行一次數(shù)據(jù)的導(dǎo)入導(dǎo)出

碎片清理的性能對比：

引用我之前一個生產(chǎn)庫的數(shù)據(jù)，對比一下清理前后的差異。

SQL執(zhí)行速度：

select count(*) from test.twitter_11;

修改前：1 row in set (7.37 sec)

修改后：1 row in set (1.28 sec)

結(jié)論：

通過對比，可以看到碎片清理前后，節(jié)省了很多空間，SQL執(zhí)行效率更快。所以，在日常運維工作中，應(yīng)對碎片進(jìn)行定期清理，保證數(shù)據(jù)庫有穩(wěn)定的性能。

MySQL—碎片整理

刪除數(shù)據(jù)必然會在數(shù)據(jù)文件中造成不連續(xù)的空白空間,而當(dāng)插入數(shù)據(jù)時,這些空白空間則會被利用起來.于是造成了數(shù)據(jù)的存儲位置不連續(xù),以及物理存儲順序與理論上的排序順序不同,這種是數(shù)據(jù)碎片.實際上數(shù)據(jù)碎片分為兩種,一種是單行數(shù)據(jù)碎片,另一種是多行數(shù)據(jù)碎片.前者的意思就是一行數(shù)據(jù),被分成N個片段,存儲在N個位置.后者的就是多行數(shù)據(jù)并未按照邏輯上的順序排列.當(dāng)有大量的刪除和插入操作時,必然會產(chǎn)生很多未使用的空白空間,這些空間就是多出來的額外空間.索引也是文件數(shù)據(jù),所以也會產(chǎn)生索引碎片,理由同上,大概就是順序紊亂的問題.Engine 不同,OPTIMIZE 的操作也不一樣的,MyISAM 因為索引和數(shù)據(jù)是分開的,所以 OPTIMIZE 可以整理數(shù)據(jù)文件,并重排索引。這樣不但會浪費空間，并且查詢速度也更慢。

查看碎片信息：

Index_length 代表索引的總量

Data_free 代表碎片數(shù)量

從information_schema中獲取信息：

碎片整理：

過程時間長短取決于表大小和碎片多少，

返回結(jié)果optimize status OK則整理完成；