SQL Academy · 课时

时间序列索引选择

Q: 「时间序列索引选择」这节课中我会学到什么？

为时间序列工作负载选择合适的索引——复合索引（device_id、ts DESC）可以覆盖常见模式 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

为时间序列工作负载选择合适的索引——复合索引（device_id、ts DESC）可以覆盖常见模式

时间序列索引选择 是 CoddyKit 上的免费 SQL Academy 课时。这是第 4 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

常见的时序查询

“某个实体的近期数据”：

SELECT * FROM metrics
WHERE device_id = 42
  AND ts >= NOW() - INTERVAL '24 hours'
ORDER BY ts DESC LIMIT 1000;

复合索引（device_id, ts DESC）

时序数据的标准索引：

CREATE INDEX metrics_device_ts_idx
  ON metrics (device_id, ts DESC);

-- Equality on device_id, range + sort on ts: index handles both.

为什么顺序很重要

(device_id, ts) — 最适合“这个设备的近期数据”
(ts, device_id) — 最适合“这个时间点的所有设备”

请根据查询组合进行选择。

仅追加表的 BRIN

BRIN 非常小——对于十亿行的表，只需几 MB。只有当数据在物理存储上按照索引列排序时才有效（时序数据通常符合这一点）：

CREATE INDEX metrics_ts_brin ON metrics USING BRIN (ts);
-- Excellent for "give me last 24 hours" on append-only tables.

B 树与 BRIN

B 树 — 根据精确标识符进行毫秒级查询；也适合按近期时间查询
BRIN — 对单调数据进行快速范围扫描；维护成本低

您可以组合使用：在 (device_id) 上建立 B 树，在 (ts) 上建立 BRIN。

超表索引

TimescaleDB 会将索引传播到所有数据块。请删除未使用的索引——每个数据块都要承担它们的开销。

为热点子集建立部分索引

如果 99% 的查询都访问近期数据：

CREATE INDEX metrics_recent_idx
  ON metrics (device_id, ts DESC)
  WHERE ts >= NOW() - INTERVAL '7 days';
-- Issue: predicate must use literal date or be re-created periodically.

避免在索引列上使用函数

不要对索引列应用函数——这会使索引无法使用：

-- BAD:
WHERE date_trunc('hour', ts) = $1
-- GOOD:
WHERE ts >= $1 AND ts < $1 + INTERVAL '1 hour'

按热点顺序进行 CLUSTER

CLUSTER 会按照某个索引对表进行物理重排。这是一次性操作；新数据仍会按照到达顺序追加：

CLUSTER metrics USING metrics_device_ts_idx;
-- Cluster is heavy. TimescaleDB chunks help by keeping recent chunks small.

高基数等值查询的哈希索引

时序数据很少使用它，但确实存在。通常对 device_id 使用 B 树就足够了。

不要建立过多索引

时序表通常写入频繁。每个索引都会造成写放大。只添加您实际查询所需的索引。

回顾

时序索引主要就是一小组常用模式。

使用复合索引 (entity, ts DESC) 进行按实体查询
在 ts 上使用 BRIN 进行仅追加数据的范围查询
超表：索引会传播到各个数据块
避免在列上使用函数作为谓词

快速检查

您最频繁的时序查询会按 device_id 进行筛选，并读取最近 24 小时的数据。最佳索引是什么？

常见问题解答