在超大数据集下 DuckDB 与 MySQL 查询速度对比
本文介绍在本地同样配置的PC中，使用同样体量的数据集，对比DuckDB与MySQL的对不同SQL语句场景地查询速度表现。结果是大多数业务需求场景下，DuckDB的查询速度基本都比MySQL快10倍左右。
PC配置：24核64GB内存
mysql版本：8.0.12
duckdb版本：1.5.1
python版本：3.12.10
数据集说明
数据集获取：https://tooli.fun/resource_detail?resource_id=330
- 核心数据表：stock_daily
- 数据行数：14150202
- 导出CSV大小: 1.15GB
- 数据表结构：
CREATE TABLE `stock_daily` (
`ts_code` varchar(10) NOT NULL COMMENT '股票代码',
`trade_date` datetime NOT NULL COMMENT '交易日期',
`open` float DEFAULT NULL COMMENT '开盘价',
`high` float DEFAULT NULL COMMENT '最高价',
`low` float DEFAULT NULL COMMENT '最低价',
`close` float DEFAULT NULL COMMENT '收盘价',
`pre_close` float DEFAULT NULL COMMENT '昨收价',
`change` float DEFAULT NULL COMMENT '涨跌额',
`pct_chg` float DEFAULT NULL COMMENT '涨跌幅(未复权)',
`vol` float DEFAULT NULL COMMENT '成交量(手)',
`amount` float DEFAULT NULL COMMENT '成交额(千元)',
`adj_factor` float DEFAULT NULL COMMENT '复权因子(前复权QFQ=当日收盘价×当日复权因子/最新复权因子，后复权HFQ=当日收盘价×当日复权因子)',
PRIMARY KEY (`ts_code`,`trade_date`),
KEY `idx_trade_date` (`trade_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 维度表：stock_base
- 数据行数：5362
- 导出CSV大小: 1.12MB
- 数据表结构：
CREATE TABLE `stock_base` (
`ts_code` varchar(10) NOT NULL COMMENT 'TS代码',
`symbol` varchar(10) DEFAULT NULL COMMENT '股票代码',
`name` varchar(64) DEFAULT NULL COMMENT '股票名称',
`area` varchar(64) DEFAULT NULL COMMENT '地域',
`industry` varchar(64) DEFAULT NULL COMMENT '所属行业',
`fullname` varchar(255) DEFAULT NULL COMMENT '股票全称',
`enname` varchar(255) DEFAULT NULL COMMENT '英文全称',
`cnspell` varchar(64) DEFAULT NULL COMMENT '拼音缩写',
`market` varchar(64) DEFAULT NULL COMMENT '市场类型（主板/创业板/科创板/CDR）',
`exchange` varchar(64) DEFAULT NULL COMMENT '交易所代码',
`curr_type` varchar(16) DEFAULT NULL COMMENT '交易货币',
`list_status` varchar(16) DEFAULT NULL COMMENT '上市状态 L上市 D退市 P暂停上市',
`list_date` datetime DEFAULT NULL COMMENT '上市日期',
`delist_date` datetime DEFAULT NULL COMMENT '退市日期',
`is_hs` varchar(16) DEFAULT NULL COMMENT '是否沪深港通标的，N否 H沪股通 S深股通',
PRIMARY KEY (`ts_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 维度表：stock_company
- 数据行数：5146
- 导出CSV大小: 9.9MB
- 数据表结构：
CREATE TABLE `stock_company` (
`ts_code` varchar(10) NOT NULL COMMENT '股票代码',
`exchange` varchar(10) DEFAULT NULL COMMENT '交易所代码 ，SSE上交所 SZSE深交所',
`chairman` varchar(64) DEFAULT NULL COMMENT '法人代表',
`manager` varchar(64) DEFAULT NULL COMMENT '总经理',
`secretary` varchar(64) DEFAULT NULL COMMENT '董秘',
`reg_capital` float DEFAULT NULL COMMENT '注册资本',
`setup_date` datetime DEFAULT NULL COMMENT '注册日期',
`province` varchar(64) DEFAULT NULL COMMENT '所在省份',
`city` varchar(64) DEFAULT NULL COMMENT '所在城市',
`introduction` text COMMENT '公司介绍',
`website` varchar(255) DEFAULT NULL COMMENT '公司主页',
`email` varchar(64) DEFAULT NULL COMMENT '电子邮件',
`office` varchar(255) DEFAULT NULL COMMENT '办公室',
`business_scope` text COMMENT '经营范围',
`employees` int(11) DEFAULT NULL COMMENT '员工人数',
`main_business` text COMMENT '主要业务及产品',
PRIMARY KEY (`ts_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
SQL运行对比
SQL：统计数据量
-- MySQL查询，耗时：8.038秒
select COUNT(*) from stock_daily;
-- DuckDB 查询CSV文件 耗时：0.452秒
select count(*) from 'e:/data/data_set/stock/stock_daily.csv';
SQL：按实际股票分组统计每只股票的交易天数
-- MySQL查询，耗时：2.627秒
SELECT ts_code,COUNT(*) FROM stock_daily GROUP BY ts_code;
-- DuckDB查询CSV文件， 耗时：0.478秒
SELECT ts_code,COUNT(*) FROM 'e:/data/data_set/stock/stock_daily.csv' GROUP BY ts_code;
SQL：按实际股票分组统计每只股票的交易天数，并按照天数排序
-- MySQL查询，耗时：13.16秒
SELECT ts_code,COUNT(*) AS days FROM stock_daily GROUP BY ts_code ORDER BY days;
-- DuckDB查询，耗时：0.495秒
SELECT ts_code,COUNT(*) AS days FROM 'e:/data/data_set/stock/stock_daily.csv' GROUP BY ts_code ORDER BY days;
SQL：查询2020年5月7号上涨的股票数量（收盘价>开盘价)
-- MySQL查询, 耗时：4.194秒
SELECT COUNT(*) AS up FROM stock_daily WHERE trade_date='2020-05-07 00:00:00' and `close` > `open`;
-- DuckDB查询, 耗时：0.511秒
result = conn.execute("""
SELECT COUNT(*) AS up FROM read_csv(
'e:/data/data_set/stock/stock_daily.csv',
auto_detect = true,
nullstr = ['\\N']
)
WHERE trade_date = DATE '2020-05-07' and close>open;
""").fetchall()
SQL：查询2020年5月7号上涨的股票数量，按照行业分组
特点：读取2个csv文件，left join 一次
-- MySQL查询, 耗时：4.705秒
SELECT b.industry,COUNT(*) AS up FROM stock_daily a LEFT JOIN stock_base b ON a.ts_code = b.ts_code WHERE a.trade_date='2020-05-07 00:00:00' AND a.`close` > a.`open` GROUP BY b.industry ORDER BY up DESC;
-- DuckDB查询, 耗时：0.596秒
SELECT b.industry,COUNT(*) AS up FROM read_csv(
'e:/data/data_set/stock/stock_daily.csv',
auto_detect =true,
nullstr = ['\\N']
) a leftjoin
read_csv(
'e:/data/data_set/stock/stock_base.csv',
auto_detect =true,
nullstr = ['\\N']
) b
ON a.ts_code = b.ts_code
WHERE a.trade_date =DATE'2020-05-07'AND a.close>a.open
GROUPBY b.industry ORDERBY up DESC;
SQL：查询2020年5月7号上涨的股票数量，按交易所和行业分组
特点：读取3个csv文件，left join 两次，
-- MySQL查询, 耗时：4.705秒
SELECT c.exchange,b.industry,COUNT(*) AS up FROM stock_daily a LEFT JOIN stock_base b ON a.ts_code = b.ts_code LEFT JOIN stock_company c ON a.ts_code=c.ts_code WHERE a.trade_date='2020-05-07 00:00:00' AND a.close>a.open GROUP BY c.exchange,b.industry ORDER BY up DESC;
--DuckDB查询, 耗时：0.883秒
SELECT c.exchange,b.industry,COUNT(*) AS up FROM read_csv(
'e:/data/data_set/stock/stock_daily.csv',
auto_detect = true,
nullstr = ['\\N']
) a
left join read_csv(
'e:/data/data_set/stock/stock_base.csv',
auto_detect = true,
nullstr = ['\\N']
) b ON a.ts_code = b.ts_code
left join read_csv(
'e:/data/data_set/stock/stock_company.csv',
auto_detect = true,
nullstr = ['\\N']
) c
ON a.ts_code = c.ts_code
WHERE a.trade_date = DATE '2020-05-07' AND a.close>a.open
GROUP BY c.exchange,b.industry ORDER BY up DESC;
结果说明
以上例子都是使用DuckDB直接查询CSV文件。同时也能直接查询parquet文件。读取Excel文件需要安装相应的扩展。
因为测试的机器是机械硬盘，所以效果很明显，如果是固态硬盘，那么在mysql能触发索引的情况下，两者的差距有所缩小。
如果将文件内容创建好物化视图，那么查询速度能更快一个量级。
例如：
-- 创建物化视图
create table view_stock_daily as select * from 'e:/data/data_set/stock/stock_daily.csv';
-- 查询物化视图
select * from view_stock_daily当然，本文对比的是DuckDB和MySQL数据查询能力，正所谓术业有专攻，MySQL的OLTP能力一直是开源界的TOP队列里的。
DuckDB和MySQL比较，有点像战斗机和客机比较飞行速度。数据是真实的但也是局部的。
本文的对比逻辑仅仅是文章介绍DuckDB能力的一个抓点。让读者能在一个标准认知的场景里清晰地理解DuckDB的能力。
浙公网安备 33010602011771号