mysql - 当发送DELETE查询时，innoDB是从数据库中逐条删除数据，还是先创建一个要删除的数据列表？

Question

我有一个包含约 140,000 行的数据库表，现在我正在执行如下查询

DELETE FROM database WHERE type="delete"

但这需要很长时间。我很想停止查询并查看数据库的大小是否实际上已缩小，但我不确定它是否正在创建要在数据实际删除之前删除的数据列表。 innoDB 在收到 DELETE 查询时实际上是如何从数据库中清除数据的？

这是 CREATE 查询(针对表 images):

CREATE TABLE IF NOT EXISTS `images` (
  `imageID` int(11) NOT NULL AUTO_INCREMENT,
  `runID_fk` int(11) DEFAULT NULL,
  `sequenceID_fk` int(11) DEFAULT NULL,
  `cameraID_fk` int(11) DEFAULT NULL,
  `data` longblob,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `name` text,
  `type` text,
  `pcadata` longblob,
  PRIMARY KEY (`imageID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

根据您的 CREATE 语句，您在 type 列上没有索引。因此，您的问题是由于 WHERE 子句缓慢造成的。基本上，您的查询会强制数据库服务器查看每一行，以查看 type 列中的值是否为“delete”。无论您有多少行，这都很慢。索引将使速度更快，因为服务器本质上只需请求所有匹配行的列表，然后仅对这些行进行操作。

作为一个非常广泛的经验法则，如果您打算在 WHERE 子句中使用列，则应对其建立索引。 (当然也有异常(exception)，但根据您发布的内容，这不是其中之一。)

您可以通过运行 EXPLAIN 查询来查看数据库服务器正在执行的操作，如

EXPLAIN DELETE FROM database WHERE type="delete"

<小时/>

根据您的评论，您的 EXPLAIN 返回如下内容:

id   select_type   table    partitions   type   possible_keys   key     key_len   ref    rows     filtered  Extra
1    SIMPLE        images                ALL    null            null    null      null   145669             Using where

您看到的 145,699 不是(正如您在评论中所写的)“行的大小”。这是服务器执行查询时必须查看的行数。正如您所看到的，它没有任何可以使用的索引。因此，它必须查看 145,669 行，而不是只查看要删除的行。

此外，由于您的 type 列是 TEXT 列，因此速度会更慢。如果您打算标记，您应该认真考虑添加 TINYINT、CHAR(不是 TEXT)或其他列来存储行的状态以这种方式删除行。

更一般地说，您确实应该重新考虑其中一些列类型。例如，我严重怀疑名为 name 的列是否需要 2^16 - 1 字节。