阿里数据库团队PostgreSQL实践

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,2016/5/21,#,阿里数据库团队,PostgreSQL,实践,杨洋洋,(,阳帅,),yang_,阿里巴巴数据库团队,提纲,IDB,产品,介,介绍,PostgreSQL,实践原因,实践步骤,实践总结,IDB,定位,HA,备份,恢,恢复,性能,压,压测,自动,化,化调,度,度,数据,质,质量,安全,审,审计,监控,告,告警,安装,配,配置,元数,据,据,用户,服,服务,层,层,-,数据,库,库服,务,务平,台,台,(iDB),资源,申,申请,数据访问,开发设计,生产,变,变更,分库,分,分表,SQL,审核,流程,管,管控,权限,管,管控,运维,组,组件,层,层,淘宝,天猫,支付,宝,宝,余额,宝,宝,口碑,芝麻,信,信用,钉钉,1688,速卖,通,通,高德,地,地图,UC,阿里云,菜鸟,物,物流,AliSQL,MongoDB,PostgreSQL,OceanBase,Oracle,基础,技,技术,层,层,SQLServer,IDB,是数,据,据库,团,团队,自,自主,研,研发,的,的一,个,个数,据,据库,服,服务,产,产品,，,，是,集,集团,去,去,O,的重,要,要实,施,施平,台,台。,先后,实,实现,对,对,Oracle,MySQL,Oceanbase,SqlServer,PostgreSQL,等数,据,据库,的,的查,询,询，,数,数据,变,变更,，,，结,构,构变,更,更的,支,支持,。,。并且支持,分,分库,分,分表,的,的操,作,作。,2010,年淘,宝,宝启,动,动“,去,去,IOE,”项,目,目,PostgreSQL,实践,原,原因,IDB,帮助,集,集团实现,了,了去,O,，但,是,是自,己,己还,在,在使,用,用,Oracle,。,2014,年底,，,，这,台,台,Oracle,服务,器,器已,经,经集,团,团仅,剩,剩的,几,几台,服,服务,器,器之,一,一，,需,需要,承,承担,高,高昂,的,的,License,费用,。,。,去,O,势在,必,必行,，,，用,什,什么,来,来替,换,换它,呢,呢？,MySQL?,需要,对,对应,用,用和,SQL,做大,量,量改,造,造。,达梦,?,功能,与,与,Oracle,兼容,，,，稳,定,定性,达,达不,到,到要,求,求,PostgreSQL?,改造成本低，,安,安全,稳,稳定,实践,步,步骤,实践,评,评估,：,：可,行,行性,分,分析,、,、工作,量,量评,估,估。,制定方案,：,：确,定,定迁,移,移的,重,重点,和,和难,点,点，,制,制定,迁,迁移,方,方案。,应用改造,：,：,SQL,改造,，,，代,码,码改,造,造。,数据,迁,迁移,：,：结,构,构迁,移,移，,数,数据,迁,迁移,。,。,回归与测试：功能,回,回归,、,、性能,测,测试,。,。,性能调优：针对上线,后,后的性能问题进行分析,和,和优化。,SQL,改造,时间间隔,Oracle,时间相减,得,得到间隔,，,，单位为,天,天。,PG,时间相减得到,interval,值，需要,转,转换为具,体,体的时间,值,值。,Oracle,中,sysdate+interval xxxxunit(precision),类型的数,据,据，,PG,中用,now()+,时间间隔,字,字符串,的方式来实,现,现。,Now()+,1 day,Now()+,14400:interval,别名设置,：,：,避免使用,关,关键字,role,data,label,type,name,作别名，,或,或者加上,as,关键字,SQL,改造,常量参数,需,需要进行,强,强制类型,转,转换，否,则,则类型则,unknown,，框架无,法,法自动解,析,析类型。,select id,new:varchar statusfrom t;,这样才能,保,保证应用,程,程序中获,取,取到的,status,数据类型,为,为,String,。,不强制转,换,换获取到,的,的是,Object,对象，会,导,导致,JavaBean,或者,DO,对象的属,性,性值设置,失,失败。,SQL,改造,函数替换,：,：将,Oracle,部分,PG,不支持的,函,函数替换,为,为,PG,的等价函,数,数。,分组合并,：,：,Oracle,中,wm_concat(xxx),转换成,PG,中的,string_agg(xxx,)over(partitionby Xoder byY),来实现。,Oracle,中的,regexp_replace(xxx,reg),在,PG,需要加上,第,第三个参数,“,”,Bitand(A,B),：替换成运,算,算符,&,A&B,sysdate,：,now,current_timestamp,NVL,：,coalesce,DECODE,：,casewhen then elseend,SQL,改造,Oracle MergeInto,插入或者,更,更新的,SQL,改造,PostgreSQL,需要联合使用,with,查询,updatereturning,insert,来进行选,择,择性更新,或,或者插入,。,。,withdataas(select#id#:integerid,#name#:varcharname,#status#:varchar status),upsertas(update my_user dst,setname=d.name,gmt_modified=now(),status=d.status,fromdata d,where d.nameisnotnullandd.id=dst.id,returning dst.*,),insertintomy_user(id,name,status),select nextVal(seq_my_user),d.name,d.status,fromdata d,wherenotexists(select1 from my_user dst wheredst.id=d.id),SQL,改造,整数参数传入,空,空字符串,的,的处理使用,#id#:numeric,时，程序传递空字,符,符串,作为参数，则,会,会出现,invalidinput syntaxfortype numeric,错误,需要使用,to_number(#param#),（,EDB,）,Select*from twhere status=newandpid=to_number(#param#),SQL,改造,分页,Oracle,SELECT*from(,SELECT rownum ASrn,t.*FROM tWHERE conditionAND rownum=start,PG,SELECT*from twhere conditionoffset start limitpagesize,SQL,改造,部分数据,类,类型转换,Oracle,布尔值,char(1),Y/N,转换成,PG,类型,boolean,Clob,大字段类,型,型,转换成,PG,类型,varchar,虚拟列,增加实际,列,列或者使,用,用视图,Oracle u0000,字符,PG,不允许存,储,储，过滤,掉,掉再保存,修改字段,类,类型时可以使用,USING,表达式,进行字段,值,值转换,如,alter tableuseralter column is_deletedtype boolean,USINGcaseis_deletedwhenY then trueelsefalse end;,alter tableuseralter column is_deletedtype boolean,USING is_deleted=Y;,SQL,改造,递归查询,STARTWITHCONNECTBY,转换成,WITHRECURSIVE,查询,例如,select*fromemp startwithempno=7connectbymgr=priorempno;,转换成,PG,的递归,SQL,：,with recursiver_emp ras,(selecta.id,a.name,a.pidfrom emp a where id=1001,union all,selectb.id,b.name,b.pid from empb innerjoin ron r.id=b.pid,),selectid,name fromr,应用程序改造,字段名的大小转,换,换与映射问题。,Oracle,字段名默认大写,，,，,PG,字段名默认小写。,全部大写,大小写混合,区分大小写,区分大小写,SQL,字段名,USER_NAME,User_Age,“,User_Id,”,“,use_desc,”,Oracle,得到的元数据,USER_NAME,USER_AGE,User_Id,use_desc,PG,得到的元数据,user_name,user_age,User_Id,use_desc,应用程序改造,Oracle,与,PG,元数据大小写差,异,异解决办法,批量修改,SQL,文件，为字段加,入,入大写别名（操,作,作性差，工作量,大,大）,Selectuser_id“USER_ID”from t where;,批量修改代码，将程序中获取数据,值,值时将字段名小,写,写。（操作性差，工,作,作量大）,row.getInt(“user_id”),;,应用程序改造,OraclePG,默认元数据大小,写,写差异解决办法,持久层框架改造,，,，修改数据库字,段,段到,JavaBean,的映射方法,(Mapping),SQL,：,selectuser_name fromt where user_id=?,Oracle,元数据：,USER_NAME,程序获取数据：,row.get(“USER_NAME”),PG,元数据：,user_name,，原来的程序无,法,法获取到数据：,SQL:user_name-PG:user_name-,框架转换：,”USER_NAME”-,原来的程序正常,获,获取到数据。,应用程序改造,持久层框架改造,改,改造示例：,rsmd,ResultSet.getMetaData(),for(int i=0,n=rsmd.getColumnCount();i n;i+),StringcolumnName=rsmd.getColumnName(i+1);,if(delegate.isUseColumnLabel(),StringcolumnLabel=rsmd.getColumnLabel(i+1);,if(isPostgreSQL),if(columnLabel.equals(columnLabel.toLowerCase()/,小写的字段名直,接,接改大写。,columnName=columnLabel.toUpperCase();,else,columnName=columnLabel;/”user_ID”,大小写敏感字段,保,保留,else,columnName=columnLabel;,应用程序改造,游标,/,流式数据处理,需,需要开启事物,PG-JDBC,驱动默认加载所,有,有行，如果结果,集,集大会造成应用,(JAVA),内存溢出,(OutOfMemory),使用游标或者设,置,置,fetchSize,需要开启事物,应用程序改造,配合表数据类型,的,的改造进行修改,。,。,javaBean.setDeleted(“Y”.equals(row.getString(IS_DELETE),javaBean.setDeleted(row.getBoolean(,IS_DELETE),通知下游数据使,用,用方进行程序改,造,造。,性能优化,模糊查询：,如果没有修改,PG,库的,locale,，使用,like abc%,查询时，默认会,扫,扫描所有行，即,使,使有索引也不走,索,索引，引发性能