AI摘要

短链点击数据“大而冷”，日增约50万、累计约5亿条100GB，查询几乎全是聚合而非单条。设计上明细表按click_time月分区、除主键不建额外索引，时间字段用BRIN；字段精简，IP用inet，不存UA和长链。写入经消息队列异步攒批，明细只写不查；小时/天预聚合表用UPSERT覆盖保证可重跑，当天查明细、历史查聚合。未引入ES/ClickHouse因数据量与成本收益不划算。

此内容根据文章生成，仅用于文章内容的解释与总结

前两篇写了短码怎么生成、跳转链路怎么缓存。这篇写这三篇里最麻烦的部分：点击数据怎么存。

麻烦的原因是它的数据特征和短链主表完全相反。主表是"小而热"——几亿条记录，但每条都不大，而且天天被按短码精确查询。点击数据是"大而冷"——每天新增几十万条，几乎没有单条查询，全是聚合统计。

这两种数据的表设计思路，几乎没有一个地方是共通的。照搬主表的做法，会死得很难看。

一、先把数据量和查询模式摆清楚

设计之前先算账。

写入侧：日均 50 万+ 次跳转，每次跳转产生一条点击记录。一天 50 万条，一年 1.8 亿条，这个系统跑到第三年，累计在 5 亿条这个量级。

单条记录的大小：点击记录要存的东西不算少——点击时间、短码、来源渠道、设备信息、IP，加上索引开销，一条下来大概 200 字节。5 亿条就是 100GB 左右。

查询侧：这才是决定设计的地方。谁在查这张表？

运营和市场的人。他们要看的是：某次活动总共多少点击、各个渠道的占比、一天内的点击趋势、点击的设备分布。

注意这里面没有一条是"查询某一条点击记录"。 没有"按 id 查单条"，没有"按短码查明细"，几乎没有"分页浏览"。

这是这张表和常规业务表最根本的区别：它是给统计用的，不是给事务用的。 常规业务表的索引设计围绕"如何快速定位一行"，而这张表要围绕"如何快速聚合一批"。

认清这一点之后，后面的取舍就都有依据了。

二、字段设计：宽表还是窄表

点击记录能存的东西很多，但不是都值得存。

我最后保留的字段：

CREATE TABLE click_log (
    id          bigserial      NOT NULL,
    code        varchar(10)    NOT NULL,   -- 短码
    click_time  timestamptz    NOT NULL,   -- 点击时间
    channel     varchar(32),               -- 来源渠道，从 URL 参数解析
    device      varchar(16),               -- 设备类型：ios / android / pc / other
    os          varchar(32),               -- 操作系统
    ip          inet,                      -- 来源 IP，用于地域分析
    PRIMARY KEY (id, click_time)           -- 分区表要求分区键进主键
) PARTITION BY RANGE (click_time);

有三处取舍值得说。

第一，不存完整的 User-Agent。

UA 字符串动辄两三百字节，比这条记录其他所有字段加起来还长。而我们真正需要的分析维度——设备类型、操作系统、浏览器——都能从 UA 里解析出来，存成短枚举值（ios、android、pc）反而是查询友好的。

代价是失去了"事后想换一种解析方式"的余地。如果哪天想把浏览器也拆出维度，历史数据就没法重算了。这是个取舍，我选择用空间换写入效率——因为写入量大，而历史数据的解析维度需求不高。

第二，IP 用 inet 类型而不是字符串。

PostgreSQL 的 inet 类型是 4 字节或 16 字节的二进制存储，而字符串形式的 IP 至少要 15 个字节。5 亿条记录，这个差别是几 GB。而且 inet 类型支持网段运算（<<= 之类），按网段聚合比字符串前缀匹配高效得多。

第三，不存长链接本身。

点击记录只需要知道"哪条短链被点了"，通过 code 关联回主表就够了。长链接是几十上百字节的字段，存进这张大表纯属浪费——而且主表里本来就有。

三、索引：能少建就少建

这一步是最反直觉的。

按常规思路，一张天天被各种条件查询的表，应该给每个查询维度都建索引：channel 建一个、click_time 建一个、code 建一个……

但对写入量大的表，每个索引都是实实在在的成本。每插入一条记录，所有的索引都要跟着更新一次——B+ 树的页分裂、磁盘随机写，全都叠加在写入路径上。一张表上建五个索引，写入开销可能就是只有一个索引时的三倍以上。

所以我的策略是：这张明细表，除了主键不带任何额外索引。

那统计查询怎么办？靠另外两张表。这是这篇文章的核心设计，放到第六节讲。

这里先说一个必须建、但经常被忽略的索引：时间字段用 BRIN，不用 B-tree。

四、BRIN：PostgreSQL 给时序数据准备的索引

这是我选 PostgreSQL 最主要的技术理由，也是我觉得这个系统里最值得写的一个点。

先看 B-tree 的问题。

在 5 亿行、100GB 的表上给 click_time 建 B-tree 索引，索引本身会有多大？B-tree 要为每一行存一个索引项（键值 + 行指针），5 亿行大概能到 10GB 量级。

也就是说，为了能按时间查，我要额外维护一个 10GB 的索引结构——而且每插入一行，这个索引就要更新一次。

BRIN（Block Range Index）是另一种思路。

它不记录每一行的位置，而是按数据块范围记录统计信息。默认每 128 个数据页（1MB 数据）为一组，BRIN 只记录这一组数据里 click_time 的最小值和最大值。

查询时先看 BRIN，跳过那些"范围完全不在查询条件内"的数据块，只扫描可能命中的数据块。

算一下索引大小：

100 GB 数据 ÷ 1 MB（每组）= 102,400 组
每组记录 min/max 两个时间戳 ≈ 32 字节
索引总大小 ≈ 3.3 MB

3MB 对 10GB。 差了三到四个数量级。

而且维护成本也完全不同：插入数据只是往已有数据块追加，BRIN 只在这些块的范围统计上做微调，不需要像 B-tree 那样做页分裂和重平衡。

BRIN 的适用条件很关键：它只在数据物理上有序的时候有效。而我们这张表是纯追加写入、按时间递增的，物理顺序天然和 click_time 一致。这正是 BRIN 最理想的场景。

如果数据是乱序插入的（比如按 id 随机写入），BRIN 的每个块范围都会覆盖整个时间区间，索引就完全失效了——它不会报错，只是扫不到任何块，退化成全表扫描。这一点在设计时就必须确认清楚。

顺带回答一个面试常问的问题：为什么这个系统用 PostgreSQL 而不是 MySQL？

除了团队本身的技术栈习惯，BRIN 是其中一个实打实的技术理由。MySQL 没有等价的索引类型，在时序大表上要么忍受大索引，要么自己实现分表 + 路由。PostgreSQL 的 BRIN 加上原生的声明式分区，让这张 5 亿行的表在运维上轻松了很多。

（当然，这不是说 PostgreSQL 全面优于 MySQL。是这张表的写入模式和查询模式，恰好踩在 BRIN 的甜点区上。）

五、分区：主要是为了归档，不是为了查询

表按 click_time 做了按月范围分区：

CREATE TABLE click_log_202601 PARTITION OF click_log
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

分区最大的价值不是查询加速，是归档。

点击数据的生命周期很清楚：最近几个月的会被频繁查询，超过一定时间的就基本没人看了。如果没有分区，要清理老数据就得 DELETE FROM click_log WHERE click_time < '...'——几千万行的删除，会产生巨大的 WAL、锁表、还得手动 VACUUM 回收空间。在线上做一次，整个库的性能都要受影响。

有了分区就不一样了：

-- 先把分区摘出去（瞬间完成，只改元数据）
ALTER TABLE click_log DETACH PARTITION click_log_202501;

-- 导出后直接删掉
DROP TABLE click_log_202501;

摘除分区是元数据操作，秒级完成，不产生 WAL，不锁表。 这是分区表在这个场景里最大的收益。

查询侧也有好处：统计某个月的点击，规划器会自动做分区裁剪，只扫那一个分区，其他分区连碰都不碰。

至于分区的粒度，我按月切。按天切的话分区数量会涨到上千个，规划器要处理的元数据太多，查询规划本身就变慢了；按月切，三年也就 36 个分区，数量刚好。

六、统计查询：让明细表只负责写

回到第三节留下的问题：明细表不建索引，运营要的报表怎么出？

答案是把读的压力从明细表上挪走——用预聚合表。

分层的思路

click_log         明细表，只写不查（除了当天的实时数据）
click_stat_hour   小时级聚合，按 (code, channel, hour) 汇总
click_stat_daily  天级聚合，按 (code, channel, date) 汇总

聚合表的结构很简单：

CREATE TABLE click_stat_daily (
    stat_date  date        NOT NULL,
    code       varchar(10) NOT NULL,
    channel    varchar(32) NOT NULL,
    pv         bigint      NOT NULL DEFAULT 0,
    uv         bigint      NOT NULL DEFAULT 0,
    PRIMARY KEY (stat_date, code, channel)
);

数据量对比一下就明白了：明细表一天 50 万行，聚合表一天可能只有几千行（因为大部分短链没有点击，有点击的也集中在少数几条上）。查询聚合表的成本比扫明细表低两三个数量级。

聚合任务怎么跑

小时级的定时任务，扫上一个小时的增量明细，汇总后写入聚合表。天级的任务基于小时表再汇总一次。

这里有个细节：聚合任务必须能重跑。 如果某个小时的汇总跑失败了，重跑的时候不能把数据算重。所以聚合用的是"先按维度聚合出结果，再按主键 UPSERT 覆盖"，而不是简单的累加：

INSERT INTO click_stat_daily (stat_date, code, channel, pv, uv)
SELECT click_time::date, code, channel, count(*), count(DISTINCT ip)
FROM click_log
WHERE click_time >= %s AND click_time < %s
GROUP BY 1, 2, 3
ON CONFLICT (stat_date, code, channel)
DO UPDATE SET pv = EXCLUDED.pv, uv = EXCLUDED.uv;

用覆盖而不是累加，是因为覆盖是可重入的，累加不是。 这一点在写定时任务的时候很容易忽略，等到需要补数据的时候才发现麻烦。

当天的数据怎么办

聚合表是按小时生成的，那运营想看"今天到现在为止的点击"怎么办？等下一个小时？那体验太差了。

我的处理是：当天走明细表，历史走聚合表。

当天的明细最多几十万行，而且有 BRIN 索引可以按时间范围裁剪，扫起来完全扛得住。到了第二天，数据进入聚合表，查询自动切过去。

这样既保证了实时性，又让明细表的查询压力被限制在"只有当天"这个范围内。

七、写入：异步 + 批量

点击记录的写入，绝对不能放在跳转的主链路上——这一点在上一篇里提过，这里说具体怎么做。

跳转接口在完成 302 响应之后，把点击事件丢进队列（我们用的是 RocketMQ），由一个独立的消费端负责落库。

消费端不是来一条写一条，而是攒批：从队列里拉一批（500 条或者等 200 毫秒，看哪个先到），然后用 COPY 或者批量 INSERT 一次性写进数据库。

为什么要攒批？因为这条链路上真正的瓶颈不是数据库的吞吐，是每次写入的网络往返和事务开销。单条插入 500 次，和批量插入 1 次写 500 条，耗时差距是几十倍。5 亿条数据的量级下，这个差距不能不重视。

代价是丢数据的窗口：如果消费端进程在攒批期间挂了，内存里没写库的那一批就丢了。我们的判断是这笔账可以接受——点击统计差几百条，不影响营销效果的判断；而为了不丢这几百条去做同步确认、重试、幂等，成本远高于收益。

这个取舍的前提是"数据可容忍少量丢失"。 如果换成交易流水，这个方案立刻就不成立了。

八、为什么没换 ES 或 ClickHouse

写到这里，肯定有人会问：这个场景不是天生适合 ES 或者 ClickHouse 吗？

确实适合。ClickHouse 是列存，聚合查询快到离谱；ES 的聚合能力和灵活性也远超 SQL。这两个方案我都认真评估过。

最后没换，原因不是技术，是成本和收益的比值：

  • 数据量还不够大。 5 亿行、100GB，PostgreSQL 加上分区和预聚合之后，报表查询是秒级的（因为大部分查询打的是几千行的聚合表）。换 ClickHouse 能把秒级变成毫秒级，但对一个运营看日报的场景来说，秒级和毫秒级没有区别。
  • 多一个组件的成本是全方位的。 部署、监控、备份、故障处理、团队学习，都要跟着涨。而当时团队里没人有 ClickHouse 的生产经验，引入一个没人能兜底的组件，风险比收益大。
  • PostgreSQL 已经在了。 主表、业务表都在同一个库里，点击数据放进来，事务边界、备份策略、运维方式都是现成的。

这也是我后来复盘时觉得比较满意的一个判断：技术选型不只看"哪个更强"，还要看"哪个更合适现在"。 如果这个系统的跳转量再涨一个数量级，或者开始做实时画像这类复杂分析，那我会重新评估——那时候 ClickHouse 就是必需品，而不是过度设计。

九、这套设计的边界

第一，明细表的写入是单库的。 5 亿行还在单库的承受范围内，但再往上（比如日均 500 万次点击），单库的写入和 WAL 压力会成为瓶颈，那时候要考虑分区表分布在不同的库上，或者换存储。

第二，预聚合的维度是固定的。 现在支持按 code、channel、时间三个维度聚合。如果哪天运营想按"设备型号 + 地域"这种新维度看数据，就得扫明细表重算，那个查询会跑很久。要支持任意维度，就得回到 ClickHouse 那种列存方案。

第三，UV 的计算不精确。 我用 count(DISTINCT ip) 算 UV，用 IP 代替用户标识。同一个用户换网络会被算成两个，公司出口 IP 下的多个用户会被算成一个。这是没有用户体系时的无奈选择——短链的访问是匿名的，本来就拿不到更精确的标识。

第四，明细表只有 BRIN 索引，意味着任何不带时间范围的明细查询都会全表扫。 比如"查一下短码 abc123 的所有点击记录"，这个查询会扫整张表。目前没有这种需求，但如果将来有了，就得重新考虑索引。

十、如果重来

会把 UV 的算法换掉。 现在用 IP 去重，误差不小。更好的方式是在短链跳转时生成一个匿名标识（比如基于 IP + UA + 日期做一次加盐哈希）写进 cookie 或本地存储，用它来算 UV。这样能跨 IP 变化识别用户，又不涉及个人信息。当时系统没有前端注入的能力，所以放弃了，但这是可以做的。

会加上数据质量的监控。 现在聚合任务跑完之后，只有结果入没入库的判断，没有"数据是否合理"的校验。如果某天队列积压或者消费端异常，聚合出来的数字会明显偏低，但没人会立刻发现。加一个简单的环比告警（比如今天的数据比昨天低 50% 就报警）就能覆盖大部分异常。

会考虑把明细数据同时投递一份到对象存储。 现在明细数据在归档后就 drop 掉了。但如果哪天要做一次全量的历史分析（比如"找出所有在特定时间段点击过特定渠道的用户"），就完全没数据了。冷数据存到对象存储成本很低，保留着总没有坏处。

最后

这三篇写下来，我自己最大的收获是：没有一种表设计是通用的，得先看数据的性格。

短链主表小而热，按短码精确查，索引围着 code 建；点击数据大而冷，全是聚合查，索引反而越少越好，靠预聚合把读的压力挪走。数据量差了几个数量级，做法就完全是两套。

技术方案的价值不在于它多先进，在于它和数据的形状对不对得上。

版权声明 ▶ 本网站名称：黄磊的博客
▶ 本文标题：短链系统的点击数据存储：一张「大而冷」的表该怎么设计
▶ 本文链接：https://www.huangleicole.com/backend-related/127.html
▶ 转载本站文章需要遵守：商业转载请联系站长，非商业转载请注明出处！！

© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏