22,207
社区成员
发帖
与我相关
我的任务
分享关于执行计划的解读，聚集索引，in的方式查询
前几天一个一同学在QQ上问
--tableTest表,ID上建立了聚集索引
select * from tableTest where id in (1,9999,12345);
类似这样一个查询，id上有聚集索引，
应该是受到IN的查询方式用不到索引的影响，不太敢用，但试着用in的方式去执行，也不见得慢，
因为没有理论证明，所以感觉不放心，
说是全部放到in里面一次性查出来块还是一个一个地查快
所以在讨论这个问题
我首先看了IO，比如三个ID，
一个一个查，逻辑IO是3（3次就是9了），
放到in 里面一次性查询出来，逻辑IO是9
从这一点上说，IO是一样的，不存在in的方式用不到索引的问题
比一个一个查询，最起码节省了数据库连接的开销
后来我在本机sqlserver2008看了一下执行计划，就有点犯晕了，
同样放到sqlserver2012下，执行计划又是不太一样的，直接上图吧
主要是搞不懂sqlserver2008下面的这个执行计划
还请大家指教
.
--tableTest表,ID上建立了聚集索引
select * from tableTest where id in (1,9999,12345);
类似这样一个查询，id上有聚集索引，
应该是受到IN的查询方式用不到索引的影响，不太敢用，但试着用in的方式去执行，也不见得慢，
因为没有理论证明，所以感觉不放心，
说是全部放到in里面一次性查出来块还是一个一个地查快
所以在讨论这个问题
我首先看了IO，比如三个ID，
一个一个查，逻辑IO是3（3次就是9了），
放到in 里面一次性查询出来，逻辑IO是9
从这一点上说，IO是一样的，不存在in的方式用不到索引的问题
比一个一个查询，最起码节省了数据库连接的开销
后来我在本机sqlserver2008看了一下执行计划，就有点犯晕了，
同样放到sqlserver2012下，执行计划又是不太一样的，直接上图吧
主要是搞不懂sqlserver2008下面的这个执行计划
还请大家指教
.
...全文
请发表友善的回复…
发表回复
专注or全面 2014-01-03
- 打赏
- 举报
我用脚本生成1000个id，放在in里面，继续用in的方式测试，一样是Constant Scan和clustered的方式出结果
select * from tabletest with (index(index_id)) where id in
(
0,
…………
)
declare @i int
set @i=0
while @i<10000
begin
print cast(@i as varchar(29))+','
set @i=@i+10
end
当继续加大这个id的数据的时候，确实走的是一个聚集索引的scan
但是这并不能说明，是因为用了in，造成无法使用索引，你几千个ID的话，可能聚集索引扫描效率会更高
有一种说法是：查询结果的条数大于表的页数的时候，那就是表扫描效率更高了
关键是用不用到索引，到底是不是in造成的
[/quote]
我觉得不是吧，最后用不用索引，是基于执行计划的综合开销来定的，和in没有必然的联系。
就像你说的， 如果sql server的优化器，觉得使用index seek效率更高，那么就会使用index seek。
如果综合判断后发现，效率不高，那么可能就会用scan，这个和用不用in 没有必然的联系，特别是这种：
in (1,2,3) 这种[/quote]
所以说，2楼的观点我是不认可的
专注or全面 2014-01-03
- 打赏
- 举报
我用脚本生成1000个id，放在in里面，继续用in的方式测试，一样是Constant Scan和clustered的方式出结果
select * from tabletest with (index(index_id)) where id in
(
0,
…………
)
declare @i int
set @i=0
while @i<10000
begin
print cast(@i as varchar(29))+','
set @i=@i+10
end
当继续加大这个id的数据的时候，确实走的是一个聚集索引的scan
但是这并不能说明，是因为用了in，造成无法使用索引，你几千个ID的话，可能聚集索引扫描效率会更高
有一种说法是：查询结果的条数大于表的页数的时候，那就是表扫描效率更高了
关键是用不用到索引，到底是不是in造成的
[/quote]其实有聚集索引的表一定会“走”索引，不会存在表扫描，但是我个人觉得所谓的走索引比较适合用于描述seek，而不是scan[/quote]
Seek和Scan我是分的清楚的，准确滴说，这里讨论的是in的方式能不能“利用索引”的问题（也就是聚集索引的seek了），或者说，in里面id多的时候，造成的聚集索引scan，是in这种查询方式造成的，还是优化器自动优化的结果
發糞塗牆 2014-01-03
- 打赏
- 举报
我用脚本生成1000个id，放在in里面，继续用in的方式测试，一样是Constant Scan和clustered的方式出结果
select * from tabletest with (index(index_id)) where id in
(
0,
…………
)
declare @i int
set @i=0
while @i<10000
begin
print cast(@i as varchar(29))+','
set @i=@i+10
end
当继续加大这个id的数据的时候，确实走的是一个聚集索引的scan
但是这并不能说明，是因为用了in，造成无法使用索引，你几千个ID的话，可能聚集索引扫描效率会更高
有一种说法是：查询结果的条数大于表的页数的时候，那就是表扫描效率更高了
关键是用不用到索引，到底是不是in造成的
[/quote]其实有聚集索引的表一定会“走”索引，不会存在表扫描，但是我个人觉得所谓的走索引比较适合用于描述seek，而不是scan
發糞塗牆 2014-01-03
- 打赏
- 举报
你是in所有ID？那扫描很正常啊
阳泉酒家小当家 2014-01-03
- 打赏
- 举报
我用脚本生成1000个id，放在in里面，继续用in的方式测试，一样是Constant Scan和clustered的方式出结果
select * from tabletest with (index(index_id)) where id in
(
0,
…………
)
declare @i int
set @i=0
while @i<10000
begin
print cast(@i as varchar(29))+','
set @i=@i+10
end
当继续加大这个id的数据的时候，确实走的是一个聚集索引的scan
但是这并不能说明，是因为用了in，造成无法使用索引，你几千个ID的话，可能聚集索引扫描效率会更高
有一种说法是：查询结果的条数大于表的页数的时候，那就是表扫描效率更高了
关键是用不用到索引，到底是不是in造成的
[/quote]
我觉得不是吧，最后用不用索引，是基于执行计划的综合开销来定的，和in没有必然的联系。
就像你说的， 如果sql server的优化器，觉得使用index seek效率更高，那么就会使用index seek。
如果综合判断后发现，效率不高，那么可能就会用scan，这个和用不用in 没有必然的联系，特别是这种：
in (1,2,3) 这种
专注or全面 2014-01-03
- 打赏
- 举报
我用脚本生成1000个id，放在in里面，继续用in的方式测试，一样是Constant Scan和clustered的方式出结果
select * from tabletest with (index(index_id)) where id in
(
0,
…………
)
declare @i int
set @i=0
while @i<10000
begin
print cast(@i as varchar(29))+','
set @i=@i+10
end
当继续加大这个id的数据的时候，确实走的是一个聚集索引的scan
但是这并不能说明，是因为用了in，造成无法使用索引，你几千个ID的话，可能聚集索引扫描效率会更高
有一种说法是：查询结果的条数大于表的页数的时候，那就是表扫描效率更高了
关键是用不用到索引，到底是不是in造成的
阳泉酒家小当家 2014-01-03
- 打赏
- 举报
定义个表达式，类型转换之类，你可以参考一下：
Microsoft SQL Server 2005技术内幕 查询、调整和优化
的第3章，里面有详细的讲解
阳泉酒家小当家 2014-01-03
- 打赏
- 举报
其实，从你贴出来的2008和2012的执行计划，能看出，同一个表，同一个聚集索引，同一个语句，所生成的执行计划却有不同：
2008的显然是采用了：
1、常量扫描-》计算标量，其实就是数据类型转化之类的 -》串联-》排序-》合并interval，这个估计有可能是去除重复
2、nested loop循环，把上面的结果，再 聚集索引中，进行index seek
而2012的，看上去非常简单，只是index seek id 加上or，也就是进行了3次index seek
说明，两者生成的执行计划，还是不同的。
专注or全面 2014-01-03
- 打赏
- 举报
我意思是，sqlserver2008里面的computer scalar那些怎么理解？
山寨DBA 2014-01-03
- 打赏
- 举报
huang大神一阵见血了。。。我模模糊糊的也感觉到你说的那个点，但是说不出来。。。不愧是版主啊
發糞塗牆 2014-01-03
- 打赏
- 举报
如果你的ID数量很多，换成join的方式目前是我见过最快的
發糞塗牆 2014-01-03
- 打赏
- 举报
In的本质是or，如果大量的ID，比如上千个，可能会导致执行计划无法生成。这个我试过，2008和2012的算法的确有不同，我看过基本上的例子，然后在本机上测试的确证明了这点。
山寨DBA 2014-01-03
- 打赏
- 举报
用in 不一定会用不到索引，只是可能会用不到。通过你的这个执行计划看，你的这个查询，就用到了索引，但是是否是最优的执行计划，就还需要进一步验证。
开着拖拉机泡妞 2014-01-03
- 打赏
- 举报
我首先看了IO，比如三个ID，
一个一个查，逻辑IO是3（3次就是9了），
放到in 里面一次性查询出来，逻辑IO是9
从这一点上说，IO是一样的，不存在in的方式用不到索引的问题
比一个一个查询，最起码节省了数据库连接的开销
首先可以肯定地说你这个查询使用in是会利用大索引的。
当然也有使用in的时候不会index seek的时候
而逻辑读取是指从缓存读取的页数，这个跟你的行大小有关系。而不是跟你的返回行数有这么明确的关系。
至于执行计划，你需要注意几个地方：
第一，每个节点返回的行数
第二，每个节点预估行数
这两点的结果对比可以判断你的执行计划是否是最优的。如果两者结果相差不大，说明SQL优化器选择的执行计划是合理的，如果有很大误差说明这个执行计划不是最优的。这个时候可能就要考虑对应的索引的问题了。可能是索引不合理，也或者是统计信息不够准确或者没来得及更新。
阳泉酒家小当家 2014-01-03
- 打赏
- 举报
我也确实有同感，以前的公司，也没人和我讨论，一方面是其他同事都比较忙，另一方面是他们都是主要做java开发的，所以对数据库这方面不是太关注，最多就是问我一些问题，他们要的是解决方法，直接了当，不是要过程，要讨论。
另外，那个深入解析的书，最好是下载一个英文版的，电子版csdn上就有下载的，实在看不明白的地方，适当参考一下英文原版，可能会更好。
發糞塗牆 2014-01-03
- 打赏
- 举报
我还在等你的数据测试呢
txg5230 2014-01-03
- 打赏
- 举报
中国无线论坛zgwxltlyy制作认证页面20131010更新
专注or全面 2014-01-03
- 打赏
- 举报
其实感觉大家讨论的过程以及提供的分析问题的思路都非常好，
非常感谢大家的回复，谢谢，结贴
平时需求，代码，测试，文档一块搞，做项目又当爹又当妈，很少抽出时间来充电，一年过一年，感到很有压力
现在是体会到了那句话，独学而无友，则孤陋寡闻，
有时候除了工作，学习自己感兴趣的东西，从来没有一个同事可以讨论的
还要有床上等你这里的高人指点，不慎感激！
再次感谢！
另外回复yupeigu
我看的那本也是2008版的，确实有些地方读起来比较生涩。
开着拖拉机泡妞 2014-01-03
- 打赏
- 举报
究竟是scan还是seek，这个是根据你的返回数据量来确定了。如果返回的结果集很大，那么优化器会觉得seek的开销比scan还打，这个时候它就会选择使用scan，而不是seek。
查询优化其实也是有规律的。
可能出现的大致也就下面的几种问题：
1、I/O瓶颈
2、没有合适的索引
3、语句复杂造成语句编译时间太长
4、统计信息不准确，造成优化器没能选择最优的执行计划
等等
一个语句出现问题后最好是有针对性的做出评估，看看问题究竟是出现在什么步骤，而不要一味的把注意力放在索引上。索引只是其中之一，而不是绝对。
阳泉酒家小当家 2014-01-03
- 打赏
- 举报
综上所述，本质上和是否用in，没有本质的联系，只是表面上，看似，有那么点关系，实际上应该是没关系的
加载更多回复（6）
