SQLite中的内存数据库的优点 [英] Advantages of an in-memory Database in SQLite

问题描述

我今天从一本关于SQLite的书中读到了关键字:memory:"，但它只说明了它的含义，用法和解释过短.因此，我在这里搜索了更多信息，但无法获取SQLite的特定信息.

1. ':memory:'模式有什么优势? (我什么时候需要这个?)

2. 内存数据库的性能更快?

3. 我是否仍需要在内存数据库中使用事务?

解决方案

一个 SQLite内存数据库的主要优势是性能:与其读写磁盘，不如将整个数据库保留在内存中.内存比磁盘快得多.使用旋转磁盘或大量IO负载的服务器，您将看到最大的性能改进，而使用SSD时，性能得到的最大改进.

但是，这并不是写得不好的查询和表的灵丹妙药.在使用内存数据库提高性能之前，请确保优化表设计，查询和索引.

主要缺点是一旦关闭进程数据库就消失了.而且数据库不能大于可用内存.

提交可能会更快，因为不需要写入磁盘，因此自动提交模式可能会更快，但仍应将事务用于数据完整性目的.

请注意，不会太大的临时SQLite数据库可能会存储在内存中.

由于其缺点，并且由于您的内存比存储少得多，因此在提交内存数据库之前，请尝试使用临时数据库.这是通过使用''作为数据库文件名来完成的.这将写入临时文件，但是将工作缓冲在内存缓存中.这是两全其美的方法，您可以在不占用过多内存的情况下提高性能.

即使为每个临时数据库分配了一个磁盘文件，实际上，临时数据库通常也驻留在内存中的寻呼机缓存中，因此，:memory:"创建的纯内存数据库与由空文件名创建的临时数据库.唯一的区别是，:memory:"数据库必须始终保留在内存中，而如果数据库变大或SQLite受到内存压力，则临时数据库的某些部分可能会刷新到磁盘.

对应用程序进行配置和基准测试，以确保它可以提高性能，考虑优化查询并添加索引是否更好，并且确保数据消失也可以.

I read about the keyword ":memory:" from a book on SQLite today but it only says what it is, how to use and the explanations were too short. So I searched for more information here, but couldn't get SQLite specific info.

1. What advantages does ':memory:' mode have? (When do I need this?)

2. In-memory database's performance is faster?

3. Do I still need to use transactions on an In-memory database?

解决方案

A SQLite in-memory database's primary advantage is performance: rather than reading and writing to disk, it will keep the whole database in memory. Memory is much faster than disk. You'll see the biggest performance improvement with a spinning disk or a heavily IO loaded server, and less with an SSD.

However, this isn't a panacea for badly written queries and tables. Before you reach for an in-memory database to improve performance, be sure to optimize your table design, queries and indexes.

The main disadvantages are once the process closes the database is gone. And the database cannot be bigger than available memory.

Commits may be faster since there's no need to write to disk, so autocommit mode might be faster, but transactions should still be used for data integrity purposes.

Note that a temporary SQLite database that doesn't get too big will probably be stored in memory.

Because of its drawbacks, and because you have much less memory than storage, before committing to an in-memory database try a temporary database instead. This is done by using '' for the database filename. This will write to a temp file, but buffer the work in a memory cache. It is the best of both worlds, you get improved performance without using too much memory.

Even though a disk file is allocated for each temporary database, in practice the temporary database usually resides in the in-memory pager cache and hence is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename. The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if database becomes large or if SQLite comes under memory pressure.

Profile and benchmark your application to be sure it will result in a performance improvement, consider whether it would be better to optimize your queries and add indexes instead, and be sure it's ok if your data disappears.

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