VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > 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
复制代码


相关教程