SQL规范
-
超过三个表禁止join。需要join的字段,数据类型必须绝对一致,多表关联查询时,保证被关联的字段需要有索引。
说明:即使双表join也要注意表索引,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
- null <> null 的返回结果为null,而不是false
- null = null 的返回结果为null,而不是true
- null <> 1的返回结果为null,而不是true
-
在代码中写分页查询逻辑时,若count为0应直接返回,避免执行后面的分页语句
-
不得只用外键与级联,一切外键概念必须在应用层解决
-
禁止使用存储过程,存储过程难以调试和扩展,更没有移植性
-
数据订正时,删除和修改记录时,要先select,避免出现误删除,确认无误才能执行更新语句
-
in操作能避免则避免,若实在避免不了,需要仔细评估in后面的集合元素的数量,控制在1000个以内。另外,in函数会导致索引失效
-
避免数据类型的隐式转换,隐式转换会导致索引失效
-
避免使用左模糊查询,如“ like ‘%xx’ ”,这种方式同样会导致索引失效
-
禁止使用不含字段列表的insert语句
-
避免使用子查询,可以把子查询优化成join查询
说明:
- 子查询的结果集无法使用索引
- 子查询会产生临时表操作,如果子查询数据量大则验证影响效率
- 消耗过多的CPU及IO资源
-
减少同数据库的交互次数
说明:
- 数据库更适合处理批量操作
- 合并多个相同操作到一起,可以提高处理效率,如批量插入
-
禁止使用order by rand()操作
-
where从句中避免对列进行函数转换和计算
说明:对列进行函数转换或计算会导致无法使用索引
-
在明显不会有重复值时使用UNION ALL 而不是UNION
说明:
- UNION会把所有数据放到临时表后进行去重操作
- UNION ALL 不会再对结果集进行去重操作
-
拆分复杂的大SQL为多个小SQL
说明:一个SQL只能使用一个CPU进行计算,SQL拆分后可以并行
关于ORM映射
-
在表查询中,一律不要使用 * 作为查询的字段列表,需要哪些字段必须明确写明。
说明:
- 增加查询分析器解析成本
- 增减字段容易与resultMap配置不一致
-
POJO类的布尔属性不能加is,而数据库字段必须加is_,要求在resultMap中进行字段与属性之间的映射
-
不要用resultClass当返回值,即使所有类属性名与数据库字段名一一对应,也需要定义;反过来,每一个表也必然有一个与之对应
-
不允许直接使用HashMap与HashTable作为查询结果集输出。
-
更新数据库表记录时,必须同时更新记录对应的gmt_modified字段值为当前时间。
-
不要写一个大而全的数据更新接口,传入POJO类,不管是不是自己的目标更新字段,都进行一次更新,这是不对的。执行SQL不要更新无改动的字段,一是容易出错,二是效率低,三是增加了binlog存储
-
@Transacation事务不要滥用,事务会影响数据库的QPS,另外,使用事务的地方需要考虑各方面的回滚方案,包括缓存回滚,搜索引擎回滚,消息补偿,统计修正等。