# 时序数据设计需求

# 场景: IoT设备数据采集
– 设备数量: 10万台
– 数据频率: 每秒1次
– 保留周期: 30天
– 查询需求: 按设备查询最近数据

# 设计挑战
1. 写入量大: 10万TPS
2. 时间热点: 最新数据写入集中
3. 数据量大: 30天数据约260TB
4. 查询效率: 快速查询最近数据

# 设计方案
# 表1: 设备最新数据表（快速查询最新数据）
CREATE TABLE fgedu_device_latest (
device_id text PRIMARY KEY,
timestamp timestamp,
temperature double,
humidity double,
pressure double,
status text
);

# 表2: 设备历史数据表（时间桶分区）
CREATE TABLE fgedu_device_history (
device_id text,
time_bucket text, — 格式: 2024-01-15-00
timestamp timestamp,
temperature double,
humidity double,
pressure double,
status text,
PRIMARY KEY ((device_id, time_bucket), timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC)
AND default_time_to_live = 2592000; — 30天自动过期

# 表3: 设备统计数据表（小时统计）
CREATE TABLE fgedu_device_hourly_stats (
device_id text,
stat_date date,
stat_hour int,
avg_temperature double,
max_temperature double,
min_temperature double,
avg_humidity double,
sample_count int,
PRIMARY KEY ((device_id, stat_date), stat_hour)
) WITH CLUSTERING ORDER BY (stat_hour ASC)
AND default_time_to_live = 7776000; — 90天自动过期

# 插入设备数据
cqlsh:fgedudb> INSERT INTO fgedu_device_latest (device_id, timestamp, temperature, humidity, pressure, status)
… VALUES (
… ‘device-001’,
… toTimestamp(now()),
… 25.5,
… 60.0,
… 1013.25,
… ‘NORMAL’
… );

# 插入历史数据（使用时间桶）
cqlsh:fgedudb> INSERT INTO fgedu_device_history
… (device_id, time_bucket, timestamp, temperature, humidity, pressure, status)
… VALUES (
… ‘device-001’,
… ‘2024-01-15-10’,
… toTimestamp(now()),
… 25.5,
… 60.0,
… 1013.25,
… ‘NORMAL’
… );

# 查询设备最新数据
cqlsh:fgedudb> SELECT * FROM fgedu_device_latest
… WHERE device_id = ‘device-001’;

device_id | humidity | pressure | status | temperature | timestamp
————+———-+———-+——–+————-+———————————-
device-001 | 60.0 | 1013.25 | NORMAL | 25.5 | 2024-01-15 10:30:00.123000+0000

(1 row)

# 查询设备历史数据（指定时间桶）
cqlsh:fgedudb> SELECT * FROM fgedu_device_history
… WHERE device_id = ‘device-001’
… AND time_bucket = ‘2024-01-15-10’
… LIMIT 100;

# 查询设备统计数据
cqlsh:fgedudb> SELECT * FROM fgedu_device_hourly_stats
… WHERE device_id = ‘device-001’
… AND stat_date = ‘2024-01-15’;

# 用户数据设计需求

# 场景: 社交应用用户系统
– 用户数量: 1亿
– 查询需求:
1. 根据用户ID查询用户信息
2. 根据邮箱查询用户
3. 根据用户名查询用户
4. 查询用户好友列表
5. 查询用户动态列表

# 设计方案
# 表1: 用户主表
CREATE TABLE fgedu_users (
user_id uuid PRIMARY KEY,
user_name text,
email text,
phone text,
password_hash text,
nickname text,
avatar text,
gender text,
birthday date,
region text,
created_at timestamp,
updated_at timestamp,
status text
);

# 表2: 邮箱索引表
CREATE TABLE fgedu_users_by_email (
email text PRIMARY KEY,
user_id uuid
);

# 表3: 用户名索引表
CREATE TABLE fgedu_users_by_name (
user_name text PRIMARY KEY,
user_id uuid
);

# 表4: 用户好友表
CREATE TABLE fgedu_user_friends (
user_id uuid,
friend_time timestamp,
friend_id uuid,
friend_name text,
friend_avatar text,
status text,
PRIMARY KEY (user_id, friend_time)
) WITH CLUSTERING ORDER BY (friend_time DESC);

# 表5: 用户动态表
CREATE TABLE fgedu_user_posts (
user_id uuid,
post_time timestamp,
post_id uuid,
post_type text,
post_content text,
like_count int,
comment_count int,
PRIMARY KEY (user_id, post_time)
) WITH CLUSTERING ORDER BY (post_time DESC)
AND default_time_to_live = 31536000; — 1年自动过期

# 创建用户（同时写入多个表）
cqlsh:fgedudb> BEGIN BATCH
… INSERT INTO fgedu_users (user_id, user_name, email, phone, password_hash, nickname, avatar, gender, birthday, region, created_at, updated_at, status)
… VALUES (uuid(), ‘wangwu’, ‘wangwu@fgedu.net.cn’, ‘13900139000’, ‘hash123’, ‘王五’, ‘avatar.jpg’, ‘MALE’, ‘1990-01-01’, ‘Beijing’, toTimestamp(now()), toTimestamp(now()), ‘ACTIVE’);
… INSERT INTO fgedu_users_by_email (email, user_id)
… VALUES (‘wangwu@fgedu.net.cn’, uuid());
… INSERT INTO fgedu_users_by_name (user_name, user_id)
… VALUES (‘wangwu’, uuid());
… APPLY BATCH;

# 查询用户（按ID）
cqlsh:fgedudb> SELECT * FROM fgedu_users
… WHERE user_id = 8e5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b;

# 查询用户（按邮箱）
cqlsh:fgedudb> SELECT u.* FROM fgedu_users u
… JOIN fgedu_users_by_email e ON u.user_id = e.user_id
… WHERE e.email = ‘wangwu@fgedu.net.cn’;
# 注意: Cassandra不支持JOIN，需要分两步查询

# 步骤1: 根据邮箱查询user_id
cqlsh:fgedudb> SELECT user_id FROM fgedu_users_by_email
… WHERE email = ‘wangwu@fgedu.net.cn’;

user_id
————————————–
8e5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b

(1 row)

# 步骤2: 根据user_id查询用户信息
cqlsh:fgedudb> SELECT * FROM fgedu_users
… WHERE user_id = 8e5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b;

# 添加好友
cqlsh:fgedudb> INSERT INTO fgedu_user_friends (user_id, friend_time, friend_id, friend_name, friend_avatar, status)
… VALUES (
… 8e5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b,
… toTimestamp(now()),
… uuid(),
… ‘李四’,
… ‘avatar_lisi.jpg’,
… ‘ACCEPTED’
… );

# 查询用户好友列表
cqlsh:fgedudb> SELECT * FROM fgedu_user_friends
… WHERE user_id = 8e5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b
… LIMIT 20;

# 检测热点分区

# 方法1: 使用nodetool tablehistograms
# nodetool tablehistograms fgedudb.fgedu_events
fgedudb/fgedu_events histograms
Percentile SSTables Write Latency Read Latency Partition Size Cell Count
(micros) (micros) (bytes)
50% 0.00 125.45 234.56 1024.00 10.00
75% 0.00 234.56 456.78 2048.00 20.00
95% 0.00 456.78 890.12 4096.00 40.00
98% 0.00 567.89 1234.56 8192.00 80.00
99% 0.00 678.90 1567.89 16384.00 160.00
Min 0.00 100.00 200.00 512.00 5.00
Max 0.00 1234.56 2345.67 1048576.00 1024.00

# 方法2: 使用nodetool cfstats
# nodetool cfstats fgedudb.fgedu_events | grep -E “Partition|Row”
Maximum partition size: 1048576 bytes
Mean partition size: 8192 bytes
Maximum row size: 1024 bytes
Mean row size: 256 bytes

# 方法3: 查询分区大小
cqlsh:fgedudb> SELECT partition_key, COUNT(*) as row_count
… FROM fgedu_events
… GROUP BY partition_key
… LIMIT 10;

# 方法4: 使用sstable-tools分析
# sstable-tools /cassandra/fgdata/data/fgedudb-fgedu_events-*/mc-1-big-Data.db partition-size

# 热点分区解决方案

# 问题: 按日期分区导致热点
# 原设计
CREATE TABLE fgedu_events_bad (
event_date date PRIMARY KEY,
event_data text
);

# 解决方案1: 时间桶分区
CREATE TABLE fgedu_events_v1 (
event_date date,
bucket int, — 0-99
event_time timestamp,
event_id uuid,
event_data text,
PRIMARY KEY ((event_date, bucket), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

# 解决方案2: 复合分区键
CREATE TABLE fgedu_events_v2 (
region text,
event_date date,
event_time timestamp,
event_id uuid,
event_data text,
PRIMARY KEY ((region, event_date), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

# 解决方案3: 随机前缀
CREATE TABLE fgedu_events_v3 (
prefix int, — 1-10
event_date date,
event_time timestamp,
event_id uuid,
event_data text,
PRIMARY KEY ((prefix, event_date), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

# 数据迁移
# 1. 创建新表
# 2. 编写迁移脚本
# 3. 迁移数据
# 4. 验证数据
# 5. 切换应用
# 6. 删除旧表

# 迁移脚本示例
#!/bin/bash
# migrate_events.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# 导出旧数据
#cqlsh -e “COPY fgedudb.fgedu_events_bad TO ‘/tmp/events.csv'”

# 转换数据格式
#awk -F’,’ ‘{print $1″,”int(rand()*100)”,”$2″,”$3″,”$4}’ /tmp/events.csv > /tmp/events_new.csv

# 导入新数据
#cqlsh -e “COPY fgedudb.fgedu_events_v1 (event_date, bucket, event_time, event_id, event_data) FROM ‘/tmp/events_new.csv'”