当前位置： 首页 > article >正文

数据库性能测试2：内存数据库

article 2025/2/26 19:55:56

继上一篇 数据库性能测试：Mysql、Sqlite、Duckdb 之后，做了一下内存数据库的性能测试，这里主要比较的是 Sqlite、Duckdb以及我这边使用容器实现的一个简单内存数据库，因为Sqlite和Duckdb都支持内存数据库模式，于是打算对比一下。Redis由于是要通过网络通信交互，没有列入比较。

Duckdb使用内存模式仅需要在创建数据库时，传入的数据库名称指定null即可，Sqlie这边需要改用 sqlite3_open_v2 来创建数据库，并且需要指定 flags 参数包含 SQLITE_OPEN_MEMORY，具体创建语句如下：

sqlite3* db;
auto ret = sqlite3_open_v2(nullptr, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |SQLITE_OPEN_MEMORY, nullptr);

单主键下duckdb与sqlite写入耗时对比：

20240829-08:52:03.618 10560 INFO WriteToDuckdb:9246 ms, RecordCount:6779139		---DuckdbTest.cpp:133[WriteToDuckdb]
20240829-08:53:07.506 10560 INFO WriteToSqlitebatchCost:61116 ms, RecordCount:6779139		---SqliteTest.cpp:131[WriteToSqliteBatch]

对比前面在文件模式(参考这篇文章中的数据：数据库性能测试：Mysql、Sqlite、Duckdb )下的写入速度还是提升不少的。

单主键下duckdb与sqlite查询耗时对比：

20240829-08:52:03.624 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9340		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.632 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:3 ms, RecordCount:11831		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.636 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:0 ms, RecordCount:2799		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.643 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:3 ms, RecordCount:15019		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.654 10560 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:5 ms, RecordCount:21515		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.659 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:5115		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.665 10560 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9451		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.672 10560 INFO ReadFromDuckdbIndex QueryCost:4 ms, ParseCost:2 ms, RecordCount:9171		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.676 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:1 ms, RecordCount:6869		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:03.682 10560 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:2 ms, RecordCount:8711		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-08:52:06.062 10560 INFO ReadFromDuckdb QueryCost:356 ms, ParseCost:2023 ms, RecordCount:6779139		---DuckdbTest.cpp:197[ReadFromDuckdb]

20240829-08:53:07.514 10560 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:9340		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.520 10560 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:11831		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.522 10560 INFO ReadFromSqliteIndex QueryCost:1 ms, RecordCount:2799		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.530 10560 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:15019		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.541 10560 INFO ReadFromSqliteIndex QueryCost:10 ms, RecordCount:21515		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.545 10560 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:5115		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.550 10560 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:9451		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.555 10560 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:9171		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.559 10560 INFO ReadFromSqliteIndex QueryCost:3 ms, RecordCount:6869		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:07.563 10560 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:8711		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-08:53:10.498 10560 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:2934 ms, RecordCount:6779139		---SqliteTest.cpp:180[ReadFromSqlite]

查询性能对比文件模式下，相差也不大，duckdb的内存模式下跟文件模式下查询耗时基本持平，sqlite内存模式比文件模式每次大约快1ms（约10%-25%）的样子。

因为本次测试引入了我自己的一套内存数据库，相较于上一次的测试，本次对所有数据库增加了一个合约代码的索引，增加索引后，Duckdb的写入性能下降比较严重，而查询性能几乎没变。sqlite 在增加索引后，写入与查询性能都跟之前相差不大。

windows下写入耗时对比：

20240829-11:15:11.642 21688 INFO WriteToDuckdb:115186 ms, RecordCount:6779139		---DuckdbTest.cpp:133[WriteToDuckdb]

20240829-11:16:19.737 21688 INFO WriteToSqlitebatchCost:65104 ms, RecordCount:6779139		---SqliteTest.cpp:131[WriteToSqliteBatch]

20240829-11:16:30.411 21688 INFO WriteToMdb:7247 ms, RecordCount:6779139		---MdbTest.cpp:60[WriteToMdb]

linux下写入耗时对比：

20240829-11:06:07.588 472875264 INFO WriteToDuckdb:108009 ms, RecordCount:6779139               ---DuckdbTest.cpp:133[WriteToDuckdb]

20240829-11:07:35.848 472875264 INFO WriteToSqlitebatchCost:80476 ms, RecordCount:6779139               ---SqliteTest.cpp:131[WriteToSqliteBatch]

20240829-11:07:48.812 472875264 INFO WriteToMdb:8784 ms, RecordCount:6779139            ---MdbTest.cpp:60[WriteToMdb]

windows下查询耗时对比：

20240829-11:15:11.669 21688 INFO ReadFromDuckdbIndex QueryCost:23 ms, ParseCost:2 ms, RecordCount:9340		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.677 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:11831		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.681 21688 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:0 ms, RecordCount:2799		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.689 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:3 ms, RecordCount:15019		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.700 21688 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:5 ms, RecordCount:21515		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.706 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:5115		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.712 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9451		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.718 21688 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:2 ms, RecordCount:9171		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.722 21688 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:1 ms, RecordCount:6869		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:11.728 21688 INFO ReadFromDuckdbIndex QueryCost:2 ms, ParseCost:1 ms, RecordCount:8711		---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:15:14.253 21688 INFO ReadFromDuckdb QueryCost:508 ms, ParseCost:2017 ms, RecordCount:6779139		---DuckdbTest.cpp:197[ReadFromDuckdb]

20240829-11:16:19.743 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9340		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.749 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:11831		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.751 21688 INFO ReadFromSqliteIndex QueryCost:1 ms, RecordCount:2799		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.759 21688 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:15019		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.771 21688 INFO ReadFromSqliteIndex QueryCost:10 ms, RecordCount:21515		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.774 21688 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:5115		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.780 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9451		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.786 21688 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9171		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.789 21688 INFO ReadFromSqliteIndex QueryCost:3 ms, RecordCount:6869		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:19.794 21688 INFO ReadFromSqliteIndex QueryCost:4 ms, RecordCount:8711		---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:16:22.733 21688 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:2939 ms, RecordCount:6779139		---SqliteTest.cpp:180[ReadFromSqlite]

20240829-11:16:30.411 21688 INFO ReadFromMdb:230 us, RecordCount:9340		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:170 us, RecordCount:11831		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:35 us, RecordCount:2799		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:226 us, RecordCount:15019		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:337 us, RecordCount:21515		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.412 21688 INFO ReadFromMdb:189 us, RecordCount:5115		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.413 21688 INFO ReadFromMdb:192 us, RecordCount:9451		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.413 21688 INFO ReadFromMdb:120 us, RecordCount:9171		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.413 21688 INFO ReadFromMdb:193 us, RecordCount:6869		---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:16:30.414 21688 INFO ReadFromMdb:88 us, RecordCount:8711		---MdbTest.cpp:92[ReadFromMdbIndex]

linux下查询耗时对比：

20240829-11:06:07.872 472875264 INFO ReadFromDuckdbIndex QueryCost:279 ms, ParseCost:4 ms, RecordCount:9340             ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.879 472875264 INFO ReadFromDuckdbIndex QueryCost:4 ms, ParseCost:2 ms, RecordCount:11831              ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.885 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:0 ms, RecordCount:2799               ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.894 472875264 INFO ReadFromDuckdbIndex QueryCost:6 ms, ParseCost:3 ms, RecordCount:15019              ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.909 472875264 INFO ReadFromDuckdbIndex QueryCost:9 ms, ParseCost:4 ms, RecordCount:21515              ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.917 472875264 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:1 ms, RecordCount:5115               ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.924 472875264 INFO ReadFromDuckdbIndex QueryCost:5 ms, ParseCost:1 ms, RecordCount:9451               ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.929 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:9171               ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.935 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:6869               ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:07.940 472875264 INFO ReadFromDuckdbIndex QueryCost:3 ms, ParseCost:1 ms, RecordCount:8711               ---DuckdbTest.cpp:229[ReadFromDuckdbIndex]
20240829-11:06:14.760 472875264 INFO ReadFromDuckdb QueryCost:1899 ms, ParseCost:4920 ms, RecordCount:6779139           ---DuckdbTest.cpp:197[ReadFromDuckdb]

20240829-11:07:35.860 472875264 INFO ReadFromSqliteIndex QueryCost:10 ms, RecordCount:9340              ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.873 472875264 INFO ReadFromSqliteIndex QueryCost:12 ms, RecordCount:11831             ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.876 472875264 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:2799               ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.885 472875264 INFO ReadFromSqliteIndex QueryCost:8 ms, RecordCount:15019              ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.898 472875264 INFO ReadFromSqliteIndex QueryCost:12 ms, RecordCount:21515             ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.902 472875264 INFO ReadFromSqliteIndex QueryCost:2 ms, RecordCount:5115               ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.910 472875264 INFO ReadFromSqliteIndex QueryCost:7 ms, RecordCount:9451               ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.916 472875264 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:9171               ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.920 472875264 INFO ReadFromSqliteIndex QueryCost:3 ms, RecordCount:6869               ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:35.927 472875264 INFO ReadFromSqliteIndex QueryCost:5 ms, RecordCount:8711               ---SqliteTest.cpp:231[ReadFromSqliteIndex]
20240829-11:07:39.712 472875264 INFO ReadFromSqlite QueryCost:0 ms, ParseCost:3784 ms, RecordCount:6779139              ---SqliteTest.cpp:180[ReadFromSqlite]

20240829-11:07:48.813 472875264 INFO ReadFromMdb:248 us, RecordCount:9340               ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:136 us, RecordCount:11831              ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:41 us, RecordCount:2799                ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:179 us, RecordCount:15019              ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:298 us, RecordCount:21515              ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:58 us, RecordCount:5115                ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.813 472875264 INFO ReadFromMdb:93 us, RecordCount:9451                ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.814 472875264 INFO ReadFromMdb:96 us, RecordCount:9171                ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.814 472875264 INFO ReadFromMdb:65 us, RecordCount:6869                ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:48.814 472875264 INFO ReadFromMdb:90 us, RecordCount:8711                ---MdbTest.cpp:92[ReadFromMdbIndex]
20240829-11:07:49.315 472875264 INFO ReadFromMdb:498109 us, RecordCount:6779139         ---MdbTest.cpp:75[ReadFromMdb]

因为内存数据库在查询时响应时间均小于1ms，所以这里改用了微秒（ms）。

结论：

在内存模式下（相较与文件模式），duckdb与sqlite的写入性能都有较大的提升，而查询性能提升不大，可能是因为文件模式下，两者都会有缓存的原因。我这边自己实现的简单内存数据库由于没有SQL的一些包袱，也仅支持一些简单的增删改查操作，性能还是要高不少（1-2个数量级）。

duckdb在增加索引的情况下，写入性能直线（指数）下降，而其对查询提升不大，建议在使用时少用索引。


http://www.kler.cn/a/286793.html

相关文章：

  • 基于 Android Studio 实现的 记账本-MySQL版
  • [C#]国密SM2算法加解密字符串加密解密文件
  • 研究生深度学习入门的十天学习计划------第五天
  • 小琳python课堂：Python核心概念 类和对象
  • 折腾 Quickwit，Rust 编写的分布式搜索引擎 - 从不同的来源摄取数据
  • Django+Vue农产品销售系统的设计与实现
  • 理解大模型中的Cursor技术：优化长文本推理的前沿方案
  • 微服务集成 Seata
  • 【 html+css 绚丽Loading 】000030 灵文闪烁符
  • 【Selenium】UI自动化实践——输入验证码登录
  • Mysql基础练习题 1084.销售分析3 （力扣）
  • 数据结构--初步了解（抽象分级）
  • 【专题】2024年中国AI人工智能基础数据服务研究报告合集PDF分享（附原数据表）
  • 架构设计（13）安全架构设计理论
  • QT +ffmpeg-4.2.2-win64-shared 拉取 RTMP/http-flv 流播放
  • 模型 冯/诺依曼思维模型
  • 实习的一点回顾单元测试
  • 网络爬虫调研报告
  • Force Yc 第九引导公告页HTML源码
  • Codeforces Round 969 (Div. 2)