範圍 join 優化

發行項
12/27/2024

當通過使用區間或區間重疊條件來聯合兩個關係時，就會發生 區間 join。 Databricks Runtime 中 join 優化支援的範圍可能會帶來查詢效能的大幅改善，但需要仔細手動調整。

Databricks 建議在效能不佳時，針對範圍聯結使用 join 提示。

在區間範圍內的點 join

在區間範圍的點是一個，其中條件包含述詞，用於指定某個關聯的值介於另一個關聯的兩個之間。例如：

-- using BETWEEN expressions
SELECT *
FROM points JOIN ranges ON points.p BETWEEN ranges.start and ranges.end;

-- using inequality expressions
SELECT *
FROM points JOIN ranges ON points.p >= ranges.start AND points.p < ranges.end;

-- with fixed length interval
SELECT *
FROM points JOIN ranges ON points.p >= ranges.start AND points.p < ranges.start + 100;

-- join two sets of point values within a fixed distance from each other
SELECT *
FROM points1 p1 JOIN points2 p2 ON p1.p >= p2.p - 10 AND p1.p <= p2.p + 10;

-- a range condition together with other join conditions
SELECT *
FROM points, ranges
WHERE points.symbol = ranges.symbol
  AND points.p >= ranges.start
  AND points.p < ranges.end;

間隔重疊範圍 join

間隔重疊範圍 join 是一種 join，其中條件包含述詞，指定每個關聯中兩個 values 的間隔重疊之情形。例如：

-- overlap of [r1.start, r1.end] with [r2.start, r2.end]
SELECT *
FROM r1 JOIN r2 ON r1.start < r2.end AND r2.start < r1.end;

-- overlap of fixed length intervals
SELECT *
FROM r1 JOIN r2 ON r1.start < r2.start + 100 AND r2.start < r1.start + 100;

-- a range condition together with other join conditions
SELECT *
FROM r1 JOIN r2 ON r1.symbol = r2.symbol
  AND r1.start <= r2.end
  AND r1.end >= r2.start;

範圍 join 優化

針對下列連接，會對範圍 join 進行優化：

有條件可以解譯為間隔或間隔重疊範圍中的點，join。
範圍 join 條件所涉及的所有 values 都是數值類型（整數、浮點數、十進位數）、DATE或 TIMESTAMP。
與範圍 join 條件相關的所有 values 類型都相同。在十進位類型的情況下，values 也必須具有相同的比例與精度。
它可以是 INNER JOIN，或者在範圍 join的情況下，是左邊具有點值的 LEFT OUTER JOIN，或者是右側具有點值的 RIGHT OUTER JOIN。
具有量化大小微調參數。

量化大小

分組大小 是一個數值調整參數，會將範圍條件的 values 範圍分割成多個大小相等的分組。例如，使用間隔大小為10，優化會將網域分割成長度為10的間隔間隔。如果您的範圍條件 p BETWEEN start AND end為，且 start 為 8 且 end 為 22，這個值間隔會與長度為 10 的三個間隔重疊 –第一個間隔從 0 到 10，第二個間隔從 10 到 20，第三個間隔從 20 到 30。只有位於相同三個間隔內的點，才能視為該間隔的 join 相符專案。例如，如果 p 為 32，則可以將其排除為介於 8 到 start 22 之間end，因為它落入間隔從 30 到 40。

注意

針對 DATEvalues，bin 大小的值被視為天數。例如，bin 大小值為 7 代表一周。
針對 TIMESTAMPvalues，bin 大小的值會解譯為秒。如果需要子秒值，可以使用小數 values。例如，60 的 bin 大小值代表一分鐘，而 bin 大小值為 0.1 則代表 100 毫秒。

您可以在查詢中使用範圍 join 作為標示，或通過設定會話配置參數來指定 bin 大小。只有當您手動指定 bin 大小時，才會套用範圍優化。區段選擇量化大小說明如何選擇最佳的量化大小。

使用提示範圍 join 啟用範圍 join

若要在 SQL 查詢中啟用範圍 join 優化，您可以使用 範圍 join 提示 來指定 bin 大小。提示必須包含其中一個聯結關聯和數值量化大小參數的關聯名稱。關聯名稱可以是 table、檢視或子查詢。

SELECT /*+ RANGE_JOIN(points, 10) */ *
FROM points JOIN ranges ON points.p >= ranges.start AND points.p < ranges.end;

SELECT /*+ RANGE_JOIN(r1, 0.1) */ *
FROM (SELECT * FROM ranges WHERE ranges.amount < 100) r1, ranges r2
WHERE r1.start < r2.start + 100 AND r2.start < r1.start + 100;

SELECT /*+ RANGE_JOIN(c, 500) */ *
FROM a
  JOIN b ON (a.b_key = b.id)
  JOIN c ON (a.ts BETWEEN c.start_time AND c.end_time)

注意

在第三個範例中，您必須將提示c放在。這是因為聯結會保持關聯性，因此查詢會解譯為 (a JOIN b) JOIN c，而 a 上的提示會套用至具有 b 的 ajoin，而不是與 c搭配的 join。

#create minute table
minutes = spark.createDataFrame(
    [(0, 60), (60, 120)],
    "minute_start: int, minute_end: int"
)

#create events table
events = spark.createDataFrame(
    [(12, 33), (0, 120), (33, 72), (65, 178)],
    "event_start: int, event_end: int"
)

#Range_Join with "hint" on the from table
(events.hint("range_join", 60)
  .join(minutes,
    on=[events.event_start < minutes.minute_end,
    minutes.minute_start < events.event_end])
  .orderBy(events.event_start,
    events.event_end,
    minutes.minute_start)
  .show()
)

#Range_Join with "hint" on the join table
(events.join(minutes.hint("range_join", 60),
  on=[events.event_start < minutes.minute_end,
    minutes.minute_start < events.event_end])
  .orderBy(events.event_start,
    events.event_end,
    minutes.minute_start)
  .show()
)

您也可以將範圍 join 提示放在其中一個聯結的數據框架上。在此情況下，提示只包含數值量化大小參數。

val df1 = spark.table("ranges").as("left")
val df2 = spark.table("ranges").as("right")

val joined = df1.hint("range_join", 10)
  .join(df2, $"left.type" === $"right.type" &&
     $"left.end" > $"right.start" &&
     $"left.start" < $"right.end")

val joined2 = df1
  .join(df2.hint("range_join", 0.5), $"left.type" === $"right.type" &&
     $"left.end" > $"right.start" &&
     $"left.start" < $"right.end")

使用工作階段設定來啟用範圍 join

如果您不想修改查詢，您可以將 bin 大小指定為組態參數。

SET spark.databricks.optimizer.rangeJoin.binSize=5

此組態參數適用於具有範圍條件的任何 join。不過，透過範圍提示 join 的不同量化大小 set 總是會覆蓋 set，並非透過參數。

選擇量化大小

範圍 join 優化的有效性取決於選擇適當的分箱大小。

較小的量化大小會產生較大的量化，這有助於篩選可能的相符專案。不過，如果間隔大小明顯小於所遇到的值間隔，且值間隔重疊多個間隔間隔，就會變得沒有效率。例如，若條件 p BETWEEN start AND end，wherestart 為 1,000,000，而 end 為 1,999,999，且區間大小為 10，則數值區間與 100,000 個區間重疊。

如果間隔的長度相當一致且已知，建議您將間隔大小 set 為值間隔的一般預期長度。不過，如果間隔的長度不同且扭曲，則必須找到平衡點，以 set 有效篩選短間隔的間隔大小，同時防止長間隔重疊太多間隔。假設 tableranges，其間隔介於 columnsstart 和 end之間，您可以使用下列查詢來判斷扭曲間隔長度值的不同百分位數：

SELECT APPROX_PERCENTILE(CAST(end - start AS DOUBLE), ARRAY(0.5, 0.9, 0.99, 0.999, 0.9999)) FROM ranges

建議的間隔大小設定是值上限為第 90 個百分位數，或第 99 個百分位數的值除以 10，或 99.9 百分位數的值除以 100 等等。理由是：

如果第90個百分位數的值是間隔大小，則值間隔長度只有10%大於間隔間隔，因此跨越超過2個相鄰的間隔間隔。
如果第99個百分位數的值是間隔大小，則值間隔長度只有1%超過11個相鄰間隔。
如果第99.9個百分位數的值是間隔大小，則值間隔長度只有0.1%超過101個相鄰間隔。
可以針對第99.99、第99.999百分位數以及其他需求的百分位數進行相同的重複操作於 values。

所述的方法會限制重疊多個間隔間隔的扭曲長值間隔數量。以這種方式取得的量化大小值只是微調的起點;實際結果可能取決於特定工作負載。

共用方式為

範圍 join 優化

在區間範圍內的點 join

間隔重疊範圍 join

範圍 join 優化

量化大小

使用提示範圍 join 啟用範圍 join

使用工作階段設定來啟用範圍 join

選擇量化大小

意見反應

其他資源