database - 在数据库中存储二维时间序列的最有效方法是什么(sqlite3)

Question

我正在执行大规模风力模拟，以生成城市上空每小时的风力模式。结果是二维轮廓的时间序列。目前我将结果存储在具有以下结构的 SQLite3 数据库表中

Table: CFD
id, timestamp,           velocity, cell_id
1 , 2010-01-01 08:00:00, 3.345,    1 
2 , 2010-01-01 08:00:00, 2.355,    2 
3 , 2010-01-01 08:00:00, 2.111,    3 
4 , 2010-01-01 08:00:00, 6.432,    4 
.., ..................., .....,    .
1000 , 2010-01-01 09:00:00, 3.345,    1 
1001 , 2010-01-01 10:00:00, 2.355,    2 
1002 , 2010-01-01 11:00:00, 2.111,    3 
1003 , 2010-01-01 12:00:00, 6.432,    4 
.., ..................., .....,    .

实际创建语句:

CREATE TABLE cfd(id INTEGER PRIMARY KEY, time DATETIME, u, cell_id integer)
CREATE INDEX idx_cell_id_cfd on cfd(cell_id)
CREATE INDEX idx_time_cfd on cfd(time)

(一共有三个表，每个表对应一个不同的结果变量)

其中 cell_id 是对域中代表城市位置的单元格的引用。查看此图片以了解它在特定时间步长的样子。

典型的查询在时间维度上执行某种聚合，并根据 cell_id 进行分组。例如，如果我想知道特定时间间隔内每个单元格的本地平均风速，我会执行

select sum(time in ('2010-01-01 08:00:00','2010-01-01 13:00:00','2010-01-01 14:00:00', ...................., ,'2010-12-30 18:00:00','2010-12-30 19:00:00','2010-12-30 20:00:00','2010-12-30 21:00:00') and u > 5.0) from cfd group by cell_id

时间戳的数量可以从 100 到 8,000 不等。

这对于小型数据库来说很好，但对于大型数据库来说会慢得多。例如，我上一个数据库是 60GB，3 个表，每个表有 222,000,000 行。

有没有更好的方法来存储数据？例如:

• 每天创建不同的表格是否有意义？
• 为时间步使用一个单独的表然后使用连接会更好吗？
• 有没有更好的索引方式？

我已经采纳了这个 question 中的所有建议以最大限度地提高性能。

Answer 1

这个特定的查询很难优化，因为 sum() 必须对所有表行进行计算。用 WHERE 过滤行是一个更好的主意:

SELECT count(*)
FORM cfd
WHERE time IN (...)
  AND u > 5
GROUP BY cell_id;

如果可能，使用更简单的表达式来过滤时间，例如time BETWEEN a AND b。

可能值得使用 covering index ，或者在这种情况下，当所有查询都按时间过滤时，clustered index (没有额外的索引):

CREATE TABLE cfd (
    cell_id INTEGER,
    time DATETIME,
    u,
    PRIMARY KEY (cell_id, time)
) WITHOUT ROWID;