具有非常大的数据库文件的sqlite的性能特征是什么？

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

但是,出于我的目的,我想在考虑其他解决方案之前了解它到底有多糟糕.

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

1> Snazzer..：

---

所以我使用sqlite对非常大的文件进行了一些测试,并得出了一些结论(至少对于我的特定应用程序而言).

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

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

单表

我试图在一个只有一个表的sqlite文件中插入多行.当文件大约7GB(抱歉我不能具体说明行数)时,插入时间太长了.我估计我插入所有数据的测试需要24小时左右,但即使在48小时后也没有完成.

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

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

多个表格

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

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

真空问题

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

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

结论

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

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

我可能还需要监控每个文件的表大小,以查看速度何时成为问题.

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

---

我很好奇,你的所有INSERTS都在交易中吗？

---

是的,插入是每批交易10000封邮件完成的.

---

你用了什么文件系统？如果ext {2,3,4},data =设置是什么,启用了日记功能？除了io模式,sqlite刷新到磁盘的方式可能很重要.

---

非常有用的信息.纯粹的推测,但我想知道是否可以使用新的备份api每天创建一个非碎片版本的数据库,并避免运行VACUUM.

---

我主要在windows上测试,所以无法评论linux上的行为.

---

如果你还有它,你可以发布你使用的代码/ sql吗？

2> 小智..：

---

我们在平台上使用50 GB +的DBS.没有抱怨很有效.确保你做的一切正确!您使用的是预定义语句吗？*SQLITE 3.7.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;

希望这会有所帮助,在这里工作得很好

---

@Alex,为什么有两个PRAGMA main.cache_size = 5000;？

---

最近在160GB范围内使用dbs测试,效果也很好.

---

不要盲目地应用这些优化.特别是synchronous = NORMAL不是崩溃安全的.即,即使没有磁盘故障,在正确的时间进程崩溃也可能破坏您的数据库.http://www.sqlite.org/pragma.html#pragma_synchronous

---

@Alex你可以解释一下这些值以及'和默认值之间的区别吗？

---

还有`PRAGMA main.temp_store = MEMORY;`.

---

你在50G和160G数据库@Snazzer和@Alex中拥有多少个表？

3> Paul Lefebvr..：

---

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

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

---

根据您执行的操作，尝试删除8G sqlite数据库中的3000行，您需要花费足够的时间来酿造一台不错的法国印刷机，哈哈

4> 小智..：

---

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

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

---

@Snazzer在类似情况下我们使用了"累加器"表:每天一次我们将累积的行从累加器表移动到单个事务中的主表.在需要的地方,视图负责将两个表作为单个表格呈现.

---

这是众所周知的...但是对于长时间运行的过程,您不会定期删除索引来重建它们,尤其是当您要查询它们进行工作时.这是在必须从头开始重建sqlite db时采用的方法,在完成所有插入后创建索引.

---

另一个选项是保留索引,但在插入数据之前按索引顺序对数据进行预排序.

5> Lester Cheun..：

---

除了通常的建议:

批量插入的掉落指数.

批量插入/更新大型事务.

调整缓冲区缓存/禁用日志/ w PRAGMA.

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

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

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

要获得最大插入速度,请不要将模式与任何列约束一起使用.(稍后根据需要更改表格 您无法使用ALTER TABLE添加约束.

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

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

SQLite可以在同一列中存储不同类型的数据(动态类型),使用它对您有利.

欢迎提问/评论.;-)

---

@martin讨厌说出来,但答案是*取决于*.这个想法是将数据划分为可管理的大小.在我的用例中,我从不同的主机收集数据并在事后报告数据,因此这种方法运行良好.按照其他人的建议按日期/时间进行分区应该适用于我想象的很长一段时间的数据.

---

@Lester Cheung：关于您的第二个第一点：根据我的文档和个人经验，到目前为止，SQLite3不支持在创建表后使用ALTER TABLE添加约束。在现有表行中添加约束或从中删除约束的唯一方法是创建具有所需特征的新表并在所有行上进行复制，这可能比用约束插入一次要慢得多。

---

@Widdershins你绝对正确 - SQLite中的ALTER TABLE不允许添加约束.我不知道我在吸烟 - 会更新答案 - 谢谢.

6> Unknown..：

---

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

单进程写.

没有镜像.

没有复制.

7> 小智..：

---

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

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

---

我很抱歉,我必须指出这一点,但是"2 ^ 18"实际上只有256 K.

---

@GabrielSchreiber,以及50GB不是(2 ^ 10)MB的事实,那只有1GB.所以对于50GB的数据库,你需要12.5MB的内存:(2 ^ 8)*(2 ^ 10)*50

8> 小智..：

---

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

---

为什么具有多个重复项的列会降低性能(严重问题)？

---

基数较低的列难以索引:http://stackoverflow.com/questions/2113181/does-it-make-sense-to-use-an-index-that-will-have-a-low-cardinality

9> eodonohoe..：

---

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

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