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

一、為什么要 DROP TABLE？

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

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

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

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

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

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

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

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

二、關(guān)于MySQL版本

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

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

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

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

• 不處理 LRU 列表中干凈的頁。因?yàn)?LRU 中不使用的頁會(huì)慢慢被淘汰，因此刪除表，不用同步地去處理對(duì)應(yīng)的頁，可以通過 LRU 機(jī)制，異步最終淘汰機(jī)制。

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

（一）IO優(yōu)化

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

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

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

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

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

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

三、自適應(yīng)哈希功能

自適應(yīng)哈希（Adaptive Hash Index，下簡(jiǎn)稱AHI）特性作為一個(gè)幾乎透明的功能，其實(shí)一般用戶無需關(guān)心，基本可以認(rèn)為 AHI 是即開即用的功能。

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

這時(shí)會(huì)對(duì)業(yè)務(wù)線程造成一定影響，從而導(dǎo)致業(yè)務(wù)系統(tǒng)的抖動(dòng)。

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

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

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

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

• 刪除表前禁用 AHI 功能；

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

• 刪除表后啟用 AHI 功能。

不過，這么麻煩的處理在 MySQL 8.0.23 版本之后，就都不再需要了。因?yàn)楣俜揭呀?jīng)徹底修復(fù)了這個(gè)問題。

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

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

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

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

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

innodb_file_per_table = 0

innodb_data_file_path=ibdata1:500G

大家可能覺得這樣不夠靈活，因?yàn)橛脩艨赡苤幌肴罩颈?、流水表等?shù)據(jù)放在一個(gè)表空間中，這樣 DROP TABLE 時(shí)不需要?jiǎng)h除物理空間。

這樣的話，可以使用 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
來源：稀土掘金
著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處。