数据库设计说明书

数据库设计说明书一、项目背景与设计目标本章界定数据库设计的业务边界与技术指标，是后续表结构设计与性能优化的基准依据。1.1项目背景随着核心业务系统日活用户数预计突破100万，原单体架构下的单库单表已无法支撑每秒3000+的并发读写请求。为解决数据存储瓶颈、保障交易链路数据一致性，需重构现有数据库架构，建立支持水平扩展、读写分离的高可用数据库集群。1.2设计目标与原则本设计说明书旨在规范数据库的表结构、索引、约束及安全策略，确保系统在3年内无需进行架构级重构。•高可用性：核心业务数据零丢失，主从切换时间≤30•高性能：核心接口查询响应时间T99•可扩展性：支持按业务维度垂直分库，按用户ID水平分表。•安全合规：满足《中华人民共和国数据安全法》及《个人信息保护法》要求，敏感数据必须加密存储。1.3读者对象与应用场景本文档面向后端开发工程师（指导代码层ORM映射与SQL编写）、数据库管理员（指导生产环境DDL审批与配置）、测试工程师（指导造数与性能压测）。文档用于指导从开发环境搭建至生产环境上线的全生命周期数据层建设。二、总体架构设计数据库架构选型直接决定系统的吞吐量上限与容灾能力，本架构以MySQL8.0为核心底座，通过读写分离与分库分表突破单机瓶颈。2.1技术选型与部署架构数据库采用一主两从的高可用架构，核心组件选型如下：•数据库版本：MySQL8.0.32（优先使用此LTS版本；若无此版本则使用8.0.x最新小版本；严禁使用5.7及以下版本，因其不支持JSON高级函数及窗口函数）。•高可用组件：MHA(MasterHighAvailability)负责主库故障切换。•中间件：ShardingSphere5.2.0负责分库分表路由与读写分离。2.2存储引擎与字符集选择所有表必须统一使用InnoDB存储引擎与utf8mb4字符集。•存储引擎：必须使用InnoDB。MyISAM不支持事务与行级锁，并发写入会导致表级锁等待，甚至引发死锁致使系统假死。•字符集：必须使用utf8mb4。utf8在MySQL中仅支持3字节，遇到Emoji表情或生僻字会抛出Incorrectstringvalue异常导致写入失败。•排序规则：统一使用utf8mb4_0900_ai_ci，大小写及重音不敏感。2.3分库分表策略依据业务边界拆分为用户域、交易域、支付域三个物理库。交易核心表按用户ID取模进行水平拆分。•垂直分库：db_user、db_trade、db_pay物理隔离，避免单库表过多引发缓存池争用。•水平分表：订单表t_order_info拆分为16张物理表（t_order_info_0至t_order_info_15），分片算法为user_id%16。•ID生成方案：采用雪花算法生成全局唯一bigint型ID，必须配置独立的机器编号，防止时钟回拨导致ID重复。三、命名规范与数据字典统一的命名规范是降低团队沟通成本、避免SQL解析歧义的前提，任何自定义对象必须严格遵循本章标准。3.1命名规范•数据库/表名：全小写，下划线分隔，必须带业务模块前缀（如t_trade_order，t_user_info）。严禁使用大写字母或数据库保留字（如order、desc）。•字段名：全小写，下划线分隔，必须带有明确业务含义（如create_time、mobile_phone）。•索引名：◦主键索引：pk_字段名◦唯一索引：uk_字段名◦普通索引：idx_字段名•约束名：外键约束fk_从表_主表，唯一约束uc_表名_字段名。3.2数据类型标准选择合适的数据类型不仅能节省存储空间，更能直接提升索引命中率和查询效率。•金额类型：严禁使用float或double。必须使用decimal(18,2)存储精确金额，避免浮点数精度丢失导致财务对账失败。•时间类型：优先使用datetime；若需要存储带时区信息的跨国时间，使用timestamp。严禁使用varchar存储时间字符串。•状态类型：使用tinyint，并在数据字典中明确定义枚举值及含义（如0-禁用，1-启用）。•布尔类型：使用tinyint(1)，取值仅限0或1。•IP地址：使用bigint无符号存储（通过inet_aton转换），较varchar(15)节省50%空间且支持范围查询。3.3数据字典基线所有业务表必须包含以下审计字段，用于问题追溯：字段名类型默认值说明idbigint无主键，雪花算法生成create_timedatetimeCURRENT_TIMESTAMP数据创建时间update_timedatetimeCURRENTTIMESTAMPONUPDATECURRENTTIMESTAMP数据最后更新时间create_byvarchar(64)无创建人账号update_byvarchar(64)无更新人账号is_deletedtinyint(1)0逻辑删除标识：0-未删除，1-已删除四、核心表结构设计本章是数据库设计的核心，通过物理模型与业务规则的结合，确立数据在存储层的流转逻辑与约束机制。4.1用户域表设计用户表负责存储账户基础信息及认证凭证，是全系统的鉴权基石。CREATETABLE`t_user_info`(

`id`bigintNOTNULLCOMMENT'主键ID',

`user_no`varchar(32)NOTNULLCOMMENT'用户编号，业务唯一',

`mobile_phone`varchar(64)NOTNULLCOMMENT'手机号(AES加密存储)',

`nick_name`varchar(64)NOTNULLDEFAULT'匿名用户'COMMENT'昵称',

`status`tinyintNOTNULLDEFAULT1COMMENT'状态：0-冻结，1-正常',

`id_card`varchar(128)DEFAULTNULLCOMMENT'身份证号(AES加密存储)',

`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',

`update_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',

`is_deleted`tinyint(1)NOTNULLDEFAULT0COMMENT'逻辑删除标识',

PRIMARYKEY(`id`),

UNIQUEKEY`uk_user_no`(`user_no`),

UNIQUEKEY`uk_mobile_phone`(`mobile_phone`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='用户基础信息表';设计机理说明：

对手机号和身份证号采用AES-256算法在应用层加密后存入数据库，若直接明文存储，一旦发生数据库拖库（如SQL注入），将直接触犯《个人信息保护法》，面临巨额罚款。同时建立唯一索引，在并发注册场景下利用数据库唯一性约束兜底，确保不会生成重复用户。4.2交易域表设计订单表记录交易全生命周期状态，是资金流转与履约动作的唯一依据。CREATETABLE`t_order_info`(

`id`bigintNOTNULLCOMMENT'主键ID',

`order_no`varchar(32)NOTNULLCOMMENT'订单号，全链路唯一',

`user_id`bigintNOTNULLCOMMENT'下单用户ID',

`total_amount`decimal(18,2)NOTNULLCOMMENT'订单总金额',

`pay_amount`decimal(18,2)NOTNULLCOMMENT'实付金额',

`status`tinyintNOTNULLCOMMENT'状态：0-待支付，1-已支付，2-已发货，3-已完成，4-已取消',

`pay_time`datetimeDEFAULTNULLCOMMENT'支付时间',

`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',

`update_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',

PRIMARYKEY(`id`),

UNIQUEKEY`uk_order_no`(`order_no`),

KEY`idx_user_id_status`(`user_id`,`status`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='订单主表';4.3日志与审计域表设计操作日志表用于记录敏感接口的调用轨迹，供安全审计与故障回溯使用。CREATETABLE`t_sys_opt_log`(

`id`bigintNOTNULLAUTO_INCREMENTCOMMENT'自增主键',

`operator`varchar(64)NOTNULLCOMMENT'操作人账号',

`api_path`varchar(255)NOTNULLCOMMENT'接口路径',

`request_body`textCOMMENT'请求参数(脱敏后)',

`response_code`intNOTNULLCOMMENT'响应状态码',

`cost_time`intNOTNULLCOMMENT'耗时(毫秒)',

`ip_addr`bigintunsignedNOTNULLCOMMENT'IP地址(整数形式)',

`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'操作时间',

PRIMARYKEY(`id`),

KEY`idx_operator_time`(`operator`,`create_time`)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT='系统操作审计日志表';五、索引与约束设计索引是把双刃剑，合理的索引能将查询效率提升数千倍，但冗余索引会导致写入性能雪崩。本章规范了索引的创建与使用边界。5.1索引设计原则与方案•覆盖索引优先：查询字段必须尽量包含在联合索引中，避免回表。错误做法（`SELECT*FROMtuserinfoWHEREmobile_phone='138******'）会导致根据二级索引查到主键后，需再次回表查询聚簇索引获取全部字段，使IO次数翻倍。正确做法为建立idxmobilephone(mobile_phone)`并仅查询所需字段。•最左前缀匹配：联合索引idx_user_id_status(user_id,status)必须从左至右使用。若查询条件仅有status，索引失效，触发全表扫描。•禁忌三件套：严禁在区分度低的字段（如性别、状态）上单独建索引（后果：索引过滤数据量极少，优化器会放弃索引直接走全表扫描，不仅浪费存储还拖慢写入速度；替代方案：将其与高区分度字段建立联合索引）。•方案分层表达：模糊搜索优先使用Elasticsearch全文检索；若业务量小不具备ES集群条件，使用LIKE'前缀%'走索引范围查询；严禁使用LIKE'%中缀%'（会导致索引失效触发全表扫描）。5.2约束与级联策略•外键约束：严禁在业务表中使用物理外键（后果：每次关联写入都会加锁，高并发下极易引发死锁，且导致分库分表无法实施；替代方案：在应用层通过事务+代码逻辑保证数据一致性）。•唯一约束：核心业务单据（如订单号、支付流水号）必须在数据库层建立唯一索引作为最后一道防线。•非空约束：状态类、金额类字段必须添加NOTNULL约束，防止出现NULL值导致聚合函数（如COUNT、SUM）统计错误。六、安全与权限设计数据库安全策略是防止数据泄露与恶意篡改的最后一道闸门，权限分配遵循最小化原则。6.1账号与权限分级生产环境账号分为三级，严禁使用root账号连接业务应用。账号角色权限范围适用场景审批流程app_rw指定库表的SELECT,INSERT,UPDATE应用服务日常连接架构师审批app_ro指定库表的SELECT报表系统、只读节点查询DBA审批dba_admin全库所有权限紧急故障处理、DDL变更CTO审批6.2敏感数据加密与脱敏•存储加密：用户手机号、身份证号必须采用AES-256加密存储。密钥由KMS（密钥管理服务）统一托管，严禁硬编码在代码或配置文件中。•传输加密：JDBC连接串必须配置useSSL=true&requireSSL=true，防止中间人抓包截获明文数据。•动态脱敏：测试环境严禁使用真实生产数据；若确需排查问题导入生产快照，必须在导出时通过DBA工具对mobile_phone字段执行`CONCAT(LEFT(mobile_phone,3),'**',RIGHT(mobile_phone,4))`脱敏处理。七、性能优化与容量规划容量规划不能凭空臆断，需基于业务增长模型进行推算，并留有充足的缓冲余地。7.1容量评估与推算以核心订单表t_order_info为例进行容量推算：•基础数据：日均订单量N=50万单，单行平均数据长度•容量推算：3年总数据量V=•分表水位判定：按16张物理表拆分，单表数据量约12.8GB。InnoDB引擎B+树层数维持在3层时查询效率最佳（单表建议2000万行以内），3年后单表约3420万行。必须在第2年末启动扩容评估。7.2慢查询监控与预警建立基于Prometheus+Grafana的慢查询监控体系，执行PDCA闭环管理。•P（计划）：设定慢查询阈值。生产环境long_query_time=1秒，告警阈值单日慢查询次数>10•D（执行）：DBA每周一09:00通过pt-query-digest工具分析上一周慢查询日志，归集TOP10高耗SQL。•C（检查）：研发负责人评估慢SQL执行计划（EXPLAIN），确认是否出现Usingfilesort或Usingtemporary，是否未走索引。•D（改进）：责任研发72小时内提交优化工单（建立索引或重构SQL），DBA审核通过后在凌晨低峰期执行，验证无误后关闭工单。八、运维与变更管理流程无管控的DDL变更是引发线上故障的高频元凶，所有变更必须受控并具备回滚能力。8.1DDL变更管理流程变更分为三级，按风险等级启动不同审批与执行通道。•一级（低风险）：普通表加普通索引。研发提单→DBA审核（确认执行计划）→凌晨使用pt-online-schema-change在线执行。•二级（中风险）：修改字段类型、加唯一索引。研发提单→架构师审核→预发环境验证24小时无报错→生产环境执行。•三级（高风险）：删表、删字段、大表清理数据。必须编写详细回退脚本，在业务低峰期执行，执行前必须进行全量物理备份。禁忌三件套：严禁在业务高峰期（10:00-22:00）直接对大表执行ALTERTABLE（后果：会锁表导致应用连接池耗尽，服务雪崩；替代方案：必须使用pt-online-schema-change或gh-ost进行无锁变更，若不具备工具条件则必须在凌晨低峰期通过主从切换方式执行）。8.2备份与恢复方案•全量备份：每日03:00通过XtraBackup进行物理全量备份，保留期30天。•增量备份：基于Binlog实时同步至异地灾备机房，延迟≤1•RPO与RTO：数据恢复点目标（RPO）≤1分钟，恢复时间目标（RTO）≤8.3应急预案风险分级处置针对数据库宕机或性能骤降场景，按严重程度执行分级响应：•P1级（主库宕机导致核心交易全量报错）：◦判定标准：核心订单接口错误率>10◦启动权限：DBA研发负责