DuckDB与MySQL在大数据OLAP场景下的性能对比分析

1. 测试背景与场景需求

最近在数据仓库项目中遇到一个典型问题：当单表数据量突破5亿条记录后，传统关系型数据库的查询性能开始急剧下降。团队需要评估在OLAP场景下，轻量级分析型数据库DuckDB与传统MySQL的性能差异。这个测试源于真实业务需求——我们每天需要处理超过2TB的日志数据，并支持业务部门的实时分析查询。

测试环境采用AWS r5.2xlarge实例（8 vCPU/64GB内存），数据集使用纽约出租车行程记录的公开数据集，包含：

• 主表：5.8亿条行程记录（约120GB）
• 关联表：出租车基础信息表（50万条记录）

2. 数据库配置与测试设计

2.1 环境准备

• MySQL 8.0.32 配置要点：

  ini复制innodb_buffer_pool_size=48G
  innodb_io_capacity=2000
  innodb_flush_neighbors=0
  query_cache_type=0
  

  建立复合索引：(pickup_datetime, payment_type)

• DuckDB 0.8.1 配置：

  sql复制SET threads TO 8;
  SET memory_limit='60GB';
  

  数据以Parquet格式存储，DuckDB直接读取文件系统

2.2 测试查询设计

设计四类典型分析查询：

1. 时间范围聚合（按月统计行程量）

   sql复制SELECT DATE_TRUNC('month', pickup_datetime) AS month, 
          COUNT(*) AS trips
   FROM trips 
   WHERE pickup_datetime BETWEEN '2015-01-01' AND '2019-12-31'
   GROUP BY 1;
   

2. 多表关联分析（车型与收入的关联）

   sql复制SELECT v.vehicle_type, 
          AVG(t.total_amount) AS avg_amount
   FROM trips t JOIN vehicles v ON t.vehicle_id = v.id
   GROUP BY 1;
   

3. 窗口函数计算（按司机ID统计移动平均）

   sql复制SELECT driver_id, pickup_datetime,
          AVG(total_amount) OVER (
            PARTITION BY driver_id 
            ORDER BY pickup_datetime 
            ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
          ) AS moving_avg
   FROM trips;
   

4. 复杂条件过滤（空间+时间复合查询）

   sql复制SELECT COUNT(*) 
   FROM trips
   WHERE ST_Distance(pickup_location, ST_Point(-73.98, 40.75)) < 1000
     AND pickup_datetime BETWEEN '2019-01-01' AND '2019-01-31';
   

3. 性能测试结果分析

3.1 冷查询响应时间对比（单位：秒）

注意：所有测试均执行3次取平均值，清空OS缓存后测试

3.2 资源占用对比

• 内存峰值：

  • MySQL：52GB（含buffer pool）
  • DuckDB：38GB（含列式缓存）

• 磁盘IO：

  • MySQL：平均读取吞吐量1.2GB/s
  • DuckDB：平均读取吞吐量680MB/s（Parquet列裁剪优势）

3.3 热点查询性能趋势

随着数据量增长（1亿→5.8亿记录），观察到：

• MySQL的查询时间呈超线性增长（指数级上升趋势）
• DuckDB保持近似线性增长，尤其在聚合类查询中表现稳定

4. 技术原理深度解析

4.1 DuckDB的列式执行优势

DuckDB采用向量化执行引擎，其核心优化包括：

1. 列式内存布局：相同数据类型连续存储，提高CPU缓存命中率
2. 批处理模式：每次处理1024行的数据块，减少虚函数调用开销
3. 延迟物化：在过滤操作中先处理谓词再取列数据

cpp复制// DuckDB向量化过滤的简化伪代码
void Filter(Vector &input, SelectionVector &sel) {
  for(idx_t i = 0; i < input.size; i += VECTOR_SIZE) {
    // 一次处理1024个值
    VectorData vdata;
    input.Orrify(i, vdata);
    
    for(idx_t j = 0; j < VECTOR_SIZE; j++) {
      if (CheckCondition(vdata, j)) {
        sel.Append(vdata.sel->get_index(j));
      }
    }
  }
}

4.2 MySQL的瓶颈分析

测试中发现MySQL主要耗时在：

1. 行存储遍历：即使使用索引，仍需回表查完整记录
2. 优化器限制：复杂查询容易产生次优执行计划
3. 并发控制开销：MVCC机制带来额外的版本检查

5. 生产环境适用建议

5.1 DuckDB最佳实践场景

• 交互式分析：BI工具直连场景（Superset/Metabase）
• ETL中间层：数据转换过程中的临时分析
• 嵌入式应用：需要本地分析能力的客户端程序

5.2 MySQL优化方向

对于必须使用MySQL的场景：

1. 分区表策略：按时间范围分区（特别是时间序列数据）

   sql复制ALTER TABLE trips PARTITION BY RANGE (YEAR(pickup_datetime)) (
       PARTITION p2015 VALUES LESS THAN (2016),
       PARTITION p2016 VALUES LESS THAN (2017),
       ...
   );
   

2. 物化视图：预计算高频聚合查询
3. 查询重写：将窗口函数拆分为多阶段查询

6. 踩坑实录与问题排查

6.1 DuckDB常见问题

1. 内存不足错误：

   bash复制# 错误示例：Error: Out of Memory
   

   解决方案：

   sql复制SET memory_limit='60GB';  -- 不超过物理内存的90%
   SET temp_directory='/mnt/tmp';  -- 指定临时文件目录
   

2. 并行度设置不当：

   • 线程数建议设置为物理核心数的75%
   • 避免与Spark/Flink等框架同时使用高并行度

6.2 MySQL优化误区

• 过度索引：测试发现超过5个索引后写入性能下降40%
• 错误的数据类型：使用DECIMAL(19,4)代替DOUBLE存储经纬度导致存储膨胀
• 错误的事务隔离级别：REPEATABLE READ在分析查询中产生不必要的锁

7. 扩展测试：数据规模的影响

7.1 不同数据量下的表现

7.2 数据格式对比

测试Parquet vs CSV加载：

• 数据加载时间：
  • MySQL导入CSV：142分钟
  • DuckDB读取Parquet：4分钟（直接分析，无需导入）

8. 结论与选型建议

从测试数据来看，在5亿级数据量下：

• 简单查询：DuckDB快5-10倍
• 复杂分析：DuckDB快6-15倍
• 存储效率：Parquet格式比MySQL表空间节省40%

实际项目中，我们最终采用混合架构：

1. 原始数据存储在S3作为数据湖（Parquet格式）
2. DuckDB作为即席查询引擎
3. MySQL仅处理高频交易类请求

这种架构使我们的月报生成时间从原来的6小时缩短到23分钟，同时硬件成本降低60%。对于需要频繁全表扫描的分析场景，列式存储引擎的优势是决定性的。