SQLiteStudio比Pandas快？ProcessPoolExecutor查询变慢原因及优化

我有大约100万条类似以下的查询语句，需要在本地3000万行的SQLite数据库上执行，目标是提升查询速度。单条查询在SQLiteStudio耗时0.04秒，通过Pandas执行耗时0.075秒：

starttime = timer()
con = sqlite3.connect(f"C:\\Users\\database.db")
query = "SELECT MIN(datetime), * FROM big_table " \
        "WHERE x = 1 AND y = 2 AND z = 3 " \
        "GROUP BY DATE(datetime), name"
try:
    df = pd.read_sql_query(query, con=con)
finally:
    con.close()
print(f"Pandas query time: {timer()-starttime}")

尝试用ProcessPoolExecutor多进程提速时，单条查询耗时骤增至约1秒。我有12核CPU，原本期望能获得约12倍的速度提升：

with concurrent.futures.ProcessPoolExecutor() as executor:
    results = executor.map(multiprocessor, queries)

def multiprocessor(query):
    starttime = timer()
    con = sqlite3.connect(f"C:\\Users\\database.db")
    try:
        df = pd.read_sql_query(query, con=con)
    finally:
        con.close()
    print(f"Multiprocessed pandas query time: {timer()-starttime}")

各执行方式耗时对比：

• SQLiteStudio：0.04秒
• Pandas单进程：0.075秒
• Pandas多进程：1.00秒

请问：

1. 为何SQLiteStudio这类IDE的查询速度比Pandas快？
2. 为何使用ProcessPoolExecutor执行时查询变慢？
3. 有哪些可行的提速方法？
4. 我的实际需求是获取每日每个name的首次出现记录，有没有针对这个需求的优化建议？

1. SQLiteStudio比Pandas快的原因 #

• 连接复用与状态优化：SQLiteStudio会维持长期数据库连接，避免了每次查询重复执行连接建立/关闭的开销；而你的Pandas代码每次查询都新建连接，这部分额外操作会增加耗时。
• 结果集处理差异：SQLiteStudio仅展示部分结果（比如前N行），不会把全量结果加载到内存；而pd.read_sql_query会把整个结果集转换成DataFrame，包含数据类型转换、内存分配等额外步骤，占用更多时间。
• 默认配置差异：SQLiteStudio可能默认启用了优化参数（如PRAGMA cache_size调大、PRAGMA journal_mode=WAL），而Python的sqlite3模块默认配置更保守，未开启这些优化。

2. 多进程查询变慢的原因 #

• SQLite锁机制限制：SQLite采用文件级锁，多进程并发读会触发共享锁，但频繁的连接建立/关闭会加剧锁竞争，反而降低效率。
• 进程开销：每个进程都要单独初始化数据库连接、重建内存页缓存，这部分重复操作带来巨大额外开销；同时进程切换、结果集序列化传递也会消耗时间。
• 缓存无法共享：每个进程的SQLite连接有独立的页缓存，相同数据需要多次加载到不同进程内存，浪费IO和内存资源。

3. 通用提速方法 #

针对Pandas单进程优化 #

• 复用数据库连接：保持长期连接供所有查询使用，避免重复连接开销：

  con = sqlite3.connect(f"C:\\Users\\database.db")
  # 启用WAL模式提升并发读性能
  con.execute("PRAGMA journal_mode=WAL")
  # 调大缓存大小（1GB，单位为页，每页默认4KB，1GB=262144页）
  con.execute("PRAGMA cache_size=-262144")
  try:
      for query in queries:
          starttime = timer()
          df = pd.read_sql_query(query, con=con)
          print(f"Pandas query time: {timer()-starttime}")
  finally:
      con.close()
  

• 优化SQLite配置：连接后执行以下命令提升性能：
  • PRAGMA journal_mode=WAL：启用预写日志，提升并发读能力
  • PRAGMA cache_size：调大内存缓存，减少磁盘IO
  • PRAGMA synchronous=NORMAL：降低同步级别，减少磁盘等待（数据安全性要求高时谨慎使用）
  • PRAGMA temp_store=MEMORY：临时表存储在内存中，加快分组排序操作

替代多进程：多线程方案 #

SQLite在WAL模式下支持多线程并发读，用ThreadPoolExecutor替代多进程，避免进程开销：

import concurrent.futures

con = sqlite3.connect(f"C:\\Users\\database.db", check_same_thread=False)
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA cache_size=-262144")

def multithread(query):
    starttime = timer()
    df = pd.read_sql_query(query, con=con)
    print(f"Multithreaded pandas query time: {timer()-starttime}")
    return df

with concurrent.futures.ThreadPoolExecutor(max_workers=8) as executor:
    results = executor.map(multithread, queries)

con.close()

注意：check_same_thread=False允许多线程共享连接，但要确保无并发写操作。

4. 针对「每日每个name首次出现记录」的需求优化 #

原查询SELECT MIN(datetime), * FROM big_table WHERE x=? AND y=? AND z=? GROUP BY DATE(datetime), name存在问题：SELECT *在GROUP BY中不符合SQL标准，SQLite虽允许，但返回的非聚合列是随机的，无法保证对应MIN(datetime)的记录。

正确且高效的优化方案 #

方法1：使用窗口函数（SQLite 3.25+支持）

WITH ranked_records AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY DATE(datetime), name
               ORDER BY datetime ASC
           ) AS rn
    FROM big_table
    WHERE x = 1 AND y = 2 AND z = 3
)
SELECT * FROM ranked_records WHERE rn = 1;

该方式能准确获取每日每个name的第一条记录，性能优于GROUP BY写法。

方法2：创建复合索引

针对查询条件、分组和排序字段创建复合索引，让SQLite直接通过索引获取数据，无需全表扫描：

CREATE INDEX idx_big_table_x_y_z_dt_name ON big_table (x, y, z, DATE(datetime), name, datetime);

此索引覆盖了WHERE条件、分组字段和排序字段，SQLite可直接从索引读取所需数据，大幅提升查询速度。

方法3：批量处理查询

若100万条查询的x,y,z组合有重复，先去重后批量查询，避免重复访问数据库：

# 提取所有唯一的(x,y,z)组合
unique_filters = list({(q_x, q_y, q_z) for q_x, q_y, q_z in queries})

# 构造批量查询SQL
placeholders = ", ".join(["(?, ?, ?)"] * len(unique_filters))
query = f"""
WITH ranked_records AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY DATE(datetime), name
               ORDER BY datetime ASC
           ) AS rn
    FROM big_table
    WHERE (x, y, z) IN ({placeholders})
)
SELECT * FROM ranked_records WHERE rn = 1;
"""

# 扁平化参数列表
params = []
for x,y,z in unique_filters:
    params.extend([x,y,z])

# 执行批量查询
df_all = pd.read_sql_query(query, con=con, params=params)

# 在Pandas中根据原查询列表过滤所需结果（可使用merge或布尔索引）

这种方式将100万次查询缩减为几次，性能提升显著。

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