場景：
當一張單表10億資料量的表放在你面前，你將面臨著什麼？
為了提升資料庫資源利用率，一個例項中，在不互相影響，保證業務高效的前提下，我們會將同一個大業務下的不同小業務放在一個例項中，我們的磁碟空間是2T，告警閾值為當磁碟剩餘空間10%時發出簡訊告警。筆者接到某業務主庫磁碟剩餘空間告警的簡訊後，經過一番查探，發現從幾天前開始，有一張表的資料量增長非常快，而在之前，磁碟空間下降率還是較為平緩的，該表存在大欄位text，其大批次寫入更新，導致磁碟消耗迅猛。
我們首先來看下該表的表結構：
mysql> CREATE TABLE `tablename_v2` （ `id` bigint（20） unsigned NOT NULL AUTO_INCREMENT， `No` varchar（64） NOT NULL DEFAULT ‘’， `Code` varchar（64） NOT NULL DEFAULT ‘’ ， `log` varchar（64） DEFAULT ‘’ ， `log1` varchar（64） DEFAULT ‘’ ， 。。。。。 `Phone` varchar（20） DEFAULT ‘’， `createTime` bigint（20） unsigned NOT NULL DEFAULT ‘0’， `updateTime` bigint（20） unsigned NOT NULL DEFAULT ‘0’， PRIMARY KEY （`id`）， UNIQUE KEY `uk_mailNo` （`No`，`Code`）， KEY `idx_Phone` （`Phone`）） ENGINE=InnoDB AUTO_INCREMENT=9794134664 DEFAULT CHARSET=utf8；
與業務瞭解得知，該表幾乎沒有刪除操作，由於資料量過大，我們模糊使用auto_increment來作為表數量預估值，避免count（）操作對線上造成的影響。
案例分析
與業務溝通了解後得知，該表可以清理4個月以前的老舊資料，因此可以使用delete的方式清除，而我們透過表結構可以看出，該表在設計之初，並沒有對updateTime列建立索引，因此以時間為範圍去進行delete操作，這顯然是不合理的。
經過與業務協商，我們確定了可以將id作為刪除條件，刪除id<2577754125之前的資料
也就是說，此時的delete語句變為了：
mysql> delete from tablename_v2 where id <2577754125；
且不說delete操作有多慢，直接執行這樣的SQL也會有諸如長事務告警，從庫大量延遲等併發症產生，因此絕不能在生產庫上進行這種大批次的危險操作。
實戰
監控
從監控圖我們能看出磁碟下降的趨勢：
監控顯示，從6月14日-6月18日期間，磁碟消耗最為嚴重，與業務溝通得知，618期間大促引發該表儲存量激增導致。
實戰操作
我們透過檢視binlog發現，叢集中binlog的重新整理量雖不說像筆者上個案例那樣多麼迅猛，但也絕不是老實本分
我們可以看出，在高峰期間，binlog的重新整理間隔最短達到了2分鐘寫滿1。1GB的binlog。因此筆者與業務溝通後，首先清理binlog日誌，將 expire_logs_days從7天調整至3天。
同時，清理一些能夠清理的無用日誌、廢舊檔案等等。
我們也能在上面的監控圖看到在做完這些清理操作後，磁碟空間剩餘從4%提升至12%，但隨後依舊保持原有速率下降。
pt-archiver
真兇找到了，我們怎麼辦，別急，使用pt-archiver。pt-archiver工具是percona工具集的一員，是歸檔MySQL大表資料的最佳輕量級工具之一。他可以實現分chunk分批次歸檔和刪除資料，能避免一次性操作大量資料帶來的各種問題。
閒話不多說，一向本著實戰的原則，我們直接上命令：
pt-archiver ——sourceh=c3-helei-db01。bj，D=helei，t=tablename_v2，u=sys_admin，p=MANAGER——where ‘id<2577754125’ ——purge ——progress 10000 ——limit=10000——no-check-charset ——txn-size=10000 ——bulk-delete ——statistics ——max-lag=20——check-slave-lag c3-helei-db02。bj
簡單說下常用的引數：
source
目標節點
where
條件
purge
刪除source資料庫的相關匹配記錄
progress
每處理多少行顯示一次資訊limit每次取出多少行處理no-check-charset不檢查字符集
txn-size
每多少行提交一次bulk-delete並行刪除statistics結束後輸出統計資訊
max-lag
最大延遲check-slave-lag檢查某個目標從庫的延遲
Warning:警告
這裡就又有個小坑了，的確，我們使用bulk-delete引數能夠增加刪除速率，相比不使用bulk-delete速度能夠提升10倍左右，但問題也就顯現出來，在使用上述命令期間，發現binlog每秒寫入量激增，這又回到了我們說的，哪些情況會導致binlog轉為row格式。
首先我們需要了解到使用bulk-delete時，sql是如下執行的：
mysql> delete from tablename_v2 where id >xxx and id < xxx limit 10000。
如果您之前關注過筆者的文章，應該知道，當使用了delete from xxx where xxx limit 語法時，會將binlog_format從mixed轉為row，這樣的話，刪除的同時，binlog由於轉為了row格式也在激增，這與我們的預期是不符的。
因此最終的命令為：
pt-archiver ——sourceh=c3-helei-db01。bj，D=helei，t=tablename_v2，u=sys_admin，p=MANAGER——where ‘id<2577754125’ ——purge ——progress 10000 ——limit=10000——no-check-charset ——txn-size=10000 ——statistics ——max-lag=20——check-slave-lag c3-helei-db02。bj
去掉了bulk-delete，這樣的話就能夠保證正常的delete，而不加limit，binlog不會轉為row格式導致磁碟消耗繼續激增。
對於Innodb引擎來說，delete操作並不會立即釋放磁碟空間，新的資料會優先填滿delete操作後的“空洞”，因此從監控來看就是磁碟不會進一步消耗了，說明我們的pt-archiver工具刪除是有效的。
困惑
首先我們要知道，當你面對一張資料量龐大的表的時候，有些東西就會受限制，例如：
不能alter操作，因為這會阻塞dml操作。
對於本案例，空間本就不足，也不能使用pt-online工具來完成。
對於不能alter其實是比較要命的，比如開發要求在某個時間段儘快上線新業務，而新業務需要新增列，此時面對這麼龐大的量級，alter操作會異常緩慢。
因此，筆者與研發溝通，儘快採用物理分表的方式解決這個問題，使用物理分表，清理表的操作就會很容易，無需delete，直接drop 老表就可以了。其次，物理分表讓alter語句不會卡住太久，使用pt-online工具也不會一次性佔據過多的磁碟空間誘發磁碟空間不足的告警。
再有就是遷移TiDB，TiDB相較MySQL更適合儲存這類業務。
再談binlog_format
我們選取其中高峰期的binlog發現其update操作轉為了row格式，記錄了所有列變更前後的所有資訊，而binlog中並未出現update xxx limit這種操作，那又會是什麼引發的row格式記錄呢？
這裡這篇文章又丟擲一個新的案例，在官網那篇何時mixed轉row格式中又一個沒有記錄的情況
官方文件：
When running in MIXED logging format， the server automatically switches from statement-based to row-based logging under the following conditions：When a DML statement updates an NDBCLUSTER table。When a function contains UUID（）。When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked。 Like all other unsafe statements， this generates a warning if binlog_format = STATEMENT。When any INSERT DELAYED is executed。When a call to a UDF is involved。If a statement is logged by row and the session that executed the statement has any temporary tables， logging by row is used for all subsequent statements （except for those accessing temporary tables） until all temporary tables in use by that session are dropped。This is true whether or not any temporary tables are actually logged。Temporary tables cannot be logged using row-based format； thus， once row-based logging is used， all subsequent statements using that table are unsafe。 The server approximates this condition by treating all statements executed during the session as unsafe until the session no longer holds any temporary tables。When FOUND_ROWS（） or ROW_COUNT（） is used。 （Bug #12092， Bug #30244）When USER（）， CURRENT_USER（）， or CURRENT_USER is used。 （Bug #28086）When a statement refers to one or more system variables。 （Bug #31168）
我們這個案例中又出現了一個新的因素就是：
當表結構中存在多個唯一索引（包括主鍵id），本案例中存在主鍵和UNIQUE KEY `uk_mailNo`這個唯一索引，且使用了
INSERT 。。。 ON DUPLICATE KEY UPDATE
這時，mysql binlog_format就會被轉為row格式，這個內容也是記錄在官網的其他章節：
https：//dev。mysql。com/doc/refman/5。5/en/insert-on-duplicate。html
也就是說，只要業務解決了使用這種語法插入的話，磁碟空間下降迅猛的原因也能夠緩解不少。我們統計發現，qps更高的其他業務中，binlog保留7天的磁碟消耗量在60GB
而該業務我們僅僅保留3天binlog，卻依舊消耗了430GB的磁碟空間，這已經超過了我們整個2T磁碟空間的5分之一了。
——總結——
透過這個案例，我們能夠了解到什麼情況下binlog_format會由MIXED格式轉為ROW格式，以及觸發的一系列併發症和解決辦法，還有pt工具pt-archiver的使用。
