笔记57-徐 调整语句设计提高性能

笔记57-徐  调整语句设计提高性能

--调整语句设计提高性能 2013-2-11  P449

--当一个问题语句，已经排除了系统资源瓶颈、阻塞与死锁、物理I/O、编译与重编译
--参数嗅探这些因素，也发现调整索引或使用计划指南不能达到要求，那怎么办？
--不幸的是，我们已经基本上把SQL上常见的调优方法都介绍了。在有些情况下，一个语句
--的写法决定了他天生是一条复杂的语句，SQL很难使用最优的方法来运行他。这时候
--调整SQLSERVER，可能效果都不会很明显。用户要想想，如果这个问题更多的是由语句
--本身导致的话，那调整语句设计是不是更好的解决方法。有时候，可能是解决问题
--唯一的选择

--常见的语句优化方法：

--1、筛选条件与计算字段
--筛选条件的写法是有讲究的。最好能够使用SARG的运算符包括=、>、<、>=、<=、in、between、like
--有时还包括like（在前缀匹配时，如like'john%'）SARG可以包括由and联接的多个条件。SARG不但可以是匹配
--特定值的查询，例如
CustomerID=ANTON
DOE=last name

--还可以是匹配一定范围的值的查询，例如
order date>'1/1/2002'
order date>'1/1/2002' and order date<'1/1/2009'
doe in('anton','about')


--对于不使用SARG运算符的表达式，SQL对它们很难使用比较优化的做法，很可能就不使用
--索引。

--非SARG运算符包括 not、<>、not exists、not in、not like和内部函数，例如
--convert、upper等。下面的查询，就不会使用在Production.Product.Name字段上
--的索引 非聚集索引

--三个都是使用索引扫描
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT [Name] FROM [Production].[Product]
WHERE [Name] LIKE'%Deca%'  --不是前缀匹配，所以使用索引扫描
GO
-----------------------------------------------
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT [Name] FROM [Production].[Product]
WHERE [Name] NOT LIKE'%Deca%'
GO
------------------------------------------------
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT [Name] FROM [Production].[Product]
WHERE LEFT([Name],4)='Deca'
GO

--另外，如果要对字段先进性计算，再比较，也会妨碍索引的使用。例如
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
SELECT [Name] FROM [Production].[Product]
WHERE [Name]+'_end'='Decal 1_end'

---下面这样才会使用索引查找
USE [AdventureWorks]
GO
SET STATISTICS PROFILE ON
SELECT [Name] FROM [Production].[Product]
WHERE [Name]='Decal 1'

--在写语句的时候，要尽量避免这些写法。有时候，程序里会有很多过滤是要通过
--先计算字段值再进行的。假如，我们找产品的时候，代入的都是大写的字母，
--做比较的时候需要先将“name”字段转成大写，再比较。这时候很自然的，语句
--就是：
SELECT [Name] FROM [Production].[Product]
WHERE UPPER([Name])='Decal 1'

--他是不会使用索引seek的。如果程序里的许多语句都是这样，那SQL的性能是不会很好的。
--解决的办法，可以是在表格里再加一个字段，专门存放UPPER([Name])以后的结果。然后
--在这个新字段上建立索引。新插入的字段可以是一个计算字段(computed column)这样
--甚至insert语句都不用太多改动

--例如：我们可以这样修改Production.Product表
USE [AdventureWorks]
GO
ALTER TABLE [Production].[Product] ADD UpperName AS UPPER([Name]) PERSISTED --带有Persisted值的计算列 会存储实际数据
--http://database.ctocio.com.cn/dbzjdysummary/48/8730048_4.shtml
--如果Persisted属性被关掉了，那么计算列只是虚拟列(书本是没有加PERSISTED关键字的)。该列将没有数据存储到磁盘上，并且这些值每次在一个脚本中参照时都会被计算。如果这个属性被设置成激活的，那么计算列的数据将会存储在磁盘中。
--加入一个计算字段，其值直接从UPPER([Name])算出
GO

CREATE NONCLUSTERED INDEX AK_Product_UName ON [Production].[Product](uppername)
--在上面创建一个非聚集索引
GO

USE [AdventureWorks]
GO
SELECT UpperName
FROM [Production].[Product]
WHERE UpperName='DECAL 1'
--改语句查询UpperName字段，会使用他上面的索引
GO

--删除
ALTER TABLE [Production].[Product] DROP COLUMN UpperName
DROP INDEX AK_Product_UName ON [Production].[Product]



--一些表格里有时间字段,而语句经常按年、月查询的时候，也可以使用这种方法
--引入计算字段，事先把计算的值存储在索引数据结构里。这样每次查询的时候
--就不用再算一遍了

--不过，不是所有的计算字段上都能加索引。有些定义，可能每次运行返回值会
--不一样non-deterministic。例如，根据employee的生日算年龄，值就会随
--当前的时间而发生不断的变化。

USE [AdventureWorks]
GO
ALTER TABLE [HumanResources].[Employee] ADD age AS DATEDIFF(yy,GETDATE(),[BirthDate])
GO

CREATE NONCLUSTERED INDEX AK_Employee_Age ON [HumanResources].[Employee](age)
GO


--对这样的语句,查询的修改可能得换一个思路.比如,我想找年龄大于30岁的员工,
--可以写成
USE [AdventureWorks]
GO
SELECT * FROM [HumanResources].[Employee] WHERE DATEDIFF(yy,[BirthDate],GETDATE())>30

--但是这样性能会有问题，不能使用索引，如果写成：
USE [AdventureWorks]
GO
SELECT * FROM [HumanResources].[Employee] WHERE [BirthDate] <DATEADD(yy,-30,GETDATE())

--SQL就有办法使用索引了。两种写法得到的结果很近似，但是第二种对SQL性能比较有利

--2、会在运行前改变值的变量
--在谈到参数嗅探的时候，提到过SQL在编译的时候，对存储过程代入的变量，SQL是
--知道他的值的，也会根据他的值对语句进行优化。但是如果在语句使用他之前，被
--其他语句修改过，那SQL生成的执行计划就不准了。这种情况，有时也会导致性能
--问题

--例如，下面这个存储过程，@date是他代入的参数。SQL会根据参数的值，生成执行计划
USE [AdventureWorks]
GO
CREATE PROCEDURE GetRecentSales(@date DATETIME)
AS
BEGIN
SELECT SUM(d.[OrderQty])
FROM [dbo].[SalesOrderHeader_test] h,[dbo].[SalesOrderDetail_test] d
WHERE h.[SalesOrderID]=d.[SalesOrderID]
AND h.[OrderDate]>@date
END
----------------------------------------------------------------------
EXEC [sys].[sp_recompile] @objname = N'GetRecentSales' -- nvarchar(776)
GO
DBCC freeproccache
GO
SET STATISTICS PROFILE ON
GO
EXEC GetRecentSales NULL
--预估结果集很小，会使用nested loops
GO

EXEC [sys].[sp_recompile] @objname = N'GetRecentSales' -- nvarchar(776)
GO
DECLARE @date DATETIME
SET @date=DATEADD(mm,-3,(SELECT MAX([OrderDate]) FROM [dbo].[SalesOrderHeader_test]))
SET STATISTICS PROFILE ON
EXEC GetRecentSales @date
--预估结果集比较大，会使用hash match
GO



--但是如果我们把存储过程改成下面这个样子：
USE [AdventureWorks]
GO
ALTER PROC GetRecentSales ( @date DATETIME )
AS
    BEGIN
        IF @date IS NULL
            SET @date = DATEADD(mm, -3,
                                ( SELECT    MAX([OrderDate])
                                  FROM      [dbo].[SalesOrderHeader_test]
                                ))
     --如果是null值，会代入一个新的日期
        SELECT  SUM(d.[OrderQty])
        FROM    [dbo].[SalesOrderHeader_test] h ,
                [dbo].[SalesOrderDetail_test] d
        WHERE   h.[SalesOrderID] = d.[SalesOrderID]
                AND h.[OrderDate] > @date
    END

-------------------------------------------------------------
EXEC [sys].[sp_recompile] @objname = N'GetRecentSales' -- nvarchar(776)
GO
DBCC freeproccache
GO
SET STATISTICS PROFILE ON
EXEC GetRecentSales NULL
GO

--我们再用null值来运行,会发现SQL没办法感知到值发生了变化,还是使用了
--nested loop完成了查询。这个执行计划不是最优的

--怎麽来解决这个问题呢？当然，你可以在使用变量的语句后面加一个
--option(recompile)的query hint。这样当SQL运行到这句话的时候，
--会重编译可能出问题的语句。在那个时候，就能根据修改过的值
--生成更精确的执行计划了

USE [AdventureWorks]
GO
ALTER PROC GetRecentSales ( @date DATETIME )
AS
    BEGIN
        IF @date IS NULL
            SET @date = DATEADD(mm, -3,
                                ( SELECT    MAX([OrderDate])
                                  FROM      [dbo].[SalesOrderHeader_test]
                                ))
     --如果是null值，会代入一个新的日期
        SELECT  SUM(d.[OrderQty])
        FROM    [dbo].[SalesOrderHeader_test] h ,
                [dbo].[SalesOrderDetail_test] d
        WHERE   h.[SalesOrderID] = d.[SalesOrderID]
                AND h.[OrderDate] > @date
        OPTION(recompile)
    END
   
--还有一种方法,是把可能出问题的语句单独做成一个子存储过程,让原来
--的存储过程调用子存储过程,而不是语句本身。例如

USE [AdventureWorks]
GO
CREATE PROCEDURE GetRecentSalesHelper(@date DATETIME)
AS
BEGIN
        SELECT  SUM(d.[OrderQty])
        FROM    [dbo].[SalesOrderHeader_test] h ,
                [dbo].[SalesOrderDetail_test] d
        WHERE   h.[SalesOrderID] = d.[SalesOrderID]
                AND h.[OrderDate] > @date
               
END


ALTER PROC GetRecentSales ( @date DATETIME )
AS
    BEGIN
        IF @date IS NULL
            SET @date = DATEADD(mm, -3,
                                ( SELECT    MAX([OrderDate])
                                  FROM      [dbo].[SalesOrderHeader_test]
                                ))
        EXEC GetRecentSalesHelper @date
    END    
   
   
--这样做的好处,是可以省下语句重编译的时间.两种方法,各有好处.可以根据
--实际情况做选择


--3、临时表和表变量
--不知道大家有没有注意到，SQL里有两种对象可以暂时存放表结构的数据
--一种就是大家很熟悉的临时表（temp table），另一种是名气小一点
--，是表变量（table variable）。这两种对象功能类似，差异不太明显
--功能上比较大的差别是，表变量可以作为存储过程的返回参数，而
--临时表不行

--那是不是用表变量就可以了，为什麽SQL还要保留临时表这个功能呢？
--其实这两个对象在内部实现上还是有很大区别的。


--最显著的区别：

--SQL会像对普通表一样，在临时表上维护统计信息，用户也可以在上面建立
--索引。而表变量上，既不能建立索引，也不会有统计信息。SQL在做执行
--计划的时候，总是认为表变量里的数据量只有很少的几行

--现在来做一个测试体会一下

--表变量情形
USE [AdventureWorks]
GO
DECLARE @tmp TABLE(ProductID INT,OrderQty INT)
INSERT INTO @tmp
SELECT [ProductID],[OrderQty]
FROM [dbo].[SalesOrderDetail_test]
WHERE [SalesOrderID]=75124
--语句会插入12万条记录

SET STATISTICS PROFILE ON
SELECT p.[Name],p.[Color],SUM(t.[OrderQty])
FROM @tmp t
INNER JOIN [Production].[Product] p
ON t.[ProductID]=p.[ProductID]
GROUP BY p.[Name],p.[Color]
ORDER BY p.[Name]
--含有12万条记录的表变量和另一张表做join
GO


--从执行计划里可以看到，SQL认为表变量只会返回1行，所以选择了nested loops。
--在这里是不太合适的


--临时表情形
USE [AdventureWorks]
GO
CREATE  TABLE #tmp(ProductID INT,OrderQty INT)
INSERT INTO #tmp
SELECT [ProductID],[OrderQty]
FROM [dbo].[SalesOrderDetail_test]
WHERE [SalesOrderID]=75124
--语句会插入12万条记录

SET STATISTICS PROFILE ON
SELECT p.[Name],p.[Color],SUM(t.[OrderQty])
FROM #tmp t
INNER JOIN [Production].[Product] p
ON t.[ProductID]=p.[ProductID]
GROUP BY p.[Name],p.[Color]
ORDER BY p.[Name]
--含有12万条记录的表变量和另一张表做join
GO
DROP TABLE [#tmp]
GO

--在 SQL Trace里会看到Auto Stats -Created 事件

--和表变量很不相同的是，SQL在insert语句之后，select语句之前，触发了
--一个自动创建统计信息auto stats created事件。建立了统计信息以后
--SQL就知道临时表里有很多数据了。join的方式，因此改用了merge join
--性能比前一种好很多

--所以表变量的好处是，他的维护成本很低，大量并发使用时对系统的负担
--比临时表要低。但是缺点是没有统计信息，存放大量的数据时性能很难保证。
--所以，表变量比较适合存放一些很小（几十行或更小）的结果集

--临时表的好处是，他的功能和普通用户表接近，能够为大数据集做优化
--但是缺点是维护成本高。大量并发使用临时表，会对系统带来比较重
--的负荷。所以临时表比较适合存放一些大的结果集

--在设计数据库应用，尤其是OLTP这样性能很敏感的应用时，要根据实际情况
--作出合理选择




--4、尽可能限定语句的复杂度
--这是一个人人皆知的道理。如果语句不太复杂，当然性能会好。如果语句非常复杂
--当然开销会大，恨呐调快。可是，为了支持业务逻辑，还是常常在SQL里看到
--非常复杂的语句。这为调优带来了很大麻烦。有时候简直是无法可想，除非
--是对应用设计做大手术


--这里列举出一些容易产生复杂语句的情形。在大家设计应用的时候，要小心使用

--（1）动态语句
--一些应用为了实现客户端的灵活性，会根据用户的选择，动态拼出TSQL语句，发给SQL
--运行。例如，在用户界面上列出各种条件，让用户根据自己的喜好，输入条件，进行
--组合查询。这样在功能上来讲比较强大，但是在复杂度控制上就有可能会出问题。
--如果用户选择的条件太多，或者根据条件返回的记录太多，就有可能会造成问题。
--而有些能够过滤大量数据，或者在索引上的条件如果没有被选上，就有可能造成在
--大表上的table scan。最好在程序里有动态语句复杂度的控制机制，限制选择的条件
--限制返回记录的数量



--（2）表格联接的数量
--为了支持复杂的业务逻辑，一个应用往往会有成百上千的表格，一些查询往往会联接
--十几张甚至几十张表。应用设计的时候对这样的查询要很慎重。如果表格很大，十几张
--表做联接，肯定不会有好的性能。如果应用是支持数据分析系统，那可能还好。如果
--应用是一个OLTP系统，这样的设计失败的风险可能会很大。有时候可能需要降低
--数据库范式级别，多保存一些冗余数据列，以减少表格联接的数量




--（3）视图和存储过程的深度
--视图和存储过程能够抽象出一些业务逻辑，简化设计，是很推荐的做法。但是如果
--在引用视图和存储过程时不加注意，视图套视图，存储过程嵌存储过程，最后
--嵌套上四五层，那复杂度累积起来，可能会超出你想象。对SQL的优化，也是很
--严重的考验。所以在引用他们的时候，也要考虑累积的复杂度



--（4）不必要的排序和计算
--对一个大结果集做排序，或者求唯一值，都是比较昂贵的计算，会占用大量系统资源
--如果用户对结果集排序或唯一性的要求不高，可以适当去掉这些计算




--（5）超大结果集申请和返回
--如果根据用户选择的过滤条件，SQL会返回十几万条记录，那应用层该如何处理？
--如果一次性返回给应用层，那应用层要缓存和处理这么多记录，自己的性能
--会受到很大的挑战。如果一次只取一部分记录，其他记录由SQL代为缓存
--（一般是应用服务器端游标），那不但会给SQL的内存使用带来负担，而且
--容易产生阻塞问题。如果应用层处理得不好，甚至会产生内存泄漏的问题。
--所以程序设计的时候，要确保应用只会申请合适的、有必要的结果集。
--例如一个用户在网页上查询他感兴趣的产品，可能最多只会看前面的
--100个。如果你返回一万一个产品记录给他，除了暗示你产品多以外，
--对用户没有任何意义。这时候在语句里设置一个top 100，可能是
--个合理的选择




--（6）用多个简单语句替代一个复杂语句
--如果一个复杂的语句有很多张表要联接，要做很多计算，很多时候，要
--根据表和表的逻辑关系，知道某一张表和另一张表如果先做联接，
--可能会过滤掉更多数据。得到的小的结果集再做其他联接，会更快
--类似的，有些计算可以先做，也可以后做，人在了解了表格的逻辑之后
--会知道是先做好还是后做好。可惜SQL作为一个计算机程序，在这方面
--没有人那么聪明。当语句太复杂的时候，他有可能看不出来了。为了
--提高性能，对这种特别复杂的语句，可以把一句话拆成两句，甚至三句
--分步做完，中间结果集，可以以临时表的形式存放。这样做对程序员来
--讲做了很多事，但是对SQL来讲，大大简化了复杂度。很多时候对性能
--也会有帮助