科技中心-数据库开发规范

作者:王小兵 于 2020年07月09日 发布在分类/ 数据库

数据库开发操作规范

                                

                           

版本:V1.0.0

作者:王小兵

日期:2020年6月15日

版本更新

版本

修改说明

操作人

日期

V1.0.0

初始化

王小兵

2020/6/15
V1.0.1

1 、表中英文字母全部改为大写 , 文档格式优化

2 、临时表视图统一使用 view_,tmp_

3 、文档中个别单词拼写错误 , 字符集 utf8mb4-general-cli 订正为 utf8mb4-general-ci

4 、根据线上数据结构实际情况,将创建人修改人字段统一分别为 create_by,update_by, 并且为非空 (not null)

王小兵

2020/6/19
V1.0.2

1、  数据库操作规范第 28 项由【强制】改为推荐,因有些业务对读实时性要求很高,需要读也在主上操作

2、  数据库操作规范增加第 29 项,具体内容见文档内容,慢 sql 对数据库性能影响很大,需定时优化线上慢 sql

3、  表设计规范第 9 项增加说明

4、  操作规范添加 sql 审核平台规范,具体内容见 30 、【强制】

5、  增加文档说明

王小兵

2020/6/24

文档说明

此规范中【强制】选项是一定要遵守的的,不包含特殊业务需求。

【推荐】和【参考】不是必须要遵守的,但是【推荐】和【参考】对使用会有一定的性能提升或者更好的使用体验

一、数据库开发规范

命名规范

1、【强制】库名、表名须见名之意

如VIEW_表一定是视图,TMP_一定是临时表,不能正常业务表名命名为VIEW_、TMP_形式

2、【强制】库名、表名、字段名禁止超过 32 个字符、禁止使用 MYSQL 保留字(如DESC、RANGE、MATCH、DELAYED等)

3、【推荐】库名与应用名称应尽量一致

4、【强制】临时库、表名必须以 TMP_ 为前缀,并以日期为后缀、备份库、表必须以 BAK_为前缀,并以日期为后缀,视图必须以VIEW_

5、【推荐】表的命名最好遵循“业务名称_表的作用”

6、【强制】库名、表名中不能包含特殊字符,库名、表名必须是小写

7、【推荐】普通索引名称IDX_为前缀 、唯一索引为UK_

表设计规范

1、【强制】表中必须有主键ID,类型为INT 或者BIGINT

2、【强制】表中所有字段必须有 注释  ##COMMENT、表也要有注释

3、【强制】表中必须有CREATE_TIME、UPDATE_TIME字段,类型为DATETIME,并且默认值为CURRENT_TIMESTAMP的值

随着数据的更改而更改(通过程序生成、或者数据库自身实现ON UPDATE)、必须有CREATE_BY,UPDATE_BY字段为非空(NOT NULL)

4、【强制】所有表统一使用INNODB非有特殊业务需求,需DBA审核

5、【强制】所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL的特殊业务需求,需DBA

6、【强制】禁止在数据库中存储图片、视频、文本文件类型。

7、【推荐】使用 INT UNSIGNED 存储 IPV4(BIGINT8个字节,INT为4个字节 SMALLINT节 TINYINT一个字节

8、【强制】禁止在数据库中存储明文密码(推荐使用HASH,一般如果知道可以反推出密码内容,而HASH不会)

9、【强制】表达是与否概念的字段,必须使用IS_命名,数据类型是TINYINT UNSIGNED(1表示是,0表示否)

例:表大逻辑删除的字段名IS_DELETED ,1表示删除,0表示未删除。

说明:公司现有逻辑删除字段为DELETE_FLAG,可以统一根据现有已存在业务进行命名。

10、【强制】表字符串类型的字段的字符集统一使用UTF8MB4、字符集排序编码统一使用UTF8MB4-GENERAL-CI

11、【推荐】避免使用自定义函数、存储过程、触发器、外键,如需使用,需DBA审核通过

12、【推荐】TINYINT/SMALL INT/INT/BIGINT字段无须指定长度,如:使用INT,而不是INT(4),尽量使用更小的类型

13、【推荐】在VARCHAR类型单个字段上创建索引需保证长度不超过255字符,可建合适的前缀索引(区分度可以由

COUNT(DISTINCT LEFT(COL,N))计算出来)

VARCHAR类型最大长度65536字节,长度超过5000的VARCHAR类型,建议使用BLOB/TEXT类型

14、【推荐】单张表中索引数量不超过 5 个、单个索引中的字段数不超过 5 个

15、【推荐】字段允许适当冗余、以提高查询性能,但必须考虑数据一致。冗余字段应遵循:

         1)不是频繁修改的字段 2)不是唯一索引的字段  3)不是VARCHAR超长的字段更不能是TEXT字段

16、【参考】合适的字符存储长度、不但节约数据库表空间、节约索引存储,更重要的是提升检索速度

例:

         TINYINT UNSIGNED   存储大小:1字节 表示范围:无符号值0-255

        

         SMALLINT UNSIGNED  存储大小:2 字节 表示范围:无符号值:0-65535

       

          INT UNSIGNED       存储大小:4字节   表示范围:0-约43亿

     

         BIGINT   UNSIGNED 存储大小:8字节   表示范围:0到约10的19次方

字段类型说明

【参考】INT:INT(N):无论N为多大,都是占用4个字节,N代表显示宽度,如果定义了ZEROFILL,INT(4)代表0001;

用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储。

【参考】DECIMAL:一般用来存金钱,DECIMAL(M,D)中D是小数部分得位数,若插入得值未指定小数部分或者小数部分不足D位

,则自动补到D位小数,若插入得值小数部分超过了D,则会发生截断,截取前D位小数,并四舍五入。

可以使用INT来存储金钱,让INT单位为分。

【参考】时间类型:DATETIME,TIMESTAMP,DATETIME在MYSQL5.6之前是占用8个字节,5.6版本之后占5个字节,

DATETIME比TIMESTAMP范围大,(DATETIME范围:1000-01-01~9999-12-3,TIMESTAMP

范围:1970-01-01 00:00:00~2038-01-19:03-14:07UTC)物理存储上仅比TIMESTAMP多占一个字节空间,

整体性能消耗并不是太大,两者从5.6开始都开始都支持自动更新为当前时间。生产环境可以使用DATETIME时间类型。

也可以使用INT来存储时间,可以通过UNIX_TIMESTAMP和FROM_UNIXTIME函数来转换。

【参考】BIT:BIT 数据类型用于存储位值。 BIT(M)类型可以存储M位的值。默认为1, M的范围是1到64。

通常BIT定义的变量作为一个标志位用 。BIT的值只能是整数,BIT和TINYINT的区别:TINYINT 一个非常小的整数。

有符号范围是-128到127.无符号范围是0到255。

【参考】CHAR(N):0~255字节大小定长字符串

【参考】VARCHAR(N):边长长度,长度范围(0~65535其中N代表字符个数得概念)例如:在UTF8字符集下:

存储空间为100*3+1个字节,

在GBK下存储空间大家为:100*2+1,当VARCHAR(N)N<255时,用一个字节来记录长度,

当N>255时用两个字节数来记录长度,还有1位用来记录是否为NULL;

【参考】TEXT和BLOB这种存大量文字和图片得大数据类型不建议和业务表存放在一起。

【参考】枚举类型可以使用ENUM,ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR)

二、数据库操作规范

1、【强制】线上禁止跨库查询、更新数据

2、【强制】禁止单条 SQL 语句同时更新多个表

3、【强制】禁止直接 SELECT * 读取全部字段,尤其是表中存在 TEXT/BLOB 大列的时候。可能本来不需要读取这些列,但因为偷懒写成 SELECT * 导致内存BUFFER POOL被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。

4、【强制】单表数据量不超过7千万(需要根据业务进行归档或者拆分,分库分表等)

5、【推荐】避免直接使用DELETE语句删除数据,推荐使用标记删除,必须执行物理删除时,需先对数据打标,逻辑删除

6、【强制】避免TRUNCATE或者DROP 表操作,DELETE操作必须有WHERE 条件

7、【强制】禁止删除字段,业务上废弃使用即可,并添加注释

8、【推荐】避免修改字段名称,字段类型,有此需求需要提前沟通所有上下游(开发组长)

9、【强制】超过三张表禁止JOIN,需要JOIN的字段,数据类型保持绝对一致,多表关联查询时,保

证被关联的字段需要有索引,注意:即使是两张表JOIN也需要注意索引、SQL性能

10、【参考】如果有ORDER BY的场景,注意利用索引的有序性。ORDER BY 最后的字段是组合索引的一部分,并且放在

索引组合顺序的最后,避免出现FILE_SORT,影响查询性能

11、【参考】利用覆盖索引来进行查询操作,避免回表

例子: 能够建立索引的种类分为:主键索引、唯一索引、普通索引,而覆盖索引只是一种查询的效果,用EXPLAIN 查

看执行计划,EXTRA列的值为:USING INDEX说明使用了覆盖索引

11、【参考】利用延迟关联或者子查询优化超多分页成

例子:快速定位需要获取的ID然后再关联:SELECT    A.* FROM TAB1 A ,(SELECT ID FROM TAB1 WHERE 条件 LIMIT  100000,

20)   B  WHERE A.ID=B.ID

【推荐】建组合索引的时候、区分度最高的再左边

说明:存在非等号和等号混合判断条件时,再建索引时,请把等号条件的列前置,如:WHERE C>TIJIAO1  AND  D=?

那么即使C的区分度更高,也必须把D放在索引的最前列,即建立组合索引IDX_D_C

12、【推荐】防止因字段类型不同造成的隐式转换,导致索引失效

13、【强制】不要使用COUNT(列名)来代替COUNT(*)  ,COUNT(*) 是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL

和非NULL无关说明:COUNT(*) 回统计值为NULL的行,而COUNT(列名)不会统计此列为NULL值得行

14、【强制】COUNT(DISTINCT COL)计算该列除NULL之外不重复得行数,注意COUNT(DISTINCT COL1,COL2)如果其中一列为NULL那么

即使另一列有不同得值,也返回为0

15、【强制】不得使用外键和级联,一切外键概念必须再应用层解决

16、【强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性

17、【强制】数据订正(特别是删除或者更改记录操作),要先SELECT,避免出现误删除,确认无误才能执行更新语句)

18、【强制】代码中写分页查询逻辑时,如哦COUNT为0应直接返回,避免执行后面得分页语句

19、【推荐】IN操作能避免则避免,实在避免不了,需要仔细评估IN后边得集合元素数量,控制再1000个之内

20、【推荐】批量导入、导出数据必须提前通知 DBA 协助观察

21、【推荐】避免在线上主库执行统计、计算(SUM)多表关联查询

22、【强制】禁止有 SUPER 、ALL PRIVILEGES权限的应用程序账号存在

23、【强制】推广活动或上线新功能必须提前通知 DBA 进行流量评估

24、【推荐】不在 MYSQL 数据库中存放业务逻辑

25、【推荐】重大项目的数据库方案选型和设计必须提前通知 DBA 参与

26、【推荐】不在业务高峰期大批量更新、查询数据库

27、【强制】提交线上建表改表需求,必须详细注明所有相关 SQL 语句

28、【推荐】线上业务对实时性要求不高的进行读写分离1主1从,分担主库压力,如果有特殊要求(事务一致性、数据实时性等)需要相关业务人负责人审核签字,提交DBA 备案

29、【强制】线上慢SQL超过1s的必须优化,具体SQL可以参考PMM监控的慢日志选项、0.1~1s的需优化

30、【强制】开发人员在SQL审核平台提交SQL后,需跟踪提交SQL语句的执行状态,特殊需求(如定时执行)需提前告知DBA及运维人员。无效的SQL必须终止。

三、线上数据访问操作规范

1、专用户专用(程序使用得用户只能程序中使用、开发人员使用得只读账只能开发人员使用)

2、本业务组开发人员只能查看本业务得数据、跨业务查询数据,需申请授权查询

3、测试用户不能直接读取线上数据

4、开发人员不能有线上删除、UPDATE账号

5、开发人员不得接受业务人员提出的导出生产环境数据的要求,此工作应该由数据中心或者运维人员完成。

6、本业务的生产数据导出,少于1000条的由DBA审核@王小兵 ,超过1000条需要@车江毅 @王于武 审核。

6、跨业务的生产数据导出,必须由@车江毅 @王于武  审核

7、生产数据库账号全部回收,另开放本业务系统的只读账号给开发负责人,只读账号权限仅有SELECT,禁止数据库导出权限,此项决议需要@车江毅 @王于武  审核。

8、生产数据库更新/删除数据操作,必须由@车江毅 @王于武  审核,不允许做跨业务数据库删除。

9、生产数据定时同步到测试环境,每个表同步的数据不得超过5W条,且测试环境每张表保存的生产环境的数据也不得超过5W条。

10、开发人员不得接受业务人员提出的导出生产环境数据的要求,此工作应该由数据中心或者运维人员完成。


分享到朋友圈 分享到微信
发表评论
验证码