在DuckDB中运行大规模滚动平均的性能优化问询

一、DuckDB核心配置调优 #

• 内存与线程设置：根据机器可用内存调整内存限制，避免磁盘交换（比如机器有64GB内存就设为PRAGMA memory_limit = '64GB';）；同时拉满CPU线程数：PRAGMA threads = <你的CPU核心数>;
• Parquet读取优化：开启下推扫描减少数据加载量：PRAGMA parquet_scan_pushdown = true;；启用并行读取：PRAGMA parallelize_reads = true;
• 临时目录与Checkpoint优化：将临时目录移至SSD：PRAGMA temp_directory = '/ssd/tmp/';；计算期间禁用或减少Checkpoint频率：PRAGMA disable_checkpointing = true;（计算完成后再恢复）

二、数据预处理优化 #

• 确保FileID内数据有序：滚动窗口依赖有序数据，如果原始数据未按FileID + 排序键排序，每个窗口计算前都会触发额外排序，这是全量慢的核心原因之一。用DuckDB重排数据并按FileID分区存储：

  COPY (
      SELECT * FROM parquet_scan('原始数据目录/') ORDER BY FileID, 你的排序键
  ) TO '优化后数据目录/' 
  FORMAT PARQUET 
  PARTITION BY (FileID) 
  PER_FILE_SIZE = '1GB';
  

• 合并Parquet小文件：如果原始目录存在大量小文件（比如<100MB），会大幅增加IO开销，上述重写命令的PER_FILE_SIZE参数可将每个FileID的数据合并为1GB左右的大文件，提升读取效率。

三、窗口函数替换优化 #

• 使用DuckDB内置滚动函数替代通用窗口函数：针对前后55行的平均（共111行窗口），用rolling_avg替代AVG() OVER()，内置函数经过专门优化，性能远高于通用窗口函数：

  SELECT 
      FileID, 
      你的排序键, 
      rolling_avg(目标列, 111) AS 滚动平均值
  FROM parquet_scan('优化后数据目录/')
  ORDER BY FileID, 你的排序键;
  

  注意：rolling_avg默认窗口是当前行及之前N-1行，若需要前后55行，可通过DESCRIBE FUNCTION rolling_avg;查看参数说明，调整为rolling_avg(目标列, 111, -55)来对齐窗口范围。

四、分批处理策略 #

既然单个/少量FileID计算极快，可手动分批处理避免一次性加载全量数据：

• 先用SQL导出所有唯一FileID：

  COPY (SELECT DISTINCT FileID FROM parquet_scan('优化后数据目录/')) TO 'file_ids.txt';
  

• 用Python脚本循环读取FileID列表，每次处理100-200个FileID，将结果写入临时表或分区Parquet：

  import duckdb
  
  con = duckdb.connect('results.db')
  con.execute("CREATE TABLE IF NOT EXISTS results (FileID VARCHAR, 排序键 INT, 滚动平均值 DOUBLE)")
  
  # 读取FileID列表
  file_ids = con.execute("SELECT * FROM read_csv('file_ids.txt')").fetchall()
  
  # 分批处理，每批100个
  batch_size = 100
  for i in range(0, len(file_ids), batch_size):
      batch = [f"'{row[0]}'" for row in file_ids[i:i+batch_size]]
      file_id_str = ','.join(batch)
      con.execute(f"""
          INSERT INTO results
          SELECT FileID, 排序键, rolling_avg(目标列, 111)
          FROM parquet_scan('优化后数据目录/')
          WHERE FileID IN ({file_id_str})
          ORDER BY FileID, 排序键
      """)
      con.commit()
  
  # 最终导出结果
  con.execute("COPY results TO '最终结果目录/' FORMAT PARQUET PARTITION BY (FileID)")
  

五、其他优化点 #

• 升级DuckDB到最新版：新版本会持续优化窗口函数、Parquet读取等核心功能，旧版本可能存在性能瓶颈；
• 检查磁盘IO：如果使用机械硬盘，换成SSD可将IO性能提升数倍，这是全量计算的关键硬件优化；
• 简化查询逻辑：确保查询中只包含必要的列，避免SELECT *加载多余数据，减少内存占用与IO开销。

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