基于SQLServer2008的SQL调优
1. 前言
2. 实验环境
- 硬件环境:Inter(R) Core(TM)2 Duo CPU P7450 @2.12GHz 、2G内存。
- 操作系统:windows7操作系统,
- SQL Server 2008版本:Microsoft SQLServer 2008
- 所用表结构:
- Consume(ConsumeID,Cardno,ShopId,GoodsId,Amount,ConsumeDate)
- CreditCard(Cardno,OwnerName,IDCard,MaxConsume,Address,PostCode,Phone,ConsumeAmount)
- Goods(GoodId,GoodsName,TypeId,ShopId,Price)
- Shop(ShopId,ShopName)
- 记录数:Consume:686302 CreditCard:9 Goods:32 Shop:4
本实验是在SQL Server Management Studio平台上执行的。且运行时均利用 DBCC FREEPROCCACHE DBCCDROPCLEANBUFFERS DBCC FREESYSTEMCACHE (‘ALL‘ )这三条语句清理了缓冲区,从而得出相对公平的执行时间。
另外,每段实验代码均放入如下代码中间执行,这样可以得到SQL语句执行所用的时间,获取执行时间的代码如下:
- declare @begin_date datetime
- declare @end_date datetime
- select @begin_date = getdate()
- <实验SQL语句写在此处>
- select @end_date = getdate()
- select datediff(ms,@begin_date,@end_date) as ‘用时/毫秒‘
3. SQL调优
3.1 优化SELECT语句
3.1.1 连接查询的优化
子查询替换连接表
低效语句:
- select c.cardno,cc.IDCard,c.amount,c.consumedate
- from CreditCard cc left join Consume c
- on cc.Cardno = c.Cardno
- where c.Cardno = ‘9555xxxxxxx1‘
高效语句:
- select @begin_date = getdate()
- select c.cardno,cc.IDCard,c.amount,c.consumedate
- from (select * from CreditCard where Cardno = ‘9555xxxxxxx1‘) ccleft join Consume c
- on cc.Cardno = c.Cardno
执行结果分别如下:
3.1.2控制查询的行和列
优化SELECT语句,首先需要考虑的是减少返回结果集中的数据量。在SELECT语句中,应该尽量可能不返回多余的数据,这需要从行和列两方面进行优化。
减少结果集中列的数量:
低效语句:
- select *
- from CreditCard cc left joinConsume c
- on cc.Cardno = c.Cardno
- where c.Cardno = ‘9555xxxxxxx1‘
高效语句:
- select @begin_date = getdate()
- selectc.cardno,cc.IDCard,c.amount,c.consumedate
- from CreditCard cc left join Consume c
- on cc.Cardno = c.Cardno
- where c.Cardno = ‘9555xxxxxxx1‘
执行结果分别如下:
3.1.3 慎用DISTINCT关键字
DISTINCT关键字一般用来于字段很少的表,如果在SELECT语句中查询的字段很多,则使用DISTINCI关键字反而会大大降低查询的效率。
低效语句(使用DISTINCT):
- select distinctc.cardno,cc.OwnerName,cc.IDCard,s.shopname,
- g.goodsname,c.amount,c.consumedate
- from Consume c inner join CreditCard cc
- on cc.Cardno = c.Cardno
- inner join Shop s on c.ShopId=s.ShopId
- inner join Goods g onc.GoodsId=g.GoodsId
高效语句(不使用DISTINCT):
- selectc.cardno,cc.OwnerName,cc.IDCard,s.shopname,
- g.goodsname,c.amount,c.consumedate
- from Consume c inner join CreditCard cc
- on cc.Cardno = c.Cardno
- inner join Shop s on c.ShopId=s.ShopId
- inner join Goods g onc.GoodsId=g.GoodsId
执行结果分别如下:
3.1.4 判断表中是否存在记录
有些人喜欢使用COUNT(*)来判断表中是否存在记录,例如
- select count(*) from consume
这种方法要统计表中所有记录的总数量,因此执行效率比较低。可以在select语句中使用TOP1返回表中的第一条记录来判断表是否存在记录:
低效语句:
- select COUNT(*) from Consume
高效语句:
- select top 1 ConsumeID from Consume
执行结果分别如下:
3.2 对大批量插入、修改和删除数据操作的优化
3.2.1 使用SQL脚本模拟实时批量插入数据
- -- 使用Credit数据库
- USE Credit
- GO
- -- 创建临时表#test
- CREATE TABLE #test
- (
- id int,
- name varchar(100),
- createdate datetime,
- )
- GO
- DECLARE @i as INT
- declare @strsql as varchar(8000)
- SET @i = 0
- -- 循环插入10万条记录
- WHILE @i < 100000
- BEGIN
- SET @strsql = ‘INSERT INTO #test VALUES(‘ + convert(varchar(100), @i) + ‘, ‘‘test‘‘, ‘‘‘ + convert(varchar(50), getdate()) + ‘‘‘)‘ --VALUES(@i, ‘test‘, getdate())
- exec(@strsql)
- SET @i = @i + 1
- END
下面我们对批量插入数据进行优化,可以使用WAITFOR DELAY语句进行休息,例如,让数据库引擎休息100ms的代码如下:
- WQITFOR DELAY ’00:00:00:100’
优化后的SQL脚本如下:
- -- 使用Credit数据库
- USE Credit
- GO
- -- 创建临时表#test
- CREATE TABLE #test
- (
- id int,
- name varchar(100),
- createdate datetime,
- )
- GO
- DECLARE @i as INT
- declare @strsql as varchar(8000)
- SET @i = 0
- -- 循环插入10万条记录
- WHILE @i < 100000
- BEGIN
- SET @strsql = ‘INSERT INTO #test VALUES(‘ + convert(varchar(100), @i) + ‘, ‘‘test‘‘, ‘‘‘ + convert(varchar(50), getdate()) + ‘‘‘)‘ --VALUES(@i, ‘test‘, getdate())
- exec(@strsql)
- WAITFOR DELAY ‘00:00:00:20‘
- SET @i = @i + 1
- END
3.2.2 优化修改和删除语句
如果一条UPDATE语句或者DELETE语句设计的记录过多,则执行语句的时间会很长,在执行语句的过程中,数据库服务器的CPU利用率会很高,从而影响其他用户对数据库的访问效率。当对大量数据进行更新和删除操作时,可以根据用户的具体情况来决定操作的方式。
a)如果在非工作时间执行大量更新或者删除操作,则应该尽可能地在一条语句中更新更多的记录,这是最快的操作方式。
b)如果在工作时间执行大量更行或删除操作,则应该根据实际情况将UPDATE语句或者DELETE语句拆分成多条语句,每执行一条语句休息一段时间。
下面做一个对比实验,执行下面的UPDATE语句,将表Consume中所有记录的Amount数量增加10%。
- update Consume set Amount = Amount * 1.1 whereConsumeID>0
执行结果如下:
如果希望在执行更新操作时尽可能降低数据库服务器的CPU使用率,不影响其他用户对数据库的访问,可以使用下面的SQL语句:
- update Consume set Amount = Amount*1.1where Amount<200
- waitfor delay ‘00:00:00:100‘
- update Consume set Amount = Amount*1.1where Amount>=200 and Amount<500
- waitfor delay ‘00:00:00:100‘
- update Consume set Amount = Amount*1.1where Amount>=1000 and Amount<2000
- update Consume set Amount = Amount*1.1where Amount>2000
- waitfor delay ‘00:00:00:100‘
从下图的数据可知,CPU的占用率与前面的比较降低了不少。
4. 总结
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。