为SQLite3中的重复行优化删除查询? [英] Optimize deletion query for duplicate rows in SQLite3?

问题描述

我正在尝试删除表中具有最高用户ID的重复行之一，该行由350万行组成.我大约有1300列要删除，并且当前正在使用以下查询:

I'm trying to delete one of the duplicate rows with the highest user ID in my table, consisting of 3.5 million rows. I have around 1300 rows to delete, and I am currently using the following query:

delete from Data
where exists (select 1 from Data t2
              where data.code = t2.code and data.issue = t2.issue
                and data.id < t2.id);

查询已运行15分钟以上.有什么方法可以优化它以减少花费的时间?我正在使用SQLite 3.22.0版.

The query has run for more than 15 minutes. Is there any way I can optimize this to decrease the time taken? I'm using SQLite version 3.22.0.

推荐答案

通常，删除表中的许多行根本没有效率.重建表的速度可能更快.

Often, deleting a lot of rows in a table is simply inefficient. It can be faster to reconstruct the table.

想法是将所需的行选择到另一个表中:

The idea is to select the rows you want into another table:

create table temp_data as
    select t.*
    from data t
    where t.id = (select max(t2.id)
                  from data t2
                  where t2.code = t.code and t2.issue = t.issue
                 );

对于此查询，您希望在(代码，问题，id)上建立索引.

For this query, you want an index on (code, issue, id).

然后，在安全地收集和验证数据之后，您可以清空现有表并重新插入:

Then when the data is safely tucked away and validated, you can empty the existing table and re-insert:

delete from data;

确保已删除表格上的所有触发器.您可以阅读有关SQLite的"truncate"信息.文档中的优化.在大多数其他数据库中，您将使用命令截断表数据.

Be sure you have removed any triggers on the table. You can read about SQLite's "truncate" optimization in the documentation. In most other databases, you would use the command truncate table data.

然后，您可以重新插入数据:

Then, you can re-insert the data:

insert into data
    select *
    from temp_data;

这篇关于为SQLite3中的重复行优化删除查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！