SQL语句调优-基础知识准备

当前位置:

首页 > 数据库 > sql数据库 >

SQL语句调优-基础知识准备

当确定了应用性能问题可以归结到某一个，或者几个耗时资源的语句后，对这些语句进行调优，就是数据库管理员或者数据库应用程序开发者当仁不让的职责了。语句调优是和数据库打交道的必备基本功之一。

当你面对一个“有问题”的语句时，应该怎么分析它的问题所在，最后达到优化语句的目的呢？首先要想一想，“有问题”的语句“问题”究竟在那里？也就是说，你要优化的目标是什么。常见的需求有:

1) 语句需要访问大量的数据页面，造成内在压力、磁盘繁忙等。

对于这类问题，所关心的是为什么语句要执行要访问这么多数据页面？是语句的结果集本身就比较大;还是SQL SERVER没有办法有效地seek，而是像大炮打苍蝇一样从大量的原始数据里找出需要返回的结果；还是因为数据页面里有很多碎片，导致SQL SERVER读了很多页面，但是每个页面里的数据量不多。这些都是要考虑的因素。

2) 在内存没有压力的前提下（语句所访问的页面都事先缓存在内存里），语句运行的时间还是很长。

语句的运行时间一般会主要花在这3步上：语句编译、语句执行和结果集返回。结果集返回的速度和SQL SERVER自身没有太大关系，所以一般不会在语句调优的时候来考虑。语句调优时要搞清楚编译和执行各花了多少时间，哪一段时间有优化的空间，以及怎么来优化。

3) 单个语句执行时间可以接受，但是苦CPU使用量比较大，多个语句并发执行会造成SQL SERVER CPU高。

有些语句单句执行可能一两秒钟就能执行完毕，对用户来讲还在可接受的范围。但是它的CPU间可能也是在一两秒，甚至更长。如果同时有十几个用户在跑同样的语句，SQL SERVER 就会满负荷了。语句的CPU时间也分编译阶段和执行阶段。优化者要先搞清楚这两个阶段各用了多少CPU资源，然后再看看有没有优化降低CPU使用量的可能。

4) 语句单独执行看不出有大问题，但是并发执行就容易遇到阻塞和死锁。

这个也是语句调优的一个重要任务。很多语句执行速度很快，使用资源量SQL SERVER也能够承受，但是就是容易引起阻塞和死锁。这种现象往往是由于应用在某个表或者索引上的并发度特别高，而问题语句申请的锁数量比较大造成的。当然有时候可以使用Query Hint 来强制 SQL SERVER使用粒度比较小的锁。但是这往往不是最好的解决办法，也可能解决不了问题。最理想的方法，是通过调整语句运行方式，引导它申请尽可能少的、粒度尽可能小的锁。这里也要做语句调优。

在做这些调优的时候，首先要对目标语句做估算，看看它优化的空间有多大。有些语句本身比较简单，可以通过调整索引的方法迅速提高性能，这样的调优是很值得做的。有些语句非常复杂，或者返回的结果集很大，通过调整SQL SERVER这里的设置，提高性能的空间往往不大。这个时候就要考虑，语句本身是不是能够换一种方法实现。很多时候改一下语句，把一条大的语句拆分成若干条小的语句，或者去掉一些不必要的逻辑，会达到事半功倍的效果

在谈论如何做语句调优的具体方法之前，必须先介绍一下最必需的背景知识。不了解这些知识，做语句调优就只能基本靠猜。所需要的背景知识主要包括理解索引和统计信息，理解什么是统计和重编译，并且能够基本读懂语句的执行计划。以下为例子，借助MS示例数据库AdventureWordks来介绍。

--测试用例

USE AdventureWorks2008
GO
IF OBJECT_ID ('SalesOrderHeader_TEST') IS NOT NULL
    DROP TABLE dbo.SalesOrderHeader_TEST
GO
IF OBJECT_ID ('dbo.SalesOrderDetail_TEST') IS NOT NULL
    DROP TABLE dbo.SalesOrderDetail_TEST
GO

-- (31465 行受影响)

SELECT * INTO dbo.SalesOrderHeader_TEST
FROM Sales.SalesOrderHeader

-- (121317 行受影响)

SELECT * INTO dbo.SalesOrderDetail_TEST
FROM Sales.SalesOrderDetail

-- 建立聚集索引
CREATE CLUSTERED INDEX SalesOrderHeader_TEST_CL ON dbo.SalesOrderHeader_TEST(SalesOrderID)

-- 建立非聚集索引
CREATE NONCLUSTERED INDEX  SalesOrderDetail_TEST_NCL ON dbo.SalesOrderDetail_test(SalesOrderID)
go

SalesOrderHeader_TEST 里存放的是每一张订单的头信息，包括订单创建日期、客户编号、合同编号、销售员编号等，每个订单都有一个单独的订单号。在订单号这个字段上，有一个聚集索引。
SalesOrderDetail_TEST 里存放的是订单的详细内容。一张订单可以销售多个产品给同一个客户，所以SalesOrderHeader_TEST 和SalesOrderDetail_TEST是一对多的关系。每每详细内容包括它所属的订单编号，它自己在表格里的唯一编号(SalesOrderDetailID)、产品编号、单价、以及销售数量等。在这里，先只在SalesOrderDetailID 上建立一个非聚集索引。
按照AdventureWorks里原先的数据， header_test 里面有3万多条订单信息，detail里有12万多条订单详细记录，基本上一条订单有3-5条详细记录。这是一个正常的分布。

下面再在 header_test 里面加入9条订单记录，他们的编号是从75124 到75132这是9张特殊的订单，每张有12万多条详细记录。也就是说 deatil_test里会有90%的数据属于这9张订单。

declare @i int 
set @i = 1
while @i < 10 
begin 
INSERT INTO [AdventureWorks2008].[dbo].[SalesOrderHeader_TEST]
           ([RevisionNumber]
           ,[OrderDate]
           ,[DueDate]
           ,[ShipDate]
           ,[Status]
           ,[OnlineOrderFlag]
           ,[SalesOrderNumber]
           ,[PurchaseOrderNumber]
           ,[AccountNumber]
           ,[CustomerID]
           ,[SalesPersonID]
           ,[TerritoryID]
           ,[BillToAddressID]
           ,[ShipToAddressID]
           ,[ShipMethodID]
           ,[CreditCardID]
           ,[CreditCardApprovalCode]
           ,[CurrencyRateID]
           ,[SubTotal]
           ,[TaxAmt]
           ,[Freight]
           ,[TotalDue]
           ,[Comment]
           ,[rowguid]
           ,[ModifiedDate])
SELECT        [RevisionNumber]
           ,[OrderDate]
           ,[DueDate]
           ,[ShipDate]
           ,[Status]
           ,[OnlineOrderFlag]
           ,[SalesOrderNumber]
           ,[PurchaseOrderNumber]
           ,[AccountNumber]
           ,[CustomerID]
           ,[SalesPersonID]
           ,[TerritoryID]
           ,[BillToAddressID]
           ,[ShipToAddressID]
           ,[ShipMethodID]
           ,[CreditCardID]
           ,[CreditCardApprovalCode]
           ,[CurrencyRateID]
           ,[SubTotal]
           ,[TaxAmt]
           ,[Freight]
           ,[TotalDue]
           ,[Comment]
           ,[rowguid]
           ,[ModifiedDate]
 FROM [SalesOrderHeader_TEST]  WHERE SalesOrderID = 75123

INSERT INTO [AdventureWorks2008].[dbo].[SalesOrderDetail_TEST]
           ([SalesOrderID]
           ,[CarrierTrackingNumber]
           ,[OrderQty]
           ,[ProductID]
           ,[SpecialOfferID]
           ,[UnitPrice]
           ,[UnitPriceDiscount]
           ,[LineTotal]
           ,[rowguid]
           ,[ModifiedDate])
  SELECT 75123 + @i
           ,[CarrierTrackingNumber]
           ,[OrderQty]
           ,[ProductID]
           ,[SpecialOfferID]
           ,[UnitPrice]
           ,[UnitPriceDiscount]
           ,[LineTotal]
           ,[rowguid]
           ,GETDATE()
  FROM Sales.SalesOrderDetail
SET @i = @i + 1
 END
 GO

栏目列表