鸿蒙原生应用开发者激励计划发布！最高获百万现金！点击立即参与

摘要：如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销，也称为 “隐式转换”，也会影响到优化器生成的执行计划的不同。

一、问题现象

二、技术背景

三、处理过程

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;

 

 

四、处理结果

五、总结

询问 AI