大海
479 lines
30 KiB
Markdown
479 lines
30 KiB
Markdown
# 第七部分：数据库设计
|
||
|
||
## 7.1 MySQL数据库设计
|
||
|
||
### 7.1.1 用户相关表
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 用户表 (users)
|
||
-- ============================================================
|
||
CREATE TABLE `users` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
|
||
`uuid` CHAR(36) NOT NULL COMMENT '用户UUID',
|
||
`phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号（加密存储）',
|
||
`email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱（加密存储）',
|
||
`password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希（bcrypt）',
|
||
`salt` VARCHAR(32) NOT NULL COMMENT '密码盐值',
|
||
`nickname` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '昵称',
|
||
`avatar` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '头像URL',
|
||
`gender` TINYINT NOT NULL DEFAULT 0 COMMENT '性别：0未知 1男 2女',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1正常 2待验证',
|
||
`register_source` VARCHAR(20) NOT NULL DEFAULT 'web' COMMENT '注册来源',
|
||
`register_ip` VARCHAR(45) NOT NULL DEFAULT '' COMMENT '注册IP',
|
||
`last_login_at` DATETIME DEFAULT NULL COMMENT '最后登录时间',
|
||
`last_login_ip` VARCHAR(45) DEFAULT NULL COMMENT '最后登录IP',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted_at` DATETIME DEFAULT NULL COMMENT '软删除时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_uuid` (`uuid`),
|
||
UNIQUE KEY `uk_phone` (`phone`),
|
||
UNIQUE KEY `uk_email` (`email`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
|
||
|
||
-- ============================================================
|
||
-- 设备表 (devices)
|
||
-- ============================================================
|
||
CREATE TABLE `devices` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '设备ID',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`device_id` VARCHAR(64) NOT NULL COMMENT '设备唯一标识（硬件指纹）',
|
||
`device_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '设备名称',
|
||
`device_type` VARCHAR(20) NOT NULL COMMENT '设备类型：windows/mac/android/ios',
|
||
`os_version` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '系统版本',
|
||
`client_version` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '客户端版本',
|
||
`push_token` VARCHAR(255) DEFAULT NULL COMMENT '推送Token',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1正常',
|
||
`last_active_at` DATETIME DEFAULT NULL COMMENT '最后活跃时间',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_device` (`user_id`, `device_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_device_id` (`device_id`),
|
||
KEY `idx_last_active` (`last_active_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备表';
|
||
|
||
-- ============================================================
|
||
-- 会员表 (memberships)
|
||
-- ============================================================
|
||
CREATE TABLE `memberships` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '会员ID',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`plan_id` VARCHAR(50) NOT NULL COMMENT '套餐ID',
|
||
`plan_name` VARCHAR(100) NOT NULL COMMENT '套餐名称',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0过期 1有效 2暂停',
|
||
`start_at` DATETIME NOT NULL COMMENT '开始时间',
|
||
`expire_at` DATETIME NOT NULL COMMENT '过期时间',
|
||
`auto_renew` TINYINT NOT NULL DEFAULT 0 COMMENT '是否自动续费',
|
||
`total_traffic` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '总流量（字节）',
|
||
`used_traffic` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '已用流量（字节）',
|
||
`device_limit` INT NOT NULL DEFAULT 1 COMMENT '设备数量限制',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_user_id` (`user_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire_at` (`expire_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员表';
|
||
|
||
-- ============================================================
|
||
-- 订单表 (orders)
|
||
-- ============================================================
|
||
CREATE TABLE `orders` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
|
||
`order_no` VARCHAR(32) NOT NULL COMMENT '订单号',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`plan_id` VARCHAR(50) NOT NULL COMMENT '套餐ID',
|
||
`plan_name` VARCHAR(100) NOT NULL COMMENT '套餐名称',
|
||
`amount` DECIMAL(10,2) NOT NULL COMMENT '订单金额',
|
||
`currency` VARCHAR(3) NOT NULL DEFAULT 'CNY' COMMENT '货币类型',
|
||
`pay_method` VARCHAR(20) NOT NULL COMMENT '支付方式：alipay/wechat/apple',
|
||
`pay_amount` DECIMAL(10,2) DEFAULT NULL COMMENT '实付金额',
|
||
`pay_time` DATETIME DEFAULT NULL COMMENT '支付时间',
|
||
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0待支付 1已支付 2已取消 3已退款',
|
||
`coupon_id` BIGINT DEFAULT NULL COMMENT '优惠券ID',
|
||
`discount_amount` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '优惠金额',
|
||
`remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`expired_at` DATETIME DEFAULT NULL COMMENT '过期时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_order_no` (`order_no`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_created_at` (`created_at`),
|
||
KEY `idx_pay_time` (`pay_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
|
||
```
|
||
|
||
### 7.1.2 节点相关表
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 节点表 (nodes)
|
||
-- ============================================================
|
||
CREATE TABLE `nodes` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '节点ID',
|
||
`node_id` VARCHAR(50) NOT NULL COMMENT '节点标识',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '节点名称',
|
||
`region` VARCHAR(20) NOT NULL COMMENT '区域：china/asia/europe/america',
|
||
`country` VARCHAR(10) NOT NULL COMMENT '国家代码',
|
||
`city` VARCHAR(50) NOT NULL COMMENT '城市',
|
||
`isp` VARCHAR(20) NOT NULL COMMENT '运营商：telecom/unicom/mobile/bgp',
|
||
`ip` VARCHAR(45) NOT NULL COMMENT '节点IP',
|
||
`port` INT NOT NULL DEFAULT 8000 COMMENT '服务端口',
|
||
`type` VARCHAR(20) NOT NULL COMMENT '类型：access/relay/exit',
|
||
`capacity` INT NOT NULL DEFAULT 10000 COMMENT '最大连接数',
|
||
`current_load` INT NOT NULL DEFAULT 0 COMMENT '当前连接数',
|
||
`bandwidth_mbps` INT NOT NULL DEFAULT 1000 COMMENT '带宽（Mbps）',
|
||
`used_bandwidth_mbps` INT NOT NULL DEFAULT 0 COMMENT '已用带宽',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0离线 1在线 2维护',
|
||
`quality_score` DECIMAL(5,2) NOT NULL DEFAULT 0 COMMENT '质量评分',
|
||
`latitude` DECIMAL(10,7) DEFAULT NULL COMMENT '纬度',
|
||
`longitude` DECIMAL(10,7) DEFAULT NULL COMMENT '经度',
|
||
`cloud_provider` VARCHAR(20) DEFAULT NULL COMMENT '云服务商',
|
||
`line_type` VARCHAR(20) NOT NULL DEFAULT 'bgp' COMMENT '线路类型',
|
||
`is_dedicated` TINYINT NOT NULL DEFAULT 0 COMMENT '是否专线',
|
||
`supported_games` JSON DEFAULT NULL COMMENT '支持的游戏列表',
|
||
`config` JSON DEFAULT NULL COMMENT '节点配置',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_node_id` (`node_id`),
|
||
KEY `idx_region` (`region`),
|
||
KEY `idx_country_city` (`country`, `city`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_type` (`type`),
|
||
KEY `idx_quality_score` (`quality_score`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='节点表';
|
||
|
||
-- ============================================================
|
||
-- 线路表 (routes)
|
||
-- ============================================================
|
||
CREATE TABLE `routes` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '线路ID',
|
||
`route_id` VARCHAR(50) NOT NULL COMMENT '线路标识',
|
||
`game_id` VARCHAR(50) NOT NULL COMMENT '游戏ID',
|
||
`source_region` VARCHAR(50) NOT NULL COMMENT '源区域',
|
||
`dest_region` VARCHAR(50) NOT NULL COMMENT '目标区域',
|
||
`access_node_id` VARCHAR(50) NOT NULL COMMENT '接入节点ID',
|
||
`relay_node_ids` JSON DEFAULT NULL COMMENT '中转节点ID列表',
|
||
`exit_node_id` VARCHAR(50) NOT NULL COMMENT '出口节点ID',
|
||
`protocol` VARCHAR(20) NOT NULL DEFAULT 'udp_relay' COMMENT '协议',
|
||
`priority` TINYINT NOT NULL DEFAULT 0 COMMENT '优先级',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0禁用 1启用',
|
||
`avg_latency` DECIMAL(8,2) DEFAULT NULL COMMENT '平均延迟',
|
||
`avg_loss` DECIMAL(5,4) DEFAULT NULL COMMENT '平均丢包率',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_route_id` (`route_id`),
|
||
KEY `idx_game_id` (`game_id`),
|
||
KEY `idx_source_region` (`source_region`),
|
||
KEY `idx_dest_region` (`dest_region`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='线路表';
|
||
```
|
||
|
||
### 7.1.3 游戏相关表
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 游戏表 (games)
|
||
-- ============================================================
|
||
CREATE TABLE `games` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '游戏ID',
|
||
`game_id` VARCHAR(50) NOT NULL COMMENT '游戏标识',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '游戏名称',
|
||
`name_en` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '英文名',
|
||
`icon` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '图标URL',
|
||
`category` VARCHAR(30) NOT NULL COMMENT '分类：fps/moba/mmorpg/rts/other',
|
||
`platform` VARCHAR(20) NOT NULL DEFAULT 'pc' COMMENT '平台：pc/mobile/console',
|
||
`is_popular` TINYINT NOT NULL DEFAULT 0 COMMENT '是否热门',
|
||
`is_enabled` TINYINT NOT NULL DEFAULT 1 COMMENT '是否启用',
|
||
`process_names` JSON DEFAULT NULL COMMENT '进程名列表',
|
||
`server_config` JSON DEFAULT NULL COMMENT '服务器配置',
|
||
`default_protocol` VARCHAR(20) NOT NULL DEFAULT 'udp_relay' COMMENT '默认协议',
|
||
`priority` TINYINT NOT NULL DEFAULT 0 COMMENT '优先级',
|
||
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序',
|
||
`description` TEXT COMMENT '描述',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_game_id` (`game_id`),
|
||
KEY `idx_category` (`category`),
|
||
KEY `idx_is_popular` (`is_popular`),
|
||
KEY `idx_sort_order` (`sort_order`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='游戏表';
|
||
|
||
-- ============================================================
|
||
-- 游戏服务器表 (game_servers)
|
||
-- ============================================================
|
||
CREATE TABLE `game_servers` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '服务器ID',
|
||
`game_id` VARCHAR(50) NOT NULL COMMENT '游戏ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '服务器名称',
|
||
`region` VARCHAR(20) NOT NULL COMMENT '区域',
|
||
`ip` VARCHAR(45) NOT NULL COMMENT '服务器IP',
|
||
`ip_range` VARCHAR(18) DEFAULT NULL COMMENT 'IP段',
|
||
`port` INT DEFAULT NULL COMMENT '端口',
|
||
`protocol` VARCHAR(10) NOT NULL DEFAULT 'udp' COMMENT '协议',
|
||
`location` JSON DEFAULT NULL COMMENT '地理位置',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_game_id` (`game_id`),
|
||
KEY `idx_region` (`region`),
|
||
KEY `idx_ip` (`ip`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='游戏服务器表';
|
||
```
|
||
|
||
### 7.1.4 测速与日志表
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 测速记录表 (speed_tests)
|
||
-- ============================================================
|
||
CREATE TABLE `speed_tests` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`node_id` VARCHAR(50) NOT NULL COMMENT '节点ID',
|
||
`game_id` VARCHAR(50) DEFAULT NULL COMMENT '游戏ID',
|
||
`rtt` DECIMAL(8,2) NOT NULL COMMENT '延迟（ms）',
|
||
`jitter` DECIMAL(8,2) NOT NULL COMMENT '抖动（ms）',
|
||
`loss_rate` DECIMAL(5,4) NOT NULL COMMENT '丢包率',
|
||
`bandwidth_mbps` DECIMAL(10,2) DEFAULT NULL COMMENT '带宽（Mbps）',
|
||
`score` DECIMAL(5,2) NOT NULL COMMENT '评分',
|
||
`client_ip` VARCHAR(45) NOT NULL COMMENT '客户端IP',
|
||
`isp` VARCHAR(20) DEFAULT NULL COMMENT '运营商',
|
||
`city` VARCHAR(50) DEFAULT NULL COMMENT '城市',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_node_id` (`node_id`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测速记录表';
|
||
|
||
-- ============================================================
|
||
-- 加速会话表 (sessions)
|
||
-- ============================================================
|
||
CREATE TABLE `sessions` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '会话ID',
|
||
`session_id` VARCHAR(64) NOT NULL COMMENT '会话标识',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`device_id` VARCHAR(64) NOT NULL COMMENT '设备ID',
|
||
`game_id` VARCHAR(50) NOT NULL COMMENT '游戏ID',
|
||
`node_id` VARCHAR(50) NOT NULL COMMENT '节点ID',
|
||
`route_id` VARCHAR(50) DEFAULT NULL COMMENT '线路ID',
|
||
`protocol` VARCHAR(20) NOT NULL COMMENT '协议',
|
||
`start_at` DATETIME NOT NULL COMMENT '开始时间',
|
||
`end_at` DATETIME DEFAULT NULL COMMENT '结束时间',
|
||
`duration` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '时长（秒）',
|
||
`total_bytes_up` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上行流量（字节）',
|
||
`total_bytes_down` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '下行流量（字节）',
|
||
`avg_latency` DECIMAL(8,2) DEFAULT NULL COMMENT '平均延迟',
|
||
`avg_loss` DECIMAL(5,4) DEFAULT NULL COMMENT '平均丢包率',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：0异常断开 1正常结束 2进行中',
|
||
`disconnect_reason` VARCHAR(50) DEFAULT NULL COMMENT '断开原因',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_session_id` (`session_id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_game_id` (`game_id`),
|
||
KEY `idx_start_at` (`start_at`),
|
||
KEY `idx_status` (`status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='加速会话表';
|
||
|
||
-- ============================================================
|
||
-- 操作日志表 (operation_logs)
|
||
-- ============================================================
|
||
CREATE TABLE `operation_logs` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '用户ID',
|
||
`admin_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '管理员ID',
|
||
`action` VARCHAR(50) NOT NULL COMMENT '操作类型',
|
||
`resource` VARCHAR(50) NOT NULL COMMENT '资源类型',
|
||
`resource_id` VARCHAR(50) DEFAULT NULL COMMENT '资源ID',
|
||
`detail` JSON DEFAULT NULL COMMENT '详情',
|
||
`ip` VARCHAR(45) NOT NULL COMMENT '操作IP',
|
||
`user_agent` VARCHAR(255) DEFAULT NULL COMMENT 'UserAgent',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_admin_id` (`admin_id`),
|
||
KEY `idx_action` (`action`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';
|
||
```
|
||
|
||
### 7.1.5 风控相关表
|
||
|
||
```sql
|
||
-- ============================================================
|
||
-- 风控事件表 (risk_events)
|
||
-- ============================================================
|
||
CREATE TABLE `risk_events` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
|
||
`event_type` VARCHAR(30) NOT NULL COMMENT '事件类型：abnormal_login/shared_account/ddos',
|
||
`risk_level` TINYINT NOT NULL COMMENT '风险等级：1低 2中 3高',
|
||
`detail` JSON DEFAULT NULL COMMENT '详情',
|
||
`ip` VARCHAR(45) NOT NULL COMMENT 'IP',
|
||
`device_id` VARCHAR(64) DEFAULT NULL COMMENT '设备ID',
|
||
`status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0待处理 1已处理 2已忽略',
|
||
`handle_admin_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '处理管理员ID',
|
||
`handle_at` DATETIME DEFAULT NULL COMMENT '处理时间',
|
||
`handle_remark` VARCHAR(255) DEFAULT NULL COMMENT '处理备注',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_event_type` (`event_type`),
|
||
KEY `idx_risk_level` (`risk_level`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_created_at` (`created_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='风控事件表';
|
||
|
||
-- ============================================================
|
||
-- 黑名单表 (blacklist)
|
||
-- ============================================================
|
||
CREATE TABLE `blacklist` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`type` VARCHAR(20) NOT NULL COMMENT '类型：ip/device/user',
|
||
`value` VARCHAR(100) NOT NULL COMMENT '值',
|
||
`reason` VARCHAR(255) NOT NULL COMMENT '原因',
|
||
`expire_at` DATETIME DEFAULT NULL COMMENT '过期时间',
|
||
`admin_id` BIGINT UNSIGNED NOT NULL COMMENT '操作管理员ID',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_type_value` (`type`, `value`),
|
||
KEY `idx_expire_at` (`expire_at`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='黑名单表';
|
||
```
|
||
|
||
### 7.1.6 索引设计说明
|
||
|
||
```
|
||
┌──────────────────────────────────────────────────────────────────┐
|
||
│ 索引设计原则 │
|
||
├──────────────────────────────────────────────────────────────────┤
|
||
│ │
|
||
│ 1. 主键索引：所有表使用自增BIGINT作为主键 │
|
||
│ │
|
||
│ 2. 唯一索引： │
|
||
│ - 业务唯一标识（uuid, node_id, game_id, order_no等） │
|
||
│ - 防止重复数据 │
|
||
│ │
|
||
│ 3. 普通索引： │
|
||
│ - 查询频繁的字段（user_id, status, created_at等） │
|
||
│ - 组合索引遵循最左前缀原则 │
|
||
│ │
|
||
│ 4. 索引优化： │
|
||
│ - 避免在频繁更新的列上建索引 │
|
||
│ - 控制单表索引数量（不超过5个） │
|
||
│ - 使用覆盖索引优化查询 │
|
||
│ │
|
||
│ 5. 分库分表策略： │
|
||
│ ┌──────────────┬──────────────┬──────────────────────────┐ │
|
||
│ │ 表名 │ 分表策略 │ 说明 │ │
|
||
│ ├──────────────┼──────────────┼──────────────────────────┤ │
|
||
│ │ sessions │ 按月分表 │ 数据量大，按月归档 │ │
|
||
│ │ speed_tests │ 按月分表 │ 数据量大，按月归档 │ │
|
||
│ │ operation_logs│ 按月分表 │ 日志类，按月归档 │ │
|
||
│ │ risk_events │ 按季度分表 │ 数据量适中 │ │
|
||
│ │ orders │ 按年分表 │ 订单数据，按年归档 │ │
|
||
│ │ 其他表 │ 不分表 │ 数据量可控 │ │
|
||
│ └──────────────┴──────────────┴──────────────────────────┘ │
|
||
│ │
|
||
└──────────────────────────────────────────────────────────────────┘
|
||
```
|
||
|
||
## 7.2 Redis设计
|
||
|
||
```
|
||
┌──────────────────────────────────────────────────────────────────┐
|
||
│ Redis 数据结构设计 │
|
||
├──────────────────────────────────────────────────────────────────┤
|
||
│ │
|
||
│ 1. 用户会话缓存 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: session:{session_id} │ │
|
||
│ │ Type: Hash │ │
|
||
│ │ Fields: │ │
|
||
│ │ user_id: 用户ID │ │
|
||
│ │ device_id: 设备ID │ │
|
||
│ │ node_id: 节点ID │ │
|
||
│ │ game_id: 游戏ID │ │
|
||
│ │ start_at: 开始时间 │ │
|
||
│ │ status: 状态 │ │
|
||
│ │ TTL: 24小时 │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
│ 2. 节点状态缓存 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: node:{node_id}:status │ │
|
||
│ │ Type: Hash │ │
|
||
│ │ Fields: │ │
|
||
│ │ load: 当前负载 │ │
|
||
│ │ rtt: 平均延迟 │ │
|
||
│ │ loss: 丢包率 │ │
|
||
│ │ jitter: 抖动 │ │
|
||
│ │ score: 评分 │ │
|
||
│ │ last_heartbeat: 最后心跳时间 │ │
|
||
│ │ TTL: 60秒（心跳超时） │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
│ 3. Token缓存 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: token:{user_id} │ │
|
||
│ │ Type: String │ │
|
||
│ │ Value: JWT Token │ │
|
||
│ │ TTL: 2小时 │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
│ 4. 验证码缓存 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: sms:{phone} │ │
|
||
│ │ Type: String │ │
|
||
│ │ Value: 验证码 │ │
|
||
│ │ TTL: 5分钟 │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
│ 5. 限流计数器 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: rate:{api}:{ip} │ │
|
||
│ │ Type: String (INCR) │ │
|
||
│ │ Value: 计数 │ │
|
||
│ │ TTL: 1分钟 │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
│ 6. 节点评分排行榜 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: nodes:{region}:ranking │ │
|
||
│ │ Type: Sorted Set │ │
|
||
│ │ Member: node_id │ │
|
||
│ │ Score: quality_score │ │
|
||
│ │ TTL: 5分钟 │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
│ 7. 在线用户统计 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: online:users │ │
|
||
│ │ Type: HyperLogLog │ │
|
||
│ │ 功能: UV统计（去重计数） │ │
|
||
│ │ TTL: 永不过期 │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
│ 8. 游戏配置缓存 │
|
||
│ ┌──────────────────────────────────────────────────────────┐ │
|
||
│ │ Key: game:{game_id}:config │ │
|
||
│ │ Type: String (JSON) │ │
|
||
│ │ Value: 游戏完整配置 │ │
|
||
│ │ TTL: 1小时 │ │
|
||
│ └──────────────────────────────────────────────────────────┘ │
|
||
│ │
|
||
└──────────────────────────────────────────────────────────────────┘
|
||
```
|