《2019年金融保险系统逃离Oracle的正确姿势.pdf》由会员分享,可在线阅读,更多相关《2019年金融保险系统逃离Oracle的正确姿势.pdf(74页珍藏版)》请在三个皮匠报告上搜索。
1、金融保险系统逃离Oracle的正确姿势让应用先跑起来技术选型让应用跑的更好、更快让应用跑的更稳定、更安全技术选型架构对比-高可用模式ADGADGOracle ADGStreamStreamPostgreSQL Stream架构对比-分片模式Oracle ShardingPostgreSQL(XC/XL)Shard DirectorADGGTMCoordinatorStreamShard Catalog架构对比-集群模式Oracle RAC模式PostgreSQLInstance 1Instance 2hbADG架构对比-读写分离模式OraclePostgreSQL 读写分离模式pgpoolSt
2、reamStream读、写读 PostgreSQL(PG)与Oracle确实很像,包括架构、数据安全等 PG提供了丰富的可选择架构,满足各种不同的场景 PG有效遵从SQL标准,这让应用迁移难度降低 有丰富的可选择组件,极易扩展 PG的代码组织的很规范让应用先跑起来能不能让应用正确的跑起来,这是事关生存的问题填“坑”指南字符集数据类型SQL语法操作符SQL行为DBLINK空串()与NULL同义词包及存储过程常用函数1、字符集问题 PG服务端不支持GBK 建议使用UTF8 不推荐使用EUC_CN 例如:“瑄”在EUC_CN下就无法编码 2、多行注释问题 上述注释在Oracle中是合法的 在PG中是
3、非法的/*some comments/*other comments/*/-合法的PostgreSQL注释格式:-This is a standard SQL comment/*multiline comment*with nesting:/*nested comment*/*/可以使用PLY(Python-Lex-Yacc)将注释自动改写掉3、NUMERIC类型问题 上述声明在Oracle中是合法的 但在PG中是非法的 PG不支持不支持负值的scale 也不支持不支持scale大于precisiona NUMBER(6,-2)a NUMBER(2,3)precision scale 负值sc
4、ale的解决方法:使用触发器,在触发器中调用round函数 SELECT round(123.6,-2);round-100(1 row)scale大于precision的解决方法:NUMBER(2,3)=NUMERIC(3,3)增加CHECK(col SELECT cast(123456789.9 as VARCHAR2(8)from dual;SELECT cast(123456789.9 as VARCHAR2(8)from dual*ERROR at line 1:ORA-25137:Data value out of rangeOracle#SELECT 123456789.9:va
5、rchar(8);varchar -12345678(1 row)PG中会被截断而不报错5、CHAR类型问题 DESC tcName Null?Type-A CHAR(4)INSERT INTO tc VALUES(1);SELECT length(a)FROM tc;LENGTH(A)-4 SELECT count(*)FROM tc WHERE a=1 ;COUNT(*)-1 SELECT length(1 )FROM dual;LENGTH(1)-4#dS tcTable public.tcColumn|Type|Collation|Nullable|Default-+-+-+-+-a|
6、character(4)|#INSERT INTO tc VALUES(1);#SELECT length(a)FROM tc;length-1#SELECT count(*)FROM tc WHERE a=1 ;count-1#SELECT length(1 );length-46、SEQUENCE最大值问题 PG的SEQUENCE最大值:9223372036854775807(bigint)而Oracle中的SEQUENCE最大值可达28位十进制值 一般情况下PG的SEQUENCE是足够的 但可能也存在一些特殊情况:LISCODE.SEQ_YBTBATTRANS_ID 1000000000
7、0000072561 该值明显已超出最大值 使用NUMERIC类型,配合触发器使用7、类型转换#CREATE TABLE t1(id VARCHAR(32);#SELECT*FROM t1 WHERE id=27;ERROR:operator does not exist:character varying=integerLINE 1:SELECT*FROM t1 WHERE id=27;HINT:No operator matches the given name and argument type(s).You might need to add explicit type casts.C
8、REATE CAST(varchar AS integer)WITH INOUTAS IMPLICIT;#SELECT*FROM t1 WHERE id=27;id-(0 rows)#EXPLAIN verbose SELECT*FROM t1 WHERE id=27;QUERY PLAN -Seq Scan on public.t1 (cost=0.00.22.95 rows=4 width=82)Output:idFilter:(t1.id):integer=27)(3 rows)善用CAST 根据自己的需求,绘制类型转换矩阵7、操作符重载#SELECT 3/5;?column?-0(1
9、row)操作符右边的值,例子中的”5”操作符左边的值,例子中的”3”操作符结果,例子中的”0”CREATE OR REPLACE FUNCTION pg_catalog.int_div(int,int)RETURNS numericAS$BEGINRETURN pg_catalog.numeric_div($1:numeric,$2:numeric);END;$LANGUAGE plpgsql;CREATE OPERATOR public./(PROCEDURE=pg_catalog.int_div,LEFTARG=int,RIGHTARG=int);#SELECT 3/5;?column?-
10、0.60000000000000000000(1 row)操作符重载是PG提供的一个非常好的特性 善用操作符重载可以解决一些兼容性问题类型转换和操作符重载是很强大的功能,但是需要仔细设计:1、PG本身内置了很多类型转换和操作符,需要考虑是否有冲突;2、类型转换和操作符是需要相互配合的,设计时需要两个结合起来一起考虑8、子查询 SELECT*FROM(SELECT*FROM t1);#SELECT*FROM(SELECT*FROM t1);ERROR:subquery in FROM must have an aliasLINE 1:SELECT*FROM(SELECT*FROM t1);HIN
11、T:For example,FROM(SELECT.)AS foo.FROM后的子查询需要alias 标量查询中的子查询就不需要alias#SELECT(SELECT a.id FROM t1 a WHERE a.id=b.id)FROM(SELECT*FROM t1)b;id-(0 rows)Oracle8、SELECT表达式别名问题 SELECT id name FROM t1;NAME-1234567890#SELECT id name FROM t1;ERROR:syntax error at or near nameLINE 1:SELECT id name FROM t1;Orac
12、le#SELECT id name1 FROM t1;name1 -1234567890(1 row)#SELECT id AS name FROM t1;name -1234567890(1 row)BUT.#SELECT id uncommited FROM t1;uncommited-1234567890(1 row)?9、SELECT INTODECLAREvId VARCHAR2(32);BEGINSELECT id INTO vId FROM t1 WHERE id=001;END;/DECLARE*ERROR at line 1:ORA-01403:no data foundOR
13、A-06512:at line 4Oracle中会报错postgres=#DO$postgres$#DECLAREpostgres$#vId VARCHAR(32);postgres$#BEGINpostgres$#SELECT id INTO vId FROM t1 WHERE id=001;postgres$#END;postgres$#$LANGUAGE plpgsql;DOPG中不会报错!这个需要特别注意 需要检查FOUND这个系统变量来判断是否有数据postgres=#DO$postgres$#DECLAREpostgres$#vId VARCHAR(32);postgres$#BE
14、GINpostgres$#SELECT id INTO vId FROM t1 WHERE id=001;postgres$#IF NOT FOUND THENpostgres$#RAISE NOTICE%,No data found.;postgres$#END IF;postgres$#END;postgres$#$LANGUAGE plpgsql;NOTICE:No data found.DO10、UPDATE语句问题 上述UPDATE语句在PG中执行会报错 ERROR:column“a”of relation“address_book”does not exist UPDATE add
15、ress_book aSET a.phone_number=*UPDATE address_book aSET a.phone_number=*INSERT也存在同样的问题“a”被认为是表的列名 11、Oracle访问PG 使用Oracle Database Gateways 不过要注意2个问题INSERT INTO t1 VALUES(1234567890);INSERT INTO t1PG VALUES(1234567890);COMMIT;Oracle的本地表 远程PG表,通过DBLINK访问 ORA-02407:cannot join the distributed transacti
16、on in progressi、不支持分布式事务SELECT*FROM ilis_sms_data_interfaceILIS_SMS_DATA_INTERFACE_LINK WHERE serial_no=12410176;SQL text from hgopars,id=1,len=49.00:53454C45 43542022 53455249 414C5F4E SELECT SERIAL_N10:4F222046 524F4D20 22494C49 535F534D O FROM ILIS_SM20:535F4441 54415F49 4E544552 46414345 S_DATA_
17、INTERFACE30:22 SQL text from hgopars,id=1,len=88.00:53454C45 43542041 312E2253 45524941 SELECT A1.SERIA10:4C5F4E4F 22204652 4F4D2022 494C4953 L_NO FROM ILIS20:5F534D53 5F444154 415F494E 54455246 _SMS_DATA_INTERF30:41434522 20413120 57484552 45204131 ACE A1 WHERE A140:2E225345 5249414C 5F4E4F22 3D4E2
18、731.SERIAL_NO=N150:32343130 31373627 2410176ii、可能会碰到性能问题数据库A数据库B执行计划一样。12、PG访问Oracle 可以使用oracle_fdw 可能会碰到以下问题ORA-08177:cant serialize access for this transaction导致这个错误发生的原因是oracle_fdw中使用的事务级别为:serializablei、应用中增加重试机制ii、在实际使用中,我们认为oracle_fdw的这个事务级别过于严苛了.if(checkerr(OCITransStart(svchp,errhp,(uword)0,
19、OCI_TRANS_SERIALIZABLE),(dvoid*)errhp,OCI_HTYPE_ERROR)!=OCI_SUCCESS).if(checkerr(OCITransStart(svchp,errhp,(uword)0,OCI_TRANS_NEW),(dvoid*)errhp,OCI_HTYPE_ERROR)!=OCI_SUCCESS).修改后,在实际使用中运行良好。13、空串()、NULL问题 在Oracle中空串()和NULL是等价的 PG里空串是空串,NULL是NULL,是不一样的东西i、所有涉及字符串判断的地方都需要进行修改ii、所有涉及字符串连接的地方都需要修改(与NUL
20、L连接后为NULL)iii、所有涉及到字符串转为数值型的都需要修改(空串强制转换会报错).这会带来非常多的麻烦:我们对对PG进行了定制化进行了定制化,使空串()与NULL等价从应用中执行14、SYNONYM问题 PG中是没有SYNONYM这个概念的1、可以通过调整search_path来解决2、配合使用VIEW15、列名大小写问题 Oracle中的列名是大写表示的 PG中的列名是小写表示的在使用类似MyBatis这样的工具时,需要将大写转为小写16、如何支持PACKAGE?PG中没有PACKAGE这个概念 结合schema来模拟PACKAGE举例CREATE PACKAGE emp_bonus
21、 ASPROCEDURE calc_bonus(date_hired employees.hire_date%TYPE);END emp_bonus;/CREATE PACKAGE BODY emp_bonus ASPROCEDURE calc_bonus(date_hired DATE)ISBEGINDBMS_OUTPUT.PUT_LINE(Employees hired on|date_hired|get bonus.);END;END emp_bonus;/CREATE SCHEMA emp_bonus;-PACKAGE名称名称CREATE OR REPLACE FUNCTION emp
22、_bonus.calc_bonus(timestamp)RETURNS VOIDAS$BEGINRAISE NOTICE Employees hired on%get bonus.,$1;END;$LANGUAGE plpgsql;在其它存储过程中或应用代码中,可以保持emp_bonus.calc_bonus()调用方式,从而模拟了PACKAGE对于PACKAGE中的全局变量,可以配合临时表来实现17、其它 decode函数 wm_conca函数 dual utl_file dbms_pipe dbms_output package推荐使用orafce组件以上是在实际迁移系统过程中可能会遇到的
23、各种“坑”点,趟过所有这些“坑”,应用才可以完成生存的第一步:活着!让应用跑的更好、更快PG代价模型简单介绍CREATE TABLE plan_test(id NUMERIC,a VARCHAR(2);DO$DECLAREa VARCHAR(2);BEGINFOR i IN 1.226 LOOPa:=chr(65+(i%8)|chr(65+(i%8);INSERT INTO plan_test VALUES(i,a);END LOOP;END;$LANGUAGE plpgsql;创建测试表plan_test插入226条测试数据postgres=#EXPLAIN SELECT*FROM plan
24、_test WHERE id=45;QUERY PLAN -Seq Scan on plan_test(cost=0.00.3.83 rows=1 width=8)Filter:(id=45:numeric)(2 rows)postgres=#SELECT relpages,reltuples FROM pg_class WHERE relname=plan_test;relpages|reltuples-+-1|226(1 row)怎么计算出来的?postgres=#SHOW seq_page_cost;seq_page_cost-1(1 row)postgres=#SHOW cpu_tup
25、le_cost;cpu_tuple_cost-0.01(1 row)postgres=#SHOW cpu_operator_cost;cpu_operator_cost-0.0025(1 row)postgres=#SELECT round(1*1)+(226*0.01)+(226*0.0025),2)AS cost;cost-3.83(1 row)CREATE UNIQUE INDEX pk_plan_test ON plan_test(id);SELECT*FROM plan_test WHERE id=45;postgres=#EXPLAIN SELECT*FROM plan_test
26、WHERE id=45;QUERY PLAN -Seq Scan on plan_test (cost=0.00.3.83 rows=1 width=8)Filter:(id=45:numeric)(2 rows)Plan如何?这是为何?postgres=#SET enable_seqscan=off;SETpostgres=#EXPLAIN SELECT*FROM plan_test WHERE id=45;QUERY PLAN -Index Scan using pk_plan_test on plan_test (cost=0.14.8.16 rows=1 width=8)Index C
27、ond:(id=45:numeric)(2 rows)先强制走索引 估算出的总成本比全表扫描高 走索引时的成本计算postgres=#EXPLAIN SELECT*FROM plan_test WHERE id=45;QUERY PLAN -Index Scan using pk_plan_test on plan_test(cost=0.14.8.16 rows=1 width=8)Index Cond:(id=45:numeric)(2 rows)startup-cost total-cost那这两个值是怎么计算出来的?postgres=#SELECT relpages,reltuples
28、 FROM pg_class WHERE relname=pk_plan_test;relpages|reltuples-+-2|226postgres=#SELECT tablename,attname,n_distinct,correlation FROM pg_stats WHERE tablename=plan_test AND attname=id;tablename|attname|n_distinct|correlation-+-+-+-plan_test|id|-1|1postgres=#SHOW random_page_cost;random_page_cost-4统计数据及
29、系统参数postgres=#SHOW cpu_index_tuple_cost;cpu_index_tuple_cost-0.005postgres=#SHOW cpu_operator_cost;cpu_operator_cost-0.0025postgres=#SHOW cpu_tuple_cost;cpu_tuple_cost-0.01统计数据及系统参数(续)indexPages=2;indexTuples=226;indexSelective=1/226=0.004424778761;/选择因子indexCorrelation=1;randomPageCost=4;cpuIndexTu
30、pleCost=0.005;cpuOperatorCost=0.0025;cpuTupleCost=0.01;indexQuals=1;/INDEX上的查询条件数量indexTreeHeight=0;/树的高度,会随数据的变化而变化numIndexTuples=indexTuples*indexSelective=226*0.004424778761=1;numIndexPages=ceil(numIndexTuples*(indexPages/indexTuples)=ceil(1*(2/226)=1;totalCost=numIndexPages*randomPageCost=1*4=4;
31、qualOpCost=cpuOperatorCost*indexQuals=0.0025*1=0.0025;toalCost=totalCost+numIndexTuples*(cpuIndexTupleCost+qualOpCost)=4+1*(0.005+0.0025)=4.0075;descentCost=ceil(log(indexTuples)/log(2.0)*cpuOperatorCost=ceil(log(226)/log(2.0)*0.0025=0.02;startupCost=descentCost=0.02;totalCost=totalCost+descentCost=
32、4.0075+0.02=4.0275;descentCost=(indexTreeHeight+1)*50.0*cpuOperatorCost=(0+1)*50.0*0.0025=0.125;startupCost=startupCost+descentCost=0.02+0.125=0.145;totalCost=totalCost+descentCost=4.0275+0.125=4.1525;runCost=totalCost startupCost=4.1525 0.145=4.0075;tupleFetched=indexTuples*indexSelective=226*0.004
33、424778761=1;maxIoCost=numIndexPages*randomPageCost=1*4=4;minIoCost=randomPageCost=4;csquared=indexCorrelation*indexCorrelation=1*1=1;runCost=runCost+maxIoCost+csquared*(minIoCost maxIoCost)=4.0075+4+1*(4 4)=8.0075;runCost=runCost+cpuTupleCost*tupleFetched=8.0075+0.01=8.0175;totalCost=stratupCost+run
34、Cost=0.145+8.0175=8.1625;startupCost=0.145;/0.145双精度值为双精度值为0.1449,四舍五入后即为,四舍五入后即为0.14totalCost=8.1625;/四舍五入后即为四舍五入后即为8.16npage*1+nrow*(0.01+0.0025)8.0175+ceil(log(nrow)/log(2.0)*0.0025+(indexTreeHeight+1)*50*0.0025npage+(226*npage)*0.0125 8.0175+ceil(log(226*npage)/log(2.0)+0.253.825*npage 8.2675+ce
35、il(5.4205+log(npage)/0.6931)npage=3不修改系统参数可以让计划走索引吗?可以!往表里插数据!数据大于3个数据块就可以走索引了CREATE INDEX idx_plan_test_a ON plan_test(a);我们在列 a 上也建一个索引,并且将数据填充满8个数据块DO$DECLAREa VARCHAR(2);BEGINFOR i IN 227.1808 LOOPa:=chr(65+(i%8)|chr(65+(i%8);INSERT INTO plan_test VALUES(i,a);END LOOP;END;$LANGUAGE plpgsql;postg
36、res=#SELECT relpages,reltuples FROM pg_class WHERE relname=plan_test;relpages|reltuples-+-8|1808postgres=#EXPLAIN SELECT*FROM plan_test WHERE a=CC;QUERY PLAN -Bitmap Heap Scan on plan_test (cost=10.03.20.85 rows=226 width=8)Recheck Cond:(a):text=CC:text)-Bitmap Index Scan on idx_plan_test_a (cost=0.
37、00.9.97 rows=226 width=0)Index Cond:(a):text=CC:text)(4 rows)DO$DECLAREaa VARCHAR(2);BEGINFOR c IN 1.50 LOOPFOR i IN 1.1808 LOOPaa:=chr(65+(i%8)|chr(65+(i%8);UPDATE plan_test SET a=aa WHERE id=i;END LOOP;END LOOP;END;$LANGUAGE plpgsql;然后我们对表plan_test全表更新50次postgres=#EXPLAIN SELECT*FROM plan_test WHE
38、RE a=CC;QUERY PLAN -Seq Scan on plan_test (cost=0.00.430.60 rows=226 width=8)Filter:(a):text=CC:text)(2 rows)再次查看之前SQL的执行计划:可以看到走全表扫描了,执行计划并不稳定。postgres=#SET random_page_cost=1;SETpostgres=#EXPLAIN SELECT*FROM plan_test WHERE a=CC;QUERY PLAN -Index Scan using idx_plan_test_a on plan_test (cost=0.28.
39、220.22 rows=226 width=8)Index Cond:(a):text=CC:text)(2 rows)我们调整下参数:postgres=#VACUUM FULL plan_test;VACUUMpostgres=#EXPLAIN SELECT*FROM plan_test WHERE a=CC;QUERY PLAN -Index Scan using idx_plan_test_a on plan_test(cost=0.28.13.12 rows=226 width=8)Index Cond:(a):text=CC:text)(2 rows)然后在表上做一次VACUUM F
40、ULL:通过前面走索引时的成本计算过程及SQL实验,说明:random_page_cost这个参数对系统判断是否走索引有非常大的影响。而调整random_page_cost为更小的值,执行计划更能保持稳定。不过至于调整为多大值,这个需要在实际的系统中测试验证后再决定。理解执行计划的成本是如何估算的很重要SELECT phone FROM lcaddressWHERE customerno IN(SELECT insurednoFROM lcinsured WHERE contno=1AND sequenceno=1AND addressno=lcaddress.addre
41、ssno);另一种优化方式介绍在Oracle中只要几十毫秒,而在PG中要几十秒!PLAN_TABLE_OUTPUT-Plan hash value:3722480500-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECT STATEMENT|1|48|6 (0)|00:00:01|1|NESTED LOOPS|1|48|6 (0)|00:00:01|2|NESTED LOOPS|1|48|6 (0)|00:00:01|*3|TABLE ACCESS BY INDEX ROWID|LCINSURED|1|30|4 (0)|00:00:
42、01|*4|INDEX RANGE SCAN|PK_LCINSURED|1|3 (0)|00:00:01|*5|INDEX UNIQUE SCAN|PK_LCADDRESS|1|1 (0)|00:00:01|6|TABLE ACCESS BY INDEX ROWID|LCADDRESS|1|18|2 (0)|00:00:01|-Oracle的PlanQUERY PLAN -Seq Scan on lcaddress(cost=0.00.36151100.39 rows=3900336 width=12)Filter:(SubPlan 1)SubPlan 1-Index Scan using p
43、k_lcinsured on lcinsured(cost=0.56.8.59 rows=1 width=11)Index Cond:(contno):text=1:text)Filter:(sequenceno):numeric=1:numeric)AND(addressno):numeric=lcaddress.addressno)(6 rows)PG的PlanSELECT phone FROM lcaddressWHERE(customerno,addressno)IN(SELECT insuredno,addressnoFROM lcinsured WHERE c
44、ontno=1AND sequenceno=1);对SQL进行简单重写:PLAN_TABLE_OUTPUT-Plan hash value:3722480500-|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-|0|SELECT STATEMENT|1|48|6 (0)|00:00:01|1|NESTED LOOPS|1|48|6 (0)|00:00:01|2|NESTED LOOPS|1|48|6 (0)|00:00:01|*3|TABLE ACCESS BY INDEX ROWID|LCINSURED|1|30|4 (0)
45、|00:00:01|*4|INDEX RANGE SCAN|PK_LCINSURED|1|3 (0)|00:00:01|*5|INDEX UNIQUE SCAN|PK_LCADDRESS|1|1 (0)|00:00:01|6|TABLE ACCESS BY INDEX ROWID|LCADDRESS|1|18|2 (0)|00:00:01|-Predicate Information(identified by operation id):-3-filter(TO_NUMBER(SEQUENCENO)=1)4-access(CONTNO=1)5-access(CUSTOM
46、ERNO=INSUREDNO AND ADDRESSNO=TO_NUMBER(ADDRESSNO)重写后的SQL在Oracle的Plan不变QUERY PLAN -Nested Loop (cost=0.99.1.44 rows=1 width=12)-Index Scan using pk_lcinsured on lcinsured (cost=0.56.0.78 rows=1 width=13)Index Cond:(contno):text=1:text)Filter:(sequenceno):numeric=1:numeric)-Index Scan using
47、 pk_lcaddress on lcaddress (cost=0.44.0.66 rows=1 width=28)Index Cond:(customerno):text=(lcinsured.insuredno):text)AND(addressno=(lcinsured.addressno):numeric)(6 rows)重写后的SQL在PG中的执行计划与在Oracle中的是一致的了以Oracle的执行计划为蓝本来优化SQL让应用跑的更稳定、更安全PG的高可用架构pgpool-1pgpool-2pgpool-3pgpool集群主库备库1备库2PG集群通过pgpool进行数据库的负载均衡(读)主库与备库间通过stream的方式实现实时同步(配置策略为备库1和备库2只要有一个写完成主库就返回,防止因为某些原因备库12均不能用时将主库hang住,提高可用性)备份重如山!