27,579
社区成员
发帖
与我相关
我的任务
分享哈希匹配和嵌套循环的问题，期望DBA_Huangzj能回答一下
我看到http://bbs.csdn.net/topics/390352313这个帖子了，有些相似，悟出这个跟数据量有关系，但是我死活不清楚，跟存的值有什么关系。操作步骤、执行计划与记录数如下
表结构：
执行结果：
行数：366324
表查询语句1：
执行上面查询结果是慢的，执行结果：31秒，返回1019行，执行计划如图：
表查询语句2：
执行结果：0秒，返回21619行，执行计划如图：
两个条件不同之处是标红部分，我很不理解，为什么一个是哈希匹配，一个是嵌套循环，在什么时候会是哈希匹配，什么时候是嵌套循环，分可以追加，求解释
SELECT *
FROM mt_house_relationPer_tbl_zq20130308
ORDER BY hou_client asc
表结构：
执行结果：
行数：366324
表查询语句1：
select a.id
from mt_house_relationPer_tbl_zq20130308 a
where
exists( select id from get_Perinfo_fordept_fun('67,91',3 ) as getPer where del=1
AND hou_client= getPer.id )
order by a.id desc
执行上面查询结果是慢的，执行结果：31秒，返回1019行，执行计划如图：
表查询语句2：
select a.id
from mt_house_relationPer_tbl a
where
exists( select id from get_Perinfo_fordept_fun('67,91',3 ) as getPer where del=1
AND add_per= getPer.id )
order by a.id desc 执行结果：0秒，返回21619行，执行计划如图：
两个条件不同之处是标红部分，我很不理解，为什么一个是哈希匹配，一个是嵌套循环，在什么时候会是哈希匹配，什么时候是嵌套循环，分可以追加，求解释
...全文
请发表友善的回复…
发表回复
zq32206124 2013-03-22
- 打赏
- 举报
受益匪浅
zq32206124 2013-03-22
- 打赏
- 举报
刚也是忙了一段时间，虽然还不太清楚具体怎么个情况，不过也不急于一时，慢慢的了解吧，谢谢
jiangshun 2013-03-12
- 打赏
- 举报
zq32206124 2013-03-12
- 打赏
- 举报
这两天有点忙，等再看，东西太多了，还需要消化一下。
DBA_磊仔 2013-03-11
- 打赏
- 举报
我们提到的临时表，只是处理你过程中这个性能问题的，为什么你会扯到在存储过程其他地方使用呢？
我们在这里只是讨论你这一句
select a.id from mt_house_relationPer_tbl_zq20130308 a where exists( select id from get_Perinfo_fordept_fun('67,91',3 ) as getPer where del=1 AND hou_client = getPer.id ) order by a.id desc
的优化方法，你举这个例子根本套用不上
發糞塗牆 2013-03-11
- 打赏
- 举报
局部临时表在当前会话有效。其他非当前会话作用域无效。
说吧，我查漏补缺
说吧，我查漏补缺發糞塗牆 2013-03-11
- 打赏
- 举报
周六日有点事，而且刚好没网络，没回复，不过还是吓我一跳，那么多问题，我一个一个来吧，尽量尽量，为了不影响我的思路连贯性，可能不会针对你的每一个问题单独回复，希望你能看得懂。
1、我看你的表mt_per_tbl 上索引那么多，会不会存在过度使用了？
2、对于你11楼的问题，不同的列很难有可比性，因为：a、数据类型的差异，哪怕char10和char20，性能都会存在可能性的差异，当然优化器也有可能优化成没有差异。b、数据分布，这个才容易造成性能差异。如果某个列存在大量相同的值，或者空值，那么它的“选择性”就相对较低，索引也相对低效。还要视乎你的统计信息是否及时。统计信息和索引的列、索引的碎片等等都会对优化器选择造成决定性的影响。这里有个建议：把你的两个查询都分别这样做：分别对同一个查询点预估执行计划和实际执行计划，看看这两者是否在某些部分有明显差异，如果有，很有可能意味着你统计信息或者碎片影响了，如果没有，再考虑其他。
3、还是11楼的问题，select * into 只是把数据和结构导过去，但是不会把索引、约束这些导过去，如果你没有手工创建，你知道后果的。
4、索引的高明之处就在于避免每次查询都要读每一行，合理的索引可以把数据或者数据的标识符存放到索引页，由于索引页由B-TREE组成，所以即使大量数据，索引的深度都不会非常变态，深度决定索引的查找速度。这个暂时不深入说，几十万字恐怕也说不清楚。何况我也不是理解透。简单说一下，假设你有聚集索引（也就是有了整个表的数据），那么如果你的数据分布靠前，那么分布在索引树上，就是靠左边的节点，而扫描索引是从左边开始，那么所谓先到先得，自然就快。
5、我觉得你的cte可以考虑改进一下，如考虑是否有必要改表结构、有没有必要加where条件限制返回结果集。然后把cte的筛选条件参数化，在使用函数的时候改成直接关联这个CTE的结果集或者这个结果集的临时表 。这样会有提高，特别大大数据量的时候都会有。
6、对于临时表，很少应用必须用到全局临时表，而你这个我觉得还不至于，所以用局部临时表就可以了，对于性能问题，主要就是tempdb的管理了。还有记得用完马上删除而不要在存储过程的最后才删除。
7、11楼最后一个问题：并行度，这个主要靠SQLServer控制，你很难控制的。因为系统动态根据运行时的负载来决定是否有可用的资源做并行操作，每个执行计划一个实例下最多只有两个，一个就是并行、一个就是串行。你把数据赋值过去，其实基本上不就等于同一个列嘛，那有什么好对比的。列的数据、数据类型、数据分布、索引（创建顺序、碎片程度、列的查找、扫描次数等等）都会对执行的结果有影响，这个好像说的太深了...
8、复合索引的列顺序也有影响，筛选性高的列放在前面，那么它会尽快减少需要处理的数据集，如果放在后面，那么索引筛选性能就相对低，需要面对的数据量也就大，如果数据量到达一定程度，就会使用哈希来替代嵌套循环这种情况的发生。
9、移除函数是提升性能比较有效的方法，详细可以看看我的文章：http://blog.csdn.net/dba_huangzj/article/details/7816384
10、总结性的一句，当发生不同的关联方式时，要考虑关联操作的两边结果集，有什么不同，比如使用哈希关联的两个表比使用嵌套循环的两个表是否相对来说大很多？是否那些列上没有索引或者没有排序等等因素，因为这些因素是影响关联方式的主要方式。不过要记住，没有绝对好的关联方式，也没有绝对差的关联方式，各有千秋，且视实际情况而定。
不知道有没有遗漏，有问题继续问吧。我有空会回答，但是不保证实时性，最近有新任务，忙。
夜深、睡觉了
zq32206124 2013-03-11
- 打赏
- 举报
存储过程里面生成的#临时表,在其他地方可以用？你这么说我以为可以用的，刚测试了一下
exec test --这里有插入临时表
select * from #TMP --这样是会报错的
mayuanf 2013-03-11
- 打赏
- 举报
..既然点名了，咱就不参合了
DBA_磊仔 2013-03-11
- 打赏
- 举报
数据库的执行的速度是基于执行计划的不同而不同的，当你的查询过于复杂，或者统计值不正确，或者使用了不可索引的查找方式的时候，都会得到不良的执行计划，导致查询速度过慢。
你看DBA兄那么长一段就是为了告诉你为什么你这个会慢，确实很难总结成一句话吧。
索引不同、或者数据分布的统计值不同，或者根本就是一个第一次执行读了硬盘，第二次已经缓存了读的是内存。
就你给出的信息，我也只能推断可能是索引不同导致了不同的执行计划
zq32206124 2013-03-11
- 打赏
- 举报
额~~不好意思，我理解错了，我之前有试过，如果不读取函数，用in ID串或者放到临时表是快的；或者在exists条件前面加上hou_client>0速度也是快的，所以解决这个快慢是有方法的，我就是不理解为什么这两个句子会有差别。
zq32206124 2013-03-11
- 打赏
- 举报
额，谢谢你的回答，不过我是想知道为什么两个查询为什么会有一个慢，有一个快。想快的解决办法，会有好多种
duoxu1983 2013-03-10
- 打赏
- 举报
select a.idfrom mt_house_relationPer_tbl_zq20130308 a
inner join ( select id from get_Perinfo_fordept_fun('67,91',3 ) where del=1) b
on a.hou_client= getPer.id
order by a.id desc
DBA_磊仔 2013-03-09
- 打赏
- 举报
抱歉啊，昨晚想了一下竟然就睡着了
对于表值函数的性能该如何处理，这点我确实没有很多经验
不过我还是认为，大数据的话，视图能不用都不用，更不要说表值函数了，何况你还使用了charindex这样不可利用索引的函数在里面
就你最后查得的数据量来说，数据应该是不大的，表结构也简单，正确书写的话不应该存在什么性能问题
对于哈希和嵌套，这都是根据开销来选择的，一般两个大表之间如果不存在可以利用的索引，进行匹配之前又不能很好的过滤数据的话，会使用哈希匹配
如果即使不能利用索引，但是where子句里面已经能在关联之前就很好的过滤数据，使得一个表的数据量变小，一般会考虑使用嵌套循环，估计这和你数据的统计值有关
zq32206124 2013-03-09
- 打赏
- 举报
哈希：主要对大数据量且没有索引或者没有排序的数据进行关联的时候用的，它会在内存中创建一个过度表进行匹配，对大数据量尤为有效。
好，对大数据量有效，可是这个是一个表里两个不同字段，数据量是一样的，区别是add_per只有394条为零的数据，而hou_client有342236条数据是为0的
查询中1的表，应该是历史表或者你通过select * into弄出来的吧。
表是select into出来的，把没用的字段去掉了，所以跟那个表一样，您就帮忙分析下这个表的情况。
如果你所需的数据基本上都在表的前面，那么扫描范围就小，查找速度就快，如果极端情况下都在表的尾部，那么要扫描到后面才能返回所需的数据，自然就慢
不管是什么情况，我怎么觉着表的每一行，都应该扫描呢，因为不把所有行扫描了，怎么知道人员ID跟函数的人员ID匹配呢，我又没限定只得到多少行
我的函数是读的区域内的所有人员，有3张表关联（就是人员表数据量比较大，27780条记录）
语句如下（可以直接跳过）：
函数执行：
执行时间：0秒，行数112行
再看一次你的执行计划，然后百分比主要在关联，但是我个人认为是函数惹的祸，你看第一个查询的执行计划，那个函数处理的表没索引，而且数据量大（看箭头的粗细，1000多行就那么一点，估计那个函数处理的表应该有10万级别），扫描这么大个表，又没有索引辅助，慢是很正常。
另外第二个查询用到了并行度，也就是这个时候可是借助多CPU来计算，对速度提升也有一定程度的帮助
为什么只是字段关联的不同，就要借助CPU计算呢，我把add_per的值，复制到hou_client里，就跟查询2的是一样的了，这是为什么？
下面说一下函数里三个表的结构，及索引情况
mt_per_tbl 数据量：27780行
索引如图： del：非聚集 dept_id：非聚集 per_shop:非聚集 per_tel:非聚集 ID：聚集
mt_shop_tbl 数据量：1196行
索引如图： dept_id：非聚集 del：非聚集 id：聚集
Agent_Department_Tbl 数据量：294行
索引如图： com_id:非聚集 del:非聚集 depttype:非聚集 ID：聚集
真诚的期望能解决我心中的疑惑，不然以后也没法注意，只能一味的只知道写功能
好，对大数据量有效，可是这个是一个表里两个不同字段，数据量是一样的，区别是add_per只有394条为零的数据，而hou_client有342236条数据是为0的
查询中1的表，应该是历史表或者你通过select * into弄出来的吧。
表是select into出来的，把没用的字段去掉了，所以跟那个表一样，您就帮忙分析下这个表的情况。
如果你所需的数据基本上都在表的前面，那么扫描范围就小，查找速度就快，如果极端情况下都在表的尾部，那么要扫描到后面才能返回所需的数据，自然就慢
不管是什么情况，我怎么觉着表的每一行，都应该扫描呢，因为不把所有行扫描了，怎么知道人员ID跟函数的人员ID匹配呢，我又没限定只得到多少行
我的函数是读的区域内的所有人员，有3张表关联（就是人员表数据量比较大，27780条记录）
语句如下（可以直接跳过）：
--根据部门（区域）ID串，得到包括本身部门（区域）及所有子集部门（区域）下的所有人（包含离职
;with wsp as
(
select id,dept_name from agent_config.dbo.Agent_Department_Tbl where CHARINDEX(','+CAST(id AS VARCHAR(100))+',',','+@dept_id+',')>0 and isnull(del,0)=0
union all
select a.id,a.dept_name from agent_config.dbo.Agent_Department_Tbl a,wsp b where a.parent_id=b.id and isnull(a.del,0)=0
)
insert into @t
select per.id,per_name,ISNULL(per.del,1) del
from wsp a
inner join agent_config.dbo.mt_shop_tbl shop on a.id=shop.dept_id --这里把只查询未删除店的条件去掉了，因为数据查看下成交房源需要查看到所有人的，包含已删除的店
inner join agent_config.dbo.mt_per_tbl per on per.per_shop=shop.shopid
UNION
SELECT per.id,per_name,ISNULL(per.del,1) del
FROM wsp a
INNER JOIN agent_config.dbo.mt_per_tbl per on a.id=per.DEPT_ID函数执行：
select id from get_Perinfo_fordept_fun('67,91',3 ) as getPer where del=1执行时间：0秒，行数112行
再看一次你的执行计划，然后百分比主要在关联，但是我个人认为是函数惹的祸，你看第一个查询的执行计划，那个函数处理的表没索引，而且数据量大（看箭头的粗细，1000多行就那么一点，估计那个函数处理的表应该有10万级别），扫描这么大个表，又没有索引辅助，慢是很正常。
另外第二个查询用到了并行度，也就是这个时候可是借助多CPU来计算，对速度提升也有一定程度的帮助
为什么只是字段关联的不同，就要借助CPU计算呢，我把add_per的值，复制到hou_client里，就跟查询2的是一样的了，这是为什么？
下面说一下函数里三个表的结构，及索引情况
mt_per_tbl 数据量：27780行
索引如图： del：非聚集 dept_id：非聚集 per_shop:非聚集 per_tel:非聚集 ID：聚集
mt_shop_tbl 数据量：1196行
索引如图： dept_id：非聚集 del：非聚集 id：聚集
Agent_Department_Tbl 数据量：294行
索引如图： com_id:非聚集 del:非聚集 depttype:非聚集 ID：聚集
真诚的期望能解决我心中的疑惑，不然以后也没法注意，只能一味的只知道写功能
zq32206124 2013-03-09
- 打赏
- 举报
add_per上没有索引，把mt_house_relationPer_tbl_zq20130308完全当做一个表来看待就可以了，因为mt_house_relationPer_tbl表字段太多，所以给删除掉了
DBA_磊仔 2013-03-09
- 打赏
- 举报
扫描是全表或者全索引全部读入内存，再进行谓词过滤
查找是指谓词查找，直接利用索引检索数据，走的是索引，你可以去看看索引的结构及其工作的方式
如果你非要用那个表值函数，建议先把他插到临时表再使用。确保 hou_client、add_per列上有索引，最好是临时表上也建一个ID列的索引
要插入临时表的话，就要用全局的了
为什么要用全局的，完全没有必要用全局临时表吧
你先弄清楚全局临时表 ## 和 局部临时表 # 的区别
zq32206124 2013-03-09
- 打赏
- 举报
我怕这个牛B的版主看不到，所以才写上名字的，期待您的回答。
zq32206124 2013-03-09
- 打赏
- 举报
首先谢谢你的回答，是不是我太愚钝了，不太明白；
函数本身的意义是读取部门下的所有人，本来是先读取出人ID来用in形式的，但是假如部门下有1000人的话，那感觉就太慢了，所以我才用函数写的，我疑惑的是为什么add_per关联和hou_client关联，查询出来的时间却不一样
zq32206124 2013-03-09
- 打赏
- 举报
蛋疼连着回复3次竟然不能再回复了，还得等着有回复的
上面忘记引用了，要插入临时表的话，就要用全局的了，可能调用的人特别多，这个是根据权限读取的人员，所以怕到时候临时表太多了。
还有，请问，返回比较大的数据，是指返回的量么，不是数据表的总量吧；还有扫描和查找是怎么区分的？不好意思一次问出了这么多的问题，还望您能耐心回答
加载更多回复（9）
