数据库查询慢分析 (续)

SQL

一. 信息收集

根据运营数据库的运营情况, 会出现死锁的问题。
根据对数据库wait time statistics的分析, 我们知道互斥锁的占用时间最高。
根据对两个数据库 QPAccountsDB 和 QPTreasureDB 中存储过程运行时间的跟踪, 发现有关Accountsinfo 和 Gamescoreinfo 两个表操作的存储过程占用时间最多。

二. 信息分析

1. 综上我们可以得出针对Accountsinfo 和 Gamescoreinfo 的两个表的操作的不合理导致了数据库资源占用率高。
2. 因为在QPTreasureDB中统计的存储过程发现只有GSP_GR_UserTakeScore这一个存储过程, 在QPAccountsDB中涉及了三个, 所以我们从QPTreasure的UserTakeScore入手干扰项更少。
3. 我们再使用 profiler 跟踪 GSP_GR_UserTakeScore 这个存储过程的每一个语句的消耗情况, 发现一个Update的消耗是最高的, 不止如此, 在update的操作之前添加了几句原本存储过程里面没有的语句(来自于触发器), 而这几个语句对资源的消耗很大。
   
   所以, 解决这个问题的第一步我们确定到对这两个句子的修改(Update 语句 和 触发器中的exists判断)。
4. 那么针对这两个句子那个一优先级更高, 我们来看这个执行计划。初步分析是这个Update的问题, 因为如果是触发器的问题, 那么影响的应该不止这一个存储过程, 应该很多的存储过程的消耗都会很高。
   
   根据分析结果我们对这两个语句进行处理

-- 1 
UPDATE GameScoreInfo SET Score=Score+@VariationScore, InsureScore=InsureScore+@VariationInsure, Revenue=Revenue+@InsureRevenue WHERE UserID=@dwUserID
-- 2
IF ((@insertedScore-@deletedScore)<>0 or (@insertedInsure-@deletedInsure<>0) and (@dwUserID not in (SELECT UserID FROM QPAccountsDB.dbo.AccountsInfo WHERE IsAndroid=1)))

但是根据对执行计划的分析, 发现触发器的消耗是UPdate的三倍, 所以我们把这两个句子分析处理的优先级设置为一样, 因为update涉及的知识点比较少, 所以我们从Update开始。

三. 处理方案

图解本次解决问题的大致流程

处理流程:

Update 语句分析

A. Update语句和执行计划, 发现十分的正常。

B. Update 索引优化?

如果这个问题的主要的原因真的出在update之上, 我们的推断就是索引优化的问题。为了验证我们的猜想, 我们去对比多个表的update。

测试代码

-- 多个update的对比
UPDATE QPTreasureDB.dbo.GameScoreInfo SET Score=1120957  WHERE UserID=2005
UPDATE QPAccountsDB.dbo.AccountsInfo SET GameID =1120957 WHERE UserID=2005
UPDATE QPTreasureDB.dbo.SignLog SET Score=500 WHERE ID=1894178
UPDATE QPTreasureDB.dbo.SignDay SET OneDay=0 WHERE ID=1
--select top 1 * from  QPTreasureDB.dbo.SignDay 
GO

没有异常

综上我们得到结论:

Update没有明显的问题。
触发器不会影响原本Update的效率。

那么好了我们进行触发器的分析

触发器分析

触发器机能维护的消耗

我们这个触发器而言, 触发器维护机能的资源消耗 > 触发器本身的消耗。[见下图]

-- 测试代码
-- Disable Trigger Test
alter table QPTreasureDB.dbo.GameScoreInfo
disable trigger truGameScoreInfo 
-- 模拟Trigger关键句子 Begin
Declare @dwUserID int
set @dwUserID = 2005
IF EXISTS ((SELECT UserID FROM QPAccountsDB.dbo.AccountsInfo WHERE UserID=@dwUserID And IsAndroid=0))
Begin
    print 'hello'
End
-- 模拟Trigger关键句子 End
UPDATE QPTreasureDB.dbo.GameScoreInfo SET Score=Score+1, InsureScore=InsureScore+1, Revenue=Revenue+1 WHERE UserID=2005
print 'Disable Over'
GO
--- 分割线 --- Disable/Enable Trigger
-- Enable Trigger Test
alter table QPTreasureDB.dbo.GameScoreInfo
enable trigger truGameScoreInfo 
UPDATE QPTreasureDB.dbo.GameScoreInfo SET Score=Score+1, InsureScore=InsureScore+1, Revenue=Revenue+1 WHERE UserID=2005
print 'Enable Over'
go

测试结果:

所以, 我们的方案是是否可以简化这个触发器:

• 触发器对我们这个操作的影响在 1ms 以下, 不是我们要处理问题的关键。
• 如果尝试采用触发器的关键语句写入存储过程的方法, 可以绕过触发器的消息触发机制因为我们这个触发器的目的是为了检测非正常的情况, 所以这个触发器机制需要保留。

所以我们暂时不用考虑是否屏蔽触发器操作

优化触发器本身的语句

我们提出来这里面消耗最大的一个句子。

if @dwUserID not in (SELECT UserID FROM QPAccountsDB.dbo.AccountsInfo WHERE IsAndroid=1)

查看执行计划我们发现, 这个执行计划里面存在一个效率比较低的聚集索引扫描.

从逻辑上来看, 在子查询中的搜索条件是IsAndroid=1这个条件显然比较宽泛, 并且IsAndroid本身并不能提供很好的索引方案, 出现了表扫描的情况。
所以, 我们用下面的方法替换这个语句, 搜索条件加入聚集索引UserID, 而且也避免了not in的使用。

IF EXISTS ((SELECT UserID FROM QPAccountsDB.dbo.AccountsInfo WHERE UserID=@dwUserID And IsAndroid=0))

两个语句的效率比较:

-- 测试代码
Declare @dwUserID int
set @dwUserID = 2005
IF EXISTS (SELECT UserID FROM QPAccountsDB.dbo.AccountsInfo WHERE UserID=@dwUserID And IsAndroid=0)
begin
    print 'yes'
end
IF (@dwUserID not in (select userid FROM QPAccountsDB.dbo.AccountsInfo WHERE IsAndroid=1))
begin
    print 'yes'
end
GO

效率提升了两倍还要多一些

最后总结

一个高频率使用的触发器中的一个关键查询中使用了索引扫描, 没有达到效率最大化

相关链接

• 看懂执行计划
• 索引扫描和索引查找
• 触发器缺点
• SQL语句优化
• 深入理解执行计划

测试技巧

• 在SQL SERVER查询编辑器中, 一次运行两个语句可以对比两个
  这幅图表明第二句对于AccountsInfo的消耗远远超过。
  
  值得注意的是, 这个比例是并不精确, 应该是一个估计值, 具体数据还需验证.查看性能还是看Profiler里面的Reads, Cpu, 和 Duration 三个选项. 这个方法只是作为比较两个几个句子性能优劣的简单测试