优化 Sqlite3 以处理 20,000+ 更新
我有大约20,000个项目的列表，想把它们插入到一个表里，这个表大约有50,000行。大部分项目是用来更新已有行的某些字段，少部分则是插入全新的行。
我对每个项目访问数据库两次。第一次是用一个查询来检查这行是否存在。接着，根据查询的结果，我要么插入新行，要么更新已有的行。我在每次更新或插入后都会提交一次事务。
在处理前几千个条目时，我的速度大约是每秒3到4个项目，但之后速度开始变慢。到最后，每次处理一个项目需要超过半秒。为什么会变慢呢？
我的平均时间是：整个过程大约0.5秒，其中选择查询大约0.18秒，插入或更新大约0.31秒。最后的0.01秒是因为在把数据输入数据库之前，有一些未测量的处理过程。
更新
我把所有的提交操作注释掉进行测试，结果没有变化，所以这不是问题所在（不过如果有关于最佳提交的想法，欢迎分享）。
关于表的结构：每一行有20列。前四列是文本字段（在第一次插入时设置），后16列是实数字段，其中一个是在初始插入语句中输入的。
随着时间的推移，'待填充'的实数字段将通过我正在优化的过程来填充。
我没有明确的索引，不过其中一个字段是每行的唯一键。
我还要提到，随着数据库变大，选择和更新查询所需的时间也越来越长，特别是选择操作的性能下降得很明显。
我最开始以为这可能是SQLite的某种结构性问题（不太明白这是什么意思），但我找不到任何文档表明这个程序有自然的限制。
现在数据库大约有60兆。
2 个回答
编辑: 真是抱歉，我看错了问题，以为是在讨论mySQL，其实是SQLite... 哎呀。
请忽略这个回复，除了获取一些关于更新数据库管理系统的通用想法。对于提问者的问题，可能的解决方案是频繁提交数据的问题，正如sixfeetsix的回复所指出的。
一个可能的解释是表格变得碎片化。
你可以通过定期整理表格来验证这一点，看看性能是否能恢复到每秒处理3到4条记录的速度。（顺便说一下，这个速度本身就比较慢，但可能还要看硬件、数据结构和其他具体情况。）当然，你需要考虑整理所花的时间，并与因更新速度慢而损失的时间进行权衡，以找到一个最佳的整理频率。
如果减速确实部分是由于碎片化造成的，你也可以考虑按照特定的顺序进行更新。具体的顺序很难说，因为这取决于数据库的整体结构和数据的统计特征，但碎片化确实对数据库中各种更改的顺序很敏感。
最后一个建议是，如果可能的话，先删除一些表上的索引，进行更新，然后再重新创建这些索引。这个看似反直觉的方法在进行较大更新时效果很好，因为重新创建索引的成本通常低于在更新过程中维护这些索引的累计成本。
我觉得你的瓶颈在于你每次插入或更新后都立即提交事务：
我在每次更新或插入后都会提交事务。
要么停止这样做，要么至少换成WAL 日志模式；看看我这个回答，里面有原因：SQL Server CE 4.0 性能比较
如果你有主键，可以通过在插入时使用 ON CONFLICT 子句来优化选择操作：
http://www.sqlite.org/lang_conflict.html
编辑：之前我想说的是“如果你有一个主键”，而不是外键；我已经修正了。