轻量级内存数据库 [英] Lightweight in-memory database

问题描述

我们的需求之一是创建一个时态内存数据库，然后执行各种插入/选择/更新.

One of our needs is to create an temporal in-memory database, to then perform various inserts/selects/updates.

一目了然，SQLite 满足了我们的所有需求.可以简单地建立与内存中 SQLite 数据库的连接:

At a glance SQLite satisfied all our needs. Connection to an in-memory SQLite DB can be established as simple as:

class SQLiteBase < ActiveRecord::Base
  self.abstract_class = true

  establish_connection(adapter:  'sqlite3', database: ':memory:')
end

不久前，我们开始研究一些性能问题，结果发现我们需要将数据批量加载(特别是批量插入)到我们的 SQLite 表中(请参阅 这些 基准).

A while ago we've started looking into some performace issues when it turned out that we need to perform bulk-loading (specifically, bulk INSERTs) of data into our SQLite tables (see these benchmarks).

不幸的是，SQLite 似乎不支持批量插入.

Unfortunately, it looks like SQLite doesn't support bulk INSERTs.

那么是否有其他基于 SQL 的轻量级内存数据库支持批量插入?

如果没有这样的 - 有没有办法利用重量级数据库，如 PostreSQL(MySQL 或任何其他主要参与者)作为内存数据库?

If there are no such – is there a way to utilize heavyweight databases such as PostreSQL (MySQL or any other major player) as in-memory database?

如果 postresql/mysql 不是一个可行的方法 – 是否还有其他 C 语言高度优化的数据结构在其之上带有查询语言?(有/没有红宝石绑定).

If postresql/mysql is not a way-to-go – are there any other C-heavily-optimized data structures with a query language on top of it? (with/without a ruby binding).

推荐答案

首先，SQLite 确实支持使用以下方法进行批量插入:

First of all, SQLite does support bulk inserts using following methods:

• 穷人替代多值插入:

• Poor man's replacement for multi-valued INSERT:

INSERT INTO mytable (a,b,c) SELECT 1 a, 2 b, 'x' c 
                  UNION ALL SELECT 2,   5,   'y' 
                  UNION ALL SELECT 3,   7,   'z'
              ...

真正的多值插入，自 SQLite 3.7.11 起支持:

INSERT INTO mytable (a,b,c) VALUES (1,2,'x'),
                                   (2,5,'y'),
                                   (3,7,'z');

使用交易:

Using transactions:

BEGIN;
INSERT INTO mytable (a,b,c) VALUES (1,2,'x');
INSERT INTO mytable (a,b,c) VALUES (2,5,'y');
INSERT INTO mytable (a,b,c) VALUES (3,7,'z');
COMMIT;

如果你问如何将这些翻译成 Ruby 或 Ruby on Rails - 我承认，我不知道，但我想这应该是可能的.

If you ask how to translate these into Ruby or Ruby on Rails - I admit, I have no idea, but I guess it should be possible.

但是，即使 SQLite 不支持这些方法，对于内存数据库来说，这些都不重要 - 因为如果它真的全部在内存中，插入速度不应该真正取决于您是逐行插入还是逐行插入作为一笔交易.您的速度限制实际上只是原始内存复制带宽.

But, even if SQLite did not support these methods, for in-memory database nothing of this should really matter - because if it is really all in-memory, insert speed should not really depend whether you insert rows one by one or as one transaction. Your speed limit is really just raw memory copy bandwidth.

这篇关于轻量级内存数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！