诡异的查询执行计划

[Novelty]

图一


图二

请看上面两个图中的查询计划，第一个比第二个多一个查询条件。

所有查询条件都有索引。

正常的猜测应该是第一个比第二个查询基本一样，但是结果是第一个比第二个慢n倍，而且执行计划也完全不同。

？？？

[Novelty]

“远程扫描”是因为扫描全文索引。

为什么第一和第二个的效率相差这么大？

[-狙击手-]

情况不明，
请提供：
1、索引情况
2、cnsb_product记录量，cat_childid= 5889的记录量，数据密度、分布情况

怀疑因索引只得执行全表扫描，计划只能执行嵌套迭代，而一旦发生嵌套迭代时联结的表内层或外层的顺序对磁盘I/O开销及顶部输入的行数影响匹配次数

[-狙击手-]

刚才试了一下，有点奇怪
1、索引不合理
2、重建索引试试

查询优化器会首先考虑Nested Loop和Sort-Merge的，而且只有两个集合量量大且没有合适的索引或无序时，才会考虑使用Hash Join。

[-狙击手-]

两个集合量量大

－－


两个集合数据量大

[mp000]

o

[Garnett_KG]

把cat_childid上面的索引去掉然後再帖一次执行计划.
(文字形式)

[viva369]

childid=5889的记录数是多少
parentid=9的记录是多少
聚集索引是哪个？

[viva369]

以上查询返回count(*)是多少？

[Novelty]

[Quote=引用 9 楼 happyflystone 的回复:]
两个集合量量大

－－


两个集合数据量大
[/Quote]

product表：50万记录
cat表:8千记录

[Novelty]

[Quote=引用 12 楼 viva369 的回复:]
childid=5889的记录数是多少
parentid=9的记录是多少
聚集索引是哪个？
[/Quote]

product表聚集索引:info_id
cat表聚集索引:catid

[Novelty]

[Quote=引用 13 楼 viva369 的回复:]
以上查询返回count(*)是多少？
[/Quote]

查询返回count(*)为0

[viva369]

大致的分析了一下

sqlserver是基于成本的，一般的连接它都会将量小的表每行去连接量大的表，但这样不是任何时候都适用的。
比如说在有排序字段的时候,查询条件阻止了根据排序字段的扫描，这样会更加额外的排序操作，从而大大降低了查询效率

而你的环境中也出现了类似这种情况。

[viva369]

还有就是连接方式，嵌套循环虽然简单，但并不适合出现在大表的扫描操作

[Novelty]

[Quote=引用 11 楼 Garnett_KG 的回复:]

把cat_childid上面的索引去掉然後再帖一次执行计划.
(文字形式)



[/Quote]

去掉cat_childid上的索引执行计划图：

[shuiniu]

1.根据提供的查询和执行计划可以推断出：
表cat的catid字段上建有UNIQUE的索引，因此的你的查询可以简化成：

SELECT d.catid,d.catname,COUNT(a.info_id) AS info_count
FROM cnsb_product a WITH(NOLOCK)
  --LEFT JOIN cat b WITH(NOLOCK) ON a.cat_parentid = b.catid
  --LEFT JOIN cat c WITH(NOLOCK) ON a.cat_childid = c.catid
	LEFT JOIN cat d WITH(NOLOCK) ON a.cat_rootid = d.catid
  --LEFT JOIN cat e WITH(NOLOCK) ON a.order4 = e.catid
WHERE ......
GROUP BY d.catid,d.catname

2.你说的慢n倍，是如何比较的，只是单纯的比较完成查询所需要的时间嘛？
比较的环境是否一致？热缓存还是冷缓存？

3.开启 SET STATISTICS IO ON 对比分析一下两个查询的输出

4.sp_helpindex cnsb_product 提供该结果集，进一步分析

5.并对所使用的关键索引输出DBCC SHOW_STATISTICS结果

[Garnett_KG]

[Quote=引用 19 楼 Novelty 的回复:]
引用 11 楼 Garnett_KG 的回复:


把cat_childid上面的索引去掉然後再帖一次执行计划.
(文字形式)




去掉cat_childid上的索引执行计划图：

[/Quote]

看上去效果應該會不錯.你再對比一下第二個查詢，會慢多少?

另，如水牛所說，打開 IO 讀數看看結果是什麼。

[Novelty]

将全文索引查询条件and contains((a.keyword,a.info_content),' "铸铁" ')去掉，两个查询的执行计划完全一样，完全利用索引检索，效率也非常高。

问题应该是出在全文索引和表索引不能同时被使用。

原理不是很明白。

[Novelty]

沉的太快了。

[zhnzzy]

查询计划里索引类型是SQL自己分析的，但是你也可以制定 with

[zhnzzy]

OPTION (HASH JOIN)