GaussDB性能调优之隐式转换

一、问题现象

某项目上一段慢SQL，相关技术人员反馈条件中用到a.systemdate >= 20240422 and a.systemdate <= 20240422 执行耗时，而把条件改为 a.systemdate = 20240422这样的点查询后，执行耗时就变快了。用户认为这两个条件是一样的，那优化器生成的执行计划也应该要一样。

二、技术背景

SQL执行计划是一个节点树，显示GaussDB执行一条SQL语句时执行的详细步骤。每一个步骤为一个数据库运算符。

使用EXPLAIN命令可以查看优化器为每个查询生成的具体执行计划。EXPLAIN给每个执行节点都输出一行，显示基本的节点类型和优化器为执行这个节点预计的开销值。影响SQL语句计划的优劣有很多方面，其中表的字段类型应使用一致的数据类型，表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销，也称为 “隐式转换”，也会影响到优化器生成的执行计划的不同。

三、处理过程

3.1 创建表结构DDL定义

CREATE TABLE test1 (
	systemdate int4 NOT NULL,
	cbsserverid varchar(4) NOT NULL,
	busidate int4 NOT NULL,
	sno int4 NOT NULL,
	busiflowid varchar(32),
	trdsysid varchar(4),
	coreid varchar(4),
	operdate int4,
	cleardate int4,
	relativesno int4,
	busitype varchar(16),
	accountingpoint bpchar(1),
	bookkeepingpoint bpchar(1),
	settgroup varchar(32),
	settunit varchar(32) NOT NULL,
	settname varchar(256),
	fundacct varchar(32),
	fundunit varchar(32),
	fortuneid int8,
	orgid varchar(32),
	brhid varchar(32),
	custkind varchar(8),
	custgroup varchar(8),
	fundkind varchar(8),
	fundlevel varchar(8),
	fundgroup varchar(8),
	curcode varchar(3),
	bankcode varchar(10),
	fundeffect numeric(20,2),
	fundbal numeric(20,2),
	secuid varchar(32),
	market varchar(2),
	mainseat varchar(16),
	trdseat varchar(16),
	stkcode varchar(32),
	stktype varchar(2),
	stkname varchar(128),
	stkeffect numeric(20,4),
	stkbal numeric(20,4)
	CONSTRAINT cbs_node_logasset_pkey PRIMARY KEY (systemdate,custid,cbsserverid,sno,busidate)
);
CREATE INDEX idx_cbs_node_logasset_custid ON fs_das.fs_das_his.cbs_node_logasset (custid);
CREATE INDEX idx_cbs_node_logasset_fundacct ON fs_das.fs_das_his.cbs_node_logasset (fundacct);
CREATE TABLE test2(
	systemdate int4 NOT NULL,
	cbsserverid varchar(4) NOT NULL,
	custid int8 NOT NULL,
	corpid int4,
	serverid int4 NOT NULL,
	bizdate int4 NOT NULL,
	sno varchar(32) NOT NULL,
	fee_one_yhs numeric(12,2),
	fee_one_ghf numeric(12,2),
	fee_one_qsf numeric(12,2),
	fee_one_jygf numeric(12,2),
	fee_one_jsf numeric(12,2),
	fee_one_zgf numeric(12,2),
	fee_one_qtf numeric(12,2),
	fee_one_fxj numeric(12,2),
	remark varchar(1024),
	settrate numeric(12,8),
	moneytype_wb bpchar(1),
	clearedamt_wb numeric(19,2),
	fee_sxf_wb numeric(12,2),
	fee_jsxf_wb numeric(12,2),
	fee_two_yhs_wb numeric(12,2),
	fee_two_ghf_wb numeric(12,2),
	fee_two_qsf_wb numeric(12,2),
	fee_two_jygf_wb numeric(12,2),
	fee_two_jsf_wb numeric(12,2),
	fee_two_zgf_wb numeric(12,2),
	fee_two_qtf_wb numeric(12,2),
	CONSTRAINT cbs_node_logasset_ext_pk PRIMARY KEY (systemdate,custid,cbsserverid,sno,bizdate)
);

3.2 执行计划分析

explain analyze
select 
rownum + 0 as posstr, 
t.* 
from 
(
select  
a.busidate as bizdate,
a.cleardate,
a.orderdate, 
a.orgid,
a.settunit as custid,
a.settname as custname, 
a.fundacct as fundid,
case when a.fortuneid = 0 then '' else a.fortuneid end as fortuneid,
a.secuid, 
LPAD(CAST(a.busitype AS VARCHAR), 6, '0') as cbsbusitype,
a.curcode as moneytype,
a.market, 
a.stktype,
a.stkcode,
a.stkname, 
a.fundeffect,
a.fundbal,
a.stkbal, 
a.orderid, 
a.orderqty,
cast(a.orderprice as numeric(12,3)) as orderprice, 
a.matchtime, 
a.matchcode,
a.matchtimes,a.matchqty, 
cast(a.matchprice as numeric(12,3)) as matchprice,
a.matchamt,
cast(a.fee_yhs as numeric(19,3)) as feeyhs,
cast(a.fee_seat as numeric(19,3)) as feesxf, 
cast(a.fee_ghf as numeric(19,3)) as feeghf,
cast(a.fee_qsf as numeric(19,3)) as feeqsf,
cast(a.fee_jygf as numeric(19,3)) as feejygf,
cast(a.fee_jsf as numeric(19,3)) as feejsf,
cast(a.fee_zgf as numeric(19,3)) as feezgf, 
cast(b.fee_one_fxj as numeric(19,3)) as feeonefxj, 
cast(a.fee_chjzf as numeric(19,3)) as feechjzf,a.bankcode,
'' as bankname,COALESCE(d.businame,'') digestname, 
left(cast(to_date(a.orderdate,'yyyy-mm-dd') as varchar(32)), 10) || ' ' || case when 10 > cast(a.ordertime/1000000 as int) then '0' || cast(cast(a.ordertime/1000000 as int) as varchar(8)) else cast(cast(a.ordertime/1000000 as int) as varchar(8)) end || ':' || case when 10 > cast(a.ordertime/10000%100 as int) then '0' || cast(cast(a.ordertime/10000%100 as int) as varchar(8)) else cast(cast(a.ordertime/10000%100 as int) as varchar(8)) end || ':' || case when 10 > cast(a.ordertime/100%100 as int) then '0' || cast(cast(a.ordertime/100%100 as int) as varchar(8)) else cast(cast(a.ordertime/100%100 as int) as varchar(8)) end as ordertime, a.fee_jjgsglf as jjglffee, 
a.stkrealsettdate, a.fundrealsettdate 
from test1  a        -- 主表
left join test2 b   -- 主表的扩展表（主表左关联他的扩展表取它放在扩展表中的一些信息）
on a.systemdate = b.systemdate    -- 按年分区的键
 and a.custid = b.custid 
 and a.cbsserverid = b.cbsserverid 
 and a.sno = b.sno  -- 两边类型改为一致，执行计划走的是参数化路径，这样就快了。
 and a.busidate = b.bizdate
     -- (systemdate,custid,cbsserverid,sno,busidate) 主表的联合主键
     -- (systemdate,custid,cbsserverid,sno,bizdate)  扩展表的联合主键
left join cbs_comm_busitype d   -- d表没有过滤条件
  on a.busitype = d.busitype 
WHERE 
--a.systemdate >= 20240422 and a.systemdate <= 20240422  
--分区键                                             
-- actual time = 38564.818
  a.systemdate = 20240422  -- 分区的键 上面一句的条件，应该跟这个条件是等价的。为什么执行时间差那么久呢？   -- actual time = 110.894
  and a.fundacct = '880300015593' 
  and a.custid = 106931062
  and a.fortuneid ='0' 
order by a.systemdate ,a.sno desc
limit 10 offset 0 ) t;

• 针对上面 systemdate >= 20240422 and a.systemdate <= 20240422 这个条件计划优化器估算的不准，导致执行算子NestedLoop。
• 而对于条件 a.systemdate = 20240422 这种写法，优化器的估算让它执行算子hasjoin。
• 条件的这两种写法，看起来是一样的，执行结果等价的啊，为什么GaussDB优化器解析的执行计划又不同了呢？
• 第一种条件a.systemdate >= 20240422 and a.systemdate <= 20240422下，客户提供的的执行计划如下：

注：上图可以看到优化器基于cost估算的值，让它走了Nested Loop的方式，导致最终actual time=38564.806ms。上面有一个Select Partition 1..2是由于d表没有条件，并不是只有分区键一个字段关联了，就一定会走分区剪枝的，所以走了全表扫描（要想走分区剪枝，必须得具备有剪枝的条件）。

• 第二种条件a.systemdate = 20240422 点查下的执行计划如下：

注：上图可以看到优化器基于成本估算的值，让他走了hashjoin的方式，导致最终actual time=110.894ms。

四、处理结果

把test1表字段sno 跟关联表test2表字段sno的数据类型修改为一致后测试，避免进行隐式转换，计划执行规则路径，计划执行时间由actual time=38564.806ms，提升到actual time=110.894ms。

五、总结

条件1：a.systemdate >= 20240422 and a.systemdate <= 20240422 -- actual time =38564.806ms

条件2：a.systemdate = 20240422 -- actual time = 110.894ms

上面两个条件写法虽然在逻辑上是等价的，优化器可能会认为有多个可能的结果（尽管实际上只有一个），两个条件的成本估算的值不同，走的join方式就不一样了，进一步导致执行计划的时间就有差别。