SQL优化、谓词、执行顺序、with公共表表达式

[spiritofdragon]

此问题出处为：http://bbs.csdn.net/topics/391948703?page=1#post-401131557

我新开贴总结下，并发问，求加精！
----根据第一大段，我得出个结论，谓词里，靠后的条件，只会遍历靠前的条件筛选出的结果集，而不会遍历全表了。（根据下面报错与否得出的结论，哪句没报错说明它没遍历全表），注：谓词顺序不是where语句顺序，需要看执行计划。
--所以，我又得到一个“貌似”正确的结论，不同运算符 会影响谓词顺序：1.Equal谓词2.Range谓词3.In列表4.Like谓词（网上搜的，我也认可了）

with t(id,Result) as 
(
select 1,'2.2' union all
select 2,'3.3' union all
select 3,'2...2' 
)
select * into #A from t;
select * from #A where cast(Result as decimal(18,5))>3 and id in(1,2)  --谓词里：先执行cast(Result as decimal(18,5))>3，后执行id in(1,2)。报错
select * from #A where id in(1,2) and cast(Result as decimal(18,5))>3  --因为，in等级靠后，即使写前面，谓词里也会排后面，顺序同上。报错
select * from #A where cast(Result as decimal(18,5))>3 and id <=2      --">" 与 "<="同级，但有函数的等级估计比没函数的等级低，估计不报错
select * from #A where id <=2 and cast(Result as decimal(18,5))>3      --百分百不报错
;

----但是根据第二大段，with通用表达式，做的表，它的谓词顺序，会受其他因素影响，哪这是啥因素呢？

with t(id,Result) as 
(
select 1,'2.2' union all
select 2,'3.3' union all
select 3,'2...2' 
) 
select * from t where id =2 and cast(Result as decimal(18,5))>3  --都等号了，优化器还把它的谓词顺序调到后面，这是为啥？

那以后优化with语句时，怎么优化？

[一个人的街-1921]

[LongRui888]

看了你的问题，还有上面的回复。 实际上你要问的就是 ，如果有多个谓词，那么sql在用这些谓词过滤数据的时候，是否有一定的规则。。 从上面你的实验结果来看，就是你得到的结论就是，如果是表，那么会按照一定的顺序，比如先=，后range，最后才是like。 但如果用的是with，从实验来看，就违反了这个规则。你想知道是为什么会有这种情况。 而这种情况，直接有可能导致sql在运行时报错，这种报错，我在实际工作中也是经常遇到的。。。

[LongRui888]

我也觉得，现在大家都是在通过结果反推 规则，而现在产生了一个和之前总结出的规则的，矛盾的结果。 我还是觉得，你可以试试联系微软的开发组人员，给他们发个邮件问问，他们在这个上是如何处理的。

[xxfvba]

学习了，---------

[spiritofdragon]

楼上把我的问题，描述的很清楚，也是我想知道的！ 但通过测试结果，实体表，基本上结论还是有定型的，with却违反了总结的规则。 刚才又试了试。那个语句是因为，常量表，而不是with，因为常量表通常是用with定义，所以我以为是用了with。 另外，真没给微软写过信问问题。从没有过这个思路，但这信些出去，是否还要英文啊，中文描述问题都挺困难，英文更描述不清了。另外，哪个邮箱能收这种信啊？

[spiritofdragon]

请看清我提出的前提。要看谓词，不是看where语句。 再看我第一大段的语句，每句的谓词 的顺序。有报错也有不报错了。 不报错的意思就是那个转型根本没有遍历全表，而是只遍历的前一个谓词的结果集。 所以我有个前提结论

谓词里，靠后的条件，只会遍历靠前的条件筛选出的结果集，而不会遍历全表了

你现在的反问，都是在针对这个，但你举例都是用where，而不是谓词，你看了每句的谓词了没？ 我想说的是这个结论，到我目前的测试语句中都成立，还没有发现不成立的。 所以，我现在要做的事，

如何优化，才能上谓词的顺序，随我的要求变化

，即我知道了某些优先级，写where时就能控制谓词顺序。 但with打破了我前面总结的优先级

1.Equal谓词2.Range谓词3.In列表4.Like谓词

所以，才发问，想知道有with 时，有啥优先级。

[spiritofdragon]

我想讨论的是： 因为，如果where里有多个条件时，优化器生成的谓词，它的顺序跟我们手写where的顺序是不同的（但生成这顺序的规律是啥，我很想知道），而且谓词的顺序既是筛选顺序（我的测试结论）。 所以，我想总结下，怎么写where条件能让 优化器生成的谓词 尽可能的 “听话”，达到有效优化的结果。

[spiritofdragon]

引用 2 楼 misterliwei 的回复:

谓词的顺序应该是SQL SERVER优化器基于成本进行优化的结果，应该没有固定的顺序。总结不起来。

优化器也是人开发的，如果开发者看到了这个问题，他肯定知道，因为算法是他(们)写的，我们在这里只能从测试语句中反推出，这算法中谁优先级高。才讨论下。希望有比较贴近的答案。那么以后优化sql时，就多了一条路。

[中国风]

with t(id,Result) as 
(
select 1,'2.2' union all
select 2,'3.3' union all
select 3,'2...2' 
)
select * into #A from t;


--1.
SELECT * from #A where id*1 <=2 and cast(Result as decimal(18,5))>3			
--2.
select * from #A where CAST(id AS INT) <=2 and cast(Result as decimal(9,5))>3



--3.
select * from #A where CAST(id AS DECIMAL(9,5)) <=2 and cast(Result as decimal(9,5))>3	

--4.
select * from #A WHERE CAST(Result as decimal(9,5))>3	AND  CAST(id AS DECIMAL(9,5)) <=2	

--要掌握用以上方法动手去测 --以上的开销成本相同的,所以不存在成本开销问题,条件是否报错的取决于表达式的运算优先级（语句1/2），当表达式相同时取决于条件先后（3/4） --分析器运算， --1.分析器会把运算符号列id*1低于转换类型列cast(Result as decimal(18,5)) --2.转换类型列cast(Result as decimal(18,5))低于id不需要转换列

[spiritofdragon]

引用 4 楼 x_wy46 的回复:

有点变形，我重新贴出来最后一段代码 create index idx_id on #A(id) select * from #A with (index(idx_id)) where cast(Result as decimal(18,5))>3 and id in(1,2) --谓词里：先执行cast(Result as decimal(18,5))>3，后执行id in(1,2)。不错啦！ select * from #A with (index(0)) where cast(Result as decimal(18,5))>3 and id in(1,2) --谓词里：先执行cast(Result as decimal(18,5))>3，后执行id in(1,2)。报错 --这里报错就是因为走了全表扫描，全表扫描的情况下 --对于id 是不是等于 2 和 cast(Result as decimal(18,5)) 是不是大于 3 这两部是同步的进行校验的

我当然知道有索引时，会走索引，那么 id in(1,2) 不会被认为是谓词，优化器会认为这是 “索引查找”，然后再执行谓词，但此时谓词就剩一个了。这种优化，我当然理解，加索引呗。 但，请看2楼，我早排除了索引条件了，假设有两个条件，它们都非索引字段。比如，id in(1,2) 我改成 version in (‘version：1.0’,‘version：1.1’) ，而version这种字符串字段，不方便加索引，那么就是，这贴讨论的问题。优化器，最终会依靠什么逻辑，生成谓词顺序？

[spiritofdragon]

引用 9 楼 roy_88 的回复:

with t(id,Result) as 
(
select 1,'2.2' union all
select 2,'3.3' union all
select 3,'2...2' 
)
select * into #A from t;


--1.
SELECT * from #A where id*1 <=2 and cast(Result as decimal(18,5))>3			
--2.
select * from #A where CAST(id AS INT) <=2 and cast(Result as decimal(9,5))>3



--3.
select * from #A where CAST(id AS DECIMAL(9,5)) <=2 and cast(Result as decimal(9,5))>3	

--4.
select * from #A WHERE CAST(Result as decimal(9,5))>3	AND  CAST(id AS DECIMAL(9,5)) <=2	

--要掌握用以上方法动手去测 --以上的开销成本相同的,所以不存在成本开销问题,条件是否报错的取决于表达式的运算优先级（语句1/2），当表达式相同时取决于条件先后（3/4） --分析器运算， --1.分析器会把运算符号列id*1低于转换类型列cast(Result as decimal(18,5)) --2.转换类型列cast(Result as decimal(18,5))低于id不需要转换列

老大，测试的结论基本我也测了，也得出差不多的结论。我一楼的第一大段。 但你看看我1楼的第二大段，真反人类啊~~~。死活 谓词的顺序 不听话

[走你_]

引用 3 楼 x_wy46 的回复:

with t(id,Result) as ( select 1,'2.2' union all select 2,'3.3' union all select 3,'2...2' ) select * from t where id =2 and cast(Result as decimal(18,5))>3 --都等号了，优化器还把它的谓词顺序调到后面，这是为啥？

--原理很简单，瞎弄清楚sqlserver是如何完成一个查询的
--看看sqlserter是怎么去执行这个查询的

select * from t where id =2 and cast(Result as decimal(18,5))>3  
--都等号了，优化器还把它的谓词顺序调到后面，这是为啥？

为啥为啥为啥？你说为啥
几遍是前面是等号了，不代表sqlserver仅仅去查询 id =2之后就完事了 ，
这里她做了全表扫描，全表扫描，全表扫描
你知道啥意思吧？
既然做了全表扫描，她就回去同时校验 id 是不是等于 2 和  cast(Result as decimal(18,5)) 是不是大于 3  
结果就是你懂得，遇到id=3的时候，cast(Result as decimal(18,5)) 报错


怎么证明我的说法？
很简答
试试如下sql，你就明白了


create index idx_id on #A(id)
select * from #A with (index(idx_id)) where cast(Result as decimal(18,5))>3 and id in(1,2)  --谓词里：先执行cast(Result as decimal(18,5))>3，后执行id in(1,2)。不错啦！

select * from #A with (index(0)) where cast(Result as decimal(18,5))>3 and id in(1,2)  --谓词里：先执行cast(Result as decimal(18,5))>3，后执行id in(1,2)。报错

建议你这样执行下试试

with t(id,Result) as 
(
select 1,'2.2' union all
select 2,'3.3' union all
select 3,'2...2' 
) 
select * into #a from t
select * from #a where id =2 and cast(Result as decimal(18,5))>3 

按你的理论 这么执行也会报错 但是实际上是通过的

[中国风]

动手测，上面的例子同你的应该是不同的，1/4会报错 如果两个条件都是IN那就是取决于先后顺序 比如：这样会报错 select * from #A WHERE CAST(Result AS DECIMAL(9,5)) IN(3.3,2.2) AND CAST(id AS INT) IN(1,2)

[spiritofdragon]

第一个建议，我认同，10楼我说了，索引查找，比谓词的执行优先级，还靠前，我认可。我现在测试的是排除了索引。 第二段：会看谓词么？这样通过是因为谓词里，id=2被编译到前面了。当然通过。我的理论是：一：1.Equal谓词2.Range谓词3.In列表4.Like谓词。二：另外字段在函数里（或表达式里），优先级比，字段单独在运算符一遍，优先级要低。 但一、二，这是两个维度，具体怎么交叉，我还没结论。

[spiritofdragon]

老大，是这个语句

with t(id,Result) as 
(
select 1,'2.2' union all
select 2,'3.3' union all
select 3,'2...2' 
) 
select * from t where id =2 and cast(Result as decimal(18,5))>3

不管是按照你的逻辑，还是我的测试结论，都会认为，先执行id=2，后执行cast(Result as decimal(18,5))>3，那么这句貌似就不会报错了。但但但，
请看计划，
id =2被编译到后面去了，唯一的区别就是 ，这句，用了with子查询，不是用 物理表。
所以，难道以后，用with优化就不能优化了么？

[中国风]

CTE是虚拟表，在虚拟表用常量扫描，条件都需要满足列所有值转换类型，不满足都会报错 #A临时表，是按物理顺序表扫描，取决于成本最低的执行计划，比如ID列的值占用字节大，那会按Result 去执行 在用临时表或表时，需要考虑 cast(Result as decimal(18,5))--这一段不转换也会有隐性转换，都会低于id不转类型的条件 当ID为IN时，会把二元运算符转为N元运算符 拆成 ID=1 or ID=2 这样拆分的条件会低Result条件范围和常量值，范围又会取决于物理顺序如有聚集索引条件会优于非聚集索引

[ArayChou]

引用 19 楼 yupeigu 的回复:

从上面你的实验结果来看，就是你得到的结论就是，如果是表，那么会按照一定的顺序，比如先=，后range，最后才是like。

没有SQL Server的经验。以我在oracle里的经验来说，这条规则在绝大多数时候都是适用的。但是并不是每次都这样。

我做了一个实验：

-- Create table
create table TEST_ARAY
(
  a NUMBER(1),
  b VARCHAR2(10)
);
-- Create/Recreate indexes 
create bitmap index IDX_A on TEST_ARAY (A);
create index IDX_B on TEST_ARAY (B);

反复执行这个语句插入数据： insert into test_aray select mod (rownum , 2),rownum from test_aray;

SQL> select * from test_aray where rownum<10;
A B
-- ----------
0 262
1 263
0 264
1 265
0 266
1 267
0 268
1 269
0 270
9 rows selected

SQL> select count(*) from test_aray;
COUNT(*)
----------
1048576

SQL> desc test_aray;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A NUMBER(1) Y
B VARCHAR2(10) Y

SQL> set autotrace on;
Cannot SET AUTOTRACE

SQL> select count(*) from test_aray where a='0' and b like '100000%';
COUNT(*)
----------
3

SQL> explain plan for
2 select count(*) from test_aray where a='0' and b like '100000%';
Explained

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3931327764
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 23 (0)|
| 1 | SORT AGGREGATE | | 1 | 20 | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TEST_ARAY | 23 | 460 | 23 (0)|
|* 3 | INDEX RANGE SCAN | IDX_B | 23 | | 3 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"=0)
3 - access("B" LIKE '100000%')
filter("B" LIKE '100000%')
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement
21 rows selected

SQL>

重上面的执行计划来看，like优先于equal。 结论：oracle基于成本优化，会自动记录些额外的统计数据，用来评估那个执行计划最优。

PS：如果oracle选择了错误的执行计划（评估失败），只需要重新分析表就会修正。

[misterliwei]

这儿有一篇关于DB2数据库的文章可以借鉴： Programmers Only: Does the Order of SQL Predicates Matter? 文章是讨论使用什么样的WHERE谓词顺序才能获得性能优化。从中我们可以看出DB2是如何处理谓词顺序的。 首先是根据索引列顺序；对于非索引列，按下面的顺序：1）相等谓词；2）范围谓词；3）IN或LIKE谓词。同类谓词按编码顺序处理。

[cxjd12]

[nongfuxu123]

学习了,感谢分享.