sql - 从优化中删除

Question

我有这样的疑问:

DELETE FROM f      WHERE ft != 'f'         OR fs NOT IN (  SELECT fs                           FROM f                       GROUP BY fs                         HAVING COUNT(fs) >1)

它的工作做得很好，只是花费的时间比我预期的要多得多。我说的是 2.25 秒将约 209,000 条记录减少到约 187,000 条。我认为它可以改进，我想知道如何改进。

查询说明:

addr  opcode         p1    p2    p3    p4                 p5  comment      ----  -------------  ----  ----  ----  -----------------  --  -------------0     Trace          0     0     0                        00               1     Goto           0     82    0                        00               2     Null           0     1     0                        00               3     String8        0     3     0     f                  00               4     OpenRead       0     2     0     4                  00               5     Rewind         0     69    0                        00               6     Column         0     2     4                        00               7     Ne             3     66    4     collseq(BINARY)    63               8     If             7     53    0                        00               9     Integer        1     7     0                        00               10    Null           0     6     0                        00               11    OpenEphemeral  4     1     0     keyinfo(1,BINARY)  00               12    OpenEphemeral  5     2     0     keyinfo(1,BINARY)  00               13    Integer        0     11    0                        00               14    Integer        0     10    0                        00               15    Gosub          13    50    0                        00               16    OpenRead       2     2     0     4                  00               17    Rewind         2     23    0                        00               18    Column         2     3     16                       00               19    Sequence       5     17    0                        00               20    MakeRecord     16    2     4                        00               21    IdxInsert      5     4     0                        00               22    Next           2     18    0                        01               23    Close          2     0     0                        00               24    Sort           5     53    0                        00               25    Column         5     0     15                       00               26    Compare        14    15    1     keyinfo(1,BINARY)  00               27    Jump           28    32    28                       00               28    Move           15    14    1                        00               29    Gosub          12    41    0                        00               30    IfPos          11    53    0                        00               31    Gosub          13    50    0                        00               32    Column         5     0     16                       00               33    AggStep        0     16    9     count(1)           01               34    Column         5     0     8                        00               35    Integer        1     10    0                        00               36    Next           5     25    0                        00               37    Gosub          12    41    0                        00               38    Goto           0     53    0                        00               39    Integer        1     11    0                        00               40    Return         12    0     0                        00               41    IfPos          10    43    0                        00               42    Return         12    0     0                        00               43    AggFinal       9     1     0     count(1)           00               44    Integer        1     4     0                        00               45    Le             4     42    9                        6a               46    SCopy          8     18    0                        00               47    MakeRecord     18    1     4     c                  00               48    IdxInsert      4     4     0                        00               49    Return         12    0     0                        00               50    Null           0     8     0                        00               51    Null           0     9     0                        00               52    Return         13    0     0                        00               53    Column         0     3     4                        00               54    NotNull        4     57    0                        00               55    Rewind         4     66    0                        00               56    Goto           0     68    0                        00               57    Affinity       4     1     0     c                  00               58    Found          4     65    4     1                  00               59    NotNull        6     63    0                        00               60    Found          4     62    6     1                  00               61    Integer        -1    6     0                        00               62    AddImm         6     1     0                        00               63    If             6     68    0                        00               64    Goto           0     66    0                        00               65    Goto           0     68    0                        00               66    Rowid          0     2     0                        00               67    RowSetAdd      1     2     0                        00               68    Next           0     6     0                        01               69    Close          0     0     0                        00               70    OpenWrite      0     2     0     4                  00               71    OpenWrite      1     3     0     keyinfo(1,BINARY)  00               72    RowSetRead     1     79    2                        00               73    NotExists      0     78    2                        00               74    Rowid          0     20    0                        00               75    Column         0     1     19                       00               76    IdxDelete      1     19    2                        00               77    Delete         0     1     0     f                  00               78    Goto           0     72    0                        00               79    Close          1     3     0                        00               80    Close          0     0     0                        00               81    Halt           0     0     0                        00               82    Transaction    0     1     0                        00               83    VerifyCookie   0     2     0                        00               84    TableLock      0     2     1     f                  00               85    Goto           0     2     0                        00               

表定义(还没有索引):

CREATE TABLE f (fi INTEGER PRIMARY KEY AUTOINCREMENT,                fn STRING,                ft STRING,                fs INTEGER)

Answer 1

我不确定两秒是一个“ killer 级”查询(当然，这取决于您的情况和需求)，但是您可以测试的一件事是拆分查询的效果查询分为两个。

这是因为它当前将删除与任一条件匹配的记录，因此可以轻松转换为两个 delete 语句(如果您想确保它是原子的，则在事务内)。您可以尝试:

DELETE FROM f WHERE ft != 'f';
DELETE FROM f WHERE fs NOT IN (
    SELECT fs FROM f
    GROUP BY fs
    HAVING COUNT(fs) >1);

看看这是否会改善情况。可能会也可能不会，具体取决于您的 DBMS 和数据的构成。它可能会在最有可能更快的第一个查询中删除交叉记录(满足两个条件的记录)。

但是，与所有数据库优化一样，要衡量，不要猜测!

然后，您可以重新组合并重新评估，或者如果问题仍然存在，则集中精力加速第二个更简单的查询。

有一点要特别确保:在ft 和fs 列上都有索引。如果您还没有查询，这应该会大大加快您的查询速度。