曲演杂坛--使用CTE时踩的小坑:No Join Predicate

在一次系统优化中，意外发现一个比较“坑”的SQL，拿出来供大家分享。

生成演示数据:

--======================================

--检查测试表是否存在

IF(OBJECT_ID('TB2002') IS NOT NULL)

BEGIN

    DROP TABLE TB2002

END

GO

--============================

--生成测试数据并创建索引

SELECT

IDENTITY(INT,1,1) AS ID,

*

INTO TB2002

FROM sys.columns C

GO

CREATE UNIQUE CLUSTERED  INDEX IDX_ID

ON TB2002

(

    ID

)

GO

CREATE INDEX IDX_column_id

ON TB2002

(

    column_id

)

执行查询：

SELECT TOP(100) *  FROM TB2002

WHERE column_id=1

上面查询虽然列column_id上有索引，但由于该列的选择性不高，查询优化引擎根据预估行数生成“使用表扫描”的执行计划：

针对此测试环境，表扫描的确是最优的查询方式，但生产环境中我们经常遇到此类问题，由于统计信息或预估行数导致执行计划不优的情况，通常我们需要通过改写SQL来“让”查询优化引擎生成我们期望的查询方式，因此我将查询SQL优化为：

WITH T1 AS (

SELECT TOP(100) ID AS RID FROM TB2002

WHERE column_id=1

)

SELECT* FROM TB2002 WITH(FORCESEEK)

WHERE ID IN (SELECT RID FROM T1)

查询生成的执行计划为：

查询先按照索引IDX_column_id来进行查找，再按照IDX_ID进行KEY LOOKUP，这样避免了“表扫描”操作。

当然以上都是是今天的重点，重点在于我手抖了，在优化过程中一不小心漏瞧了一个字母，于是悲剧粗线了。

漏敲一个字母的SQL为：

WITH T1 AS (

SELECT TOP(100) ID AS RID FROM TB2002

WHERE column_id=1

)

SELECT* FROM TB2002

WHERE ID IN (SELECT ID FROM T1)

生成执行计划为：

先不考虑执行计划中的红叉叉，查看返回数据，我们会发现“整表的数据被返回啦”，这是什么鬼？

理论上CTE的结果集中只有RID一列，那么SELECT ID FROM T1 这个子查询应该会执行失败，我们执行以下查询

WITH T1 AS (

SELECT TOP(100) ID AS RID FROM TB2002

WHERE column_id=1

)

执行会得到以下错误：

消息 207，级别 16，状态 1，第 5 行

列名 'ID' 无效。

但对那个漏敲一个字母的SQL，查询优化引擎“赤裸裸”地忽略掉这个错误，在Nested Loops运算时只有一个“No Join Predicate”的警告，Nested Loops操作描述为“对于顶部(外部)输入的每一行，扫描底部(内部)输入，然后输出匹配的行。”，查询进行表扫描，得到一个“整表数据”的结果集T1，然后准备对结果集T1中的每一行到“CTE的结果集”中进行匹配，由于“CTE的结果集”中没有ID列，于是“莫名其妙”地认为所有行都匹配上，将整表数据都返回啦。

就好比警察抓到了一帮人，打算“在逃罪犯”系统里依次匹配每个人是不是“逃犯”，结果“在逃罪犯”系统蓝屏了，于是抓到的这帮人全成了“逃犯”，通通拉出去死啦死啦滴，还能好好玩耍了么？

--=================================================================================

群里兄弟补充,在临时表里同样有类似问题：

测试代码：

SELECT  IDENTITY( INT,1,1 ) AS ID ,

        *

INTO    TB2002

FROM    sys.columns C

GO

SELECT TOP 1

        id AS ROWID

INTO    #tmp

FROM    TB2002

GO

SELECT  *

FROM    TB2002

WHERE   id IN ( SELECT  ID

                FROM    #tmp )

--=================================================================================

惨痛教训：由于优化的是DELETE 语句，本来想着通过CTE使用NOLOCK将满足条件的ID查找出来再按照ID进行删除，检查完过滤条件没有问题，直接执行导致整表数据被删除，幸好该操作没有影响业务，并有完整备份和日志备份，最终使用“指定时间点还原”+STANDBY的方式找回数据，但想想也是后怕！建议有类似习惯的童鞋在做此类操作时，先将DELETE 修改为SELECT,确保返回数据是要删除数据后，再执行DELETE。

--=================================================================================

照例是妹子镇贴和压惊

巴特西

曲演杂坛--使用CTE时踩的小坑:No Join Predicate

最新文章

热门文章