提高SQLite的每秒更新性能？

问题描述：

我的问题直接来自this one，虽然我只对UPDATE感兴趣，而且只有这一点。提高SQLite的每秒更新性能？

我已经写在C/C++一个应用程序，它使用了大量的SQLite，大多SELECT/UPDATE，在一个非常频繁的间隔（约20个查询每0.5至1秒）

我的数据库并不大，约2500个记录的时刻，这里是表结构：

CREATE TABLE player (
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    name VARCHAR(64) UNIQUE, 
    stats VARBINARY, 
    rules VARBINARY 
);

到现在为止我也没用过transactions因为我提高了代码，想要刺性能而非表现。

// 10 times execution of this 
UPDATE player SET stats = ? WHERE (name = ?)

其中stats正好150个字符的JSON和name是从5-10：

然后，我通过仅仅执行10 update查询，以下的（在不同的值的循环）测量了我的数据库的性能字符。

没有交易，其结果是不能接受的： - 约1充满第二（0.096每个）

随着交易中，时间下降x7.5时间： - 约0.11 - 0.16秒（0.013每个）

我尝试删除大部分数据库和/或重新排序/删除列，看看是否改变了什么，但没有。即使数据库仅包含100个记录（测试），我也会得到上述数字。

然后我试着用PRAGMA选择玩：

PRAGMA synchronous = NORMAL 
PRAGMA journal_mode = MEMORY

给我小的时候，但并非总是如此，更像约0.08 - 0.14秒

PRAGMA synchronous = OFF 
PRAGMA journal_mode = MEMORY

终于给了我非常小的时候大约0.002 - 0.003秒但我不想使用它，因为我的应用程序每秒都会保存数据库，并且数据库损坏的可能性很高OS /电源故障。

我C SQLite用于查询的代码是：（评论/错误处理/不相关的部分省略）

// start transaction 
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); 

// query 
sqlite3_stmt *statement = NULL; 
int out = sqlite3_prepare_v2(query.c_str(), -1, &statement, NULL); 
// bindings 
for(size_t x = 0, sz = bindings.size(); x < sz; x++) { 
    out = sqlite3_bind_text(statement, x+1, bindings[x].text_value.c_str(), bindings[x].text_value.size(), SQLITE_TRANSIENT); 
    ... 
} 

// execute 
out = sqlite3_step(statement); 

if (out != SQLITE_OK) { 
    // should finalize the query no mind the error 
    if (statement != NULL) { 
     sqlite3_finalize(statement); 
    } 
} 

// end the transaction 
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, NULL);

正如你看到的，这是一个非常典型的TABLE，记录数量少，我做一个简单的简单UPDATE正好10次。还有什么我可以做的，以减少我的UPDATE次？我正在使用最新的SQLite 3.16.2。

注：定时以上直接从单个END TRANSACTION查询到来。查询完成一个简单的交易，我使用准备好的语句。

UPDATE：

我进行了一些测试与交易的启用和禁用和各种更新计数。我进行以下设置测试：

VACUUM; 
PRAGMA synchronous = NORMAL; -- def: FULL 
PRAGMA journal_mode = WAL; -- def: DELETE 
PRAGMA page_size = 4096;  -- def: 1024

结果如下：

无交易（10次更新）

0.30800秒（0.0308每次更新）
0.30200秒
0.36200秒
0.28600小号ECS

没有交易记录（100次更新）

2.64400秒（0.02644每个更新）
2.61200秒
2.76400秒
2.68700秒

没有交易附件（1000次更新）

28.02800秒（0.028每个更新）
27.73700秒
...

与交易（10次更新）

0.12800秒（0.0128每个更新）
0.08100秒
0.16400秒
0.10400秒

与交易（100次更新）

0.088秒（0.00088每个更新）
0.091秒
0.052秒
0。101秒

与交易（1000次更新）

0.08900秒（0.000089每个更新）
0.15000秒
0.11000秒
0.09100秒

我的结论是transactionstime cost per query没有意义。也许时代变得越来越庞大，但我对这些数字不感兴趣。在单个交易上，10和1000更新之间几乎没有时间成本差异。不过，我想知道这是否是我的机器上的硬件限制，并且不能做太多。看来我不能低于~100毫秒，即使使用WAL，也可以使用单个事务并进行10-1000次更新。

没有交易，固定的时间成本大约为0.025秒。

如果使用languiage C/C++，使用正确的标签。否则，它看起来像C++，而不是**不同的**语言C！这不是一个代码评论网站。 – Olaf

@Olaf，唯一'C++'的东西是'std :: string';其余的是'C'。上面我特别强调了这一点。其次，我不希望有人审查我的代码，我想要一个更好的方法SQLite来解决我的问题 – user6096479

它不会**编译为C，因此它不是C.只是因为你有相同的语法/语法并不意味着你有相同的语义！谁告诉你C++是“带类的C”是明显错误的，并且不知道它们中的至少一个是否足够编写非平凡的代码。 – Olaf

答

利用这样小的数据量的，对于数据库操作本身的时间是微不足道;你测量的是事务开销（迫使写入磁盘所需的时间），这取决于操作系统，文件系统和硬件。

如果您可以忍受其限制（主要是无网络），您可以通过启用WAL mode来使用异步写入。

我试着设置'PRAGMA synchronous = NORMAL'和'PRAGMA journal_mode = WAL'来试试'WAL'，但我没有得到任何改进，我的意思是根本没有。我根本不需要网络，我想从WAL中受益，我只是没有获得任何收益（可能还需要其他一些选项？！？）。另一方面，我只需要每次最多1秒钟更新最多20个查询。他们（查询）可能会更少，但不能更多。在我看来，使用'synchronous = NORMAL或FULL'我不能打破'10 ms /更新查询'的障碍，除非我选择对OS进行处理，从而降低安全性。 – user6096479

要检查WAL是否启用，请执行'PRAGMA journal_mode;'。 –

我不知道为什么，但是我的'PRAGMA journal_mode = WAL'在我的C调用中根本没有被查询，所以模式仍然是'DELETE'。当我在'PHPLiteAdmin'上使用查询时正常执行。然而，我的时间略微减少到大约'0.07 - 0.11'，这似乎没问题。 – user6096479

答

您可能仍然受限于提交事务所花费的时间。在第一个例子中，每笔交易大约需要0.10秒才能完成，这非常接近插入10条记录的交易时间。如果您在单次交易中批量更新100或1000，会得到什么样的结果？另外，SQLite在平均硬盘驱动器上每秒钟预计大约60次事务，而您只能达到10次左右。磁盘性能可能是这里的问题吗？

https://sqlite.org/faq.html#q19

我是否受限于硬盘速度和同步模式等待SQLite验证数据写入？因此，如果我选择安全性而不是性能，那么对于典型的7200磁盘更新查询，我仅限于'〜10 ms'？我没有测试100或1000次更新，因为我的应用程序每次处理最多只能处理15-20个查询（其间有一些SELECT），所以我仿效了这个问题的场景。我一周前拆分了我的磁盘，我会再次回复并回复:) – user6096479

nope，由10个查询事务处理，时间从0.10到0.14秒不等。 – user6096479

请参阅我的问题更新 – user6096479

答

尝试添加索引到数据库：

CREATE INDEX IDXname ON player (name)

提高SQLite的每秒更新性能？

相关推荐