MySQL数据库设计与SQL编写规范
一、规范背景与目的
为充分发挥MySQL内核优势，规避劣势，在数据库变更处理流程、表设计及SQL编写等方面制定规范[1]，以保障业务系统的稳定运行。
二、设计规范
数据库设计
以下规范按【高危】、【强制】、【建议】三个级别标注：
- 库名须控制在32字符内，且模块表名体现join关系。
- 库名格式：业务系统名称_子系统名；同一模块表名需统一前缀。
- 分库命名通配格式为库名_编号或库名_时间。
- 创建数据库必须指定utf8或utf8mb4字符集。
表结构
- 表和列名限32字符内，仅用字母、数字与下划线，小写形式。
- 表名需与模块强相关。
- 建表时显式指定字符集为utf8或utf8mb4，存储引擎类型推荐InnoDB。
- 建表须带comment。
- 主键推荐id，类型为int或bigint且auto_increment。
- 核心表应含create_time与update_time字段。
- 所有字段为NOT NULL属性。
- 大字段如blob、text建议垂直拆分。
- 常用join查询字段可冗余存储。
- 中间表命名以tmp_开头，备份表以bak_开头。
- 对超百万行大表进行alter table操作需经DBA审核。
列数据类型优化
- 自增列推荐使用bigint类型。
- 状态、类型等字段推荐tinytint或smallint。
- IP地址字段推荐使用int类型。
- 不推荐使用enum、set类型。
- 避免使用blob、text类型。
- 金钱字段建议用int。
- 文本数据尽量用varchar。
- 时间类型优先timestamp。
索引设计
- InnoDB表主键为id int/bigint auto_increment，禁止更新。
- 索引命名规则：pk_/uk_/idx_ + 表名/字段名缩写。
- 索引类型须为BTREE（MEMORY表可选HASH或BTREE）。
- 单个索引记录长度不能超64KB。
- 单表索引数不超过7个。
- 建立联合索引时将区分度高的字段放前。
- 多表join时确保被驱动表连接列上有索引。
- 表中避免冗余索引。
分库分表、分区表
- 分区字段须有索引或组合索引首列。
- 单分区表的分区数不超过1024。
- 上线前指定分区表清理策略。
- 访问分区表SQL包含分区键。
- 分区表执行alter table操作于低峰期。
- 分库数不超过1024，分表数不超过4096。
- 单分表行数不超过500万，ibd文件大小不超过2GB。
- 水平分表推荐取模方式，日志报表类数据建议按日期分表。
字符集
- 数据库本身库、表、列字符集一致为utf8或utf8mb4。
- 前端程序字符集与数据库一致为utf8。
程序层DAO设计建议
- 推荐手动拼SQL+绑定变量传参方式。
- 程序需有连接超时与失败重连机制。
- 报错信息尽量提示原生态报错。
- 配置初始、最小、最大连接数与回收机制。
- log或history类型表上线前需清理或归档方案。
- 规避主从延迟影响业务。
- 更新类SQL尽量基于主键。
- 业务逻辑加锁顺序保持一致。
- 热点数据放入缓存中。
一个规范的建表语句示例
CREATE TABLE user ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `user_id` bigint(11) NOT NULL COMMENT '用户id', `username` varchar(45) NOT NULL COMMENT '真实姓名', `email` varchar(30) NOT NULL COMMENT '用户邮箱', `nickname` varchar(45) NOT NULL COMMENT '昵称', `avatar` int(11) NOT NULL COMMENT '头像', `birthday` date NOT NULL COMMENT '生日', `sex` tinyint(4) DEFAULT '0' COMMENT '性别', `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己', `user_resume` varchar(300) NOT NULL COMMENT '用户简历存放地址', `user_register_ip` int NOT NULL COMMENT '用户注册源ip', `create_time` timestamp NOT NULL COMMENT '用户记录创建时间', `update_time` timestamp NOT NULL COMMENT '用户资料修改时间', `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态', PRIMARY KEY (`id`), UNIQUE KEY `idx_user_id` (`user_id`), KEY `idx_username`(`username`), KEY `idx_create_time`(`create_time`,`user_review_status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';
SQL编写规范
DML语句
- SELECT语句必须指定具体字段。
- insert语句需指定具体字段。
- in值列表限制500以内。
- 事务涉及表全为InnoDB。
- write与事务发往主库，只读SQL发往从库。
- DML语句必须有where条件且使用索引。
- 生产环境禁用hint。
- where条件字段类型需一致。
- 禁止全模糊LIKE条件。
- 索引列避免使用函数或表达式。
- 减少使用or语句。
- 分页查询起点较高时，先过滤。
多表连接
- 禁止跨db join。
- 更新类SQL禁用join。
- 子查询推荐拆开或用join代替。
- 线上多表join不超过3个表。
- 多表连接推荐别名引用字段。
- 选取结果集小的表作为驱动表。
事务
- 事务操作行数控制2000以内。
- 批量操作控制间隔时间。
- 并发插入操作控制200以内。
- 建议事务隔离级别为repeatable-read。
- 事务包含SQL不超过5个。
- 更新语句尽量基于主键。
- 外部调用移出事务。
- 敏感select语句强制访问主库。
排序与分组
- 减少order by、group by、distinct使用。
- 利用索引检索排序好数据。
- 过滤结果集保持1000行以内。
线上禁止使用的SQL语句
- 禁用带limit的update|delete语句。
- 禁止关联子查询。
- 禁用procedure、function、trigger、views、event、外键约束。
- 禁用on duplicate key update。
- 禁止联表更新语句。