SQL SERVER执行计划选择原理解决办法

SQL SERVER执行计划选择原理
一直遇到多张表进行连接后执行计划随着连接数目的变化而变化的现象，最初以为跟连接的数目有关，后来发现是不对的。求教老大。
一张大表（132W数据行），分别与4、5、6张纬度表相连，4张表时0-1秒，5张表时10-16秒，6张表时0-1秒。由于这几张表调用都很频繁，所以都在缓存中。
我使用了set statistics profile on和set statistics io on等选项进行了观察。由于外侧表都非常小，只有几行或是几行，个别表只有1行，所以大部分外侧表都没有建立索引，所以连接大多执行的是hash join。但是经过观察后并不是因为sql server选择hash join 而不是nested loop所造成的，而是其中一张只有一行的表坐在的缓存页被重复读了132W次。
示例如下：
5张表连接：

select count(1)
from Fact_test_MS_brand a11
inner join LU_region a16 on (a11.Region_id = a16.Region_id)
inner join LU_POPDB a15 on (a11.POPDB_id = a15.POPDB_id)
inner join LU_category a14 on (a11.Category_id = a14.Category_id)
inner join LU_brand a13 on (a11.Brand_id = a13.Brand_id)

Table 'LU_brand'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_category'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_POPDB'. Scan count 17, logical reads 428, physical reads 0, read-ahead reads 52, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_region'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact_test_MS_brand'. Scan count 17, logical reads 34933, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(19 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
  CPU time = 3019 ms, elapsed time = 313 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

--------------------------------------
6张表连接：

select count(1)
from Fact_test_MS_brand a11
inner join LU_region a16 on (a11.Region_id = a16.Region_id)
inner join LU_POPDB a15 on (a11.POPDB_id = a15.POPDB_id)
inner join LU_category a14 on (a11.Category_id = a14.Category_id)
inner join LU_brand a13 on (a11.Brand_id = a13.Brand_id)
inner join lu_city a12 on a11.city_id=a12.city_id

Table 'LU_POPDB'. Scan count 17, logical reads 428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_brand'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_city'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_region'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_category'. Scan count 16, logical reads 1327428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact_test_MS_brand'. Scan count 17, logical reads 34933, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(22 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
  CPU time = 123233 ms, elapsed time = 10659 ms.
SQL Server parse and compile time: 

  CPU time = 0 ms, elapsed time = 0 ms.

-------------------------------------
8张表连接：

select count(1)
from Fact_test_MS_brand a11
inner join LU_region a16 on (a11.Region_id = a16.Region_id)
inner join LU_POPDB a15 on (a11.POPDB_id = a15.POPDB_id)
inner join LU_category a14 on (a11.Category_id = a14.Category_id)
inner join LU_brand a13 on (a11.Brand_id = a13.Brand_id)
inner join lu_city a12 on a11.city_id=a12.city_id
inner join lu_chain a17 on a11.chain_id=a17.chain_id
inner join dbo.LU_area a18 on a11.area_id=a18.area_id

Table 'LU_POPDB'. Scan count 17, logical reads 428, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_brand'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_chain'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_area'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_category'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_city'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LU_region'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fact_test_MS_brand'. Scan count 17, logical reads 34933, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(28 row(s) affected)

(1 row(s) affected)

SQL Server Execution Times:
  CPU time = 6218 ms, elapsed time = 514 ms.
SQL Server parse and compile time: 
  CPU time = 0 ms, elapsed time = 0 ms.

-----------------------
注明：'LU_category'表只有1行数据。
可以明显看出，在6-7张表连接时，'LU_category'表的页被重复读了内侧表的行数，其他时候都只有1次。


各位请不要在反复贴关于join的3种连接原理的帖子，我都看过了，请谈谈自己的理解。
恳请老大指点，谢谢。


[解决办法]


[解决办法]
看看查询优化方面的内容吧。主要是生成多个执行计划，然后计算每个计划所花费的资源，然后选择花费资源最小的那个
[解决办法]
关注...
[解决办法]
有时间再看。
[解决办法]
要搞清楚它的优化逻辑，还是比较难啊。可以看看数据库理论方面的内容，另外可以看看sql server2005技术内幕一套书 Inside SQL Server 2005: Query Tuning and Optimization 这一本，还有胡百敬写的一本sql server2005性能调教
[解决办法]
微软的执行计划也有算不对的时候.. 这个是比较郁闷的事
[解决办法]
这是高难度研究.精神值得鼓励.
[解决办法]
>>而是其中一张只有一行的表坐在的缓存页被重复读了132W次。

不是吧，这不成了子查询了？
[解决办法]
帮顶。
学习
------解决方案--------------------

.

探讨

帮顶。
学习

[解决办法]
抛开影响执行计划选择的一些众所周知的因素，比如统计值、索引等等，我觉得在大数据量的多表

联接时，影响SQL Server优化器做出选择的最重要的因素就是CPU的数量.往往这个时候的执行计划成

本都很高，SQL Server一般都会启用并行的执行计划,会尽可能的考虑多CPU并行执行。然而这也是最

复杂的部份，优化器可能会错误的预估成本，从而可能会选择一个它认为是最优的执行计划。


楼主可以在单CPU的环境下做一些测试，我想上述几条语句的执行计划应该不会有太大的差异。

[解决办法]
再帖出执行计划看看.

[解决办法]

引用 20 楼 回复:

另外，我对表 'LU_POPDB'增加了一个聚集索引后，5个标连接没有问题，但是在8张表连接时又出现了这种问题。

[解决办法]
学习
[解决办法]
楼主的查询没什么特别，很简单啊。
除了a11，在所有其他a1x 表与a11表关联的字段上建index。
例如：针对a18
[code=sql]
create index idx_Area_id on dbo.LU_area(Area_id)
[/code]

如果index可以唯一加上unique。
[解决办法]
你想要多快？1s还是5s？
目前来看是不可能的，查询都是关联的条件，没有其他的限定条件。
Fact_test_MS_brand表的数据量也不小，而且肯定要被全部扫描一遍

如果有建index，查询基本上会选择NL，基本没有memory的开销，cpu的开销会可能大一点。
如果没有index 查询基本会选择hash，逻辑i/o会小一点，但是memory开销会大很多。


或者换一个思路：
a1x表上关联的字段可以不建index
在a11表上关联的字段都建上index
[解决办法]
忘了问了，楼主的测试环境是什么？
不同版本SQL Server，查询优化器的优化策略会有不同的考虑点。
[解决办法]
1.
12楼查询慢的原因：
a)并行计划
b)计划中有merge join
如果可以重建12楼查询的环境，使用option(hash join)查询提示
效率会有一些提升。

2.对于这个查询，用hash要比NL要更好一点，考虑对查询使用option(hash join)查询提示

3.

探讨

本机：  windows XP SP3 +sql server 2005 DE SP3
服务器：windows server 2003 SE SP2 + sql server 2005 SE SP3

之前我尝试过，在a11表上个别关联的字段（比如popdb_id）都建上index,结果查询计划中看到对a11仍然是Table Scan，没有使用索引。

[解决办法]
仔细看了一下20楼的执行计划，想请问一下

Fact_test_MS_brand.Category_id的数据分布情况是怎样的?

Fact_test_MS_brand.Category_id=LU_category.Category_id的数据有多少笔? 

Fact_test_MS_brand.Category_id总共有多少不同的值?



[解决办法]
1.
LU_category 逻辑读取次数高就是因为它是NL的内部访问的表。
所以现在要努力的目标就是减少LU_category逻辑读取次数，要么让它作为NL的外部表，要么就不用NL，用hash join

2.楼主在查询结束后，比较实际和估计的计划看是否有个别操作符有较大的误差？

3.个人认为，即使LU_category的逻辑i/o次数大，对查询性能有影响，但是还是可以接受的。

4.
如果LU_category能确保只有一条记录，那么对于这个查询
试着这样修改:
select .... from ..... --这里不要再关联LU_category 
where exists(select * from LU_category as a14 where a11.Category_id = a14.Category_id)

是否可以避免LU_category 作为NL的内部表来访问。



[解决办法]
基于以下事实：
1. Fact_test_MS_brand表数据量较大。
2. LU_*这些表数据量很小。
3. 查询都是Fact_test_MS_brand与LU_*关联，没有其他的谓词,并且Fact_test_MS_brand表
在关联字段上的查询选择性又较低（即字段的数据密较大）。
4. 这个查询的结果值：select count(1) ....如果很大，接近Fact_test_MS_brand表的总记录
数或还大于Fact_test_MS_brand的总记录数，同样说明了Fact_test_MS_brand表关联字段的
数据密度值是比较大的。
5. 第3点加粗部分注定了如果查询计划中出现2个或2个以上NL操作时，那么一定出现LU_*表是
NL的内部访问表的情况，自然就会出现大量的逻辑I/O。

那么结论就是：对于这样的查询用HASH JOIN要比用NL要有效率。



[解决办法]
早给你说了，研究也没有用，都是黑盒，换台高档服务器，可能执行计划立刻就不一样了，呵呵
执行计划跟太多东西有关了，比如cpu、内存、硬盘空间、数据文件的分布、数据量大小、数据密度、统计信息及时更新

还有 sql server 误算成本等等.
[解决办法]
补充2点：
1.因为Fact_test_MS_brand和LU_*数据量悬殊很大，SQL Server很容易就可以决定让LU_*表作为HJ的组建输入，
这样查询所要消耗memroy很少。如11楼的查询计划所示。
2.当SQL Server会选择用NL，并将LU_*作为内部输入表，而不是外部输入表。
可以检查一下查询计划，如20楼8张表联接的查询计划，加粗部分操作符的Estimated Number of Rows是否偏小，
或是和Actual Number of Rows相差较大

select count(1) from Fact_test_MS_brand a11 inner join LU_region a16 on (a11.Region_id = a16.Region_id) inner join LU_POPDB a15 on (a11.POPDB_id = a15.POPDB_id) inner join LU_category a14 on (a11.Category_id = a14.Category_id) inner join LU_brand a13 on (a11.Brand_id = a13.Brand_id) inner join lu_city a12 on a11.city_id=a12.city_id inner join lu_chain a17 on a11.chain_id=a17.chain_id inner join dbo.LU_area a18 on a11.area_id=a18.area_id 
|--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(int,[globalagg1025],0))) 
|--Stream Aggregate(DEFINE:([globalagg1025]=SUM([partialagg1024]))) 
|--Hash Match(Inner Join, HASH:([a17].[Chain_id])=([a11].[Chain_id]), RESIDUAL:([TPMMonitors].[dbo].[Fact_test_MS_brand].[Chain_id] as [a11].[Chain_id]=[TPMMonitors].[dbo].[LU_chain].[Chain_id] as [a17].[Chain_id])) 
|--Table Scan(OBJECT:([TPMMonitors].[dbo].[LU_chain] AS [a17])) 
|--Hash Match(Aggregate, HASH:([a11].[Chain_id]), RESIDUAL:([TPMMonitors].[dbo].[Fact_test_MS_brand].[Chain_id] as [a11].[Chain_id] = [TPMMonitors].[dbo].[Fact_test_MS_brand].[Chain_id] as [a11].[Chain_id]) DEFINE:([partialagg1024]=COUNT(*))) 
|--Hash Match(Inner Join, HASH:([a15].[POPDB_id])=([a11].[POPDB_id])) 
|--Clustered Index Scan(OBJECT:([TPMMonitors].[dbo].[LU_POPDB].[PK_LU_POPDB] AS [a15])) 
|--Nested Loops(Inner Join, WHERE:([TPMMonitors].[dbo].[Fact_test_MS_brand].[Category_id] as [a11].[Category_id]=[TPMMonitors].[dbo].[LU_category].[Category_id] as [a14].[Category_id])) 
|--Hash Match(Inner Join, HASH:([a12].[City_id])=([a11].[City_id]), RESIDUAL:([TPMMonitors].[dbo].[Fact_test_MS_brand].[City_id] as [a11].[City_id]=[TPMMonitors].[dbo].[LU_city].[City_id] as [a12].[City_id])) 
| |--Table Scan(OBJECT:([TPMMonitors].[dbo].[LU_city] AS [a12])) 
| |--Hash Match(Inner Join, HASH:([a16].[Region_id])=([a11].[Region_id]), RESIDUAL:([TPMMonitors].[dbo].[Fact_test_MS_brand].[Region_id] as [a11].[Region_id]=[TPMMonitors].[dbo].[LU_region].[Region_id] as [a16].[Region_id])) 
| |--Table Scan(OBJECT:([TPMMonitors].[dbo].[LU_region] AS [a16])) 
| |--Hash Match(Inner Join, HASH:([a18].[Area_id])=([a11].[Area_id]), RESIDUAL:([TPMMonitors].[dbo].[Fact_test_MS_brand].[Area_id] as [a11].[Area_id]=[TPMMonitors].[dbo].[LU_area].[Area_id] as [a18].[Area_id])) 
| |--Table Scan(OBJECT:([TPMMonitors].[dbo].[LU_area] AS [a18])) 
| |--Hash Match(Inner Join, HASH:([a13].[Brand_id])=([a11].[Brand_id]), RESIDUAL:([TPMMonitors].[dbo].[Fact_test_MS_brand].[Brand_id] as [a11].[Brand_id]=[TPMMonitors].[dbo].[LU_brand].[Brand_id] as [a13].[Brand_id])) 
| |--Table Scan(OBJECT:([TPMMonitors].[dbo].[LU_brand] AS [a13])) 
| |--Table Scan(OBJECT:([TPMMonitors].[dbo].[Fact_test_MS_brand] AS [a11])) 
|--Table Scan(OBJECT:([TPMMonitors].[dbo].[LU_category] AS [a14]))
[解决办法]
这个问题讨论到此也差不多了，再继续个人认为意义不大。而从这个例子我们只要能明白以下几点就可以了：

1.
在某些场景中，NL带来的大量的内部表index scan(seek)操作，性能上不如HJ一次Table(CLustered)scan来的
有效率，毕竟后者可以大大减少逻辑I/O的数量，其实也就是在降低cpu的消耗。而在一些其他的
场景情况却是相反的，HJ在减少逻辑I/O的同时，消耗的CPU却变的大了，原因可能是两种逻辑I/O数相差的不是那么的
"大"。

SQL code

SET STATISTICS TIME ONSET STATISTICS IO ONGOUSE NorthwindGOSELECT * FROM dbo.[Orders]     INNER LOOP JOIN dbo.[Order Details]        ON dbo.Orders.OrderID = dbo.[Order Details].OrderIDSELECT * FROM dbo.[Orders]     INNER HASH JOIN dbo.[Order Details]        ON dbo.Orders.OrderID = dbo.[Order Details].OrderIDGO 

 

查看更多 下一篇