範圍聯結優化

發行項
01/23/2025

當使用間隔或間隔重疊條件的點聯結兩個關聯時，就會發生 範圍聯結。 Databricks Runtime 中的範圍聯結優化支援可以大幅提升查詢效能，但需要仔細手動調整來達到最佳效果。

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

區間範圍連結中的點

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

-- 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;

間隔重疊範圍聯結

間隔重疊範圍聯結 是聯結，其中條件包含述詞，指定每個關聯兩個值之間的間隔重疊。例如：

-- 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;

範圍聯結優化

範圍聯結優化會針對下列聯結執行：

有某個條件可以解譯為間隔內的點或間隔重疊範圍的連接。
範圍聯結條件所涉及的所有值都是數值類型（整數、浮點數、十進位）、DATE或 TIMESTAMP。
範圍聯結條件所涉及的所有值都是相同的類型。在十進位類型的情況下，值也必須具有相同的刻度和精密度。
在某種情況下，它是 INNER JOIN，或者，如果是區間範圍聯結時，則是左側有點值的 LEFT OUTER JOIN 或右側有點值的 RIGHT OUTER JOIN。
具有量化大小微調參數。

量化大小

區間大小 是一個數值微調參數，會將範圍條件的值定義域分割成多個大小相等的區間。例如，使用間隔大小為10，優化會將網域分割成長度為10的間隔間隔。如果您的範圍條件 p BETWEEN start AND end為，且 start 為 8 且 end 為 22，這個值間隔會與長度為 10 的三個間隔重疊 –第一個間隔從 0 到 10，第二個間隔從 10 到 20，第三個間隔從 20 到 30。只有落在相同三個範疇內的點，才需要被視為在該範疇中可能的聯結匹配項目。例如，如果 p 為 32，則可以將其排除為介於 8 到 start 22 之間end，因為它落入間隔從 30 到 40。

注意

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

您可以使用查詢中的範圍聯結提示或設定工作階段組態參數，來指定 bin 大小。只有當您手動指定 bin 大小時，才會套用範圍聯結優化。區段選擇量化大小說明如何選擇最佳的量化大小。

使用範圍聯結提示選項啟用範圍聯結

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

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 的 a 聯結，而不是與 c聯結的聯結。

#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()
)

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

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")

使用會話設定啟用範圍聯結

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

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

此組態參數適用於任何具有範圍條件的聯結。不過，透過範圍連接提示設定的不同儲存格大小一律會覆寫透過參數所設定的大小。

選擇量化大小

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

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

如果間隔的長度相當一致且已知，建議您將組距大小設定為值間隔的一般預期長度。不過，如果間隔的長度不同且扭曲，則必須找到平衡點來設定間隔大小，以有效率地篩選短間隔，同時防止長間隔重疊太多間隔。假設資料表 ranges，其間隔介於數據行 start 和 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百分位數的值，可以重複相同的過程，並根據需要繼續進行。

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

共用方式為

範圍聯結優化

區間範圍連結中的點

間隔重疊範圍聯結

範圍聯結優化

量化大小

使用範圍聯結提示選項啟用範圍聯結

使用會話設定啟用範圍聯結

選擇量化大小

意見反應

其他資源