构建函数实现高效操作 - Amazon Aurora
单分片操作非常重要的原因单分片操作和函数函数和不稳定性分配函数函数和效率模式检查单分片操作

构建函数实现高效操作

默认情况下，用户定义的函数不会进行单分片优化，但可以将函数配置作为单分片操作执行。函数可以封装逻辑并确保以单分片优化方式执行。

单分片操作非常重要的原因

资源使用率对于性能和成本效益来说非常重要。与跨分片操作相比，单分片操作使用的资源要少得多。例如，在执行插入一百万行的函数时，单分片执行使用大约 90.5 个 ACU，而跨分片执行使用 126.5 个 ACU，单分片执行的资源效率提高了 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)

您可以通过检查 rds_aurora.limitless_stat_statements 中的 sso_calls 列来确认是否进行了单分片优化：

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

执行时间的差异证明了单分片优化的性能优势。