# 场景1: 根据用户ID查询
# 最优方案：分区键查询
cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE user_id = ?;

# 性能：毫秒级
# 说明：直接定位分区，效率最高

# 场景2: 根据邮箱查询
# 方案1：创建索引
cqlsh:fgedudb> CREATE CUSTOM INDEX idx_email_sai ON fgedu_users (email)
… USING ‘StorageAttachedIndex’;

cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE email = ?;

# 方案2：创建邮箱索引表
cqlsh:fgedudb> CREATE TABLE fgedu_users_by_email (
… email text PRIMARY KEY,
… user_id uuid
… );

# 两步查询
cqlsh:fgedudb> SELECT user_id FROM fgedu_users_by_email WHERE email = ?;
cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE user_id = ?;

# 场景3: 根据用户名模糊查询
# 方案：创建SASI索引
cqlsh:fgedudb> CREATE CUSTOM INDEX idx_name_sasi ON fgedu_users (user_name)
… USING ‘org.apache.cassandra.index.sasi.SASIIndex’
… WITH OPTIONS = {‘mode’: ‘CONTAINS’};

cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE user_name LIKE ‘%张%’;

# 场景4: 根据状态查询
# 方案：创建二级索引（低基数列）
cqlsh:fgedudb> CREATE INDEX idx_status ON fgedu_users (status);

cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE status = ‘ACTIVE’;

# 1. 创建必要的索引
cqlsh:fgedudb> CREATE CUSTOM INDEX idx_users_email_sai ON fgedu_users (email)
… USING ‘StorageAttachedIndex’;

cqlsh:fgedudb> CREATE CUSTOM INDEX idx_users_phone_sai ON fgedu_users (phone)
… USING ‘StorageAttachedIndex’;

cqlsh:fgedudb> CREATE INDEX idx_users_status ON fgedu_users (status);

# 2. 创建查询表
# 按创建时间查询用户
cqlsh:fgedudb> CREATE TABLE fgedu_users_by_created (
… created_date date,
… created_time timestamp,
… user_id uuid,
… user_name text,
… email text,
… PRIMARY KEY (created_date, created_time)
… ) WITH CLUSTERING ORDER BY (created_time DESC);

# 按地区查询用户
cqlsh:fgedudb> CREATE TABLE fgedu_users_by_region (
… region text,
… user_id uuid,
… user_name text,
… email text,
… PRIMARY KEY (region, user_id)
… );

# 3. 查询性能测试
# 开启TRACING
cqlsh:fgedudb> TRACING ON;

# 测试分区键查询
cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE user_id = ?;
# 预期：1-5ms

# 测试索引查询
cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE email = ?;
# 预期：5-20ms

# 测试模糊查询
cqlsh:fgedudb> SELECT * FROM fgedu_users WHERE user_name LIKE ‘%张%’;
# 预期：20-100ms

# 关闭TRACING
cqlsh:fgedudb> TRACING OFF;

# 4. 监控查询性能
# 查看慢查询日志
# grep “slow query” /cassandra/logs/system.log

# 分析查询统计
# nodetool tablestats fgedudb.fgedu_users

# 场景1: 根据订单ID查询
# 最优方案：分区键查询
cqlsh:fgedudb> SELECT * FROM fgedu_orders WHERE order_id = ?;

# 场景2: 根据用户ID查询订单列表
# 方案：分区键+聚簇键查询
cqlsh:fgedudb> SELECT * FROM fgedu_orders
… WHERE user_id = ?
… ORDER BY order_time DESC
… LIMIT 20;

# 场景3: 根据订单状态查询
# 方案：创建索引表
cqlsh:fgedudb> CREATE TABLE fgedu_orders_by_status (
… status text,
… order_time timestamp,
… order_id uuid,
… user_id uuid,
… amount decimal,
… PRIMARY KEY (status, order_time)
… ) WITH CLUSTERING ORDER BY (order_time DESC);

cqlsh:fgedudb> SELECT * FROM fgedu_orders_by_status
… WHERE status = ‘PAID’
… LIMIT 100;

# 场景4: 根据时间范围查询
# 方案：创建时间分区表
cqlsh:fgedudb> CREATE TABLE fgedu_orders_by_date (
… order_date date,
… order_time timestamp,
… order_id uuid,
… user_id uuid,
… amount decimal,
… status text,
… PRIMARY KEY (order_date, order_time)
… ) WITH CLUSTERING ORDER BY (order_time DESC);

cqlsh:fgedudb> SELECT * FROM fgedu_orders_by_date
… WHERE order_date = ‘2024-01-15’
… LIMIT 100;

# 1. 创建索引表
# 按状态查询
cqlsh:fgedudb> CREATE TABLE fgedu_orders_by_status (
… status text,
… created_at timestamp,
… order_id uuid,
… user_id uuid,
… amount decimal,
… PRIMARY KEY (status, created_at)
… ) WITH CLUSTERING ORDER BY (created_at DESC);

# 按日期查询
cqlsh:fgedudb> CREATE TABLE fgedu_orders_by_date (
… order_date date,
… order_time timestamp,
… order_id uuid,
… user_id uuid,
… amount decimal,
… status text,
… PRIMARY KEY (order_date, order_time)
… ) WITH CLUSTERING ORDER BY (order_time DESC);

# 2. 数据同步写入
cqlsh:fgedudb> BEGIN BATCH
… INSERT INTO fgedu_orders (order_id, user_id, order_time, amount, status)
… VALUES (?, ?, ?, ?, ‘CREATED’);
… INSERT INTO fgedu_orders_by_status (status, created_at, order_id, user_id, amount)
… VALUES (‘CREATED’, ?, ?, ?, ?);
… INSERT INTO fgedu_orders_by_date (order_date, order_time, order_id, user_id, amount, status)
… VALUES (?, ?, ?, ?, ?, ‘CREATED’);
… APPLY BATCH;

# 3. 查询性能测试
# 开启TRACING
cqlsh:fgedudb> TRACING ON;

# 测试用户订单查询
cqlsh:fgedudb> SELECT * FROM fgedu_orders
… WHERE user_id = ?
… ORDER BY order_time DESC
… LIMIT 20;

# 测试状态查询
cqlsh:fgedudb> SELECT * FROM fgedu_orders_by_status
… WHERE status = ‘PAID’
… LIMIT 100;

# 测试日期查询
cqlsh:fgedudb> SELECT * FROM fgedu_orders_by_date
… WHERE order_date = ‘2024-01-15’
… LIMIT 100;

# 关闭TRACING
cqlsh:fgedudb> TRACING OFF;

# 4. 查询优化脚本
#!/bin/bash
# order_query_optimize.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# 统计订单查询性能
echo “订单查询性能统计：”
echo “==================”

# 统计分区键查询
echo “分区键查询：”
cqlsh -e “SELECT COUNT(*) FROM fgedudb.fgedu_orders WHERE user_id = ?” 2>&1 | grep “Query”

# 统计索引查询
echo “索引查询：”
cqlsh -e “SELECT COUNT(*) FROM fgedudb.fgedu_orders_by_status WHERE status = ‘PAID'” 2>&1 | grep “Query”

echo “统计完成”

# 场景1: 查询最近日志
# 方案：分区键+聚簇键查询
cqlsh:fgedudb> SELECT * FROM fgedu_logs
… WHERE log_date = ‘2024-01-15’
… ORDER BY log_time DESC
… LIMIT 100;

# 场景2: 查询特定级别日志
# 方案：创建索引表
cqlsh:fgedudb> CREATE TABLE fgedu_logs_by_level (
… log_level text,
… log_date date,
… log_time timestamp,
… log_id uuid,
… message text,
… PRIMARY KEY ((log_level, log_date), log_time)
… ) WITH CLUSTERING ORDER BY (log_time DESC);

cqlsh:fgedudb> SELECT * FROM fgedu_logs_by_level
… WHERE log_level = ‘ERROR’
… AND log_date = ‘2024-01-15’
… LIMIT 100;

# 场景3: 查询特定服务日志
# 方案：创建服务分区表
cqlsh:fgedudb> CREATE TABLE fgedu_logs_by_service (
… service_name text,
… log_date date,
… log_time timestamp,
… log_id uuid,
… log_level text,
… message text,
… PRIMARY KEY ((service_name, log_date), log_time)
… ) WITH CLUSTERING ORDER BY (log_time DESC);

cqlsh:fgedudb> SELECT * FROM fgedu_logs_by_service
… WHERE service_name = ‘user-service’
… AND log_date = ‘2024-01-15’
… LIMIT 100;

# 场景4: 查询包含关键字的日志
# 方案：创建SASI索引
cqlsh:fgedudb> CREATE CUSTOM INDEX idx_message_sasi ON fgedu_logs (message)
… USING ‘org.apache.cassandra.index.sasi.SASIIndex’
… WITH OPTIONS = {‘mode’: ‘CONTAINS’};

cqlsh:fgedudb> SELECT * FROM fgedu_logs
… WHERE message LIKE ‘%error%’
… LIMIT 100;

# 1. 创建日志表结构
# 主日志表
cqlsh:fgedudb> CREATE TABLE fgedu_logs (
… log_date date,
… log_time timestamp,
… log_id uuid,
… log_level text,
… service_name text,
… logger text,
… message text,
… stack_trace text,
… PRIMARY KEY (log_date, log_time)
… ) WITH CLUSTERING ORDER BY (log_time DESC)
… AND default_time_to_live = 604800; — 7天自动过期

# 按级别分表
cqlsh:fgedudb> CREATE TABLE fgedu_logs_by_level (
… log_level text,
… log_date date,
… log_time timestamp,
… log_id uuid,
… service_name text,
… message text,
… PRIMARY KEY ((log_level, log_date), log_time)
… ) WITH CLUSTERING ORDER BY (log_time DESC)
… AND default_time_to_live = 2592000; — 30天自动过期

# 2. 创建索引
cqlsh:fgedudb> CREATE CUSTOM INDEX idx_service_sai ON fgedu_logs (service_name)
… USING ‘StorageAttachedIndex’;

cqlsh:fgedudb> CREATE CUSTOM INDEX idx_message_sasi ON fgedu_logs (message)
… USING ‘org.apache.cassandra.index.sasi.SASIIndex’
… WITH OPTIONS = {‘mode’: ‘CONTAINS’};

# 3. 查询性能测试
# 开启TRACING
cqlsh:fgedudb> TRACING ON;

# 测试日期查询
cqlsh:fgedudb> SELECT * FROM fgedu_logs
… WHERE log_date = ‘2024-01-15’
… LIMIT 100;

# 测试级别查询
cqlsh:fgedudb> SELECT * FROM fgedu_logs_by_level
… WHERE log_level = ‘ERROR’
… AND log_date = ‘2024-01-15’;

# 测试关键字查询
cqlsh:fgedudb> SELECT * FROM fgedu_logs
… WHERE message LIKE ‘%exception%’
… LIMIT 100;

# 关闭TRACING
cqlsh:fgedudb> TRACING OFF;

# 4. 日志清理
# 自动清理（通过TTL）
# 手动清理
cqlsh:fgedudb> DELETE FROM fgedu_logs WHERE log_date < '2024-01-01';