最近研究了一下duckdb,最初由于使用的C++版本的API,感觉性能很差,于是打算做一下性能测试,同时也把之前使用过的Mysql和Sqlite添加进来一起进行测试比较。关于如何使用duckdb,可以参阅我的前一篇博客: 使用Duckdb
测试代码我提交到了Gitee上面,所有人可见,感兴趣的可以看一下:Gitee-DBPerformance
测试程序提交到csdn资源里面了,有编译好的Windows和linux执行程序,同时包含配置文件和测试数据:测试程序
我这边使用的测试数据是上海期货交易所一天的行情Tick数据,总计是677万多条。由于数据量较大,我这边仅创建了主键,没有创建其它索引。之前有创建两个额外的索引,同时那会还使用的是duckdb C++版本的API,于是乎,我在执行写入数据时发现写入速度成加速下降,最终写了一超过14个小时,才写到500万条数据的样子。
关于mysql,我这边为了提高mysql的性能,使用的是MyISAM 引擎。
我这边测试从创建表开始,首先是将tick数据写入表中,然后根据主键字段的子集(主键是四个字段,我取了其中的三个)进行查询10次,然后对全表进行一次查询。
测试环境:
Windows:
Windows 11,i7-12700,32G内存,普通硬盘
Linux:
Ubuntu 22.04,虚拟机,普通硬盘
写入性能对比:
我这边在写入处理时对SQL进行了拼接,使用的是批量写入,单条SQL写入大约3万条数据,而duckdb则使用其提供的Appender进行写入。
在Windows下写入耗时对比:
20240812-13:28:43.559 23784 INFO WriteToDuckdb:19559 ms, RecordCount:6779139 ---DuckdbTest.cpp:106[WriteToDuckdb]
20240812-13:30:49.047 23784 INFO WriteToSqlitebatchCost:119945 ms, RecordCount:6779139 ---SqliteTest.cpp:106[WriteToSqliteBatch]
20240812-13:33:26.137 23784 INFO WriteToMysqlBatchCost:152420 ms, RecordCount:6779139 ---MysqlTest.cpp:92[WriteToMysqlBatch]
Linux下写入耗时对比:
20240812-14:52:06.566 1991133120 INFO WriteToDuckdb:12350 ms, RecordCount:6779139 ---DuckdbTest.cpp:106[WriteToDuckdb]
20240812-14:53:28.609 1991133120 INFO WriteToSqlitebatchCost:77640 ms, RecordCount:6779139 ---SqliteTest.cpp:106[WriteToSqliteBatch]
20240812-14:56:14.819 1991133120 INFO WriteToMysqlBatchCost:158633 ms, RecordCount:6779139 ---MysqlTest.cpp:92[WriteToMysqlBatch]
可以发现在duckdb使用CAPI时,其写入性能是遥遥领先,sqlite次之,mysql最慢。
查询性能对比:
均是通过SQL进行查询,使用主键的子集,单次查询记录数平均1万条。
在Windows下查询耗时对比:
20240812-13:28:43.567 23784 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:3 ms, RecordCount:9340 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.574 23784 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:11831 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.578 23784 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:0 ms, RecordCount:2799 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.584 23784 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:3 ms, RecordCount:15019 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.594 23784 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:5 ms, RecordCount:21515 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.599 23784 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:1 ms, RecordCount:5115 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.603 23784 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:2 ms, RecordCount:9451 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.609 23784 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:2 ms, RecordCount:9171 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.613 23784 INFO ReadFromDuckdbIndex QueryCost:1 ms, ParseCost:1 ms, RecordCount:6869 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:43.617 23784 INFO ReadFromDuckdbIndex QueryCost:1 ms, ParseCost:1 ms, RecordCount:8711 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-13:28:45.832 23784 INFO ReadFromDuckdb QueryCost:159 ms, ParseCost:2055 ms, RecordCount:6779139 ---DuckdbTest.cpp:170[ReadFromDuckdb]
20240812-13:30:49.066 23784 INFO ReadFromSqliteIndex QueryCost:18 ms, RecordCount:9340 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.073 23784 INFO ReadFromSqliteIndex QueryCost:6 ms, RecordCount:11831 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.075 23784 INFO ReadFromSqliteIndex QueryCost:1 ms, RecordCount:2799 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.084 23784 INFO ReadFromSqliteIndex QueryCost:9 ms, RecordCount:15019 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.098 23784 INFO ReadFromSqliteIndex QueryCost:12 ms, RecordCount:21515 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.102 23784 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:5115 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.107 23784 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9451 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.114 23784 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9171 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.118 23784 INFO ReadFromSqliteIndex QueryCost:3 ms, RecordCount:6869 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:49.123 23784 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:8711 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-13:30:52.398 23784 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:3274 ms, RecordCount:6779139 ---SqliteTest.cpp:155[ReadFromSqlite]
20240812-13:33:26.285 23784 INFO ReadFromMysqlIndex QueryCost:72 ms, ParseCost:75 ms, RecordCount:9340 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:26.402 23784 INFO ReadFromMysqlIndex QueryCost:32 ms, ParseCost:84 ms, RecordCount:11831 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:26.430 23784 INFO ReadFromMysqlIndex QueryCost:7 ms, ParseCost:19 ms, RecordCount:2799 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:26.707 23784 INFO ReadFromMysqlIndex QueryCost:44 ms, ParseCost:232 ms, RecordCount:15019 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:26.920 23784 INFO ReadFromMysqlIndex QueryCost:51 ms, ParseCost:161 ms, RecordCount:21515 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:26.969 23784 INFO ReadFromMysqlIndex QueryCost:12 ms, ParseCost:35 ms, RecordCount:5115 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:27.063 23784 INFO ReadFromMysqlIndex QueryCost:25 ms, ParseCost:66 ms, RecordCount:9451 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:27.149 23784 INFO ReadFromMysqlIndex QueryCost:21 ms, ParseCost:63 ms, RecordCount:9171 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:27.215 23784 INFO ReadFromMysqlIndex QueryCost:17 ms, ParseCost:47 ms, RecordCount:6869 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:33:27.366 23784 INFO ReadFromMysqlIndex QueryCost:19 ms, ParseCost:131 ms, RecordCount:8711 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-13:34:34.512 23784 INFO ReadFromMysql QueryCost:17205 ms, ParseCost:49940 ms, RecordCount:6779139 ---MysqlTest.cpp:134[ReadFromMysql]
在Linux下查询耗时对比:
20240812-14:52:06.576 1991133120 INFO ReadFromDuckdbIndex QueryCost:4 ms, ParseCost:1 ms, RecordCount:9340 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.584 1991133120 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:3 ms, RecordCount:11831 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.590 1991133120 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:0 ms, RecordCount:2799 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.600 1991133120 INFO ReadFromDuckdbIndex QueryCost:7 ms, ParseCost:2 ms, RecordCount:15019 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.613 1991133120 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:6 ms, RecordCount:21515 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.620 1991133120 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:0 ms, RecordCount:5115 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.626 1991133120 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:9451 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.634 1991133120 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:1 ms, RecordCount:9171 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.638 1991133120 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:0 ms, RecordCount:6869 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:06.642 1991133120 INFO ReadFromDuckdbIndex QueryCost:1 ms, ParseCost:1 ms, RecordCount:8711 ---DuckdbTest.cpp:202[ReadFromDuckdbIndex]
20240812-14:52:10.051 1991133120 INFO ReadFromDuckdb QueryCost:457 ms, ParseCost:2951 ms, RecordCount:6779139 ---DuckdbTest.cpp:170[ReadFromDuckdb]
20240812-14:53:28.624 1991133120 INFO ReadFromSqliteIndex QueryCost:13 ms, RecordCount:9340 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.637 1991133120 INFO ReadFromSqliteIndex QueryCost:12 ms, RecordCount:11831 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.648 1991133120 INFO ReadFromSqliteIndex QueryCost:9 ms, RecordCount:2799 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.675 1991133120 INFO ReadFromSqliteIndex QueryCost:27 ms, RecordCount:15019 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.706 1991133120 INFO ReadFromSqliteIndex QueryCost:29 ms, RecordCount:21515 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.714 1991133120 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:5115 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.723 1991133120 INFO ReadFromSqliteIndex QueryCost:8 ms, RecordCount:9451 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.733 1991133120 INFO ReadFromSqliteIndex QueryCost:9 ms, RecordCount:9171 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.744 1991133120 INFO ReadFromSqliteIndex QueryCost:10 ms, RecordCount:6869 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:28.757 1991133120 INFO ReadFromSqliteIndex QueryCost:11 ms, RecordCount:8711 ---SqliteTest.cpp:206[ReadFromSqliteIndex]
20240812-14:53:34.979 1991133120 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:6219 ms, RecordCount:6779139 ---SqliteTest.cpp:155[ReadFromSqlite]
20240812-14:56:15.015 1991133120 INFO ReadFromMysqlIndex QueryCost:142 ms, ParseCost:53 ms, RecordCount:9340 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.076 1991133120 INFO ReadFromMysqlIndex QueryCost:23 ms, ParseCost:36 ms, RecordCount:11831 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.099 1991133120 INFO ReadFromMysqlIndex QueryCost:9 ms, ParseCost:11 ms, RecordCount:2799 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.176 1991133120 INFO ReadFromMysqlIndex QueryCost:29 ms, ParseCost:46 ms, RecordCount:15019 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.320 1991133120 INFO ReadFromMysqlIndex QueryCost:64 ms, ParseCost:78 ms, RecordCount:21515 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.356 1991133120 INFO ReadFromMysqlIndex QueryCost:17 ms, ParseCost:16 ms, RecordCount:5115 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.413 1991133120 INFO ReadFromMysqlIndex QueryCost:23 ms, ParseCost:33 ms, RecordCount:9451 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.456 1991133120 INFO ReadFromMysqlIndex QueryCost:12 ms, ParseCost:29 ms, RecordCount:9171 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.488 1991133120 INFO ReadFromMysqlIndex QueryCost:10 ms, ParseCost:20 ms, RecordCount:6869 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:56:15.528 1991133120 INFO ReadFromMysqlIndex QueryCost:13 ms, ParseCost:26 ms, RecordCount:8711 ---MysqlTest.cpp:185[ReadFromMysqlIndex]
20240812-14:57:54.329 1991133120 INFO ReadFromMysql QueryCost:12931 ms, ParseCost:85867 ms, RecordCount:6779139 ---MysqlTest.cpp:134[ReadFromMysql]
Sqlite 由于执行 sqlite3_step 与数据的解析是交替进行的,所以提供的是一个整体的查询耗时数据。另外,由于Windows平台本身提供的系统时钟在毫秒级精度是不足的,其测试仅作为参考。
从整表查询来看:
Windows下:duckdb耗时大约2.2秒,sqlite是3.3秒、mysql是67秒;
Linux下:duckdb耗时大约3.4秒,sqlite是6.2秒、mysql是98秒。
从索引查询来看,duckdb平均在10ms以下,sqlite不太稳定,有一些在10ms以下,也有一些到20ms级别,而mysql则是100ms级。
总体来说,性能情况 duckdb 最优,sqlite次之,mysql最差。