知识点滴 -- 函数三种稳定态及其对函数调用次数的影响

原创金仓数据库 2023-04-18

22199

一、函数的属性

KingbaseES 函数有三种稳定性级别：volatile、stable 和 immutable。默认情况下，创建函数的稳定性为volatile。以下是这三种函数的区别：

Volatile 函数体内可以做任何事情，包括修改数据。在调用时，输入同样的参数可能会返回不同的结果，比如：currtid 。在一个Query中，对于每一行都会重新计算该函数。
Stable 函数体内不能修改数据库，单个Query中针对所有行，给定同样的参数确保返回相同的结果。这种稳定级别允许优化器将多次函数调用转换为一次。在索引扫描的条件中使用这种函数是可行的，因为索引扫描只计算一次比较值(comparison value)，而不是每行都计算一次。
Immutable 函数不能修改数据库，在任何情况下，只要输入参数相同，返回结果就相同。这种级别的函数，优化器可以提前进行计算，在查询过程中作为常量参数。比如：Select ... Where x=2+2 可以简化为 Select ... Where x=4。
Deterministic 这是KingbaseES 为了与 Oracle 兼容而增加的一个属性，等价于 immutable 。

稳定性属性影响优化器的行为。为了得到最佳的性能结果，在创建函数时我们应该指定严格的稳定性级别。

二、不同稳定态与函数调用次数

以下举例说明函数的属性影响相同参数情况多次执行的效率。

1、构建函数和数据

构建数据

create table test(id integer); 并插入500条完全相同的数据。
insert into test select 1 from generate_series(1,500);

create table t1(id integer , name varchar(64));
insert into t1 select generate_series(1,1000000),md5(generate_series(1,1000000));

构建函数

create or replace function test_volatile(v_id integer)
    returns bigint
    volatile
    language sql
as
$$ select count(*) from t1 where id=v_id $$ ;

create or replace function test_stable(v_id integer)
    returns bigint
    stable
    language sql
as
$$ select count(*) from t1 where id=v_id $$ ;

create or replace function test_immutable(v_id integer)
    returns bigint
    immutable
    language sql
as
$$ select count(*) from t1 where id=v_id $$ ;

2、以列为参数调用函数

对于传入变量值，三个函数调用时间基本没有差别。因为，传入的参数是id 变量值（虽然实际值是相同的）。

test=# select count(*) from test where test_volatile(id)=1;
 count
-------
   500
(1 row)

Time: 26839.593 ms (00:26.840)

test=# select count(*) from test where test_stable(id)=1;
 count
-------
   500
(1 row)

Time: 26969.679 ms (00:26.970)

test=# select count(*) from test where test_immutable(id)=1;
 count
-------
   500
(1 row)

Time: 27316.593 ms (00:27.317)

3、以常量为参数调用函数

对于常量值，stable 和 immutable 类型的函数实际只需调用一次。

test=# select count(*) from test where test_volatile(1)=1;       -- 一条记录调用一次
 count
-------
   500
(1 row)

Time: 26931.872 ms (00:26.932)
test=# select count(*) from test where test_stable(1)=1;          -- 一个语句调用一次
 count
-------
   500
(1 row)

Time: 64.120 ms

test=# select count(*) from test where test_immutable(1)=1;
 count
-------
   500
(1 row)

Time: 63.230 ms

三、Immutable 与 Stable 差异的场景

Immutable 函数执行发生在规则优化时，也就是在SQL planner 时执行，只要执行一次；而Stable函数则在execute时执行，针对每条tuple，函数都要执行一次。

1、函数在等式右边的情况

test=# select count(*) from test where id=test_volatile(1);
 count
-------
   500
(1 row)

Time: 27456.622 ms (00:27.457)
test=# select count(*) from test where id=test_stable(1);
 count
-------
   500
(1 row)

Time: 26994.071 ms (00:26.994)

test=# select count(*) from test where id=test_immutable(1);
 count
-------
   500
(1 row)

Time: 68.484 ms

2、select 列表包含函数

test=# explain analyze select test_volatile(1) from test;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..133.00 rows=500 width=8) (actual time=65.768..27456.622 rows=500 loops=1)
 Planning Time: 0.133 ms
 Execution Time: 27456.622 ms
(3 rows)

test=# explain analyze select test_stable(1) from test;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..133.00 rows=500 width=8) (actual time=78.336..27451.777 rows=500 loops=1)
 Planning Time: 0.150 ms
 Execution Time: 27451.777 ms
(3 rows)

test=# explain analyze select test_immutable(1) from test;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..8.00 rows=500 width=8) (actual time=0.023..0.067 rows=500 loops=1)
 Planning Time: 64.822 ms
 Execution Time: 0.107 ms
(3 rows)

四、块体内循环调用的场景

可以看到，对于相同的输入参数， test_immutable 在同一query 只执行一次，volatile 与 stable 则要执行多次。

test=# \set SQLTERM /
test=#
test=# begin
test-#   for i in 1..100 loop
test-#     perform test_volatile(1);             
test-#   end loop;
test-# end;
test-# /
ANONYMOUS BLOCK

Time: 5456.121 ms (00:05.456)

test=#
test=# begin
test-#   for i in 1..100 loop
test-#     perform test_stable(1);              -- 一个语句调用一次
test-#   end loop;
test-# end;
test-# /
ANONYMOUS BLOCK

Time: 5311.829 ms (00:05.312)

test=# begin
test-#   for i in 1..100 loop
test-#     perform test_immutable(1);          -- 一个事务调用一次
test-#   end loop;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 66.533 ms

对于参数值，不管哪类函数，实际执行次数与循环次数相同。

test=# begin
test-#   for i in 1..100 loop
test-#     perform test_volatile(i);
test-#   end loop;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 5582.093 ms (00:05.582)

test=# begin
test-#   for i in 1..100 loop
test-#     perform test_stable(i);
test-#   end loop;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 5486.094 ms (00:05.486)

test=#
test=# begin
test-#   for i in 1..100 loop
test-#     perform test_immutable(i);
test-#   end loop;
test-# end;
test-# /
ANONYMOUS BLOCK
Time: 5475.526 ms (00:05.476)

五、Oracle处理差异

oracle 函数只有两种态：deterministic and volatile ， deterministic 相当于 immutable

构建数据

create table test(id integer);
insert into test select 1 from dba_objects where rownum<501;   --这里插入的id 都是相同的

create table t1(id number,name varchar(64));
insert into t1 select object_id,dbms_random.string('U',32) from dba_objects; --共插入100W条

构建存储过程

create or replace function test_deterministic(v_id integer)
    return integer
    deterministic
as
  cnt integer;
begin
  select count(*) into cnt from t1 where id = v_id;
  return cnt;
end ;


create or replace function test_volatile(v_id integer)
    return integer
as
  cnt integer;
begin
  select count(*) into cnt from t1 where id = v_id;
  return cnt;
end ;

测试结果如下：部分场景与 KingbaseES 有差异。

SQL> select * from test where test_volatile(id)=1;   --即使id 都是 1 ，也同样需要多次调用

no rows selected

Elapsed: 00:00:08.09

SQL> select * from test where test_deterministic(id)=1; --这个与KingbaseES 不同，因为id值都一样，Oracle只需调用一次。

no rows selected

Elapsed: 00:00:00.06

SQL> select * from test where id=test_volatile(1);

no rows selected

Elapsed: 00:00:08.17

SQL> select * from test where id=test_deterministic(1);

no rows selected

Elapsed: 00:00:00.06

SQL> select * from test where test_volatile(1)=1;  --这个与KingbaseES 不同，只需调用一次

no rows selected

Elapsed: 00:00:00.03

SQL> select * from test where test_deterministic(1)=1;

no rows selected

Elapsed: 00:00:00.02

注意：以上是因为test.id 值是完全相同的，如果值不同会怎样？

SQL> insert into test select object_id from dba_objects where rownum<501;
500 rows created.
SQL>SQL> select * from test where test_deterministic(id)=1;  --执行次数
no rows selected
Elapsed: 00:00:08.34

结论：Oracle 函数对于每个id 值都要执行一次，但如果id 是相同的，Oracle 则针对相同值只需要执行一次？（结果集缓存？）

人大金仓