删除..。删除半个大表后的挂起(即使是删除10行)

Question

即使对于大型MariaDB InnoDB表中的少量行(10)，删除挂起也有问题。

背景

我有一个大表(约400,000,000行)，需要对行进行归档和删除。删除大约一半行后，我的归档进程开始挂在DELETE语句上。我花了时间运行(非常慢的)11小时优化(ALTER )查询，以回收磁盘空间，并希望解除对删除过程的阻碍。直到我删除了大约一半的剩馀行(减少到2000万行中的106米)，这才起作用。我想我可以在我的巨型表上继续运行优化，让我再删除1/2行，但是肯定有更好的方法.

注意，该表确实获得了大量的INSERT/UPDATE通信量，每分钟有1000 s的更改。

存档过程

为了简化归档脚本，我将整个过程放入一个存储过程中。这一程序的步骤是：

1. 将活DB中的N行扁平化并复制到存档DB中，其中的行已超过1个月。
2. 列出可能位于活动数据库中的归档ID。将这些存储在临时表A中
3. 将临时表A筛选为只包含实际位于活动DB中的行in。将这些存储在临时表B中
4. 从live_db.test_table中删除test_id IN (从temp_table_B中选择*)顺序由test_id ASC限制N；

既然表是半空的，第4步是挂起的，即使在表半空之前N= 10，它工作得很好，N= 1000花了~1秒。

诊断学

在存储过程中添加一个解释让我知道MySQL正计划使用删除的主索引，正如我所希望的那样：

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

从显示引擎INNODB状态中选择的输出(某些内容已修改)：

---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

Answer 1

我发现这里发生了什么

由于我是如何在步骤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亿行中的每一行进行了比较，这使得无所事事的删除只需25分钟。

现在我已经修改了我的进程，以便在调用DELETE之前检测空表并中止，我可以手动监视导致我的行到删除列表偶尔变为空的(特定于数据的)条件。

编辑:即使在修复了空列表问题之后，我的查询也大大加快了速度，从“IN (SELECT * temp_table)”版本切换到了连接版本，类似于yper多维数据集中的注释。这种修改消除了执行计划中的“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