DELETE … IN 刪除一半大表後掛起（即使刪除 10 行）

我在 DELETE 掛起時遇到問題，即使是大型 MariaDB InnoDB 表中的少量行 (10)。

背景

我有一張大表（約 400,000,000 行），需要存檔和刪除行。刪除大約一半的行後，我的存檔過程開始掛在 DELETE 語句上。我花時間執行（非常慢）11 小時 OPTIMIZE (ALTER for InnoDB) 查詢以回收磁碟空間，並希望解除對 DELETE 程序的阻塞。這一直有效，直到我刪除了大約一半的剩餘行（在 200m 行中減少到大約 106m）。我想我可以繼續在我的巨型桌子上執行 OPTIMIZE 讓我刪除另外 1/2 的行，但必須有更好的方法……

請注意，該表確實獲得了大量的 INSERT/UPDATE 流量，每分鐘 1000 次更改。

存檔過程

為了簡化歸檔腳本，我把整個過程放到了一個儲存過程中。該過程中的步驟是：

1. 展平並將最多 N 行從實時數據庫複製到存檔數據庫，其中行早於 1 個月
2. 列出可能在實時數據庫中的存檔 ID。將這些儲存在臨時表 A 中
3. 過濾臨時表 A 以僅包含實際在實時數據庫中的行 ID。將這些儲存在臨時表 B 中
4. DELETE FROM live_db.test_table WHERE test_id IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

現在桌子半空，第 4 步掛起，即使 N = 10 在桌子半空之前，它工作正常，N = 1000 花了 ~ 1 秒

診斷

在儲存過程中添加 EXPLAIN 讓我知道 MySQL 正計劃將 PRIMARY 索引用於 DELETE，正如我希望的那樣：

id select_type         table          type   possible_keys  key      key_len  ref   rows     Extra
1  PRIMARY             test_runs      index  NULL           PRIMARY  4        NULL  10       Using where
8  DEPENDENT SUBQUERY  tests_to_drop  ALL    NULL           NULL     NULL     NULL  1        Using where

從 SHOW ENGINE INNODB STATUS 中選擇的輸出（某些內容已編輯）：

---TRANSACTION 6457899980, ACTIVE 14 sec starting index read, thread declared inside InnoDB 3008
mysql tables in use 2, locked 2
242717 lock struct(s), heap size 25507368, 5532268 row lock(s)
MySQL thread id 1834147, OS thread handle 0x7f0f68c4a700, query id 204328569 1.2.3.4 user123 Sending data
DELETE FROM live_db.test_table
WHERE test_id IN (SELECT * FROM temp_table_B)
ORDER BY test_id ASC
LIMIT 10
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC

（來自評論）

CREATE  TEMPORARY TABLE temp_table_B (
       test_id int(10) unsigned NOT NULL DEFAULT '0'
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1

我發現這裡發生了什麼：

由於我在步驟 1 和 2 中選擇 test_id 值的方式，temp_table B 有時會為空。（有關步驟/表格上下文，請參閱問題描述）

然後，使用一個空的 temp_table_B，我正在執行第 4 步：

DELETE FROM live_db.test_table WHERE test_id
   IN (SELECT * FROM temp_table_B) ORDER BY test_id ASC LIMIT N;

這給了

DELETE FROM live_db.test_table WHERE test_id
   IN ( <empty> ) ORDER BY test_id ASC LIMIT N;

這會掛很長時間。

基於這個答案：https://dba.stackexchange.com/a/86103/144766，似乎空表可能與我的 1.06 億行中的每一行進行了比較，使得無操作 DELETE 需要 25 分鐘。

現在我已經修改了我的流程以檢測空表並在呼叫 DELETE 之前中止，我可以手動觀察導致我的要刪除的行列表偶爾變為空的（特定於數據的）條件。

編輯：即使在修復了空列表問題之後，通過從“IN (SELECT * temp_table)”版本切換到 JOIN 版本，我的查詢也大大加快了速度，類似於上面ypercube在評論中建議的內容。此修改從執行計劃中消除了“SUBQUERY”，查詢變為“簡單”查詢：

'IN' version:
id select_type         table           type  key     key_len rows Extra
1  PRIMARY             test_runs       index PRIMARY 4       10   Using where
8  DEPENDENT SUBQUERY  temp_table_B    ALL   NULL    NULL    10   Using where

'JOIN' version:
id  select_type  table        type   key     key_len ref                  rows Extra
1   SIMPLE       temp_table_B ALL    NULL    NULL    NULL                 10   Using where
1   SIMPLE       test_runs    eq_ref PRIMARY 4       temp_table_B.test_id 1 

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