Sql语句中同样实现关联和子查询选择策略

最新推荐文章于 2023-07-25 09:15:13 发布

Edwinlb

最新推荐文章于 2023-07-25 09:15:13 发布

阅读量4.4k

点赞数

分类专栏： Server 专柜文章标签： sql hibernate join 数据库测试优化

Server 专柜专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在项目中查询关于客户订单信息和客户所在帐户的信息中，根据订单号码和客户的会员号码，查询客户的订单的信息和客户所在公司的信息：

会员卡号码在会员表，会员表关联子帐户表，子帐户表关联主帐户表，主帐户关联公司信息表

订单表关联，酒店表，关联房型表等。

选择每位客户的所有订单非常简单；事实上任何 SQL 新手都可以在几分钟内构造出这个查询。然而，当你想在干草堆中找出一根针时，就需要一点窍门了。下面我将向你展示如何选择每位客户最新的订单，稍微改变一点语法还可以选择每位客户最老的订单。

这里最大的问题是相互关联子查询（correlated subqueries），相互关联子查询与嵌套子查询有很明显的区别。

在嵌套子查询中，内部查询只执行一次并返回它的值给外部查询，然后外部查询在它的处理中使用内部查询返回给它的值。

而在相互关联子查询中，对于外部查询返回的每一行数据，内部查询都要执行一次。另外，在相互关联子查询中是信息流是双向的。

外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策。

下面我们使用Northwind 数据库作为一个例子。我们需要列出由每位客户下达的最新的订单。

SELECT o1.CustomerID, o1.OrderID, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate =
(SELECT Max(OrderDate)
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID)

对于外部查询返回的每行数据，内部查询都会执行，条件是限制其结果集与CustomerID匹配。Max() 调用将结果集限制为感兴趣的一行数据。

如果有500位客户下达了订单，内部查询将执行500次

每个CustomerID执行一次。现在我们已经有了答案：返回90行数据，也就是说这90条数据中每条数据都代表一位客户下达了一个订单。

你可以很容易地对这个查询进行扩展。

例如，你可能想查询每个客户所下订单的最新两个订单。在这种情况下，需要将内部查询改为以下这种形式：

SELECT o1.CustomerID, o1.OrderID, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate IN(
SELECT TOP 2 o2.OrderDate
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID

你可以以不同方式优化这个例子，以避免开销很大的 IN() 谓词。

以我的经验来看，很多开发人员在学习完相互关联子查询之后很快就会忘记其概念。这真的很让人感到遗憾，因为相互关联子查询可以很简单很优雅地回答难度很大的问题。

我的意见是这个语法可行，并且用一个 SQL 语句就可以查询出期望的结果。这使我想到了一位开发经理反复跟我说的一句话：“第一个版本是使其能够运行，第二个版本是使其能够更快地运行。”

开始写的子查询语句实现如下：

                select c.corp_cnname from t_buss_corporation c where c.corp_no in
                (
                   select mainA.Corp_No from t_buss_main_account mainA where MainA.Main_Acnt_No in
                  (
                    select cAccount.Main_Acnt_No from t_Buss_Child_Account cAccount where cAccount.Child_Acnt_No in
                    (
                      select CA.CHILD_ACNT_NO from T_BUSS_CARD CA where CA.Memb_Card_No=9123456701
                    )
                  )
                )

改写后的SQL：使用关联查询如下：

                select c.corp_cnname from
                (
                    (T_BUSS_CARD CA inner join t_Buss_Child_Account childA on childA.Child_Acnt_No=CA.Child_Acnt_No)
                    inner join t_buss_main_account mainA on childA.Main_Acnt_No=mainA.Main_Acnt_No
                ) inner join t_buss_corporation c on c.corp_no=mainA.Corp_No
                 where CA.Memb_Card_No =9123456701

下面测试转自一位数据管理人员的测试结果：

SQL优化--使用关联查询代替子查询

测试例子：

子查询：

select a. * ,
( select workflowname from workflowbase where id = workflowid) workflowname
from [ [zping.com ] ]] a
where a.operator = ' 402882ed1112669201112a8385892f33 '

执行结果:

( 360 行受影响)
表 ' Worktable ' 。扫描计数 360 ，逻辑读取 142334 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ' workflowbase ' 。扫描计数 1 ，逻辑读取 1589 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ' [zping.com] ' 。扫描计数 1 ，逻辑读取 366 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

关联查询：

select a. * ,b.workflowname
from [ [zping.com ] ]] a inner join workflowbase b on a.workflowid = b.id
where operator = ' 402882ed1112669201112a8385892f33 '

执行结果：

( 360 行受影响)
表 ' Worktable ' 。扫描计数 0 ，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ' workflowbase ' 。扫描计数 1 ，逻辑读取 1589 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ' [zping.com] ' 。扫描计数 1 ，逻辑读取 366 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

这里：子查询IO次数：142334 +1589+366=144289

关联查询IO次数：1589 +366 =1922

关联查询是子查询的75倍

总结：

使用子查询和关联查询，一般情况下如果能用关联查询就不用子查询，

项目中采用Hibernate的运行缓慢，一面时我们对于Hibernate的API掌握的不够，另外一方面原因是因为他自动生产的子查询的原因可能也产生一部原因把。所以我自己写SQL时禁止使用子查询尽可能的使用关联查询，提供自己的查询效率。

以为数据库人员的blog，很值得去看的！

http://www.cnblogs.com/zping/