lvorySQL 2025生态大会暨PostgreSQL高峰论坛：性能优化之大气磅礴和精雕细作

性能优化之大气磅礴和精雕细作数据库遗老的经验分享IvorySQL

2025生态大会暨PostgreSQL高峰论坛

大气磅礴与精雕细琢

OLTP型技术的运用

OLAP型技术的运用

总结和交流目录CONTENTSIvorySQL

2025生态大会暨PostgreSQL高峰论坛IT系统两大类型:OLTP/OLAPIvorySQL

2025生态大会暨PostgreSQL高峰论坛比较项目OLTP应用OLAP应用业务特征操作特点日常业务操作，

尤其是大量前

台操作后台操作，

例如统计报表

、大批量数据加载响应速度优先级最高，

要求反应速度非常高要求速度高

、吞吐量大吞吐量小大并发访问量非常高不高单笔事务的资

源消耗小大SQL语句类型主要是插入和修改操作（DML）

,查询操作是大海捞

针式主要是大量查询操作或批量

DML

操作技术运用索引类型B*索引Bitmap

、BitmapJoin索引索引量适量多访问方式按索引访问全表扫描/全分区扫描连接方式Nested_loopHashJoinBIND变量使用或强制使用不使用并行技术使用不多大量使用分区技术使用使用物化视图使用少量使用大量使用OLTP应用和OLAP应用无论在业务操作特征，

还是在技术运用方面均有很大区别•OLTP应用和OLAP应用的很多技术运用策略恰恰相反性能优化总体策略OLTP和OLAP分别有很多专项技术IvorySQL

2025生态大会暨PostgreSQL高峰论坛80%的性能问题是由20%的应用导致的

。

如少量大表的全表扫描导

致的性能瓶颈

。

并不是应用一有问题，

就一定要对现有数据库结

构大卸八块，

应用推倒重来。性

化

规

暨PostgreSQL高峰论坛

应用设计对系统性能的影响能占到80%，

而数据库体系结构的设

计

、数据库系统参数调整

、操作系统参数的调整等系统方面因素，

只占到20%。80%的性能问题可以由20%的优化技术所解决

。

如简单的索引策略，执行路径分析等，

能解决绝大部分性能问题。能优

之20/80

则

IvorySQL

2025生态大会系统软件Bug架构和数据库设计应用开发硬件配置其它ⅢIⅡ优化项目目的1：

降低现有系统运行负载压力根据国家信创政策要求，

主要业务系统将迁移到国产化数据库平台开展对现有基于Oracle平台系统进行全面优化项目某央企国产化迁移和优化项目优化项目目的2：

降低向数据库迁移和运行的风险和负载IvorySQL

2025生态大会暨PostgreSQL高峰论坛重点优化的I/O指标下降达到68%运用awrdiff工具(AWR对比分析工具)评估优化效果几乎所有指标都呈下降趋势某系统优化效果IvorySQL

2025生态大会暨PostgreSQL高峰论坛优化建议•

创建索引•

实施分区•

修改语句优化实施情况•

创建索引•

没有实施分区•

没有修改语句某系统优化措施IvorySQL

2025生态大会暨PostgreSQL高峰论坛select

*

from

(selectrow_.*,

rownum

rn

from

(SELECT

tt.*

FROM

(SELECT

POST.*, e.emp_nameASoperatorEmpName, ve.acco_item_code, ve.acco_item_name

FROM

(SELECTDISTINCT

* FROMRFM_POST_VOUCHER

t WHEREt.is_delete

=

'0'

and

t.is_search

!=

'1'or

t.is_search

isnull)

andt.account_date

>=

to_timestamp

(:1,

'yyyymmddHH24:MI:SS.FF')

andt.account_date

<=

to_timestamp

(:2,

'yyyymmddHH24:MI:SS.FF')

andt.voucher_state_code

in

(:3)

andt.COMP_CODE

in

(:4)

andt.IS_HANDWORK

=

:5)

POST leftjoinmdm_employee

e one.emp_id=

POST.operator_code leftjoin

rfm_voucher_entryve onve.voucher_id=POST.voucher_id)

tt

WHEREexists

(select

1

from

(selectdbc.sec_valueas

org_id fromdic_bigsecurity_rcachedbc

wheredbc.sec_role

in

(:6,

:7,

:8,...

:317)) anddbc.bussobject_code=

'RMF0001')B WHEREB.org_id=

t_id)

andtt.acco_Item_Code=

:318)

row_

where

rownum<=

10)

where

rn

>

0语句总体特点分析•涉及RFM_POST_VOUCHER、md

m_employee

、rfm_voucher_entry、

dic_bigsecurity_rcache四张表的关联查询•按时间

、单位查询RFM_POST_VOUCHER表•按权限查询dic_bigsecurity_rcache表•先忽略标志

、代码等过滤条件•RFM_POST_VOUCHER和dic_bigsecurity_rcache含谓词条件，

因此这两张表的哪个表做驱动表是优化的重点方向•分页查询,但没有orderby操作，

随机查询10条语句？•典型的OLTP交易型查询语句语句一优化过程

（

1

）IvorySQL

2025生态大会暨PostgreSQL高峰论坛现有执行计划分析•

先按RFM_POST_VOUCHER(COMP_CODE、account_date)组合索引访问RFM_POST_VOUCHER表•

再按md

m_employee(emp_id)主键索引访问

md

m_employee表•

再按rfm_voucher_entry(voucher_id)索引访问

rfm_voucher_entry表•

最后按dic_bigsecurity_rcache(sec_role)索引访问

dic_bigsecurity_rcache表优化空间•

按dic_bigsecurity_rcache(sec_role)索引访问dic_bigsecurity_rcache表的cost达到3006，

有优化空间•

应通过被驱动表(dic_bigsecurity_rcache)的连接字段(org_id)与其谓词字段(sec_role)的组合索引访问被驱动表(dic_bigsecurity_rcache)•

即创建dic_bigsecurity_rcache(sec_value,sec_role)索引组合索引account_date)

(emp_id)主键

(voucher_id)

(sec_role)索组合索引

索引

索引

引RFM_POST_VOUCHERmdm_employeerfm_voucher_entrydic_bigsecurity_rcache语句一优化过程

（

2

）IvorySQL

2025生态大会暨PostgreSQL高峰论坛(COMP_CODE、(

Ct_

)、

(emp_id)

(voucher_id)

(sec_

l

索,s

_role)组合索引

主键索引

索引

RFM_POST_VOUCHERmdm_employeerfm_voucher_entrydic_bigsecurity_rcache引ec合ue组vadateODEccounOMP_aC优化后执行计划分析•执行计划没有显著变化，

只是最后按新建的

dic_bigsecurity_rcache(sec_value,sec_role)组合索引访问dic_bigsecurity_rcache表•Cost从3006下降为306，

下降10倍•经验总结•应通过被驱动表的连接字段与其谓词字段的

组合索引访问被驱动表•

除非被驱动表的连接字段与其谓词字段已经

是主键索引，

或者谓词条件选择性不高语句一优化过程

（3）IvorySQL

2025生态大会暨PostgreSQL高峰论坛•由于account_date查询范围达到3年，

优化器先按

dic_bigsecurity_rcache(sec_role)索引访问dic_bigsecurity_rcache表•再按RFM_POST_VOUCHER(org_id)索引访问

RFM_POST_VOUCHER表•再按mdm_employee(emp_id)主键索引访问mdm_employee表•最后按rfm_voucher_entry(voucher_id)索引访问

rfm_voucher_entry表•RFM_POST_VOUCHER(org_id)索引访问RFM_POST_VOUCHER表，

记录数达到3M，

过滤性很差•因此应该在被驱动表RFM_POST_VOUCHER的连接字段org_id和过滤性强的COMP_CODE谓词字段创建RFM_POST_VOUCHER(org_id,COMP_CODE)组合索引。•由于account_date查询范围达到3年，

过滤性差，

不宜设置为组合索引字段语句一优化过程

（

4

）•优化空间rfm_voucher_entry

mdm_employee

RFM_POST_VOUCHER

dic_bigsecurity_rcache真实执行计划分析IvorySQL

2025生态大会暨PostgreSQL高峰论坛(org_id,COMP_CODE)组合索引(voucher_id)索引(sec_role)索引(emp_id)主键索引•

优化效果显著•

组合索引的神奇功效•

Adaptive

Cursor

Sharing特性能让Oracle优化器根据输入条件的不同，

灵活选择不同的驱动表和最佳执行计划•

Oracle优化器无法自动创建组合索引，

需要开发人员和DBA的精准数据分析，

才

能做出合理的组合索引设计•

优化器和人的智能高度融合优化前优化后运行时间535.41秒3.2秒逻辑读1,024,872*8K=8,006MB4,075*8K=31.83MB物理读943,646*8K=7,372MB3,428*8K=26.78MB语句一优化总结总结IvorySQL

2025生态大会暨PostgreSQL高峰论坛语句总体特点分析•

按时间进行数据update操作，F_date=TO_CHAR(SYSDATE,

‘YYYYMM’

)表示对当月数据进行处理•

典型的OLAP大批量数据处理语句========================================================================================================================|

Name

|

Position

|Type|Value

|========================================================================================================================|:

_

|

|

|

|

========================================================================================================================|||I)(:MDDMKXXMMSYYBYYDRYYYFZ222222333333222222RRRRRRAAAAAAHHHHHHCCCCCCRRRRRRAAAAAAVVVVVV765421654310BBBBBBSSSSSSYYYYYYSSSSSS AND((F_DATE=TO_CHAR(SYSDATE,

:"SYS_B_3")AND

F_ZQLX=

:"SYS_B_4") (select

:"SYS_B_2"fromZJWBSKXXYWwhereYW.F_ID=JL.F_DJID)语句二优化过程

（

1

） (F_DATE=TO_CHAR(SYSDATE,

:"SYS_B_5")AND

F_ZQLX=

:"SYS_B_6"))set

F_DJBH

= (select

F_DJBHfromZJWBSKXXYWwhereYW.F_ID=JL.F_DJID)IvorySQL

2025生态大会暨PostgreSQL高峰论坛 whereJL.F_YWTABLE=

:"SYS_B_0"

andJL.F_YWCOL=

:"SYS_B_1"updateZJ_YJH_ZXJL_HISJL andtrim(JL.F_DJBH)is

null绑定变量情况andexistsOR:现有执行计划分析•先按ZJ_YJH_ZXJL_HIS(F_DATE,

…

)6个字段组成的组合索引访问ZJ_YJH_ZXJL_HIS表•再按ZJWBSKXX(F_ID)主键索引访问

ZJWBSKXX表暨PostgreSQL高峰论坛

语句二优化过程

（

2

）IvorySQL

2025生态大会•虽然按索引访问两个表，

但存在大量索引回表操作，

资源消耗依然很大，

性能并不是很好•

既然是访问当前月正月数据，

如果采取分区策略，无需索引，

直接通过分区裁剪和分区扫描访问技术，

避免大量索引回表操作优化空间ZJ_YJH_ZXJL_HISZJWBSKXX(F_DATE,

…

)6个字段组成的组合索引(F_ID)主键索引第一次优化执行计划分析•对ZJ_YJH_ZXJL_HIS表基于F_DATE字段按月分区•首先，

基于分区裁剪技术对ZJ_YJH_ZXJL_HIS表的当月数据进行分区扫描•其次，

再按ZJWBSKXX(F_ID)主键索引访问ZJWBSKXX表暨PostgreSQL高峰论坛

语句二优化过程

（3）

IvorySQL

2025生态大会•性能有一定提升，

但对ZJWBSKXX表依然按

索引进行大量数据的回表访问，

性能提升有限…

ZJWBSKXX2018.012018.022025.06优化效果ZJ_YJH_ZXJL_HIS表(F_ID)主键索引第二次优化执行计划分析•

对ZJWBSKXX表基于F_TXDATE字段按月分区•

首先，

基于分区裁剪技术对ZJ_YJH_ZXJL_HIS表的当月数据进行分区扫描•

其次，

基于分区裁剪技术对ZJWBSKXX表的当月数据进行分区扫描•

最后，

通过HASHJOIN技术进行两个分区表裁剪之后的关联操作

…

2025.062018.012018.022025.06语句二优化过程

（

4

）语句增加如下时间条件：ZJ_YJH_ZXJL_HIS表

ZJWBSKXX表IvorySQL

2025生态大会暨PostgreSQL高峰论坛2018.012018.02…运行时间逻辑读优化前00:00:07.03863,642*8K=6,747MB第一次优化00:00:04.28664,831*8K=5,193MB第二次优化00:00:00.4762036*8192=484MB索引技术搬家方式，

即便采取语句二优化总结•如果要看一本书中的某一页，

则需要通过目录进行查找

。如果要看一本书的整章内容，就不需要目录，直接翻到该章就可以•

目录就是索引，

整章就是分区IvorySQL

2025生态大会暨PostgreSQL高峰论坛总结季度结息现状•运行了8个小时！

熬夜之苦•

主要问题：•最大的账户类表没有分区•结息应用只跑在RAC一个节点上•

采取逐条记录处理模式优化空间•通过分区方案的实施，

有效提升结息应用速度•可以结合分区方案的实施，

将部分结息应用分摊到节点二去，

充分利用RAC集群环境的硬件和软件资源，

进一步提升结息应用速度•

采取Oracle典型的大批量数据处理技术，

例如并行处理技术，

再进一步大幅度提升整体吞吐量和运行速度某银行季度结息案例IvorySQL

2025生态大会暨PostgreSQL高峰论坛最消耗时间的语句selectB.KEY_1,B.TOD_CDEP_TOT_AMT,...

...B.LAST_MAINT_STATfromINVEBwhereB.KEY_1between

:b1and

:b2

orderbyB.KEY_1案例时间提升比案例1：

普通表按索引查询00:24:45N/A案例2：

分区表按索引查询00:10:272.36案例3：

分区表按分区扫描查询00:07:133.42案例4：

分区表按分区扫描+parallel查询00:06:263.84某银行季度结息案例：

分区优化IDX__

KEY__

1INVE优化方案号段1号段n号段2…•将INVE表按照现在的账号分段逻辑进行范围分区IvorySQL

2025生态大会暨PostgreSQL高峰论坛现有执行计划优化结果号段1号段2……号段n案例3执行计划示意图号段1

号段2

…

号段n案例4执行计划示意图案例1执行计划示意图INVEPK号段1

号段2

……

号段nINVE案例2执行计划示意图号段

1

号段2

……

号段

n号段1

号段2

…

号段n号段

1

号段2

…

号段

n某银行季度结息案例：

分区优化IvorySQL

2025生态大会暨PostgreSQL高峰论坛...

...

...

...INVE__

P__

PKINVE__

P__

PKINVE__

P__

PKINVE__

P__

PK绑定变量情况：=============================================================================|Name

|

Position

|Type|

Value|========================================================================================================================|

:

YS_

B_0

|

(

8)(

3|

276

||

|

::

|

|

((

))

|

02505

|

|

|

::

YS_|B_2

|10

|

|

A(

2)(3|

)02|

5

|

||

:SYS_B_4

|

11

|VARCHAR2(32)

|

%

|============================================================================50%2232RRCHAR2VARCHVA86S213232