提问



我知道sqlite对于非常大的数据库文件表现不佳,即使它们受支持(曾经在sqlite网站上发表评论,说明如果你需要大于1GB的文件大小,你可能要考虑使用企业rdbms。 找不到它,可能与旧版本的sqlite有关。


然而,就我的目的而言,在想到其他解决方案之前,我想知道它到底有多糟糕。


我说的是数千兆字节的sqlite数据文件,从2GB开始。
有人对此有经验吗?任何提示/想法?

最佳参考


所以我使用sqlite对非常大的文件进行了一些测试,并得出了一些结论(至少对于我的具体应用)。


测试涉及单个sqlite文件,包含单个表或多个表。每个表有大约8列,几乎所有整数和4个索引。


想法是插入足够的数据,直到sqlite文件大约50GB。


单桌


我试图只用一个表将多行插入sqlite文件。当文件大约是7GB时(抱歉我不能具体说明行数),插入时间太长了。我估计插入我所有数据的测试需要24小时左右,但即使经过48小时。


这使我得出结论,单个非常大的sqlite表将存在插入问题,并且可能还有其他操作。


我想这并不奇怪,因为表变大,插入和更新所有索引需要更长时间。


多个表格


然后我尝试将数据按时间分成几个表,每天一个表。原始1表的数据被分成约700个表。


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


真空问题


正如i_like_caffeine所指出的,VACUUM命令是一个问题,sqlite文件越大。随着更多插入/删除操作,磁盘上文件的碎片将变得更糟,因此目标是定期VACUUM优化文件并恢复文件空间。


但是,正如文档所指出的那样,数据库的完整副本可以实现真空,需要很长时间才能完成。因此,数据库越小,此操作完成的速度就越快。[2]


结论


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


这使查询变得复杂,但对我而言,能够为这么多数据编制索引是值得的权衡。另一个优点是我可以删除整个数据库文件以丢弃一天的数据(我的常见操作)应用)。


我可能必须监控每个文件的表大小,以查看速度何时成为问题。


除了自动真空之外,似乎不是一种增量真空方法,这太糟糕了。我不能使用它,因为我的真空目标是对文件进行碎片整理(文件空间不是很大),自动真空无法做到。实际上,文档说它可能会使碎片变得更糟,所以我不得不求助于定期对文件进行全真空。[3]

其它参考1


我们在平台上使用50 GB +的DBS。没有抱怨很有效。
确保你做的一切正确!您使用预定义语句吗?
* SQLITE 3.7.3



  1. 交易

  2. 预先陈述

  3. 应用这些设置(创建数据库后立即)


    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;
    



希望这会有助于其他人,在这里工作得很好

其它参考2


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


根据SQLite Limits页面,每个数据库页面的最大大小为32K。并且数据库中的最大页数为1024 ^ 3。所以根据我的数学计算,最大尺寸为32太字节。我想你会在点击SQLite之前达到文件系统的限制![4]

其它参考3


花费大约48小时进行插入的大部分原因是因为你的索引。它非常快:


1 - 删除所有索引
2 - 做所有插入
3 - 再次创建索引

其它参考4


除了通常的建议:



  1. 批量插入的丢弃索引。

  2. 在大型交易中批量插入/更新。

  3. 调整缓冲区缓存/禁用日记/w PRAGMA。

  4. 使用64位机器(能够使用大量缓存™)。

  5. [[2014年7月添加]]使用公用表表达式(CTE)而不是运行多个SQL查询!需要SQLite版本3.8.3。



我从SQLite3的经验中学到了以下内容:[5]



  1. 为了获得最大插入速度,请不要使用任何列约束的模式。(稍后根据需要更改表您不能使用ALTER TABLE添加约束。)

  2. 优化您的架构以存储您需要的内容。有时这意味着在插入数据库之前分解表和/或甚至压缩/转换数据。一个很好的例子是将IP地址存储为(长)整数。

  3. 每个db文件一个表 - 最小化锁争用。 (如果您想拥有一个连接对象,请使用 ATTACH DATABASE 。

  4. SQLite可以在同一列中存储不同类型的数据(动态类型),并将其用于您的优势。



问/评欢迎。 ;-)

其它参考5


我认为关于sqlite扩展的主要抱怨是:



  1. 单一流程写作。

  2. 没有镜像。

  3. 没有复制。


其它参考6


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

其它参考7


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


我还没有尝试过auto_vacuum功能。如果你希望经常更新和删除数据,那么这值得关注。

其它参考8


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


但是,从SQLite的最新版本开始,不再需要这样做了。在这里阅读更多。[6]