MySQL 规范
一、 数据库环境
以下为单节点环境部署,分布式环境不适用;
dev
:开发环境,开发可读写,可修改表结构。开发人员可以修改表结构,可以随意修改其中的数据但是需要保证不影响其他开发同事;qa
:测试环境,开发可读写,开发人员可以通过工具修改表结构; sim:模拟环境,开发可读写,发起上线请求时,会先在这个环境上进行预执行,这个环境也可供部署上线演练或压力测试使用;pro
: 生产数据主库,线上环境,开发人员不允许直接在线上环境进行数据库操作,如果需要操作必须找DBA进行操作并进行相应记录,禁止进行压力测试;pro_slave
: 生产数据从库(准实时同步),只读环境,不允许修改数据,不允许修改表结构,供线上问题查找,数据查询等使用;
二、命名规范:
2.1. 基本命名规则:
- 使用有意义的英文词汇,词汇中间以下划线分隔;
- 只能使用英文字母,数字,下划线,并以英文字母开头;
- 库、表、字段全部采用小写,不要使用驼峰式命名;
- 避免使用
MySQL
的保留字,如desc
,index
; - 命名禁止超过32个字符,须见名知意,建议使用名词而不是动词;
- 数据库,数据表一律使用前缀;
- 临时库、表名必须以
tmp
为前缀,并以日期为后缀; - 备份库、表必须以
bak
为前缀,并以日期为后缀;
- 临时库、表名必须以
- 数据库对象命名汇总表:
对象名 | 前缀 | 范例 | 说明 |
---|---|---|---|
表 | t_ | t_tablename | t_表名,表名长度不可超过30个字符 |
普通视图 | v_ | v_viewname | v_视图名 |
主键约束 | pk_ | pk_tablename | 如果表名过长,则用表名的缩写表示,尽量使用通用缩写或去元音的缩写方式 |
唯一性约束 | uk_ | uk_tablename_columnname | 唯一索引;如果表名或字段名过长,则用表名和字段名的缩写表示,尽量使用通用缩写或去元音的缩写方式 |
外键约束 | fk_ | fk_tablename_columnname | tablename:外健所在表的名称; reftablename:参照表的名称; refcolumnname:参照表的字段名称 |
普通B树索引 | idx_ | idx_tablename_columnname | 最常用的索引,普通btree 索引,用idx_表示。如果表名或字段名过长,则用表名和字段名的缩写表示,尽量使用通用缩写或去元音的缩写方式 |
触发器 | trg_ | trg_triggername | rg_触发器名 |
存储过程 | p_ | p_procedurename | p_存储过程 |
函数 | f_ | f_functionname | f_函数名 |
2.2 数据库命名规范:
- 数据库名不能超过30个字符;
- 数据库命名必须为项目英文名称或有意义的简写;
- 数据库创建时必须添加默认字符集和校对规则子句。默认字符集为utf8mb4 ;
- 普通的
utf8
编码长度为3四字节,不支持Emoji表情,utf8mb4
长度为4字节,支持Emoji表情 🐕;
- 普通的
- 命名应使用小写;
三、设计规范
请牢记,设计完成后方可开始编写代码;
3.1 表设计规范:
- 字符统一使用utf8mb4,建议直接沿用数据库配置;
- 禁止表、字段单独定义字符集;
- 所有表使用
innodb
存储引擎; - 表必须有主键,优先使用顺序自增字段,不建议使用UUID字段;
仅限单机环境,分布式环境不适用;
- 表的主键字段只能为1个字段;
- 禁止创建重复索引;
- 特殊情况下,字段相同顺序不同的,可以视为不同索引;
join
字段建议创建索引;- 表的设计尽量满足第二范式( 2NF ),基于性能考虑可以适当增加冗余而不必满足第三范式( 3NF )
- 禁止创建外键,使用业务来实现约束;
- 表的列数不宜过多,建议不多于200;
innodb
表列数的限制为1017列;
- 表的行长度默认限制为
innodb_page_size
的一半,默认为16KB的一半8KB;innodb
最多可以存储8096个字符(utf8mb4
),建议当单列varchar
长度超过4000时使用BLOB
或TEXT
类型存储;- 对于
BLOB
和TEXT
类型的列,实际占用列表长度很小,因为这两种类型数据是单独存放的,表中只保存了链接;
3.2 字段设计规范
varchar
类型的字符长度应小于4000;- 自增主键是非严格连续的,要求连续时必须配置
innodb_autoinc_lock_mode = 0
; - 建议字段尽量为
NOT NULL
提升查询效率; - 非定长字符串建议使用
varchar
; - 对于精确浮点型数据存储,需要使用DECIMAL,严禁使用FLOAT和DOUBLE;
- 对于一些小数位定长的数据,可以讲小数点后移存储,业务中进行转换来提高性能;
- 禁止使用字符串保存日期;
- 建议使用
DateTime
保存日期时间数据; - 索引中包含的数据不允许为
NULL
,必须设置默认值; - 禁止使用复杂数据类型(数组,自定义等等);
- 无特殊需要,不允许使用
BLOB
类型; - 根据数值大小合理设置数据类型:
TEXT
类型必须使用单独的表进行存储以提高效率;- 禁止使用字符串来保存日期;
- 尽量使用
VARCHAR
而不是CHAR
来存储字符串信息,节省空间;
3.3 索引设计规范
- 索引建议使用
BTREE
索引; - 索引类型建议使用
NORMAL
类型,非必要情况下不要使用FULLTEXT
,SPATIAL
,UNIQUE
索引;- 8.0 之后的版本可以使用
FULLTEXT
;
- 8.0 之后的版本可以使用
- 禁止唯一索引和主键重复;
- 单表索引不宜超过5个,如果需要增加的索引过多,建议在业务层面进行重构拆分;
- 增加索引必须进行充分的测试,防止影响已有的SQL运行;
- 索引组合的字段禁止超过3个;
- 常用的连接列建议增加索引;
- 组合索引字段的顺序需要考虑字段数据分布,分布密的放在前面;
- 禁止唯一索引和主键重复;
- 禁止总字符串长度超过200的组合索引;
四、SQL编写规范
- 尽量避免使用
SELECT *
,只列出需要获取的列;- 尤其是存在长字符串列,例如
TEXT
,BLOB
类型列时,必须指明具体的列;
- 尤其是存在长字符串列,例如
- 禁止没有
WHERE
条件的SELECT *
; - 禁止隐式数据转换;
- 禁止对长字符串的列进行
ORDER BY
,DISTINCT
,GROUP BY
,UNION
等会引起排序的操作;- 原因为大量占用和消耗CPU和内存资源;
- 禁止
WHERE
条件使用表达式或是函数;- 错误示例:
WHERE ABS(id) > 3
,id * 10 > 100
;
- 错误示例:
- 查询分区表时,在
WHERE
条件中必须有分区字段; - 多表关联查询必须明确制定各表的连接条件,以避免产生笛卡尔积;
- 8.0 之前的版本避免使用子查询;
- 8.0 版本之前的子查询效率非常的低;
- 多表关联
JOIN
时必须使用表别名(Alias); - 优先使用
UNION ALL
,以提升性能; INSERT
语句中必须有明确的插入字段;- UPDATE, DELETE 语句中不使用
LIMIT
; - 禁止使用
<>
,使用!=
代替;- 两个符号功能一致,但是
<>
在很多情况下需要进行转码否则会触发语法错误;
- 两个符号功能一致,但是