# 1. 账户余额
– 强一致性要求
– 防止数据不一致
– 支持事务操作

# 2. 交易记录
– 强一致性要求
– 数据不可丢失
– 支持审计查询

# 3. 风控数据
– 实时性要求
– 高可用性要求
– 准确性要求

# 一致性配置方案

# 账户余额表
CREATE TABLE fgedu_account_balance (
account_id uuid PRIMARY KEY,
balance decimal,
version int,
updated_at timestamp
);

# 一致性配置
写一致性: QUORUM
读一致性: QUORUM
副本因子: 5

# 交易记录表
CREATE TABLE fgedu_transactions (
transaction_id uuid PRIMARY KEY,
account_id uuid,
amount decimal,
type text,
status text,
created_at timestamp
);

# 一致性配置
写一致性: QUORUM
读一致性: QUORUM
副本因子: 5

# 风控数据表
CREATE TABLE fgedu_risk_alerts (
alert_id uuid PRIMARY KEY,
account_id uuid,
alert_type text,
alert_level text,
created_at timestamp
);

# 一致性配置
写一致性: ONE
读一致性: ONE
副本因子: 3

# 使用轻量级事务保证原子性

# 步骤1: 查询转出账户余额
cqlsh:fgedudb> SELECT balance, version FROM fgedu_account_balance
… WHERE account_id = ?
… USING CONSISTENCY QUORUM;

balance | version
———+———
1000.00 | 5

(1 rows)

# 步骤2: 检查余额是否充足
# 应用层判断: balance >= transfer_amount

# 步骤3: 执行转账（使用LWT）
cqlsh:fgedudb> BEGIN BATCH
… USING CONSISTENCY QUORUM
… AND SERIAL CONSISTENCY LOCAL_SERIAL
…
… — 扣减转出账户
… UPDATE fgedu_account_balance
… SET balance = balance – 100.00,
… version = version + 1,
… updated_at = toTimestamp(now())
… WHERE account_id = ?
… IF balance >= 100.00 AND version = 5;
…
… — 增加转入账户
… UPDATE fgedu_account_balance
… SET balance = balance + 100.00,
… version = version + 1,
… updated_at = toTimestamp(now())
… WHERE account_id = ?
… IF EXISTS;
…
… — 记录交易
… INSERT INTO fgedu_transactions
… (transaction_id, account_id, amount, type, status, created_at)
… VALUES (uuid(), ?, -100.00, ‘TRANSFER_OUT’, ‘SUCCESS’, toTimestamp(now()));
…
… INSERT INTO fgedu_transactions
… (transaction_id, account_id, amount, type, status, created_at)
… VALUES (uuid(), ?, 100.00, ‘TRANSFER_IN’, ‘SUCCESS’, toTimestamp(now()));
…
… APPLY BATCH;

[applied] | balance | version
———–+———+———
True | 900.00 | 6

(1 rows)

# 步骤4: 验证转账结果
cqlsh:fgedudb> SELECT * FROM fgedu_account_balance
… WHERE account_id IN (?, ?)
… USING CONSISTENCY QUORUM;

account_id | balance | updated_at | version
————————————–+———+———————————-+———
8e5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b | 900.00 | 2024-01-15 10:30:00.123000+0000 | 6
9f7g8h9i-0j1k-2l3m-4n5o-6p7q8r9s0t1u | 1100.00 | 2024-01-15 10:30:00.123000+0000 | 3

(2 rows)

# 1. 用户资料
– 中等一致性要求
– 高可用性要求
– 可容忍短暂不一致

# 2. 好友关系
– 强一致性要求
– 双向关系同步
– 防止关系不一致

# 3. 动态消息
– 高写入性能要求
– 最终一致性可接受
– 支持大量数据

# 一致性配置方案

# 用户资料表
CREATE TABLE fgedu_user_profiles (
user_id uuid PRIMARY KEY,
user_name text,
nickname text,
avatar text,
status text,
updated_at timestamp
);

# 一致性配置
写一致性: QUORUM
读一致性: ONE
副本因子: 3

# 好友关系表
CREATE TABLE fgedu_friendships (
user_id uuid,
friend_id uuid,
status text,
created_at timestamp,
PRIMARY KEY (user_id, friend_id)
);

# 一致性配置
写一致性: QUORUM
读一致性: QUORUM
副本因子: 3

# 动态消息表
CREATE TABLE fgedu_posts (
user_id uuid,
post_time timestamp,
post_id uuid,
content text,
like_count int,
PRIMARY KEY (user_id, post_time)
) WITH CLUSTERING ORDER BY (post_time DESC);

# 一致性配置
写一致性: ONE
读一致性: ONE
副本因子: 3

# 步骤1: 检查是否已是好友
cqlsh:fgedudb> SELECT * FROM fgedu_friendships
… WHERE user_id = ? AND friend_id = ?
… USING CONSISTENCY QUORUM;

# 步骤2: 添加好友关系（双向）
cqlsh:fgedudb> BEGIN BATCH
… USING CONSISTENCY QUORUM
…
… — 用户A添加用户B为好友
… INSERT INTO fgedu_friendships
… (user_id, friend_id, status, created_at)
… VALUES (?, ?, ‘ACCEPTED’, toTimestamp(now()));
…
… — 用户B添加用户A为好友
… INSERT INTO fgedu_friendships
… (user_id, friend_id, status, created_at)
… VALUES (?, ?, ‘ACCEPTED’, toTimestamp(now()));
…
… APPLY BATCH;

# 步骤3: 验证好友关系
cqlsh:fgedudb> SELECT * FROM fgedu_friendships
… WHERE user_id = ?
… USING CONSISTENCY QUORUM;

user_id | friend_id | created_at | status
————————————–+————————————–+———————————-+———
8e5f6a7b-8c9d-0e1f-2a3b-4c5d6e7f8a9b | 9f7g8h9i-0j1k-2l3m-4n5o-6p7q8r9s0t1u | 2024-01-15 10:30:00.123000+0000 | ACCEPTED

(1 rows)

# 步骤4: 查询好友列表
cqlsh:fgedudb> SELECT friend_id, status FROM fgedu_friendships
… WHERE user_id = ?
… USING CONSISTENCY ONE
… LIMIT 100;

# 1. 设备数据
– 高写入性能要求
– 数据量大
– 可容忍短暂不一致

# 2. 告警数据
– 实时性要求
– 中等一致性要求
– 高可用性要求

# 3. 统计数据
– 最终一致性可接受
– 高写入性能要求
– 支持聚合查询

# 一致性配置方案

# 设备数据表
CREATE TABLE fgedu_device_data (
device_id text,
data_time timestamp,
data_id uuid,
temperature double,
humidity double,
pressure double,
PRIMARY KEY ((device_id), data_time)
) WITH CLUSTERING ORDER BY (data_time DESC)
AND default_time_to_live = 2592000;

# 一致性配置
写一致性: ONE
读一致性: ONE
副本因子: 3

# 告警数据表
CREATE TABLE fgedu_device_alerts (
device_id text,
alert_time timestamp,
alert_id uuid,
alert_type text,
alert_level text,
message text,
PRIMARY KEY ((device_id), alert_time)
) WITH CLUSTERING ORDER BY (alert_time DESC);

# 一致性配置
写一致性: QUORUM
读一致性: ONE
副本因子: 3

# 统计数据表
CREATE TABLE fgedu_device_stats (
device_id text,
stat_date date,
stat_hour int,
avg_temperature double,
max_temperature double,
min_temperature double,
sample_count int,
PRIMARY KEY ((device_id, stat_date), stat_hour)
);

# 一致性配置
写一致性: ANY
读一致性: ONE
副本因子: 3

# 步骤1: 写入设备数据
cqlsh:fgedudb> INSERT INTO fgedu_device_data
… (device_id, data_time, data_id, temperature, humidity, pressure)
… VALUES (
… ‘device-001’,
… toTimestamp(now()),
… uuid(),
… 25.5,
… 60.0,
… 1013.25
… )
… USING CONSISTENCY ONE;

# 步骤2: 批量写入设备数据
cqlsh:fgedudb> BEGIN UNLOGGED BATCH
… USING CONSISTENCY ONE
… INSERT INTO fgedu_device_data (device_id, data_time, data_id, temperature, humidity, pressure)
… VALUES (‘device-001’, toTimestamp(now()), uuid(), 25.5, 60.0, 1013.25);
… INSERT INTO fgedu_device_data (device_id, data_time, data_id, temperature, humidity, pressure)
… VALUES (‘device-002’, toTimestamp(now()), uuid(), 26.0, 58.0, 1013.50);
… INSERT INTO fgedu_device_data (device_id, data_time, data_id, temperature, humidity, pressure)
… VALUES (‘device-003’, toTimestamp(now()), uuid(), 24.5, 62.0, 1013.00);
… APPLY BATCH;

# 步骤3: 写入告警数据
cqlsh:fgedudb> INSERT INTO fgedu_device_alerts
… (device_id, alert_time, alert_id, alert_type, alert_level, message)
… VALUES (
… ‘device-001’,
… toTimestamp(now()),
… uuid(),
… ‘TEMPERATURE_HIGH’,
… ‘WARNING’,
… ‘Temperature exceeded threshold: 35.5°C’
… )
… USING CONSISTENCY QUORUM;

# 步骤4: 查询设备数据
cqlsh:fgedudb> SELECT * FROM fgedu_device_data
… WHERE device_id = ‘device-001’
… USING CONSISTENCY ONE
… LIMIT 100;

device_id | data_time | data_id | humidity | pressure | temperature
————+———————————–+————————————+———-+———-+————-
device-001 | 2024-01-15 10:30:00.123000+0000 | a1b2c3d4-e5f6-7890-abcd-ef1234567890 | 60.0 | 1013.25 | 25.5

(1 rows)

# 步骤5: 写入统计数据
cqlsh:fgedudb> INSERT INTO fgedu_device_stats
… (device_id, stat_date, stat_hour, avg_temperature, max_temperature, min_temperature, sample_count)
… VALUES (
… ‘device-001’,
… ‘2024-01-15’,
… 10,
… 25.5,
… 30.0,
… 20.0,
… 3600
… )
… USING CONSISTENCY ANY;