1.1 建表规约
1.表名、字段名必须使用小写字母、下划线;表名不使用复数名词,表名命名 t_模块名_XXXX。
正例: 表:t_basedata_user_info
反例: GetterAdmin, taskConfig,
说明: 表名应该仅仅表示表里面的实体内容,不应该表示实体数量,对应于 DO 类名也是单数形式,符合表达习惯。
2.视图必须使用小写字母、下划线;视图命名 v_模块名_XXXX。
正例: 视图:v_sett_transinfo
反例: v_sett_name3
3.字段名必须使用小写字母、数字、下划线; 禁止出现数字开头,禁止两个下划线中间只出现数字; 设计字段类型只有 varcher2,number;
正例: 字段:getter_admin, used_amount
反例: level_name3
说明: 主键使用 varchar2(64)
日期使用varchar2(10) yyyy-MM-dd
时间使用 varchar2(19)yyyy-MM-dd HH24:mi:ss
是否使用number(1)
金额使用 number(22,2)
利率、汇率 number(10,6)
如果存储超过 varchar2(4000) 使用大字段存储,需要提前说明
4.禁用保留字,如 desc、 range、 match、 delayed ,abstract等, 请参考官方保留字。
5. 唯一索引名为 uk_字段名; 普通索引名则为 idx_字段名。
说明: uk_ 即 unique key; idx_ 即 index 的简称。
6. 表必备五个字段: id(主键), creation_by(创建人编号)、creation_date(创建时间)、last_updated_by(更新人编号 )、last_update_date(更新时间)。
五个字段都为非空,数据第一次录入是 修改人和创建人相同 修改时间和创建时间相同
说明: 其中 id 必为主键,类型为 varchar2(64)。
creation_date,last_update_date 都为varchar2(19)
其他说明:
1.业务表设计时允许冗余10个备用字段,用于客户化开发,启用备用字段需要及时更新字段注释。
2.产品开发不允许使用备用字段,需要使用新字段时需要添加确定的字段名称及类型
3.业务表设计时,必须包含单据时间,且单据时间不能为空,用于业务表分区
4.表设计时不设置外检,如果使用到表之间的关联关系,使用方在注释中标明关联表
1.2 索引规约
1.业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。
说明: 不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的; 另外,即使在应用层做了非常完善的校验和控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
2.超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致; 多表关联查询时,保证被关联的字段需要有索引。
说明: 即使双表 join 也要注意表索引、 SQL 性能
3.在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度。
说明: 索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
4.页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
说明: 索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
5.如果有 order by 的场景,请注意利用索引的有序性。 order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。
正例: where a=? and b=? order by c; 索引: a_b_c
反例: 索引中有范围查找,那么索引有序性无法利用,如: WHERE a>10 ORDER BY b; 索引a_b 无法排序。
6.利用覆盖索引来进行查询操作,来避免回表操作。
说明: 如果一本书需要知道第 11 章是什么标题,会翻开第 11 章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。
正例: 能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的一种效果,用 explain 的结果, extra 列会出现: using index。
7.利用延迟关联或者子查询优化超多分页场景。
说明: MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。
正例: 先快速定位需要获取的 id 段,然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
8.SQL 性能优化的目标:至少要达到 range 级别, 要求是 ref 级别, 如果可以是 consts最好。
说明:
1) consts 单表中最多只有一个匹配行( 主键或者唯一索引) ,在优化阶段即可读取到数据。
2) ref 指的是使用普通的索引( normal index) 。
3) range 对索引进行范围检索。
反例: explain 表的结果, type=index,索引物理文件全扫描,速度非常慢,这个 index 级别比较 range 还低,与全表扫描是小巫见大巫。
9.建组合索引的时候,区分度最高的在最左边。
正例: 如果 where a=? and b=? , a 列的几乎接近于唯一值,那么只需要单建 idx_a 索引即可。
说明: 存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如: where a>? and b=? 那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。
10.创建索引时避免有如下极端误解:
1) 误认为一个查询就需要建一个索引。
2) 误认为索引会消耗空间、严重拖慢更新和新增速度。
3) 误认为唯一索引一律需要在应用层通过“先查后插”方式解决。
1.3 SQL规约
1.不要使用 count(列名)或 count(常量)来替代 count(*), count(*)就是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。说明: count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。
count(distinct col) 计算该列除 NULL 之外的不重复数量。注意 count(distinctcol1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。
2. 当某一列的值全是 NULL 时, count(col)的返回结果为 0,但 sum(col)的返回结果为NULL,因此使用 sum()时需注意 NPE 问题。
正例: 可以使用如下方式来避免 sum 的 NPE 问题: SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
3.使用 ISNULL()来判断是否为 NULL 值。注意: NULL 与任何值的直接比较都为 NULL。说明:
1) NULL<>NULL 的返回结果是 NULL, 而不是 false。
2) NULL=NULL 的返回结果是 NULL, 而不是 true。
3) NULL<>1 的返回结果是 NULL,而不是 true。
4.在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句。
5.不得使用外键与级联,一切外键概念必须在应用层解决。
说明: ( 概念解释) 学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,则为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群; 级联更新是强阻塞,存在数据库更新风暴的风险; 外键影响数据库的插入速度。
6.禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
7.数据订正时,删除和修改记录时,要先 select,避免出现误删除,确认无误才能执行更新语句。
8.in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。
9.如果有全球化需要,所有的字符存储与表示,均以 utf-8 编码,那么字符计数方法
注意:说明:
SELECT LENGTH("轻松工作"); 返回为 12
SELECT CHARACTER_LENGTH("轻松工作"); 返回为 4
如果要使用表情,那么使用 utfmb4 来进行存储,注意它与 utf-8 编码的区别。
10.TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。
说明: TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
1.4 ORM规约
1.在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。
说明: 1) 增加查询分析器解析成本。
2) 增减字段容易与 resultMap 配置不一致。
2.POJO 类的 boolean 属性不能加 is,而数据库字段必须加 is_,要求在 resultMap 中进行字段与属性之间的映射。
说明: 参见定义 POJO 类以及数据库字段定义规定,在 sql.xml 增加映射,是必须的。
3.不要用 resultClass 当返回参数,即使所有类属性名与数据库字段一一对应,也需要定义; 反过来,每一个表也必然有一个与之对应。
说明: 配置映射关系,使字段与 DO 类解耦,方便维护。
4.xml 配置中参数注意使用: #{}, #param# 不要使用${} 此种方式容易出现 SQL 注入。
5.iBATIS 自带的 queryForList(String statementName,int start,int size)不推荐使用。
说明:其实现方式是在数据库取到 statementName对应的SQL语句的所有记录,再通过 subList取 start,size 的子集合,线上因为这个原因曾经出现过 OOM。
正例: 在 sqlmap.xml 中引入 #start#, #size#
Map<String, Object> map = new HashMap<String, Object>();
map.put("start", start);
map.put("size", size);
1.5 数据库公共字段命名
字段名 | 字段类型(mysql) | 字段类型(oracle) | 是否为空 | 实体名 | 备注 |
id | varchar(64) | varchar2(64) | No | id | 表ID |
xxx_id | varchar(64) | varchar2(64) | No | 表明Id | 外键ID |
create_by_code | varchar(32) | varchar2(32) | No | createByCode | 创建用户编号 |
create_by_name | varchar(32) | varchar2(32) | No | createByName | 创建用户名称 |
create_time | varchar(17) | varchar2(17) | No | createTime | 创建时间 |
update_by_code | varchar(32) | varchar2(32) | No | updateByCode | 修改用户编号 |
update_by_name | varchar(32) | varchar2(32) | No | updateByName | 修改用户名称 |
update_time | varchar(17) | varchar2(17) | No | updateTime | 修改时间 |
data_status | int(2) | number(2) | No | data_status | 数据状态:0无效,1有效 |
data_version | int | number | No | dataVersion | 乐观锁,每次更新数据+1,新增插入默认为1 |
partition_date | date | date | No | partitionDate | 分区时间(分页查询数据量大的表必须添加此字段) |
biz_status | int(2) | number(2) | No | biz_status | 业务状态: |
biz_no | varchar(64) | varchar2(64) | No | biz_no | 业务单据号: |
serial_no | varchar(64) | varchar2(64) | No | serial_no | 业务流水号: |
* 注 意:从表(没有对应单独操作的功能表)和关系表不需要加创建人和修改人的信息,分区时间、数据状态,dataVersion也不需要
不能冗余没有用的字段和备用字段
如:关系表、日志表、无任何界面操作的配置表等。