建表规约
- 表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是
unsigned tinyint
( 1 表示是,0 表示否)。
说明:任何字段如果为非负数,必须是unsigned。
正例:表达逻辑删除的字段名 is_deleted,1 表示删除,0 表示未删除。
2. 表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。
数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
说明:MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。因此,数据库名、表名、字段名,都不允许出现任何大写字母,避免节外生枝。
正例:aliyun_admin,rdc_config,level3_name
反例:AliyunAdmin,rdcConfig,level_3_name
4. 表名不使用复数名词。
说明:表名应该仅仅表示表里面的实体内容,不应该表示实体数量,对应于 DO 类名也是单数形式,符合表达习惯。
- **禁用保留字,**如
desc、range、match、delayed
等,请参考 MySQL 官方保留字。 - 主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。
说明:pk_ 即primary key
;uk_ 即unique key
;idx_ 即 index 的简称。 - 小数类型为
decimal
,禁止使用 float 和 double。
说明:float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不 正确的结果。如果存储的数据范围超过decimal 的范围,建议将数据拆成整数和小数分开存储。
- 如果存储的字符串长度几乎相等,使用 char 定长字符串类型。
varchar
是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。- 表必备三字段:
id, gmt_create, gmt_modified
说明:其中 id 必为主键,类型为
unsigned bigint、
单表时自增、步长为 1。gmt_create, gmt_modified
的类型均为 date_time 类型,前者现在时表示主动创建,后者过去分词表示被 动更新
索引规约
- 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。
- 超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。
说明:即使双表 join 也要注意表索引、SQL 性能。
- 在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。
说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90%以上,可以使用
count(distinct left(列名, 索引长度))/count(*)
的区分度来确定。
- 页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
说明:索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引。
- 如果有
order by
的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现 file_sort 的情况,影响查询性能。
正例:where a=? and b=? order by c; 索引:a_b_c
反例:索引中有范围查找,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引a_b 无法排序。
SQL语句
- 不要使用 c
ount(列名)或 count(常量)
来替代count(*),count(*)
是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。
说明:count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。
- count(distinct col) 计算该列除 NULL 之外的不重复行数,注意 count(distinct col1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。
- 当某一列的值全是 NULL 时,count(col)的返回结果为 0,但 sum(col)的返回结果为NULL,因此使用 sum()时需注意 NPE 问题。
正例:可以使用如下方式来避免 sum 的 NPE 问题:SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
- 使用 ISNULL()来判断是否为 NULL 值。
说明:NULL 与任何值的直接比较都为 NULL。
1) NULL<>NULL 的返回结果是 NULL,而不是 false。
2) NULL=NULL 的返回结果是 NULL,而不是 true。
3) NULL<>1 的返回结果是 NULL,而不是 true。
- 在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句。
- 不得使用外键与级联,一切外键概念必须在应用层解决。
说明:以学生和成绩的关系为例,学生表中的 student_id是主键,那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
- 禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
- 数据订正时,删除和修改记录时,要先 select,避免出现误删除,确认无误才能执行更新语句。
ORM映射
- 在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。
说明:
1)增加查询分析器解析成本。
2)增减字段容易与 resultMap 配置不一致。
- POJO 类的布尔属性不能加 is,而数据库字段必须加 is_,要求在 resultMap 中进行字段与属性之间的映射。
说明:参见定义 POJO 类以及数据库字段定义规定,在中增加映射,是必须的。
在 MyBatis Generator 生成的代码中,需要进行对应的修改。 - 不要用
resultClass
当返回参数,即使所有类属性名与数据库字段一一对应,也需要定义;反过来,每一个表也必然有一个与之对应。
说明:配置映射关系,使字段与 DO 类解耦,方便维护。 - sql.xml 配置参数使用:#{},#param# 不要使用${} 此种方式容易出现 SQL 注入。
- BATIS 自带的
queryForList(String statementName,int start,int size)
不推荐使用。
说明:其实现方式是在数据库取到statementName对应的SQL语句的所有记录,再通过subList 取 start,size的子集合。
正例:
Map<String, Object> map = new HashMap<String, Object>();
map.put("start", start);
map.put("size", size);
-
不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
说明:resultClass=”Hashtable”,会置入字段名和属性值,但是值的类型不可控。 -
更新数据表记录时,必须同时更新记录对应的 gmt_modified 字段值为当前时间。