MySQL：库表设计完整规范与实战方案-北京尧图网络

库表设计的重要性一段程序的起点一、整体设计原则优先遵守1、范式适度基础遵循第三范式高频关联查询场景允许适当冗余避免过度范式导致大量 JOIN2、冷热分离大日志、历史归档数据单独分表 / 分库3、索引先行建表同时规划索引禁止后期随意新增大索引4、兼容扩展预留扩展字段禁止硬编码字段含义5、字符集统一推荐 utf8mb4支持 emoji排序规则utf8mb4_unicode_ci6、引擎统一业务表默认InnoDB事务、行锁、外键可选日志临时表可考虑 MyISAM极少场景。❌ 避坑不要使用utf8mysql utf8 ≠ utf8最多 3 字节不支持表情二、数据库Schema设计规范1. 库命名规范小写字母下划线禁止大写、中文、特殊符号格式业务模块_db示例order_db、user_db、goods_db2. 库参数标准配置建库语句CREATE DATABASE IF NOT EXISTS user_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;3. 分库策略简单区分垂直分库按业务拆分用户库、订单库、商品库水平分库单库数据量过大同一张表拆分到多个库三、数据表设计规范1. 表名规范小写、下划线名词复数形式格式模块_表名示例user_info、order_main、order_item禁止UserInfo、userInfo、用户表2. 必备标准字段所有业务表建议统一带上字段名类型说明idbigint unsigned NOT NULL主键自增主键雪花 ID 可选分布式create_timedatetime NOT NULL DEFAULT CURRENT_TIMESTAMP创建时间update_timedatetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP更新时间自动刷新is_deletedtinyint NOT NULL DEFAULT 0逻辑删除0 正常1 删除禁止物理删除分布式系统不建议自增主键改用BIGINT 雪花算法 ID3. 字段类型选型最佳实践数字类型主键 IDbigint unsigned不要 int数据量容易溢出状态标识tinyint0/1/2/3不要 char/varchar 存状态金额DECIMAL(m,n)严禁 float/double浮点精度丢失例decimal(12,2)最多千万级金额保留 2 位小数普通数量int超大数量用bigint字符串类型短标识、编码varchar(32) / varchar(64)名称、标题varchar(255)超长文本、备注text尽量少用无法建立普通索引varchar 长度够用即可不要统一全部 255时间类型时间点datetime推荐可读性强不受时区影响仅日期date仅时分秒time不推荐使用 timestamp时区限制、2038 上限布尔逻辑不用 bool统一tinyint(1)0 否1 是4. 字段命名规范小写下划线见名知意user_name、phone、order_amount状态统一后缀_statusorder_status、pay_status数量统一后缀_numbuy_num5. 约束规范1、主键 PRIMARY KEY单一主键优先id尽量避免联合主键2、唯一索引 UNIQUE手机号、用户名、订单号等唯一字段3、NOT NULL 优先能不为 NULL 尽量 NOT NULL设置默认值NULL 查询会带来额外开销4、谨慎使用外键 InnoDB 支持外键但高并发业务不推荐由应用程序保证数据一致性四、索引设计规范重中之重1. 索引分类主键索引PRIMARY KEY(id)普通索引单字段 / 联合索引唯一索引UNIQUE KEY uk_phone(phone)前缀索引超长字符串使用如 url2. 联合索引最左匹配原则建立idx_name_status(user_name, order_status)✅ 可以命中where user_name?/where user_name? and order_status?❌ 无法命中where order_status?3. 索引避坑清单1、不要在低基数字段单独建索引如性别、is_deleted2、索引字段禁止函数操作where DATE(create_time)xxx→ 失效3、不要超量建索引写入insert/update/delete会同步维护索引4、字符串匹配禁止左模糊like %xxx索引失效like xxx%可行五、完整建表示例用户主表 user_infoCREATE TABLE user_info ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_name varchar(64) NOT NULL COMMENT 用户名, phone varchar(32) NOT NULL COMMENT 手机号, password varchar(128) NOT NULL COMMENT 加密密码, user_status tinyint NOT NULL DEFAULT 1 COMMENT 用户状态 0禁用 1正常, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, is_deleted tinyint NOT NULL DEFAULT 0 COMMENT 逻辑删除 0未删 1已删, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), KEY idx_user_status (user_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户信息表;订单主表 order_mainCREATE TABLE order_main ( id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 订单主键, order_no varchar(64) NOT NULL COMMENT 订单编号, user_id bigint unsigned NOT NULL COMMENT 用户id, order_amount decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 订单金额, order_status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态, pay_status tinyint NOT NULL DEFAULT 0 COMMENT 支付状态, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted tinyint NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_userid_status (user_id,order_status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT订单主表;订单明细表 order_item一对多拆分规范主表存订单整体信息明细表存储多条商品禁止用逗号拼接商品 ID 存在一个字段CREATE TABLE order_item ( id bigint unsigned NOT NULL AUTO_INCREMENT, order_id bigint unsigned NOT NULL COMMENT 订单主表id, goods_id bigint unsigned NOT NULL COMMENT 商品id, goods_name varchar(255) NOT NULL COMMENT 商品名称快照, price decimal(12,2) NOT NULL COMMENT 下单单价, buy_num int NOT NULL DEFAULT 1 COMMENT 购买数量, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted tinyint NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_order_id (order_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT订单明细表;六、常用设计最佳实践1、一对多关系用户 1 → 订单 N订单表保存 user_id2、多对多关系用户 - 角色新建中间表user_role_rel存储 user_id、role_id联合唯一索引3、字典 / 常量状态含义不要写死在 SQL 注释建议建立字典表sys_dict代码读取4、大文本文章正文、富文本单独一张表避免主表查询加载大字段5、分页查询尽量基于有序索引字段分页避免limit 1000000,10深度分页七、开发规范红线禁止操作1、❌ 表、字段使用中文2、❌ 使用TEXT/BLOB建立普通索引3、❌ 使用 float/double 存储金额4、❌ 大量使用 NULL不设置默认值5、❌ 业务数据物理 DELETE6、❌ 一条字段存储多条数据逗号分隔 id7、❌ 随意创建联合索引不遵守最左前缀8、❌ 在线业务直接执行ALTER TABLE锁表操作。八、延伸方案数据量大水平分表按 user_id 哈希、时间范围分表读写压力高主从分离读走从库海量日志时序表、按月分区表PARTITION BY RANGE

MySQL：库表设计完整规范与实战方案

相关推荐

linux磁盘空间被占满但是找不到大文件

分布式文件系统元数据与数据分离设计及性能优化实践

注意及流程

更多资讯

基于PCA降维与BP神经网络的交通流量预测MATLAB项目实例

金融科技Java初级岗笔试复盘：集合并发、SQL优化与Spring核心考点

金融科技初级Java笔试全复盘：题型考点与编程题实战拆解

专科生AI论文工具实测：千笔与学术猹如何解决写作痛点

电力用户用电信息采集通信协议集合与测试工具实战指南

8个AI论文网站实测：是智商税还是论文写作加速器？

今日更新

MATLAB缩进快捷键与批量格式化实战指南

用Poco实现Qt风格信号与槽的跨平台C++方案

ESXi裸机虚拟化安装指南：从硬件兼容到虚拟机部署

本周精选

金融AI落地指南：从调研报告到技术选型与工程实践

大模型上下文成本与注意力优化技术深度解析——为何O(n²)平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测

基于GMM的语音识别系统原理与MATLAB实现详解

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？