我在我的一个项目中使用
SQLite3,我需要确保插入到表中的行是唯一的,因为它们的一些列的组合。在大多数情况下,插入的行在这方面会有所不同,但是在匹配的情况下,新行必须更新/替换现有行。
明显的解决方案是使用复合主键,使用冲突子句来处理冲突。因此:
CREATE TABLE Event (Id INTEGER,Fld0 TEXT,Fld1 INTEGER,Fld2 TEXT,Fld3 TEXT,Fld4 TEXT,Fld5 TEXT,Fld6 TEXT);
成为这样:
CREATE TABLE Event (Id INTEGER,Fld6 TEXT,PRIMARY KEY (Fld0,Fld2,Fld3) ON CONFLICT REPLACE);
这确实强制执行唯一性约束,因为我需要它。不幸的是,这种改变也会导致性能损失,超出我的预期。我做了
一些测试使用sqlite3命令行实用程序,以确保我的代码的其余部分没有故障。测试涉及输入100,000行,或者在一个单独的
交易或100个交易1000行。我得到以下结果:
| 1 * 100,000 | 10 * 10,000 | 100 * 1,000 | |---------------|---------------|---------------| | Time | cpu | Time | cpu | Time | cpu | | (sec) | (%) | (sec) | (%) | (sec) | (%) | --------------------------------|-------|-------|-------|-------|-------|-------| No primary key | 2.33 | 80 | 3.73 | 50 | 15.1 | 15 | --------------------------------|-------|-------|-------|-------|-------|-------| Primary key: Fld3 | 5.19 | 84 | 23.6 | 21 | 226.2 | 3 | --------------------------------|-------|-------|-------|-------|-------|-------| Primary key: Fld2,Fld3 | 5.11 | 88 | 24.6 | 22 | 258.8 | 3 | --------------------------------|-------|-------|-------|-------|-------|-------| Primary key: Fld0,Fld3 | 5.38 | 87 | 23.8 | 23 | 232.3 | 3 |
我的应用程序当前执行的行数最多为1,000行,我对性能下降了15倍感到惊讶。我预计吞吐量下降3倍,cpu使用率上升,如100k事务中所示。我认为涉及维护主键约束的索引需要大量的同步数据库操作,从而使我的硬盘驱动器成为这种情况下的瓶颈。
使用WAL mode确实有一些效果 – 性能提高约15%。不幸的是,这是不够的。 PRAGMA synchronous = NORMAL
似乎没有任何效果。
我可能能够通过增加事务大小恢复一些性能,但我不想这样做,由于增加的内存使用和对响应性的担忧和
可靠性。
每行中的文本字段平均具有约250字节的可变长度。查询性能无关紧要,但插入性能非常重要。我的应用程序代码是C,并且(应该)可移植到至少Linux和Windows。
有没有办法提高插入性能,而不增加事务大小?在sqlite中的一些设置(任何,但永久强制数据库进入异步操作,即)或在我的应用程序代码编程?例如,是否有一种方法来确保行唯一性而不使用索引?
BOUNTY:
通过使用在我自己的答案中描述的散列/索引方法,我设法有些温和性能下降到一个点,它可能是我的应用程序可以接受。
然而,看来,随着表中的行数增加,索引的存在使插入速度变慢。
我对任何技术或微调设置感兴趣,这将提高在这个特定的用例中的性能,只要它不涉及破解sqlite3代码或以其他方式导致项目变得不可维护。