Yasuraoka Mugi1. 超大数据集查询性能对比：DuckDB vs MySQL实战测评
最近在优化一个数据分析项目时，我遇到了一个典型问题：当单表数据量超过5亿条时，传统关系型数据库的查询性能开始急剧下降。经过多轮技术选型测试，最终将范围缩小到DuckDB和MySQL这两个热门选择。本文将分享完整的对比测试过程，包含从环境搭建到具体查询优化的全流程实录。
需要模型API调用？ 免费领10W Token，多模型网关一键接入 Claude、DeepSeek 等主流模型。
2. 测试环境与数据集准备
2.1 硬件配置与软件版本
测试使用阿里云ecs.g7ne.4xlarge实例：
- 16核vCPU
- 64GB内存
- 500GB ESSD云盘
- Ubuntu 22.04 LTS
对比软件版本：
- MySQL 8.0.33（默认InnoDB引擎）
- DuckDB 0.8.1
2.2 测试数据集生成
使用Python脚本生成模拟电商订单数据，包含以下字段：
python复制import pandas as pd
import numpy as np
def generate_data(rows):
return pd.DataFrame({
'order_id': np.arange(1, rows+1),
'user_id': np.random.randint(1000, 999999, rows),
'product_id': np.random.choice(['A001','B205','C307','D422'], rows),
'amount': np.round(np.random.uniform(10, 1000, rows), 2),
'order_time': pd.date_range('2020-01-01', periods=rows, freq='s'),
'payment_type': np.random.choice(['credit','debit','paypal'], rows),
'region': np.random.choice(['east','west','north','south'], rows)
})
生成5亿条记录的CSV文件（约45GB）：
bash复制df = generate_data(500_000_000)
df.to_csv('orders.csv', index=False)
3. 数据库加载与索引优化
3.1 MySQL数据加载方案
采用分批加载策略避免内存溢出：
sql复制-- 创建表结构
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
user_id INT,
product_id VARCHAR(4),
amount DECIMAL(10,2),
order_time DATETIME,
payment_type VARCHAR(10),
region VARCHAR(10)
) ENGINE=InnoDB;
-- 分批加载数据
LOAD DATA INFILE '/var/lib/mysql-files/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
关键索引配置：
sql复制CREATE INDEX idx_product ON orders(product_id);
CREATE INDEX idx_time ON orders(order_time);
ALTER TABLE orders ADD INDEX idx_composite (region, payment_type);
3.2 DuckDB数据加载方案
DuckDB直接读取CSV文件无需预处理：
sql复制-- 创建视图直接映射CSV文件
CREATE VIEW orders AS SELECT * FROM read_csv('orders.csv');
-- 或者持久化到磁盘
CREATE TABLE orders AS SELECT * FROM read_csv('orders.csv');
DuckDB的自动索引特性不需要手动创建索引，但可以通过以下方式优化：
sql复制PRAGMA enable_progress_bar;
PRAGMA threads=8;
4. 查询性能对比测试
4.1 基础查询测试
测试用例1：单产品订单统计
sql复制-- MySQL
SELECT COUNT(*), AVG(amount)
FROM orders
WHERE product_id = 'B205';
-- DuckDB
SELECT COUNT(*), AVG(amount)
FROM orders
WHERE product_id = 'B205';
测试结果：
| 数据库 | 首次执行 | 缓存后执行 |
|---|---|---|
| MySQL | 28.7s | 3.2s |
| DuckDB | 1.8s | 0.9s |
4.2 复杂分析查询
测试用例2：区域销售趋势分析
sql复制-- MySQL
SELECT
region,
payment_type,
DATE_FORMAT(order_time, '%Y-%m') AS month,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE order_time BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY region, payment_type, month
ORDER BY month, region;
-- DuckDB
SELECT
region,
payment_type,
strftime(order_time, '%Y-%m') AS month,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
WHERE order_time BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY region, payment_type, month
ORDER BY month, region;
测试结果：
| 数据库 | 执行时间 | 内存峰值 |
|---|---|---|
| MySQL | 142s | 12GB |
| DuckDB | 19s | 4GB |
5. 关键技术原理分析
5.1 DuckDB的列式存储优势
DuckDB采用列式存储引擎，在分析查询时：
- 只读取涉及列的磁盘数据
- 向量化执行引擎批量处理数据
- 自动使用SIMD指令加速计算
5.2 MySQL的优化瓶颈
- 行式存储导致全行扫描
- 即使使用索引，回表操作成本高
- 聚合计算需要临时表
5.3 内存管理差异
- MySQL：全局缓冲池管理
- DuckDB：按查询分配内存，支持内存映射文件
6. 实战优化建议
6.1 适合使用DuckDB的场景
- 单机分析型工作负载
- 需要快速原型验证的场景
- 复杂聚合查询需求
- 嵌入式应用场景
6.2 适合坚持MySQL的场景
- 高并发事务处理
- 需要完整ACID保证
- 已有成熟MySQL生态
- 多用户权限管理需求
6.3 混合架构建议
在实际项目中可以采用：
code复制应用程序 → MySQL (OLTP) → 定期ETL → DuckDB (OLAP)
7. 常见问题解决方案
7.1 DuckDB内存不足处理
当查询超出内存时：
sql复制-- 设置临时目录
SET temp_directory='/path/to/temp';
-- 启用外部排序
PRAGMA temp_directory='/path/to/temp';
7.2 MySQL大表查询优化
- 使用覆盖索引
- 分区表策略
- 预聚合物化视图
7.3 数据导入导出技巧
DuckDB高效导出：
sql复制-- 并行导出
COPY (SELECT * FROM orders) TO 'output.parquet'
WITH (FORMAT PARQUET, ROW_GROUP_SIZE 100000);
8. 性能对比总结
经过全面测试，在5亿条记录的测试环境下：
- 简单查询：DuckDB快15-20倍
- 复杂分析：DuckDB快5-8倍
- 内存占用：DuckDB低60-70%
- 加载速度：DuckDB快3-5倍
但需要注意：
- DuckDB不支持高并发写入
- MySQL的稳定性更成熟
- 事务隔离级别有差异
在实际项目中，我最终采用了DuckDB作为分析引擎+MySQL作为事务库的混合架构。对于需要实时分析的场景，通过触发器将MySQL变更同步到DuckDB内存表，取得了不错的平衡效果。