SQLSERVER中CTE(WITH AS)语句的优化器行为分析

momo314相同方式共享非商业用途署名转载

话说,今天遇到一个问题,是这样的:

SQLSERVER中有一张表,其中一个备注字段[MEMO],这个字段呢,是字符串类型的,但是其中也会存一些Id之类的数字类型的值。现在呢,需要把则个字段中数值类型的值小于 2 的查出来,怎么办呢?

OK,看起来很简单,我们来模拟一下,假设下面这张 @SOURCE 表就是我们的数据源

DECLARE @SOURCE TABLE
(
    [Id] INT IDENTITY(1, 1),
    [MEMO] NVARCHAR(5)
)

INSERT INTO @SOURCE
SELECT 'A' UNION
SELECT 'B' UNION
SELECT 'C' UNION
SELECT 'D' UNION
SELECT '1' UNION
SELECT '2' UNION
SELECT '3' UNION
SELECT '4'

SELECT * FROM @SOURCE

谁都知道直接写 SELECT * FROM @SOURCE WHERE CAST([MEMO] AS INT) < 2 肯定会报错,因为我们没办法把字母转换为数字。

那么,首先,我们肯定要把[MEMO]字段为数值类型的数据单独搞出来,然后再对其进行查询应该就OK了吧?大致有下面几种写法:

方案一:

SELECT * 
FROM
(
    SELECT * FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
) T
WHERE CAST([MEMO] AS INT) < 2

这种写法必然是不行哒(●′ω`●),因为显然优化器会自动将语句优化为直接查询而非子查询,那么也就是说,我们必须要保证语句在执行的时候是强制执行子查询的,于是就有了方案二三四。

方案二/方案三

--方案二/三分别使用 临时表#TEMPSOURCE 和 表变量@TEMPSOURCE 的方式
SELECT * INTO #TEMPSOURCE FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
SELECT * FROM #TEMPSOURCE WHERE CAST([MEMO] AS INT) < 2
DROP TABLE #TEMPSOURCE

毫无疑问,可以正常执行。因为加入了临时表,导致了第一步子查询结束后的INSERT操作,这个操作是不会优化掉的,所以可以保证第二步的查询中的[MEMO]一定是数值类型。

方案四

WITH TEMPSOURCE 
AS
(
    SELECT * FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
)
SELECT * FROM TEMPSOURCE WHERE [MEMO] < 2

好啦,神奇的方案四出场啦!虽然看起来跟方案二/方案三差不多,那么为什么要单独列出来呢?这个方案到底行不行呢?

OK,不卖关子,神奇海螺非常确定的告诉你:这种写法是不行哒!

在将 nvarchar 值 'A' 转换成数据类型 int 时失败。

如果你也觉得应该可以的话,那么请继续往下看:

为什么会报错呢?我明明把符合要求的数据放到 表TEMPSOURCE 中去了啊?

我们来看一下方案四的执行计划: 因为实际在执行语句的过程中会报错,所以我们只能查看预估的执行计划,而非实际执行计划 CTE语句的预估执行计划CTE语句的预估执行计划 直接表扫描得到结果!!! Inconceivable!!!

那么,我们再来看看方案二和方案三的执行计划是什么样子的呢: 内存表语句的执行计划内存表语句的执行计划

第一步 插入内存表; 第二步 表扫描得到结果。

这才对嘛!这才是我们希望的流程呀!

那么到底为什么会导致这种差异呢?

WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。

如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。

那么重点来了,看执行计划,我们确实只对其进行了一次查询,所以根本就没有被塞到临时表里吗?

好,既然如此,那么我们就执行一个多次查询试一下:

WITH TEMPSOURCE 
AS
(
    SELECT * FROM @SOURCE WHERE ISNUMERIC([MEMO]) = 1
)
SELECT *
FROM
TEMPSOURCE A
INNER JOIN 
TEMPSOURCE B
ON CAST(A.[MEMO] AS INT) = B.[Id]
--WHERE CAST(B.[MEMO] AS INT) > 2

CTE语句多次查询的执行计划CTE语句多次查询的执行计划

果然!可以正常执行!

结论: 如果 WITH AS 短语所定义的表名被调用两次以上,则优化器会自动将WITH AS 短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。


疑问: 大家可能注意到了,在上面最后的一条语句中,我把 WHERE查询条件 给注释掉了。为什么呢?

  1. 因为我发现加上 WHERE条件 之后,执行又会报错,而且我查看了他们的执行计划,是一模一样的,是不是很奇怪?
  2. 如果将上面的 WHERE条件 修改为 WHERE B.[Id] > 2 ,也就是说不再涉及类型转换的话,也是可以正常执行的。

以上。百思不得其解("▔□▔)/

如果各位大侠知道原因的话,麻烦评论里写一下,共同进步 (•̀ᴗ•́)و ̑̑

✎﹏ 本文来自于 momo314的神奇海螺 ,文章原创,转载请注明作者并保留原文链接。