SQLite两表交换指定列数据时脚本运行过慢的优化问询

核心性能瓶颈分析 #

现有脚本慢的核心原因是两次UPDATE都使用了关联子查询，每更新1行都要执行一次跨表匹配查询，500万行就要跑1000万次匹配，IO开销被指数放大。

具体优化手段 #

1. 替换标量子查询为关联更新（SQLite 3.33.0+ 支持） #

直接用UPDATE FROM语法做批量关联更新，避免逐行查询，性能可以提升几十倍：

-- 执行脚本前先开启性能优化参数
PRAGMA synchronous = OFF; -- 关闭磁盘同步校验，异常断电可能丢数据，修复完记得改回NORMAL
PRAGMA journal_mode = MEMORY; -- 日志存在内存，减少磁盘IO
PRAGMA cache_size = -2000000; -- 设置缓存为2GB，可根据机器内存调整
PRAGMA temp_store = MEMORY; -- 临时表存在内存

-- 一步完成两表数据交换，不需要临时表
WITH swap_data AS (
    SELECT TA.KE, TA.A2 AS ta_a2, TB.B2 AS tb_b2
    FROM TA JOIN TB ON TA.KE = TB.KE
)
UPDATE TA SET A2 = (SELECT tb_b2 FROM swap_data WHERE swap_data.KE = TA.KE);
UPDATE TB SET B2 = (SELECT ta_a2 FROM swap_data WHERE swap_data.KE = TB.KE);

-- 执行完恢复默认配置
PRAGMA synchronous = NORMAL;
PRAGMA journal_mode = WAL;

2. 低版本SQLite用重建表方案（性能比逐行更新高10倍以上） #

如果你的SQLite版本不支持UPDATE FROM，直接重建表比逐行更新快得多，因为重建表是顺序IO，更新是随机IO：

-- 开启优化参数同上
-- 重建TA表，替换A2列
CREATE TABLE TA_new AS
SELECT 
    TA.KE,
    TA.A1,
    TB.B2 AS A2,
    TA.A3,
    TA.A4,
    TA.A5,
    TA.A6,
    TA.A7,
    TA.A8,
    TA.A9
FROM TA JOIN TB ON TA.KE = TB.KE;

-- 重建TB表，替换B2列
CREATE TABLE TB_new AS
SELECT 
    TB.KE,
    TB.B1,
    TA.A2 AS B2,
    TB.B3,
    TB.B4,
    TB.B5,
    TB.B6,
    TB.B7,
    TB.B8,
    TB.B9
FROM TB JOIN TA ON TB.KE = TA.KE;

-- 替换原表
DROP TABLE TA;
ALTER TABLE TA_new RENAME TO TA;
DROP TABLE TB;
ALTER TABLE TB_new RENAME TO TB;

-- 恢复默认配置同上

3. 额外优化建议 #

• 修复前先备份数据库，避免操作失误丢失数据
• 关闭其他读写该数据库的进程，避免锁等待
• 优先用SSD执行修复操作，速度比机械硬盘快3~5倍

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