开发者社区> 深蓝居> 正文

数据库SQL开发的一些要点

简介:
+关注继续查看

前段时间做一个项目,其中涉及到报表部分编写了大量复杂的SQL,比如其中的一个存储过程就有700多行。项目上线过后,进入维护阶段,发现大量的SQL很难维护。于是总结点经验教训:

设计

一、数据库命名遵循一些通用规范。

数据库命名规范是个基本的命名标准,每个团队都有自己的命名规范,我们做项目中以全大写下划线分割作为标准。表名或字段名要准确表达其业务含义。以DATE结尾的数据类型都是date类型,以TIME结尾的数据类型是datetime类型。以IS开头的都是bool类型。

二、大数据对象列应该独立成表。

比如员工照片是一个blob对象,按照范式来说,这个字段完全可以放在Employee表中,但是出于性能的考虑,最好单独出一张EmployeePhoto表,与Employee是一对一的关系,这样使用ORM的时候,平时取Employee对象就不会取到照片,只有需要显示照片时才取EmployeePhoto对象。

三、数据库字段尽量不要为null。

一个字段允许为空,那么在SQL查询时就需要进行一些特殊处理,比如在WHERE条件中用上 t1.COLUMN1 IS NULL或者在SELECT时用上ISNULL()函数。而在ORM时,对应的对象的数据类型如果是不允许为空的,还必须加上?表示允许为空。在编程时也要进行判断该值是否为空。一不小心就容易漏掉空的判断,造成计算结果不正确。所以在数据库设计时,尽量将每个字段设计为not null。

四、带小数的字段使用Decimal数据类型而不要使用Float数据类型。

因为Float类型是用于表示浮点数据的近似数据类型,所以存储后可能会造成一点误差,如果在程序中传入2.4进行保存,可能读取到的值却是2.4000001或者2.399999999。

五、使用配置表来存储可能经常变化的配置项,而不是写死在代码中。

在编写查询语句,写存储过程或者出报表时,经常会对某些字段进行过滤。比如ProjectAssignment表中有个RoleCode字段,表示在往项目上分配人时,该人的角色。在查询时经常会把角色A、B、C放在一起作为管理层角色,那么在关于管理层分配的各种报表中,就充斥着where pa.ROLE_CODE in ('A','B','C')这样的条件。但是有一天,用户说现在角色D也算是管理层角色了,那么之前做的所有报表,都要将这段代码进行修改。

所以对于这种可能修改的查询条件,那么最好是建立一个配置表,然后所有查询都是从这个配置表中读取数据进行查询。那么前面是SQL可以改为:

where pa.ROLE_CODE in (select CODE from CONFIG where CODE_TYPE='Management')

虽然这样要牺牲一点点的性能,但是由于本身配置表数据量不会很大,而且可以以CODE_TYPE建立聚集索引,那么性能不是很大的问题。

六、不要使用ORM工具通过对象模型生成数据库。

数据库的创建和修改都应该以脚本来完成,而每个字段的数据类型、长度、表的各种约束(主键约束、外键约束、唯一约束、非空约束、CHECK约束等)、索引都应该是需要根据实际需求进行设计的,而使用ORM工具通过对象模型只能生成一个大概的表和列,无法生成准确的Schema。推荐使用专业的数据库建模工具PowerDesigner或者ERWin进行数据库建模,然后生成数据库脚本。

开发

一、使用有意义的表别名。

在进行查询时经常会JOIN很多表,那么就经常用到表别名,表别名使得SQL开发更简单,查看起来也更简洁。表别名一般就1个字母,或者2个字母,采用表的单词首字母作为别名即可。

select 'ProjectAssignAuth', p.PROJECT_ID , 0 , 0 , gs.EMPLOYEE_ID,'STAFF' from GROUP_STAFF gs 
join GROUP_PROJECT gp on gs.GROUP_ID = gp.GROUP_ID 
join PROJECT p on gp.PROJECT_ID = p.PROJECT_ID

二、SQL语句中应该写上详细注释。

这个算是老生常谈了,SQL也是一种语言,对于复杂的逻辑,一不小心存储过程就写出了几百行,如果没有注释,那么看一个几百行的SQL那真是无比痛苦的事情,即使这个SQL是自己写的,那么一个月以后,没有注释连自己都看不懂自己在写什么。

三、使用print打印出过程信息。

在编写复杂的存储过程时,不可避免的就是要调试存储过程的正确性,虽然SQL Server支持调试SQL语句的功能,但是在对于几百行的SQL来说,还是很麻烦的。所以在编写SQL时加入print过程信息的功能,这个相当于写程序时的Debug.WriteLine(),打印的信息对外部程序并没有影响,只是在SSMS调用存储过程时能够打印一些有用的信息。

四、增加调试参数帮助输出更多的调试信息。

在编写C#代码的时候,我们在VS中可以设置Debug或者Release模式,同样我们可以在存储过程中增加一个带有默认值的参数,比如我们有一个计算项目金额的存储过程,计算逻辑复杂,我们可以增加一个@debug参数,默认情况下是关闭的,输入一些调试信息。

create proc PROC_CALC_PROJECT_AMOUNT 
@pid int,--项目ID 
@debug bit=0 
asSQL if(@debug=1) 
begin 
--输出一下调试信息

end

这样我们平时调用时只传入一个参数,在SSMS中想打开调试信息时,只需要增加第二个参数1即可:

exec PROC_CALC_PROJECT_AMOUNT 100,1

五、尽量避免在WHERE条件中对字段使用函数。

这个是编程人员容易犯的错误。因为对字段使用函数后将无法使用到字段中的索引,降低了执行效率。比如查询所有2012年新建的项目,那么应该写成:

select *
from Project p
where p.CreateDate between ‘2012-1-1’ and ‘2012-12-31’;

而不要写成

select *
from Project p
where Year(p.CreateDate)=2013;

六、使用视图来抽象公共的查询部分。

在设计中提到使用配置表来把一些可能变化的查询条件放在数据库中,这样在需求更改时只修改数据库中的配置,而不用一个一个的改存储过程和SQL语句。另外还有一种方法就是使用视图来抽取公共查询的部分,将一些逻辑和条件放在视图中,然后其他存储过程和SQL直接使用视图,在需求发生变化时,我们只需要修改视图,其他的存储过程和SQL都不用修改。

七、小心查询时数据类型不匹配隐式转换导致的性能问题。

对于数据库中每个字段的类型不一定完全和其存储的值匹配。比如我们在设计员工表的员工号字段时,考虑到员工号不一定是个整数,所以设计成了varchar(10),但是在实际应用中所有员工号都是5位数的整数,那么我们可能在写查询时可能就直接把int类型的员工号传入进行查询。

八、公用表表达式CTE、临时表和表变量的使用。

CTE 可用于:

  • 创建递归查询。这个在树结构查询中常用。

  • 在不需要常规使用视图时替换视图,也就是说,不必将定义存储在元数据中。

  • 启用按从标量嵌套 select 语句派生的列进行分组,或者按不确定性函数或有外部访问的函数进行分组。

  • 在同一语句中多次引用生成的表。

临时表分为局部临时表#开头和全局临时表##开头。临时表可以建立索引,对于大数据量的临时存储时就使用临时表。

表变量适用于存储数据量不大的临时数据。表变量不可用创建索引。

运维

一、数据库操作必须脚本化并进行版本控制。

所有数据库的操作,包括前期的建表、初始化数据、建索引后期的增量修改和数据维护,都必须以SQL脚本来执行。这些脚本都保存到源代码管理中。这样方便于测试和部署。

二、数据库脚本应该能够重复执行。

在创建或者修改数据库对象时,先判断现有数据库中是否已经有这个对象,有的话就不再创建或者改为更新对象或者将原对象删除,重新创建。这样脚本可以重复执行,避免了环境不一致导致脚本在这个环境可以正常运行,在另外一个数据库却报错的情况。

三、在修改或删除数据时,先把原有的数据值SELECT出来并将结果保存在Log中。

系统上线后有可能因为用户操作的原因,也可能是系统的Bug,导致了错误数据的产生,那么就需要出维护脚本将这些错误的数据删除或者更新回来。对于delete和update类的维护脚本,需要在删除和修改之前先select出要修改的数据,维护人员将查询的结果保存到维护日志中,这样如果编写的维护脚本有问题,那么还可以根据维护日志看到原来的数据,将数据修复回来。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【SQL开发实战技巧】系列(二十八):数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。
12 0
【SQL开发实战技巧】系列(二十七):数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
本篇文章讲解的主要内容是:***通过执行计划看开窗函数开窗语法rows\range between preceding and current row以及rows\range between unbounded preceding and unbounded following对移动范围的值进行聚集的原理以及区别】、如何通过一个SQL打印九九乘法口表!!!***
54 0
【SQL开发实战技巧】系列(二十六):数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
本篇文章讲解的主要内容是:***ROLLUP、UNION ALL是如何分别做分组合计的以及如何通过CUBE 、GROUPING、GROUPING_ID 识别哪些行是做汇总的结果行***
15 0
【SQL开发实战技巧】系列(二十五):数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
本篇文章讲解的主要内容是:***如何使用lag函数让结果集重复数据只显示一次、用行转列pivot写法优化部门之间计算工资差异类似需求、如何通过ceil函数对已有数据进行分组打印、放假安排团队分组值班,如何通过ntile()over(order by )快速进行人员分组***
17 0
【SQL开发实战技巧】系列(二十四):数仓报表场景☞通过执行计划详解”行转列”,”列转行”是如何实现的
本篇文章讲解的主要内容是:***目前Oracle支持的行列互换有两种方式:case when、pivot\unpivot,我将通过几个案例来给大家详解如何通过这两种方式实现“行转列”,“列转行”的需求,并通过执行计划看case when、pivot\unpivot二者的底层逻辑关系以及效率上的影响。***
20 0
【SQL开发实战技巧】系列(二十三):数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
怎样对数据组合重新排列并去重的问题、通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。本篇文章主要介绍的两个方面,第一个方面曾经有好几个网友和同事问我,第二个问题真的是很多同行的通病,认为分析函数是万金油,一股脑用。
20 0
【SQL开发实战技巧】系列(二十二):数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
怎样对SQL查询结果集分页比较好、平时你用分析函数优化传统查询,所以你会不会认为分析函数一定比传统查询效率高?一个实验告诉你答案、我想对数据进行隔行抽样应该怎么实现?【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。分析查询的一个小建议,可能大家平时为了方便,用row_number做分页的比较多,但是在有些场景,这个效率真的挺低。
15 0
【SQL开发实战技巧】系列(二十一):数据仓库中时间类型操作(进阶)识别重叠的日期范围,按指定10分钟时间间隔汇总数据
如何识别重叠的日期范围、日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。例如:以上就是今天要讲的内容,本文仅仅简单介绍了pandas的使用,而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。
15 0
【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据
本篇文章讲解的主要内容是:***汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工***
15 0
【SQL开发实战技巧】系列(十九):数据仓库中时间类型操作(进阶)如何一个SQL打印当月或一年的日历?如何确定某月内第一个和最后—个周内某天的日期?
如何一个SQL打印出当月日历或当年日历???如何统计一年内属于周内某一天的所有日期???如何确定某月内第一个和最后—个周内某天的日期???【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。本例要求返回当月内第一个星期一与最后一个星期一,我们分别找上月末及当月末之前七天的下一周周一即可。
19 0
+关注
深蓝居
关注于区块链技术、跨链、密码学、通证经济、智能合约
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
阿里云流计算 Flink SQL 核心功能解密
立即下载
Comparison of Spark SQL with Hive
立即下载