登陆

第六章 经典,表的连接学以致用

admin 2022-11-24 11人围观 ,发现0个评论

表的三种连接方式

嵌套循环(use_nl(nesting loop))

在嵌套循环连接中,驱动表返回多少条记录,被驱动表就被访问多少次(驱动表 : 驱动表在SQL语句执行的过程中,总是先读取。 而被驱动表在SQL语句执行的过程中,总是后读取)

散列连接(use_hash)

在散列连接中,驱动表和被驱动表都只会被访问0次或者1次(散列连接 散列连接是SQL Anywhere 数据库服务器所支持的最通用的连接方法。 简言之,散列连接算法会用两个输入中的较小输入建立内存中的散列表,然后读取较大的输入并探查内存中的散列表以查找匹配项。)

排序合并(use_merge)

排序合并连接根本就没有驱动和被驱动的概念,而嵌套循环连接和散列连接要考虑驱动和被驱动情况

三种连接顺序的影响

嵌套循环的顺序

嵌套循环连接要特别注意驱动表的顺序,先访问小的结果集,后访问大的结果集,才能保证被驱动表的被访问次数降到最低,从而提升性能

散列连接的表驱动顺序

驱动表的顺序不同也影响表连接的性能

排序合并的表驱动顺序

排序合并连接没有驱动的概念,无论哪张表在前都无妨

案例:

有一次生产系统出现相关的故障,有大致10条左右4表关联的SQL查询语句在生产环境中执行,这些语句涉及的表记录最多的有近百万条。由于是基于吞吐量层面的操作,要排序返回大部分记录,执行计划走的是排序合并连接。最终的结果是排序的尺寸过大导致内存中无法容纳,致使部分排序在磁盘中进行。“最终导致系统CPU资源被耗尽,应用缓慢,生产面临严重的性能故障。介入查询后,我发现这些语句全部是取了所有的字段参与排序合并连接,而后续相关的应用取这个结果集时又只取了部分字段,非常奇怪,向开发人员了解的结果是,用select * 代码比较方便,所以这样写了,实际这一环节的应用只需要2个字段足矣。

三种连接的限制

散列连接的限制

散列连接不支持不等值连接<>,不支持>和<的连接方式,也不支持LIKE的连接方式

排序合并连接的限制

排序合并连接不支持<>连接条件,也不支持 LIKE 连接条件,但是比起散列连接,它的支持面要广一些,支持>之类的连接条件

嵌套循环无限制

三种连接与索引的关系

嵌套循环连接与索引

嵌套循环连接要在驱动表的限制条件上加索引,在被驱动表的连接条件上加索引

在驱动表的限制条件上建索引是为了减少扫描驱动表的时间,如果在驱动表的连接条件上建索引就没任何意义了,所有列关联到另一张表的所有列,等同于每条记录都要关联。而在驱动表的限制条件上建了索引,只快速返回1条或者几条,然后再等传递给t2表的t2_id列,一般情况下t2表对应t1表的记录返回不多,所以在t2表的t1_id列建索引是有意义的。”

散列连接与索引

两表关联等值查询,在没有任何索引的情况下,Oracle倾向于走散列连接这种算法,因为散列连接的算法本身是比较高效且先进的。散列连接需要在 PGA 中的 HASH_AREA_SIZE中完成,因此增大HASH_ARAE_SIZE也是优化散列连接的一种有效途径,一般在内存自动管理的情况下,我们只要加大PGA区的大小即可。”

排序合并连接与索引

索引对于嵌套循环连接来说非常重要,既要考虑驱动表的限制条件上的索引,又要考虑被驱动表的连接条件上的索引;而索引对于散列连接来说,仅需考虑限制条件上的索引是否能用上索引,连接条件上的索引是不能发挥作用的;排序合并连接和散列连接又有差别,排序合并连接上的连接条件虽然没有检索的作用,却有消除排序的作用,

请发表您的评论
请关注微信公众号
微信二维码
不容错过
Powered By Z-BlogPHP