在 MySQL 上刪除時鎖定表大小問題

我正在嘗試根據某些日期從表中刪除錯誤記錄並遇到以下錯誤：

mysql> delete from myTable where dataDate in ('2011-07-12');
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

該表還沒有索引（這是載入後清理），並且包含 ~2B 記錄：

mysql> select table_rows from information_schema.tables where table_name = 'myTable';
+------------+
| table_rows |
+------------+
| 2073294315 |
+------------+
1 row in set (0.51 sec)

我通過搜尋找到的建議建議增加innodb_buffer_pool_size，但我已經將預設池大小增加了 8 倍，以便在合理的時間內載入數據，目前大小為 512MB：

mysql> select @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                1073741824 |
+---------------------------+
1 row in set (0.00 sec)

我計劃在刪除錯誤記錄後重新載入 3 個日期（~3M 記錄）的數據。想知道我是否應該首先索引表，或者是否有其他調整可以允許這樣做。

示意圖（草稿程式碼！）：

CREATE PROCEDURE loop_until_done()
BEGIN
DECLARE records_count_before BIGINT;
DECLARE records_count_after BIGINT;
SELECT COUNT(*) FROM myTable INTO records_count_after;
cycle: LOOP
   SET records_count_before = records_count_after;
   DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
   /* SELECT SLEEP(1) INTO records_count_after; */
   SELECT COUNT(*) FROM myTable INTO records_count_after;
   IF records_count_before <= records_count_after THEN
       LEAVE cycle;
   END IF;
END LOOP;
END

將 SELECT 添加到某個服務表中以獲取某個停止操作的標誌值是安全的。例如，這可以是一個延遲值（SLEEP 參數）——它的變化可能會改變刪除速度和在此期間伺服器載入，並且高於某個預定義值（或可能為零）的值是一個離開週期並無條件停止刪除操作的信號。

---

根據danblack的建議簡化程式碼：

CREATE PROCEDURE loop_until_done()
BEGIN
REPEAT
   /* SELECT SLEEP(1) INTO @dummy; */
   DELETE FROM myTable WHERE dataDate IN ('2011-07-12') LIMIT 10000;
UNTIL NOT ROW_COUNT() 
END REPEAT;
END

• 基於 8G 可用，設置innodb_buffer_pool_size = 6G.
• 如果您使用的是 MyISAM，請切換到 InnoDB。
• 添加索引。在填充表格後添加它通常會更快。（不過，除非你有 8.0，否則這將需要很長時間。）
• 有關批量刪除的多種技術，請參閱此：http: //mysql.rjweb.org/doc.php/deletebig
• 如此描述測試： dataDate = '2011-07-12'
• 如果dataDate是DATETIME，那麼你需要一個範圍測試。
• SELECT SLEEP(1);不需要INTO子句。

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