一、建表规约
1.表达是与否概念的字段,使用is_xxxx的方式命名,数据类型是 unsigned tinyint(1表示是,0表是否),任何字段如果为非负数,必须是unsigned。
eg:表示逻辑删除的字段名为is_deleted,1表示删除,0表示未删除。
注意:数据库表示是与否的值,使用tinyint类型,坚持is_xxxx的命名方式是为了明确其取值含义与取值范围。
2.表名、字段名必须使用小写字母或数字,禁止使用数字开头,禁止两个下划线中间只出现数字。
3.表名不使用复数名词。表名应该仅仅表示表里面的实体内容,不应该表示实体数量。
4.禁止使用保留字。如desc、range、match、delayed等。
5.小数类型为decimal,禁止使用float和double。
6.如果存储的字符串长度几乎相等,使用char定长字符串类型。
7.varchar是可变长字符串,不预先分配存储空间,长度不要超过5000,如果存储长度大于5000,定义字段类型为text,独立出来一张表,用主键来对应,避免影响其他字段索引效率。
8.表必备三个字段:id、create_time、update_time。其中id必为主键,类型为bigint unsigend、表单时自增,步长为1。create_time,update_time的类型均为datetime类型,前者为创建时间,后者为更新时间。
9.如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注解。
10.库名与应用名称尽量一致。
11.字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
- 不是频繁修改的字段。
- 不是唯一索引的字段。
- 不是varchar超长字段,更不能时text字段。
12.适合的字符存储长度,不但节约数据库表空间,节约索引存储,更重要的是提升检索速度。
类型 | 字节 | 表示范围 |
tinyint unsigned | 1 | 无符号值:0到255 |
smallint unsigned | 2 | 无符号值:0到65535 |
int unsigned | 4 | 无符号值:0到约43亿 |
bigint unsigned | 8 | 无符号值:0到约10的19次方 |
二、索引规约
1.业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。
2.超过三个表禁止join。需要join的字段,数据类型保持绝对一致;多表关联查询时,保证被关联的字段需要有索引。
3.在varchar字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度。
4.页面搜索严禁做模糊或者全模糊,如果需要请走搜索引擎来解决。
5.如果有order by的场景,请注意利用索引的有序性。order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort 的情况,影响查询性能。
6.SQL性能优化的目标:至少要达到range级别,要求是ref级别,如果可以是consts最好。
- consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
- ref 指的是使用普通的索引(normal index)。
- range 对索引进行范围检索。
7.建组合索引的时候,区分度最的在最左边。
8.创建索引时避免有如下极端误解:
- 索引宁滥勿缺。认为一个查询就需要建一个索引。
- 吝啬索引的创建。认为索引会消耗空间,严谨拖慢记录的更新以及行的新增速度。
- 抵制唯一索引。认为唯一索引一律需要在应用层通过“先查后插”方式解决。
三、SQL语句
1.不要使用count(列名)或count(常量) 来代替count(*),count(*) 跟数据库无关,也跟NULL和NOT NULL无关。
2.当某一列的值全是NULL时,count(col)的返回结果为0,但是sum(col)的返回结果为NULL,因此使用sum()时需要注意NPE问题。
可以使用该方式来避免sum的NPE问题:select IFNULL(SUM(column), 0) from table;
3.使用ISNULL() 来判断是否为NULL值。
说明:NULL与任何值的直接比较都为NULL。
- NULL < > NULL 的返回结果是NULL,而不是false。
- NULL = NULL 的返回结果是NULL,而不是true。
- NULL < > 的返回结果是NULL,而不是true。
4.代码中写分页查询逻辑时,若count为0应直接返回,避免执行后面的分页语句。
5.禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。
6.数据在删除和修改记录操作时,要先select,避免出现误删除,确认无误才能执行更新语句。
7.对于数据库中表记录的查询和变更,只要涉及多个表,都需要在列名前加表的别名(或表名)进行限定。
8.SQL语句中表的别名前加as。别名可以时表的简称,别名前加as 使别名更容易识别。
9.in 操作能避免就避免,若实在避免不了,需要仔细评估in 后边的集合元素的数量,控制1000个之内。
10.所有的字符存储与表示,均采用utf8字符集,如果需要存储表情,那么选择utf8mb4来进行存储。
四、ORM映射
1.在查询中,一律不要使用 * 作为查询的字段列表,需要那些字段必须明确写明。
原因:
- 增加查询分析器解析成本。
- 增减字段容易与resultMap配置不一致。
- 无用字段增加网络消耗,尤其是text类型的字段。
2.POJO类的布尔属性不能加 is ,而数据库字段必须加 is_,要求在resultMap中进行字段与属性之间的映射。
3.不要用resultClass当返回值,即使所有类属性与数据库字段一一对应,也需要定义<resultMap>;反过来,每个表也必然有一个<resultMap>与之对应。
4.sql.xml 配置参数使用:#{},#param#,不要使用${} 此种方式,容易出现SQL注入。
5.不允许直接拿HashMap 与HashTable作为查询结果集输出。
6.更新数据表记录时,必须同时更新记录对应的update_time 字段值为当前时间。
7.@Transactional事务不要滥用。事务会影响数据库的QPS,另外使用事务的地方需要考虑个方面的回滚方案,包括缓存回滚,搜索引擎回滚,消息补偿,统计修正等。
8.<isEqual>中的compareValue是与属性值对比的常量,一般是数字,表示相等时带上此条件;<isNotEqual>表示不为空且不为null时执行;<isNotNull>表示不为null值时执行。