三种表连接类型:
插一句,采用alter session set statistics_level = all跟踪sql执行性能,可以查看到表被访问的次数(Starts)
嵌套循环连接(Nested Loop Join):
- 日常使用占比70%
- 需要区分驱动表Leading(t1)与被驱动表use_nt(t2)
- 驱动表返回多少条记录,被驱动表就访问多少次
- 结论:驱动表的的顺序很重要,小的结果集先访问,大的结果集后访问,保证被驱动表的访问次数最小,性能最高
- 不需要排序
- 支持所有的SQL条件连接写法,没有限制
建立索引提升性能:
- 连接条件:连接两张表的条件(eg:t1.id = t2.t1_id)
限制条件:单表的限制条件(eg:t1.id = 19、t2.n = 100)
适合NL的场景:
- 两表关联返回的记录不多时,此时驱动表返回1条或几条记录,被驱动表匹配到1条或几条记录,尽管两表记录很大很大,效率也会很高
- 存在不等值<>查询导致哈希连接和排序合并连接不能使用时
- 提升NL性能:在驱动表的限制条件限制的列上建立索引;在被驱动表的连接条件连接的列上建立索引
哈希连接(Hash Join):
- 日常使用占比20%
- 区分驱动表Leading(t1)与被驱动表use_hash(t2)
- 驱动表与被驱动表都只会访问0次或1次
- 需要建立HASH表(监控可看到消耗内存Used-Mem)
不支持不等于<>、>、<、LIKE的连接方式
哈希连接时,连接条件不起作用(索引也起不到快速检索的作用),只能在限制条件上建立索引(即单表索引模式)
排序合并连接(Merge Sort Join):
- 日常使用占比10%
- 不区分驱动表与被驱动表
- 访问表的次数和哈希连接相同:每个表都只会被访问0次或1次
- 需要两次排序(监控可看到消耗内存Used-Mem)
不支持不等于<>、LIKE的连接方式
排序合并连接同样是连接条件的索引起不到快速检索的作用,但由于是索引(本身有序),可以抵消一次排序。即可以对连接条件列建索引,消除一张表的排序,提示性能