SQLite：删除大量行的有效方法

Question

SQlite，Android，真实的故事。我有一个表，用作缓存：

CREATE TABLE cache(key TEXT, ts TIMESTAMP, size INTEGER, data BLOB);
CREATE UNIQUE INDEX by_key ON cache(key);
CREATE INDEX by_ts ON cache(ts);

在应用程序生命周期内，我会填充缓存，并在某些时候想清除缓存并删除N记录。通常，此表将包含〜25000个Blob，每个〜100-500Kb，DB中的总Blob大小为600-800Mb，但是现在我测试的是〜2000，约为60Mb（以下是这种情况）。清除将删除90％的缓存条目。

我尝试了不同的方法来进行此操作，此处简要说明：

[1] 最简单。首先选择一个游标，而不是一个一个地删除游标。太慢了。

[2] 使SQLite使用查询来执行此操作（删除其中总有N字节的blob ）：

DELETE FROM blobs WHERE
  ROWID IN (SELECT ROWID FROM blobs WHERE 
             (SELECT SUM(size) FROM blobs AS _ WHERE ts <= blobs.ts) <= N);

这更快，但仍然非常慢：〜15秒。似乎它也具有二次复杂度。

[3] 选择要删除的行（使用平均Blob大小进行计算）并使用简单WHERE子句删除：

-- Find row after which to delete, let it's time stamp is T0:
SELECT ts FROM cache ORDER BY ts LIMIT 1 OFFSET count;
-- Delete
DELETE FROM cache WHERE ts < T0;

这要好得多，但是大约需要7秒钟。

[4] 创建新表，复制我需要保存并删除的旧表。请注意，复制所有这些内容后，我将在新表中创建索引：

  -- Insert only rows I want leave
  INSERT INTO temp(key, ts, size, data) SELECT key, ts, size, data 
    FROM cache ORDER BY ts LIMIT count;
  -- Drop table and indices.
  DROP INDEX by_key;
  DROP INDEX by_ts;
  DROP TABLE cache;
  -- Rename temp table and create indices...

复制需要大约300ms的6Mb斑点。但是DROP TABLE大约是8秒。

请注意，在所有情况下我VACUUM都要花大约1秒钟的时间。我该如何快速？为什么DROP TABLE和删除都这么慢？我认为这可能是由于索引造成的：当我删除键索引之前，DELETE它无法更快地工作。如何使SQLite快速删除？

Answer 1

您正在使用具有“大”数据的数据库-即每个blob使用多个页面。

在接近最佳性能的某个时刻，您将达到无法提高的极限。

检查所有选择后，我看到了不同的行为，而不仅仅是不同的算法。

[1]只要您使用一项交易，这个速度就不会太慢。您需要一次执行两项操作，即查询（获取blob大小）和删除。

[2]这是一个好方法。由于两个查询和一个删除都在一个命令中，因此SQLite引擎将进行优化。

[3]这是与以往不同的行为。与相同DELETE FROM cache WHERE ts < (SELECT ts FROM cache ORDER BY ts LIMIT 1 OFFSET count)。查询比上一查询便宜，但是我敢打赌删除的行数比上一查询少得多！查询/删除的昂贵部分将被删除！查询优化很重要，但是删除总是会变慢。

[4]这是一个非常糟糕的方法！将所有数据复制到一个新表（可能是另一个数据库）中将非常昂贵。我只能从中得到一个好处：您可以将数据复制到新数据库中，避免使用VACUUM，因为新数据库是从基础构建的，而且很干净。

关于VACUUM…最糟的DELETE是VACUUM。不应在数据库中经常使用真空。我知道该算法应该“清理”您的数据库，但是清理不应是频繁的操作-
数据库已针对选择/插入/删除/更新进行了优化-不能将所有数据保持在最小大小。

DELETE ... IN (SELECT ...)根据预定义的标准，我的选择是使用单个操作。VACUUM不会被使用，至少不会经常使用。一个不错的选择是Monitor db size-
当该大小超出限制时，运行假定昂贵的清理操作以修剪数据库。

最后，当使用多个命令时，请不要忘记使用事务！