優化巴貝爾魚中的相關子查詢 - Amazon Aurora
使用子查詢轉換提高巴貝爾魚查詢性能使用子查詢緩存來提高巴貝爾魚查詢性能

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

優化巴貝爾魚中的相關子查詢

相關子查詢從外部查詢引用表列。它是由外部查詢返回的每一行評估一次。在下列範例中，子查詢會參考資料表 t1 中的資料行。此資料表不包含在子查詢的子FROM句中，但會在外部查詢的子FROM句中參考。如果表 t1 有 1 百萬行，則子查詢需要評估 100 萬次。


SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);

使用子查詢轉換提高巴貝爾魚查詢性能

Babelfish 可以通過將它們轉換為等效的外部連接來加速相關的子查詢。此最佳化適用於下列兩種類型的相關子查詢：

  • 傳回單一彙總值並顯示在SELECT清單中的子查詢。如需詳細資訊，請參閱 Microsoft 交易SQL文件中的SELECT子句。

    
SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1;

  • 傳回單一彙總值並出現在子WHERE句中的子查詢。

    
SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;

在子查詢中啟用轉換

若要啟用相互關聯的子查詢轉換為對等的外部聯結，請將apg_enable_correlated_scalar_transform參數設定為。ON此參數在巴貝爾魚 4.2.0 及更高版本中提供。此參數的預設值為 OFF。

您可以修改叢集或執行處理參數群組來設定參數。如需進一步了解，請參閱 Amazon Aurora 的參數組 RDS。

或者，您可以通過調用該函數來僅配置當前會話的設置set_config。例如，執行下列命令以開啟 Babelfish 中的子查詢快取。若要深入瞭解，請參閱組態設定功能。


1> SELECT set_config('apg_enable_subquery_cache', 'on', false)
2> GO

驗證轉換

使用命EXPLAIN令來驗證相關的子查詢是否已轉換為查詢計畫中的外部聯結。如需詳細資訊，請參閱使用解釋計劃改善 Babelfish 查詢效能。

啟用轉換後，適用的相關子查詢部分將被轉換為外部連接。例如：


1> select set_config('apg_enable_correlated_scalar_transform', 'true', false);
2> GO
1> set BABELFISH_STATISTICS PROFILE on
2> GO
1> select customer_name, ( select max(o.cost) from correlated_orders o 
2> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount
3> from correlated_customers c order by customer_name ;
4> GO

QUERY PLAN                                                                                                                                                                                                                                                      
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Text: select customer_name, ( select max(o.cost)                                                                                                                                                                                                          
 from correlated_orders o where o.customer_id = c.customer_id                                                                                                                                                                                                   
 and o.total_amount > 10 and o.customer_id != 10                                                                                                                                                                                                                
) AS max_order_amount                                                                                                                                                                                                                                           
 from correlated_customers c order by customer_name                                                                                                                                                                                                             
Sort  (cost=88.23..90.18 rows=780 width=40)                                                                                                                                                                                                                     
  Sort Key: c.customer_name NULLS FIRST                                                                                                                                                                                                                         
  ->  Hash Left Join  (cost=30.90..50.76 rows=780 width=40)                                                                                                                                                                                                     
        Hash Cond: (c.customer_id = o.customer_id)                                                                                                                                                                                                              
        ->  Seq Scan on correlated_customers c  (cost=0.00..17.80 rows=780 width=36)                                                                                                                                                                            
        ->  Hash  (cost=28.73..28.73 rows=174 width=12)                                                                                                                                                                                                         
              ->  HashAggregate  (cost=26.99..28.73 rows=174 width=12)                                                                                                                                                                                          
                    Group Key: o.customer_id                                                                                                                                                                                                                    
                    ->  Seq Scan on correlated_orders o  (cost=0.00..25.30 rows=338 width=12)                                                                                                                                                                   
                          Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10))                                                                                                                                                                      
                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

當GUC參數轉動時，不會轉換相同的查詢OFF。計劃不會有外部聯結，而是子計劃。


1> select set_config('apg_enable_correlated_scalar_transform', 'false', false);
2> GO
1> select customer_name, ( select max(o.cost) 
2> from correlated_orders o 
3> where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount
4> from correlated_customers c order by customer_name ;
5> GO
QUERY PLAN                                                                                                                                                                                                                                                      
-------------------------------------------------------------------------------------------------------------------------
Sort  (cost=21789.97..21791.92 rows=780 width=40)                                                                                                                                                                                                               
  Sort Key: c.customer_name NULLS FIRST                                                                                                                                                                                                                         
  ->  Seq Scan on correlated_customers c  (cost=0.00..21752.50 rows=780 width=40)                                                                                                                                                                               
        SubPlan 1                                                                                                                                                                                                                                               
          ->  Aggregate  (cost=27.86..27.87 rows=1 width=8)                                                                                                                                                                                                     
                ->  Seq Scan on correlated_orders o  (cost=0.00..27.85 rows=2 width=8)                                                                                                                                                                          
                      Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))

限制

  • 子查詢必須在 select_list 中或其中一個條件中。否則，它將不會被轉換。

  • 子查詢必須返回一個聚合函數。轉換不支援使用者定義的彙總函式。

  • 返回表達式不是簡單聚合函數的子查詢將不會被轉換。

  • 子查詢子句中的相關WHERE條件應該是一個簡單的列引用。否則，它將不會被轉換。

  • 子查詢中的相關條件 where 子句必須是一個普通的相等謂詞。

  • 包含子TOP句的相關子查詢無法轉換。

  • 子查詢不能包含HAVING或 GROUP BY 子句。

  • 子查詢中的 where 子句可能包含一個或多個謂詞結合在一起。AND如果子WHERE句包含 OR 子句，則無法轉換。

使用子查詢緩存來提高巴貝爾魚查詢性能

從版本 4.2.0 開始，Babelfish 支持子查詢緩存來存儲相關子查詢的結果。當子查詢結果已存在於快取中時，此功能會略過重複的相關子查詢執行。

了解子查詢緩存

Postgre SQL 的記憶節點是子查詢緩存的關鍵部分。該 Memoize 節點維護在本地緩存哈希表從輸入參數值映射到查詢結果行。雜湊資料表的記憶體限制是工作記憶體和雜湊值乘數的乘積。若要深入了解，請參閱資源消耗。

在查詢執行期間，子查詢快取會使用快取命中率 (CHR) 來估計快取是否正在改善查詢效能，並決定在查詢執行階段是否繼續使用快取。CHR是快取命中次數與要求總數的比率。例如，如果相關子查詢需要執行 100 次，而這些執行結果中的 70 個可以從快取中擷取，則CHR為 0.7。

對於每個快取遺漏的 apg_subquery_cache_check_間隔數目可評估子查詢快取的效益是否大於apg_subquery_cache_hit_rate_threshold。CHR如果沒有，緩存將從內存中刪除，並且查詢執行將返回到原始的，未緩存的子查詢重新執行。

控制子查詢快取行為的參數

下表列出控制子查詢快取行為的參數。

參數

描述

預設

允許

APG 啟用子查詢緩存

啟用快取用於相關的純量子查詢。

OFF

 在上，OFF

apg_子查詢緩存_檢查間隔

設定評估子查詢快取命中率的頻率 (以快取遺漏數為單位)。

500

 0–2147483647

apg_子查詢緩存_hit_速率/值

設定子查詢快取命中率的臨界值。

0.3

 0.0—1.0
注意

  • 較大的值apg_subquery_cache_check_interval可能會改善CHR基礎快取效益估計的準確性，但會增加快取額外負荷，因為在快取資料表包含資料列之前，才CHR會進apg_subquery_cache_check_interval行評估。

  • 較大的apg_subquery_cache_hit_rate_threshold偏差值對放棄子查詢緩存並返回到原始的，未緩存的子查詢重新執行。

您可以修改叢集或執行處理參數群組來設定參數。若要深入瞭解，請參閱使用參數群組。

或者，您可以通過調用該函數來僅配置當前會話的設置set_config。例如，執行下列命令以開啟 Babelfish 中的子查詢快取。若要深入瞭解，請參閱組態設定功能。


1> SELECT set_config('apg_enable_subquery_cache', 'on', false)
2> GO

在巴貝爾魚打開子查詢緩存

目前，子查詢緩存默認情OFF況下。如上所述，您可以透過修改參數群組來將其開啟。如果apg_enable_subquery_cache是ON，Babelfish 會套用子查詢快取來儲存子查詢結果。然後，查詢計劃將在下有一個記憶節點。 SubPlan

例如，下列命令序列會顯示沒有子查詢快取的簡單相關子查詢的估計查詢執行計畫。若要深入瞭解，請參閱使用說明計畫來改善 Babelfish 查詢效能。


1> CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)
2> CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)
3> GO
1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'
2> GO
1> SET BABELFISH_SHOWPLAN_ALL ON
2> GO
1> SELECT outer_col1, (
2> SELECT inner_col1
3> FROM inner_table
4> WHERE inner_col2 = outer_col2
5> ) FROM outer_table
6> GO

QUERY PLAN
------------------------------------------------------------
Query Text: SELECT outer_col1, (
        SELECT inner_col1
        FROM inner_table
        WHERE inner_col2 = outer_col2
) FROM outer_table
Seq Scan on outer_table
  SubPlan 1
    ->  Seq Scan on inner_table
          Filter: (inner_col2 = outer_table.outer_col2)
          
    

1> SET BABELFISH_SHOWPLAN_ALL OFF
2> GO
1> EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'
2> GO

開啟之後apg_enable_subquery_cache，查詢計畫會包含節點下方的 Memoize SubPlan 節點，表示子查詢計劃使用快取。


            
Seq Scan on outer_table
  SubPlan 1
    ->  Memoize
          Cache Key: outer_table.outer_col2
          Cache Mode: logical
          ->  Seq Scan on inner_table
                Filter: (inner_col2 = outer_table.outer_col2)

實際的查詢執行計畫包含子查詢快取的更多詳細資訊，包括快取命中和快取遺漏。下面的輸出顯示插入一些值的表後，上面的例子查詢的實際查詢執行計劃。


            
Seq Scan on outer_table (actual rows=10 loops=1)
  SubPlan 1
    ->  Memoize (actual rows=1 loops=10)
          Cache Key: outer_table.outer_col2
          Cache Mode: logical
          Hits: 4  Misses: 6  Evictions: 0  Overflows: 0  Memory Usage: 1kB
          ->  Seq Scan on inner_table (actual rows=1 loops=6)
                Filter: (inner_col2 = outer_table.outer_col2)
                Rows Removed by Filter: 4

快取命中總數為 4，快取未命中總數為 6。如果命中和未命中的總數小於 Memoize 節點中的迴圈數，則表示CHR評估未通過，而且快取在某個時候已清理並放棄。然後，子查詢執行返回到原始未緩存的重新執行。

限制

子查詢緩存不支持相關子查詢的某些模式。這些類型的查詢將在沒有緩存的情況下運行，即使子查詢緩存打開：

  • IN//EXISTSANY/ALL相關的子查詢

  • 包含非確定性函數的相關子查詢。

  • 參考的外部資料表資料行VARBINARY或BINARY資料類型的BIT相關子查詢。