什么是从MySQL数据库中删除数百万行的最快方法？

Question

我的数据库空间不足，所以我对旧记录进行了备份。现在我需要清除这些记录，我的参考列是“日期”。

我尝试使用标准方法：

DELETE FROM table WHERE date >= '2017-01-01' AND date <= '2017-12-31'

但是显然这需要花费太长时间，因为要删除的行数超过了700万。有没有一种方法可以加快速度？我试图按月份和更小的块进行划分，但在运行一段时间后，我会与服务器断开连接。

提前致谢。

编辑：

CREATE TABLE table (

  id INT(11) NOT NULL AUTO_INCREMENT,
  date DATE DEFAULT NULL,
  # 18 more columns

  PRIMARY KEY (id)
)
ENGINE = INNODB,
AUTO_INCREMENT = 29616055,
AVG_ROW_LENGTH = 317,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci;

- Vinícius Alcântara

---

你尝试过哪些调试方法来解决这个问题？是否有设置索引？ - Nico Haase

这个表的引擎是InnoDB还是MyISAM？（或者为了回答我的和Nico的问题，请在你的问题中添加表的CREATE语句）。 - Uueerdo

你的日期字段可能没有被索引。 - Ibu

请添加MySql或Sql标签 - Alex

ALTER TABLE table ADD INDEX date_index (date);你的列 date 是什么类型？ - Alex

Answer 1

如果有足够的空间，那么创建一个临时表并重新加载原始表：

create table temp_t as
    select *
    from table
    where date >= '2018-01-01';

truncate table t;

insert into t
     select *
     from temp_t;

这样做可以避免delete的所有日志开销，而这可能非常昂贵。

接下来，您需要了解分区。这将使整个过程变得更加简单。您只需删除一个分区，而不是逐行删除数据 - 删除分区时没有逐行记录日志。

- Gordon Linoff

---

太好了，它像魔法一样奏效。我已经在大约11亿行数据上测试了这个答案，它的表现符合预期。感谢分享这个答案。 - Mayur Patel

这是一个很好的替代删除的方法，我想知道如何让“创建表”更快。 - Joel

@joel . . . 减少日志记录和最小化锁定的影响。 - Gordon Linoff

Answer 2

1. 创建一个空的表副本（包括所有键）。
2. 从原始表中选择要保留的数据进行INSERT...SELECT操作，插入到副本中。
3. 删除引用原始表的任何外键约束。
4. 删除原始表。
5. 将副本重命名为原始表的名称。
6. 重新创建在步骤3中删除的任何外键。

- Uueerdo