本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 使用 函數建置以提高效率 使用者定義的函數預設不會進行單一碎片最佳化，但可以設定為執行單一碎片操作。函數可以封裝邏輯，並確保以單一碎片最佳化的方式執行。 ## 為什麼單一碎片操作很重要 資源使用率對於效能和成本效益至關重要。相較於跨碎片操作，單一碎片操作使用的資源明顯較少。例如，執行函數插入一百萬個資料列時，單一碎片執行會使用大約 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. 從複雜資料擷取碎片金鑰，以叫用個別的單一碎片最佳化函數 1. 將跨碎片工作負載轉換為單一碎片最佳化，方法是將跨碎片邏輯與單一碎片最佳化陳述式分開 ### 從複雜資料擷取碎片索引鍵 考慮具有執行數個資料庫操作`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 ``` 執行時間的差異顯示了單一碎片最佳化的效能優勢。