浅析SQL SERVER执行计划中的各类怪相

jinying8869

　　在查看执行计划或调优过程中，执行计划里面有些现象总会让人有些疑惑不解：
　　    1：为什么同一条SQL语句有时候会走索引查找，有时候SQL脚本又不走索引查找，反而走全表扫描？
　　    2：同一条SQL语句，查询条件的取值不同，它的执行计划会一致吗？
　　    3: 同一条SQL语句，其执行计划会变化，为什么
　　    4: 在查询条件的某个或几个字段上创建了索引，执行计划就一定会走该索引吗？
　　    5：同时存在几个索引，SQL语句会走那个索引?
　　     ............................................................
　　有时候如果要跟别人解释清楚这些问题，如果不通过一些案例或例子来解说，很难阐述清楚，一方面是表达能力问题。另外一方面，再华丽的语言也难敌眼见为实，毕竟人接受信息大部分通过眼睛，小部分通过耳朵。眼见为实耳听为虚吗！　　下面来看一个简单的例子，为什么我在对应的查询字段上建有索引，但是它不走索引反而走全表扫描。


DROP TABLE TEST    CREATE TABLE TEST (OBJECT_ID  INT, NAME VARCHAR(8));    CREATE INDEX PK_TEST ON TEST(OBJECT_ID)    DECLARE @Index INT =0; WHILE @Index < 20BEGIN    INSERT INTO TEST    SELECT @Index, 'kerry';        SET @Index = @Index +1;END  UPDATE STATISTICS TEST WITH FULLSCAN SELECT * FROM TEST WHERE OBJECT_ID=1　　
　　
　　
　　已经在查询字段OBJECT_ID上建立了索引，为什么SQL优化器不走索引，而要走全表扫描呢？为了说明白，那么我们借助于查询提示（Hints）强制优化器走索引查找来说明上述情况，对比走索引查找、全表扫描两者的代价开销，从下图，我们可以看到当前情况下，走全表扫描的开销要小于索引查找。因为当前情况下，走索引需要额外的IO开销，反而不如全表扫描。所以优化器选择了走全表扫描而非索引查找。很多开发人员有种根深蒂固的固执观念“走索引查找一定要优于全表扫描”（我跟他们解释的时候，很多人不相信，"慷慨激昂"的质疑我，以至于我的解释都显得苍白无力）,大多数情况下，走索引查找要优于全表扫描，但是在特定的场景、特定数据情况下，会出现全表扫描优于索引查找的情况。尤其是ORACLE里面，很多做开发的同事一看到SQL执行计划走全表扫描，立马大呼小叫。其实完全是先入为主的观念作怪。




SELECT * FROM TEST WHERE OBJECT_ID=1  SELECT * FROM TEST WITH(INDEX=PK_TEST) WHERE OBJECT_ID =1 　　
　　

　　　　两者开销不一致，其实在IO开销这一块，可以从下面看出逻辑读取的差异。




DBCC FREEPROCCACHE;DBCC DROPCLEANBUFFERS;SET STATISTICS IO ON; SELECT * FROM TEST WHERE OBJECT_ID=1　　
　　
　　
　　




DBCC FREEPROCCACHE; DBCC DROPCLEANBUFFERS; SET STATISTICS IO ON; SELECT * FROM TEST WITH(INDEX=PK_TEST) WHERE OBJECT_ID =1 　　
　　
　　

　　
　　　　那么接下来，我们将该表的数据从20条记录增长到10000条记录，你觉得执行计划会变化吗？大家不妨先思考一下这个问题，再看下文。




TRUNCATE TABLE TEST;DECLARE @Index INT =0; WHILE @Index < 10000BEGIN    INSERT INTO TEST    SELECT @Index, 'kerry';        SET @Index = @Index +1;END  UPDATE STATISTICS TEST WITH FULLSCANSELECT * FROM TEST WHERE OBJECT_ID=1
　　
　　如下所示，当数据变化时，优化器认为走索引查找要优于全表扫描，所以选择了索引查找，说到底优化器是基于成本的优化器，在众多的执行计划中，它会选择代价开销最小的一个执行计划。
　　
　　
　　
　　此时，强制优化器走全表扫描，对比开销结果，你会发现结果完全跟上面结果相反。
　　
　　
　　　　我如果更新该表数据，使其分布完全倾斜，那么你可以看到对于同一个SQL，不同的取值，它的执行计划也会完全不同。




UPDATE TEST SET OBJECT_ID =1 WHERE OBJECT_ID='2013-07-09 00:00'   AND CREATE_DATE ='2013-07-09 00:00'   AND CREATE_DATE ='2013-04-09 00:00'   AND CREATE_DATE ='2013-04-09 00:00'   AND CREATE_DATE ='2013-04-09 00:00'   AND CREATE_DATE ='2013-04-09 00:00'   AND CREATE_DATE