高效的方式确保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代码或以其他方式导致项目变得不可维护。

>使用尽可能少的事务。
>使用参数化命令
插入数据(准备
命令一次，只是改变
循环中的参数值)
>设置
PRAGMA synchronous OFF(不确定
它如何与WAL一起使用)
>增加数据库的页面大小。
>增加缓存大小。这是一个重要的设置，因为它会导致sqlite实际写入数据到磁盘更少的次数，并将在内存中运行更多的操作，使整个过程更快。
>如果您需要一个索引，通过运行必要的sqlite命令插入行后添加它。在这种情况下，你需要确保自己的唯一性，因为你现在正在做它。

如果你尝试这些，请张贴您的测试结果。我相信这将是有趣的大家。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!