理解SQL Server的SQL查询计划(1)
来源：互联网 发布：经典英文歌知乎 编辑：程序博客网 时间：2024/05/18 13:08
入门指南 让我们以一个简单的例子帮助你理解如何阅读查询计划，可以通过发出SET SHOWPLAN_TEXT On命令，或者在SQL Query Analyzer 的配置属性中设置同样的选项等方式得到查询计划。 注意：这个例子使用了表pubs.big_sales，该表与pubs..sales表完全相同，除了多了80000行的记录，以当作简单explain plan例子的主要数据。 如下所示，这个最简单的查询将扫描整个聚集索引，如果该索引存在。注意聚集键值是物理次序，数据按该次序存放。所以，如果聚集键值存在，你将可能避免对整个表进行扫描。即使你所选的列不在聚集键值中，例如ord_date，这个查询引擎将用索引扫描并返回结果集。 SELECT * SELECT ord_date StmtText 上面的查询展示返回的数据量非常不同，所以小结果集(ord_date)的查询比其它查询运行更快，这只是因为存在大量底层的I/O。然而，这两个查询计划实际上是一样的。你可以通过使用其它索引提高性能。例如，在title_id列上有一个非聚集索引存在： SELECT title_id StmtText 上面的查询的执行时间与SELECT *查询相比非常小，这是因为可以从非聚集索引即可得到所有结果。该类查询被称为covering query（覆盖查询），因为全部结果集被一个非聚集索引所覆盖。 SEEK与SCAN 第一件事是你需要在查询计划中区别SEEK和SCAN操作的不同。 注意：一个简单但非常有用的规则是SEEK操作是有效率的，而SCAN操作即使不是非常差，其效率也不是很好。SEEK操作是直接的，或者至少是快速的，而SCAN操作需要对整个对象进行读取（表，聚集索引或非聚集索引）。因此，SCAN操作通常比SEEK要消耗更多的资源。如果你的查询计划仅是扫描操作，你就应该考虑调整你的查询了。 where子句在查询性能中能产生巨大的差异，如下面展示的： Select * StmtText SEEK: ([big_sales].[stor_id]= ORDERED FORWARD) 上面的查询是在聚集索引上执行SEEK而不是SCAN操作。这个SHOWPLAN确切的描述SEEK操作是基于stor_id并且结果是按照在索引中存储的顺序排序的。因为SQL Server支持索引的向前和向后滚动的性能是相同的，所以你可以在查询计划中看到ORDERED FORWARD 或ORDERED BACKWARD。这只是告诉你表或索引读取的方向。你甚至可以在ORDER BY子句中通过用ASC和DESC关键字操作这些行为。范围查询返回的查询计划，与前面的直接查询的查询计划很相似。下面两个范围查询可提供一些信息： Select * StmtText SEEK: ([big_sales].[stor_id]>=’7131’) ORDER FORWARD 上面的查询看起来很象以前的例子，除了SEEK谓词有点不同。 Select * StmtText SEEK:([big_sales].[stor_id]>=’7066’ and ([big_sales].[stor_id]<=’7131’) ORDER FORWARD) 这个看起来也一样。只是查找谓词改变了。因为查找是非常快的，所以这个查询是相当好的。 SEEK和SCAN也可包含Where谓词。在这种情况下，这个谓词告诉你Where子句从结果集中过滤出哪些记录。因为它是作为SEEK或SCAN的一个组件执行的， Where子句通常既不损害也不提高这个操作本身的性能。Where子句会帮助查询优化器找到可能有最佳性能的索引。 查询优化的一个重要部分是要确定是否在某个索引上执行SEEK操作，如果是这样，就找到了具有最佳性能的索引。大部分情况下，查询引擎能出色地查找到存在的索引。但是，目前有三种涉及到索引的常见问题： ◆数据库设计师，通常是应用开发者，在表中没有建立任何索引。 如果你在你的查询计划中看到大量的SCAN而不是SEEK，你应该从新评估你的索引。例如，看看下面的查询： Select ord_num WHERE : ([sales].[ord_date]>’Jan 1,2002 12:00:00 AM ’)) 现在这个查询在我们刚创建的sales_ord_date索引上执行SEEK INDEX操作。 通过比较连接和子查询说明分支步骤 一条正确的老规则是：在结果集相同的情况下，连接比子查询具有更好的性能。 SELECT au_fname,au_lname StmtText SEEK:([authors].[au_id]=[titleauthor].[au_id]) ORDERED FORWARD) Table ‘authors’. Scan count 38,logical reads 76,physical reads 0,read-ahead reads 0. Table ‘titleauthor’. Scan count 2, logical reads 2, physical reads 1,read-ahead reads 0. 在这种情况下，查询引擎选择一个嵌套循环操作。这个查询被迫用聚集索引读取整个authors表，在处理中执行大量的逻辑页读。 注意：在带分支步骤的查询中，缩进行给你展示那些步骤是其它步骤的分支。 Select distinct au_fname,au_lname StmtText |--stream Aggregate(group by: ([a].[au_lname].[a].[au_fname])) SEEK: ([t].[au_id]=[a].[au_id]) ORDER FORWARD) physical reads 0,read ahead reads 0. Table ‘authors’ .Scan count 1,logical reads 1,physical reads 0,read-ahead read 0. 上面的这个查询中，titleauthors表逻辑读的数字上升而authors表下降。注意到，stream aggregation在查询计划中位置更高，即发生的更晚。<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
FROM big_sales
FROM big_sales
-------------------------------------------------------------------------
|--ClusteredIndexScan(OBJECT:([pubs].[dbo].[big_sales].[UPKCL_big_sales]))
FROM big_sales
------------------------------------------------------------------
|--Index Scan(OBJECT:([pubs].[dbo].[big_sales].[ndx_sales_ttlID]))
From big_sales
Where stor_id=’6380’
-----------------------------------------------------------------------------|--Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales])),
From big_sales
Where stor_id>=’7131’
------------------------------------------------------------------------------|-Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),
From big_sales
Where stor_id between ‘7066’ and ‘7131’
------------------------------------------------------------------------------|-Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),
◆数据库设计师通常猜测不到常用的查询或事务类型，所以建立在表上的索引或主键往往效率不高。
◆当索引表被创建时，即使数据库设计师猜测较准，但事务负载随着时间将发生改变，使得这些索引效率变差。
From sales
Where ord_date IS NOT NUL
And ord_date>’Jan 01,2002 12:00:00 AM’
StemtText
----------------------------------------------------------------------------------|--
Clustered Index Scan(OBJECT: ([pubs].[dbo].[sales].[UPKCL_sales] ),
FROM authors
WHERE au_id IN (select au_id from titleauther )
-------------------------------------------------------------------------------------------
|---Nested Loops(Inner Join, OUTER ReFERENCES:([titleauthor].[au_id])
|--Stream Aggregate(GROUP BY:([titleauthor].[au_id]))
| |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[auidind]),ORDERED FORWARD)
|--ClusteredIndex Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
From authors as a
Join titleauthor as t ON a.au_id=t.au_id
---------------------------------------------------------------------------------
|-Nested loops(Inner Join,OUTER REFERENCES: ([a].[au_id]))
|-Index scan(OBJECT:([pubs].[dbo].[authors].[authord ]as[a]),ordered forward)
|-Index Seek (OBJECT: [pubs].[dbo].[titleauthor].[authord ]as [t]),
Table ‘titleauthors’ .Scan count 23,logical reads 23,
