如何高效去除SQLite中2亿行大表的全列重复数据？

为什么原始SELECT DISTINCT *慢到离谱？ #

当你执行SELECT DISTINCT *时，SQLite需要完成以下操作：

• 全表扫描2亿行数据，把每一行的6列完整数据加载到内存（或临时磁盘文件）
• 对所有加载的数据做去重比较：因为没有预设的顺序，SQLite只能逐一对比每行的全部列值，判断是否重复
• 内存不足时，会频繁将临时数据刷到磁盘，产生大量随机IO，这是性能瓶颈的核心

2亿行的全量数据处理，加上无顺序的重复判断，IO和计算开销呈指数级增长，导致10小时都无法完成。

索引与rowid分组的提速逻辑 #

复合索引的作用 #

给所有6列创建复合索引后，SQLite可以利用索引的两个特性提速：

1. 有序性：索引会按指定列的顺序排序存储，相同列值的条目会集中在一起。去重时只需顺序遍历索引，跳过连续重复的条目即可，无需对全表数据做无差别对比
2. 轻量化：索引仅存储索引列和对应的rowid，比原始表的单条数据体积小很多，遍历索引的IO开销远低于全表扫描

按rowid分组的原理 #

用GROUP BY 所有列 + 取rowid的方式去重，本质是借助索引的有序性快速分组：

• 当有复合索引时，GROUP BY col1,col2,...col6可以直接遍历索引完成分组，无需加载全表数据
• 每组只保留一个rowid（比如MIN(rowid)），再通过rowid回表查询完整数据。rowid是SQLite内置的主键，查询速度极快，相当于直接定位到磁盘上的物理位置

这种方式把“全量数据去重”拆解成“索引分组取唯一标识”+“精准回表取数”，大幅降低了中间过程的内存和IO开销。

步骤1：调整SQLite核心参数（先做） #

先修改配置提升基础性能，尤其是IO和缓存：

-- 增大缓存（按内存调整，比如设为1000000页=4GB，默认页大小4KB）
PRAGMA cache_size = 1000000;
-- 开启WAL模式，提升读写性能
PRAGMA journal_mode = WAL;
-- 临时文件优先放内存（内存不足时自动转磁盘，建议用SSD）
PRAGMA temp_store = MEMORY;

步骤2：创建复合索引 #

给需要去重的6列创建复合索引（替换为你的实际列名）：

CREATE INDEX idx_plates_all_cols ON plates(col1, col2, col3, col4, col5, col6);

注：2亿行表建索引需要一定时间（可能1-3小时，取决于磁盘性能），但这是一次性开销，后续去重会大幅提速。

步骤3：用rowid分组法创建去重表 #

执行以下语句生成去重后的临时表，比直接DISTINCT效率高5-10倍：

CREATE TABLE temp AS
SELECT p.*
FROM plates p
JOIN (
    SELECT MIN(rowid) AS unique_rid
    FROM plates
    GROUP BY col1, col2, col3, col4, col5, col6
) t ON p.rowid = t.unique_rid;

如果内存有限，也可以用WHERE IN写法，效果相近：

CREATE TABLE temp AS
SELECT * FROM plates
WHERE rowid IN (
    SELECT MIN(rowid)
    FROM plates
    GROUP BY col1, col2, col3, col4, col5, col6
);

备选方案：分批次处理（适合硬件资源有限的情况） #

如果服务器内存/磁盘不足以支撑全量索引和去重，可以按rowid分段处理：

1. 先创建空的temp表，结构和plates一致：

CREATE TABLE temp AS SELECT * FROM plates WHERE 1=0;

2. 分批次插入去重数据（每次处理1000万行，可根据资源调整）：

-- 第一次批次
INSERT INTO temp SELECT DISTINCT * FROM plates WHERE rowid BETWEEN 1 AND 10000000;
-- 第二次批次，以此类推
INSERT INTO temp SELECT DISTINCT * FROM plates WHERE rowid BETWEEN 10000001 AND 20000000;

3. 最后对temp表再做一次去重（处理跨批次的重复数据）：

CREATE TABLE temp_final AS SELECT DISTINCT * FROM temp;
DROP TABLE temp;
ALTER TABLE temp_final RENAME TO temp;

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