对于非常大的数据库文件，sqlite的性能特征是什么?

2020年更新，大约11年后，这个问题被发布，后来被关闭，阻止了更新的答案。

这里写的几乎都过时了。曾几何时，sqlite被限制在内存容量或2gb的存储空间(32位)或其他流行的数字…那是很久以前的事了。

官方限制列在这里. cn。实际上，只要有可用的存储空间，sqlite就可能工作。它适用于比内存大的数据集，它最初是在内存很薄的时候创建的，从一开始就非常重要。

存储100gb的数据绝对没有问题。它可能可以很好地存储TB，但最终这是你需要质疑SQLite是否是最好的工具，你可能想要一个完整的数据库的功能(远程客户端，并发写入，只读副本，分片等…)

原:

我知道即使sqlite支持超大的数据库文件，sqlite也不能很好地处理它们(sqlite网站上曾经有一条评论说，如果你需要超过1GB的文件大小，你可能要考虑使用企业rdbms。再也找不到它了，可能与sqlite的旧版本有关)。

然而，出于我的目的，我想在考虑其他解决方案之前了解它到底有多糟糕。

我说的是千兆字节范围内的sqlite数据文件，从2GB开始。 有人有这方面的经验吗?任何建议/想法吗?< / p >

195687 次浏览
小开

我认为关于sqlite缩放的主要抱怨是:

  1. 单进程写。
  2. 没有镜像。
  3. 没有复制。
小开

我创建了最大3.5GB的SQLite数据库，没有明显的性能问题。如果我没记错的话，我认为SQLite2可能有一些下限，但我不认为SQLite3有任何这样的问题。

根据SQLite的限制页，每个数据库页的最大大小是32K。数据库中的最大页面是1024^3。根据我的计算，最大大小是32tb。我认为在达到SQLite的限制之前，您将达到您的文件系统的限制!

小开

我在使用vacuum命令时遇到过大型sqlite文件的问题。

我还没有尝试auto_vacuum特性。如果您希望经常更新和删除数据，那么这值得一看。

小开
最佳答案

因此，我使用sqlite对非常大的文件进行了一些测试，并得出了一些结论(至少对于我的特定应用程序)。

测试涉及单个sqlite文件，其中包含单个表或多个表。每个表大约有8列，几乎都是整数，还有4个索引。

我们的想法是插入足够的数据，直到sqlite文件达到50GB左右。

单表

我尝试将多行插入到只有一个表的sqlite文件中。当文件大约7GB时(对不起，我不能具体说明行数)，插入花费的时间太长了。我曾估计插入所有数据的测试需要24小时左右，但即使在48小时后也没有完成。

这使我得出结论，一个非常大的sqlite表在插入和其他操作方面都会有问题。

我想这并不奇怪，随着表变大，插入和更新所有索引需要更长的时间。

多个表

然后，我尝试将数据按时间分割到几个表中，每天一个表。原始1个表的数据被分割为~700个表。

这种设置没有插入问题，随着时间的推移，它不会花费更长的时间，因为每天都会创建一个新表。

真空问题

正如i_like_caffeine所指出的，sqlite文件越大，VACUUM命令就会产生问题。随着插入/删除操作的增加，磁盘上文件的碎片会变得更糟，因此目标是定期使用VACUUM来优化文件并恢复文件空间。

然而，正如文档所指出的那样，数据库的完整副本是用来做真空的，需要很长时间才能完成。因此，数据库越小，该操作完成的速度就越快。

结论

对于我的特定应用程序，我可能会将数据分割到几个db文件中，每天一个，以获得最佳的真空性能和插入/删除速度。

这使查询变得复杂，但对我来说，能够索引这么多数据是值得的。另一个优点是，我可以删除整个db文件来删除一天的数据(这是我的应用程序的常见操作)。

我可能还要监视每个文件的表大小，以查看速度何时会成为问题。

除了汽车真空之外，似乎没有其他增量真空方法，这太糟糕了。我不能使用它，因为我对真空的目标是整理文件(文件空间不是大问题)，这是自动真空做不到的。事实上，文档表明这可能会使碎片化更糟，所以我必须定期对文件进行完全真空处理。

小开

在SQLite文档中曾经有一个声明，数据库文件的实际大小限制是几十GB:s。这主要是因为当您启动事务时，SQLite需要“分配脏页面的位图”。因此，数据库中每MB需要256字节的RAM。插入一个50gb的db文件需要一个巨大的(2^8)*(2^10)=2^18=256 MB的RAM。

但是在SQLite的最新版本中，不再需要这样做了。阅读更多在这里。

小开

>花费48小时进行插入的主要原因是您的索引。它是令人难以置信的快:

1 -删除所有索引 2 -做所有的插入 3 -重新创建索引

小开
我们在我们的平台上使用50gb +的DBS。没有抱怨很有效。 确保你做的每件事都是正确的!您是否使用预定义语句? * SQLITE 3.7.3 < / p >
  1. 交易
  2. 预先发表的声明
  3. 应用这些设置(在你创建DB之后)

    PRAGMA main.page_size = 4096;
PRAGMA main.cache_size=10000;
PRAGMA main.locking_mode=EXCLUSIVE;
PRAGMA main.synchronous=NORMAL;
PRAGMA main.journal_mode=WAL;
PRAGMA main.cache_size=5000;

Hope this will help others, works great here

小开
我有一个7GB的SQLite数据库。 使用内部连接执行特定查询需要2.6秒 为了加快速度，我尝试添加索引。根据我添加的索引，有时查询会下降到0.1秒，有时会上升到7秒。 我认为在我的情况下的问题是，如果一个列是高度重复的，然后添加索引降低性能:(

小开

除了通常的建议:

  1. 为批量插入删除索引。
  2. 在大型事务中批量插入/更新。
  3. 调优缓冲区缓存/禁用日志/w PRAGMAs。
  4. 使用64位机器(以便能够使用大量缓存™)。
  5. [2014年7月添加]使用公共表表达式(CTE)代替运行多个SQL查询!需要SQLite 3.8.3版。

我从SQLite3的经验中学到了以下几点:

  1. 为了获得最大的插入速度，不要使用带有任何列约束的模式。你不能在ALTER TABLE中添加约束。
  2. 优化您的模式以存储所需的内容。有时，这意味着在插入到数据库之前分解表和/或甚至压缩/转换数据。一个很好的例子是将IP地址存储为(长)整数。
  3. 每个db文件一个表-以减少锁争用。(如果你想要一个单独的连接对象，使用附加数据库。
  4. SQLite可以在同一列中存储不同类型的数据(动态类型)，充分利用这一点。

问题/欢迎发表评论。: -)


提交答案