轻松上手openGauss第4期：openGauss基本管理（下）

轻松上手openGauss轻松上手openGauss openGaussGauss松鼠会Gauss松鼠会2Gauss松鼠会sussussopenGauss序列和同义词管理3openGaussopenGaussopenGauss表管理Gauss松鼠会4报表挖掘分析/预测OLAP数据库索业务应用中活动的数据以支撑日常的业务活动；BI数据仓库DataWarehouse数据集市报表挖掘分析/预测OLAP数据库索业务应用中活动的数据以支撑日常的业务活动；BI数据仓库DataWarehouse数据集市DatamartETL数据集市DatamartL2、联机分析处理(OLAP)：存储历史数据以支撑复杂的分析操作，侧重决策支持；据挖掘DataScoringMining知识/规则Gauss松鼠会OLTP数据库业业务系统财财务系统销售系销售系统客服系客服系统5TABLEIFTABLEIFNOTEXISTSwarehouse_t1--表不存在时才创建，使得当该表存在时该建表语句不会报错(W_WAREHOUSE_SKINTEGERNOTNULL,W_WAREHOUSE_IDCHAR(16)NOTNULL,W_WAREHOUSE_NAMEVARCHAR(20)UNIQUEDEFERRABLE,--事务结束时检查字段是否有重复W_STREET_NAMEVARCHAR(60)DICTIONARY,--压缩算法DictionaryW_SUITE_NUMBERCHAR(10),W_STATECHAR(2)DEFAULT'GA',--缺省值为'GA'W_GMT_OFFSETDECIMAL(5,2))TABLESPACEtbs1;Gauss松鼠会createcreatetableemp1asselect*fromempwheresal<2000;createtableemp2astableemp;CREATECREATEUNLOGGEDTABLEwarehouse_t2--不写入预写日志(W_WAREHOUSE_SKINTEGERPRIMARYKEY,--主键约束W_WAREHOUSE_IDCHAR(16)NOTNULL,--非空约束W_WAREHOUSE_NAMEVARCHAR(20)UNIQUE,--唯一键约束W_GMT_OFFSETDECIMAL(5,2),CONSTRAINTW_CONSTR_KEY2CHECK(W_WAREHOUSE_SK>0ANDW_WAREHOUSE_NAMEISNOTNULL)--检查列约束)WITH(fillfactor=70);--表的填充因子70%6Gauss松Gauss松鼠会CREATECREATETEMPORARYTABLEwarehouset3(WWAREHOUSESKINTEGER__W_WAREHOUSE_IDCHAR(16)PRIMARYKEY(W_WAREHOUSE_SK))ONCOMMITDELETEROWS;--主键约束--提交事务时删除该临时表数据--创建临时表(临时表不能指定schema)NOTNULL,NOTNULL,_CREATETABLEwarehouset4(W_WAREHOUSE_SKINTEGERNOTNULL,W_WAREHOUSE_IDCHAR(16)NOTNULL,W_WAREHOUSE_NAMEVARCHAR(20)UNIQUEUSINGINDEXTABLESPACEtbs1,--指定该列索引存储的表空间CONSTRAINTW_CSTR_KEY1PRIMARYKEY(W_WAREHOUSE_SK,W_WAREHOUSE_ID)--复合主键约束W_CSTR_KEY1)COMPRESS;_CREATETABLEwarehouset5(WWAREHOUSESK__WWAREHOUSEID__WWAREHOUSENAME__WGMTOFFSET__);INTEGERPRIMARYKEYCHECK(W_WAREHOUSE_SK>0),CHAR(16)NOTNULL,VARCHAR(20)CHECK(W_WAREHOUSE_NAMEISNOTNULL),DECIMAL(5,2)REFERENCESDEPT(DEPTNO)--检查列约束--外键约束--检查列约束_77Gauss松鼠会CREATECREATETABLEwarehouset6(WWAREHOUSESK__WWAREHOUSEID__WWAREHOUSENAME__WGMTOFFSET__PARTIALCLUSTERKEY(W_WAREHOUSE_SK,W_WAREHOUSE_ID))WITH(ORIENTATION=COLUMN,COMPRESSION=HIGH);--局部聚簇存储--带有压缩特性的列存储表(列存储表不支持约束)INTEGERCHAR(16)VARCHAR(20)DECIMAL(5,2),NOTNULL,NOTNULL,_Ø示例八(MOT表)：openGauss引入了MOT存储引擎，完全支持ACID特性。提高多核服务器的利用率。[omm@db1~]$gs_gucset-Nall-Iall-c"enable_incremental_checkpoint=off"--MOT特性需要关闭增量检查点[omm@db1~]$gs_om-tstop&&gs_om-tstartgsql-dmydb-p26000-rshowenable_incremental_checkpoint;_CREATEFOREIGNTABLEwarehousemot_•低开销：CALC的开销比其他异步检查点算法小。•使用虚拟一致性点：CALC不需要静默数据库以实现物理一致性点。JOBCHAR(16),SALDECIMAL(8,2),DEPTNOINTEGER)SERVERMOT_SERVER;--MOT表不支持ALTER操作8表管理操作及注意事项Gauss松鼠会闭行访问控制开关等。但有些限制需要注意：•不能修改分区表的tablespace，但可以修改分区的tablespace；•不支持修改存储参数ORIENTATION；•不支持增加自增列，或者增加DEFAULT值中包含nextval()表达式的列；•不支持对外表、临时表开启行访问控制开关；PRIMARYKEYNOTNULL约束。列存表注意事项：.支持delta表；.支持的数据类型有限制要求；.不支持插入数组；.表的数量建议不超过1000个；.表级约束只支持PARTIALCLUSTERKEY，不支持主外键等表级约束；.字段约束只支持NULL、NOTNULL和DEFAULT常量值；.对字段约束的修改当前只支持对DEFAULT值的修改，不支持对非空约束的修改。--仅能看到指定用户的数据，--仅能看到指定用户的数据，系统管理员不受行访问控制影响dropGauss松鼠会alteraltertableemp1modifysalnumber(10,2);--修改列属性altertableemp1renamecolumnenametoname;--重命名列altertableemp1addprimarykey(empno);--添加主键约束altertableemp1addconstraintchk_deptcheck(deptnoisnotnull);--添加check约束altertableemp1addconstraintfk_deptforeignkey(deptno)referencesdept(deptno);--添加外键约束altertableemp1modifysalconstraintchk_salnotnull;--修改列约束条件altertableemp1renameconstraintchk_depttochk_deptno;--重命名约束altertableemp1setschemajack;--设置所属schemaaltertablejack.emp1renametoemp2;--重命名表\djack.emp2useralicepassword'gauss@123';createtableuseralicepassword'gauss@123';createtableall_data(idint,rolevarchar(100),datavarchar(100));insertintoall_datavalues(1,'alice','alicedata');insertintoall_datavalues(2,'bob','bobdata');insertintoall_datavalues(3,'peter','peterdata');grantselectonall_datatoalice;altertableall_dataenablerowlevelsecurity;createrowlevelsecuritypolicyall_data_rlsonall_datausing(role=current_user);\c-aliceselect*fromall_data;explainselect*fromcreatepublic.all_data;tabletableall_data;9Gauss松鼠会分区表是把逻辑上的一张表根据某种方案分成几个物理块进行存储，这张逻辑上的表称之为分区区方案有范围分区(RangePartitioning)、间隔分区(IntervalPartitioning)、哈希分区 分区优势：ü改善查询性能：按需搜索指定分区ü增强可用性：分区故障不影响其他分区ü方便维护：分区故障后，只修复该分区即可。ü均衡I/O：不同分区映射到不同磁盘以平衡I/Otbs2tbs2Ø创建范围分区表Gauss松鼠会CREATETABLEpt1(idnamevarchar(20)scoreDECIMAL(5,2))PARTITIONBYRANGE(score)(PARTITIONP1VALUESLESSTHAN(60)tablespacetbs1,PARTITIONP2VALUESLESSTHAN(85)tablespacetbs2,PARTITIONP3VALUESLESSTHAN(MAXVALUE)tablespaceinsertselectselect*frompt1partition(p3);selectp.relname,c.relnamefrompg_partitionp,pg_classcwherep.parentid=c.oid;relname|relname---------+---------p3|pt1p2|pt1p1|pt1pt1|pt1Ø创建间隔分区表Gauss松鼠会CREATECREATETABLEsales(prod_idNUMBER(6),prod_namevarchar(20),time_idDATE)PARTITIONBYRANGE(time_id)INTERVAL('1month')storein(tbs1,tbs2)(PARTITIONp1VALUESLESSTHAN('2020-10-0100:00:00'),PARTITIONp2VALUESLESSTHAN('2020-11-0100:00:00'));INSERTINTOsalesVALUES(001,'Hamburger','2020-09-1012:00:00');INSERTINTOsalesVALUES(002,'Cheese','2020-10-2019:20:00');INSERTINTOsalesVALUES(003,'Pizza','2020-11-2200:00:00');INSERTINTOsalesVALUES(004,'Chips','2020-12-2018:18:00');SELECTSELECTt1.oid,t1.relname,partstrategy,boundaries,t1.reltablespace,t1.parentidFROMpg_partitiont1,pg_classt2WHEREt1.parentid=t2.oidANDt2.relname='sales'ANDt1.parttype='p';oid|relname|partstrategy|boundaries|reltablespace|parentid-------+---------+--------------+-------------------------+---------------+----------{"2020-10-0100:00:00"}|{"2020-12-0100:00:00"}{"2021-01-0100:00:00"}select*fromsalespartition(p1);select*fromsalespartition(p2);select*fromsalespartition(sys_p1);select*fromsalespartition(sys_p2);{"2020-11-0100:00:00"}p1p2sys_p1sys_p2--零表示使用该数据库的缺省表空间0016386165161665916660166611666216655166551665516655rriiØ创建HASH分区表Gauss松鼠会CREATECREATETABLEpt_hash(idint,namevarchar(20))PARTITIONBYHASH(id)(partitionpt_hash_1,partitionpt_hash_2,partitionpt_hash_3);--插入测试数据postgrespostgres=#SELECTt1.oid,t1.relname,partstrategy,boundaries,t1.reltablespace,t1.parentidpostgres-#FROMpg_partitiont1,pg_classt2postgres-#WHEREt1.parentid=t2.oidpostgres-#ANDt2.relname='pt_hash'postgres-#ANDt1.parttype='p';oid|relname|partstrategy|boundaries|reltablespace|parentid-------+-----------+--------------+------------+---------------+----------||16408--零表示使用该数据库的缺省表空间select*frompt_hashpartition(pt_hash_1);select*frompt_hashpartition(pt_hash_2);select*frompt_hashpartition(pt_hash_3);pt_hash_1pt_hash_2pt_hash_3164121641316414{0}{1}{2}1640816408hhh000Ø创建LIST分区表Gauss松鼠会CREATECREATETABLEpt_list(idint,namevarchar(20),locvarchar(20))PARTITIONBYlist(loc)(partitionpt_list_1values('beijing'),partitionpt_list_2values('shanghai'),partitionpt_list_3values('shenzhen'));--插入测试数据postgrespostgres=#SELECTt1.oid,t1.relname,partstrategy,boundaries,t1.reltablespace,t1.parentidpostgres-#FROMpg_partitiont1,pg_classt2postgres-#WHEREt1.parentid=t2.oidpostgres-#ANDt2.relname='pt_list'postgres-#ANDt1.parttype='p';oid|relname|partstrategy|boundaries|reltablespace|parentid-------+-----------+--------------+------------+---------------+----------||16415--零表示使用该数据库的缺省表空间select*frompt_listpartition(pt_list_1);select*frompt_listpartition(pt_list_2);select*frompt_listpartition(pt_list_3);{beijing}{shanghai}{shenzhen}pt_list_1pt_list_2pt_list_31641916420164211641516415lll000Gauss松鼠会alteraltertablept_rangedroppartitionfor(90);或altertablept_rangedroppartitionp3;alteraltertablept_rangeaddpartitionp3valueslessthan(95);altertablept_rangeaddpartitionp4valueslessthan(MAXVALUE);alteraltertablept_rangerenamepartitionp4topmax;altertablept_rangemovepartitionpmaxtablespacetbs1;altertablept_rangesplitpartitionp3at(90)into(partitionp5,partitionp6);altertablept_rangemergepartitionsp5,p6intopartitionp3;altertablept_rangeexchangepartition(pmax)withtableex_t1withvalidation;--查询分区表属性信息altertablept_rangeexchangepartition(pmax)withtableex_t1withvalidation;SELECTt1.oid,t1.relname,partstrategy,boundaries,t1.reltablespace,t1.parentid,t2.relnameFROMpg_partitiont1,pg_classt2WHEREt1.parentid=t2.oidANDt1.parttype='p';--重命名分区--移动分区表空间--分离分区--合并分区--普通表与分区表数据交换openGaussopenGaussopenGauss索引管理Gauss松鼠会Gauss松鼠会Ø应该考虑在哪些列上创建索引？1.经常执行查询的字段；2.在连接条件上创建索引，对于存在多字段连接的查询，建议在这些字段上建立组合索引；3.where子句的过滤条件字段上(尤其是范围条件)；4.为经常出现在关键字ORDERBY、GROUPBY、DISTINCT后面的字段建立索引。意事项：DML应整个分区表。Gauss松鼠会行存表支持的索引类型：ØBtree(行存表缺省值)ØginØgist过这种结构能够快速的查找索引。btree适合比较查询以及查询范围。个键的值(比如数组)。◾gist：gist索引适用于几何和地理等多维数据类型和集合数据类型。Gauss松鼠会列存表支持的索引类型：ØPsort(列存表缺省值)Btree索引：列存储引擎在Btree索引的支持角度，与传统的行存储引擎无本质差别，索引页面上都存储的是[key->ctid]映射信息。行存储引擎的ctid中记录的是blocknumber(块号)和offset(偏移量)，通过ctid的信息可直接找到对应的数据所在数据文件页面中的位置。列存储引擎的ctid中记录的是(cu_id,offset)，要通过CUDesc结构来进行查找。Btree扫描操作查找CUDesc表的cu_id列，找到对应的CUDesc记录，并由此打开对应的CU文件，根据offset找到数据。稀稀疏索引：列存储引擎每个列自带min/max稀疏索引，在CUDesc表中存储着每个CU的最小值和最大值。在查询的时候，可以根据查询条件做简单的min/max判断，如果查询条件不在(min,max)范围大地减少IO读取。20Psort索引可以对部分区间内的数据Psort索引可以对部分区间内的数据保证CU之前交集尽场景下稀疏索引效率。Gauss松鼠会Gauss松鼠会列存表缺省索引：局部排序索引(partialsortindex)如果数据模型较为离散，那么稀疏索引不同CU之间的min、max就会有大量交集，这种情况下根据谓词对列存表进行检索的过程中，会出现大量的CU误读取。Psort索引生效后PsortPsort索引生效后21Gauss松鼠会Gauss松鼠会surrogatekey)来用于生成主键索的记录以及引用。value部分是对应数据的行指针。MasstreeMasstreeGauss松鼠会l每个前缀树节点对应的键值都有着相同的前缀l每个内部节点包含最多16个指向叶子节点的连接基于键值匹配指向下一层的指针Masstree实际上是一个类似于诸多B+树以前缀树(trie)的组织形式堆叠的基数树(Radixtree)模式，以键 (Key)的前缀作为索引，每k个字节形成一层B+树结构，在每层中处理键(Key)中这k个字节对应所需的insert/lookup/update/delete流程。Masstree中的读操作使用了类OCC(乐观并发控制)的实现，而所有的update锁仅为本地锁。在树的结构上，每层的内部节点和叶子节点都会带有版本，因此可以借助版本检查来避免细粒度锁。可以最大程度的利用CPU预取的结果(尤其是在树的深度遍历过程中)，减少了与DRAM交互带来的额外时延。执行时延大部分由于内存交互的时延组成，因此预取可以有效地提高masstree遍历操作的执行效率以及缓存块的命中率。参考资料：《第4期:openGauss高吞吐低时延存储模式》/zh/video.html22Gauss松鼠会tabletidxtabletidxtasselectrelfilenode,relowner,reltablespace,relnamefrompg_classlimitcreatecreateuniqueindext1_fn_idxont1(relfilenode);createindext1_owner_tbs_idxont1(relowner,reltablespace);createindext1_lttbs_idxont1(reltablespace)wherereltablespace<20;createindext1_upname_idxont1(upper(relname));createindexpt1_id_idxonpt1(id)local;createindexpt1_score_idxonpt1(score)globaltablespacetbs1;indext1_fnindext1_fn_idxrenametot1_fn_idx2;alterindext1_fn_idx2settablespacetbs2;alterindext1_lttbs_idxunusable;alterindext1_lttbs_idxrebuild;alterindexpt1_id_idxrebuildpartitionp1_id_idx;alterindexpt1_id_idxmodifypartitionp1_id_idxunusable;alterindexpt1_id_idxrenamepartitionp1_id_idxtop1_id_idx2;alterindexpt1_id_idxmovepartitionp1_id_idx2tablespace232324Gauss松鼠会openGauss视图管理openGauss25物化"(Materialized)物化"(Materialized)之后查询的结果实际存储在磁盘类表的形式保存结果，但无法物化视图是"中，有效提升查询物化视图以像普通表那样进行从基表获取更新数视图简介Gauss松鼠会26视图的创建与管理Gauss松鼠会createcreateviewv1asSELECT*FROMpg_tablespaceWHEREspcname='pg_default';--创建视图creatematerializedviewmv1tablespacetbs1asSELECT*FROMpg_tablespaceWHEREspcname='pg_default';--创建物化视图--查询视图数据select*fromv--查询视图数据select*frommv1;\d+v1--查询视图定义selectpg_get_viewdef('--查询视图定义pg_get_viewdefSELECT*FROMpg_tablespaceWHERE(pg_tablespace.spcname='pg_default'::name);--重命名视图--修改视图属主--修改视图schema----重命名视图--修改视图属主--修改视图schema--刷新物化视图alterviewv2ownertojack;alterviewv2setschemajack;refreshmaterializedviewmv1;dropdropviewjack.v2;dropmaterializedviewmv1;--删除视图--删除物化视图27Gauss松鼠会openGauss序列和同义词管理openGauss28Gauss松鼠会eSequence具有唯一标识性,常被用作主键的原因。使用序列确保字段唯一的方法有两种：----方法一：postgres=#createtables1(idserial);NOTICE:CREATETABLEwillcreateimplicitsequence"s1_id_seq"forserialcolumn"s1.id"CREATETABLE--方法二：postgres=#createtables2(idintdefaultnextval('seq_test'));CREATETABLE--缺省值为1--缺省值为1--缺省值为263-1--缺省值：对于递增序列为--缺省值为1--缺省值为1--缺省值为263-1--缺省值：对于递增序列为minvalue，递减序列为maxvalue--缺省值为1--缺省值为NOCYCLE--缺省