MYSQL 5.7 根據另一個相關表的分區鍵對錶進行分區

在了解問題之前，請先了解一點背景。

TABLE transaction (

t_id: AUTO INCREMENT BIGINT

t_execute_on_date: DATE,

timestamp: DATETIME,

... other columns
)

該表transaction有 300M（三億）行並且還在增長（每天 1M 行）並且還在增長，我們需要根據該列刪除舊數據 t_execute_on_date。我計劃在基於 KEY 創建每月範圍分區的基礎上刪除舊數據（超過 2 年。大約 40M 行）t_execute_on_date。我已經在測試伺服器上使用@RickJames 部落格文章MySQL 中的分區維護中建議的過程進行了測試：創建一個新的分區表，根據查詢優化索引，然後重新插入記錄，並刪除舊分區，這種方法似乎很好對我來說。也歡迎對這種方法提出任何建議。

我已經在 master-master 複製中做到了這一點，其中一個 master 離線，重新組織數據，恢復複製滯後，然後刪除舊 master，將流量帶到新 master，如果一切正常的話。

TABLE transaction_history (

th_id: AUTO INCREMENT BIGINT,

t_id: FOREIGN KEY (t_id of TABLE transaction)

timestamp: DATETIME,

... other columns
)

該表有 1.5B+（15 億）行，每天增長 15M+。該表具有相關的事務歷史記錄，並包含對應於一個的多行t_id。

一旦刪除了表中的舊事務數據transaction，我也想從該表中刪除相同的數據transaction_history。

該表沒有任何t_execute_on_date我可以根據它進行分區的鍵。該表有 1.5B+ 行我認為添加額外的列t_execute_on_date是不可能的。

像上面討論的方法一樣，我是否必鬚transaction_history根據t_idTABLE 事務手動從表中提取數據（重組，刪除舊數據）並僅將選定的記錄重新插入新表中transaction_history_partitioned？

我面臨的問題是如何從表中清除舊數據transaction_history?

使用 MYSQL 5.7 執行

$$ 2.8T HDD, Amazon Linux AMI 2017.03, 16 core, 63GB RAM $$

注意： FOREIGN KEYS在分區表中是不允許的。只需刪除 FK，但保留它們生成的索引。假設您已經調試了應用程序，FK 檢查是多餘的。

添加一個額外的列 t_execute_on_date 是不可能的。

見pt-online-schema-change或gh-ost。

使用PARTITIONs是從歷史表中刪除“舊”數據的最佳方法，但還有其他方法：http: //mysql.rjweb.org/doc.php/deletebig

想到的一個是有一個持續執行的作業，不斷查看“下 1000”行，看看是否應該刪除其中的任何一個。（它必須使用 . 遍歷表PRIMARY KEY。）它可能會LEFT JOIN查看哪些已消失，如以下虛擬碼所示：

$a = 0;
Loop...
   $z = SELECT th_id FROM transaction_history order by th_id LIMIT 1000,1;
   exit loop if $z IS NULL
   SELECT th_id
       FROM      transaction_history AS the
       LEFT JOIN transaction AS t  ON th.t_id = t.t_id
       WHERE t.t_id IS NULL
         AND th.th_id >= $a
         AND th.t_id   < $z
   DELETE those rows -- probably better to do this as a single query
$a = $z;

當您退出循環時，只需重新開始。

希望這種刪除“舊東西”的方法能夠足夠快地跟上，儘管會有一些滯後。

儘管如此，我還是希望pt-online-schema-change或者gh-ost可以將非分區表轉換為分區表，且停機時間最短，無論表大小如何。（沒有 FK。）

注意：分區表的最佳索引通常不同於等效的非分區表。添加分區時丟棄任何未使用的表；在建構具有二級索引的表時存在一些成本。

引用自：https://dba.stackexchange.com/questions/292203