数据库规范开发使用规范-资深人工智能从业者

1 概述

1.1 目的

鉴于以往各方在数据库设计和使用方面的经验总结，本规范的以下内容是为了保证数据库设计及使用的质量，规范数据库设计及使用习惯，从而提高程序开发效率，提升研发质量。长期坚持并严格遵守规范，从长远来看，于公于私都是双赢的事情。

1.2 适用范围

程序开发人员
数据库开发人员
其他需要使用数据库的人员

1.3 现有数据库的问题

目前数据库中存在的问题：

分库太多，没有明确的分库标准，随意性太大
重复设计相同业务性质的表
表名及字段名没有遵循统一规范的命名规范
核心业务的操作日志记录不全
枚举字段没有码表
大表需要分表
字段数据类型很多没有遵循最小原则

1.4 概念说明

范式：关系数据库中，设计表的时候，字段间关系要满足的不同程度的要求。各范式满足：5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF。目前数据库设计满足3NF即可。
反范式：指的是通过增加冗余或重复的数据来提高数据库的读性能

l 强制: 必须遵守。

l 推荐: 最佳实践，建议遵守。

l 参考: 可选遵守。

2 数据库设计规范

2.1 总体目标

数据库设计总体目标：

规范性：数据库表设计总体遵循3NF，减少不必要的冗余；
高效性：兼顾范式与反范式，平衡读写矛盾。
紧凑性：表结构宽度适中，数据冷热分离，数据类型简单化、最小化，提高空间和资源利用率
易用性：结构严谨，逻辑清晰，使用方便，容易理解。
安全性：合理分配权限，严格控制数据库连接，安全合规使用数据库。

2.2 设计规范

2.2.1 表设计规范

【强制】如无必要，建表时一律采用Innodb存储引擎
【强制】表名不使用复数名词。
【强制】每个innodb表必须有主键，主键字段建议使用BIGINT类型，自增，步长为1。
【强制】表中的行记录超过1000万，要采用分表策略，控制单表容量在百万级别。
【推荐】数据一旦落库，禁止物理删除，通过控制IS_DELETE字段进行逻辑删除
【强制】每个表必须具备以下字段：

`CREATE_TIME` datetime NOT NULL COMMENT '创建时间'
`CREATE_ACCT_ID` bigint(20) NOT NULL COMMENT '创建人账户ID'
`UPDATE_TIME` datetime NOT NULL COMMENT '更新时间'
`UPDATE_ACCT_ID` bigint(20) NOT NULL COMMENT '更新人账户ID'

【强制】一个表中的字段不要太多，不要超过80个。如果表的宽度过大应考虑冷热数据分离，减小表的宽度。
【推荐】谨慎使用分区表。
【推荐】平衡范式与反范式

在平衡范式与反范式的过程中，应当考虑数据在业务场景中的使用方式，对于访问频次高而修改频次很低的数据，设计过程中可以考虑反范式处理，但禁止冗余varchar 超长字段，更不能是 text 字段。对于修改频次高而访问频次很低的数据，设计过程中应当遵循3NF。数据库表的整体设计还是以3NF为基础，然后再根据整个应用系统的需要反范式化数据。

2.2.2 字段设计规范

【强制】使用能正确存储和表示数据的最小类型。如果不确定需要什么数据类型，则选择不会超出范围的最小类型。
【强制】要使用更简单的数据类型。如，相比字符串整数在比较的时候代价更小。
【强制】如无特殊需求，字段要在SQL建表脚本中要明确定义为 NOT NULL。
【强制】所有存储字符的字段使用varchar(n)类型，且使用utf8mb4_bin让存储内容大小写敏感。
【强制】自增主键除了处理主从关系外不要在业务场景中使用，以防sql注入，应该增加唯一键字段来满足需求。
【强制】表间建立关系的字段，比如主从表的主外键，字段数据类型及长度要完全一致且名称一样。
【强制】禁止使用set，enum， boolean类型，应用tinyint类型来替代。
【强制】状态字段也用tinyint类型来表示。
【强制】数据库中所有布尔性质的字段数值0表示为假，数值1表示为真。
【强制】统一使用datetime类型存储时间，禁止使用字符串存储日期型的数据。
【强制】财务的相关金额使用decimal(m,n)类型。
【强制】禁止使用二进制类型（tinyblob，blob，mediumblob，longblob）保存大文本、图片和文件，应使用其他方式存储，如文件系统，数据库只保存其地址信息。
【强制】尽量不要使用TEXT数据类型，Mysql的varchar类型支持65535字节，满足大多数场景。如必须使用，建议把TEXT列分离到单独的扩展表中。
【强制】禁止使用float、double类型。
【推荐】凡是可能被索引的字段，必须定义为NOT NULL

2.2.3 其他设计规范

Mysql中键包括primary key, unique key, foreign key 等，是数据库的物理结构，有两方面的用途：一是约束，侧重于数据库的完整性约束，二是索引，用于优化查询。换言之，每个键除了规范约束的功能，同时也有索引的功能。

键和索引设计要遵循如下原则：

【强制】每个Innodb 表必须有一个主键。
【强制】禁止在数据库中设计外键约束，外键约束在程序层面实现。
【强制】禁止在区分度很差的字段上建索引。
【参考】尽可能避免使用复合键。
【参考】一个表中组合主键的字段个数尽可能少。
【参考】对于索引选择性高的列使用B-Tree索引。
【参考】HASH索引只适用于相等比较。
【推荐】避免在WHERE条件中将字段用于表达式或函数中，这样不会在该列上使用索引。
【推荐】建组合索引的时候，区分度最高的在最左边。
【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
【推荐】不要索引大型字段（有很多字符的字段）。
【推荐】在常用的小型表中不必要建立索引。
【推荐】每张表的索引数量不超过5个。

2.3 命名规范

2.3.1 总则

【强制】所有命名采用26个英文大小写字母和0－9这十个自然数，以下划线_分割。不能出现其他字符（注释除外）；
【强制】禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，所以字段名称需要慎重考虑。
【强制】名称长度不超过64个字符，尽量控制在30个字符以内；
【强制】实际名称尽量描述实体的内容，由英文单词、单词组合或单词缩写组成，不要以数字和_开头；
【强制】名称不能与SQL关键字相同；
【强制】对象名称尽量短；
【强制】所有命名要以见名知意为准则；

2.3.2 数据库命名

数据库名称以小写英文字母和数字命名，以下划线_分割，名称要涵盖主要的业务职能，长度不要超过10个字符。

2.3.3 表命名

<项目名称><模块名称><表名称>

表名称要以小写英文字母和数字命名，以下划线_分割，能简单描述该表的基本职能，表名不使用复数名词。

项目名称要简短，最好用字母缩写。

模块名称要简要，可以用缩写，可以用核心词拼接。

2.3.4 字段命名

字段名称以大写英文字母和数字命名，以下划线_分割，字段名称及其注释要指明业务含义，做到用词准确，表达无歧义。

2.3.5 其他命名

唯一键

UK_<构成主键的字段名称>

索引

IDX_<构成索引的字段名称>

3 SQL开发规范

3.1 脚本编写规范：

【强制】禁止超过三个表的关联join ，禁止跨库做关联join。
【强制】在表查询中，禁止使用 * 作为查询的字段列表，必须明确指定所需字段。
【强制】查询脚本中，必须为每个表提供别名，每个字段前必须显式指定表的别名前缀，如

SELECT a.COL1,a.COL2,b. COL1,b.COL2 FROM tab1 a join tab2 b on a.COL1 = b.COL1;

【强制】INSERT插入语句必须有字段列表且包含该表所有NOT NULL字段，禁止使用default。如：

INSERT INTO `goods`.`goods_info`(`GOODS_ID`, `ITEM_ID`, `SOURCE_TYPE`, `PUBLISH_POSITION`, `STORE_ID`, `SELLER_GOODS_ID`, `SELLER_SNAPSHOT_GOODS_ID`, `CHANNEL_ID`, `AVAILABLE_STOCK`, `DAILY_CAPACITY`, `MIN_PURCHASE_NUM`, `MATERIAL_ID`, `MATERIAL_CODE`, `MATERIAL_SNAPSHOT_ID`, `SALE_CNT`, `UNIT_PRICE`, `SUPPLY_PRICE`, `STATUS`, `SALE_STATUS`, `STOP_SALE_REASON`, `MOD_TIME`, `MOD_ACCT_ID`, `CREATE_TIME`, `CREATE_ACCT_ID`, `APPROVE_TIME`, `APPROVE_ACCT_ID`, `APPROVE_DESC`, `WARNING_NUM`, `UNQUALIFIED_STOCK`, `PLATFORM_UNQUALIFIED_STOCK`, `PLATFORM_QUALIFIED_STOCK`, `WMS_STOCK`) VALUES (100000000, 100000000, 1, 0, NULL, 100000000, 100000000, 1, 963329.000, NULL, NULL, 17, '1', 17, 420524.000, NULL, 89.04, 1, 1, NULL, '2021-02-02 09:22:34', 100011805, '2018-06-22 14:36:35', 302, NULL, NULL, NULL, 1.000, 0.000, 0.000, 830955.000, 0.000);

【推荐】Sql脚本中每个表名之前要指定所在库，如：库名.表名。

3.2 WHERE条件规范：

【强制】禁止sql脚本中出现数据类型的隐式转换。
【强制】对同一列对象进行or 判断时，使用in 替代or；不同字段的值进行or判断是，用UNION ALL替代or。
【强制】禁止在WHERE 从句中对列进行函数转换和计算。
【推荐】避免WHERE条件中使用左模糊或者全模糊。
【推荐】控制全表扫描范围，控制在30%左右。

3.3 脚本使用行为规范：

【强制】程序里不允许出现DELETE * FROM 表名;TRUNCATE TABLE 表名;DROP TABLE 表名;DROP DATABASE库名;等高危操作。
【强制】在确定无重复值时使用UNION ALL而非UNION，UNION会去重，代价高。
【强制】超过100万行数据的批量操作(update，delete，insert)，分多次进行。
【推荐】Insert操作优先选择批量插入，但VALUES的个数应在500个以内。
【强制】禁止在数据库中存储明文密码。
【强制】禁止使用order by rand() 进行随机排序。
【强制】避免使用子查询，可以把子查询优化为join关联操作。
【推荐】连表join查询的时候，将过滤后结果集较小的表作为驱动表。
【推荐】表之间的关联条件放到JOIN… ON里面而不是WHERE子句里面。
【推荐】禁止单条SQL语句同时更新多个表。
【推荐】分组，排序，集合是比较复杂的运算，应避免使用。

减少或避免使用临时表。当ORDER BY子句和GROUP BY子句同时出现且字段列表不同则会自动创建临时表；当ORDER BY子句或GROUP BY子句的字段列表中的字段超出驱动表，则自动创建临时表。

减少或避免排序，如无法避免，尽量在索引列中完成排序。

避免HAVING子句，尽量将条件放到WHERE子句中。

【推荐】排序字段尽量选择驱动表的字段，否则排序无法利用索引。
【参考】不要使用count(列名)或count(常量)来替代count()，count()是SQL 92定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明： count( * ) 会统计值为 NULL 的行，而count(列名)不会统计此列为 NULL 值的行。

【参考】count(distinct col) 计算该列除NULL之外的不重复行数，注意count(distinct col 1, col 2 ) 如果其中一列全为 NULL ，那么即使另一列有不同的值，也返回为 0。
【参考】当某一列的值全是NULL时，count(col) 的返回结果为0，sum(col)的返回结果为NULL。
【强制】禁止使用存储过程，存储过程难以调试和扩展，移植性差。
【强制】禁止使用触发器。
【强制】数据订正（特别是删除、修改记录操作）时，要先 select ，避免出现误删除，确认无误才能执行更新语句。
【推荐】利用延迟关联或者子查询优化超多分页场景。MySQL 并不是跳过OFFSE行，而是取 OFFSET + N 行，然后返回放弃前OFFSE行，返回N行，那当OFFSET特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

正例：先快速定位需要获取的 id 段，然后再关联：

SELECT a.* FROM 表 1 a, (SELECT id FROM表1 WHERE条件 LIMIT OFFSET, N) b where a.id=b.id;

【推荐】 SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

说明：1 ）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。2 ）ref 指的是使用普通的索引（normal index）。3 ）range 对索引进行范围检索。

反例： explain 表的结果， type = index ，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描相差不多。

4 数据库帐号使用规范

【强制】对于连接数据库账号，遵循权限最小原则。
【强制】禁止给程序使用的账号授予super 权限。
【强制】禁止除生产服务器之外的环境连接生产数据库。
【强制】禁止私自导出、传播生产数据。