开发人员数据库Mysql规范

   

目录                                    

概述

名词约定

2.【强制】总体命名规范

3.1、【弱强制】命名规范

3.2、【弱强制】字符集以及字符集排序规则

4.3、表设计规范

4.4 列设计规范

5.0、命名规范

6.1、DML 规范

---

                                         

1. 概述

   1.名词约定

#	名称	描述
1	DDL	Data Definition Language
2	DML	Data Manipulation Language
3	DCL	Data Control Language

2.【强制】总体命名规范

1. a～z， 0～9，下划线"_"；

2. 名称不能以数字和下划线开始；

3. 不要使用MySQL保留的字段来命名（例如： rank,type, ）；

详细参考官方文档http://dev.mysql.com/doc/refman/5.7/en/keywords.html；

• 命名中的单词使用原则：尽量避免使用缩写，选择尽量简单的单词标准。不可使用复数单词；

• 对象名称必须控制在32个字符以内；

• 缩写需遵循行业缩写规范，或者采用行内默认缩写，不得自行创造。

• Schema规范

3.1、【弱强制】命名规范

业务系统名称_子系统名，避免不同业务子系统名一致，同一模块使用的表名尽量使用统一前缀。

#	数据库名称	简称
1	EBMS-Exchange Business Management	ebms_mgt
2	BBMS-Broker Business Management	bbms_mgt

 

3.2、【弱强制】字符集以及字符集排序规则

除特殊业务特殊情况外，数据库字符集统一为UTF8MB4 ,排序集为utf8mb4_bin;

1. Table/View 规范

4.1、【强制】命名规范

命名一般主要分为3段，第一段 只允许以t_/v_ 开头区分表与视图，第二段为子系统名称，第三段为表或者视图详细功能名称（缩写）。禁止

4.2、【强制】临时表，备份表

所有以tmp_/bak_ 视为临时表以及备份表，一般来说临时表以及备份表不出现于PRO环境。且绝大多数不对业务有所影响。 临时/备份表 在命名 为 第一段为 tmp_/bak_ ,第二段为表功能名，第三段为表存活截至时期。例如：tmp_clearingtask_20210401,用于临时存放清理任务，并截止到2021-04-01 23：59：59.

4.3、表设计规范

343.1、【弱强制】创建表时必须显式指定字符集为utf8mb4。

4.3.2、【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB。当需要使用除InnoDB以外的存储引擎时，必须通过DBA审核才能使用。

4.3.3、【强制】建表必须有comment，字段必须有comment,自增主键id

4.3.4、【强制】系统核心表必备三个字段sys_insert_datetime, sys_upd_datetime，is_delete 便于数据审计和问题排查。

4.3.5、【建议】建议对核心表 添加 int_ext(1-4) ,str_ext(0-4) 的拓展列保留

4.3.6、【强制】表中的所有字段必须not null,所有新增字段必须有默认（DEFAULT）值 

4.3.7、【强制】建议对表里的blob、text，varchar(>500)等大字段，垂直拆分到拓展表里，用主键来对应，仅在需要读这些对象的时候才去select

4.3.8、【建议】反范式化设计，字段适当冗余。冗余字段应遵循：a) 表小于100W行的情况下，字段为非频繁变更字段，否则为静态字段 ; b) 非超长（blob、text，varchar(>500)）字段;

4.4 列设计规范

4.4.1、【建议】表中的自增列（auto_increment属性），推荐使用bigint类型

4.4.2、【建议】形如状态，类型等字段，无特殊设计建议使用smallint 。

4.4.3、【建议】业务中IP地址字段推荐使用int类型，不推荐用char(15)，节省空间。SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044)。

4.4.4、【强制】禁止使用enum，set。因为它们浪费空间，且枚举值写死了，变更不方便

4.4.5、【强制】表中禁止存放照片，文件等直接的2进制内容。

4.4.6、【建议】对文件信息存储 ，采用地址+ 名称的2列存储形式

4.4.7、【建议】对于char和varchar，text 的选取，通常来说时选择varchar。text 在后台内容控制不足时，容易产生巨大风险。

4.4.8、【强制】varchar 类型字段只支持长度增加，不支持缩减.

4.4.9、【强制】表中字段类型和长度按需使用，慎用blob、text等大容量字段，不使用json 类型。（1，需要开发人员sql 水平提高。2，ORM 不兼容的情况）

4.4.10、【强制】处理资金字段时，至少使用decimal 并保留小数点后18位以上。

4.4.11、【建议】对于时间类型的存储上使用date,datetime,varchar。尽量避免使用timestamp。

1. 索引设计规范

5.0、命名规范

idx_x(n)_u/[i(m)] 第一部分为定值idx； 第二部分为自定义索引名称（通常为字段名），第三部分为后缀共2位，第1位代表索引类型，取值可以是：u-唯一索引,i-非唯一索引；建议使用第2位代表索引编号，m取1～9；

5.1、【强制】必须有主键索引以及 idx_sys_insert_time(sys_insert_datetime)，且主键不允许更新。

5.2、【强制】不允许使用全文索引。

5.3、【强制】表索引不超过5个  

5.4、【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面，注意最左匹配原则

1. 业务SQL 规范

6.1、DML 规范

6.1.1【强制】insert语句必须写列名。但不要对自增长ID、系统时间字段进行赋值。

6.1.2、【建议】insert into…values(XX),(XX),(XX).. 这里XX的值不要超过5000个。

6.1.3、【弱强制】insert into select语句不建议使用，可能会有数据错乱问题，也有一定的情况下锁全表。

6.1.4、【强制】禁止不带where条件以及条件内无索引字段的删除/更新sql，以及limit 限制。如"update t set t.status = 0;"，尽量使用主键或者时唯一索引进行更新。

6.1.5、【强制】SELECT语句必须指定具体字段名称，禁止写成"*"

6.1.6、【强建议】不要使用count(列名) 来替代count(常量)或者 count(*)，count(列名) 存在列为数据存在null 时会失真

6.1.7、【强制】where条件里等号左右字段类型必须一致，否则发生隐式转换，无法利用索引。

6.1.8、【强制】索引列不要使用函数或表达式

6.1.9、【强制】WHERE 子句中禁止只使用全模糊的LIKE条件以及左模糊的Like进行查找，必须有其他等值或范围查询条件，否则无法利用索引。

6.1.10、【强制】避免全表扫描；避免过滤集过大 ，保证过滤集在10w。

6.1.11、【弱强制】尽量使用or语句，可将or语句优化为union语句，然后在各个where条件上建立索引

6.1.12、【强建议】避免子查询与join ，尽量使用简单查询

6.1.13、【建议】多表join不要超过3个表。尽量选取结果集较小的表作为驱动表，来join其他小表

6.1.14、【建议】减少使用order by，能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU、

6.1.15、【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。例如：where a=a1 and b=b1 order by c; 索引：a_b_c。

6.1.16、【强制】包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内

6.2 事务规范

6.2.1、【强建议】使用Repeatable-read 作为默认隔离级别。必要情况下可以考虑使用Read-commited.

6.2.2、【建议】事务不宜过长。建议在5个sql 左右

6.2.3、【强建议】 对同步延迟较为敏感的查询语句，建议访问主库。

1. 视图规范

7.0、【弱建议】核心业务使用视图功能。

7.1、【强制】禁止使用视图嵌套视图。

1. 数据生命周期

8.1、【强制】业务上线前需明确数据管理机制（根据业务需求以及监管合规要求）