建表规约
- 表名、字段名必须使用小写字母或数字,禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
- 表名不使用复数名词。
- 禁用保留字,如 desc、range、match、delayed 等,请参考 MySQL 官方保留字。
- 主键索引名为 pk_字段名;唯一索引名为 uk_字段名;普通索引名则为 idx_字段名。
pk_ 即 primary key;uk_ 即 unique key;idx_ 即 index 的简称。
- 数据表、数据字段必须加入中文注释。
- 原则上不使用存储过程, !!#ff0000 外键!! ,视图,触发器,Event
难以调试和扩展,更没有移植性。
- 文件或图片统一存储在文件系统中,URI存储文件的相对路径。
- 数据库连接优先使用内网域名,尽量避免使用IP连接。
- 小数类型为 decimal,禁止使用 float 和 double。
float 和 double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确结果。如果存储的数据范围超过 decimal 的范围,建议将数据拆成整数和小数分开存储
- 如果存储的字符串长度几乎相等,使用 char 定长字符串类型。
- varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
- 表的命名最好是加上“业务名称_表的作用”。
- 数据表必须设置主键,主键数据类型为 bigint unsigned、单表时自增、步长为 1。
- 字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
1)不是频繁修改的字段。
2)不是 varchar 超长字段,更不能是 text 字段。
- 布尔类型的数值使用
tinyint
存储,0表示false
,1表示true
- 涉及货币的数据存储统一用整型存储,出库计算时通过程序移位完成。
- 数值类型除非有特殊说明,否则默认设置为无符号整数。
索引规约
- 业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。
- 超过三个表禁止 join。需要 join 的字段,数据类型必须绝对一致;多表关联查询时,保证被关联的字段需要有索引。
- 在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。
- 建组合索引的时候,区分度最高的在最左边。
存在非等号和等号混合时,在建索引时,请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高,也必须把 d 放在索引的最前列,即索引 idx_d_c。
- 页面搜索不建议左模糊或者全模糊,如果需要请走搜索引擎来解决。
- SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好。
1)consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
2)ref 指的是使用普通的索引(normal index)。
3)range 对索引进行范围检索。
- 对于业务表必备如下字段(业务表是指关系产品具体业务逻辑实现的数据表,不包括诸如日志,配置等表):
id
:主键
create_on
:创建时间
create_by
: 创建人
modify_on
: 最近一次更新时间(创建时等于创建时间)
modify_by
: 最近一次更新人
del_flag
: 删除标记(默认为0,即正常状态)
SQL规约
- 不要使用 count(列名)或 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 应直接返回,避免执行后面的分页语句。
- 不得使用外键与级联,一切外键概念必须在应用层解决。
- in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。
- TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少,但 TRUNCATE无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。
- 禁止使用SELECT *,只获取必要的字段。
- 禁止使用属性隐式转换,如
SELECT id FROM user_user WHERE mobile=123456
,而是SELECT id FROM user_user WHERE mobile='123456'
导致索引失效。 - 建议尽量避免使用负向查询,如
NOT
,!=
,<>
,!<
,!>
,NOT IN
,NOT LIKE
等。
ORM 映射规约
- @Transactional 事务不要滥用。事务会影响数据库的 QPS,另外使用事务的地方需要考虑各方面的回滚方案,包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
- 不要写一个大而全的数据更新接口。传入为 POJO 类,不管是不是自己的目标更新字段,都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL时,不要更新无改动的字段,一是易出错;二是效率低;三是增加 binlog 存储。
- 更新数据表记录时,必须同时更新记录对应的 modified 字段值为当前时间。