引起 MySQL 數(shù)據(jù)庫性能抖動的原因有很多，比如大事務、定時批量查詢等，而這些原因我們一般都會注意到。但是，有一個引起性能抖動的原因卻經(jīng)常被我們忽視，那就是在生產(chǎn)環(huán)境刪除無用的大表，即 DROP TABLE。

一、為什么要 DROP TABLE？

生產(chǎn)環(huán)境中，為什么要 DROP TABLE？相信絕大部分原因是為了釋放空間。

生產(chǎn)環(huán)境大多數(shù)是已經(jīng)確定的庫表，一般不會進行 DROP TABLE 這么重的操作，甚至大部分DBA 都不應該擁有 DROP 權限。

但是隨著數(shù)據(jù)無限增長，在某些系統(tǒng)中，需要將數(shù)據(jù)歸檔到歷史數(shù)據(jù)庫，刪除生產(chǎn)數(shù)據(jù)庫中的表，從而釋放存儲空間，典型的就是偏日志、流水類的數(shù)據(jù)。

不過，當我們談一個數(shù)據(jù)庫多大的時候，單純討論存儲容量意義并不大，我們更希望得到的是活躍記錄有多少。

銀行系統(tǒng)的 Oracle 數(shù)據(jù)庫，雖然單庫都可以數(shù) T，但大部分都是日志數(shù)據(jù)或流水數(shù)據(jù)。

生產(chǎn)環(huán)境的流水表，單表容量可能幾百 G，上 T，怎樣在不影響業(yè)務的情況下，優(yōu)雅地 DROP TABLE 呢 （這里優(yōu)雅的定義為業(yè)務耗時上升不超過20%） ？

DROP TABLE 本來是一個非常簡單的操作，但是要做到在生產(chǎn)環(huán)境中，對業(yè)務耗時影響很小，基本無感知，簡直就是一件藝術。

或許大部分數(shù)據(jù)庫系統(tǒng)都沒有這么高的要求，但是海量并發(fā)的互聯(lián)網(wǎng)業(yè)務有！

二、關于MySQL版本

在 MySQL 5.5.23 版本前，當刪除一張表時，會去遍歷 BP（Buffer Pool 緩沖池）中所有該表對應的頁，這時需要持有 BP 的互斥鎖，而這是一把巨大的鎖，對于 BP 的訪問都需要持有該鎖才能繼續(xù)。

隨著硬件的發(fā)展，128G、256G 的內(nèi)存都已是常態(tài)，若 BP 非常大，如 100G，則遍歷 BP 所需要的時間就會非常長，可能是 5 秒，甚至更長。這時，業(yè)務對于數(shù)據(jù)庫的請求會直接掉底，直接變?yōu)?0。顯然，這不符合我們對于優(yōu)雅的定義。

MySQL 5.5.23 版本開始，做了兩點優(yōu)化：

• 遍歷臟頁列表（flush list）時，只持有一小段時間，之后就釋放 BP 的大鎖，并且不需要真正刷新臟頁，這樣的處理能讓其他業(yè)務請求有機會獲得大鎖的可能，從而業(yè)務的請求不會掉底；

• 不處理 LRU 列表中干凈的頁。因為 LRU 中不使用的頁會慢慢被淘汰，因此刪除表，不用同步地去處理對應的頁，可以通過 LRU 機制，異步最終淘汰機制。

MySQL 5.6 版本支持了多個 BP 拆分，5.7 版本支持多個臟頁列表，因此對于上述 DROP TABLE 的處理，又得到進一步優(yōu)化，即持有大鎖的時間越來越少，對業(yè)務的影響也就越來越少。

（一）IO優(yōu)化

上面的這些優(yōu)化是針對內(nèi)存中的鎖進行優(yōu)化，在 DROP TABLE 的過程中，讓持有的大鎖盡可能的時間縮短，從而避免 QPS 掉底的問題。

然而，想要對業(yè)務操作的耗時響應時間控制在 20%，還需要考慮物理 I/O 的影響。

例如，刪除一張 100G 的表，意味著要刪除 100G 的物理文件，在刪除過程中，I/O 的開銷也將會是非常巨大，從而影響業(yè)務的耗時。

常見優(yōu)化的思想是在 DROP TABLE 前，對 ibd 文件創(chuàng)建硬鏈接，這樣在 DROP TABLE 時，只會刪除 ibd 文件，不會真正刪除 ibd 文件，也就不會釋放空間。

這樣 DROP TABLE 的操作速度就能得到進一步提升。不過，這時存儲空間并沒有第一時間釋放，還是需要選擇低峰期刪除文件，方能最終釋放存儲空間。

但同樣的問題，雖然異步刪除物理文件，但又如何做到最業(yè)務的耗時影響最小呢？這時，可以考慮使用 ionice 這個工具，選擇在業(yè)務低峰期進行 ibd 文件的刪除，如：bash復制代碼ionice -c 2 -n 6 rm xxx.ibd

三、自適應哈希功能

自適應哈希（Adaptive Hash Index，下簡稱AHI）特性作為一個幾乎透明的功能，其實一般用戶無需關心，基本可以認為 AHI 是即開即用的功能。

默認 AHI 參數(shù)的設置也是比較合理的，例如參數(shù) innodb_adaptive_hash_index_parts 設置為 8 。然而，AHI 存在一個副作用：當刪除大表，且緩沖池（Buffer Pool，下簡稱 BP）比較大，如超過 32G，則 MySQL 數(shù)據(jù)庫可能會有短暫被 hang 住的情況發(fā)生。

這時會對業(yè)務線程造成一定影響，從而導致業(yè)務系統(tǒng)的抖動。

產(chǎn)生這個問題的原因是在刪除表的時候，InnoDB 存儲引擎會將該表在 BP 中的內(nèi)存都淘汰掉，釋放可用空間。這其中包括數(shù)據(jù)頁、索引頁、自適應哈希頁等。

當 BP 比較大時，掃描 BP 中 flush_list 鏈表需要比較長的時間，因此會產(chǎn)生系統(tǒng)的抖動。

所以在海量的互聯(lián)網(wǎng)并發(fā)業(yè)務中，刪除表操作需要做精細的邏輯控制，如：

• 業(yè)務低峰期刪除大表；

• 刪除表前禁用 AHI 功能；

• 控制臟頁鏈表長度，只有長度小于一定閾值，才發(fā)起刪除操作；

• 刪除表后啟用 AHI 功能。

不過，這么麻煩的處理在 MySQL 8.0.23 版本之后，就都不再需要了。因為官方已經(jīng)徹底修復了這個問題。

四、終極刪除優(yōu)化

除了前面的這些對于版本的選擇、I/O 的優(yōu)化、自適應哈希算法的調(diào)整，還可以有進一步減小刪除大表時發(fā)生抖動的可能性。

當前 MySQL 數(shù)據(jù)庫對于刪除時內(nèi)存的控制已經(jīng)做的很好了，接下去的問題在于還需要進行 I/O操作，從磁盤上刪除操作。ionice 等命令依然有 I/O 開銷，性能抖動依然不可避免。

如果根據(jù)第一性原理，那是不是可以實現(xiàn)不刪除表文件，也能釋放存儲空間呢？為什么要這么辛苦地去釋放空間？只需要把原來的物理存儲空間給新表使用不就好了嗎？

更推薦在初始化時，就完成 ibdata1 的空間分配，即固定單個 MySQL 實例能使用到存儲空間上限。這樣可以省去后續(xù)磁盤空間的申請釋放，進一步提升在業(yè)務高峰期，數(shù)據(jù)庫耗時的平穩(wěn)性。

innodb_file_per_table = 0

innodb_data_file_path=ibdata1:500G

大家可能覺得這樣不夠靈活，因為用戶可能只想日志表、流水表等數(shù)據(jù)放在一個表空間中，這樣 DROP TABLE 時不需要刪除物理空間。

這樣的話，可以使用 MySQL 中的通用表空間（General Tablespace）：

CREATE TABLESPACE `ts_log` ADD DATAFILE 'ts_log.ibd'


CREATE TABLE log_2020_01 ( ... ) tablespace = ts_log;


CREATE TABLE log_2020_02 ( ... ) tablespace = ts_log;

作者：我愛娃哈哈
鏈接：https://juejin.cn/post/7380179109290688550
來源：稀土掘金
著作權歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權，非商業(yè)轉(zhuǎn)載請注明出處。