MySQL与DuckDB亿级数据查询性能对比测试

1. 背景与测试目标

最近在优化公司数据分析平台时，我遇到了一个经典问题：当数据量达到亿级时，传统关系型数据库的查询性能急剧下降。为此我对比测试了两种解决方案：老牌劲旅MySQL和新兴的嵌入式分析数据库DuckDB。测试结果令人震惊——在某些场景下DuckDB的查询速度能达到MySQL的千倍以上。

2. 测试环境搭建

2.1 硬件配置

• 服务器：AWS EC2 r5.2xlarge
• CPU：8核 Intel Xeon Platinum 8000系列
• 内存：64GB
• 存储：500GB GP3 EBS卷（基准吞吐量250MB/s）

2.2 软件版本

• MySQL 8.0.32（默认InnoDB配置）
• DuckDB 0.8.1（默认配置）
• 数据集：TPC-H SF100（约100GB数据）

重要提示：测试前确保关闭所有数据库的查询缓存，避免缓存对测试结果的影响。在MySQL中执行SET GLOBAL query_cache_size = 0;，DuckDB默认不启用查询缓存。

3. 数据集准备

3.1 TPC-H数据生成

使用dbgen工具生成标准测试数据：

./dbgen -s 100 -f

这会生成约100GB的CSV文件，主要包含以下表：

• lineitem（6亿行）
• orders（1.5亿行）
• partsupp（8000万行）

3.2 数据加载对比

DuckDB的列式存储设计使其在数据加载时就能进行压缩，而MySQL需要额外时间构建B+树索引。

4. 查询性能测试

4.1 单表扫描测试

查询语句：

SELECT * FROM lineitem WHERE L_COMMENT LIKE '%special%' LIMIT 1000000;

DuckDB的向量化执行引擎可以批量处理数据，而MySQL需要逐行解析。

4.2 聚合查询测试

复杂分组聚合：

SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS sum_qty, AVG(L_EXTENDEDPRICE) AS avg_price FROM lineitem WHERE L_SHIPDATE <= '1998-12-01' GROUP BY L_RETURNFLAG, L_LINESTATUS;

执行过程分析：

1. MySQL需要全表扫描并创建临时表
2. DuckDB直接对列数据应用SIMD指令计算
3. 最终结果一致性校验通过

性能对比：

4.3 多表连接性能

五表关联查询：

SELECT c.C_NAME, o.O_ORDERDATE, SUM(l.L_EXTENDEDPRICE) FROM customer c JOIN orders o ON c.C_CUSTKEY = o.O_CUSTKEY JOIN lineitem l ON o.O_ORDERKEY = l.L_ORDERKEY JOIN partsupp ps ON l.L_PARTKEY = ps.PS_PARTKEY JOIN part p ON ps.PS_PARTKEY = p.P_PARTKEY WHERE p.P_TYPE LIKE 'MEDIUM%' GROUP BY c.C_NAME, o.O_ORDERDATE;

连接算法对比：

• MySQL：使用嵌套循环连接
• DuckDB：采用哈希连接+向量化执行

实测性能：

5. 深度技术解析

5.1 DuckDB的列式存储

DuckDB采用的自研存储格式具有以下特点：

1. 列式压缩：对每列采用RLE、字典编码等压缩算法
2. 向量化处理：每次处理1024行的数据块
3. 延迟物化：仅解压需要的列数据

5.2 执行引擎优化

与MySQL的火山模型对比：

5.3 内存管理差异

在处理10GB大表时的内存行为：

# DuckDB内存管理示例 duckdb.execute("PRAGMA memory_limit='16GB'") # 自动溢出到磁盘 duckdb.execute("PRAGMA temp_directory='/tmp'")

6. 实际应用建议

6.1 适用场景

推荐使用DuckDB：

• 嵌入式分析应用
• 数据科学工作流
• 需要交互式分析的场景

建议保留MySQL：

• 高并发OLTP
• 需要严格事务保证
• 已有成熟MySQL生态

6.2 混合架构方案

graph LR A[业务系统] -->|写入| B(MySQL) B -->|CDC同步| C(DuckDB) C -->|服务| D[分析应用]

6.3 性能调优技巧

DuckDB优化：

1. 设置合适的内存限制
2. 对常用过滤列创建索引
3. 使用PRAGMA调整并行度

MySQL优化：

1. 增加合适的复合索引
2. 考虑使用列式存储引擎
3. 优化join_buffer_size等参数

7. 常见问题排查

7.1 数据一致性问题

当发现两边查询结果不一致时：

1. 检查字符集配置（特别是UTF8与UTF8MB4）
2. 验证浮点数精度设置
3. 确认时间戳时区一致

7.2 性能异常排查

DuckDB查询变慢的可能原因：

1. 内存不足导致溢出到磁盘
2. 统计信息过期（执行ANALYZE）
3. 存在数据倾斜（使用PRAGMA explain查看）

8. 测试数据全集

完整测试结果汇总表：

这个对比结果让我重新思考了数据分析架构的设计。当数据量超过千万级时，列式存储引擎的优势呈指数级增长。不过在实际项目中，我们最终采用了混合架构——用MySQL处理事务，用DuckDB加速分析，通过定时同步实现两全其美。