如何优化SQLite数据库操作？批量文件校验代码性能优化问询

1. 避免循环内重复创建数据库连接 #

你的代码每次循环都重新建立数据库连接，这会产生大量不必要的开销。应该将连接逻辑移到循环外部，整个处理流程只建立一次连接：

import sqlite3 as lite

# 仅初始化一次连接和游标
con = lite.connect(DB_PATH)
con.text_factory = str
cur = con.cursor()

for i, file in enumerate(allMedia):
    cur.execute("SELECT 1 FROM files WHERE path = ? LIMIT 1", (file,))
    row = cur.fetchone()
    if row is not None:
        pass

# 处理完成后关闭连接
con.close()

2. 给path字段添加索引 #

当前查询没有索引的情况下，每次都会执行全表扫描，这是速度慢的核心原因。给files表的path字段创建索引：

-- 执行一次该SQL创建索引（可在代码中执行，或通过SQLite可视化工具操作）
CREATE INDEX IF NOT EXISTS idx_files_path ON files(path);

索引创建后，查询会从全表扫描转为索引查找，速度会大幅提升。

3. 简化查询语句 #

你仅需要判断文件是否存在，不需要返回所有字段，因此可以简化查询语句，减少数据传输和处理开销：

# 只查询占位值1，且限制返回1条结果
cur.execute("SELECT 1 FROM files WHERE path = ? LIMIT 1", (file,))

4. 批量查询减少交互次数 #

针对数千个文件的场景，逐个查询效率极低，可以按批次批量查询：

# 设定每批处理的文件数量，比如100个
batch_size = 100
for i in range(0, len(allMedia), batch_size):
    batch_files = allMedia[i:i+batch_size]
    # 生成对应数量的占位符
    placeholders = ','.join(['?'] * len(batch_files))
    # 批量查询已存在的path
    cur.execute(f"SELECT path FROM files WHERE path IN ({placeholders})", batch_files)
    # 将结果存入集合，方便快速判断
    existing_paths = {row[0] for row in cur.fetchall()}
    # 遍历当前批次的文件
    for file in batch_files:
        if file in existing_paths:
            pass
        # 处理不存在的文件逻辑...

这种方式将数千次查询压缩为几十次，能显著提升整体处理速度。

5. 调整SQLite性能参数 #

通过开启WAL模式、增大缓存等参数优化SQLite运行性能：

con = lite.connect(DB_PATH)
con.text_factory = str
# 开启WAL模式，提升读写性能
con.execute("PRAGMA journal_mode=WAL")
# 增大缓存大小，例如设置为100MB（单位为页，默认每页4KB，100MB对应25600页）
con.execute("PRAGMA cache_size=-25600")
cur = con.cursor()

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