建表规约
-
表达是与否概念的字段,必须使用
is_xxx
的方式命名,数据类型是unsigned tinyint
(1 表示是, 0 表示否)。
注意: POJO 类中的任何布尔类型的变量,都不要加 is 前缀,在 <resultMap> 设置从 is_xxx 到
xxx 的映射关系。 -
表名、字段名必须使用小写字母或数字, 禁止出现数字开头,禁止两个下划线中间只
出现数字。MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。 -
表名不使用复数名词。对应于 DO 类名也是单数形式。
-
主键索引名为
pk_字段名
;唯一索引名为uk_字段名
; 普通索引名则为idx_字段名
。 -
如果存储的字符串长度几乎相等,使用 char 定长字符串类型。
-
如果字符串存储长度大于5000,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索引效率。
-
表必备三字段:
id, gmt_create, gmt_modified
。其中 id 必为主键,类型为bigint unsigned
、单表时自增、步长为 1。 gmt_create, gmt_modified 的类型均为datetime
类型,前者现在时表示主动式创建,后者过去分词表示被动式更新。 -
字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循:
1) 不是频繁修改的字段。
2) 不是唯一索引的字段。
3) 不是 varchar 超长字段,更不能是 text 字段。
索引规约
-
业务上具有唯一特性的字段,必须建成唯一索引。不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的;
-
超过三个表禁止 join。需要 join 的字段,数据类型保持绝对一致; 多表关联查询时,保证被关联的字段需要有索引。
-
在 varchar 字段上建立索引时,必须指定索引长度,可以使用
count(distinct left(列名, 索引长度))/count(*)
的区分度来确定。 -
利用覆盖索引来进行查询操作, 避免回表。用 explain 的结果, extra 列会出现: using index。
-
利用延迟关联或者子查询优化超多分页场景。MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下。
正例: 先快速定位需要获取的 id 段,然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
-
SQL 性能优化的目标:至少要达到 range 级别, 要求是 ref 级别, 如果可以是 consts
最好。
1) consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
2) ref 指的是使用普通的索引( normal index) 。
3) range 对索引进行范围检索。 -
使用组合索引的时候,存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。如: where c>? and d=? 那么即使 c 的区分度更高,也必须把 d 放在索引的最前列, 即建立组合索引 idx_d_c。
SQL 语句
-
不要使用
count(列名)
或count(常量)
来替代count(*)
,count(*)
是 SQL92 定义的标
准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。count(列名)
不会统计此列为 NULL 值的行。 -
当某一列的值全是 NULL 时,
count(col)
的返回结果为 0,但sum(col)
的返回结果为 NULL -
使用 ISNULL()来判断是否为 NULL 值。
说明: NULL 与任何值的直接比较都为 NULL。
1) NULL<>NULL 的返回结果是 NULL, 而不是 false。
2) NULL=NULL 的返回结果是 NULL, 而不是 true。
3) NULL<>1 的返回结果是 NULL,而不是 true。 -
数据订正(特别是删除或修改记录操作) 时,要先 select,避免出现误删除,确认无误才能执行更新语句。
-
in 操作能避免则避免,若实在避免不了,需要仔细评估 in 后边的集合元素数量,控制在 1000 个之内。
ORM 映射
-
不要用 resultClass 当返回参数,即使所有类属性名与数据库字段一一对应,也需要定义<resultMap>,使字段与 DO 类解耦,方便维护。
-
不允许直接拿 HashMap 与 Hashtable 作为查询结果集的输出。
-
不要写一个大而全的数据更新接口。 传入为 POJO 类,不管是不是自己的目标更新字段,都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL 时,不要更新无改动的字段,一是易出错;二是效率低;三是增加 binlog 存储。
-
@Transactional 事务不要滥用。事务会影响数据库的 QPS,另外使用事务的地方需要考虑各方面的回滚方案,包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。