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

一、為什么要 DROP TABLE？

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

生產環境大多數是已經確定的庫表，一般不會進行 DROP TABLE 這么重的操作，甚至大部分DBA 都不應該擁有 DROP 權限。

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

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

銀行系統的 Oracle 數據庫，雖然單庫都可以數 T，但大部分都是日志數據或流水數據。

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

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

或許大部分數據庫系統都沒有這么高的要求，但是海量并發的互聯網業務有！

二、關于MySQL版本

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

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

MySQL 5.5.23 版本開始，做了兩點優化：

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

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

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

（一）IO優化

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

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

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

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

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

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

三、自適應哈希功能

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

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

這時會對業務線程造成一定影響，從而導致業務系統的抖動。

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

當 BP 比較大時，掃描 BP 中 flush_list 鏈表需要比較長的時間，因此會產生系統的抖動。

所以在海量的互聯網并發業務中，刪除表操作需要做精細的邏輯控制，如：

• 業務低峰期刪除大表；

• 刪除表前禁用 AHI 功能；

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

• 刪除表后啟用 AHI 功能。

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

四、終極刪除優化

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

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

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

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

innodb_file_per_table = 0

innodb_data_file_path=ibdata1:500G

大家可能覺得這樣不夠靈活，因為用戶可能只想日志表、流水表等數據放在一個表空間中，這樣 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
來源：稀土掘金
著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。