【MySQL数据库开发设计规范】之SQL使用规范_查询条件使用强制限制。

欢迎点开这篇文章，自我介绍一下哈，本人姑苏老陈 ，是一名JAVA开发老兵。

本文收录于 《MySQL数据库开发设计规范》专栏中，该专栏主要分享一些关于MySQL数据库开发设计相关的技术规范文章，定期更新，欢迎关注，谢谢 :-）

一、前言

在我们对数据库技术方案设计的时候，我们是否有自己的设计理念或者原则，还是更多的依据自己的直觉去设计，是否曾经懊悔线上发生过的一次低级故障，可能稍微注意点就可以避免，是否想过怎么才能很好的避免，规范的价值正是我们工作的检查清单，需要我们不断从错误中积累有效经验来指导未来的工作。

本文介绍MySQL数据库开发设计规范之SQL使用规范。

PS：以下规范在大型互联网公司经过了充分的验证，尤其适用于并发量大、数据量大的业务场景。

二、SQL使用规范

1.【强制】禁止使用SELECT *，只获取必要的字段，需要显示说明列属性；
说明：按需获取字段，可以减少网络带宽消耗，能有效利用覆盖索引，表结构变更对程序基本无影响。

2.【强制】禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性；

3.【强制】WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化；
说明：因为MySQL进行隐式类型转化之后，可能会将索引字段类型转化成=号右边值的类型，导致使用不到索引，原因和避免在索引字段中使用函数是类似的；

示例：select uid from t_user where phone=15855550101，其中phone字段为varchar类型，此时查询中使用数字查询，会导致索引失效）；
1

4.【强制】禁止在WHERE条件的属性上使用函数或者表达式；

反例：SELECT uid FROM t_user WHERE from_unixtime(day)>='2024-05-12'，会导致全表扫描；

正例：SELECT uid FROM t_user WHERE day>= unix_timestamp('2024-05-12 00:00:00');

反例：SELECT uid FROM t_user WHERE age +1>40，会导致全表扫描；
1
2
3
4
5

5.【强制】禁止负向查询，以及%开头的模糊查询；
说明：负向查询比如使用NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描；

反例：select * from t_user where user_name like ‘%子轩’；
反例：select * from t_user where user_name not like ‘陈%’；
1
2

6.【强制】应用程序必须捕获SQL异常，并有相应处理；

7.【推荐】sql语句尽可能简单、大的sql想办法拆成小的sql语句；
说明：简单的SQL容易使用到MySQL的querycache、减少锁表时间特别是MyISAM、可以使用多核cpu；

8.【推荐】事务要简单，整个事务的时间长度不要太长；

9.【强制】避免在数据库中进行数学运算或者函数运算；
说明：MySQ不擅长数学运算和逻辑判断，在数据库中进行数学运算或者函数运算，也容易将业务逻辑和DB耦合在一起；

10.【推荐】sql中使用到OR的改写为用IN() ；
说明：or的效率没有in的效率高；

11.【参考】SQL语句中IN包含的值不应过多，里面数字的个数建议控制在1000个以内；

12.【推荐】limit分页注意效率，Limit越大，效率越低。
说明： Limit过大的改下方法如下

1）改写方法一
延迟回表写法，select xx,xx from t t1, (select id from t where ....  limit 10000,10) t2 where t1.id = t2.id；
2）改写方法二
select id from t limit 10000, 10; 应该改为 => select id from t where id > 10000 limit 10;
1
2
3
4

13.【推荐】尽量使用union all替代union；

14.【参考】避免使用大表JOIN；

15.【推荐】对数据的更新要打散后批量更新，不要一次更新太多数据；

16.【推荐】使用合理的SQL语句减少与数据库的交互次数；

17.【参考】注意使用性能分析工具Sql explain / showprofile / mysqlsla；

18.【推荐】能不用NOT IN就不用NOT IN，坑太多了，会把空和NULL给查出来；

19.【推荐】关于分页查询，程序里建议合理使用分页来提高效率，limit、offset较大要配合子查询使用；

20.【强制】禁止在线上数据库中跑大查询；

21.【强制】禁止单条SQL语句同时更新多个表；

22.【推荐】统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1)；
说明：count( * ) 会统计值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL 值的行；

23.【推荐】上万条大数据量批量插入时，INSERT语句使用batch提交，values的个数不应过多；
说明：INSERT语句使用batch提交，格式为INSERT INTO tableVALUES(),(),()……；

例如：
INSERT INTO students (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
1
2
3
4
5

24.【推荐】获取大量数据时，建议分批次获取数据，每次获取数据少于2000条，结果集应小于1M；

25.【推荐】在做开发时建议使用数据库框架(如mybatis)或prepared statement，可以提升性能并避免SQL注入；

26.【强制】禁止跨库查询（为数据迁移和分库分表留出余地，降低耦合度，降低风险）；

27.【推荐】尽量避免使用子查询，可以把子查询优化为join操作；
说明： 子查询是嵌套在另一个 SELECT, INSERT, UPDATE, 或 DELETE查询的 SQL 查询。子查询的结果集无法使用索引，子查询会产生临时表操作，如果子查询数据量大会影响效率，消耗过多的CPU及IO资源。

28.【强制】超过三个表禁止join。
说明：需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。即使双表join也要注意表索引、SQL 性能。

29.【推荐】SQL性能优化的目标：至少要达到range级别，要求是ref级别，如果可以的话，consts最好；

30.【推荐】尽量不要使用物理删除（即直接删除，如果要删除的话提前做好备份），而是使用逻辑删除，使用字段delete_flag做逻辑删除，类型为tinyint，0表示未删除，1表示已删除；

31.【强制】在代码中写分页查询逻辑时，若count 为0应直接返回，避免执行后面的分页语句；

32.【强制】程序连接不同的数据库要使用不同的账号；

33.【推荐】使用ISNULL()来判断是否为NULL值；

三、总结

以上介绍了软件开发过程中，在使用SQL语句时，可以遵循的一些技术规范。希望对大家有帮助，谢谢 :-）

附本文参考资料：

• 阿里云官方知乎：https://www.zhihu.com/org/a-li-yun-97-77
• 阿里云开发者官方社区：https://developer.aliyun.com/
• 阿里开发者官方CSDN社区：https://blog.csdn.net/alitech2017?type=blog
• 阿里云云栖号CSDN：https://blog.csdn.net/yunqiinsight/category_10231626.html
• 阿里巴巴技术团队发布的《JAVA开发手册》泰山版
• 阿里云开发者官方微信公众号

如果您对文章中内容有疑问，欢迎在评论区进行留言，我会尽量抽时间给您回复。如果文章对您有帮助，欢迎点赞、收藏。您的点赞，是对我最大的支持和鼓励，谢谢 :-）