使用 函數建置以提高效率 - Amazon Aurora
為什麼單一碎片操作很重要單一碎片操作和函數函數和波動分發 函數函數和效率模式檢查單一碎片操作

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

使用 函數建置以提高效率

使用者定義的函數預設不會進行單一碎片最佳化，但可以設定為執行單一碎片操作。函數可以封裝邏輯，並確保以單一碎片最佳化的方式執行。

為什麼單一碎片操作很重要

資源使用率對於效能和成本效益至關重要。相較於跨碎片操作，單一碎片操作使用的資源明顯較少。例如，執行函數以插入一百萬個資料列時，單一碎片執行會使用大約 90.5 ACUs，相較於 126.5 ACUs 進行跨碎片執行，資源效率提升 35%。

單一碎片執行也提供：

  • 輸送量比跨碎片操作高出 35%

  • 更可預測的回應時間

  • 隨著資料成長而提高可擴展性

單一碎片操作和函數

當符合下列任一先決條件時，函數會在碎片上執行：

  • 函數會建立為不可變，並包含在單一碎片最佳化查詢中

  • 函數由使用者分發

在碎片上執行的函數會執行和擴展更佳，因為它們會執行資料所在的位置。

函數和波動

若要檢查函數的波動，請在 PostgreSQL 的系統資料表上使用此查詢：

SELECT DISTINCT nspname, proname, provolatile 
FROM pg_proc PRO 
JOIN pg_namespace NSP ON PRO.pronamespace = NSP.oid 
WHERE proname IN ('random', 'md5');

輸出範例：

  nspname   | proname | provolatile 
------------+---------+-------------
 pg_catalog | md5     | i
 pg_catalog | random  | v
(2 rows)

在此範例中， md5() 是不可變的，並且random()是揮發性的。這表示包含 的單一碎片最佳化陳述式md5()會保持單一碎片最佳化，而包含 的陳述式random()則不會。

具有不可變函數的範例：

EXPLAIN ANALYZE 
SELECT pg_catalog.md5('123') 
FROM s1.t1 
WHERE col_a = 776586194 
  AND col_b = 654849524 
  AND col_c = '3ac2f2affb02987159ccd6ebd23e1ae5';
                          QUERY PLAN 
----------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) 
               (actual time=3.409..3.409 rows=1 loops=1)
 Single Shard Optimized
 Planning Time: 0.313 ms
 Execution Time: 4.253 ms
(4 rows)

使用揮發性函數的範例：

EXPLAIN ANALYZE 
SELECT pg_catalog.random() 
FROM s1.t1 
WHERE col_a = 776586194 
  AND col_b = 654849524 
  AND col_c = '3ac2f2affb02987159ccd6ebd23e1ae5';
                          QUERY PLAN 
------------------------------------------------------
 Foreign Scan on t1_fs00001 t1  
   (cost=100.00..15905.15 rows=1 width=8) 
   (actual time=0.658..0.658 rows=1 loops=1)
 Planning Time: 0.263 ms
 Execution Time: 2.892 ms
(3 rows)

輸出顯示 md5() 已下推並執行為單一碎片最佳化，而 random() 則否。

分發 函數

僅在一個碎片上存取資料的函數應該在該碎片上執行，以獲得效能優勢。必須分發函數，且函數簽章必須包含完整的碎片索引鍵 - 碎片索引鍵中的所有資料欄都必須作為參數傳遞至函數。

函數範例：

CREATE OR REPLACE FUNCTION s1.func1(
    param_a bigint, 
    param_b bigint, 
    param_c char(100)
) 
RETURNS int AS $$
DECLARE 
    res int;
BEGIN
    SELECT COUNT(*) INTO res
    FROM s1.t1
    WHERE s1.t1.col_a = param_a
      AND s1.t1.col_b = param_b
      AND s1.t1.col_c = param_c;
    
    RETURN res;
END
$$ LANGUAGE plpgsql;

在分佈之前，函數不是單一碎片最佳化：

EXPLAIN ANALYZE 
SELECT * FROM s1.func1(776586194, 654849524, '3ac2f2affb02987159ccd6ebd23e1ae5');
                                              QUERY PLAN 
------------------------------------------------------------------------------------------------------
 Function Scan on func1  (cost=0.25..0.26 rows=1 width=4) 
                         (actual time=37.503..37.503 rows=1 loops=1)
 Planning Time: 0.901 ms
 Execution Time: 51.647 ms
(3 rows)

若要分發函數：

SELECT rds_aurora.limitless_distribute_function(
    's1.func1(bigint,bigint,character)', 
    ARRAY['param_a','param_b','param_c'], 
    's1.t1'
);

分佈之後，函數會進行單一碎片最佳化：

EXPLAIN ANALYZE 
SELECT * FROM s1.func1(776586194, 654849524, '3ac2f2affb02987159ccd6ebd23e1ae5');
                                           QUERY PLAN 
------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) 
               (actual time=4.332..4.333 rows=1 loops=1)
 Single Shard Optimized
 Planning Time: 0.857 ms
 Execution Time: 5.116 ms
(4 rows)

您可以檢查 中的sso_calls資料欄，以確認單一碎片最佳化rds_aurora.limitless_stat_statements：

subcluster_id | subcluster_type | calls | sso_calls |                query 
--------------+-----------------+-------+-----------+--------------------------------------
 2            | router          |     2 |         1 | SELECT * FROM s1.func1( $1, $2, $3 )
 3            | router          |     1 |         1 | SELECT * FROM s1.func1( $1, $2, $3 )
(2 rows)

函數和效率模式

執行靠近資料的邏輯更有效率，而且函數在達成此目標時扮演重要角色。使用 函數提高效率有兩種主要使用案例：

  1. 從複雜資料擷取碎片金鑰，以叫用個別的單一碎片最佳化函數

  2. 透過將跨碎片邏輯與單一碎片最佳化陳述式分開，將跨碎片工作負載轉換為單一碎片最佳化

從複雜資料擷取碎片索引鍵

考慮具有執行數個資料庫操作s3.func3(p_json_doc json)之簽章的函數。這些操作將在交易中跨越所有碎片的所有碎片中執行。如果 JSON 文件包含碎片索引鍵，您可以建置單一碎片最佳化函數來執行資料庫操作。

原始模式：

s3.func3(p_json_doc json)
    database operation 1;
    database operation 2;
    database operation 3;

最佳化模式：

s3.func3(p_json_doc json)
DECLARE 
    v_a bigint;
BEGIN
    v_a := (p_json_doc->>'field_a')::bigint;
    SELECT s3.func3_INNER(v_a, p_json_doc);
END;

內部函數的執行位置：

s3.func3_INNER(p_a, p_json_doc)
    database operation 1 WHERE shard_key = p_a;
    database operation 2 WHERE shard_key = p_a;
    database operation 3 WHERE shard_key = p_a;

在此模式中，碎片金鑰會封裝在複雜的資料類型中，或從其他參數將其刪除。邏輯、資料存取和函數可以判斷、擷取或建構碎片金鑰，然後調用單一碎片最佳化函數，以僅執行有關單一碎片的操作。由於應用程式界面不會變更，因此最佳化比較容易測試。

從其他函數或資料延遲碎片索引鍵

當邏輯或資料存取計算或決定碎片索引鍵時，會套用另一個設計模式。這在大多數調用可在單一碎片上執行函數時非常有用，但偶爾需要跨碎片執行。

原始模式：

NEWORD(INTEGER, …) RETURNS NUMERIC
DECLARE
    all_whid_local := true;
    LOOP through the order lines
        Generate warehouse ID;
        IF generated warehouse ID == input warehouse ID
        THEN
            ol_supply_whid := input warehouse ID;
        ELSE
            all_whid_local := false;
            ol_supply_whid := generated warehouse ID;
        END IF;
        …
    END LOOP;
    …
    RETURN no_s_quantity;

具有不同函數的最佳化模式：

CREATE OR REPLACE FUNCTION NEWORD_sso(no_w_id INTEGER, …)
RETURNS NUMERIC
…
    RETURN no_s_quantity;
    …
END;
LANGUAGE 'plpgsql';

SELECT rds_aurora.limitless_distribute_function(
    'NEWORD_sso(int,…)', 
    ARRAY['no_w_id'], 
    'warehouse'
);

CREATE OR REPLACE FUNCTION NEWORD_crosshard(no_w_id INTEGER, …)
RETURNS NUMERIC
…
    RETURN no_s_quantity;
    …
END;
LANGUAGE 'plpgsql';

然後，讓主要函數呼叫單一碎片最佳化或跨碎片版本：

IF all_whid_local THEN
    SELECT NEWORD_sso(…) INTO no_s_quantity;
ELSE
    SELECT NEWORD_crosshard(…) INTO no_s_quantity;
END IF;

此方法可讓大多數調用受益於單一碎片最佳化，同時針對需要跨碎片執行的案例維持正確的行為。

檢查單一碎片操作

使用 EXPLAIN來驗證陳述式是否為單一碎片最佳化。輸出會明確報告「單一碎片最佳化」以進行最佳化操作。

分佈前的跨碎片調用：

                       QUERY PLAN 
---------------------------------------------------------------------
 Function Scan on func1  (cost=0.25..0.26 rows=1 width=4) 
                         (actual time=59.622..59.623 rows=1 loops=1)
 Planning Time: 0.925 ms
 Execution Time: 60.211 ms

分佈後的單一碎片叫用：

                       QUERY PLAN 
----------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) 
               (actual time=4.576..4.577 rows=1 loops=1)
 Single Shard Optimized
 Planning Time: 1.483 ms
 Execution Time: 5.404 ms

執行時間的差異顯示了單一碎片最佳化的效能優勢。