文盲的数据库指令优化心得：第二部分，指令变形和执行计划

本文主要是介绍文盲的数据库指令优化心得：第二部分，指令变形和执行计划，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

首先，文盲先写了四个语句，这四个预计得到的结果集是完全相同的，但是第一个语句对索引的要求最多，也是我们平时所写的语句格式，因为这么写起来字符最少，维护方便。。。。。。但是从效率和索引维护上来将，第一条语句其实不是最好的，所以，我们产生了三中变形，分别是exists子查询，apply子查询，join子查询

select id from test_index where n1>40 and n5<100 order by n5select main.id from test_index main  
inner join (select id,n5 from test_index where n5<100) app_n5 on main.id=app_n5.id  
where n1>40 and n2=n2 and b1=b1 and d1=d1  
order by app_n5.n5 select a.id from (select id from test_index where n1>40 and n2=n2 and b1=b1 and d1=d1) a
cross apply(select n5 from test_index where n5<100 and id=a.id) n5
order by n5select a.id from test_index a where n5<100
and exists(select top 1 1 from test_index where id=a.id and n1>40 and n2=n2 and b1=b1 and d1=d1)
order by n5

exists 是直接跟随在where之后的，但是，这个子查询得到的结果是无法参与下一步计算的，比如order，所以可以看到第四个语句的查询主条件已经变了，这个说实话，不太方便，那么 exists 的使用范围就限定了，只能是条件，但不能参与更进一步的运算

再看看join，这个关联查询到时可以返回多个字段参与下一步运算，但是join语句无法直接使用主语句返回的结果，所以如果需要使用主查询的结果集，就需要把运算写在on语句里，十分麻烦，尤其是无法直接使用表值函数对主查询的值进行计算，这点反而是exists可以做到

最后看看apply，首先，这个指令可以直接使用主查询的结果，其次，由apply提供的列可以参与下一步计算，所以apply的适用范围更广，好了变形方式第一个知识点完毕

然后再看看这些指令的下一组比较

select id from test_index where n1>40 and (n5<100 or n5 is null)SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 18 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 5，逻辑读取 21319 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 405 毫秒，占用时间 = 815 毫秒。

select id from test_index where n1>40 and isnull(n5,0)<100SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 13 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 5，逻辑读取 21319 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 266 毫秒，占用时间 = 786 毫秒。

select id from test_index where n1>40 and n5<100
union
select id from test_index where n1>40 and n5 is nullSQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 6 毫秒。(367267 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'test_index'。扫描计数 2，逻辑读取 20936 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 172 毫秒，占用时间 = 761 毫秒。

select id from test_index a 
where n1>40 and n2=n2 and b1=b1 and d1=d1
and exists(select 1 from test_index where isnull(n5,0)<100 and id=a.id)SQL Server 分析和编译时间: CPU 时间 = 6 毫秒，占用时间 = 6 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 10，逻辑读取 10574 次，物理读取 0 次，预读 9 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 544 毫秒，占用时间 = 869 毫秒。

select id from test_index a
where n1>40 and n2=n2 and b1=b1 and d1=d1
and exists(select 1 from test_index where n5<100 and id=a.id union select 1 from test_index where n5 is null and id=a.id)SQL Server 分析和编译时间: CPU 时间 = 14 毫秒，占用时间 = 14 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 11，逻辑读取 7022 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 578 毫秒，占用时间 = 838 毫秒。

select a.id from test_index a
inner join (select id from test_index where isnull(n5,0)<100) b on a.id=b.id
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 16 毫秒，占用时间 = 18 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 10，逻辑读取 10574 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 468 毫秒，占用时间 = 824 毫秒。

select a.id from test_index a
inner join (select id from test_index where n5<100 union select id from test_index where n5 is null) b on a.id=b.id
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 11，逻辑读取 7022 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 577 毫秒，占用时间 = 818 毫秒。

select id from test_index a
cross apply (select n5 from test_index where isnull(n5,0)<100 and id=a.id) b
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 21 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 10，逻辑读取 10574 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 515 毫秒，占用时间 = 898 毫秒。

select id from test_index a
cross apply (select n5 from test_index where n5<100 and id=a.id union select n5 from test_index where n5 is null and id=a.id) n5
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 5，逻辑读取 11025992 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 6786 毫秒，占用时间 = 2340 毫秒。

select id from test_index a
cross apply (select n5 from (select id,n5 from test_index where n5<100 union select id,n5 from test_index where n5 is null) b where id=a.id) n5
where n1>40 and n2=n2 and b1=b1 and d1=d1SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。(367267 行受影响)
表 'test_index'。扫描计数 11，逻辑读取 7022 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。SQL Server 执行时间:CPU 时间 = 594 毫秒，占用时间 = 820 毫秒。

好了，同一个条件的，同样索引情况下的各种变形指令列出来了9种，可以看到，执行计划即便是全部是索引查找的也未必一定的最快的，比如最第九个语句，而在语句中存在了列计算或者or的时候，则变成了索引扫描，所以，到底一个实际的应用到底效率如何，还是需要把指令拿出来具体分析，统计一下到底如何写效率才是最高的

关于条件中出现or要用union代替，别人说过了，列参与计算会变成索引扫描，别人说过了，使用索引来提速，前一篇也说过了。。。。那么在这组比较中我们发现，第七条指令和第十条指令相对较快，执行计划相对合理，那么根据这组比较来看，我们对查询指令的优化并无定向，只能试试各种方式看看到底如何才能得到最高效率的查询方式

我么由此得到结论，指令的各种变形会直接影响到索引的使用和执行计划的规划，所以，多试试各种写法，对提高查询指令效率是有帮助的

----------------------------------------------------------------------------------------

文盲的数据库指令优化心得：第一部分，关于索引

文盲的数据库指令优化心得：第二部分，指令变形和执行计划