目录
1 配置规范
- MySQL数据库默认使用InnoDB存储引擎;
- 保证字符集统一,数据库、表、字段字符集都是用utf8mb4,应用程序连接、展示等可以设置字符集的地方也统一设置为utf8;
- 数据库事务隔离级别设置为RC(Read-Commited),MySQL默认是RR(Repeatable-Read);
- 单表建议记录数控制在2000万以内;
- MySQL单实例下,Schema不超过50个,每个Schema下,数据表不超过500个;
2 建表规范
- 禁止使用外键约束,通过程序保证外键约束;
- 存储精确浮点数必须使用Decimal替代Float和Double;
- 整型无需定义显示宽度,如,使用int,而不是int(4);
- 不建议使用enum类型,枚举类型,尽量使用最短长度的整型或char代替,比如tinyint;
- 尽可能不适用text、blob类型,如果必须使用,且字段不常用,建议拆分到其他表中;
- 禁止使用MySQL数据库存储图片、音频、文件等对象;
- 存储年份时,使用year(4),不适用year(2);
- 建议字段定义为not null;
- 表和字段必须有注释;
- 必须有主键,主键使用尽量短的递增无符号的整数(能用tinyint不用smallint),建议使用自增主键或利用算法生成的递增的整数(主键的设计极大程度影响表的性能,整型性能好于字符型,同类型下长度短的更好);
- 高并发OLTP系统中,不建议使用分区表;
- 建议DBA提供SQL审核工具,建表规范性需要通过审核后方可上线;
类型名称 | 有符号数(signed) | 无符号数(unsigned) | 存储空间 |
---|---|---|---|
tinyint | -129~127 | 0~255 | 1字节 |
smallint | -32768~32767 | 0~65535 | 2字节 |
mediumint | -8388608~8388607 | 0~16777215 | 3字节 |
int(integer) | 2147483648~2147483647 | 0~4294967295 | 4字节 |
bigint | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 | 8字节 |
3 命名规范
- 所有对象(库、表、字段、索引、用户等)名,全部采用小写;
- 库、表、字段、索引、用户名建议不超过12个字符;
- 命名见名知意,使用英文或英文缩写,禁止英文和汉语拼音混搭(用户名例外);
- 用户命名格式:usr_用户类型缩写_项目名缩写/人名拼音缩写/用途;
- 其他对象命名格式:对象类型前缀_对象含义;
对象前缀和用户类型表:
对象中文名称 | 对象英文名称 | 对象类型前缀 |
---|---|---|
视图 | view | vw_ |
函数 | function | fc_ |
存储过程 | procedure | pc_ |
触发器 | trigger | tg_ |
普通索引 | index | idx_ |
唯一索引 | unique index | uq_ |
主键索引 | primary key | pk_ |
表 | table | tb_ |
模式(数据库) | schema | db_ |
用户 | user | usr_ |
用户类型 | 描述 | 用户类型缩写 |
---|---|---|
管理员 | DBA或自动化运维程序使用,可根据情况进行定期修改和禁用 | ito |
程序用户 | 业务程序使用,一般不可随意更改,会影响业务 | app |
开发用户 | 开发人员临时连接调试,一般仅赋查询权限,可根据情况进行定期修改和禁用 | dev |
临时用户 | 其他用途的临时用户,一般仅赋查询权限,且规定用户失效时间,定期回收 | tmp |
4 索引规范
- 单个复合索引字段数尽量控制在5个以内;
- 单表索引个数尽量不要超过5个;
- 建立复合索引时,优先将选择性高的字段放在前面;
- 合理利用覆盖索引,提升查询效率;
5 应用规范
- 避免使用存储过程、触发器、函数等,减小业务逻辑与DB的耦合性,数据库一般是性能的瓶颈且较难水平扩展,逻辑运算尽量放在应用服务器上进行;
- 能用union all的就不用union,减少排序操作;
- 使用limit n,少用limit m,n,尤其是大表且m较大时;
- 尽量减少排序,group by语句如果不需要排序,可增加order by null;
- 尽量避免在OLTP系统使用大数据量的统计操作,统计实时性要求高的话考虑使用memcache或Redis或使用MQ+flink,非实时统计可单独使用统计表或抽取到数仓中,定时更新,必须做统计记录时使用count(*),而非count(primary key) 或 count(1);
- Java中使用prepared statement,避免SQL注入;
- SQL语句中in包含的值不应过多;
- update和delete语句一定要有明确的where条件;
- where条件中字段值需要与字段类型一致,避免MySQL进行隐式转换;
- select、insert语句必须显式指名字段名称,且查询的时候只查询需要的字段,禁止使用select * 或insert into table_name values();
- insert时使用batch提交(insert into table_name(col1,col2……) values(),(),()……);
6 安全规范
- 杜绝SQL注入:JDBC中使用prepared statement;JS中规范用户输入,规避用户输入不符合规范的变量,比如设置输入长度;
- 异常捕获,程序异常尤其是MySQL元信息相关的禁止直接抛到前台页面,不给攻击者试错的机会;
- 用户权限管理:最小化权限,为每个用户赋予满足其工作的最小权限,权限要精细到表级,一般不赋予除了DBA用户,一般程序用户仅赋予select、update、insert权限,开发用户和临时用户仅赋予select权限;
- 定期修改管理用户、开发用户密码,人员离职要及时冻结或删除用户,临时用户要设定过期时间,到期冻结或删除;
- 数据不删除原则:不delete数据,使用状态标记位,通过update状态标记位达到“删除”效果;
- 数据更新原则:创建日志表,记录数据变更历史;