SQL优雅编码总结

Leo_Hu666

已于 2024-03-01 17:40:57 修改

阅读量842

点赞数 26

于 2024-03-01 17:32:23 首次发布

本文链接：https://blog.csdn.net/weixin_43103956/article/details/136402190

版权

MySQL 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

sql

1 篇文章 0 订阅

订阅专栏

1.

不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的，标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

2.

count(distinct col)
计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

3.

当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE 问题
避免 sum 的 NPE 问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;

4.

在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

5.

使用 ISNULL()来判断是否为 NULL 值。
说明：NULL 与任何值的直接比较都为 NULL。
1） NULL<>NULL 的返回结果是 NULL，而不是 false。
2） NULL=NULL 的返回结果是 NULL，而不是 true。
3） NULL<>1 的返回结果是 NULL，而不是 true。

6.

不得使用外键与级联，一切外键概念必须在应用层解决。
说明：以学生和成绩的关系为例，学生表中的 student_id是主键，那么成绩表中的 student_id
则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

7.

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

8.

in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。

9.

事务：
a)@Transactional 事务不要滥用，事务会影响数据库的 QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。

b)如果需要事务的支持，在确认使用了 innodb 存储引擎的前提下，在数据库连接时，先关闭自动提交
比如，设定 set auto_commit =0 ;

10.

应用程序里的 SQL 语句，禁止一切 DDL 操作
例： Create table ， Drop table ， Create database ， Drop database ，Alter table ，grant … …
如有特殊需要，必需与技术专家（如DBA）协商同意方可使用。

11.

获取当前时间请使用 now()，不要用 sysdate()来代替
这对复制来说是很危险的，会导致主从数据不一致的情况；因为 sysdate,取的是系统主机时间，在 BINLOG 会原文传输，当在应用时会与主库产生差异。

12.

写 SQL 的时候一定要给每个字段指定表名做前缀
比如:
select a.id,a.name from test a;
好处是：
一来带来性能的提升；
二来可以避免一些错误的发生。

13.

在 iBatis 的 SqlMap 文件中绑定变量使用 “#var_name#”表示,替代变量使用“ $var_name$ ”
所有需要动态 Order By 条件的 Query，在使用替代变量过程中，需要将可能传入的内
容以枚举类写死在代码中，禁止接受任何外部传入内容。

14.

请不要写 select * 这样的代码，指定需要的字段名
几个方面考虑：增加查询分析器解析成本、网络、二级索引（覆盖索引）、增减字段容易与 resultMap 配置不一致。

15.

Mysql 对日期（datetime）允许“不严格”语法
任何标点符都可以用做日期部分或时间部分之间的间隔符。
例如：‘22-12-31 11:30:45’、‘22.12.31 11+30+45’、'22/12/31 113045’和’22@12@31 11³⁰45’是等价的。
约定一种所有数据库通用的写法： ‘2009-12-31 11:30:45’

16.

Mysql 的日期与字符是相同的，所以不需要做另外的转换
比如:
Select e.username from employee e where e.birthday >=’1998-12-31 11:30:45’

17.

避免多余的排序。使用 GROUP BY 时，默认会进行排序，当你不需要排序时，可以使用order by null
比如：
Select product,count(*) cnt from crm_sale_detail group by product order by null;

18.

避免在 where 子句中对字段施加函数
a) 通常，不允许在字段上添加函数或者表达式，这样将导致索引失效，如：

错误的写法：select * from iw_account_log where substr(username,1,5)=’abcde’
正确的写法：select * from iw_account_log where username like ’abcde%’

b) 如果是业务要求的除外，但需要和技术经理、技术专家(DBA)等充分讨论；

c) 特别注意，当表连接时，用于连接的两个表的字段如果数据类型不一致，则必须在
一边加上类型转换的函数。

19.

严格要求使用正确类型的变量，杜绝 Mysql 做隐式类型转换的情况

20.全模糊查询无法使用 INDEX，应当尽可能避免

比如：select * from table where name like '%jacky%';

21. “join”、“in”、“not in”、"exsits"和"not exists"的使用

a) 比较 IN,EXISTS,JOIN
	按效率从好到差排序: 
		字段上有索引 : EXISTS, IN, JOIN 
		字段上没有索引: JOIN, EXISTS ,IN

b) Anti-Joins: NOT IN ,NOT EXISTS, LEFT JOIN
	按效率从好到差排序: 
		字段上有索引 : LEFT JOIN, NOT EXISTS, NOT IN
		字段上没有索引: NOT IN, NOT EXISTS, LEFT JOIN

22.表连接规范

23. 表连接分页查询的使用

a) 常规分页语句写法(start：起始记录数，page_offset：每页记录数)：
SELECT ID,username FROM gl_user
WHERE username like ‘%@163.com’ ORDER BY M.gmt_create LIMIT start, page_offset;

b) 多表 Join 的分页语句，如果过滤条件在单个表上，需要先分页，再 Join：
低性能写法：
SELECT M.username,P.rolename FROM gl_user M INNER JOIN gl_role P ON
M.ID=P.userid WHERE username like ‘%@163.com’ ORDER BY M.gmt_create LIMIT
start, page_offset;
高性能写法：
SELECT M.username,P.rolename FROM (
SELECT ID,username FROM gl_user WHERE username like ‘%@163.com’
ORDER BY M.gmt_create LIMIT start, page_offset
)M,gl_role P
WHERE M.ID=P.userid;

这样写的前提是关联的表之间记录一一对应，否则可能会返回的记录数目少于或多余
page_offset 的值。

Leo_Hu666

关注

26
点赞
踩
17

收藏

觉得还不错? 一键收藏
0
评论
SQL优雅编码总结

例： Create table ， Drop table ， Create database ， Drop database ，Alter table ，grant …a)@Transactional 事务不要滥用，事务会影响数据库的 QPS，另外使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE 问题。
复制链接

扫一扫