SQLite非常慢

Question

我一直在努力让我的C++程序中的sqlite更快。我相信结果与预期相差甚远。

我的数据库中有几个表，其中大部分只有少量记录，而另一个表有大量记录（4986450条）。达到这个规模非常困难，因为每个事务中插入的数据太多，而且插入速度很慢。

另一方面，现在我正在对那个大表进行简单的查询，例如

sqlite3_prepare_v2(db,"SELECT * FROM Table where primary_key=?1;",-1, &query,NULL);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
....
while(running){
   sqlite3_bind_text(query, 1, pkey.c_str(), (int)pkey.size() , SQLITE_STATIC);

  int query_status = sqlite3_step(query);
  if(query_status ==  SQLITE_ROW){
      data = sqlite3_column_int(query,1);
      (... just saving data in a map)
  }
}
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

（我为了简单起见更改了表格和列的名称。） 这个查询在while循环中，而且在同一个事务中执行多次。选择查询大约需要9秒钟，执行500次。即使我在将数据插入表格时，也能获得更好的时间。

我在数据库上有以下的pragma。

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

你能帮我优化数据库吗？我做错了什么？

- anatp2015

---

纯属好奇，当（...只是将数据保存在列表中）被完全注释掉时，性能如何？ - WhozCraig

也许对您有兴趣：这两个 答案。 - Siguza

@WhozCraig 对不起，我的意思是map。它需要几乎相同的时间。 - anatp2015

@Siguza 感谢您的帖子。我已经尝试了大部分的建议，但结果仍然相同 :/ - anatp2015

你确定那一栏是主键吗？展示EXPLAIN QUERY PLAN的输出。 - CL.

请尝试使用auto_vacuum=true来缩小数据库（如果进行了一些删除）。你尝试过将page_size加倍吗？我看到你重复了cache_size...这是一个错误吗？ - aprados

Answer 1

选项一： 是否有可能使用整数主键而不是字符串主键？这真的会影响SELECT的性能。

选项二： 表中有多少列，您是否需要在记录集中使用所有这些列？如果可以避免读取和复制数据，则可能会获得更好的结果。

选项三： 是否有可能重写循环以由sqlite3_step控制而不是人为的外部控制？对记录集进行迭代（偶尔跳过未使用的行）比每次重新选择新的记录集要好得多。将数据保存到映射中表明这应该是可能的。

- Thinkeye