数据库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出要修改的数据,维护人员将查询的结果保存到维护日志中,这样如果编写的维护脚本有问题,那么还可以根据维护日志看到原来的数据,将数据修复回来。

目录
相关文章
|
27天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
8天前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
6天前
|
SQL 监控 安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制,旨在检测潜在威胁和漏洞,监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息,帮助管理员追踪数据库中的特定活动,确保数据安全和合规性。SQL Server审核分为服务器级和数据库级,涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警,帮助快速响应安全事件。
|
17天前
|
SQL 关系型数据库 MySQL
体验使用DAS实现数据库SQL优化,完成任务可得羊羔绒加厚坐垫!
本实验介绍如何通过数据库自治服务DAS对RDS MySQL高可用实例进行SQL优化,包含购买RDS实例并创建数据库、数据导入、生成并优化慢SQL、执行优化后的SQL语句等实验步骤。完成任务,即可领取羊羔绒加厚坐垫,限量500个,先到先得。
|
14天前
|
SQL 存储 BI
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
|
14天前
|
SQL 数据库
gbase 8a 数据库 SQL优化案例-关联顺序优化
gbase 8a 数据库 SQL优化案例-关联顺序优化
|
18天前
|
SQL 存储 Linux
从配置源到数据库初始化一步步教你在CentOS 7.9上安装SQL Server 2019
【11月更文挑战第16天】本文介绍了在 CentOS 7.9 上安装 SQL Server 2019 的详细步骤,包括配置系统源、安装 SQL Server 2019 软件包以及数据库初始化,确保 SQL Server 正常运行。
|
28天前
|
存储 SQL API
探索后端开发:构建高效API与数据库交互
【10月更文挑战第36天】在数字化时代,后端开发是连接用户界面和数据存储的桥梁。本文深入探讨如何设计高效的API以及如何实现API与数据库之间的无缝交互,确保数据的一致性和高性能。我们将从基础概念出发,逐步深入到实战技巧,为读者提供一个清晰的后端开发路线图。
|
25天前
|
存储 缓存 NoSQL
2款使用.NET开发的数据库系统
2款使用.NET开发的数据库系统
|
29天前
|
存储 SQL 数据库
深入浅出后端开发之数据库优化实战
【10月更文挑战第35天】在软件开发的世界里,数据库性能直接关系到应用的响应速度和用户体验。本文将带你了解如何通过合理的索引设计、查询优化以及恰当的数据存储策略来提升数据库性能。我们将一起探索这些技巧背后的原理,并通过实际案例感受优化带来的显著效果。
41 4

热门文章

最新文章