MySQL DBA：2.1 开发规范

目录

1 配置规范

2 建表规范

3 命名规范

4 索引规范

5 应用规范

6 安全规范

---

1 配置规范

1. MySQL数据库默认使用InnoDB存储引擎；
2. 保证字符集统一，数据库、表、字段字符集都是用utf8mb4，应用程序连接、展示等可以设置字符集的地方也统一设置为utf8；
3. 数据库事务隔离级别设置为RC（Read-Commited），MySQL默认是RR（Repeatable-Read）；
4. 单表建议记录数控制在2000万以内；
5. MySQL单实例下，Schema不超过50个，每个Schema下，数据表不超过500个；

2 建表规范

1. 禁止使用外键约束，通过程序保证外键约束；
2. 存储精确浮点数必须使用Decimal替代Float和Double；
3. 整型无需定义显示宽度，如，使用int，而不是int(4);
4. 不建议使用enum类型，枚举类型，尽量使用最短长度的整型或char代替，比如tinyint；
5. 尽可能不适用text、blob类型，如果必须使用，且字段不常用，建议拆分到其他表中；
6. 禁止使用MySQL数据库存储图片、音频、文件等对象；
7. 存储年份时，使用year(4)，不适用year(2);
8. 建议字段定义为not null；
9. 表和字段必须有注释；
10. 必须有主键，主键使用尽量短的递增无符号的整数（能用tinyint不用smallint），建议使用自增主键或利用算法生成的递增的整数（主键的设计极大程度影响表的性能，整型性能好于字符型，同类型下长度短的更好）；
11. 高并发OLTP系统中，不建议使用分区表；
12. 建议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 命名规范

1. 所有对象（库、表、字段、索引、用户等）名，全部采用小写；
2. 库、表、字段、索引、用户名建议不超过12个字符；
3. 命名见名知意，使用英文或英文缩写，禁止英文和汉语拼音混搭（用户名例外）；
4. 用户命名格式：usr_用户类型缩写_项目名缩写/人名拼音缩写/用途；
5. 其他对象命名格式：对象类型前缀_对象含义；

 对象前缀和用户类型表：

对象中文名称	对象英文名称	对象类型前缀
视图	view	vw_
函数	function	fc_
存储过程	procedure	pc_
触发器	trigger	tg_
普通索引	index	idx_
唯一索引	unique index	uk_
主键索引	primary key	pk_
表	table	tb_
模式（数据库）	schema	db_
用户	user	usr_

用户类型	描述	用户类型缩写
管理员	DBA或自动化运维程序使用，可根据情况进行定期修改和禁用	ito
程序用户	业务程序使用，一般不可随意更改，会影响业务	app
开发用户	开发人员临时连接调试，一般仅赋查询权限，可根据情况进行定期修改和禁用	dev
临时用户	其他用途的临时用户，一般仅赋查询权限，且规定用户失效时间，定期回收	tmp

4 索引规范

1. 单个复合索引字段数尽量控制在5个以内；
2. 单表索引个数尽量不要超过5个；
3. 建立复合索引时，优先将选择性高的字段放在前面；
4. 合理利用覆盖索引，提升查询效率；

5 应用规范

1. 避免使用存储过程、触发器、函数等，减小业务逻辑与DB的耦合性，数据库一般是性能的瓶颈且较难水平扩展，逻辑运算尽量放在应用服务器上进行；
2. 能用union all的就不用union，减少排序操作；
3. 使用limit n，少用limit m,n，尤其是大表且m较大时；
4. 尽量减少排序，group by语句如果不需要排序，可增加order by null；
5. 尽量避免在OLTP系统使用大数据量的统计操作，统计实时性要求高的话考虑使用memcache或Redis或使用MQ+flink，非实时统计可单独使用统计表或抽取到数仓中，定时更新，必须做统计记录时使用count(*)，而非count(primary key) 或 count(1)；
6. Java中使用prepared statement，避免SQL注入；
7. SQL语句中in包含的值不应过多；
8. update和delete语句一定要有明确的where条件；
9. where条件中字段值需要与字段类型一致，避免MySQL进行隐式转换；
10. select、insert语句必须显式指名字段名称，且查询的时候只查询需要的字段，禁止使用select * 或insert into table_name values()；
11. insert时使用batch提交（insert into table_name(col1,col2……) values(),(),()……）；

6 安全规范

1. 杜绝SQL注入：JDBC中使用prepared statement；JS中规范用户输入，规避用户输入不符合规范的变量，比如设置输入长度；
2. 异常捕获，程序异常尤其是MySQL元信息相关的禁止直接抛到前台页面，不给攻击者试错的机会；
3. 用户权限管理：最小化权限，为每个用户赋予满足其工作的最小权限，权限要精细到表级，一般不赋予除了DBA用户，一般程序用户仅赋予select、update、insert权限，开发用户和临时用户仅赋予select权限；
4. 定期修改管理用户、开发用户密码，人员离职要及时冻结或删除用户，临时用户要设定过期时间，到期冻结或删除；
5. 数据不删除原则：不delete数据，使用状态标记位，通过update状态标记位达到“删除”效果；
6. 数据更新原则：创建日志表，记录数据变更历史；