MariaDB 大批量删除

问题

怎样高效地从大表中删除大量行？例如，清理超过 30 天的数据：

DELETE FROM tbl WHERE
  ts < CURRENT_DATE() - INTERVAL 30 DAY

如果表中有数百万行，这条语句可能执行几分钟，甚至几小时。如何加快它？

为什么会成为问题

• MyISAM 会在整个操作期间锁住表，其他操作无法访问该表。
• InnoDB 不会以同样方式锁住整张表，但会消耗大量资源，使系统变慢。
• 删除所需的 undo 信息会显著增加 I/O。
• 对于异步复制，长时间运行的 DELETE 会让副本应用进度延迟。

InnoDB 与 undo

InnoDB 这样的事务引擎需要记录操作，以便处理崩溃恢复。顺序写入日志可以降低开销。原文把大规模删除带来的日志压力描述为：日志文件（原文称通常有两个）填满后，undo 信息会溢出到实际数据块，引起更多 I/O。

分批删除可以避免部分额外开销。作者有限的基准测试发现：批次大小超过某个阈值后，总删除时间约为阈值以下时的两倍，但作者没有给出日志文件大小与该阈值之间的计算公式；批次小于几百行也会更慢，可能因为每批开始与结束的固定开销占据了主要时间。

有两种方案：使用分区，需要谨慎设计，但非常适合按时间清理数据；或者每次处理 N 行，小批量遍历整张表。

分区

这里采用滑动窗口式分区。假设新闻文章保留 30 天，可以使用用于清理的 datetime 或 timestamp 列作为分区键，采用 RANGE 分区。

每天夜间由 cron 创建下一天的新分区，并丢弃最旧的分区。丢弃分区通常很快，远快于逐行删除同样数量的数据。但表设计必须允许整块分区一起丢弃，不能让同一分区内的某些条目比其他条目保留更久。

分区表有许多限制。可以不设置 UNIQUE 或 PRIMARY KEY；如果设置，每个唯一键都必须包含分区键。本例中分区键是时间列，作者建议它不要放在主键的最前面。

InnoDB 和 MyISAM 表都能使用分区。两篇新闻可能具有相同时间戳，因此不能认为时间分区键本身足以保证主键唯一，仍需其他列协助。分区维护的参考资料见 MariaDB 分区文档。

分批删除

以下讨论也适用于其他分批处理，例如 UPDATE，或者 SELECT 后进行复杂处理；MyISAM 和 InnoDB 都适用。

关键是避免每批都全表扫描。下面的方式把单次查询访问范围限制在约 1001 行内，1000 这个批量大小可以调整。假设待清理的新闻表结构类似下面的示意片段：

CREATE TABLE tbl
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      ts TIMESTAMP,
      ...
      PRIMARY KEY(id)

删除超过 30 天的数据，可以使用以下伪代码：

@a = 0
   LOOP
      DELETE FROM tbl
         WHERE id BETWEEN @a AND @a+999
           AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      SET @a = @a + 1000
      sleep 1  -- be a nice guy
   UNTIL end of table

这里有几点需要注意，后文会处理其中多数限制：

• 使用主键而不是二级索引，能改善磁盘访问的局部性，对 InnoDB 尤其如此。
• 可以设法避免遍历最近几天的数据却没有任何可删除行，但判断逻辑本身也可能很昂贵。
• 调整 1000 这个批量大小，让 DELETE 通常在例如一秒以内结束。
• 不要求在 ts 上建立索引，这也会稍微减轻 INSERT 的负担。
• 复合主键会让代码更复杂。
• 这一版本要求数值型主键或唯一键。

首次清理后，id 往往出现较大间隔，此时可改用：

@a = SELECT MIN(id) FROM tbl
   LOOP
      SELECT @z := id FROM tbl WHERE id >= @a ORDER BY id LIMIT 1000,1
      IF @z IS NULL
         EXIT LOOP  -- last chunk
      DELETE FROM tbl
         WHERE id >= @a
           AND id <  @z
           AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      SET @a = @z
      sleep 1  -- be a nice guy, especially in replication
   ENDLOOP
   # Last chunk:
   DELETE FROM tbl
      WHERE id >= @a
        AND ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

这种方式适用于数值或字符 id；id 不唯一时多数情况下也能工作，但当 @z==@a 时可能陷入循环。可以检测这一情况，并寻找严格大于当前 id 的下一个值：

...
      SELECT @z := id FROM tbl WHERE id >= @a ORDER BY id LIMIT 1000,1
      IF @z == @a
         SELECT @z := id FROM tbl WHERE id > @a ORDER BY id LIMIT 1
   ...

缺点是，同一个 id 可能对应超过 1000 条记录，从而突破原计划的批量大小。作者认为，在很多实际场景中这种情况不常见。

如果没有主键或唯一键，但 ts 上存在索引，可以考虑下面的伪代码：

LOOP
      DELETE FROM tbl
         WHERE ts < DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
         ORDER BY ts   -- to use the index, and to make it deterministic
         LIMIT 1000
   UNTIL no rows deleted

作者不推荐这种技巧，因为 LIMIT 在复制中会引发非确定性警告，后文将继续讨论。

InnoDB 分批处理建议

• 为 innodb_log_file_size 选择合理大小。
• 删除会话使用 AUTOCOMMIT=1。
• 从每批约 1000 行开始。
• 如果基于语句的异步复制导致副本延迟过大，或者操作长时间占用表，就减小批量。

遍历复合键

分批删除需要沿着主键向前移动。主键包含多列时，这一步会更加复杂。假设已经处理到 ($g, $s) 这一行，可以使用下面的复合“大于”条件：

INDEX(Genus, species)
   SELECT/DELETE ...
      WHERE Genus >= '$g' AND ( species  > '$s' OR Genus > '$g' )
      ORDER BY Genus, species
      LIMIT ...

原文补充：上面的 AND/OR 形式适合较老的 MySQL，而 MariaDB 和较新的 MySQL 可使用另一种形式。以下保留原文片段：

WHERE ( Genus = '$g' AND species  > '$s' ) OR Genus > '$g' )

用会话变量表示字符串时还要注意字符集和排序规则。如果把 '$g' 换成 @g，必须确保 @g 与 Genus 的 CHARACTER SET、COLLATION 相同，否则运行时转换可能妨碍索引使用。索引对性能至关重要，因此可能需要在 SET NAMES 或 SELECT 中的 @g 上明确指定 COLLATE。

回收磁盘空间

这项操作代价很高；可行时可以考虑改用分区方案。

MyISAM 删除后会在 .MYD 文件里留下空隙。OPTIMIZE TABLE 可以回收大批量删除释放的空间，但可能耗时很久并锁表。

InnoDB 按主键组织为 B 树，使用块结构。单独删除一行后，对应块的填充率下降；大量删除可能让相邻块合并。块通常为 16KB，参见 innodb_page_size。

对于 ibdata1，通常只能等待以后复用空闲块，难以直接把空间归还操作系统。原文指出，在 innodb_file_per_table=0 的情况下，要缩小共享表空间，需要导出所有表、移除 ibdata 文件、重启并重新导入，通常不值得投入这样的时间和工作量。

即使 innodb_file_per_table=1，普通 DELETE 也不会直接把文件空间交还操作系统，不过至少可以只重建一个表，例如：

CREATE TABLE new LIKE main;
   INSERT INTO new SELECT * FROM main;  -- This could take a long time
   RENAME TABLE main TO old, new TO main;   -- Atomic swap
   DROP TABLE old;   -- Space freed up here

必须有足够空间同时保存新旧两份数据，并且整个过程中不能写入该表。

删除超过半张表的数据

下面的技巧可用于高效删除大部分数据、增加分区、转换为 innodb_file_per_table=ON 或整理碎片，也可以组合使用这些目标。可以分批复制，条件允许时也可以一次完成：

-- Optional:  SET GLOBAL innodb_file_per_table = ON;
   CREATE TABLE New LIKE Main;
   -- Optional:  ALTER TABLE New ADD PARTITION BY RANGE ...;
   -- Do this INSERT..SELECT all at once, or with chunking:
   INSERT INTO New
      SELECT * FROM Main
         WHERE ...;  -- just the rows you want to keep
   RENAME TABLE main TO Old, New TO Main;
   DROP TABLE Old;   -- Space freed up here

同样需要足够空间容纳两份表。过程中必须停止对原表的写入，否则 Main 中的变更未必反映到 New 中。

非确定性复制

带 LIMIT 的 UPDATE、DELETE 等操作经基于语句的复制发送到副本时，可能造成主副本不一致。因为副本发现待修改行的顺序可能不同，最终修改的行集也可能不同。

应增加 ORDER BY，并确保排序具有确定性，即排序列或表达式组合能够唯一确定一行。例如，每个 date 存在多行时，以下原文示意不足以保证顺序：

DELETE * FROM tbl ORDER BY date LIMIT 111

如果 id 是主键或唯一键，原文用下面的示意说明需要补上的唯一排序项：

DELETE * FROM tbl ORDER BY date, id LIMIT 111

原文还指出，MySQL 某些情况下即使已有 ORDER BY，也可能在 mysqld.err 中产生误报警告 Statement is not safe to log in statement format.。前述部分代码通过把定位与删除分开，避免这种警告：

SELECT @z := ... LIMIT 1000,1;  -- not replicated
   DELETE ... BETWEEN @a AND @z;   -- deterministic

作者指出，这对语句把处理限制在一个批次内，而不是扫描整张表。

复制与 KILL

如果在主库执行 DELETE 的过程中将它 KILL，复制会怎样处理？

对于 InnoDB，查询应当回滚；原文对此保留了“是否有例外”的疑问。MyISAM 则边执行边删除，没有回滚机制，可能一部分行已删除、一部分尚未删除，也难以知道究竟执行到哪里。

单机情况下可以重新执行删除。在复制环境中，这条语句会带错误 1317 写入 binlog；复制无法据此保证主副本一致，就会停止并等待人工干预。

对于依赖复制的高可用系统，这是严重的运行问题。原文要求先到各副本确认确实因此停住，再给出如下恢复命令：

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
   START SLAVE;

然后重新执行 DELETE，原文推测这能完成被中断的任务。作者把这也列为将表从 MyISAM 迁移到 InnoDB的理由之一。

SBR、RBR 与 Galera

原文此节尚待补充，并指出基于行的复制可能改变前面的讨论。

后记与来源

作者说明，文中的技巧适用于 MySQL、MariaDB 和 Percona。

原文：MariaDB：Big DELETEs，作者 Rick James。MariaDB 经作者许可收录；原始出处：deletebig。作者网站还有其他技巧、操作指南、优化与调试资料。

原页面标明许可为 CC BY-SA / Gnu FDL，没有注明版本。本译稿保留署名和原许可声明，代码与伪代码按来源保留，未声称它们可直接运行或已通过执行验证。