引言本文主要从思路上梳理了核心表的设计框架。实际的表结构比这复杂得多——更多字段、更细的索引策略、更完整的分表规则。限于篇幅完整DDL和技术细节欢迎技术交流。一、业务场景分析理解数据特征才能设计好库表在开始设计表结构之前先把业务场景吃透。体育赛事直播平台的数据库访问模式和其他互联网产品有明显差异读写比例严重失衡直播间信息、赛事数据、球队资料等读请求占总请求的95%以上写操作集中在用户登录、投注记录、弹幕落库、订单生成等场景占比不足5%。这意味着数据库设计可以偏向读优化通过冗余字段、合理索引、缓存等手段提升查询效率。热点数据高度集中80%的查询集中在20%的赛事上——国家德比的直播间信息被访问次数是普通联赛的100倍以上。热点数据可以单独做缓存或特殊处理非热点数据走常规查询。数据访问呈现明显时间局部性比赛期间90分钟数据访问频率是平时的50倍以上。赛前一周开始预热赛中达到峰值赛后迅速回落。数据一致性要求分场景用户余额、投注记录、订单状态需要强一致性不允许脏读。直播间在线人数、弹幕内容可以接受最终一致性容忍秒级延迟。不同业务场景需要不同的一致性级别不能一刀切。二、核心表结构设计从业务模型到表结构落地2.1 用户域表设计用户域是系统的核心基础围绕用户身份认证、权限控制、资产管理和个人设置展开。用户表存储账户的基本身份信息与安全凭证采用BCrypt加密存储密码手机号作为唯一登录凭证并建立唯一索引。余额字段使用DECIMAL(12,2)类型避免浮点数精度误差所有余额变更操作必须记录流水明细确保可追溯。用户扩展表存储用户的个性化信息与主表按1:1关系独立存储将高频访问的基础信息与低频访问的扩展信息物理隔离减少主表的字段冗余和行记录大小。用户余额流水表是所有余额变更的审计依据充值、投注扣减、竞猜中奖、提现等操作均在此记录。流水表采用append-only设计只插入不更新通过流水号保证幂等性避免重复处理。用户表主键使用自增BIGINT并配套设计基于手机号的唯一索引保障登录查询效率。状态字段标记账户是否正常、是否被封禁等状态。密码字段存储BCrypt加密后的密文长度60字符。sqlCREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, password VARCHAR(100) NOT NULL COMMENT 密码(BCrypt), balance DECIMAL(12,2) DEFAULT 0.00 COMMENT 余额, status TINYINT DEFAULT 1 COMMENT 状态:1正常,2封禁, register_ip VARCHAR(45) COMMENT 注册IP, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), KEY idx_status (status), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;2.2 赛事域表设计赛事数据是体育直播平台最核心的内容资产数据结构相对稳定但查询频率极高。赛事主表存储单场比赛的核心信息包括对阵双方、开赛时间、当前比分、比赛状态等。比赛状态字段包含未开始、进行中、已结束、已取消等枚举值状态变更驱动业务流程如进行中时开启竞猜、已结束时触发结算。赛事表在设计时需要特别注意球队名称的存储方式球队名称直接冗余在赛事表中还是通过球队ID关联查询采用冗余存储方式在赛事表中直接存储球队名称和Logo URL避免每次查询都需要JOIN球队表。虽然存在数据冗余但查询性能大幅提升符合读多写少的场景特征。sqlCREATE TABLE match ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 赛事ID, home_team_name VARCHAR(100) NOT NULL COMMENT 主队名称, away_team_name VARCHAR(100) NOT NULL COMMENT 客队名称, home_team_logo VARCHAR(255) COMMENT 主队Logo, away_team_logo VARCHAR(255) COMMENT 客队Logo, start_time DATETIME NOT NULL COMMENT 开赛时间, status TINYINT DEFAULT 0 COMMENT 状态:0未开始,1进行中,2已结束,3已取消, home_score TINYINT DEFAULT 0 COMMENT 主队比分, away_score TINYINT DEFAULT 0 COMMENT 客队比分, match_time VARCHAR(10) COMMENT 比赛进行时间(如45:30), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_start_time (start_time), KEY idx_status_start_time (status, start_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT赛事表;比分字段独立存储在赛事主表中不单独建立比分历史表减少查询复杂度。比赛进行时间以字符串形式存储前端直接展示。2.3 直播域表设计直播间是连接赛事和用户的桥梁。直播间表通过match_id关联到具体赛事一个赛事可以对应一个直播间。stream_key由后端动态生成是推流鉴权的核心凭证建立唯一索引保证不可重复。推流状态实时更新记录当前推流是否正常、推流开始时间、观看人数等。在线人数是高频更新字段采用Redis原子操作维护MySQL只做最终持久化。直播间状态字段控制前端展示与推流状态联动。sqlCREATE TABLE live_room ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 直播间ID, match_id BIGINT NOT NULL COMMENT 关联赛事ID, room_name VARCHAR(100) COMMENT 直播间名称, stream_key VARCHAR(64) NOT NULL COMMENT 推流密钥, push_status TINYINT DEFAULT 0 COMMENT 推流状态:0未推流,1推流中,2已结束, online_count INT DEFAULT 0 COMMENT 在线人数, push_start_time DATETIME COMMENT 推流开始时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_stream_key (stream_key), KEY idx_match_id (match_id), KEY idx_push_status (push_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT直播间表;2.4 竞猜域表设计竞猜是体育平台提升用户活跃度的核心功能业务逻辑复杂涉及多个表的协同操作。竞猜项目表存储单场比赛的竞猜配置包括竞猜类型胜负/比分/总进球数、各选项的赔率、截止投注时间等。赔率支持动态调整通过version字段实现乐观锁防止并发修改冲突。sqlCREATE TABLE bet_project ( id BIGINT NOT NULL AUTO_INCREMENT, match_id BIGINT NOT NULL, bet_type TINYINT NOT NULL COMMENT 竞猜类型:1胜负,2比分,3总进球, option_a VARCHAR(50) COMMENT 选项A(如主胜), option_b VARCHAR(50) COMMENT 选项B(如平局), option_c VARCHAR(50) COMMENT 选项C(如客胜), odds_a DECIMAL(5,2) COMMENT A赔率, odds_b DECIMAL(5,2) COMMENT B赔率, odds_c DECIMAL(5,2) COMMENT C赔率, deadline DATETIME NOT NULL COMMENT 截止投注时间, status TINYINT DEFAULT 1 COMMENT 状态:1进行中,2已截止,3已结算, version INT DEFAULT 0 COMMENT 乐观锁版本号, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_match_id (match_id), KEY idx_status_deadline (status, deadline) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT竞猜项目表;投注记录表存储用户每次投注的明细中奖金额在结算时回填。投注状态与订单状态类似需要完整的状态流转管理。sqlCREATE TABLE bet_record ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, project_id BIGINT NOT NULL, match_id BIGINT NOT NULL, bet_option VARCHAR(50) NOT NULL COMMENT 投注选项, bet_amount DECIMAL(10,2) NOT NULL COMMENT 投注金额, win_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT 中奖金额, status TINYINT DEFAULT 1 COMMENT 状态:1待开奖,2已中奖,3未中奖, settle_time DATETIME COMMENT 结算时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_project_id (project_id), KEY idx_match_id (match_id), KEY idx_user_match (user_id, match_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT投注记录表;复合索引说明idx_user_match (user_id, match_id)用于防刷校验查询用户是否已投注某场比赛覆盖索引避免回表。2.5 交易域表设计订单表记录所有资金变动交易包括充值、打赏、付费解锁等。订单状态采用完整的状态机设计待支付→已支付→已完成。支付回调是外部依赖可能重复通知需要request_id做幂等性保证。订单表按时间维度设计索引便于对账和数据统计同时配合按月分表策略。sqlCREATE TABLE order ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL, type TINYINT NOT NULL COMMENT 类型:1充值,2打赏,3解锁, amount DECIMAL(10,2) NOT NULL, pay_method VARCHAR(20) COMMENT 支付方式, request_id VARCHAR(64) COMMENT 幂等请求ID, status TINYINT DEFAULT 0 COMMENT 状态:0待支付,1已支付,2已完成,3已取消, pay_time DATETIME COMMENT 支付时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), UNIQUE KEY uk_request_id (request_id), KEY idx_user_id (user_id), KEY idx_status (status), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;提现表存储用户提现申请与订单表类似需要完整的状态流转管理待审核→已通过→已打款或待审核→已驳回。sqlCREATE TABLE withdraw ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, account VARCHAR(100) NOT NULL COMMENT 收款账号, status TINYINT DEFAULT 0 COMMENT 状态:0待审核,1已通过,2已驳回,3已打款, auditor_id BIGINT COMMENT 审核人ID, audit_time DATETIME COMMENT 审核时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT提现表;2.6 社区域表设计社区模块相对轻量表结构较为简单。帖子表存储用户发布的内容支持图文混排图片以JSON数组存储。帖子状态用于审核和删除控制。sqlCREATE TABLE post ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, content TEXT COMMENT 内容, images JSON COMMENT 图片列表(JSON数组), like_count INT DEFAULT 0, comment_count INT DEFAULT 0, status TINYINT DEFAULT 1 COMMENT 状态:1正常,2已删除,3审核中, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_status_created (status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT帖子表;评论表支持嵌套回复通过parent_id字段点赞和评论数冗余在帖子表中避免每次统计都需要COUNT查询。sqlCREATE TABLE comment ( id BIGINT NOT NULL AUTO_INCREMENT, post_id BIGINT NOT NULL, user_id BIGINT NOT NULL, parent_id BIGINT DEFAULT 0 COMMENT 父评论ID(0表示一级评论), content VARCHAR(500) NOT NULL, like_count INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_post_id (post_id), KEY idx_parent_id (parent_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT评论表;三、索引设计策略3.1 索引设计原则索引的设计服务于具体的查询场景星逐赛事采用以下原则最左前缀匹配原则联合索引idx_status_start_time (status, start_time)可以支持status单独查询也可以支持status start_time组合查询但不支持start_time单独查询。设计联合索引时将过滤性最强的字段放在最左位置。区分度优先区分度高的字段优先建索引。例如phone区分度极高唯一status区分度低只有几个值单独建status索引效果有限通常与start_time等字段组合使用。查询频率驱动只对WHERE、JOIN、ORDER BY中频繁出现的字段建索引不常用的字段不建索引。避免冗余索引已有idx_status_start_time (status, start_time)的情况下不再建idx_status减少维护成本和存储空间。3.2 联合索引实战案例以下索引是星逐赛事生产环境中最关键的几个表索引名称字段组合覆盖查询matchidx_status_start_time(status, start_time)查询进行中的比赛列表live_roomidx_stream_key(stream_key)推流鉴权查询bet_recordidx_user_match(user_id, match_id)防刷校验orderuk_request_id(request_id)幂等性校验postidx_status_created(status, created_at)查询正常帖子列表倒序3.3 索引命中验证所有上线后的索引变更必须通过EXPLAIN验证执行计划。以下是一个典型查询的执行计划对比sql-- 查询进行中的比赛按时间倒序排列 EXPLAIN SELECT * FROM match WHERE status 1 AND start_time NOW() ORDER BY start_time\G -- 使用 idx_status_start_time 后typerangerows50ExtraUsing index condition执行计划解读typerange表示范围扫描效率较高rows50表示扫描行数可控ExtraUsing index condition表示索引下推无需回表。四、读写分离读多写少的必选方案体育赛事直播平台95%的请求是读请求读写分离是性价比最高的扩展手段。4.1 主从架构设计星逐赛事采用一主三从的MySQL架构主库处理所有写入操作INSERT/UPDATE/DELETE保证数据一致性从库1承载核心读业务直播间信息、赛事数据专用查询通道从库2承载社区读业务帖子列表、评论与核心业务隔离从库3作为分析和备份专用节点不影响线上业务读写分离通过ShardingSphere-JDBC实现代码零侵入yamlspring: shardingsphere: masterslave: name: ms master-data-source-name: master slave-data-source-names: slave1, slave2, slave3 load-balance-algorithm-type: round_robin主从延迟监控实时监控Seconds_Behind_Master指标延迟超过5秒触发告警超过30秒将读流量切换到主库。4.2 多数据源配置对于某些特殊场景手动指定数据源javaService public class MatchService { // 默认走从库 public ListMatch getMatchList() { return matchMapper.selectList(...); } // 强制走主库数据实时性要求极高 Master public Match getMatchForUpdate(Long id) { return matchMapper.selectById(id); } }Master注解通过AOP实现在方法执行前将数据源切换到主库。五、分库分表实践应对数据量增长单表数据量超过500万行或单库QPS超过5000时必须考虑分库分表。5.1 分表策略星逐赛事采用ShardingSphere-JDBC实现分库分表表名分表键分表数量分表策略useruser_id16hash取模ordercreated_at按月分表按时间bet_recorduser_id16hash取模bet_record_archivecreated_at按月分表按时间用户表分表按user_idyamlsharding: tables: user: actual-data-nodes: db_master.user_${0..15} table-strategy: inline: sharding-column: id algorithm-expression: user_${id % 16}订单表按月分表yamlsharding: tables: order: actual-data-nodes: db_master.order_${2024_01..2026_12} table-strategy: standard: sharding-column: created_at precise-algorithm-class-name: com.xxx.OrderShardingAlgorithm5.2 数据归档策略对于历史数据采用冷热分离策略热数据最近3个月保留在分表中在线可查温数据3-12个月迁移到归档表按需查询冷数据12个月以上导出到OSS或Hive离线存储归档任务通过定时任务执行每天凌晨2点处理前一天的订单和投注记录。5.3 跨表查询的解决方案分表后跨表查询如按时间范围查询所有用户的订单变得困难。解决方案冗余存储在订单表中同时存储user_id和created_at按时间分表的同时支持用户维度查询双写同步同时写入分表和ES复杂查询走ES业务规避页面查询强制带分表键避免全表扫描六、数据库运维与监控6.1 慢查询治理慢查询是数据库性能的隐形杀手必须建立持续的治理机制。星逐赛事的慢查询治理体系发现与告警开启MySQL慢查询日志long_query_time1秒配合Druid监控实时展示TOP慢SQL超过阈值自动触发告警并推送钉钉群。分析与优化通过EXPLAIN分析执行计划重点优化全表扫描、文件排序、临时表创建等问题。给WHERE/JOIN/ORDER BY字段加索引避免SELECT *拆解复杂JOIN为多次简单查询。案例赛事列表接口三表关联查询未走索引执行时间超过5秒。加联合索引后降到50毫秒接口响应时间从2秒降到150ms。sql-- 优化前5秒 SELECT * FROM match m LEFT JOIN team t1 ON m.home_team_id t1.id LEFT JOIN team t2 ON m.away_team_id t2.id WHERE m.status 1 AND m.start_time NOW(); -- 加联合索引50ms ALTER TABLE match ADD INDEX idx_status_time (status, start_time);持续监控慢查询月报机制每月分析TOP10慢SQL并优化。高频查询建立基准线指标劣化超过20%触发review。6.2 连接池监控与调优HikariCP连接池监控指标活跃连接数、空闲连接数、等待获取连接的线程数、连接超时次数。告警规则活跃连接数 最大连接数的80%连接池紧张告警连接等待超时次数 10次/分钟接口或SQL问题告警调优策略调整连接池大小读业务加大连接池100写业务减小连接池20优化SQL执行时间减少连接占用时长检查连接泄漏确认所有连接在使用后正确关闭七、灾备与数据恢复7.1 备份策略星逐赛事采用多重备份策略保障数据安全全量备份每天凌晨2点执行mysqldump全量备份保留最近30天增量备份每6小时binlog增量备份保留最近7天异地备份备份文件同步到异地OSS防止机房级故障7.2 恢复演练每季度执行一次数据恢复演练验证备份的有效性和恢复流程从全量备份恢复数据约1小时应用增量binlog恢复到指定时间点约30分钟验证数据完整性记录恢复时间和问题点持续优化7.3 高可用切换MySQL高可用采用MHAMaster High Availability方案自动监控主库健康状态故障时自动切换切换时间控制在30秒以内应用层配置自动重连切换过程用户基本无感知八、总结数据库架构设计核心原则可以总结为六点表结构设计三范式与冗余的平衡核心业务表遵循三范式减少数据冗余但为查询性能适度冗余如赛事表中冗余球队名称。索引策略以查询驱动基于实际查询场景设计索引用EXPLAIN验证定期清理无效索引。读写分离解决读压力一主三从架构读请求分流到从库主库专注写入。社区业务独立从库与核心业务隔离。分库分表应对数据增长用户表按ID哈希分16表订单表按月分表历史数据定期归档。冷热分离降低存储成本热数据在线存储温数据归档冷数据离线存储。监控告警覆盖全链路慢查询、连接池、主从延迟、备份状态全面可观测。 千万级数据0故障：星逐赛事直播平台数据库架构设计与实践
引言本文主要从思路上梳理了核心表的设计框架。实际的表结构比这复杂得多——更多字段、更细的索引策略、更完整的分表规则。限于篇幅完整DDL和技术细节欢迎技术交流。一、业务场景分析理解数据特征才能设计好库表在开始设计表结构之前先把业务场景吃透。体育赛事直播平台的数据库访问模式和其他互联网产品有明显差异读写比例严重失衡直播间信息、赛事数据、球队资料等读请求占总请求的95%以上写操作集中在用户登录、投注记录、弹幕落库、订单生成等场景占比不足5%。这意味着数据库设计可以偏向读优化通过冗余字段、合理索引、缓存等手段提升查询效率。热点数据高度集中80%的查询集中在20%的赛事上——国家德比的直播间信息被访问次数是普通联赛的100倍以上。热点数据可以单独做缓存或特殊处理非热点数据走常规查询。数据访问呈现明显时间局部性比赛期间90分钟数据访问频率是平时的50倍以上。赛前一周开始预热赛中达到峰值赛后迅速回落。数据一致性要求分场景用户余额、投注记录、订单状态需要强一致性不允许脏读。直播间在线人数、弹幕内容可以接受最终一致性容忍秒级延迟。不同业务场景需要不同的一致性级别不能一刀切。二、核心表结构设计从业务模型到表结构落地2.1 用户域表设计用户域是系统的核心基础围绕用户身份认证、权限控制、资产管理和个人设置展开。用户表存储账户的基本身份信息与安全凭证采用BCrypt加密存储密码手机号作为唯一登录凭证并建立唯一索引。余额字段使用DECIMAL(12,2)类型避免浮点数精度误差所有余额变更操作必须记录流水明细确保可追溯。用户扩展表存储用户的个性化信息与主表按1:1关系独立存储将高频访问的基础信息与低频访问的扩展信息物理隔离减少主表的字段冗余和行记录大小。用户余额流水表是所有余额变更的审计依据充值、投注扣减、竞猜中奖、提现等操作均在此记录。流水表采用append-only设计只插入不更新通过流水号保证幂等性避免重复处理。用户表主键使用自增BIGINT并配套设计基于手机号的唯一索引保障登录查询效率。状态字段标记账户是否正常、是否被封禁等状态。密码字段存储BCrypt加密后的密文长度60字符。sqlCREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 用户ID, phone VARCHAR(20) NOT NULL COMMENT 手机号, password VARCHAR(100) NOT NULL COMMENT 密码(BCrypt), balance DECIMAL(12,2) DEFAULT 0.00 COMMENT 余额, status TINYINT DEFAULT 1 COMMENT 状态:1正常,2封禁, register_ip VARCHAR(45) COMMENT 注册IP, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), KEY idx_status (status), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;2.2 赛事域表设计赛事数据是体育直播平台最核心的内容资产数据结构相对稳定但查询频率极高。赛事主表存储单场比赛的核心信息包括对阵双方、开赛时间、当前比分、比赛状态等。比赛状态字段包含未开始、进行中、已结束、已取消等枚举值状态变更驱动业务流程如进行中时开启竞猜、已结束时触发结算。赛事表在设计时需要特别注意球队名称的存储方式球队名称直接冗余在赛事表中还是通过球队ID关联查询采用冗余存储方式在赛事表中直接存储球队名称和Logo URL避免每次查询都需要JOIN球队表。虽然存在数据冗余但查询性能大幅提升符合读多写少的场景特征。sqlCREATE TABLE match ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 赛事ID, home_team_name VARCHAR(100) NOT NULL COMMENT 主队名称, away_team_name VARCHAR(100) NOT NULL COMMENT 客队名称, home_team_logo VARCHAR(255) COMMENT 主队Logo, away_team_logo VARCHAR(255) COMMENT 客队Logo, start_time DATETIME NOT NULL COMMENT 开赛时间, status TINYINT DEFAULT 0 COMMENT 状态:0未开始,1进行中,2已结束,3已取消, home_score TINYINT DEFAULT 0 COMMENT 主队比分, away_score TINYINT DEFAULT 0 COMMENT 客队比分, match_time VARCHAR(10) COMMENT 比赛进行时间(如45:30), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_start_time (start_time), KEY idx_status_start_time (status, start_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT赛事表;比分字段独立存储在赛事主表中不单独建立比分历史表减少查询复杂度。比赛进行时间以字符串形式存储前端直接展示。2.3 直播域表设计直播间是连接赛事和用户的桥梁。直播间表通过match_id关联到具体赛事一个赛事可以对应一个直播间。stream_key由后端动态生成是推流鉴权的核心凭证建立唯一索引保证不可重复。推流状态实时更新记录当前推流是否正常、推流开始时间、观看人数等。在线人数是高频更新字段采用Redis原子操作维护MySQL只做最终持久化。直播间状态字段控制前端展示与推流状态联动。sqlCREATE TABLE live_room ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 直播间ID, match_id BIGINT NOT NULL COMMENT 关联赛事ID, room_name VARCHAR(100) COMMENT 直播间名称, stream_key VARCHAR(64) NOT NULL COMMENT 推流密钥, push_status TINYINT DEFAULT 0 COMMENT 推流状态:0未推流,1推流中,2已结束, online_count INT DEFAULT 0 COMMENT 在线人数, push_start_time DATETIME COMMENT 推流开始时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_stream_key (stream_key), KEY idx_match_id (match_id), KEY idx_push_status (push_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT直播间表;2.4 竞猜域表设计竞猜是体育平台提升用户活跃度的核心功能业务逻辑复杂涉及多个表的协同操作。竞猜项目表存储单场比赛的竞猜配置包括竞猜类型胜负/比分/总进球数、各选项的赔率、截止投注时间等。赔率支持动态调整通过version字段实现乐观锁防止并发修改冲突。sqlCREATE TABLE bet_project ( id BIGINT NOT NULL AUTO_INCREMENT, match_id BIGINT NOT NULL, bet_type TINYINT NOT NULL COMMENT 竞猜类型:1胜负,2比分,3总进球, option_a VARCHAR(50) COMMENT 选项A(如主胜), option_b VARCHAR(50) COMMENT 选项B(如平局), option_c VARCHAR(50) COMMENT 选项C(如客胜), odds_a DECIMAL(5,2) COMMENT A赔率, odds_b DECIMAL(5,2) COMMENT B赔率, odds_c DECIMAL(5,2) COMMENT C赔率, deadline DATETIME NOT NULL COMMENT 截止投注时间, status TINYINT DEFAULT 1 COMMENT 状态:1进行中,2已截止,3已结算, version INT DEFAULT 0 COMMENT 乐观锁版本号, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_match_id (match_id), KEY idx_status_deadline (status, deadline) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT竞猜项目表;投注记录表存储用户每次投注的明细中奖金额在结算时回填。投注状态与订单状态类似需要完整的状态流转管理。sqlCREATE TABLE bet_record ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, project_id BIGINT NOT NULL, match_id BIGINT NOT NULL, bet_option VARCHAR(50) NOT NULL COMMENT 投注选项, bet_amount DECIMAL(10,2) NOT NULL COMMENT 投注金额, win_amount DECIMAL(10,2) DEFAULT 0.00 COMMENT 中奖金额, status TINYINT DEFAULT 1 COMMENT 状态:1待开奖,2已中奖,3未中奖, settle_time DATETIME COMMENT 结算时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_project_id (project_id), KEY idx_match_id (match_id), KEY idx_user_match (user_id, match_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT投注记录表;复合索引说明idx_user_match (user_id, match_id)用于防刷校验查询用户是否已投注某场比赛覆盖索引避免回表。2.5 交易域表设计订单表记录所有资金变动交易包括充值、打赏、付费解锁等。订单状态采用完整的状态机设计待支付→已支付→已完成。支付回调是外部依赖可能重复通知需要request_id做幂等性保证。订单表按时间维度设计索引便于对账和数据统计同时配合按月分表策略。sqlCREATE TABLE order ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL, type TINYINT NOT NULL COMMENT 类型:1充值,2打赏,3解锁, amount DECIMAL(10,2) NOT NULL, pay_method VARCHAR(20) COMMENT 支付方式, request_id VARCHAR(64) COMMENT 幂等请求ID, status TINYINT DEFAULT 0 COMMENT 状态:0待支付,1已支付,2已完成,3已取消, pay_time DATETIME COMMENT 支付时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), UNIQUE KEY uk_request_id (request_id), KEY idx_user_id (user_id), KEY idx_status (status), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;提现表存储用户提现申请与订单表类似需要完整的状态流转管理待审核→已通过→已打款或待审核→已驳回。sqlCREATE TABLE withdraw ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, account VARCHAR(100) NOT NULL COMMENT 收款账号, status TINYINT DEFAULT 0 COMMENT 状态:0待审核,1已通过,2已驳回,3已打款, auditor_id BIGINT COMMENT 审核人ID, audit_time DATETIME COMMENT 审核时间, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT提现表;2.6 社区域表设计社区模块相对轻量表结构较为简单。帖子表存储用户发布的内容支持图文混排图片以JSON数组存储。帖子状态用于审核和删除控制。sqlCREATE TABLE post ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, content TEXT COMMENT 内容, images JSON COMMENT 图片列表(JSON数组), like_count INT DEFAULT 0, comment_count INT DEFAULT 0, status TINYINT DEFAULT 1 COMMENT 状态:1正常,2已删除,3审核中, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_status_created (status, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT帖子表;评论表支持嵌套回复通过parent_id字段点赞和评论数冗余在帖子表中避免每次统计都需要COUNT查询。sqlCREATE TABLE comment ( id BIGINT NOT NULL AUTO_INCREMENT, post_id BIGINT NOT NULL, user_id BIGINT NOT NULL, parent_id BIGINT DEFAULT 0 COMMENT 父评论ID(0表示一级评论), content VARCHAR(500) NOT NULL, like_count INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_post_id (post_id), KEY idx_parent_id (parent_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT评论表;三、索引设计策略3.1 索引设计原则索引的设计服务于具体的查询场景星逐赛事采用以下原则最左前缀匹配原则联合索引idx_status_start_time (status, start_time)可以支持status单独查询也可以支持status start_time组合查询但不支持start_time单独查询。设计联合索引时将过滤性最强的字段放在最左位置。区分度优先区分度高的字段优先建索引。例如phone区分度极高唯一status区分度低只有几个值单独建status索引效果有限通常与start_time等字段组合使用。查询频率驱动只对WHERE、JOIN、ORDER BY中频繁出现的字段建索引不常用的字段不建索引。避免冗余索引已有idx_status_start_time (status, start_time)的情况下不再建idx_status减少维护成本和存储空间。3.2 联合索引实战案例以下索引是星逐赛事生产环境中最关键的几个表索引名称字段组合覆盖查询matchidx_status_start_time(status, start_time)查询进行中的比赛列表live_roomidx_stream_key(stream_key)推流鉴权查询bet_recordidx_user_match(user_id, match_id)防刷校验orderuk_request_id(request_id)幂等性校验postidx_status_created(status, created_at)查询正常帖子列表倒序3.3 索引命中验证所有上线后的索引变更必须通过EXPLAIN验证执行计划。以下是一个典型查询的执行计划对比sql-- 查询进行中的比赛按时间倒序排列 EXPLAIN SELECT * FROM match WHERE status 1 AND start_time NOW() ORDER BY start_time\G -- 使用 idx_status_start_time 后typerangerows50ExtraUsing index condition执行计划解读typerange表示范围扫描效率较高rows50表示扫描行数可控ExtraUsing index condition表示索引下推无需回表。四、读写分离读多写少的必选方案体育赛事直播平台95%的请求是读请求读写分离是性价比最高的扩展手段。4.1 主从架构设计星逐赛事采用一主三从的MySQL架构主库处理所有写入操作INSERT/UPDATE/DELETE保证数据一致性从库1承载核心读业务直播间信息、赛事数据专用查询通道从库2承载社区读业务帖子列表、评论与核心业务隔离从库3作为分析和备份专用节点不影响线上业务读写分离通过ShardingSphere-JDBC实现代码零侵入yamlspring: shardingsphere: masterslave: name: ms master-data-source-name: master slave-data-source-names: slave1, slave2, slave3 load-balance-algorithm-type: round_robin主从延迟监控实时监控Seconds_Behind_Master指标延迟超过5秒触发告警超过30秒将读流量切换到主库。4.2 多数据源配置对于某些特殊场景手动指定数据源javaService public class MatchService { // 默认走从库 public ListMatch getMatchList() { return matchMapper.selectList(...); } // 强制走主库数据实时性要求极高 Master public Match getMatchForUpdate(Long id) { return matchMapper.selectById(id); } }Master注解通过AOP实现在方法执行前将数据源切换到主库。五、分库分表实践应对数据量增长单表数据量超过500万行或单库QPS超过5000时必须考虑分库分表。5.1 分表策略星逐赛事采用ShardingSphere-JDBC实现分库分表表名分表键分表数量分表策略useruser_id16hash取模ordercreated_at按月分表按时间bet_recorduser_id16hash取模bet_record_archivecreated_at按月分表按时间用户表分表按user_idyamlsharding: tables: user: actual-data-nodes: db_master.user_${0..15} table-strategy: inline: sharding-column: id algorithm-expression: user_${id % 16}订单表按月分表yamlsharding: tables: order: actual-data-nodes: db_master.order_${2024_01..2026_12} table-strategy: standard: sharding-column: created_at precise-algorithm-class-name: com.xxx.OrderShardingAlgorithm5.2 数据归档策略对于历史数据采用冷热分离策略热数据最近3个月保留在分表中在线可查温数据3-12个月迁移到归档表按需查询冷数据12个月以上导出到OSS或Hive离线存储归档任务通过定时任务执行每天凌晨2点处理前一天的订单和投注记录。5.3 跨表查询的解决方案分表后跨表查询如按时间范围查询所有用户的订单变得困难。解决方案冗余存储在订单表中同时存储user_id和created_at按时间分表的同时支持用户维度查询双写同步同时写入分表和ES复杂查询走ES业务规避页面查询强制带分表键避免全表扫描六、数据库运维与监控6.1 慢查询治理慢查询是数据库性能的隐形杀手必须建立持续的治理机制。星逐赛事的慢查询治理体系发现与告警开启MySQL慢查询日志long_query_time1秒配合Druid监控实时展示TOP慢SQL超过阈值自动触发告警并推送钉钉群。分析与优化通过EXPLAIN分析执行计划重点优化全表扫描、文件排序、临时表创建等问题。给WHERE/JOIN/ORDER BY字段加索引避免SELECT *拆解复杂JOIN为多次简单查询。案例赛事列表接口三表关联查询未走索引执行时间超过5秒。加联合索引后降到50毫秒接口响应时间从2秒降到150ms。sql-- 优化前5秒 SELECT * FROM match m LEFT JOIN team t1 ON m.home_team_id t1.id LEFT JOIN team t2 ON m.away_team_id t2.id WHERE m.status 1 AND m.start_time NOW(); -- 加联合索引50ms ALTER TABLE match ADD INDEX idx_status_time (status, start_time);持续监控慢查询月报机制每月分析TOP10慢SQL并优化。高频查询建立基准线指标劣化超过20%触发review。6.2 连接池监控与调优HikariCP连接池监控指标活跃连接数、空闲连接数、等待获取连接的线程数、连接超时次数。告警规则活跃连接数 最大连接数的80%连接池紧张告警连接等待超时次数 10次/分钟接口或SQL问题告警调优策略调整连接池大小读业务加大连接池100写业务减小连接池20优化SQL执行时间减少连接占用时长检查连接泄漏确认所有连接在使用后正确关闭七、灾备与数据恢复7.1 备份策略星逐赛事采用多重备份策略保障数据安全全量备份每天凌晨2点执行mysqldump全量备份保留最近30天增量备份每6小时binlog增量备份保留最近7天异地备份备份文件同步到异地OSS防止机房级故障7.2 恢复演练每季度执行一次数据恢复演练验证备份的有效性和恢复流程从全量备份恢复数据约1小时应用增量binlog恢复到指定时间点约30分钟验证数据完整性记录恢复时间和问题点持续优化7.3 高可用切换MySQL高可用采用MHAMaster High Availability方案自动监控主库健康状态故障时自动切换切换时间控制在30秒以内应用层配置自动重连切换过程用户基本无感知八、总结数据库架构设计核心原则可以总结为六点表结构设计三范式与冗余的平衡核心业务表遵循三范式减少数据冗余但为查询性能适度冗余如赛事表中冗余球队名称。索引策略以查询驱动基于实际查询场景设计索引用EXPLAIN验证定期清理无效索引。读写分离解决读压力一主三从架构读请求分流到从库主库专注写入。社区业务独立从库与核心业务隔离。分库分表应对数据增长用户表按ID哈希分16表订单表按月分表历史数据定期归档。冷热分离降低存储成本热数据在线存储温数据归档冷数据离线存储。监控告警覆盖全链路慢查询、连接池、主从延迟、备份状态全面可观测。