sql - 如何使用日期 GROUP BY 加速 SQL 查询？

Question

我有一个名为 table1 的普通 SQLite 数据库表，它有 7 列，当然还有一个 rowid。第一列是 custom_id 数字，第二列是 YYYY-MM-DD 格式的日期，其他 5 列是实数数据列。数据库中大约有 1000 万行，custom_id 和 date 列都有索引。

我想做的是加快以下查询:

SELECT date,max(data1) AS maximum
FROM table1
WHERE custom_id = '1123' AND data1 <> 'NaN'
GROUP BY strftime('%Y-%m', date)

我想为每个年-月组合找到 custom_id 1123 的最大正确(不是 NaN)data1 值。上面的代码实际上工作正常，但查询在第一次运行时持续 10 秒，但第二次运行不到 1 秒，这对我来说没问题。我在家用 PC Apache 服务器上使用 PHP 运行查询。我认为 Apache 使用了一些缓存来解释差异。

但问题是，如何加快首次运行性能呢？我还有很多其他的custom_id:s要查询，不是都可以缓存的!我需要更多索引吗？另一种查询？

Answer 1

我们将创建一个支持以下操作的索引:

1. 检索特定客户的记录
2. 按月汇总

无法创建以下索引，因为 strftime不是确定性函数

create index table1_ix on table1 (custom_id,strftime('%Y-%m', date));

non-deterministic functions prohibited in index expressions

所以不是 strftime('%Y-%m', date)我们将使用 substr(date,1,7)

create index table1_ix on table1 (custom_id,substr(date,1,7));

查询应该相应地改变

select      substr(date,1,7), max(data1) as maximum
from        table1
where       custom_id = '1123' 
        and data1 <> 'NaN'
group by    substr(date,1,7)