c++ - 5 个双字段上的 SELECT 查询的 SQLite3 c++ 性能问题

Question

我正在使用 SQLite3 存储一个包含大约 1 000 000 个节点的 5D 常规网格，但“SELECT”查询的性能存在一些问题。

上下文

数据库描述

每个条目由5+25个 double 组成，代表规则网格(节点)上的一个点:

• 5 firsts double : 5D 规则网格上点的坐标 (v1,v2,...,v5)
• 25 以下 double :一些特征(p1、p2、...、p25)

每个点都是唯一的(前 5 个值的任何组合都是唯一的)。该表是用 CREATE TABLE myTable(v1 double,..., v5 double, p1 double,..., p25 double) 创建的.我没有添加特定的约束。

条目在它们的坐标之后按升序排序(v1，然后是 v2，然后是 v3，...):

v1|v2|v3|v4|v5|p1|p2|p3|...
 0| 0| 0| 0| 0| x| x| x|...
 0| 0| 0| 0| 1| x| x| x|...
 0| 0| 0| 0| 2| x| x| x|...
...
 0| 0| 0| 1| 0| x| x| x|...
 0| 0| 0| 1| 1| x| x| x|...
 0| 0| 0| 1| 2| x| x| x|...
...

我已经在这个表上创建了一个索引，使用 CREATE INDEX idx ON myTable (v1,v2,v3,v4,v5)

SELECT 查询说明

我想在 5D 网格中进行“三次”插值。所以我必须在我想要的点周围的每个维度中提取 4 个点。我的 SELECT 查询应该返回 4*4*4*4*4=1024 点。

由于对称属性，我必须进行 16 次查询而不是 1 次查询。每个请求的形式为 SELECT * FROM myTable WHERE (v1=X AND v2=X AND v3 BETWEEN x1 AND x2 AND v4 BETWEEN y1 AND y2 AND v5 BETWEEN z1 AND z2) .实际上，v1 和 v2 是角。在我的 5D 网格中，我的值从 -165 到 180，步长为 15°。因此，如果我想在 -160 处插入一个值，则无法进行 v1 BETWEEN -180 AND -135 类型的查询。 (得到-180、-165、-150、-135)因为我的表中不存在-180。我可以优化这部分以仅进行 4 个查询，但这不是我主要关心的问题。

我正在使用一个准备好的语句:
sqlite3_prepare_v2(db,"SELECT * FROM myTable WHERE (v1=? AND v2=? AND v3 BETWEEN ? AND ? AND v4 BETWEEN ? AND ? AND v5 BETWEEN ? AND ?"),length,statement,NULL)


然后，对于每个查询，我会:

sqlite3_bind_double(statement, int, double);
while(sqlite3_step(statement)==SQLITE_ROW) {
    // for each row (for each "node") :
    //   1) retrieve its properties
    double myvar1 = sqlite3_colum_double(statement,6)
    double myvar2 = sqlite3_colum_double(statement,7)
    ....
    double myvar25 = sqlite3_colum_double(statement,25)
    //   2) create the object and add it to a vector for the interpolation 
}
sqlite3_reset(statement);
sqlite3_clear_bindings(statement);

数据库是用 sqlite3_open_v2(path,&db,SQLITE_OPEN_READONLY,NULL)

问题

查询16次，提取1024个点，耗时约2.5秒。 Sqlite3 操作占用了 99.8% 的计算量(callgrind)...

我做错了什么？

我试过了EXPLAIN QUERY PLAN SELECT * FROM myTable WHERE (v1=0 AND v2=0 AND v3 BETWEEN 0 AND 3 AND v4 BETWEEN 0 AND 3 AND v5 BETWEEN 0 AND 3 .结果是 SEARCH TABLE myTable USING INDEX idx (v1=? AND v2=? AND v3>? AND v3<?)

根据 5 个坐标创建唯一索引并进行 1024 次查询会更快吗？之前，我使用 vector 并通过索引直接访问。

Answer 1

当您使用相等比较 (=) 进行查找时，普通索引的效果最好。正如您在 EXPLAIN QUERY PLAN 输出中看到的那样，不相等的比较会阻止使用索引的任何其他列；数据库必须扫描所有 可能的v4 和v5 行以找到结果。

1. 您正在常规 网格中查询相当少的点，因此您确切知道所需所有点的坐标。只需使用一个简单的查询来搜索具有所有五个精确坐标的点，并执行 1024 次。这将导致效率更高的单个索引查找，即使它针对每个点执行也是如此。

   要使多个查询更加高效，请将所有查询包装在一个事务中。

2. 使用单独的 R-tree索引来查找点。R 树针对(多维)区间查询进行了优化。这将导致这样的查询:

   SELECT *
   FROM myTable
   WHERE rowid IN (SELECT id
                   FROM RtreeIndexTable
                   WHERE v1 = ?
                     AND v2 = ?
                     AND v3 BETWEEN ? AND ?
                     AND v4 BETWEEN ? AND ?
                     AND v5 BETWEEN ? AND ?)
   

   R 树通常用于不规则或稀疏数据；如果您可以摆脱查询单个点的麻烦，那么 R 树可能就有点矫枉过正了。