在MySQL上删除时的锁表大小问题

Question

我试图根据特定日期从表中删除错误记录，并遇到以下错误：

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倍，以便在合理的时间内加载数据，而当前的内存为512 at：

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

我计划在删除错误记录后重新加载3个日期(~3M记录)的数据。想知道我是否应该先对表进行索引，或者是否还有其他允许这样做的调整。

Answer 1

示意性(草案代码！)：

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添加到某个服务表中，用于终止操作的某些标志值。例如，这可以是一个延迟值(睡眠参数)-它的变化可能会改变删除速度和服务器加载在其中，而高于某些预定义的值(或可能为零)是一个信号离开周期，并无条件停止删除操作。

根据丹黑S推荐的简化代码：

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

Answer 2

• 基于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子句。