Cassandra数据建模:支持组内时间范围指标按item聚合的主键设计
Cassandra时序指标表主键设计方案
一、主键设计规则
Partition Key(分区键)
选择 (account_id, group_id, month_bucket) 作为联合分区键,设计逻辑如下:
- 适配删除需求:account_id 放在分区键首位,完美支持按账号+日期批量删除重写的要求,无需扫描全表
- 适配查询模式:group_id 作为第二分区键,核心查询固定指定 group 条件,无需跨无关 group 的分区查询
- 避免分区过大:
month_bucket是日期对应的年月值(例如202105代表2021年5月),单group单日最多10万条数据,单月最大300万行,远低于Cassandra单分区1000万行的最优阈值,不会产生分区热点。常规查询最多90天,仅需跨3个月份分区,查询代价极低
Clustering Key(聚类键)
选择 date, item 作为联合聚类键,设计逻辑如下:
- 时间过滤优先:date 放在聚类键首位,直接支持查询时的时间范围条件过滤,无需扫描分区内无关日期的数据
- 优化聚合性能:相同日期下按item排序,同一item的所有时序数据在分区内连续存储,按item聚合时的IO开销大幅降低
- 保留扩展空间:如果有subgroup维度的查询需求,可将subgroup_id加到聚类键的最前方,不影响原有查询性能
二、完整建表CQL示例
注意group是Cassandra保留关键字,此处统一改为group_id:
CREATE TABLE IF NOT EXISTS org_metric_data ( account_id INT, group_id INT, month_bucket INT, date DATE, item VARCHAR, temp DOUBLE, humidity DOUBLE, -- 其他业务指标字段按需扩展,无需使用list类型 PRIMARY KEY ((account_id, group_id, month_bucket), date, item) ) WITH CLUSTERING ORDER BY (date ASC, item ASC);
三、对应需求的CQL实现
1. 核心聚合查询(带HAVING过滤)
以查询group_id=8在2021年4月平均温度大于50的所有item的平均温度、平均湿度为例:
SELECT item, AVG(temp) AS avg_temp, AVG(humidity) AS avg_humidity FROM org_metric_data WHERE account_id = ? -- 业务侧可通过group_id映射得到对应account_id AND group_id = 8 AND month_bucket = 202104 AND date >= '2021-04-01' AND date <= '2021-04-30' GROUP BY item HAVING AVG(temp) > 50;
如果查询范围跨多个月份,仅需在WHERE条件中添加month_bucket IN (202103,202104)即可。
2. 按账号+日期删除重写
以删除account_id=1在2021年4月1日的所有数据为例:
DELETE FROM org_metric_data WHERE account_id = 1 AND group_id IN (/* 该账号下所有需要删除的group_id,全量删除时传所有group_id即可 */) AND month_bucket = 202104 AND date = '2021-04-01';
四、可选优化
如果单group单月数据量超过500万行,可以将分区键的month_bucket调整为周粒度(例如202119代表2021年第19周),进一步缩小单分区大小,90天查询最多跨13个周分区,性能无明显损耗。
内容的提问来源于stack exchange,提问作者SirJ
相关产品推荐
相关产品推荐

