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

（图拍摄于办公室落地窗口）

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

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

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

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

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

SET NOCOUNT ON ;




DECLARE @SalesQuotaKey Bigint




DECLARE MY_Cur Cursor For
SELECT TOP 1000000 SalesQuotaKey
FROM FactSalesQuotaAudit 
WHERE SalesAmountQuota<500000
ORDER BY SalesAmountQuota ASC




OPEN MY_Cur  
FETCH NEXT FROM MY_Cur INTO @SalesQuotaKey




WHILE(@@FETCH_STATUS = 0 )
BEGIN
UPDATE FactSalesQuotaAudit 
SET SalesAmountQuota = SalesAmountQuota + 100000
        WHERE SalesQuotaKey = @SalesQuotaKey 


FETCH NEXT FROM MY_Cur INTO @SalesQuotaKey 
END




CLOSE MY_Cur 
DEALLOCATE MY_Cur

‍

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

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

CREATE Unique CLUSTERED  index PK_SalesQuotaKey 
ON FactSalesQuotaAudit(SalesQuotaKey)


CREATE INDEX IDX_SALES_AMT_QUTA 
ON FactSalesQuotaAudit(SalesAmountQuota)

为保分析无误，L 还是先看了下现状：

SELECT TOP 1000000 SalesQuotaKey
FROM FactSalesQuotaAudit 
WHERE SalesAmountQuota<500000
ORDER BY SalesAmountQuota ASC

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

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

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

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

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

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

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

SELECT COUNT(*) 
FROM FactSalesQuotaAudit WITH(NOLOCK)
WHERE SalesAmountQuota<500000

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

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

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

"那什么是 Halloween 问题？"

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

他们写了一段 SQL，大意是这样的：

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

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

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

--完--

往期精彩：

本号精华合集（三）

外企一道 SQL 面试题，刷掉 494 名候选人

我在面试数据库工程师候选人时，常问的一些题

零基础 SQL 数据库小白，从入门到精通的学习路线与书单