【HiveSQL】join关联on和where的区别及服从对比

测试环境：hive on spark
spark版本：3.3.1
  
  
一、实行机遇

sql连接中，where属于过滤条件，用于对join的结果集举行过滤，以是理论上的实行机遇在join之后。on属于关联条件，决定了满意什么样条件的数据才可以被关联到一起，因此理论上的实行机遇在join时。
但是，大多数数据库系统为了提升服从都采用了一些优化技术，思想都是将where中的筛选条件或是on中的关联条件尽大概的提前到数据源侧举行筛选，目标是淘汰参与关联的数据量。因此它们实际的实行机遇大多时候和理论上的不同。
二、对结果集的影响

内连接中，条件放在where或者on中对结果集无影响。
外连接中（以左外连接为例），因为左外连接是完全保存左表记载，on在join时见效，因此终极的结果集也会保存左表的全部记载。where是对join后的结果集举行操作，以是会过滤掉一些数据导致二者的结果集不相同。
三、服从对比

测试数据量如下：
   poi_data.poi_res表：数据量8300W+
bi_report.mon_ronghe_pv表：分区表，总数据量120E+，本次采用分区20240522的数据关联，数据量5900W+，其中 bid like ‘1%’ & pv>100 的数据量120W+

两表的关联字段均无重复值。
  1.内连接

1）on

1. select
2.         t1.bid,
3.     t1.name,
4.     t1.point_x,
5.     t1.point_y,
6.         t2.pv
7. from poi_data.poi_res t1
8. join (select bid, pv from bi_report.mon_ronghe_pv where event_day='20240522') t2
9. on t1.bid=t2.bid
10. and t2.bid like '1%' and t2.pv>100;

复制代码

1. == Physical Plan ==
2. AdaptiveSparkPlan (28)
3. +- == Final Plan ==
4.    CollectLimit (17)
5.    +- * Project (16)
6.       +- * SortMergeJoin Inner (15)
7.          :- * Sort (6)
8.          :  +- AQEShuffleRead (5)
9.          :     +- ShuffleQueryStage (4), Statistics(sizeInBytes=5.3 GiB, rowCount=4.57E+7)
10.          :        +- Exchange (3)
11.          :           +- * Filter (2)
12.          :              +- Scan hive poi_data.poi_res (1)
13.          +- * Sort (14)
14.             +- AQEShuffleRead (13)
15.                +- ShuffleQueryStage (12), Statistics(sizeInBytes=58.5 MiB, rowCount=1.28E+6)
16.                   +- Exchange (11)
17.                      +- * Project (10)
18.                         +- * Filter (9)
19.                            +- * ColumnarToRow (8)
20.                               +- Scan parquet bi_report.mon_ronghe_pv (7)
21. +- == Initial Plan ==
22.    CollectLimit (27)
23.    +- Project (26)
24.       +- SortMergeJoin Inner (25)
25.          :- Sort (20)
26.          :  +- Exchange (19)
27.          :     +- Filter (18)
28.          :        +- Scan hive poi_data.poi_res (1)
29.          +- Sort (24)
30.             +- Exchange (23)
31.                +- Project (22)
32.                   +- Filter (21)
33.                      +- Scan parquet bi_report.mon_ronghe_pv (7)
36. (1) Scan hive poi_data.poi_res
37. Output [4]: [bid#297, name#299, point_x#316, point_y#317]
38. Arguments: [bid#297, name#299, point_x#316, point_y#317], HiveTableRelation [`poi_data`.`poi_res`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [bid#297, type#298, name#299, address#300, phone#301, alias#302, post_code#303, catalog_id#304, c..., Partition Cols: []]
40. (2) Filter [codegen id : 1]
41. Input [4]: [bid#297, name#299, point_x#316, point_y#317]
42. Condition : (StartsWith(bid#297, 1) AND isnotnull(bid#297))
44. (3) Exchange
45. Input [4]: [bid#297, name#299, point_x#316, point_y#317]
46. Arguments: hashpartitioning(bid#297, 600), ENSURE_REQUIREMENTS, [plan_id=774]
48. (4) ShuffleQueryStage
49. Output [4]: [bid#297, name#299, point_x#316, point_y#317]
50. Arguments: 0
52. (5) AQEShuffleRead
53. Input [4]: [bid#297, name#299, point_x#316, point_y#317]
54. Arguments: coalesced
56. (6) Sort [codegen id : 3]
57. Input [4]: [bid#297, name#299, point_x#316, point_y#317]
58. Arguments: [bid#297 ASC NULLS FIRST], false, 0
60. (7) Scan parquet bi_report.mon_ronghe_pv
61. Output [3]: [bid#334, pv#335, event_day#338]
62. Batched: true
63. Location: InMemoryFileIndex [afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/warehouse/bi_report.db/mon_ronghe_pv/event_day=20240522]
64. PartitionFilters: [isnotnull(event_day#338), (event_day#338 = 20240522)]
65. PushedFilters: [IsNotNull(bid), IsNotNull(pv), StringStartsWith(bid,1), GreaterThan(pv,100)]
66. ReadSchema: struct<bid:string,pv:int>
68. (8) ColumnarToRow [codegen id : 2]
69. Input [3]: [bid#334, pv#335, event_day#338]
71. (9) Filter [codegen id : 2]
72. Input [3]: [bid#334, pv#335, event_day#338]
73. Condition : (((isnotnull(bid#334) AND isnotnull(pv#335)) AND StartsWith(bid#334, 1)) AND (pv#335 > 100))
75. (10) Project [codegen id : 2]
76. Output [2]: [bid#334, pv#335]
77. Input [3]: [bid#334, pv#335, event_day#338]
79. (11) Exchange
80. Input [2]: [bid#334, pv#335]
81. Arguments: hashpartitioning(bid#334, 600), ENSURE_REQUIREMENTS, [plan_id=799]
83. (12) ShuffleQueryStage
84. Output [2]: [bid#334, pv#335]
85. Arguments: 1
87. (13) AQEShuffleRead
88. Input [2]: [bid#334, pv#335]
89. Arguments: coalesced
91. (14) Sort [codegen id : 4]
92. Input [2]: [bid#334, pv#335]
93. Arguments: [bid#334 ASC NULLS FIRST], false, 0
95. (15) SortMergeJoin [codegen id : 5]
96. Left keys [1]: [bid#297]
97. Right keys [1]: [bid#334]
98. Join condition: None
100. (16) Project [codegen id : 5]
101. Output [5]: [bid#297, name#299, point_x#316, point_y#317, pv#335]
102. Input [6]: [bid#297, name#299, point_x#316, point_y#317, bid#334, pv#335]
104. (17) CollectLimit
105. Input [5]: [bid#297, name#299, point_x#316, point_y#317, pv#335]
106. Arguments: 1000
108. (18) Filter
109. Input [4]: [bid#297, name#299, point_x#316, point_y#317]
110. Condition : (StartsWith(bid#297, 1) AND isnotnull(bid#297))
112. (19) Exchange
113. Input [4]: [bid#297, name#299, point_x#316, point_y#317]
114. Arguments: hashpartitioning(bid#297, 600), ENSURE_REQUIREMENTS, [plan_id=759]
116. (20) Sort
117. Input [4]: [bid#297, name#299, point_x#316, point_y#317]
118. Arguments: [bid#297 ASC NULLS FIRST], false, 0
120. (21) Filter
121. Input [3]: [bid#334, pv#335, event_day#338]
122. Condition : (((isnotnull(bid#334) AND isnotnull(pv#335)) AND StartsWith(bid#334, 1)) AND (pv#335 > 100))
124. (22) Project
125. Output [2]: [bid#334, pv#335]
126. Input [3]: [bid#334, pv#335, event_day#338]
128. (23) Exchange
129. Input [2]: [bid#334, pv#335]
130. Arguments: hashpartitioning(bid#334, 600), ENSURE_REQUIREMENTS, [plan_id=760]
132. (24) Sort
133. Input [2]: [bid#334, pv#335]
134. Arguments: [bid#334 ASC NULLS FIRST], false, 0
136. (25) SortMergeJoin
137. Left keys [1]: [bid#297]
138. Right keys [1]: [bid#334]
139. Join condition: None
141. (26) Project
142. Output [5]: [bid#297, name#299, point_x#316, point_y#317, pv#335]
143. Input [6]: [bid#297, name#299, point_x#316, point_y#317, bid#334, pv#335]
145. (27) CollectLimit
146. Input [5]: [bid#297, name#299, point_x#316, point_y#317, pv#335]
147. Arguments: 1000
149. (28) AdaptiveSparkPlan
150. Output [5]: [bid#297, name#299, point_x#316, point_y#317, pv#335]
151. Arguments: isFinalPlan=true

复制代码

从物理实行计划可以看到第（2）步中的Filter使用条件Condition : (StartsWith(bid#297, 1) AND isnotnull(bid#297))在t1表读取源数据时举行了过滤，在第（7）步中通过谓词下推在t2表scan源数据时使用条件PushedFilters: [IsNotNull(bid), IsNotNull(pv), StringStartsWith(bid,1), GreaterThan(pv,100)]举行了过滤，两表都是在数据源侧举行的数据过滤，淘汰了shuffle和参与join的数据量。
2）where

1. select
2.         t1.bid,
3.     t1.name,
4.     t1.point_x,
5.     t1.point_y,
6.         t2.pv
7. from poi_data.poi_res t1
8. join (select bid, pv from bi_report.mon_ronghe_pv where event_day='20240522') t2
9. on t1.bid=t2.bid
10. where t2.bid like '1%' and t2.pv>100;

复制代码

1. == Physical Plan ==
2. AdaptiveSparkPlan (28)
3. +- == Final Plan ==
4.    CollectLimit (17)
5.    +- * Project (16)
6.       +- * SortMergeJoin Inner (15)
7.          :- * Sort (6)
8.          :  +- AQEShuffleRead (5)
9.          :     +- ShuffleQueryStage (4), Statistics(sizeInBytes=5.3 GiB, rowCount=4.57E+7)
10.          :        +- Exchange (3)
11.          :           +- * Filter (2)
12.          :              +- Scan hive poi_data.poi_res (1)
13.          +- * Sort (14)
14.             +- AQEShuffleRead (13)
15.                +- ShuffleQueryStage (12), Statistics(sizeInBytes=58.5 MiB, rowCount=1.28E+6)
16.                   +- Exchange (11)
17.                      +- * Project (10)
18.                         +- * Filter (9)
19.                            +- * ColumnarToRow (8)
20.                               +- Scan parquet bi_report.mon_ronghe_pv (7)
21. +- == Initial Plan ==
22.    CollectLimit (27)
23.    +- Project (26)
24.       +- SortMergeJoin Inner (25)
25.          :- Sort (20)
26.          :  +- Exchange (19)
27.          :     +- Filter (18)
28.          :        +- Scan hive poi_data.poi_res (1)
29.          +- Sort (24)
30.             +- Exchange (23)
31.                +- Project (22)
32.                   +- Filter (21)
33.                      +- Scan parquet bi_report.mon_ronghe_pv (7)
36. (1) Scan hive poi_data.poi_res
37. Output [4]: [bid#350, name#352, point_x#369, point_y#370]
38. Arguments: [bid#350, name#352, point_x#369, point_y#370], HiveTableRelation [`poi_data`.`poi_res`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [bid#350, type#351, name#352, address#353, phone#354, alias#355, post_code#356, catalog_id#357, c..., Partition Cols: []]
40. (2) Filter [codegen id : 1]
41. Input [4]: [bid#350, name#352, point_x#369, point_y#370]
42. Condition : (StartsWith(bid#350, 1) AND isnotnull(bid#350))
44. (3) Exchange
45. Input [4]: [bid#350, name#352, point_x#369, point_y#370]
46. Arguments: hashpartitioning(bid#350, 600), ENSURE_REQUIREMENTS, [plan_id=908]
48. (4) ShuffleQueryStage
49. Output [4]: [bid#350, name#352, point_x#369, point_y#370]
50. Arguments: 0
52. (5) AQEShuffleRead
53. Input [4]: [bid#350, name#352, point_x#369, point_y#370]
54. Arguments: coalesced
56. (6) Sort [codegen id : 3]
57. Input [4]: [bid#350, name#352, point_x#369, point_y#370]
58. Arguments: [bid#350 ASC NULLS FIRST], false, 0
60. (7) Scan parquet bi_report.mon_ronghe_pv
61. Output [3]: [bid#387, pv#388, event_day#391]
62. Batched: true
63. Location: InMemoryFileIndex [afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/warehouse/bi_report.db/mon_ronghe_pv/event_day=20240522]
64. PartitionFilters: [isnotnull(event_day#391), (event_day#391 = 20240522)]
65. PushedFilters: [IsNotNull(bid), IsNotNull(pv), StringStartsWith(bid,1), GreaterThan(pv,100)]
66. ReadSchema: struct<bid:string,pv:int>
68. (8) ColumnarToRow [codegen id : 2]
69. Input [3]: [bid#387, pv#388, event_day#391]
71. (9) Filter [codegen id : 2]
72. Input [3]: [bid#387, pv#388, event_day#391]
73. Condition : (((isnotnull(bid#387) AND isnotnull(pv#388)) AND StartsWith(bid#387, 1)) AND (pv#388 > 100))
75. (10) Project [codegen id : 2]
76. Output [2]: [bid#387, pv#388]
77. Input [3]: [bid#387, pv#388, event_day#391]
79. (11) Exchange
80. Input [2]: [bid#387, pv#388]
81. Arguments: hashpartitioning(bid#387, 600), ENSURE_REQUIREMENTS, [plan_id=933]
83. (12) ShuffleQueryStage
84. Output [2]: [bid#387, pv#388]
85. Arguments: 1
87. (13) AQEShuffleRead
88. Input [2]: [bid#387, pv#388]
89. Arguments: coalesced
91. (14) Sort [codegen id : 4]
92. Input [2]: [bid#387, pv#388]
93. Arguments: [bid#387 ASC NULLS FIRST], false, 0
95. (15) SortMergeJoin [codegen id : 5]
96. Left keys [1]: [bid#350]
97. Right keys [1]: [bid#387]
98. Join condition: None
100. (16) Project [codegen id : 5]
101. Output [5]: [bid#350, name#352, point_x#369, point_y#370, pv#388]
102. Input [6]: [bid#350, name#352, point_x#369, point_y#370, bid#387, pv#388]
104. (17) CollectLimit
105. Input [5]: [bid#350, name#352, point_x#369, point_y#370, pv#388]
106. Arguments: 1000
108. (18) Filter
109. Input [4]: [bid#350, name#352, point_x#369, point_y#370]
110. Condition : (StartsWith(bid#350, 1) AND isnotnull(bid#350))
112. (19) Exchange
113. Input [4]: [bid#350, name#352, point_x#369, point_y#370]
114. Arguments: hashpartitioning(bid#350, 600), ENSURE_REQUIREMENTS, [plan_id=893]
116. (20) Sort
117. Input [4]: [bid#350, name#352, point_x#369, point_y#370]
118. Arguments: [bid#350 ASC NULLS FIRST], false, 0
120. (21) Filter
121. Input [3]: [bid#387, pv#388, event_day#391]
122. Condition : (((isnotnull(bid#387) AND isnotnull(pv#388)) AND StartsWith(bid#387, 1)) AND (pv#388 > 100))
124. (22) Project
125. Output [2]: [bid#387, pv#388]
126. Input [3]: [bid#387, pv#388, event_day#391]
128. (23) Exchange
129. Input [2]: [bid#387, pv#388]
130. Arguments: hashpartitioning(bid#387, 600), ENSURE_REQUIREMENTS, [plan_id=894]
132. (24) Sort
133. Input [2]: [bid#387, pv#388]
134. Arguments: [bid#387 ASC NULLS FIRST], false, 0
136. (25) SortMergeJoin
137. Left keys [1]: [bid#350]
138. Right keys [1]: [bid#387]
139. Join condition: None
141. (26) Project
142. Output [5]: [bid#350, name#352, point_x#369, point_y#370, pv#388]
143. Input [6]: [bid#350, name#352, point_x#369, point_y#370, bid#387, pv#388]
145. (27) CollectLimit
146. Input [5]: [bid#350, name#352, point_x#369, point_y#370, pv#388]
147. Arguments: 1000
149. (28) AdaptiveSparkPlan
150. Output [5]: [bid#350, name#352, point_x#369, point_y#370, pv#388]
151. Arguments: isFinalPlan=true

复制代码

物理实行计划没有厘革，因此可以说，当数据库支持谓词下推时，筛选条件用where照旧on没有区别，数据库都会在数据源侧举行数据过滤，淘汰参与关联的数据量。
2.外连接

1）on

1. select
2.         t1.bid,
3.     t1.name,
4.     t1.point_x,
5.     t1.point_y,
6.         t2.pv
7. from poi_data.poi_res t1
8. left join (select bid, pv from bi_report.mon_ronghe_pv where event_day='20240522') t2
9. on t1.bid=t2.bid
10. and t2.bid like '1%' and t2.pv>100;

复制代码

1. == Physical Plan ==
2. AdaptiveSparkPlan (28)
3. +- == Final Plan ==
4.    CollectLimit (17)
5.    +- * Project (16)
6.       +- * SortMergeJoin LeftOuter (15)
7.          :- * Sort (6)
8.          :  +- AQEShuffleRead (5)
9.          :     +- ShuffleQueryStage (4), Statistics(sizeInBytes=36.5 MiB, rowCount=3.07E+5)
10.          :        +- Exchange (3)
11.          :           +- * LocalLimit (2)
12.          :              +- Scan hive poi_data.poi_res (1)
13.          +- * Sort (14)
14.             +- AQEShuffleRead (13)
15.                +- ShuffleQueryStage (12), Statistics(sizeInBytes=58.5 MiB, rowCount=1.28E+6)
16.                   +- Exchange (11)
17.                      +- * Project (10)
18.                         +- * Filter (9)
19.                            +- * ColumnarToRow (8)
20.                               +- Scan parquet bi_report.mon_ronghe_pv (7)
21. +- == Initial Plan ==
22.    CollectLimit (27)
23.    +- Project (26)
24.       +- SortMergeJoin LeftOuter (25)
25.          :- Sort (20)
26.          :  +- Exchange (19)
27.          :     +- LocalLimit (18)
28.          :        +- Scan hive poi_data.poi_res (1)
29.          +- Sort (24)
30.             +- Exchange (23)
31.                +- Project (22)
32.                   +- Filter (21)
33.                      +- Scan parquet bi_report.mon_ronghe_pv (7)
36. (1) Scan hive poi_data.poi_res
37. Output [4]: [bid#403, name#405, point_x#422, point_y#423]
38. Arguments: [bid#403, name#405, point_x#422, point_y#423], HiveTableRelation [`poi_data`.`poi_res`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [bid#403, type#404, name#405, address#406, phone#407, alias#408, post_code#409, catalog_id#410, c..., Partition Cols: []]
40. (2) LocalLimit [codegen id : 1]
41. Input [4]: [bid#403, name#405, point_x#422, point_y#423]
42. Arguments: 1000
44. (3) Exchange
45. Input [4]: [bid#403, name#405, point_x#422, point_y#423]
46. Arguments: hashpartitioning(bid#403, 600), ENSURE_REQUIREMENTS, [plan_id=1043]
48. (4) ShuffleQueryStage
49. Output [4]: [bid#403, name#405, point_x#422, point_y#423]
50. Arguments: 0
52. (5) AQEShuffleRead
53. Input [4]: [bid#403, name#405, point_x#422, point_y#423]
54. Arguments: coalesced
56. (6) Sort [codegen id : 3]
57. Input [4]: [bid#403, name#405, point_x#422, point_y#423]
58. Arguments: [bid#403 ASC NULLS FIRST], false, 0
60. (7) Scan parquet bi_report.mon_ronghe_pv
61. Output [3]: [bid#440, pv#441, event_day#444]
62. Batched: true
63. Location: InMemoryFileIndex [afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/warehouse/bi_report.db/mon_ronghe_pv/event_day=20240522]
64. PartitionFilters: [isnotnull(event_day#444), (event_day#444 = 20240522)]
65. PushedFilters: [IsNotNull(bid), IsNotNull(pv), StringStartsWith(bid,1), GreaterThan(pv,100)]
66. ReadSchema: struct<bid:string,pv:int>
68. (8) ColumnarToRow [codegen id : 2]
69. Input [3]: [bid#440, pv#441, event_day#444]
71. (9) Filter [codegen id : 2]
72. Input [3]: [bid#440, pv#441, event_day#444]
73. Condition : (((isnotnull(bid#440) AND isnotnull(pv#441)) AND StartsWith(bid#440, 1)) AND (pv#441 > 100))
75. (10) Project [codegen id : 2]
76. Output [2]: [bid#440, pv#441]
77. Input [3]: [bid#440, pv#441, event_day#444]
79. (11) Exchange
80. Input [2]: [bid#440, pv#441]
81. Arguments: hashpartitioning(bid#440, 600), ENSURE_REQUIREMENTS, [plan_id=1067]
83. (12) ShuffleQueryStage
84. Output [2]: [bid#440, pv#441]
85. Arguments: 1
87. (13) AQEShuffleRead
88. Input [2]: [bid#440, pv#441]
89. Arguments: coalesced
91. (14) Sort [codegen id : 4]
92. Input [2]: [bid#440, pv#441]
93. Arguments: [bid#440 ASC NULLS FIRST], false, 0
95. (15) SortMergeJoin [codegen id : 5]
96. Left keys [1]: [bid#403]
97. Right keys [1]: [bid#440]
98. Join condition: None
100. (16) Project [codegen id : 5]
101. Output [5]: [bid#403, name#405, point_x#422, point_y#423, pv#441]
102. Input [6]: [bid#403, name#405, point_x#422, point_y#423, bid#440, pv#441]
104. (17) CollectLimit
105. Input [5]: [bid#403, name#405, point_x#422, point_y#423, pv#441]
106. Arguments: 1000
108. (18) LocalLimit
109. Input [4]: [bid#403, name#405, point_x#422, point_y#423]
110. Arguments: 1000
112. (19) Exchange
113. Input [4]: [bid#403, name#405, point_x#422, point_y#423]
114. Arguments: hashpartitioning(bid#403, 600), ENSURE_REQUIREMENTS, [plan_id=1029]
116. (20) Sort
117. Input [4]: [bid#403, name#405, point_x#422, point_y#423]
118. Arguments: [bid#403 ASC NULLS FIRST], false, 0
120. (21) Filter
121. Input [3]: [bid#440, pv#441, event_day#444]
122. Condition : (((isnotnull(bid#440) AND isnotnull(pv#441)) AND StartsWith(bid#440, 1)) AND (pv#441 > 100))
124. (22) Project
125. Output [2]: [bid#440, pv#441]
126. Input [3]: [bid#440, pv#441, event_day#444]
128. (23) Exchange
129. Input [2]: [bid#440, pv#441]
130. Arguments: hashpartitioning(bid#440, 600), ENSURE_REQUIREMENTS, [plan_id=1030]
132. (24) Sort
133. Input [2]: [bid#440, pv#441]
134. Arguments: [bid#440 ASC NULLS FIRST], false, 0
136. (25) SortMergeJoin
137. Left keys [1]: [bid#403]
138. Right keys [1]: [bid#440]
139. Join condition: None
141. (26) Project
142. Output [5]: [bid#403, name#405, point_x#422, point_y#423, pv#441]
143. Input [6]: [bid#403, name#405, point_x#422, point_y#423, bid#440, pv#441]
145. (27) CollectLimit
146. Input [5]: [bid#403, name#405, point_x#422, point_y#423, pv#441]
147. Arguments: 1000
149. (28) AdaptiveSparkPlan
150. Output [5]: [bid#403, name#405, point_x#422, point_y#423, pv#441]
151. Arguments: isFinalPlan=true

复制代码

因为左关联，on中的条件属于连接条件，结果必要保存左表全部记载，以是t1表全量读取，t2表使用了谓词下推过滤。
2）where

1. select
2.         t1.bid,
3.     t1.name,
4.     t1.point_x,
5.     t1.point_y,
6.         t2.pv
7. from poi_data.poi_res t1
8. left join (select bid, pv from bi_report.mon_ronghe_pv where event_day='20240522') t2
9. on t1.bid=t2.bid
10. where t2.bid like '1%' and t2.pv>100;

复制代码

1. == Physical Plan ==
2. AdaptiveSparkPlan (28)
3. +- == Final Plan ==
4.    CollectLimit (17)
5.    +- * Project (16)
6.       +- * SortMergeJoin Inner (15)
7.          :- * Sort (6)
8.          :  +- AQEShuffleRead (5)
9.          :     +- ShuffleQueryStage (4), Statistics(sizeInBytes=5.3 GiB, rowCount=4.57E+7)
10.          :        +- Exchange (3)
11.          :           +- * Filter (2)
12.          :              +- Scan hive poi_data.poi_res (1)
13.          +- * Sort (14)
14.             +- AQEShuffleRead (13)
15.                +- ShuffleQueryStage (12), Statistics(sizeInBytes=58.5 MiB, rowCount=1.28E+6)
16.                   +- Exchange (11)
17.                      +- * Project (10)
18.                         +- * Filter (9)
19.                            +- * ColumnarToRow (8)
20.                               +- Scan parquet bi_report.mon_ronghe_pv (7)
21. +- == Initial Plan ==
22.    CollectLimit (27)
23.    +- Project (26)
24.       +- SortMergeJoin Inner (25)
25.          :- Sort (20)
26.          :  +- Exchange (19)
27.          :     +- Filter (18)
28.          :        +- Scan hive poi_data.poi_res (1)
29.          +- Sort (24)
30.             +- Exchange (23)
31.                +- Project (22)
32.                   +- Filter (21)
33.                      +- Scan parquet bi_report.mon_ronghe_pv (7)
36. (1) Scan hive poi_data.poi_res
37. Output [4]: [bid#456, name#458, point_x#475, point_y#476]
38. Arguments: [bid#456, name#458, point_x#475, point_y#476], HiveTableRelation [`poi_data`.`poi_res`, org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, Data Cols: [bid#456, type#457, name#458, address#459, phone#460, alias#461, post_code#462, catalog_id#463, c..., Partition Cols: []]
40. (2) Filter [codegen id : 1]
41. Input [4]: [bid#456, name#458, point_x#475, point_y#476]
42. Condition : (StartsWith(bid#456, 1) AND isnotnull(bid#456))
44. (3) Exchange
45. Input [4]: [bid#456, name#458, point_x#475, point_y#476]
46. Arguments: hashpartitioning(bid#456, 600), ENSURE_REQUIREMENTS, [plan_id=1176]
48. (4) ShuffleQueryStage
49. Output [4]: [bid#456, name#458, point_x#475, point_y#476]
50. Arguments: 0
52. (5) AQEShuffleRead
53. Input [4]: [bid#456, name#458, point_x#475, point_y#476]
54. Arguments: coalesced
56. (6) Sort [codegen id : 3]
57. Input [4]: [bid#456, name#458, point_x#475, point_y#476]
58. Arguments: [bid#456 ASC NULLS FIRST], false, 0
60. (7) Scan parquet bi_report.mon_ronghe_pv
61. Output [3]: [bid#493, pv#494, event_day#497]
62. Batched: true
63. Location: InMemoryFileIndex [afs://kunpeng.afs.baidu.com:9902/user/g_spark_rdw/rdw/poi_engine/warehouse/bi_report.db/mon_ronghe_pv/event_day=20240522]
64. PartitionFilters: [isnotnull(event_day#497), (event_day#497 = 20240522)]
65. PushedFilters: [IsNotNull(bid), IsNotNull(pv), StringStartsWith(bid,1), GreaterThan(pv,100)]
66. ReadSchema: struct<bid:string,pv:int>
68. (8) ColumnarToRow [codegen id : 2]
69. Input [3]: [bid#493, pv#494, event_day#497]
71. (9) Filter [codegen id : 2]
72. Input [3]: [bid#493, pv#494, event_day#497]
73. Condition : (((isnotnull(bid#493) AND isnotnull(pv#494)) AND StartsWith(bid#493, 1)) AND (pv#494 > 100))
75. (10) Project [codegen id : 2]
76. Output [2]: [bid#493, pv#494]
77. Input [3]: [bid#493, pv#494, event_day#497]
79. (11) Exchange
80. Input [2]: [bid#493, pv#494]
81. Arguments: hashpartitioning(bid#493, 600), ENSURE_REQUIREMENTS, [plan_id=1201]
83. (12) ShuffleQueryStage
84. Output [2]: [bid#493, pv#494]
85. Arguments: 1
87. (13) AQEShuffleRead
88. Input [2]: [bid#493, pv#494]
89. Arguments: coalesced
91. (14) Sort [codegen id : 4]
92. Input [2]: [bid#493, pv#494]
93. Arguments: [bid#493 ASC NULLS FIRST], false, 0
95. (15) SortMergeJoin [codegen id : 5]
96. Left keys [1]: [bid#456]
97. Right keys [1]: [bid#493]
98. Join condition: None
100. (16) Project [codegen id : 5]
101. Output [5]: [bid#456, name#458, point_x#475, point_y#476, pv#494]
102. Input [6]: [bid#456, name#458, point_x#475, point_y#476, bid#493, pv#494]
104. (17) CollectLimit
105. Input [5]: [bid#456, name#458, point_x#475, point_y#476, pv#494]
106. Arguments: 1000
108. (18) Filter
109. Input [4]: [bid#456, name#458, point_x#475, point_y#476]
110. Condition : (StartsWith(bid#456, 1) AND isnotnull(bid#456))
112. (19) Exchange
113. Input [4]: [bid#456, name#458, point_x#475, point_y#476]
114. Arguments: hashpartitioning(bid#456, 600), ENSURE_REQUIREMENTS, [plan_id=1161]
116. (20) Sort
117. Input [4]: [bid#456, name#458, point_x#475, point_y#476]
118. Arguments: [bid#456 ASC NULLS FIRST], false, 0
120. (21) Filter
121. Input [3]: [bid#493, pv#494, event_day#497]
122. Condition : (((isnotnull(bid#493) AND isnotnull(pv#494)) AND StartsWith(bid#493, 1)) AND (pv#494 > 100))
124. (22) Project
125. Output [2]: [bid#493, pv#494]
126. Input [3]: [bid#493, pv#494, event_day#497]
128. (23) Exchange
129. Input [2]: [bid#493, pv#494]
130. Arguments: hashpartitioning(bid#493, 600), ENSURE_REQUIREMENTS, [plan_id=1162]
132. (24) Sort
133. Input [2]: [bid#493, pv#494]
134. Arguments: [bid#493 ASC NULLS FIRST], false, 0
136. (25) SortMergeJoin
137. Left keys [1]: [bid#456]
138. Right keys [1]: [bid#493]
139. Join condition: None
141. (26) Project
142. Output [5]: [bid#456, name#458, point_x#475, point_y#476, pv#494]
143. Input [6]: [bid#456, name#458, point_x#475, point_y#476, bid#493, pv#494]
145. (27) CollectLimit
146. Input [5]: [bid#456, name#458, point_x#475, point_y#476, pv#494]
147. Arguments: 1000
149. (28) AdaptiveSparkPlan
150. Output [5]: [bid#456, name#458, point_x#475, point_y#476, pv#494]
151. Arguments: isFinalPlan=true

复制代码

where属于过滤条件，影响左关联的终极结果，以是实行计划第（2）步中将where提前到join关联之前按照bid对t1表举行过滤。
四、总结

假设数据库系统支持谓词下推的前提下，

• 内连接：内连接的两个实行计划中，对t2表都使用了PushedFilters: [IsNotNull(bid), IsNotNull(pv), StringStartsWith(bid,1), GreaterThan(pv,100)]，对t1表都使用了Condition : (StartsWith(bid#297, 1) AND isnotnull(bid#297)) ，因此可以说，内连接中where和on在实行服从上没区别。
  
• 外连接：照旧拿左外连接来说，右表相干的条件会使用谓词下推，而左表是否会提前过滤数据，取决于where照旧on以及筛选条件是否与左表相干，1）当为on时，左表的数据必须全量读取，此时服从的差别主要取决于左表的数据量。2）当为where时，假如筛选条件涉及到左表，则会举行数据的提前过滤，否则左表仍然全量读取。
  

PS

在内连接的物理实行计划中，对poi_res表的过滤单独作为一个Filter步骤（2）Condition : (StartsWith(bid#297, 1) AND isnotnull(bid#297))，而对mon_ronghe_pv表的过滤在第（7）步scan中PushedFilters: [IsNotNull(bid), IsNotNull(pv), StringStartsWith(bid,1), GreaterThan(pv,100)] ，二者有什么区别？查了一些资料，说的是可以将PushedFilters明白为在读取数据时的过滤，不满意条件的数据直接不读取。Filter时将数据读取之后，再判断是否满意条件，决定是否参与后续盘算。
既然都是在数据源侧举行数据过滤，为什么Filter不能像PushedFilters那样，直接在读取数据的时候判断，淘汰读入的数据量呢，如许也可以提升服从，这是一开始个人的疑问。查了一些资料，说的是是否支持在scan时filter数据，主要受数据源的影响。大数据中的存储方式主要分为行式存储和列式存储，列式存储的数据存储方式和丰富的元数据对谓词下推技术有更好的支持。当前测试中，mon_ronghe_pv表的存储格式为parquet，poi_res表存储格式text。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。