SQL Server SQL语句调优技巧

webmaster 在 2010-03-19 17:18 提交
        www.InnovateDigital.com 整理

      通过例子和解析计划，本文展示了在Microsoft SQL Server上提高查询效率有效的一些技巧。在编程中有很多小提示和技巧。了解这些技巧可以扩展你在性能优化上的可用机能。在这部分里我们所有的例子都选择使用Microsoft SHOWPLAN_ALL输出，因为它更紧凑并且展示典型的信息。（Sybase的查询计划基本与此相同，可能包含其它一些信息）大部分的例子都是要么基于PUBS数据库，要么基于标准系统表的。我们在PUBS数据库中对用到的表进行了很大扩充，对很多表增加了好几万行。
 

      子查询优化

      一条好的值得称赞的规则是尽量用连接代替所有的子查询。优化器有时可以自动将子查询“扁平化”，并且用常规或外连接代替。但那样也不总是有效。明确的连接对选择表的顺序和找到最可能的计划给出了更多的选项。当你优化一个特殊查询时，了解一下是否去掉自查询可产生很大的差异。
 

      示例
 

      下面查询选择了pubs数据库中所有表的名字，以及每个表的聚集索引（如果存在）。如果没有聚集索引，表名仍然显示在列表中，在聚集索引列中显示为虚线。两个查询返回同样的结果集，但第一个使用了一个子查询，而第二个使用一个外连接时。比较Microsoft SQL Server产生的查询计划

      SUBQUERY SOLUTION

      ----------------------

      SELECT st.stor_name AS 'Store',

      (SELECT SUM(bs.qty)

      FROM big_sales AS bs

      WHERE bs.stor_id = st.stor_id), 0)

      AS 'Books Sold'

      FROM stores AS st

      WHERE st.stor_id IN

      (SELECT DISTINCT stor_id

      FROM big_sales)

JOIN SOLUTION

----------------------

SELECT st.stor_name AS 'Store',

SUM(bs.qty) AS 'Books Sold'

FROM stores AS st

JOIN big_sales AS bs

ON bs.stor_id = st.stor_id

WHERE st.stor_id IN

(SELECT DISTINCT stor_id

FROM big_sales)

GROUP BY st.stor_name

      SUBQUERY SOLUTION

      ----------------------

      SQL Server parse and compile time:

          CPU time = 28 ms

          elapsed time = 28 ms

      SQL Server Execution Times:

          CPU time = 145 ms

          elapsed time = 145 ms

 

      Table 'big_sales'. Scan count 14, logical reads

      1884, physical reads 0, read-ahead reads 0.

      Table 'stores'. Scan count 12, logical reads 24,
      physical reads 0, read-ahead reads 0.
 

JOIN SOLUTION

----------------------

SQL Server parse and compile time:

    CPU time = 50 ms

    elapsed time = 54 ms

SQL Server Execution Times:

    CPU time = 109 ms

    elapsed time = 109 ms

 

Table 'big_sales'. Scan count 14, logical reads

966, physical reads 0, read-ahead reads 0.

Table 'stores'. Scan count 12, logical reads 24,
physical reads 0, read-ahead reads 0.

      不必更深探索，我们可以看到在CPU和总的实耗时间方面连接更快，仅需要子查询方案逻辑读的一半。此外，这两种情况伴随着相同的结果集，虽然排序的顺序不同，这是因为连接查询（由于它的GROUP BY子句）有一个隐含的ORDER BY:

      Store Books Sold

      -------------------------------------------------

      Barnum's 154125

      Bookbeat 518080

      Doc-U-Mat: Quality Laundry and Books 581130

      Eric the Read Books 76931

      Fricative Bookshop 259060

      News & Brews 161090

      (6 row(s) affected)

 

      Store Books Sold

      -------------------------------------------------

      Eric the Read Books 76931

      Barnum's 154125

      News & Brews 161090

      Doc-U-Mat: Quality Laundry and Books 581130

      Fricative Bookshop 259060

      Bookbeat 518080

      (6 row(s) affected)
      查看这个子查询方法展示的查询计划:

      |--Compute Scalar(DEFINE:([Expr1006]=isnull([Expr1004], 0)))

      |--Nested Loops(Left Outer Join, OUTER REFERENCES:([st].[stor_id]))

      |--Nested Loops(Inner Join, OUTER REFERENCES:([big_sales].[stor_id]))

         | |--Stream Aggregate(GROUP BY:([big_sales].[stor_id]))

            | | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales].

            [UPKCL_big_sales]), ORDERED FORWARD)

         | |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores].[UPK_storeid]

      AS [st]),

      SEEK:([st].[stor_id]=[big_sales].[stor_id]) ORDERED FORWARD)

       |--Stream Aggregate(DEFINE:([Expr1004]=SUM([bs].[qty])))

      |--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales].

        [UPKCL_big_sales] AS [bs]),

      SEEK:([bs].[stor_id]=[st].[stor_id]) ORDERED FORWARD)
      反之，求和查询操作我们可以得到：

      |--Stream Aggregate(GROUP BY:([st].[stor_name])

        DEFINE:([Expr1004]=SUM([partialagg1005])))

      |--Sort(ORDER BY:([st].[stor_name] ASC))

      |--Nested Loops(Left Semi Join, OUTER REFERENCES:([st].[stor_id]))

      |--Nested Loops(Inner Join, OUTER REFERENCES:([bs].[stor_id]))

        | |--Stream Aggregate(GROUP BY:([bs].[stor_id])

          DEFINE:([partialagg1005]=SUM([bs].[qty])))

             | | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales].

            [UPKCL_big_sales] AS [bs]), ORDERED FORWARD)

        | |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores].

            [UPK_storeid] AS [st]),

        SEEK:([st].[stor_id]=[bs].[stor_id]) ORDERED FORWARD)

      |--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales].

          [UPKCL_big_sales]),

        SEEK:([big_sales].[stor_id]=[st].[stor_id]) ORDERED FORWARD)

      使用连接是更有效的方案。它不需要额外的流聚合（stream aggregate），即子查询所需在big_sales.qty列的求和。


 

 

      UNION vs UNION ALL

 


      无论何时尽可能用UNION ALL 代替UNION。其中的差异是因为UNION有排除重复行并且对结果进行排序的副作用，而UNION ALL不会做这些工作。选择无重复行的结果需要建立临时工作表，用它排序所有行并且在输出之前排序。（在一个select distinct 查询中显示查询计划将发现存在一个流聚合，消耗百分之三十多的资源处理查询）。当你确切知道你得需要时，可以使用UNION。但如果你估计在结果集中没有重复的行，就使用UNION ALL吧。它只是从一个表或一个连接中选择，然后从另一个表中选择，附加在第一条结果集的底部。UNION ALL不需要工作表和排序（除非其它条件引起的）。在大部分情况下UNION ALL更具效率。一个有潜在危险的问题是使用UNION会在数据库中产生巨大的泛滥的临时工作表。如果你期望从UNION查询中获得大量的结果集时，这就可能发生。
 

      示例


      下面的查询是选择pubs数据库中的表sales的所有商店的ID，也选择表big_sales中的所有商店的ID，这个表中我们加入了70，000多行数据。在这两个方案间不同之处仅仅是UNION 与UNION ALL的使用比较。但在这个计划中加入ALL关键字产生了三大不同。第一个方案中，在返回结果集给客户端之前需要流聚合并且排序结果。第二个查询更有效率，特别是对大表。在这个例子中两个查询返回同样的结果集，虽然顺序不同。在我们的测试中有两个临时表。你的结果可能会稍有差异。
      UNION SOLUTION

      -----------------------

       UNION ALL SOLUTION

      -----------------------

      SELECT stor_id FROM big_sales

      UNION

      SELECT stor_id FROM sales

      ----------------------------

      SELECT stor_id FROM big_sales

      UNION ALL

      SELECT stor_id FROM sales

      ----------------------------

      |--Merge Join(Union)

       |--Stream Aggregate(GROUP BY:

      ([big_sales].[stor_id]))

      | |--Clustered Index Scan

      (OBJECT:([pubs].[dbo].

      [big_sales].

      [UPKCL_big_sales]),

      ORDERED FORWARD)

      |--Stream Aggregate(GROUP BY:

      ([sales].[stor_id]))

       |--Clustered Index Scan

       (OBJECT:([pubs].[dbo].

       [sales].[UPKCL_sales]),

       ORDERED FORWARD)

      |--Concatenation

      |--Index Scan

      (OBJECT:([pubs].[dbo].

       [big_sales].[ndx_sales_ttlID]))

      |--Index Scan

      (OBJECT:([pubs].[dbo].

      [sales].[titleidind]))

      UNION SOLUTION

      -----------------------

      Table 'sales'. Scan count 1, logical

      reads 2, physical reads 0,

      read-ahead reads 0.

      Table 'big_sales'. Scan count 1,

      logical

      reads 463, physical reads 0,

      read-ahead reads 0.

      UNION ALL SOLUTION

      -----------------------

      Table 'sales'. Scan count 1, logical

      reads 1, physical reads 0,

      read-ahead reads 0.

      Table 'big_sales'. Scan count 1,

      logical

      reads 224, physical reads 0,

      read-ahead reads 0.

      虽然在这个例子的结果集是可互换的，你可以看到UNION ALL语句比UNION语句少消耗一半的资源。所以应当预料你的结果集并且确定已经没有重复时，使用UNION ALL子句。


 

      函数和表达式约束索引


      当你在索引列上使用内置的函数或表达式时，优化器不能使用这些列的索引。尽量重写这些条件，在表达式中不要包含索引列。
 

      示例

      你应该帮助SQL Server移除任何在索引数值列周围的表达式。下面的查询是从表jobs通过唯一的聚集索引的唯一键值选择出的一行。如果你在这个列上使用表达式，这个索引就不起作用了。但一旦你将条件’job_id-2=0’ 该成‘job_id=2’，优化器将在聚集索引上执行seek操作。

 
      QUERY WITH SUPPRESSED INDEX

      -----------------------

       OPTIMIZED QUERY USING INDEX

      -----------------------

      SELECT *

      FROM jobs

      WHERE (job_id-2) = 0

      SELECT *

      FROM jobs

      WHERE job_id = 2

      |--Clustered Index Scan(OBJECT:

      ([pubs].[dbo].[jobs].

      [PK__jobs__117F9D94]),

      WHERE:(Convert([jobs].[job_id])-

      2=0))

      |--Clustered Index Seek(OBJECT:

      ([pubs].[dbo].[jobs].

      [PK__jobs__117F9D94]),

      SEEK:([jobs].[job_id]=Convert([@1]))

      ORDERED FORWARD)

      Note that a SEEK is much better than a SCAN,

      as in the previous query.
 

      下面表中列出了多种不同类型查询示例，其被禁止使用列索引，同时给出改写的方法，以获得更优的性能。

 
      QUERY WITH SUPPRESSED INDEX

      ---------------------------------------

       OPTIMIZED QUERY USING INDEX

      --------------------------------------

      DECLARE @job_id VARCHAR(5)

      SELECT @job_id = ‘2’

      SELECT *

      FROM jobs

      WHERE CONVERT( VARCHAR(5),

      job_id ) = @job_id

      -------------------------------

      DECLARE @job_id VARCHAR(5)

      SELECT @job_id = ‘2’

      SELECT *

      FROM jobs

      WHERE job_id = CONVERT(

      SMALLINT, @job_id )

      -------------------------------

      SELECT *

      FROM authors

      WHERE au_fname + ' ' + au_lname

      = 'Johnson White'

      -------------------------------

      SELECT *

      FROM authors

      WHERE au_fname = 'Johnson'

      AND au_lname = 'White'

      -------------------------------

      SELECT *

      FROM authors

      WHERE SUBSTRING( au_lname, 1, 2 ) = 'Wh'

      -------------------------------

      SELECT *

      FROM authors

      WHERE au_lname LIKE 'Wh%'

      -------------------------------

      CREATE INDEX employee_hire_date

      ON employee ( hire_date )

      GO

      -- Get all employees hired

      -- in the 1st quarter of 1990:

      SELECT *

      FROM employee

      WHERE DATEPART( year, hire_date ) = 1990

      AND DATEPART( quarter, hire_date ) = 1

      -------------------------------

      CREATE INDEX employee_hire_date

      ON employee ( hire_date )

      GO

      -- Get all employees hired

      -- in the 1st quarter of 1990:

      SELECT *

      FROM employee

      WHERE hire_date >= ‘1/1/1990’

      AND hire_date < ‘4/1/1990’

      -------------------------------

      -- Suppose that hire_date may

      -- contain time other than 12AM

      -- Who was hired on 2/21/1990?

      SELECT *

      FROM employee

      WHERE CONVERT( CHAR(10),

      hire_date, 101 ) = ‘2/21/1990’

      -- Suppose that hire_date may

      -- contain time other than 12AM

      -- Who was hired on 2/21/1990?

      SELECT *

      FROM employee

      WHERE hire_date >= ‘2/21/1990’

      AND hire_date < ‘2/22/1990’


 

      SET NOCOUNT ON


      使用SET NOCOUNT ON 提高T-SQL代码速度的现象使SQL Server开发者和数据库系统管理者惊讶难解。你可能已经注意到成功的查询返回了关于受影响的行数的系统信息。在很多情况下，你不需要这些信息。这个SET NOCOUNT ON命令允许你禁止所有在你的会话事务中的子查询的信息，直到你发出SET NOCOUNT OFF。
      这个选项不只在于其输出的装饰效果。它减少了从服务器端到客户端传递的信息量。因此，它帮助降低了网络通信量并提高了你的事务整体响应时间。传递单个信息的时间可以忽略，但考虑到这种情况，一个脚本在一个循环里执行一些查询并且发送好几千字节无用的信息给用户。

      为做个例子，一个文件含T-SQL批处理，其在big_sales表插入了9999行。

 

 

 


      当带SET NOCOUNT OFF命令运行，实耗时间是5176毫秒。当带SET NOCOUNT ON命令运行，实耗时间是1620毫秒。如果不需要输出中的行数信息，考虑在每一个存储过程和脚本开始时增加SET NOCOUNT ON 命令将。

      TOP 和 SET ROWCOUNT

 

      SELECT 语句中的TOP子句限制单个查询返回的行数，而SET ROWCOUNT限制所有后续查询影响的行数。在很多编程任务中这些命令提供了高效率。
      SET ROWCOUNT在SELECT，INSERT，UPDATE OR DELETE语句中设置可以被影响的最大行数。这些设置在命令执行时马上生效并且只影响当前的会话。为了移除这个限制执行SET ROWCOUNT 0。
一些实际的任务用TOP or SET ROWCOUNT比用标准的SQL命令对编程是更有效率的。让我们在几个例子中证明：

 

      TOP n

      在几乎所有的数据库中最流行的一个查询是请求一个列表中的前N项。在 pubs数据库案例中，我们可以查找销售最好CD的前五项。比较用TOP，SET ROWCOUNT和使用ANSI SQL的三种方案。

 

      纯 ANSI SQL:

      Select title,ytd_sales

      From titles a

      Where (select count(*)

      From titles b

      Where b.ytd_sales>a.ytd_sales

      )<5

      Order by ytd_sales DESC

 

      这个纯ANSI SQL方案执行一个效率可能很低的关联子查询，特别的在这个例子中，在ytd_sales上没有索引支持。另外，这个纯的标准SQL命令没有过滤掉在ytd_sales的空值,也没有区别多个CD间有关联的情况。

 

 

      使用 SET ROWCOUNT:

      SET ROWCOUNT 5

      SELECT title, ytd_sales

      FROM titles

      ORDER BY ytd_sales DESC

      SET ROWCOUNT 0

      使用 TOP n:

      SELECT TOP 5 title, ytd_sales

      FROM titles

      ORDER BY ytd_sales DESC

      第二个方案使用SET ROWCOUNT来停止SELECT查询，而第三个方案是当它找到前五行时用TOP n来停止。在这种情况下，在获得结果之前我们也要有一个ORDER BY子句强制对整个表进行排序。两个查询的查询计划实际上是一样的。然而，TOP优于SET ROWCOUNT的关键点是SET必须处理ORDER BY子句所需的工作表，而TOP 不用。

 

      在一个大表上，我们可以在ytd_sales上创建一个索引以避免排序。查询将使用该索引找到前5行并停止。与第一个方案相比较，其扫描了整个表，并对每一行执行了一个关联子查询。在小表上，性能的差异是很小的。但是在一个大表上，第一个方案的处理时间可能是数个小时，而后两个方法是数秒。

 

      当确定查询需要时，请考虑是否只需要其中几行，如果是，使用TOP子句将节约大量时间。

 

SQL Server