在SQLite中使用WHERE子句提高SELECT COUNT(*)的性能？

Question

我正在尝试构建一个排行榜，其中使用了以下查询：

SELECT id, COUNT(type) FROM Data 
WHERE UPPER(type) = $1 AND id != '123123123123' 
GROUP BY id ORDER BY COUNT(type) DESC LIMIT 10;

这在我的表Data中大约需要8-10秒。400万行，并且每天以100,000+行的速度增长。

该表还有一个唯一的索引，当组合来自两个不同列的值- code和version时，该索引是唯一的。

我可以做什么来提高这个查询的速度和性能？

我曾考虑为每个用户的行构建一个计数器，但这不是很直观，因为100+值之间的type可能不同。我还尝试在id和type上添加索引，如果不是更长，也需要8-10秒。

Answer 1

使用insert触发器在该列上保留运行合计

Trigger for updating total records on both insert and delete

Answer 2

有许多具有不同性能优势和权衡的选项。

但是所有的工作都是通过维护第二个汇总表来实现的，该汇总表包含type by id列的每个值的计数。(基本上，您是在数据库中的一个表中存储您想要提高性能的查询输出，这是为了以后使用一个简单而快速的查询而使用)

因此，由于您还没有确切地给出如何创建您正在使用的表，因此我不得不从您的查询中推导出一个示例

因此，对于由其创建的Data表

CREATE TABLE "Data" (
    "id"    INTEGER NOT NULL,
    "type"  TEXT
)

您将创建第二个表来存储计数

CREATE TABLE "Data_summary" (
    "type"  TEXT NOT NULL,
    "data_id" INTEGER NOT NULL,
    "total_count"   INTEGER,
    UNIQUE ("type", "data_id")
)

(当您按id分组时，这必须是唯一约束的一部分，也必须是type的一部分)

您想要加速的查询将是

SELECT * FROM Data_summary WHERE data_id != 123123123123
    ORDER BY total_count DESC LIMIT 10 

所有这些都在存储空间成本与查询速度之间进行了权衡，此外还需要额外的工作来维护汇总表，下面将详细介绍各种策略。

type和id列的内容有多少不同的值并不重要，这只会使Data_summary表存储得更大

选项：

1)使用@stehenmwyatt22-w建议的Trigger更新汇总表

(但我会在我的工作示例的答案中给出实际细节)。

因此，您将创建如下触发器：-

CREATE TRIGGER update_summary AFTER INSERT ON Data
BEGIN
    INSERT INTO Data_summary (type, data_id, total_count)
    -- If Insert is success this is a new value of type so count is 1
    VALUES (NEW.type, NEW.id, 1)
    -- If value of type/id combo exists then there will be a conflict (this requires sqlite version 3.24.0 or later)
    ON CONFLICT(type, data_id)
    DO UPDATE SET total_count=total_count+1
        WHERE type == NEW.type AND data_id == NEW.id;
END;

因此，每次对Data执行插入操作时，都会自动对Data_summary表执行第二次插入/更新操作(如果在将total_count设置为1之前从未看到类型/id组合的值，否则在插入操作上有一个CONFLICT，因为它被设置为唯一组合，因此使用UPSERT过程插入UPDATE total_count +1

这种方法的一个缺点是，如果您已经有了包含数据的Data表，那么在TRIGGER接管维护Data_summary表之前，您将使用代码中的某个“更新”SQL语句(类似于INSERT INTO ...)来“引导”SQL表，以计算现有数据。

更新：

Online Demo

2)不使用触发器来更新Data_summary表，您可以进行“批处理”更新。为此，您还需要有一个唯一的索引列整数，它是自动递增的，或者类似于DATE+TIME列，以维护指向Data_summary最后更新到的位置的指针(这个“最后更新到”的指针需要存储在像第三个表这样的地方)。

当您想要对Data_summary表进行“更新”时，您可以运行一个与原始查询一样的查询，但是使用额外的WHERE子句将查询限制为仅针对Data表中尚未在Data_summary表中汇总的“新”行。

这将使用类似的ON CONFLICT来插入新的id和type组合，或者更新现有的组合计数(如果它们已经存在)。

批量更新可以作为后台任务完成，也可以在需要结果之前完成，因为昂贵的查询将只针对“新”数据加上简单的查询。

我不会给出一个例子，因为这就是所有的SQL与选项1)的例子非常相似。