数据库设计原则与编写sql规范要求-据于阿里开发手册简化

北京速融科技数据库设计与sql编写规范要求

热水.钟

2019.3.28

1. 前言

规范的数据库设计是提升数据库性能的提前条件，为后期维护减轻了压力，一份好的指导规范文件，作为数据库设计人员的工作纲要，指导其如何设计数据库，同时也指导开发人员如何在编写sql语句时要注意的地方。最终目的是提升系统的性能。

本规范借鉴了阿里巴巴的开发手册，向大公司表示感谢与敬意！

1. 库与表设计原则

1. 创建的数据库应用InfoDB引擎，这样才支持事务,myisam不支持事务,mysqlt5.5之后的版本默认的引擎是infoDB，注意在powerDesign物理模型生成sql脚本时，把它的导出选项中引擎缺省改为InfoDB.

              mysql8.0驱动：jdbc.driver=com.mysql.cj.jdbc.Driver

              数据库：字符集utf8mb4，数据库排序规则utf8mb4_general_ci

1. 库名与应用名称要一致。
2. 表名全部小写（由于windows环境是不区分大小写，而linux和unix区分大小写，所以避免兼容性一率小写），禁用保留字作为名称，单词之间用下划线，表名不要用复数，表名加上“业务名称_表作用”的命名法，例如：credit_apply。powerdesin设计时一定在备注是说明表中文名称,同时检查导入sql到数据库后此备注字段不乱码。
3. 字段可以适当冗余，但不是频繁修改的字段，同时不是varchar超长字段（比较小的varchar可以），更不能不是text字段，所以不能过于保守地遵守数据库第三范式。
4. 单表超过500万行或者单表容量超过2GB时，才推荐分库分表。
5. 表必备字段：id（雪花主键bigint类型）,gmt_create（设定缺省时间）,gmt_modified（设定缺省时间），id主键，后2个是datetime类型，建议对于外键引用id时每一个表的外键字段应用业务名称加上id命名法，例如：credit_id，这样在做表关联时，容易区分。
6. 数据库的设计用工具时，一定要直接用物理模理进行设计，不要用概念模型和逻辑模型，因为有一些细节的设置不支持，例如字段的唯一性约束，备注字段说明转换（概念转物理模型）时会丢失。
7. powerDesign在设计物理模型时，注意区分主键和主键索引，字段唯一性约束和唯一性索引的区别，实践中，设置了字段为主键，就不需要设置此字段为索引了，它会自动创建，字段唯一性约束看情况是否创建（一般情况不创建，只约束数据这列是否重复），但唯一性索引，每一张表一定要创建一个，加快速度，每一张表均有一个唯一性索引（可以是一个业务字段或者几个业务字段的组合，建议只用一个业务字段表示唯一性，少用复合）。
8. 如果研发互联平台相关软件，一定不要有外键的设计，对于互联网平台没有外键，外键相关实现由业务层代码去实现。

 

 

1. 字段设计原则

1. 表必备字段：id,gmt_create,gmt_modified

   其中id为主键bigint长整型 ，采用雪花算法生成主键。gmt_create和gmt_modified均为datetime类型，且有缺省值设置，前者表示主动创建，后者表示被动创建。

1. 字段名全部小写（由于windows环境是不区分大小写，而linux和unix区分大小写，所以避免兼容性一率小写），一率小写英文字符(禁用保留字），单词之间用下划线连接,例如：client_name,尽量不要用拼单，用简单的单词，一看就懂的。powerdesin设计时一定在备注是说明字段中文名称,同时检查导入sql到数据库后此备注字段不乱码
2. 对于字段是布尔型的命名，改为admin_is，把is放后面，避免java生成实体类时把is当成特殊处理，这条没有采纳阿里的做法把is放前面，会造成实体属性和真实字段不一致，人工还要去调mybatis映射，增加工作量。
3. 小数类型为decimal，禁用float和double
4. 如果存储的字符串长度几乎相等，使用char
5. varchar是可变长度，长度不要超过5000，超过的定义字段类型为text，并且要独立一张表，用主键来对应，避免造成索引慢 。

少用text类型(尽量使用varchar代替text字段);

1. 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能)，即能用数字类型代替的就别用字符类型定义。
2. 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
3. 为了减少开发过程的失误，以及为了各大数据库之间的兼容，布尔型一率用int代替，整型统一用int，smallint,long,tinyint尽量别用，减少编程出错的机会。
4. 可以设计外键字段，但不能建立与其他表的外键字段的强制约束关系。

1. 索引设计原则

1. 每个表的都有主索引（主键），尽量不超过5个的索引，外键要有索引，主键索引为pk_字段名，唯一索引为uk_字段名，普通索引idx_字段名。

pk_：primary key

uk_：unique key

idx:  index

1. 每一个表尽量有一个满足业务的唯一性索引，业务必须具有唯一特性（除自增量主键以外）的字段，即使多个字段的组合，也必须建成唯一索引,查询速度明显，即使在应用层做了非常完善的校验控件，没有唯一索引必然会有脏数据，即uk_。
2. 索引应该建立在小字段上，大的文本字段不要建索引，varchar字段上建立索引，必须指定索引长度，没有必要对全字段建立索引，尽量根据实际可能值长度，一般长度为20，即前缀索引。
3. 为经常需要排序，分组和联合操作的字段建立索引。
4. 经常出现在where语句后面的字段建立索引。
5. 复合索引请慎用，复合索引包含字段控制别超过3个时，尽量用单字段索引代替，复合索引一般在where后面的and子句中，复合索引最左边原则才能走索引，对应字段的单列索引一般没要必要再创建。
6. 不用外键(由程序保证约束)硬性强关联。

1. sql语句编写约束

1. 不要使用count(列名）或者count(常量名），应改用count(*),它和数据库和null和非null无关
2. 避免sum(列）为NULL时，可以写成：SELECT IF (ISNULL(SUM(g)),0,SUM(g)) FROM table.
3. 使用ISNULL（）来判断是否为NULL值,因为NULL和任何值比都是NULL
4. 禁上用存储过程，难以调试和扩展，更没有移植性。
5. in 尽量别用，实在要用，要控制n 后面的集合控制在1000个
6. 表查询中，一律不要使用*,就把所需要字段名列出来。
7. 不要用resultClass当返回值，一定要配置映射关系，有利维护。
8. @Transactional事务不要滥用。
9. sql对于关键字必须用大写，表名和字段用''.
10. join尽量不要超过三个表，尽量由业务逻加上去拼数据，不要用left join语句
11. 学会插入一条记录，立马返回此记录的主键值的用法：

<insert id="insertSelective" parameterType="com.zjm.gwork.model.Sr_credit_apply" keyProperty="credit_id" useGeneratedKeys="true">

 

int credit_id = sr_credit_applyMapper.insertSelective(sr_credit_apply); // 插入到授信记录并返回获得这条的主键id
 System.out.println("添加一条授信："+sr_credit_apply.getCredit_id());

 

1. 不得使用外键与级联：即在设计数据库时表中可以有外键字段，但不要建立硬性关联约束，表之间的外键约束不适合分布式，高并发集群要求。

 

 

 

1. ORM映射

1. 在查询表时，一律不要使用*作为查询字段的列表，哪些字段需写明。
2. 不要用resultClass作为返回参数，应配置类与表字段的映射关系，每一个表必须有一个POJO与之对应。
3. 不允许resultClass=”Hashtable”，即不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出，说明：resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。
4. mybatis映射方件*.xml配置参数使用：#{}，#param#, 不要使用${}，此种方式容易出现sql注入。
5. @Transactional事务不要滥用.
6. <isEqual>中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时带

上此条件；<isNotEmpty>表示不为空且不为 null 时执行；<isNotNull>表示不为 null 值时

执行。

1. iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。

说明：其实现方式是在数据库取到statementName对应的SQL语句的所有记录，再通过subList

取 start,size 的子集合。

正例：Map<String, Object> map = new HashMap<>();

map.put("start", start);

map.put("size", size);

 

1. 数据库范式

        第一范式(1NF)：字段值具有原子性,不能再分(所有关系型数据库系统都满足第一范式);  

            例如：姓名字段,其中姓和名是一个整体,如果区分姓和名那么必须设立两个独立字段;  

           

        第二范式(2NF)：一个表必须有主键,即每行数据都能被唯一的区分;  

            备注：必须先满足第一范式;  

           

        第三范式(3NF)：一个表中不能包涵其他相关表中非关键字段的信息,即数据表不能有冗余字段;  

            备注：必须先满足第二范式;  

           

        备注：往往我们在设计表中不能遵守第三范式,因为合理的沉余字段将会给我们减少join的查询;  

              例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;