大型SQLite数据库中my_table表的重新索引优化方法咨询

针对你提到的2亿行数据的my_table表操作耗时过长的问题，通过新增reindex列重新组织数据确实是个有效的优化方向，下面给你详细拆解具体操作和相关注意事项：

一、为什么新增自增reindex列能提速？ #

SQLite对于没有主键或合适索引的超大表，查询时会进行全表扫描，2亿行的规模下这个过程必然耗时很久。新增一个连续自增的reindex列相当于给表加上了一个紧凑的、无间隙的主键索引，SQLite可以利用这个索引快速定位数据块，大幅减少IO开销和扫描时间。

二、具体操作步骤 #

1. 创建带reindex列的新表 #

首先创建一个包含reindex（自增主键）、原表所有列的新表：

CREATE TABLE my_table_new (
    reindex INTEGER PRIMARY KEY AUTOINCREMENT,
    index1 INTEGER,
    column_one TEXT
);

2. 批量迁移数据 #

因为数据量高达2亿行，直接用INSERT INTO ... SELECT可能会内存溢出，建议分批次插入，比如每次插入10万行：

-- 先获取最大index1值
SELECT MAX(index1) FROM my_table;

-- 循环插入，假设max_index是上面得到的值
WITH RECURSIVE batches(start) AS (
    SELECT 0
    UNION ALL
    SELECT start + 100000 FROM batches WHERE start < max_index
)
INSERT INTO my_table_new(index1, column_one)
SELECT index1, column_one FROM my_table
WHERE index1 >= batches.start AND index1 < batches.start + 100000;

如果你的index1本身就是连续无间隙的，也可以直接用：

INSERT INTO my_table_new(index1, column_one)
SELECT index1, column_one FROM my_table ORDER BY index1;

3. 替换原表 #

数据迁移完成后，替换原表：

DROP TABLE my_table;
ALTER TABLE my_table_new RENAME TO my_table;

4. 验证数据完整性 #

最后一定要验证数据是否完整：

SELECT COUNT(*) FROM my_table; -- 应该等于2亿
SELECT COUNT(DISTINCT reindex) FROM my_table; -- 应该等于总行数，确保reindex无重复

三、额外优化建议 #

• 迁移前关闭SQLite的写同步和事务日志，提升插入速度：

  PRAGMA synchronous = OFF;
  PRAGMA journal_mode = MEMORY;
  

  注意：操作完成后记得恢复默认设置，避免数据丢失风险：

  PRAGMA synchronous = FULL;
  PRAGMA journal_mode = WAL;
  

• 如果后续查询主要基于column_one，可以给column_one再建立一个普通索引：

  CREATE INDEX idx_column_one ON my_table(column_one);
  

• 考虑启用WAL模式（Write-Ahead Logging），它比传统的ROLLBACK JOURNAL模式更适合大规模数据的读写操作：

  PRAGMA journal_mode = WAL;
  

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