Python操作SQLite大表删除重复行耗时过长优化方案咨询

云原生阿岚

2026-8-29

问题背景

现有SQLite3数据库规模约2.6GB，仅包含1张数据表，共82万行记录、26个字段。
数据采用迭代流程处理：每次生成新数据后先存入pandas DataFrame对象，再调用insert_values_to_table函数将数据插入SQLite数据库，当前插入流程运行稳定、执行速度较快。
每次插入完成后会调用sanitize_database函数清除数据库重复记录，重复判定规则为26个字段的值完全一致。该函数和插入函数采用相同方式连接数据库，创建游标后执行逻辑为：基于原表所有唯一值创建临时表temp_table → 删除原表全部记录 → 将临时表所有记录插入清空后的原表 → 删除临时表。
现存问题：该去重方案执行速度极慢，处理当前规模数据集需要近1小时。曾尝试通过给指定字段设置主键或添加唯一约束的方式优化，但pandas.DataFrame.to_sql方法不支持这类操作——该方法插入逻辑为原子操作，要么整个DataFrame全部插入成功，要么全部插入失败，官方也尚未推出append_skipdupes相关能力。

现有功能实现代码如下：

# 插入pandas数据到SQLITE3数据库的函数
def insert_values_to_table(table_name, output):
    conn = connect_to_db("/mnt/wwn-0x5002538e00000000-part1/DATABASE/table_name.db")

    # 连接存在时执行数据插入
    if conn is not None:
        c = conn.cursor()        
        
        # 将pandas数据（output）写入SQL数据库
        output.to_sql(name=table_name, con=conn, if_exists='append', index=False)                

        # 关闭连接
        conn.close()
        print('SQL insert process finished')

# 数据库去重函数，仅保留唯一行
def sanitize_database():
    conn = connect_to_db("/mnt/wwn-0x5002538e00000000-part1/DATABASE/table_name.db")
    c = conn.cursor()   
    c.executescript(""" 
        CREATE TABLE temp_table as SELECT DISTINCT * FROM table_name; 
        DELETE FROM table_name;
        INSERT INTO table_name SELECT * FROM temp_table;
        DROP TABLE temp_table
        """)
    conn.close()

优化方案

当前去重流程慢的核心原因是做了两次全量数据读写（建临时表读全量写临时表、删原表后读临时表写回原表），没有任何增量逻辑，磁盘IO开销极高，可按以下优先级选择优化方案：

前置去重，从源头减少重复数据入库
每次生成新DataFrame后，先在pandas层调用output = output.drop_duplicates()清除当前批次内的重复数据；如果条件允许，可提前拉取数据库内已存数据的唯一标识，和当前批次做差集，仅将数据库中不存在的新行写入，从根源降低后续去重的计算量。
用唯一约束+SQLite原生去重语法，彻底替代插后全量去重
不要直接依赖to_sql的append模式写入：
1. 首次建表时给26个字段添加联合唯一约束，或给存量表创建覆盖所有字段的唯一索引
2. 后续插入数据时，先用to_sql将当前批次DataFrame写入临时表，再执行INSERT OR IGNORE INTO 主表 SELECT * FROM 临时表完成写入，遇到重复数据SQLite会直接跳过，不会触发整批插入失败，完全不需要插入后再做全表扫描去重。
  该方案仅需处理新增批次的数据，不需要扫描、重写存量82万行记录，速度比现有方案提升10~100倍。
优化现有全量去重逻辑，减少不必要的IO开销
如果必须保留定期全量去重的流程，可替换原有SQL逻辑，省去一次全量写入操作：
```
-- 直接创建去重后的新表，删除旧表后重命名新表，省去回写步骤
CREATE TABLE temp_table AS SELECT DISTINCT * FROM table_name;
DROP TABLE table_name;
ALTER TABLE temp_table RENAME TO table_name;
```
执行去重前可临时调整SQLite配置进一步提速：
```
conn.execute("PRAGMA journal_mode = WAL;")
conn.execute("PRAGMA synchronous = OFF;")
```
注意去重完成后可根据需要改回原有同步配置，避免极端情况下的数据损坏风险。

内容的提问来源于stack exchange，提问作者Rivered