Greenplum 数据库开发基础

Greenplum数据库开发基础12014年10月目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例MPP架构MPP(MassivelyParallelProcessing)

Shared-NothingArchitectureMPP无共享架构的优势InterconnectLoading数据分布在所有的并行节点上每个节点只处理其中一部分数据最优化的I/O处理所有的节点同时进行并行处理节点之间完全无共享，无I/O冲突自动化的并行处理机制内部处理自动化并行，无需人工分区或优化加载与访问方式与一般数据库相同最易于扩展的架构BI和数据分析的最佳选择增加节点实现线性扩展增加节点可线性增加存储、查询和加载性能Greenplum基本架构MPP(MassivelyParallelProcessing)

Shared-NothingArchitectureNetworkInterconnect............Master

SeversQueryplanning&dispatchSegment

SeversQueryprocessing&datastorageSQLMapReduceExternal

SourcesLoading,streaming,etc.Greenplum的并行处理特性并行处理由系统自动完成，无需人工干预所有数据均匀分布到所有节点，每个节点都计算自己的部分数据，所以并行处理无需人工干预，系统自动完成。

无需复杂的调优需求，只需要加载数据和查询DBA工作量极少，无需复杂的调优工作和维护工作。

扩展性可线性扩展到10，000个节点每增加一个节点，查询、加载性能都成线性增长

客户端访问及第三方工具支持完全支持数据库技术接口标准，例如:SQL,ODBC,JDBC,OLEDB等。同时，广泛地支持各个BI和ETL软件工具。Greenplum基本体系架构客户端接口和程序

psqlpgAdminIIIODBC/DatadirectJDBCPerlDBIPythonlibpqOLEDBMasterHost访问系统的入口建立与客户端的连接和管理SQL的解析并形成执行计划执行计划向Segment的分发收集Segment的执行结果协调工作处理过程Master不存储业务数据，只存储系统目录表和元数据（数据字典）Segment每段（Segment）存放一部分用户数据

一个系统可以有多段

用户不能直接存取访问

所有对段的访问都经过Master用户查询SQL的执行Interconnect

Greenplum数据库之间的连接层

进程间协调和管理

基于千兆以太网架构

属于系统内部私网配置

支持两种协议：TCPorUDPGreenplum高可用性体系架构Master/Standby镜像保护Standby实时与Master节点的Catalog和事务日志保持同步Standby节点用于当PrimaryMaster节点损坏时提供Master服务数据冗余-Segment镜像保护每个Segment的数据冗余存放在另一个Segment上，数据实时同步当PrimarySegment失败时，MirrorSegment将自动提供服务PrimarySegment恢复正常后，使用gprecoverseg–F

同步数据。表分布的策略-并行计算的基础

Hash分布CREATETABLE…DISTRIBUTEDBY(column[,…])同样数值的内容被分配到同一个Segment上

循环分布

CREATETABLE…DISTRIBUTEDRANDOMLY具有同样数值的行内容并不一定在同一个Segment上分布键(DistributionKeys)用于将数据平均分布到Segments之中的一个或者多个字段用表的主键作分布键可以使数据分布均匀建表时使用DISTRIBUTEDBY

子句定义表的分布键

CREATETABLEsales

(dtdate,prcfloat,qtyint,cust_idint,

prod_idint,vend_idint)DISTRIBUTEDBY(dt,cust_id,prod_id);如果表没有主键，或者没有合适的字段作为分布键，可以使用随机分布键

（DISTRIBUTEDRANDOMLY）如果没有明确定义分布键，系统会把第一个字段作为表的分布键分布存储数据均匀分布-并行处理的关键43Oct2020051264Oct2020051145Oct2020054246Oct2020056477Oct2020053248Oct20200512OrderOrder#Order

DateCustomer

ID50Oct2020053456Oct2020052163Oct2020051544Oct2020051053Oct2020058255Oct20200555策略:

数据尽可能的均匀分布到每个节点查询命令的执行SQL查询处理机制并行查询计划SELECTcustomer,amountFROMsalesJOINcustomerUSING(cust_id)WHEREdate=04302008;压缩存储和行列存储压缩存储支持ZLIB和QUICKLZ方式的压缩,压缩比可到10:1压缩表只能是AppendOnly方式压缩数据不一定会带来性能的下降，压缩表将消耗CPU资源，而减少I/O资源占用语法CREATETABLEfoo(aint,btext)

WITH(appendonly=true,compresstype=zlib,compresslevel=5);行或列存储模式列模式目前只支持AppendOnly如果常用的查询只取表中少量字段，则列模式效率更高，如查询需要取表中的大量字段，行模式效率更高语法：

CREATETABLEsales2(LIKEsales)WITH(appendonly=true,orientation=column);锁停止活动的SQL查询查看要停止的SQL查询的进程ID

执行select*frompg_stat_activity查看到当前数据库连接的IP地址，用户名，提交的查询等。（另外也可以在master主机上查看进程，对每个客户端连接，master都会创建一个进程。ps-ef|grep-ipostgres|grep-icon）##查询表是否被锁selectprocpid,t.*

frompg_stat_activityt

whereusename=‘lds_betl’

anddatname=‘ldsdb’

andwaiting=‘t’;停止SQL：

执行Select

pg_cancel_backend(procpid)

或者Selectpg_terminate_backend(procpid)或者在MASTER

OS：$killprocpid注：极端情况下，kill不能停止SQL时，采用kill

-11停止进程千万不要使用kill

-9，该操作导致数据库崩溃；

生产系统请不要采用kill操作。表分区的概念将一张大表逻辑性地分成多个部分，如按照分区条件进行查询，将减少数据的扫描范围，提高系统性能。提高对于特定类型数据的查询速度和性能也可以更方便数据库的维护和更新两种类型:Range分区(日期范围或数字范围)/如日期、价格等List分区,例如地区、产品等Greenplum中的表分区在使用中具有总表的继承性，并通过Check参数指定相应的子表分区的子表依然根据分布策略分布在各segment上分区是一种非常有用的优化措施，例如一年的交易按交易日期分区后，查询一天的交易性能将提高365倍！！！DataDistribution&PartitioningSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DJan2005Feb2005Mar2005Apr2005May2005Jun2005Jul2005Aug2005Sep2005Oct2005Nov2005Dec2005每个分区表的数据平均分布到各个节点表分区可减少数据的搜索范围，提高查询性能FullTableScanVS.PartitionPruningSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3DSELECTCOUNT(*)FROMordersWHEREorder_date>=‘Oct202005’ANDorder_date<‘Oct272005’VSHashDistributionHashDistribution+TablePartitioningSegment1ASegment1BSegment1CSegment1DSegment2ASegment2BSegment2CSegment2DSegment3ASegment3BSegment3CSegment3D表分区示意图Range分区CREATETABLEorders(order_id BIGINT,order_date TIMESTAMP,order_mode VARCHAR(8),customer_id NUMERIC(6),order_status NUMERIC(2),order_total NUMERIC(8,2),sales_rep_id NUMERIC(6),promotion_id NUMERIC(6))DISTRIBUTEDBY(customer_id)PARTITIONBYRANGE(order_date)(START('2005-12-01')END('2007-12-01')EVERY(interval'1year'),START('2007-12-01')END('2008-12-01')EVERY(interval'1month'),START('2008-12-01')END('2008-12-08')EVERY(interval'1day'),START('2008-12-08')END('2008-12-09')EVERY(interval'1hour'));List分区CREATETABLErank(

idint,

rankint,

yearint,

genderchar(1),

countint)DISTRIBUTEDBY(id)PARTITIONBYLIST(gender)(PARTITIONgirlsVALUES('F'),PARTITIONboysVALUES('M'),DEFAULTPARTITIONother);Multi-level分区CREATETABLEsales(

trans_idint,

datedate,

amountdecimal(9,2),

regiontext)DISTRIBUTEDBY(trans_id)PARTITIONBYRANGE(date)SUBPARTITIONBYLIST(region)SUBPARTITIONTEMPLATE(SUBPARTITIONusaVALUES('usa'),SUBPARTITIONasiaVALUES('asia'),SUBPARTITIONeuropeVALUES('europe'),START(date'2008-01-01')INCLUSIVEEND(date'2009-01-01')EXCLUSIVEEVERY(INTERVAL'1month'),DEFAULTPARTITIONoutlying_dates);修改表分区ALTERTABLE… |ALTERPARTITION… |DROPPARTITION… |TRUNCATEPARTITION… |RENAMEPARTITION… |ADDPARTITION… |EXCHANGEPARTITION… |SPLITPARTITION… |SETSUBPARTITIONTEMPLATEEg:ALTERTABLEfooEXCHANGEPARTITIONFOR(RANK(1))WITHTABLEbar;Querythecatalog:SELECTpartitiontablename,partitionlevel,partitionrank,partitionrangestart,partitionrangeend,partitioneveryclauseFROMpg_partitions;目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例外部表加载外部表的特征Read-only数据存放在数据库外可执行SELECT,JOIN,SORT等命令，类似正规表的操作外部表的优点并行方式加载ETL的灵活性格式错误行的容错处理支持多种数据源两种方式ExternalTables:

基于文件WebTables:

基于URL或指令基于外部表的高速数据加载利用并行数据流引擎，Greenplum可以直接用SQL操作外部表数据加载完全并行Master主机Segment主机内部互联网—千兆以太网交换机gpfdistgpfdistSegment主机Segment主机Segment主机外部表文件外部表文件ETL服务器内部网络外部表加载的特征并行数据加载提供最好的性能能够处理远程存储的文件采用HTTP协议每个gpfdist可达到200MB/s数据分布率gpfdist文件分发守护进程启动:

gpfdist-d/var/load_files/expenses-p8080-l/home/gpadmin/log&外部表定义:CREATEEXTERNALTABLEext_expenses

(nametext,datedate,

amountfloat4,descriptiontext)LOCATION('gpfdist//etlhost:8081/*','gpfdist//etlhost:8082/*')FORMAT'TEXT'(DELIMITER'|')ENCODING’UTF-8’LOGERRORSINTOext_expenses_loaderrorsSEGMENTREJECTLIMIT10000ROWS;外部表加载异常处理加载正常数据并捕获格式异常的数据，比如：缺少某些属性的行属性数据类型错误无效的字符集编码不符合约束PRIMARYKEY,NOTNULL,CHECKorUNIQUEconstraints外部表错误处理可选子句：[LOGERRORSINTOerror_table]SEGMENTREJECTLIMITcount[ROWS|PERCENT](PERCENTbasedongp_reject_percent_thresholdparameter)例子CREATEEXTERNALTABLEext_customer(idint,nametext,sponsortext)LOCATION('gpfdist://filehost:8081/*.txt')FORMAT'TEXT'(DELIMITER'|'NULL'')LOGERRORSINTOerr_customerSEGMENTREJECTLIMIT5ROWS;COPYSQL

命令PostgreSQL命令支持数据加载和数据卸载加载大量数据的最佳方法串行加载所有行（非并行）从文件或者标准输入读取加载数据和外部表一样支持错误处理EXAMPLECOPYmytableFROM'/data/myfile.csv'WITHCSVHEADER;（文件生成在Master）\COPYmytableFROM‘/data/myfile.csv’WITHCSVHEADER;（文件生成在本地）COPYcountryFROM'/data/gpdb/country_data'WITHDELIMITER'|'LOGERRORSINTOerr_countrySEGMENTREJECTLIMIT10ROWS;数据加载性能优化提示

删除索引，加载完成后再重建

加载完成后执行ANALYZE

加载出错、DELETE/UPDATE等操作之后执行VACUUM

不要使用ODBCINSERT加载大量数据目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例客户端工具pgAdmin3图形化管理和SQL执行/分析/监控工具psql

行命令操作和管理工具pgAdmin3forGPDBpgAdmin3是一款重要的PostgreSQL图形化管理和开发的开源管理工具pgAdmin3forGPDBpgAdmin3forGPDB监控活动session,同SQL：select*frompg_stat_activity;监控锁，从pg_lock中获取信息可以停止正在运行的SQLPSQL通过master建立连接连接选项databasename(-d|PGDATABASE)masterhostname(-h|PGHOST)masterport(-p|PGPORT)username(-U|PGUSER)ConnectToGPDBpsql-hdb_ip-pport-Udbusr-vON_ERROR_STOP=1-ddatabaseeg：psql-h1-p5432-Ugpadmin-vON_ERROR_STOP=1-dsordb常用PSQL命令\?(helponpsqlmeta-commands)\h(helponSQLcommandsyntax)\dt(showtables)\dtS(showsystemtables)\dgor\du(showroles)\l(showdatabases)\cdb_name(connecttothisdatabase)\q(quitpsql)\!(Enterintoshellmode)\df(showfunction)\dn(showschema)Setsearch_path=…\timing目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例数据库ToCreate:CREATEDATABASEorcreatedbToDrop:DROPDATABASEordropdbToEdit:ALTERDATABASE

ChangenameAssignnewownerSetconfigurationparametersPSQLTipsPSQL显示所连接的数据库

EXAMPLE:template1=#(superuser)

names=>(non-superuser)Toshowalistofalldatabases:

\lToconnecttoanotherdatabase:

\cdb_nameUsePGDATABASEenvironmentvariabletosetthedefaultdatabaseSchemaToCreate:CREATESCHEMAToDrop:DROPSCHEMAToEdit:ALTERSCHEMAChangenameAssignnewownerPSQLTipsToseethecurrentschema:

SELECTcurrent_schema();Toseealistofallschemasinthedatabase:

\dnToseetheschemasearchpath:

SHOWsearch_path;Tosetthesearchpathforadatabase:

ALTERDATABASESETsearch_pathTOmyschema,public,pg_catalog;表ToCreate:CREATETABLEAdditionalDISTRIBUTEDBYorDISTRIBUTEDRANDOMLYclauseSomesyntaxnotsupportedToEdit:ALTERTABLECannotalterdistributionkeycolumnsToDrop:DROPTABLEPSQLTipsTolisttablesinthedatabase:

\dtToseestructureofatable:

\d+table_nameTolistsystemcatalogtables:

\dtSTolistexternaltablesonly:

\dxToseethedistributionkeycolumnsofatable:

\dtable_name表和字段约束CHECKtableorcolumnconstraintsNOTNULLcolumnconstraintsUNIQUEcolumnconstraintsOneallowedpertableUniquecolumnsmustalsobeindistributionkeyNotallowediftablealsohasaprimarykeyPRIMARYKEYtableconstraintsUsedasdistributionkeybydefaultFOREIGNKEYconstraintsdefinitionsaresupportedbutnotenforcedForeignkeyrelationshipsareutilizedbythequeryplannertoimprovequeryplans.视图

ViewSQLCommands:CREATEVIEWDROPVIEW

PSQLTips:Tolistallviewswhileinpsql:\dv

Toseeaviewdefinition:\d+view_name

EXAMPLE:

CREATEVIEWtopten

ASSELECTname,rank,gender,year

FROMnames,rank

WHERErank<’11’ANDnames.id=rank.id;

SELECT*FROMtoptenORDERBYyear,rank;索引在Greenplum数据库中应谨慎创建索引索引不一定都能优化查询应测试索引是否真正提升了性能删除没用的索引PRIMARYKEY索引会自动创建唯一性索引只能在分布键字段创建索引（续）索引类型:B-tree

Bitmap索引相关SQL命令：

CREATEINDEXALTERINDEXDROPINDEXREINDEX

PSQLTips:在PSQL显示所有索引：

\di显示索引定义:\d+index_name

大批量ETL加工最好不建索引，对性能提升作用不大B-TREE适用每次通过单一字段筛选查询少量数据B-MAP适用每次通过多个字段筛选查询大量数据其他数据库对象FunctionsandoperatorsSequencesTriggersTablespaces数据类型常用数据类型CHAR,VARCHAR,TEXTSmallint,integer,bigintNumeric,real,doubleprecisionTimestamp,date,timeBooleanArray

类型。如

integer[]其它数据类型请参考常用系统表及视图所有系统表在pg_catalogschema标准PostgreSQL系统表(pg_*)常用系统表：pg_stat_activitypg_tablespg_class

pg_attributepg_namespace在psql显示所有系统表:\dtSPsql显示所有系统视图:\dvS其它catalog参考

函数日期函数Extract（day|month|year。。。Fromdate);Selectdate+‘1day’::interval,date+‘1month’::intervalSELECTdate_part('day',TIMESTAMP'2001-02-1620:38:40');Result:16SELECTdate_trunc('hour',TIMESTAMP'2001-02-1620:38:40');Result:2001-02-1620:00:00pg_sleep(seconds);系统日期变量Current_dateCurrent_timeCurrent_timestampNow()Timeofday()在

事务中发生变化，以上函数在事务中不变函数字符串处理函数Substr/length/lower/upper/trim/replace/positionrPad/lpadTo_char,||(字符串连接)substringlike，simillar

to(模式匹配)其它杂类Case。。。When/Coalescenullifgenerate_seriesIn/notin/exists/any/allBuilt-InFunctions(SELECT)FunctionDescriptionExampleCURRENT_DATEReturnsthecurrentsystemdate2006-11-06CURRENT_TIMEReturnsthecurrentsystemtime16:50:54CURRENT_TIMESTAMPReturnsthecurrentsystemdateandtime2008-01-0616:51:44.430000+00:00LOCALTIMEReturnsthecurrentsystemtimewithtimezoneadjustment19:50:54LOCALTIMESTAMPReturnsthecurrentsystemdateandtimewithtimezoneadjustment2008-01-0619:51:44.430000+00:00CURRENT_ROLE

ROLEReturnsthecurrentdatabaseuserjdoeMathematicalFunctionsFunctionReturnsDescriptionExampleResults+-*/sameAdd,Subtract,Multiply&Divide1+12%IntegerModulo10%20^SameExponentiation2^24|/NumericSquareRoot|/93||/NumericCubeRoot||/82!NumericFactorial!36&|#~NumericBitwiseAnd,Or,XOR,Not91&1511<<>>NumericBitwiseShiftleft,right1<<48>>2162MathematicalFunctions(Continued)FunctionReturnsDescriptionExampleResultsabssameAbsoluteValueabs(-998.2)998.2ceiling(numeric)NumericReturnssmallestintegernotlessthanargumentceiling(48.2)49floor(numeric)NumericReturnslargestintegernotgreaterthanargumentfloor(48.2)48pi()NumericTheπconstantpi()3.1419…random()NumericRandomvaluebetween0.0and1.0random().87663round()NumericRoundtonearestintegerround(22.7)23StringFunctionsFunctionReturnsDescriptionExampleResultsstring||stringTextStringconcatenation‘my’||‘my’‘mymy’char_length(string)Integernumberofcharsinstringchar_length(‘mymy’)4position(stringinstring)IntegerLocationofspecifiedsub-stringposition(‘my’in‘ohmy’)3lower(string)TextConvertstolowercaselower(‘MYMY’)‘mymy’upper(string)TextConvertstouppercaseupper(‘mymy’)‘MYMY’substring(stringfromnforn)TextDisplaysportionofstringsubstring(‘myohmy’from3for2)‘oh’trim(both,leading,trailingfromstring)TextRemoveleadingand/ortrailingcharacterstrim(‘mymy‘)‘mymy’StringFunctions(Continued)FunctionReturnsDescriptionExampleResultsinitcap(string)TextChangescaseinitcap(‘mymy’)‘MyMy’length(string)IntegerReturnsstringlengthlength(‘mymy’)4split_part(string,delimiter,occurrence)TextSeparatesdelimitedlistsplit_part(‘one|two|three’,’|’,2)‘two’DateFunctionsFunctionReturnsDescriptionExampleResultsage(timestamp,timestamp)TimestampDifferenceinyears,monthsanddaysage(‘2008-08-12’timestamp,current_timestamp)0years1month11daysextract(fieldfromtimestamp)IntegerReturnsyear,month,day,hour,minuteorsecondextract(dayfromcurrent_date)11now()TimestampReturnscurrentdate&timenow()2008-09-2211:00:01overlapsBooleanSimplifiescomparingdaterangesWHERE(‘2008-01-01’,’2008-02-11’)overlaps(‘2008-02-01’,’2008-09-11’)TRUE存储过程Greenplum支持SQL/PYTHON/PERL/C语言构建函数，以下着重介绍SQL

存储过程。一个存储过程就是一个事务，包括对子过程的调用都在一个事务内存储过程结构：CREATEFUNCTIONsomefunc()RETURNSintegerAS$$DECLAREquantityinteger:=30;BEGINRETURN;END;$$LANGUAGEplpgsql;赋值给一个变量或行/记录赋值用下面方法：identifier:=expression例子：user_id:=20;执行一个没有结果的查询:PERFORMquery;一个例子：PERFORMcreate_mv('cs_session_page_requests_mv',my_query);存储过程请参考：存储过程动态SQLEXECUTEcommand-string[INTO[STRICT]target];SELECTINTOExample：SELECTIDINTOVAR_IDFROMTABLEA获取结果状态GETDIAGNOSTICSvariable=item[,...];一个例子： ·GETDIAGNOSTICSinteger_var=ROW_COUNT;SQL返回变量SQLERRM（SQL出错信息），

SQLSTATE（SQL执行返回状态编码）控制结构IF...THEN...ELSEIF...THEN...ELSELOOP,EXIT,CONTINUE,WHILE,FOR从函数返回有两个命令可以用来从函数中返回数据：RETURN和RETURNNEXT。Syntax：RETURNexpression;设置回调EXECSQLWHENEVERcondition

action;condition

可以是下列之一：SQLERROR，SQLWARNING，NOTFOUND存储过程异常处理EXCEPTIONWHENunique_violationTHEN--donothingEND;忽略错误：EXCEPTIONWHENOTHERSTHEN

RAISENOTICE'anEXCEPTIONisabouttoberaised';

RAISEEXCEPTION'NUM:%,DETAILS:%',SQLSTATE,SQLERRM;END;错误和消息RAISElevel'format'[,expression[,...]];Level:Info:信息输入Notice:信息提示Exception:产生一个例外，将退出存储过程Example:RAISENOTICE'Callingcs_create_job(%)',v_job_id;OVER(PARTITIONBY…)ExampleSELECT*,row_number()OVER()FROMsaleORDERBYcn;SELECT*,row_number()OVER(PARTITIONBYcn)FROMsaleORDERBYcn;row_number|cn|vn|pn|dt|qty|prc++++++1|1|10|200|1401-03-01|1|02|1|30|300|1401-05-02|1|03|1|50|400|1401-06-01|1|04|1|30|500|1401-06-01|12|55|1|20|100|1401-05-01|1|01|2|50|400|1401-06-01|1|02|2|40|100|1401-01-01|1100|24001|3|40|200|1401-04-01|1|0(8rows)row_number|cn|vn|pn|dt|qty|prc++++++1|1|10|200|1401-03-01|1|02|1|30|300|1401-05-02|1|03|1|50|400|1401-06-01|1|04|1|30|500|1401-06-01|12|55|1|20|100|1401-05-01|1|06|2|50|400|1401-06-01|1|07|2|40|100|1401-01-01|1100|24008|3|40|200|1401-04-01|1|0(8rows)OVER(ORDERBY…)ExampleSELECTvn,sum(prc*qty)FROMsaleGROUPBYvnORDERBY2DESC;SELECTvn,sum(prc*qty),rank()OVER(ORDERBYsum(prc*qty)DESC)FROMsaleGROUPBYvnORDERBY2DESC;vn|sum|rank++40|2640002|130|180|250|0|320|0|310|0|3(5rows)vn|sum+40|264000230|18050|020|010|0(5rows)事务事务将多个语句捆绑为‘all-or-nothing’操作事务相关命令BEGINorSTARTTRANSACTIONENDorCOMMITROLLBACKSAVEPOINTandROLLBACKTOSAVEPOINTPsql中设置自动提交模式：\setautocommiton|off目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例数据类型DataType

ORACLE

GreenplumNumericNUMBER(p,s)SMALLINT(2bytes)INTEGER(4bytes)BIGINT(8bytes)DECIMAL(p,s)(11+p/2bytes)NUMERIC(p,s)(11+p/2bytes)REAL(4bytes)DOUBLE(8bytes)CharacterCHARandNCHARCHARVARCHAR2andNVARCHAR2VARCHARDate&TimeDATE(includestimetosec)DATEorTIMESTAMPwithoutTimeZoneTIMESTAMPTIMESTAMPINTERVALINTERVAL/TIMEBinaryBFILE(>1GB)LargeObjects(upto2GB)RAWBFILE(<1GB)BYTEACLOBandNCLOBTEXTGreenplum与Oracle比较（1）DifferenceOracleGreenplumDUALSELECT1+1FROMDUALSELECT1+1;or

CREATEVIEWdualASSELECT'X'::VARCHAR(1)ASDUMMY;

SELECT1+1FROMdual;NEXTVALSELECTA_TABLE_SEQUENCE.NEXTVALFROMDUALSELECTNEXTVAL('A_TABLE_SEQUENCE')FROMDUALROWNUMSELECT*FROMAGE_TYPEWHEREROWNUM<=5SELECT*FROMAGE_TYPE

LIMIT5OFFSET0SELECT*FROMAGE_TYPEWHERECODEISNOTNULL

ANDROWNUM<=5

ORDERBYCODEDESCSELECT*FROMAGE_TYPE

WHERECODEISNOTNULL

ORDERBYCODEDESC

LIMIT5OFFSET0ASSELECTA.COL1A_COL1,

A.COL2A_COL2

FROMA_TABLEASELECTA.COL1ASA_COL1,

A.COL2ASA_COL2

FROMA_TABLEAGreenplum与Oracle比较（2）DifferenceOracleGreenplum(+)SELECT*FROMA_TABLEA,B_TABLEB

WHEREA.ID(+)=B.IDSELECT*FROMA_TABLEA

RIGHTOUTERJOINB_TABLEB

ONA.ID=B.IDSELECT*FROMA_TABLEA,B_TABLEB

WHEREA.ID(+)=B.ID

ANDA.COL1='COL1_VALUE'SELECT*FROMA_TABLEA

RIGHTOUTERJOINB_TABLEB

ONA.ID=B.IDANDA.COL1='COL1_VALUE'SELECT*FROMA_TABLEA,B_TABLEB,C_TABLEC,D_TABLED

WHEREA.ID=B.ID(+)AND

A.ID=C.ID(+)AND

A.COL1=D.COL1SELECT*FROM(A_TABLEA

LEFTOUTERJOINB_TABLEB

ONA.ID=B.ID)

LEFTOUTERJOINC_TABLEC

ONA.ID=C.ID,D_TABLED

WHEREA.COL1=D.COL1SELECT*FROMA_TABLEA

WHEREA.COL1(+)=0AND

A.COL2(+)='A_VALUE2'SELECT*FROMA_TABLEA

WHEREA.COL1=0AND

A.COL2='A_VALUE2'Greenplum与Oracle比较（3）DifferenceOracleGreenplumNVLSELECTNVL(SUM(VALUE11),0)FS_VALUE1,

NVL(SUM(VALUE21),0)FS_VALUE2

FROMFIELD_SUMSELECTCOALESCE(SUM(VALUE11),0)ASFS_VALUE1,

COALESCE(SUM(VALUE21),0)ASFS_VALUE2

FROMFIELD_SUMTO_NUMBERSELECTCOL1

FROMA_TABLE

ORDERBYTO_NUMBER(COL1)SELECTCOL1

FROMA_TABLE

ORDERBYTO_NUMBER(COL1,999999)

[note：'999999'islengthofCOL1]DECODESELECTDECODE(ENDFLAG,'1','A','B')ENDFLAG

FROMTESTSELECT(CASEENDFLAG

WHEN'1'THEN'A'ELSE'B'END)ASENDFLAG

FROMTEST||SELECTNULL||'-'||NULLASVALUES1

FROMDUALSELECTCOALESCE(NULL,'')||'-'||COALESCE(NULL,'')ASVALUES1

FROMDUALGreenplum与Oracle比较（4）DifferenceOracleGreenplumSYSDATEUPDATEA_TABLE

SETENTREDATE=SYSDATEUPDATEA_TABLE

SETENTREDATE=CURRENT_TIMESTAMP;or

UPDATEA_TABLE

SETENTREDATE=TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DDHH24:MI:SS')SELECTTO_DATE(SYSDATE,'YYYY-MM-DD')ASDAY

FROMDUALSELECTTO_DATE(CURRENT_DATE,'YYYY-MM-DD')ASDAY

FROMDUALaggregateSELECTROUND(AVG(SUM(BASICCNT1)))BASICCNT

FROMACCESS_INFO_SUM1_V

WHEREYEARCODEBETWEEN'200305'AND'200505'

GROUPBYSCCODESELECTROUND(AVG(AIV.BASICCNT))ASBASICCNT

FROM(SELECTSUM(BASICCNT1)ASBASICCNT

FROMACCESS_INFO_SUM1_V

WHEREYEARCODEBETWEEN'200305'AND'200505'

GROUPBYsccode

)AIVCEILSELECTCEIL(SYSDATE-TO_DATE('2005102714:56:10','YYYYMMDDHH24:MI:SS'))ASDAYS

FROMDUALSELECTEXTRACT(DAYFROM(TO_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD-HH24-MI-SS')-TO_TIMESTAMP('2005-10-2714:56:10','YYYY-MM-DD-HH24-MI-SS')))+1ASDAYS

FROMDUALGreenplum与Oracle比较（5）DifferenceOracleGreenplum「"」SELECTLENGTH('')ASVALUE1FROMDUAL

[Result]VALUE1=NULLSELECTLENGTH('')ASVALUE1FROMDUAL

[Result]VALUE1=0SELECTTO_DATE('','YYYYMMDD')ASVALUE2

FROMDUAL

[Result]VALUE2=NULLSELECTTO_DATE('','YYYYMMDD')ASVALUE2

FROMDUAL

[Result]VALUE2=0001-01-01BCSELECTTO_NUMBER('',1)ASVALUE3FROMDUAL

[Result]VALUE3=NULLSELECTTO_NUMBER('',1)ASVALUE3FROMDUAL

[Result]cannotexecuteINSERTINTOTEST(VALUE4)VALUES('')

[Result]VALUE4=NULL

[note:VALUE4isnumerictype]INSERTINTOTEST(VALUE4)VALUES('')

[Result]VALUE4=0

[note:VALUE4isnumerictype]INSERTINTOTEST(VALUE5)VALUES('')

[Result]VALUE5=NULL

[note:VALUE5ischaractertype]INSERTINTOTEST(VALUE5)VALUES('')

[Result]VALUE5=''

[note:VALUE5ischaractertype,lengthequal0]INSERTINTOTEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))

[Result]VALUE6=NULL

[note:VALUE6istimetype]INSERTINTOTEST(VALUE6)VALUES(TO_DATE('','YYYYMMDD'))

[Result]VALUE6=0001-01-01BC

[note:VALUE6istimetype]Greenplum与Oracle比较（6）DifferenceOracleGreenplumNULLIFnotsupportNULLIFSELECTNULLIF(VALUE1,VALUE2)ASCOL1FROMDUALCONCATCONCAT(CHAR,CHAR)CREATEFUNCTIONCONCAT(CHAR,CHAR)

RETURNSCHARAS'SELECT$1||$2'LANGUAGE'sql';ADD_MONTHSADD_MONTHS(date,int)CREATEFUNCTIONadd_months(date,int)

RETURNSdateAS

'SELECT($1+($2::text||''months'')::interval)::date;'

LANGUAGE'sql';LAST_DAYLAST_DAY(DATE)CREATEFUNCTIONLAST_DAY(DATE)

RETURNSDATEAS

'SELECTdate(substr(text($1+interval(''1month'')),1,7)||''-01'')-1'

LANGUAGE'sql';MONTH_BETWEENMONTH_BETWEEN(DATA,DATA)CREATEFUNCTIONMONTH_BETWEEN(DATA,DATA)

RETURNSNUMERICAS

'SELECTto_number((date($1)-date($2)),''999999999'')/31'

LANGUAGE'sql';BIN_TO_NUMSELECTBIN_TO_NUM(1,0,1,0)ASVALUE1FROMDUALSELECTCAST(B'1010'ASINTEGER)ASVALUE1BITANDBITAND(int,int)SELECTint&int目录Greenplum概述和数据分布数据加载和外部表客户端工具Greenplum数据库基础Greenplum与Oracle优化策略其他要点及示例查看执行计划查看sql执行计划的命令:EXPLAIN<query>EXPLAINANALYZE<query>查看执行计划:一般从最后面往上查看以下命令可能会包含Gather,Redistribute,Broadcast等操作JoinsSortsAggregations每步操作给出如下度量：cost(unitsofdiskpagefetches)rows(rowsoutputbythisnode)width(bytecountofthewidestrowproducedbythisnode)执行计划（EXPLAIN）EXPLAINSELECT*FROMnamesWHEREname='Joelle';

QUERYPLANGatherMotion2:1(slice1)(cost=0.00..20.88rows=1width=13)->SeqScanon'names'(cost=0.00..20.88rows=1width=13)Filter:name::text~~'Joelle'::textSQLqueryFilterconditionGathermotionCost,rows,andwidth执行计划（EXPLAINANALYZE）EXPLAINANALYZESELECT*FROMnamesWHEREname='Joelle';QUERYPLANGatherMotion2:1(slice1)(cost=0.00..20.88rows=1width=13)recv:Total1rowswith0.305mstofirstrow,0.537mstoend.->SeqScanon'names'(cost=0.00..20.88rows=1width=13)

Total1rows(seg0)with0.255mstofirstrow,0.486mstoend.Filter:name::text~~'Joelle'::text22.548mselapsed1segment

returnedrowsActualtimeto

runthequery1rowreturned

tomaster优化策略（1）1、数据分布-选用合适字段作为DistributionKey(DK)，尽量做到平均分布Selectgp_segment_id,count(*)fromtablenamegroupby1;例1：优化策略（2）2、尽量选择常用连接条件或Groupby列作为DistributionKey，最好只用一个字段作为DK，并且DK列的distinct值越多越好。例2：insertintotablec(auction_id,….)select*fromtablealeftjointalbebontablea.selid=tablec.id;优化前耗时120秒优化方式：将tablea的DistributionKey改为selid，tableb的改为id，tablec的DistributionKey改为auction_id优化后耗时88秒，提升了32秒

修改DK的语法：ALTERTABLEnameSETDISTRIBUTEDBY(column,[...])；ALTERTABLEnameSETWITH(REORGANIZE=true)；

3、采用Createtable代替Insertinto;尽量对中间过程使用临时表；

数据库内有很多表都是全量更新的，因此可以用Createtable来代替Insertinto，性能能够大幅提高。例2中的SQL，改成Createtabletablecas(select*fromtablealeftjointablebontablea.selid=tablec.id)distributedby(auction_id)，优化后耗时为65秒，提升了13秒优化策略（3）4、定期收集统计信息和执行Vacuum定期收集统计信息,可以优化SQL执行路径；sytax：analyzetable;数据加载后执行Vacuum，或者采用重创建表的方式来释放垃圾数据，可以提高SQL效率；对系统的数据字典也需要定期做vacuum由于GPDB使用的是MVCC事务并发模型，被删除或更新的数据行依然占据着物理磁盘空间，即便它们对于新的事务已经不可见。如果数据库有大量的更新和删除，会产生大量过期记录。VACUMM命令还会收集表级别的统计信息，如行数和页面数。ANALYZE命令收集查询规划器需要用到的列统计信息。VACUUM和ANALYZE操作可以在同一个命令中一起运行。例：=#VACUUMANALYZEmytable;

5、SQL合并在Greenplum中，建议将多个表的join和嵌套子查询SQL适当合并为一个SQL实现，可以减少IO，达到提高性能的目的