时间和日期的索引方式

这个小节的目标是回答三个问题：

1. PostgreSQL 在内部到底怎么存储日期和时间？
2. B 树索引是如何利用这些存储形式来加速查询的？
3. 在真实业务里，应该怎么建索引、怎么写 SQL 才高效？

下面从存储原理讲起，再回到实际写 SQL 时容易踩的坑。

---

1. timestamp / timestamptz 在索引里的真实样子

数据库在内部会将日期或时间戳转换成一个单一的、巨大的数字来进行存储和比较。B 树索引中存放的并不是 '2025-11-15 09:30:00' 这样的字符串，而是这个时间点对应的那个数字。

一个时间戳，比如 2025-11-15 09:30:15，包含了年、月、日、时、分、秒等多个部分。直接比较这么多部分会很复杂。最简单的方法是把它们合并成一个单独的、可以比较大小的数字。

真实的数据库实现会更精确和标准化。它们使用一个叫做 “纪元（Epoch）” 的概念：

• Epoch：一个被选定的、固定的“时间起点”。
• 任何一个时间点，都可以被表示为 “从 Epoch 起点到这个时间点，总共经过了多少个微秒（或秒、毫秒）”。
• 这个“经过的微秒数”就是一个巨大整数（通常是 64 位的 bigint）。

PostgreSQL 的具体实现：

• 使用一个 64 位整数 来存储从起点开始所经过的 微秒数（百万分之一秒）。
  • 正数表示 2000-01-01 之后的时间。
  • 负数表示 2000-01-01 之前的时间。

当你在数据库中存入一个时间戳 '2025-11-15 00:00:00' 时：

• PostgreSQL 内部会计算出它距离 2000-01-01 过去了多少微秒，然后把这个巨大的整数（比如是 182822400000000，示意）存入 B 树索引中。
• 当你要存入 '2025-11-16 00:00:00'，它会存入另一个更大的整数（例如 182908800000000，示意）。

B 树索引如何利用这个数字？

1. 索引的建立：
   • B 树索引中存储的不是可读的日期字符串，而是一系列排好序的、代表时间的巨大整数。
2. 精确查询 (WHERE created_at = '2025-11-15 09:30:00')：
   • 数据库先把 '2025-11-15 09:30:00' 转换成它对应的微秒数（那个大整数）。
   • 然后拿着这个整数去 B 树里进行高效的查找，这和查找 age = 32 的原理完全一样。
3. 范围查询 (WHERE created_at BETWEEN '2025-11-01' AND '2025-11-30')：
   • 数据库会把范围的起点和终点都转换成它们各自的微秒数。
   • 然后去 B 树里执行一个数值范围查询，找到第一个大于等于“起始微秒数”的叶子节点，然后沿着叶子节点的链表一直扫描，直到找到一个大于“结束微秒数”的值为止。这同样极其高效。
4. 排序查询 (ORDER BY created_at)：
   • 这变得非常简单。因为索引中的叶子节点本身就是按微秒数（也就是时间顺序）排好序的，数据库只需要沿着叶子节点的链表顺序读取即可，完全避免了代价高昂的排序操作。

结论：对于 B 树来说，索引一列 timestamp 和索引一列 bigint 在底层机制上是完全相同的。它根本不“认识”日期或时间，它只认识可以比较大小的、排好序的数字。

---

2. date 类型在索引里的样子

date 看起来也是日期，但实现方式和 timestamp 有一点不同：

• 它存储的是什么：从纪元起点 2000-01-01 到该日期，总共经过了多少“天”。
• 底层数据类型：一个 32 位整数 (integer)。
• 精度：天。它完全不关心具体的时间（时、分、秒）。
• 例子：
  • '2000-01-01' -> 存储为整数 0
  • '2000-01-03' -> 存储为整数 2
  • '2025-11-15' -> 存储为整数 9449（从起点算经过了 9449 天）

B 树索引中存放的就是这些相对较小的、代表天数的整数。

timestamp / timestamptz 对比回顾

• 它存储的是什么：从纪元起点 2000-01-01 到该时间点，总共经过了多少“微秒”。
• 底层数据类型：一个 64 位整数 (bigint)。
• 精度：微秒（百万分之一秒）。
• 例子：
  • '2025-11-15 00:00:00' -> 约等于 9449 * 24 * 3600 * 1000000，一个非常巨大的数字。
  • '2025-11-15 00:00:01' -> 在上面数字的基础上 + 1000000。

小结：date 是“第几天”的 int4，timestamp 是“第几个微秒”的 int8，两者在 B 树里都是整数排序。

---

3. 示例：索引在时间范围查询中的执行过程

假设我们有一张典型的业务表：

CREATE TABLE user_events (
  id         bigserial PRIMARY KEY,
  user_id    bigint       NOT NULL,
  event_type text         NOT NULL,
  created_at timestamptz  NOT NULL
);

CREATE INDEX idx_user_events_created_at
  ON user_events (created_at);

现在表里有几条示意数据（只展示 created_at）：

id | created_at
---+---------------------
 1 | 2025-11-01 08:00:00
 2 | 2025-11-01 09:00:00
 3 | 2025-11-02 10:00:00
 4 | 2025-10-30 07:00:00
 5 | 2025-10-30 08:00:00
 6 | 2025-11-01 12:00:00

我们要查 2025-11-01 这一天的记录：

SELECT *
FROM user_events
WHERE created_at >= '2025-11-01 00:00:00'
  AND created_at <  '2025-11-02 00:00:00';

执行步骤可以理解为：

1. 把两个边界时间都转换成“微秒数整数”。
2. 在 B 树索引中用“起始整数”做一次二分查找，定位到第一个满足条件的叶子节点。
3. 从这个叶子节点开始，沿着叶子链表依次往后扫，直到遇到第一个大于等于“结束整数”的值为止。

这样，B 树只扫描范围内的那一小段索引页，而不是整棵树或整张表。

---

4. 实际影响与最佳实践

理解了这些区别后，我们就能知道它在实际工作中意味着什么。

4.1 索引大小和效率的权衡

1. 如果你的业务只关心到“天”，比如统计每天的用户注册量：
   • 使用 date 类型会 明显更优。
   • 索引键更小，每个索引页能存放更多的键值，I/O 效率和缓存命中率都更高。
2. 用 timestamp 来存储一个只关心到天的数据：
   • 就像用一个巨大的货车去运送一封信，是空间和性能上的浪费。
   • 你多存了“时分秒微秒”这些根本不会用到的精度。

经验建议：

• 只按天统计、分组、对账的业务字段，用 date。
• 需要用到“时间点排序、最近 N 分钟、秒级日志”的，用 timestamp/timestamptz。

4.2 查询中的“陷阱”：在列上使用函数

这是一个新手最常犯、也最容易导致索引失效的错误！

假设你有一个 timestamp 类型的列 created_at，并且上面建了索引。现在你想查找所有“2025 年 11 月 15 日”的记录。

错误的写法：

SELECT *
FROM my_table
WHERE date(created_at) = '2025-11-15';

这个查询几乎肯定不会使用 created_at 的索引！原因是：

• 数据库必须对 created_at 列的每一行都先执行 date() 函数进行转换，然后再进行比较。
• B 树索引中存的是“微秒整数”，它根本不知道 date() 函数转换后的结果是什么。
• 于是它无法用已有的索引有序结构，只能退化为“逐行计算 + 比较”，通常就是全表扫描或顺序扫描。

正确的写法：将对列的计算，转化为对值的计算。

我们应该改成对“时间范围”的比较：

SELECT *
FROM my_table
WHERE created_at >= '2025-11-15 00:00:00'
  AND created_at <  '2025-11-16 00:00:00';

这个查询能完美地利用 B 树索引：

• 数据库把两个时间点转换成微秒数。
• 在 B 树上执行一次高效的范围扫描，性能极高。

口诀：不要在被索引的列上调用函数，把函数挪到常量一侧，改写成“范围查询”。

4.3 如果业务一定要用函数怎么办？（派生索引）

有些场景，业务逻辑天生就是“按天”或“按小时”聚合，写范围条件会比较长，这时可以考虑 表达式索引（函数索引）：

-- 在表达式 date(created_at) 上建索引
CREATE INDEX idx_my_table_created_at_date
  ON my_table (date(created_at));

-- 下面的写法就可以使用这个表达式索引
SELECT *
FROM my_table
WHERE date(created_at) = '2025-11-15';

这样做的好处：

• 业务 SQL 可以继续使用 date(created_at) 这种直观写法。
• 索引提前计算好了 date(created_at) 的值，并按这个值排序。

代价是：

• 索引额外占用空间。
• 写入/更新时需要维护一个额外索引，有一定写入开销。

实战建议：读多写少、且大量查询都用相同的函数表达式时，可以为该表达式单独建索引。

---

5. 典型业务场景的建模建议

场景 1：只做“按天对账”的账单表

• 字段可以设计为：billing_date date NOT NULL。
• 常见查询：

SELECT sum(amount)
FROM billing
WHERE billing_date BETWEEN '2025-11-01' AND '2025-11-30';

这时候：

• 给 billing_date 建一个普通 B 树索引就足够。
• 不需要 timestamp，也不需要在查询中强行写 00:00:00。

场景 2：订单创建时间 + 只按天统计

• 字段可能会设计成：created_at timestamptz NOT NULL。
• 但绝大多数报表是“按天统计订单数”。

两种方案：

1. 双列设计（推荐）：
   • created_at 保存真实下单时间（timestamptz）。
   • created_date 额外增加一列 date，保存“订单所属自然日”。
   • 按天统计、分区、归档等都使用 created_date。
2. 表达式索引：
   • 直接在 date(created_at) 上建索引，如上文 4.3 所示。

场景 3：最近 7 天 / 30 天 活跃用户

这类查询通常是这样的：

SELECT count(*)
FROM user_login
WHERE login_at >= now() - interval '7 days';

特点：

• 明显是一个时间范围查询。
• B 树对范围扫描极其友好。

只要在 login_at 上有 B 树索引，这种写法就非常适合走索引，无需额外技巧。

---

6. 总结与记忆要点

• 原理相同：date 和 timestamp 都是把时间/日期转成数字，再用 B 树索引做排序和范围查找。
• 实现不同：
  • date -> 天数（int4）。
  • timestamp / timestamptz -> 微秒数（int8）。
• 选择原则：
  • 只需要关心到“天”，就用 date，索引更紧凑。
  • 需要关心到“时分秒”，就用 timestamp / timestamptz。
• 使用原则：
  • 永远不要在 WHERE 子句中直接对被索引的列使用函数（如 date(created_at)），优先改写成范围查询。
  • 如果业务大量依赖某个函数表达式，可以为这个表达式单独建索引（表达式索引）。

掌握这些原则后，再看执行计划（EXPLAIN ANALYZE），你就能非常清晰地判断：当前这个时间/日期条件有没有吃到索引、为什么吃不到、该怎么改写。