欢迎投稿

今日深度:

禁用 SQL 游标,告诉你外面听不到的原因,

禁用 SQL 游标,告诉你外面听不到的原因,


文末本文转载自微信公众号「有关SQL」,作者Lenis。转载本文请联系有关SQL公众号。

周六清晨,东方刚刚露白。

L 早早来到办公室,捎带上最爱的热焦玛。今天会是一场苦战,计划了两个月的上线产品,今天发布。他需要极其敏捷的脑子。另外,只要 L 还在喝着咖啡,说明 DB 肯定是没问题的,也能起到一点安慰军心的作用吧。所以大事件面前,L 总是拿着星巴克晃悠。谁都猜不到他到底是爱喝,还是臭显摆。

F 晃着小脑袋过来了,Release 已经开始了 1 小时,按理 DB 部分部署早该完成。这次稍微超过 L 的预期,但没有告警,大家也就没有太放心上。直到 F 过来找 L, 低头问了下:

“L, 有段更新数字的脚本,跑了40多分钟还没结束。理论上只有100多万数据会被更新,花这么长时间,不知道是否正常?”

F 是个五年陈了,该经历的也都经历了,如今冒出这么个疑问,L 也是慎重起来。“哪段脚本?”

  1. SET NOCOUNT ON ; 
  2.  
  3.  
  4. DECLARE @SalesQuotaKey Bigint 
  5.  
  6.  
  7. DECLARE MY_Cur Cursor For 
  8. SELECT TOP 1000000 SalesQuotaKey 
  9. FROM FactSalesQuotaAudit  
  10. WHERE SalesAmountQuota<500000 
  11. ORDER BY SalesAmountQuota ASC 
  12.  
  13.  
  14. OPEN MY_Cur   
  15. FETCH NEXT FROM MY_Cur INTO @SalesQuotaKey 
  16.  
  17.  
  18. WHILE(@@FETCH_STATUS = 0 ) 
  19. BEGIN 
  20. UPDATE FactSalesQuotaAudit  
  21. SET SalesAmountQuota = SalesAmountQuota + 100000 
  22.         WHERE SalesQuotaKey = @SalesQuotaKey  
  23.  
  24. FETCH NEXT FROM MY_Cur INTO @SalesQuotaKey  
  25. END 
  26.  
  27.  
  28. CLOSE MY_Cur  
  29. DEALLOCATE MY_Cur 

“嗯,这段貌似会有问题,就看索引是怎么建的”L 常说,trouble shooting 就像是做侦探,有时候,话其实是说给自己听的,“如果在 SalesAmountQuota 上加索引的话,这就有危险”

“果不其然”,L打开 SSMS窗口,找到了索引定义:

  1. CREATE Unique CLUSTERED  index PK_SalesQuotaKey  
  2. ON FactSalesQuotaAudit(SalesQuotaKey) 
  3.  
  4. CREATE INDEX IDX_SALES_AMT_QUTA  
  5. ON FactSalesQuotaAudit(SalesAmountQuota) 

为保分析无误,L 还是先看了下现状:

  1. SELECT TOP 1000000 SalesQuotaKey 
  2. FROM FactSalesQuotaAudit  
  3. WHERE SalesAmountQuota<500000 
  4. ORDER BY SalesAmountQuota ASC 

“目前来看,这段脚本还在继续跑着”

“但执行计划显示正确跑了 SalesAmountQuota 的索引呢?”F 不解

“其实这里真是这个索引惹的祸”

“索引是用到了,但是每次更新,更新的那行跑到 IDX_SALES_AMT_QUTA 索引后面去了,导致无限在更新 SalesAmountQuota 的值,直到大于 50万”L 觉得平时太强调 seek 索引了,但没有全面透彻的讲解索引其实也有好心办坏事儿的时候。所以索引要给 F 画个脑图:

“更新完的数据又排回索引了,而游标一直在往前读满足条件的数据,你可以细想下这个有趣的过程”看到 F 频频点头,L 自以为已经讲的很明晰了。

"终于跑完了," F 眼见监控 Dashboard 上的那个超长 session 消失了,脸色也开始和悦起来。

“大错即将发生”L 一盆冷水浇过去,F 又不惑,90后小姑娘的脸色,真是跟天气一样,瞬间都能千变万化。

  1. SELECT COUNT(*)  
  2. FROM FactSalesQuotaAudit WITH(NOLOCK) 
  3. WHERE SalesAmountQuota<500000 

“你看,结果是0,肯定不是你想要的结果吧。你原意肯定是在不满50万额度的那些销售上,再加十万,现在全部都加到了50万。这是典型的 Halloween 问题”

“那,怎么办?”F 面对这段让她面红耳赤的游标,简直奔溃

“用临时表,先把数据更新对了,再找最优解决方法”

"那什么是 Halloween 问题?"

故事发生在 50年前的一个晚上,1970年左右,IBM 的一群研究员决定给不满25000美金年薪的雇员,增加10% 的薪水。

他们写了一段 SQL,大意是这样的:

  1. update Employee 
  2. Set Salary = Salary * (1 + 10%) 
  3. where Salary < 25000 

结果等他们运行完毕,发现所有的年薪不满 25000 美金的雇员,他们的薪水统统加到了 25000.

例如,原本是 15000薪水的雇员和 8000 美金年薪的雇员,他们的薪水更新完了之后,都到了25000 美金。这一天正好是 10月31日,Halloween Day. 所以被称为 Halloween Problem.

www.htsjk.Com true http://www.htsjk.com/shujukukf/43847.html NewsArticle 禁用 SQL 游标,告诉你外面听不到的原因, 文末本文转载自微信公众号「有关SQL」,作者Lenis。转载本文请联系有关SQL公众号。 周六清晨,东方刚刚露白。 L 早早来到办公室,捎带上最...
评论暂时关闭