SQLite:使用 LIMIT 进行大更新，是否值得删除索引

Question

我需要更新一张表中的大量记录(大约 300k 到 500k)并删除另一张表中的几百万条记录。由于这需要一些时间，我正在使用 LIMIT 以 block 方式执行此操作，以便向用户显示进度条。

我的第一个小问题是，为什么我可以在 SQLiteSpy 中使用以下语句，但在使用 ADO.NET 提供程序 System.Data.SQLite 时却不能？

UPDATE Table1
SET Status1 = newValue1, Status2 = value2
WHERE Key1 = key1Value
AND Status1 = value1
LIMIT 1000

我必须使用以下语句才能使其工作:

UPDATE Table1
SET Status1 = newValue1, Status2 = value2
WHERE Key1 = key1Value
AND Key2 in (
  SELECT Key2
  FROM Table
  WHERE Key1 = key1Value
  AND Status1 = value1
  LIMIT 1000)

我正在使用最新版本的 SQLiteSpy(使用 SQLite 3.7.2)和 System.Data.SQlite。

我的另一个问题更复杂。
我正在使用 2 个表:

CREATE TABLE Table1 (
Key1 INTEGER NOT NULL,
Key2 INTEGER NOT NULL,
...
Some fixed varchar data fields
...
Status1 CHAR(1) NOT NULL,
Status2 VARCHAR NULL,
Status3 CHAR(1) NOT NULL,
UpdateDate DATETIME NOT NULL,
CONSTRAINT PK_Table1 PRIMARY KEY (Key1 ASC, Key2 ASC))

和

CREATE TABLE Table2 (
Key1 INTEGER NOT NULL,
Key2 INTEGER NOT NULL,
Key3 INTEGER NOT NULL,
...
Some fixed varchar data fields
...
CONSTRAINT PK_Table2 PRIMARY KEY (Key1 ASC, Key2 ASC, Key3 ASC))

在 table1 上有两个索引:

CREATE INDEX IDX_Tabel1_Status1 ON Table1 (Key1 ASC, Status1 ASC, Key2 ASC)
CREATE INDEX IDX_Tabel1_Status2 ON Table1 (Key1 ASC, Status2 ASC, Key2 ASC)

正如您可能已经猜到的那样，两个表中的 Key1 和 Key2 都很受欢迎。

我想要做的是，在 Table2 中删除 Table1 中具有特定状态的记录的所有记录，并将 3 个状态字段重置为其原始值并更新 Table1 中的日期。由于涉及的记录数可能很大(Table1 最多包含 500k 条记录，Table2 包含 20M 到 40M 之间)，并且大多数情况下它涉及 table1 的 50% 到 100% 之间，我以“小”执行删除和更新 block (表 1 中介于 1000 到 10000 条记录之间)。所以我重复以下两条语句，直到所有相关记录都被删除/更新(每个事务一个删除和更新):

DELETE FROM Table2
WHERE Key1 = @Key1
AND Key2 in (
  SELECT Key2
  FROM Table
  WHERE Key1 = @Key1
  AND Status1 = @Status1
  LIMIT 1000)

UPDATE Table1
SET Status1 = @NewStatus1, Status2 = @Status2, Status3 = @Status3, UpdateDate = @Date
WHERE Key1 = @Key1
AND Key2 in (
  SELECT Key2
  FROM Table
  WHERE Key1 = @Key1
  AND Status1 = @Status1
  LIMIT 1000)

删除速度非常快，但更新需要很长时间(1000 条记录大约需要 2 到 3 秒)。我想这是因为这两个索引都需要更新。所以我想知道在删除/更新之前删除两个索引并在之后重新创建它们是否会提高性能。但是随后子选择会变得更慢。在什么时候(涉及的总记录的百分比，或记录的绝对数量)，我应该考虑删除索引？

谢谢，
马克

Answer 1

At what point (percentage of the total records involved, or absolute number of records), should i consider dropping the indexes ?

我不能说，但是对于如此大量的数据，删除索引并在事务完成后重新创建它们大多会产生好处。

获得好的答案的唯一方法是根据您的场景来衡量性能。

恕我直言，您的主要问题是向用户显示进度的限制子句。我不会那样做。如果排除在外，它可能会产生可观的绩效奖金。此外，如果我是用户，我宁愿尽快完成工作，也不愿知道进度。
您可以使用选取框来显示正在进行的操作。

您还可以测量每条记录的平均时间(或取决于数据量的公式)并显示有关进度的有根据的猜测。每次运行都进行测量，将其保存在某个地方，并从最后 10 次运行中建立平均值来计算进度。

或者，您可以将逻辑分成不同的步骤，并显示当前正在执行的查询的步骤编号。

如果您确实需要显示进度，请尝试使用 and exists 语句而不是 in 子句。它可能会更快。