入门指南
让我们以一个简单的例子帮助你理解如何阅读查询计划,可以通过发出SET SHOWPLAN_TEXT On命令,或者在SQL Query Analyzer 的配置属性中设置同样的选项等方式得到查询计划。
注意:这个例子使用了表pubs.big_sales,该表与pubs..sales表完全相同,除了多了80000行的记录,以当作简单explain plan例子的主要数据。
如下所示,这个最简单的查询将扫描整个聚集索引,如果该索引存在。注意聚集键值是物理次序,数据按该次序存放。所以,如果聚集键值存在,你将可能避免对整个表进行扫描。即使你所选的列不在聚集键值中,例如ord_date,这个查询引擎将用索引扫描并返回结果集。
SELECT *
FROM big_salesSELECT ord_date
FROM big_salesStmtText
-------------------------------------------------------------------------
|--ClusteredIndexScan(OBJECT[pubs].[dbo].[big_sales].[UPKCL_big_sales]))
上面的查询展示返回的数据量非常不同,所以小结果集(ord_date)的查询比其它查询运行更快,这只是因为存在大量底层的I/O。然而,这两个查询计划实际上是一样的。你可以通过使用其它索引提高性能。例如,在title_id列上有一个非聚集索引存在:
SELECT title_id
FROM big_salesStmtText
------------------------------------------------------------------
|--Index Scan(OBJECT[pubs].[dbo].[big_sales].[ndx_sales_ttlID]))
上面的查询的执行时间与SELECT *查询相比非常小,这是因为可以从非聚集索引即可得到所有结果。该类查询被称为covering query(覆盖查询),因为全部结果集被一个非聚集索引所覆盖。
SEEK与SCAN
第一件事是你需要在查询计划中区别SEEK和SCAN操作的不同。
注意:一个简单但非常有用的规则是SEEK操作是有效率的,而SCAN操作即使不是非常差,其效率也不是很好。SEEK操作是直接的,或者至少是快速的,而SCAN操作需要对整个对象进行读取(表,聚集索引或非聚集索引)。因此,SCAN操作通常比SEEK要消耗更多的资源。如果你的查询计划仅是扫描操作,你就应该考虑调整你的查询了。
where子句在查询性能中能产生巨大的差异,如下面展示的:
Select *
From big_sales
Where stor_id=’6380’StmtText
-----------------------------------------------------------------------------|--Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales])),SEEK: ([big_sales].[stor_id]={@1} ORDERED FORWARD)
上面的查询是在聚集索引上执行SEEK而不是SCAN操作。这个SHOWPLAN确切的描述SEEK操作是基于stor_id并且结果是按照在索引中 存储的顺序排序的。因为 SQL Server支持索引的向前和向后滚动的性能是相同的,所以你可以在查询计划中看到ORDERED FORWARD 或ORDERED BACKWARD。这只是告诉你表或索引读取的方向。你甚至可以在ORDER BY子句中通过用ASC和DESC关键字操作这些行为。范围查询返回的查询计划,与前面的直接查询的查询计划很相似。下面两个范围查询可提供一些信息:
Select *
From big_sales
Where stor_id>=’7131’StmtText
------------------------------------------------------------------------------|-Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),SEEK: ([big_sales].[stor_id]>=’7131’) ORDER FORWARD
上面的查询看起来很象以前的例子,除了SEEK谓词有点不同。
Select *
From big_sales
Where stor_id between ‘7066’ and ‘7131’StmtText
------------------------------------------------------------------------------|-Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),SEEK[big_sales].[stor_id]>=’7066’ and ([big_sales].[stor_id]
这个看起来也一样。只是查找谓词改变了。因为查找是非常快的,所以这个查询是相当好的。
SEEK和SCAN也可包含Where谓词。在这种情况下,这个谓词告诉你Where子句从结果集中过滤出哪些记录。因为它是作为SEEK或SCAN的一个组件执行的, Where子句通常既不损害也不提高这个操作本身的性能。Where子句会帮助查询优化器找到可能有最佳性能的索引。
查询优化的一个重要部分是要确定是否在某个索引上执行SEEK操作,如果是这样,就找到了具有最佳性能的索引。大部分情况下,查询引擎能出色地查找到存在的索引。但是,目前有三种涉及到索引的常见问题:
◆数据库 设计师,通常是应用开发者,在表中没有建立任何索引。
◆数据库设计师通常猜测不到常用的查询或事务类型,所以建立在表上的索引或主键往往效率不高。
◆当索引表被创建时,即使数据库设计师猜测较准,但事务负载随着时间将发生改变,使得这些索引效率变差。
如果你在你的查询计划中看到大量的SCAN而不是SEEK,你应该从新评估你的索引。例如,看看下面的查询:
Select ord_num
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] ),WHERE : ([sales].[ord_date]>’Jan 1,2002 12:00:00 AM ’))
现在这个查询在我们刚创建的sales_ord_date索引上执行SEEK INDEX操作。
让我们以一个简单的例子帮助你理解如何阅读查询计划,可以通过发出SET SHOWPLAN_TEXT On命令,或者在SQL Query Analyzer 的配置属性中设置同样的选项等方式得到查询计划。
注意:这个例子使用了表pubs.big_sales,该表与pubs..sales表完全相同,除了多了80000行的记录,以当作简单explain plan例子的主要数据。
如下所示,这个最简单的查询将扫描整个聚集索引,如果该索引存在。注意聚集键值是物理次序,数据按该次序存放。所以,如果聚集键值存在,你将可能避免对整个表进行扫描。即使你所选的列不在聚集键值中,例如ord_date,这个查询引擎将用索引扫描并返回结果集。
SELECT *
FROM big_salesSELECT ord_date
FROM big_salesStmtText
-------------------------------------------------------------------------
|--ClusteredIndexScan(OBJECT[pubs].[dbo].[big_sales].[UPKCL_big_sales]))
上面的查询展示返回的数据量非常不同,所以小结果集(ord_date)的查询比其它查询运行更快,这只是因为存在大量底层的I/O。然而,这两个查询计划实际上是一样的。你可以通过使用其它索引提高性能。例如,在title_id列上有一个非聚集索引存在:
SELECT title_id
FROM big_salesStmtText
------------------------------------------------------------------
|--Index Scan(OBJECT[pubs].[dbo].[big_sales].[ndx_sales_ttlID]))
上面的查询的执行时间与SELECT *查询相比非常小,这是因为可以从非聚集索引即可得到所有结果。该类查询被称为covering query(覆盖查询),因为全部结果集被一个非聚集索引所覆盖。
SEEK与SCAN
第一件事是你需要在查询计划中区别SEEK和SCAN操作的不同。
注意:一个简单但非常有用的规则是SEEK操作是有效率的,而SCAN操作即使不是非常差,其效率也不是很好。SEEK操作是直接的,或者至少是快速的,而SCAN操作需要对整个对象进行读取(表,聚集索引或非聚集索引)。因此,SCAN操作通常比SEEK要消耗更多的资源。如果你的查询计划仅是扫描操作,你就应该考虑调整你的查询了。
where子句在查询性能中能产生巨大的差异,如下面展示的:
Select *
From big_sales
Where stor_id=’6380’StmtText
-----------------------------------------------------------------------------|--Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales])),SEEK: ([big_sales].[stor_id]={@1} ORDERED FORWARD)
上面的查询是在聚集索引上执行SEEK而不是SCAN操作。这个SHOWPLAN确切的描述SEEK操作是基于stor_id并且结果是按照在索引中 存储的顺序排序的。因为 SQL Server支持索引的向前和向后滚动的性能是相同的,所以你可以在查询计划中看到ORDERED FORWARD 或ORDERED BACKWARD。这只是告诉你表或索引读取的方向。你甚至可以在ORDER BY子句中通过用ASC和DESC关键字操作这些行为。范围查询返回的查询计划,与前面的直接查询的查询计划很相似。下面两个范围查询可提供一些信息:
Select *
From big_sales
Where stor_id>=’7131’StmtText
------------------------------------------------------------------------------|-Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),SEEK: ([big_sales].[stor_id]>=’7131’) ORDER FORWARD
上面的查询看起来很象以前的例子,除了SEEK谓词有点不同。
Select *
From big_sales
Where stor_id between ‘7066’ and ‘7131’StmtText
------------------------------------------------------------------------------|-Clustered
Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),SEEK[big_sales].[stor_id]>=’7066’ and ([big_sales].[stor_id]
这个看起来也一样。只是查找谓词改变了。因为查找是非常快的,所以这个查询是相当好的。
SEEK和SCAN也可包含Where谓词。在这种情况下,这个谓词告诉你Where子句从结果集中过滤出哪些记录。因为它是作为SEEK或SCAN的一个组件执行的, Where子句通常既不损害也不提高这个操作本身的性能。Where子句会帮助查询优化器找到可能有最佳性能的索引。
查询优化的一个重要部分是要确定是否在某个索引上执行SEEK操作,如果是这样,就找到了具有最佳性能的索引。大部分情况下,查询引擎能出色地查找到存在的索引。但是,目前有三种涉及到索引的常见问题:
◆数据库 设计师,通常是应用开发者,在表中没有建立任何索引。
◆数据库设计师通常猜测不到常用的查询或事务类型,所以建立在表上的索引或主键往往效率不高。
◆当索引表被创建时,即使数据库设计师猜测较准,但事务负载随着时间将发生改变,使得这些索引效率变差。
如果你在你的查询计划中看到大量的SCAN而不是SEEK,你应该从新评估你的索引。例如,看看下面的查询:
Select ord_num
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] ),WHERE : ([sales].[ord_date]>’Jan 1,2002 12:00:00 AM ’))
现在这个查询在我们刚创建的sales_ord_date索引上执行SEEK INDEX操作。
转载于:https://blog.51cto.com/lhw135222/118003