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

目录
相关文章
|
1月前
|
SQL 数据库 数据安全/隐私保护
数据库数据恢复——sql server数据库被加密的数据恢复案例
SQL server数据库数据故障: SQL server数据库被加密,无法使用。 数据库MDF、LDF、log日志文件名字被篡改。 数据库备份被加密,文件名字被篡改。
|
26天前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
26天前
|
关系型数据库 MySQL 大数据
大数据新视界--大数据大厂之MySQL 数据库课程设计:MySQL 数据库 SQL 语句调优的进阶策略与实际案例(2-2)
本文延续前篇,深入探讨 MySQL 数据库 SQL 语句调优进阶策略。包括优化索引使用,介绍多种索引类型及避免索引失效等;调整数据库参数,如缓冲池、连接数和日志参数;还有分区表、垂直拆分等其他优化方法。通过实际案例分析展示调优效果。回顾与数据库课程设计相关文章,强调全面认识 MySQL 数据库重要性。为读者提供综合调优指导,确保数据库高效运行。
|
1月前
|
SQL 调度 数据库
开发YashanDB数据库?用 DBeaver for YashanDB 更顺手
数据库开发复杂易错,尤其在企业级场景中。为提升效率,YashanDB 团队基于 DBeaver 开源工具打造专属解决方案——DBeaver for YashanDB。它支持多类型数据库对象管理(表、视图、函数等),适配 YashanDB 特有表结构(HEAP、LSC),提供智能补全、语法高亮、SQL 调试等功能,让开发更高效流畅。推荐用于数据库应用开发团队、高频调试用户及中大型企业统一工具栈场景。
|
1月前
|
SQL 数据可视化 IDE
开发数据库不想写命令?YashanDB Developer Center 帮你轻松搞定
YashanDB Developer Center(YDC)是一款可视化的数据库开发工具,专为提升数据库开发效率而设计。它通过图形化对象管理让数据库对象清晰可见,提供智能SQL编辑器支持语法高亮与自动补全,实现PL调试的图形化操作,帮助快速定位问题。此外,操作记录可追溯,多端灵活部署,适配多种场景。无论是中大型企业研发团队,还是不熟悉命令行的业务开发者,YDC都能显著优化开发体验,堪称YashanDB的“可视化IDE”。
|
2月前
|
SQL 数据库连接 Linux
数据库编程:在PHP环境下使用SQL Server的方法。
看看你吧,就像一个调皮的小丑鱼在一片广阔的数据库海洋中游弋,一路上吞下大小数据如同海中的珍珠。不管有多少难关,只要记住这个流程,剩下的就只是探索未知的乐趣,沉浸在这个充满挑战的数据库海洋中。
62 16
|
2月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
22天前
|
SQL IDE 关系型数据库
JetBrains DataGrip 2025.1 发布 - 数据库和 SQL 跨平台 IDE
JetBrains DataGrip 2025.1 (macOS, Linux, Windows) - 数据库和 SQL 跨平台 IDE
92 0
|
3月前
|
SQL 数据库
数据库数据恢复—SQL Server报错“错误 823”的数据恢复案例
SQL Server数据库附加数据库过程中比较常见的报错是“错误 823”,附加数据库失败。 如果数据库有备份则只需还原备份即可。但是如果没有备份,备份时间太久,或者其他原因导致备份不可用,那么就需要通过专业手段对数据库进行数据恢复。
|
3月前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
122 9

热门文章

最新文章