GaussDB(DWS)性能调优：IMMUTABLE类型函数最优实践【华为云DWS Studio SQL编辑器体验】

【华为云DWS Studio SQL编辑器体验】## 1、函数类型
DWS函数存在三种类型的属性：IMMUTABLE、STABLE、VOLATILE
详细介绍可见链接：https://support.huaweicloud.com/sqlreference-dws/dws_06_0163.html

1.1 IMMUTABLE

该函数在任何情况下同样的输入参数永远返回同样的结果
如果函数的入参是常量，会在优化器阶段计算该函数的值。益处是可以尽早获取表达式的值，从而能更准确的进行代价估算，生成的执行计划也更优。
这类型函数在入参相同的时候，返回结果总是固定的，不受任何环境或者配置参数的影响。一般这类满足以下特征:

1. 函数的入参的数据解析不受任何配置参数影响
2. 函数体内调用的函数的易变属性必须都是IMMUTABLE
3. 函数内部不涉及表查询动作

1.2 STABLE

该函数不能修改数据库，在固定的配置下，在同一个查询中，对于同样的输入参数，在同一次表扫描里，该函数的返回值不变，但是返回值可能在不同SQL语句之间变化。
这类型函数在入参相同的时候，返回结果总是固定的。但是配置参数一旦变更，结果就可能发生变更。一般这类函数至少满足以下特征

1. 函数的入参的数据解析受配置参数影响，不同配置情况下，解析结果不一致
2. 函数体内调用的函数处理逻辑受配置参数影响，不同配置情况下，解析结果不一致
3. 函数体内不能调用的函数不能包含易变属性是VOLATILE的函数
4. 函数体内涉及的表查询只能是用户表，且必须是复制表；如果为其它分布表，则不能定义为stable

1.3 VOLATILE

对于同样的输入参数，该函数值可以在一次表扫描内改变，即每次返回结果可能不同，因此不会做任何优化。
作为函数默认值，不满足STABLE和IMMUTABLE属性的函数的易变属性都要定义为VOLATILE

2、IMMUTABLE类型函数的使用

2.1 错误方法❌

调用SQL

select *,schemaname.function1(字段名) from Table limit 10000;

函数1——解密数据：

CREATE OR REPLACE FUNCTION schemaname.function1(p_s character varying DEFAULT NULL::character varying)
RETURNS character varying
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
	DECLARE
	v_key CLOB;
AS $function$ 
	BEGIN
	SELECT * into v_key FROM schemaname.function_invoke();
	RETURN gs_decrypt(p_s,v_key, 'aes128', 'cbc', 'sha256' )::VARCHAR;
--异常处理
	EXCEPTION 
	WHEN OTHERS THEN
		RAISE EXCEPTION '%',
		SQLERRM;
END $function$

函数2——解密密钥：

CREATE OR REPLACE FUNCTION schemaname.function_invoke()
RETURNS character varying
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $function$ 
	DECLARE
	v_eek VARCHAR;
	v_key VARCHAR;
BEGIN

	select t.key into v_eek from s1.t1 t where t.id = 1;
	select t.key into v_key from s1.t1 t where t.id = 2;
  RETURN gs_decrypt(v_key, v_eek, 'aes128', 'cbc',  'sha256');

--异常处理
	EXCEPTION 
	WHEN OTHERS THEN
		RAISE EXCEPTION '%',
		SQLERRM;
	
END; 
$function$

分析

查看该SQL的performance计划，可以看出性能瓶颈在于表扫描。从SQL执行过程分析，function1函数下推到DN执行，扫描表中每一行数据然后当作入参，在function1中调用function_invoke。执行过程中，function1函数入参在改变，function_invoke无入参（即入参相同）。此时，由于function1函数入参不同，导致每次调用都要重新执行function1与function_invoke函数，严重影响SQL执行性能。

2.2 正确方法✔

调用SQL

select *,schemaname.function1(字段名,[function_invoke()]) from Table limit 10000;

函数1——解密数据：

CREATE OR REPLACE FUNCTION schemaname.function1(p_s character varying DEFAULT NULL::character varying, v_key text DEFAULT schemaname.function_invoke())
RETURNS character varying
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $function$ 
	BEGIN
	RETURN gs_decrypt(p_s,v_key, 'aes128', 'cbc', 'sha256' )::VARCHAR;
--异常处理
	EXCEPTION 
	WHEN OTHERS THEN
		RAISE EXCEPTION '%',
		SQLERRM;
END $function$

函数2——解密密钥：

CREATE OR REPLACE FUNCTION schemaname.function_invoke()
RETURNS character varying
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $function$ 
	DECLARE
	v_eek VARCHAR;
	v_key VARCHAR;
BEGIN

	select t.key into v_eek from s1.t1 t where t.id = 1;
	select t.key into v_key from s1.t1 t where t.id = 2;
  RETURN gs_decrypt(v_key, v_eek, 'aes128', 'cbc',  'sha256');

--异常处理
	EXCEPTION 
	WHEN OTHERS THEN
		RAISE EXCEPTION '%',
		SQLERRM;
	
END; 
$function$

调用SQL后，查看performance计划，相比优化前性能提升了100倍。这种调用方式，更好地利用了function_invoke函数的IMMUTABLE属性（在任何情况下同样的输入参数永远返回同样的结果）