应用程序 sql监控_适用于应用程序开发人员SQL

最新推荐文章于 2021-08-04 20:37:41 发布

danpu0978

最新推荐文章于 2021-08-04 20:37:41 发布

阅读量152

点赞数

文章标签：数据库大数据编程语言 python 人工智能

原文链接：https://www.javacodegeeks.com/2016/06/sql-application-developers.html

版权

应用程序 sql监控

“ SQL？这不是我的ORM工具所使用的吗？” 应用程序开发人员通常可以并且应该至少保持从SQL删除一级的权限。 JPA和Entity Framework各自提供了更多抽象（且面向OO友好）的查询语言，通常根本不需要编写任何查询。

但是，如果您曾经诊断过生产支持问题，那么精通SQL可以极大地提高您的生产率。（逐表浏览数据和逐记录记录可能会很缓慢且乏味；您可能不想每次出现新问题时都编写一堆一次性的测试应用程序。）

即使使用ORM工具的查询语言，也建议至少了解SQL的基础知识。对SQL连接语义的深入了解可以消除某些行为的神秘性，否则这些行为似乎会违反直觉，并具有严重的恶意性。分析您的ORM工具生成SQL的能力可能会为难以捉摸的错误提供有价值的见解。

有时，由于某种原因，您可能会发现自己不在一个使用ORM工具的项目中。诸如ETL和报告之类的任务并不总是适合于基于ORM的数据访问。也许您正在编写视图或存储过程。（不考虑狗狗大战，它们有其地位。）也许您已经继承了使用SQL的旧代码库，无论它是否是“正确的选择”。也许您使用的语言没有很好的ORM工具。

当然，并不是每个数据库都首先使用SQL。如果您更喜欢NoSQL解决方案，并且可以控制每个项目上的工具集，则可能永远不需要读取或编写一行SQL。本文适用于我们其他人。

有很多很好的资源可用于学习SQL语言。 我的重点是应用程序开发人员可能需要转变思维方式才能有效地使用SQL。

过程式与声明式编程

我将从技术上的区别入手，因为它可能具有最广泛的含义。

许多开发人员似乎最喜欢过程编程范例。粗略地讲，我的意思是他们写了指令列表，然后计算机逐步遵循这些指令。尽管您可能不认为Java和C＃是过程语言（因为它们支持OO模式以及某些声明性和功能性元素），但逐步指定算法的思想在这两种语言中仍然很普遍。

在SQL查询中，流行的范例是声明式的。查询描述需要什么数据，DBMS的工作是决定提供数据所需的一系列步骤，即是使用索引还是直接访问相应的表；从查询中使用的表中读取的顺序；在构建最终结果集时使用哪些联合策略；等等

优化器是允许使用声明式方法的一部分。在Java或C＃中，我们不希望编译器过于文字化。优化器可以消除x = x + 1和x++之间的性能差异，因此我们编写我们认为使代码更易读的任何一种，然后继续进行。因此，即使我们按程序进行思考，我们也知道确切的执行步骤可能会有所不同。

另一方面，直到最近，数据库优化器还是相当有限的。你可能会写

select e.EMPLOYEE_ID
  from EMPLOYEE e
 where exists (select 1
                 from EMPLOYEE mgr
                where e.MANAGER_ID = mgr.EMPLOYEE_ID
                  and mgr.LAST_NAME = ‘Lister’
              );

否则你可能会写

select e.EMPLOYEE_ID
	  from EMPLOYEE e
	 where    e.EMPLOYEE_ID
	       in (select mgr.MANAGER_ID
                  from EMPLOYEE mgr
                 where mgr.LAST_NAME = ‘Lister’
          );

这些查询描述的是相同的结果，但是您可能会发现一个查询的运行速度比另一个查询快得多。甚至有可能在某些条件下运行得更快，而在其他条件下运行得慢。这可能会使您处于糟糕的境地，在这种情况下，您将部署“快速”查询，但是在迁移到新的DBMS或升级现有的DBMS之后，甚至只是随着时间的推移积累的变更之后，它会变成缓慢的查询。数据库中的数据。

幸运的是，查询优化器已经变得更好，主要是因为从基于规则的优化器（从您编写查询的方式获得线索，有时甚至依赖于嵌入到查询中的“提示”）转向基于成本的优化器（它们使用有关数据的统计数据来预测一组可能的执行计划中的每一个所需的“成本”（大致意味着时间））。

以前，您可能已经比较了上面两个查询的执行计划，以确定应该使用哪个查询。如今，您仍然可以检查执行计划以诊断运行缓慢的查询，但这与检查优化器的使用原因有关，而不是对步骤的实际顺序进行第二次猜测。（它是否认为联接只会产生少量记录，而实际上却产生许多记录呢？也许需要更新统计信息，或者您合法地发现了DBMS优化器的局限性。）我不期望现代的数据库，以照顾您使用上面哪个查询。

结果是您通常应该以声明性的方式来考虑SQL。当您逐步组装查询逻辑时，不是在找出代码的末尾“下一步”，而是在寻找一种方法来编辑查询，使其更接近所需的结果。

一个例子

假设您需要回答以下问题：“平均薪资高于$ 50,000的所有部门的名称是什么？” （除了可以从以下代码中推断出的内容之外，我们无需担心数据模型或其他详细的假设；我们实际上只关心这里的概念。）

现在，如果您以程序化的心态去做该怎么办？以下是一个极端（简化）的示例，但是我已经看到了：

1）我需要从EMPLOYEE表中获取与部门相关的薪水数据。

select e.SALARY
     , e.DEPARTMENT_ID
  from EMPLOYEE e;

2）然后我需要按部门平均工资。

with
  salaries as (
    select e.SALARY, e.DEPARTMENT_ID
      from EMPLOYEE e
  )
select s.DEPARTMENT_ID
     , avg(s.SALARY)
  from salaries s
 group by s.DEPARTMENT_ID;

3）接下来，我需要筛选出平均工资低于（或低于）临界值的部门。

with
  salaries as (
    select e.SALARY, e.DEPARTMENT_ID
      from EMPLOYEE e
  )
, avg_salaries as (
    select s.DEPARTMENT_ID, avg(s.SALARY) AVG_SALARY
      from salaries s
     group by s.DEPARTMENT_ID
  )
select a.DEPARTMENT_ID
  from avg_salaries a
 where a > 50000;

4）最后，我需要获取部门名称。

with
  salaries as (
    select e.SALARY, e.DEPARTMENT_ID
      from EMPLOYEE e
  )
, avg_salaries as (
    select s.DEPARTMENT_ID, avg(s.SALARY) AVG_SALARY
      from salaries s
     group by s.DEPARTMENT_ID
  )
, department_list as (
   select a.DEPARTMENT_ID
     from avg_salaries a
    where a.AVG_SALARY > 50000
  )
select d.DEPARTMENT_NAME
  from DEPARTMENT d
 where    d.DEPARTMENT_ID
       in (select DEPARTMENT_ID from department_list);

哇，SQL必须很复杂，是吗？这是一个很长的混乱查询，不需要太多。这将很难阅读和维护。告诉你真相，如果不运行它，我不确定它是否正确。

让我们以声明式的心态再试一次。而不是思考“下一步是什么？” 我们会认为“我该如何更改结果集？”

1）我需要从EMPLOYEE表中获取与部门相关的薪水数据…

select e.SALARY
     , e.DEPARTMENT_ID
  from EMPLOYEE e;

2） …但是我需要按部门平均工资...

select e.DEPARTMENT_ID
     , avg(e.SALARY)
  from EMPLOYEE e
 group by e.DEPARTMENT_ID;

3） …而且我需要筛选出平均工资低于（或低于）临界值的部门……

select e.DEPARTMENT_ID
  from EMPLOYEE e
 group by e.DEPARTMENT_ID
having avg(e.SALARY) > 50000;

4） …，我需要知道部门的名称。

select d.DEPARTMENT_NAME
  from DEPARTMENT d
 where    d.DEPARTMENT_ID
       in (select e.DEPARTMENT_ID
             from EMPLOYEE e
            group by e.DEPARTMENT_ID
           having avg(e.SALARY) > 50000);

使用起来相当容易。同样重要的是，因为我没有以要求每个步骤都可以在下一步开始之前完成的方式编写查询，所以优化程序可能具有更大的灵活性来提出有效的执行计划。

公平地说，我不能说此版本的查询消除了所有程序思想。使用子查询建议将EMPLOYEE数据进行分组和过滤，然后再与DEPARTMENT数据进行比较。我可以写：

select d.DEPARTMENT_NAME
	  from            DEPARTMENT d
	       inner join EMPLOYEE e
                     on e.DEPARTMENT_ID = d.DEPARTMENT_ID
	 group by d.DEPARTMENT_ID, d.DEPARTMENT_NAME
	having avg(e.SALARY > 50000);

（请注意，我已将DEPARTMENT_NAME添加到group by子句中；由于我们已经按DEPARTMENT_ID了分组，这没关系，但是它允许我们在select子句中包含DEPARTMENT_ID 。另一种选择是使用免费聚合函数，例如select max(d.DEPARTMENT_NAME) 。我认为这是一种样式选择；我认为任何一种都很好，除非您发现您的特定DBMS比另一种DBMS处理得更好。）

从100％的声明性角度来看，我可以为这种形式的查询辩护。从理论上讲，由于联接密钥包含在分组密钥中，因此优化程序仍可以选择在联接之前执行聚合（可能会节省大量I / O，这将在以下内容中进行介绍）是性能驱动因素SQL查询）；但这是我仍然不希望优化器解决的问题。

I / O是关键性能驱动力

在大学的计算机体系结构课程中，我们编写了MIPS汇编语言程序，以在名为SPIM的模拟器上运行。在这种过于简化的环境中，性能的主要单位是指令。您可以学习玩一个反常的游戏，由于某种原因，您永远无法向同学解释，以这种方式编写代码比以明显的方式编写代码要好一些，因为这样可以节省三个时钟周期。

事实证明，计算机在该特定游戏上非常出色，这就是Java和C＃程序员不必如此的原因。当我们考虑性能时，我们可以自由地关注诸如“不要使用冒泡排序”之类的事情。由于我们有缓存，因此我们可能不必太担心如何使用硬盘。我们将寄存器分配留给编译器。

除了算法复杂度（避免气泡排序的原因）之外，最常见的性能问题可能是网络往返。与数据库交互时，这可能很重要（因为我们经常使用远程数据库服务器）。但是，一旦查询到数据库，什么决定了数据库的性能如何？

答案是：I / O。

从一个DBMS到另一个DBMS，细节有所不同，但通常，您希望最大程度地减少从磁盘读取（或写入）的数据块数量。如上所述，通常将其留给查询优化器。（您的DBA还通过确保为您的环境定义了正确的索引，统计信息等来发挥作用，以使优化器可以完成其工作。这是物理数据建模的一部分。）

您可能听说过，数据库中最昂贵的操作是join操作。这可能是一个很好的经验法则，但很容易造成误解。上面的几个示例使用子查询将一个表中的数据与另一个表中的数据进行比较。即使我们没有显式编写联接表达式，查询优化器也会选择一个涉及联接表的执行计划。（是否要说是否不联接表，实际上取决于您对单词的挑剔程度；最重要的是，您可以期望合并或比较两个表中的数据会产生成本。）

因此，请再次记住，您通常不应该猜测要采取的具体步骤-这意味着，如果优化程序决定进行联接，则可能有充分的理由，除非您绝对愿意，否则您应该犹豫尝试改变主意。确定这是一个错误–重点仅在于确保您不涉及不需要的数据。

I / O是性能驱动因素这一事实的另一个结果是，在大多数情况下，似乎密集处理的操作实际上并不是性能问题。 SQL支持诸如CASE表达式和窗口聚合函数之类的功能，一旦您理解它们，它们将非常强大。如果查询已经需要读取这些操作所需的所有输入数据，那么使用它们（在适当的情况下）可能不会降低速度。

但是请注意，我说“适当时”是因为……

仅仅因为你可以并不意味着你应该

我认为大多数人（当然包括我自己）都分两个阶段学习SQL。首先，您将学习可以在SQL中完成的所有很酷的事情。其次，您学会了不要做大多数的事情（或者至少要谨慎地做，只有有充分的理由）。

查询几乎始终是更广泛系统的一部分。您只想确保它发挥应有的作用。许多因素可能会限制您的查询应执行的操作：

驻留在数据访问层中的查询不应包含业务逻辑。

当ETL（或ELT或ETLT）过程从实时交易系统中读取时，您可能必须限制提取阶段对性能的影响。您可以使用复杂的提取查询来“预转换”数据（使转换阶段变得多余），但您可能不应该这样做。

报表定义通常围绕查询建立。但是体面的报告工具也将具有自己的过滤，分组/汇总，排序等功能。使用报告工具功能可以使报告更加灵活和互动。

有时可能需要进行复杂的查询。专用于报表应用程序的数据集市可能具有过剩的计算能力，如果复杂的聚合可以将流向报表客户端计算机的数据量减少一个数量级，那几乎肯定是性能上的胜利。

但是，即使如此，您还是要小心：根据DBMS处理并发和锁定的方式（可能还取决于事务隔离设置），复杂的查询可能会相互阻塞并抹去任何性能提升，或者更糟的是，导致死锁（充其量是死锁）将导致您的查询之一失败，必须重新启动）。

无论如何，如果您的默认思维方式是让查询执行所有操作，那么您可能会执行过多的查询。

另外，如果您必须编写复杂的查询，请记住…

格式化SQL与格式化Java或C＃不同

SQL是代码，就像其他任何代码一样，为了便于阅读，也需要对其进行格式化。无论出于何种原因，许多开发人员在遇到SQL块时似乎都“放弃”了格式化。也许他们不了解SQL格式化的良好标准；如果是这样，则值得学习。单行查询可能无关紧要，但是查询越复杂，对它进行格式化的重要性就越大。

我没有SQL格式化规则的完整列表；我相信重要的是找到一种适合您的样式。以下是一些我认为有帮助的规则。

我通常倾向于“两列”方法，在第一列中将主要子句关键字右对齐。（上面的示例使用这种通用方法。）

至少对于顶级查询， select子句中的每一列都有自己的行。逗号放在一行的开头，而不是结尾。（我将它们与select的t select 。）这使得在列表末尾添加/删除（或注释/取消注释）列更加容易。（无论出于何种原因，这似乎比能够添加，删除或注释列表中的第一列更大。）

类似地， from子句中的每个表都有自己的行。如果我使用逗号分隔的表列表，则逗号在行的开头（在from的m下）。如果使用的是ANSI连接符号，则使用如下缩进：

from                 FIRST_TABLE t1
	            inner join SECOND_TABLE t2
                    on t1.KEY1 = t2.KEY1
	       left outer join THIRD_TABLE t3
	                    on t2.KEY2 = t3.KEY2

（有时，如果from子句包含一个子查询，我会非常同意。）我不会在单个from子句中将逗号分隔与ANSI连接符号混合在一起。

我避免使用无意义的表别名。表别名就像变量名。具有有意义别名的查询更易于阅读。我几乎总是使用表别名，并且即使在查询中只有一个表，我也认为使用别名来限定列名是一种很好的做法。（如果以后再向查询中添加第二个表，则可以避免进行大量的编辑。）

我使用缩进来阐明子查询或CASE表达式的开始和结束位置。（在相关说明上，尽管从技术上讲不是格式问题，但我通常会尝试避免嵌套CASE语句。通常不需要嵌套CASE语句，而且更难阅读。请记住， WHEN子句按顺序求值，并且第一个匹配项获胜。不必担心在WHEN子句中使用AND或OR 。）

我希望我的查询可以在80列的终端中使用/读取，因此请确保避免让行变得更长。

如果您知道自己喜欢SQL样式指南，或者您认为其他规则有助于提高可读性，请随时在注释中进行讨论。

SQL逻辑本质上不是二进制的

SQL中的逻辑表达式有三个值： TRUE ， FALSE和NULL 。（任何数据类型的值都可以为NULL ；您必须注意，SQL的NULL与大多数语言中的null指针或null引用不太一样。）

从技术上讲， NULL表示“未知”。实际上，它经常与外键一起使用以表示“不存在相关实体”，但是各种SQL运算符的语义都将其解释为“未知”。因此，如果我有一个DEPARTMENT_ID为NULL的EMPLOYEE记录，那么如何评估谓词DEPARTMENT_ID = 37 ？由于不知道DEPARTMENT_ID值，因此我们不知道谓词为TRUE ， 但我们也不知道它为FALSE因为未知值可能只是37 ，因此我们将其评估为unknown（ NULL ）为好。

现在， where子句会过滤掉所有谓词不是TRUE ； CASE表达式的THEN子句仅匹配其值为TRUE记录。这就是为什么人们有时将NULL与FALSE混淆的原因。但是，如果将NOT运算符应用于值为NULL的谓词会发生什么？

正如既不知道DEPARTMENT_ID = 37也不知道它为假一样， DEPARTMENT_ID <> 37也未知， 而NOT (DEPARTMENT_ID = 37)也不是。 NULL的逻辑取反是NULL ，而不是TRUE 。

顺便说一句，这就是为什么对NULL值进行相等性测试不会执行您想要的操作的原因。谓词SOME_COLUMN = NULL将始终评估为NULL。因此，我们有IS NULL和IS NOT NULL 。

NULL逻辑值的另一个令人惊讶的行为是，如果将NOT IN应用于包含NULL值的列表。当DEPARTMENT_ID=37 ，您可能希望DEPARTMENT_ID NOT IN (42, 100, NULL)的计算结果为TRUE ；但是由于该NULL值未知， 因此可能为37，因此谓词本身的计算结果为NULL （并且在大多数情况下，其行为似乎是FALSE ，造成了很多混乱）。

了解您的DBMS

JavaScript开发人员可能对这最后一点非常熟悉。尽管SQL长期以来一直是标准，但不同的DBMS具有不同的方言。有些不能完全实现规范中支持的所有功能。有些具有扩展名。有些人仍然支持非标准的做某些事情的方法，而在标准包括做这些事情的方法之前，这些方法一直被搁置。它们几乎都具有自己的功能库，目录的布局（描述数据库中定义的表，视图和其他对象的模式）是特定于DBMS的。

缺乏实际的标准化对早期JavaScript开发人员造成了特别严重的影响，因为他们正在编写可在多个浏览器上运行的代码。使用SQL并非如此。您可能使用内存数据库进行测试，而生产环境中使用数据库服务器，但这甚至是相对受控的情况（并且您可能使用ORM工具来记录存在的差异）。

但这仍然值得注意：每当您开始使用新的DBMS时，您都希望熟悉其特定SQL怪癖。

加起来…

SQL与我们通常使用的大多数语言有很大的不同。随着时间的增长，可以在不同类型的环境中满足不同类型的需求。与任何可能不熟悉的工具一样，花时间学习使用它的最佳方法也是一项投资。某些问题变得越来越令人沮丧，并且您对团队的价值可以大大增加。

关于SQL的知识比我在这里要解决的要多得多。感受各种SQL构造以及如何组合它们以解决不同的问题需要花费时间和实践。而且我敢肯定，我还没有想到还有其他SQL怪癖，这使应用程序开发人员大吃一惊。尽管如此，我希望当需要编写一些有效且可维护的查询时，这些指针对您有所帮助。

翻译自: https://www.javacodegeeks.com/2016/06/sql-application-developers.html

应用程序 sql监控

danpu0978

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
应用程序 sql监控_适用于应用程序开发人员SQL

应用程序 sql监控 “ SQL？这不是我的ORM工具所使用的吗？” 应用程序开发人员通常可以并且应该至少保持从SQL删除一级的权限。 JPA和Entity Framework各自提供了更多抽象（且面向OO友好）的查询语言，通常根本不需要编写任何查询。但是，如果您曾经诊断过生产支持问题，那么精通SQL可以极大地提高您的生产率。（逐表浏览数据和逐记录记录可能会很缓慢且乏味；您可能不想每...
复制链接

扫一扫