本文主要是介绍【MySQL数据库开发设计规范】之SQL使用规范,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
欢迎点开这篇文章,自我介绍一下哈,本人姑苏老陈 ,是一名JAVA开发老兵。
本文收录于 《MySQL数据库开发设计规范》专栏中,该专栏主要分享一些关于MySQL数据库开发设计相关的技术规范文章,定期更新,欢迎关注,谢谢 :-)
文章目录
- 一、前言
- 二、SQL使用规范
- 三、总结
一、前言
在我们对数据库技术方案设计的时候,我们是否有自己的设计理念或者原则,还是更多的依据自己的直觉去设计,是否曾经懊悔线上发生过的一次低级故障,可能稍微注意点就可以避免,是否想过怎么才能很好的避免,规范的价值正是我们工作的检查清单,需要我们不断从错误中积累有效经验来指导未来的工作。
本文介绍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类型,此时查询中使用数字查询,会导致索引失效);
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,会导致全表扫描;
5.【强制】禁止负向查询,以及%开头的模糊查询;
说明:负向查询比如使用NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等,会导致全表扫描;
反例:select * from t_user where user_name like ‘%子轩’;
反例:select * from t_user where user_name not like ‘陈%’;
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;
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');
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开发手册》泰山版
- 阿里云开发者官方微信公众号
如果您对文章中内容有疑问,欢迎在评论区进行留言,我会尽量抽时间给您回复。如果文章对您有帮助,欢迎点赞、收藏。您的点赞,是对我最大的支持和鼓励,谢谢 :-)
这篇关于【MySQL数据库开发设计规范】之SQL使用规范的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!