Cassandra短信日志存储Schema设计优化咨询

一、现有表结构的精简与复用 #

你当前按查询维度拆分表的思路是符合Cassandra设计原则的，但可以针对需求做合并调整，减少冗余：

• 保留sms_logs_by_id：主键设为PRIMARY KEY (id)，用于单条日志的精确查询（对账时查单条详情）。
• 合并时间维度相关表：所有带时间段的查询都依赖时间筛选，可将这类查询对应的表合并，避免重复存储全量日志。

二、针对各查询需求的最优表设计 #

1. 总发送数 & 指定时间段总数 #

用计数器表替代全表扫描，性能提升显著：

CREATE TABLE sms_total_counter (
    count_key TEXT PRIMARY KEY,
    total COUNTER
);

• 插入日志时同步更新计数器：
  • 总计数：UPDATE sms_total_counter SET total = total + 1 WHERE count_key = 'all';
  • 每日计数：UPDATE sms_total_counter SET total = total + 1 WHERE count_key = '2024-05-20';
• 查询时直接取对应count_key的值，时间段统计只需累加对应日期的计数器即可。

2. 指定服务商/状态（含时间段）的总数 #

合并为一张复合主键表，覆盖多维度查询：

CREATE TABLE sms_logs_by_provider_status (
    service_provider TEXT,
    status TEXT,
    log_time TIMESTAMP,
    id UUID,
    phone_number TEXT,
    message TEXT,
    response TEXT,
    PRIMARY KEY ((service_provider, status), log_time, id)
) WITH CLUSTERING ORDER BY (log_time DESC);

• 分区键用(service_provider, status)，支持单独按服务商、单独按状态、服务商+状态的组合查询；
• 聚类键log_time按降序排列，快速筛选时间段范围；
• 若仅需统计数量，可配套建预聚合计数器表，按(service_provider, status, date)作为分区键，每日更新计数。

3. 指定收件人（含时间段）的总数 #

单独建表适配收件人维度查询：

CREATE TABLE sms_logs_by_phone (
    phone_number TEXT,
    log_time TIMESTAMP,
    id UUID,
    service_provider TEXT,
    status TEXT,
    message TEXT,
    response TEXT,
    PRIMARY KEY (phone_number, log_time, id)
) WITH CLUSTERING ORDER BY (log_time DESC);

• 你的场景每日数据量仅数千条，收件人基数不大，直接用phone_number做分区键即可；若后续收件人量级暴涨，可对phone_number做哈希拆分（如取后4位）作为分区前缀，避免单个分区过大。

三、字段类型优化 #

• 将date字段改为TIMESTAMP类型，支持精确到毫秒的时间范围查询，比单独DATE更灵活；
• phone_number用TEXT类型存储，避免带国际区号（如+86）的数字存储异常；
• status可选用ENUM类型节省空间，或TEXT类型保留扩展性。

四、预聚合提升对账效率 #

针对月末对账场景，每日定时跑批量任务生成聚合表：

CREATE TABLE sms_daily_agg (
    agg_date DATE,
    service_provider TEXT,
    status TEXT,
    phone_number TEXT,
    total_count INT,
    PRIMARY KEY (agg_date, service_provider, status, phone_number)
);

• 每日将各维度的统计数写入该表，月末对账直接查询聚合表，无需扫描全量原始日志。

五、现有表的取舍建议 #

如果现有表已满足性能需求，可保留但需做精简：

• 合并功能重叠的表（如单独的服务商表和状态表可合并为上述的sms_logs_by_provider_status）；
• 删除无对应查询需求的冗余表，确保每张表都服务于明确的查询场景。

内容的提问来源于stack exchange，提问作者realnsleo