请谁做网站比较放心,南京网站制作公司招聘,网站建设 公司 天津,做智能网站软件文章目录 0.简介1.概念介绍2.分区表技术产生的背景3.分区类型及使用方式4.实现原理4.1 分区表创建4.2 分区表查询4.3 分区表写入4.4 分区表删除 0.简介
本文主要介绍PG中分区表的概念#xff0c;产生分区表技术的原因#xff0c;使用方式和其内部实现原理#xff0c;旨在能… 文章目录 0.简介1.概念介绍2.分区表技术产生的背景3.分区类型及使用方式4.实现原理4.1 分区表创建4.2 分区表查询4.3 分区表写入4.4 分区表删除 0.简介
本文主要介绍PG中分区表的概念产生分区表技术的原因使用方式和其内部实现原理旨在能对PG分区表技术有一个系统的说明。
1.概念介绍
分区表是数据库用于管理大量数据的一种技术它允许将一个大表分割成多个小表这些小表在物理上是独立的但在逻辑上作为一个整体被查询和更新。分区表的主要优势在于提高查询性能特别是当查询集中在少数几个分区时。此外分区表还可以简化数据的批量删除和加载以及将不常用的数据迁移到成本较低的存储介质上实现冷热分离。 1主表/父表/Master Table该表是创建子表的模板。它是一个正常的普通表但正常情况下它并不储存任何数据。 2子表/分区表/Child Table/Partition Table这些表继承并属于一个主表。子表中存储所有的数据。主表与分区表属于一对多的关系也就是说一个主表包含多个分区表而一个分区表只从属于一个主表
2.分区表技术产生的背景
在使用数据库过程中随着时间的推移每张表数据量会不断增加造成查询速度越来越慢在分区表之前有很多查询的技术去优化它比如添加特殊的索引将磁盘分区把日志文件放到单独的磁盘分区调整参数等等。这些优化技术都能对查询性能做出或多或少的提升但其并没有对于表特点以及局部性的原理进行合理应用因为对于很多应用来说许多历史数据对于查询可能并没有太多用处或者是某一列是特定值时是更为关系的数据如果能够将不常用数据进行隐藏就能大大提高查询速度分区表就是为了解决这个问题而产生的。比如可以按照时间作为分区键进行分区将新老数据分离。
3.分区类型及使用方式
PG 10以后支持三种分区以下都使用主流的使用方式声明式分区还有表继承进行说明 1范围Range分区
CREATE TABLE students (grade INTEGER) PARTITION BY RANGE(grade);
CREATE TABLE stu_fail PARTITION OF students FOR VALUES FROM (MINVALUE) TO (60);
CREATE TABLE stu_pass PARTITION OF students FOR VALUES FROM (60) TO (MAXVALUE);\d studentsTable public.studentsColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------------------------------------------------------------grade | integer | | | | plain | |
Partition key: RANGE (grade)
Partitions: stu_fail FOR VALUES FROM (MINVALUE) TO (60),stu_pass FOR VALUES FROM (60) TO (MAXVALUE)\d stu_failTable public.stu_failColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------------------------------------------------------------grade | integer | | | | plain | |
Partition of: students FOR VALUES FROM (MINVALUE) TO (60)
Partition constraint: ((grade IS NOT NULL) AND (grade 60))可以看出其中最大值是小于关系不是小于等于关系。2列表List分区 列表分区明确指定根据某字段的某个具体值进行分区默认分区可选值保存不属于任何指定分区的列表值。
CREATE TABLE students (status character varying(30)) PARTITION BY LIST(status);
CREATE TABLE stu_active PARTITION OF students FOR VALUES IN (ACTIVE);
CREATE TABLE stu_exp PARTITION OF students FOR VALUES IN (EXPIRED);
CREATE TABLE stu_others PARTITION OF students DEFAULT;\d studentsTable public.studentsColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------status | character varying(30) | | | | extended | |
Partition key: LIST (status)
Partitions: stu_active FOR VALUES IN (ACTIVE),stu_exp FOR VALUES IN (EXPIRED),stu_others DEFAULT\d stu_others;Table public.stu_othersColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------------------------------------------------------------------------------------------status | character varying(30) | | | | extended | |
Partition of: students DEFAULT
Partition constraint: (NOT ((status IS NOT NULL) AND ((status)::text ANY (ARRAY[ACTIVE::character varying(30), EXPIRED::character varying(30)]))))3哈希Hash分区 通过对每个分区使用取模和余数来创建hash分区modulus指定了对N取模而remainder指定了除完后的余数。
CREATE TABLE students (id INTEGER) PARTITION BY HASH(id);
CREATE TABLE stu_part1 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE stu_part2 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE stu_part3 PARTITION OF students FOR VALUES WITH (modulus 3, remainder 2);\d students;Table public.studentsColumn | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------------------------------------------------------------id | integer | | | | plain | |
Partition key: HASH (id)
Partitions: stu_part1 FOR VALUES WITH (modulus 3, remainder 0),stu_part2 FOR VALUES WITH (modulus 3, remainder 1),stu_part3 FOR VALUES WITH (modulus 3, remainder 2)\d stu_part1;Table public.stu_part1Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------------------------------------------------------------------------id | integer | | | | plain | |
Partition of: students FOR VALUES WITH (modulus 3, remainder 0)
Partition constraint: satisfies_hash_partition(16439::oid, 3, 0, id)PG分区还支持创建子分区LIST-LISTLIST-RANGELIST-HASHRANGE-RANGERANGE-LISTRANGE-HASHHASH-HASHHASH-LIST和HASH-RANGE以及和普通表之间互相转换DETACH PARTITION可以将分区表转换为普通表而attach partition可以将普通表附加到分区表上。
4.实现原理
4.1 分区表创建
分区表创建相对简单对PG来说实际是一张逻辑表对应多张物理表下面简单看创建时其分区表相关的调用流程。
-- transformPartitionBound -- RelationGetPartitionKey-- get_partition_strategy-- transformPartitionBoundValue-- transformPartitionRangeBounds-- validateInfiniteBounds-- check_new_partition_bound-- StorePartitionBound // Update pg_class tuple of rel to store the partition bound and set relispartition to true-- StoreCatalogInheritance // 向系统表pg_inherits插入信息// 处理stmt-partspec-- transformPartitionSpec-- ComputePartitionAttrs-- StorePartitionKey // 向pg_partitioned_table中插入分区键等信息4.2 分区表查询
分区表查询是要根据条件查询一定数量的子表然后进行返回其主要分为三步 1识别分区表并找到所有的分区子表
/** expand_inherited_tables* Expand each rangetable entry that represents an inheritance set* into an append relation. At the conclusion of this process,* the inh flag is set in all and only those RTEs that are append* relation parents.*/
void
expand_inherited_tables(PlannerInfo *root)
{Index nrtes;Index rti;ListCell *rl;/** expand_inherited_rtentry may add RTEs to parse-rtable. The function is* expected to recursively handle any RTEs that it creates with inhtrue.* So just scan as far as the original end of the rtable list.*/nrtes list_length(root-parse-rtable);rl list_head(root-parse-rtable);for (rti 1; rti nrtes; rti){RangeTblEntry *rte (RangeTblEntry *) lfirst(rl);expand_inherited_rtentry(root, rte, rti);rl lnext(rl);}
}2根据约束条件识别需要查询的分区,也就是分区裁剪只读取需要的分区;
prune_append_rel_partitions* Process rels baserestrictinfo and make use of quals which can be* evaluated during query planning in order to determine the minimum set* of partitions which must be scanned to satisfy these quals. Returns* the matching partitions in the form of a Relids set containing the* partitions RT indexes.** Callers must ensure that rel is a partitioned table.*/
Relids
prune_append_rel_partitions(RelOptInfo *rel)
{Relids result;List *clauses rel-baserestrictinfo;List *pruning_steps;GeneratePruningStepsContext gcontext;PartitionPruneContext context;Bitmapset *partindexes;int i;Assert(clauses ! NIL);Assert(rel-part_scheme ! NULL);/* If there are no partitions, return the empty set */if (rel-nparts 0)return NULL;/** Process clauses to extract pruning steps that are usable at plan time.* If the clauses are found to be contradictory, we can return the empty* set.*/gen_partprune_steps(rel, clauses, PARTTARGET_PLANNER,gcontext);if (gcontext.contradictory)return NULL;pruning_steps gcontext.steps;/* Set up PartitionPruneContext */context.strategy rel-part_scheme-strategy;context.partnatts rel-part_scheme-partnatts;context.nparts rel-nparts;context.boundinfo rel-boundinfo;context.partcollation rel-part_scheme-partcollation;context.partsupfunc rel-part_scheme-partsupfunc;context.stepcmpfuncs (FmgrInfo *) palloc0(sizeof(FmgrInfo) *context.partnatts *list_length(pruning_steps));context.ppccontext CurrentMemoryContext;/* These are not valid when being called from the planner */context.partrel NULL;context.planstate NULL;context.exprstates NULL;/* Actual pruning happens here. */partindexes get_matching_partitions(context, pruning_steps);/* Add selected partitions RT indexes to result. */i -1;result NULL;while ((i bms_next_member(partindexes, i)) 0)result bms_add_member(result, rel-part_rels[i]-relid);return result;
}3对结果集执行APPEND,作为最终结果输出这和其他表append操作一致使用ExecInitAppend和ExecAppend函数。
/* ----------------------------------------------------------------* ExecAppend** Handles iteration over multiple subplans.* ----------------------------------------------------------------*/
static TupleTableSlot *
ExecAppend(PlanState *pstate)
{AppendState *node castNode(AppendState, pstate);if (node-as_whichplan 0){/** If no subplan has been chosen, we must choose one before* proceeding.*/if (node-as_whichplan INVALID_SUBPLAN_INDEX !node-choose_next_subplan(node))return ExecClearTuple(node-ps.ps_ResultTupleSlot);/* Nothing to do if there are no matching subplans */else if (node-as_whichplan NO_MATCHING_SUBPLANS)return ExecClearTuple(node-ps.ps_ResultTupleSlot);}for (;;){PlanState *subnode;TupleTableSlot *result;CHECK_FOR_INTERRUPTS();/** figure out which subplan we are currently processing*/Assert(node-as_whichplan 0 node-as_whichplan node-as_nplans);subnode node-appendplans[node-as_whichplan];/** get a tuple from the subplan*/result ExecProcNode(subnode);if (!TupIsNull(result)){/** If the subplan gave us something then return it as-is. We do* NOT make use of the result slot that was set up in* ExecInitAppend; theres no need for it.*/return result;}/* choose new subplan; if none, were done */if (!node-choose_next_subplan(node))return ExecClearTuple(node-ps.ps_ResultTupleSlot);}
}4.3 分区表写入
分区表写入分为两个阶段一个是查找到要写入的分区然后就是正常去做写入下面来看查找分区的函数。
/** ExecPrepareTupleRouting --- prepare for routing one tuple** Determine the partition in which the tuple in slot is to be inserted,* and modify mtstate and estate to prepare for it.** Caller must revert the estate changes after executing the insertion!* In mtstate, transition capture changes may also need to be reverted.** Returns a slot holding the tuple of the partition rowtype.*/
static TupleTableSlot *
ExecPrepareTupleRouting(ModifyTableState *mtstate,EState *estate,PartitionTupleRouting *proute,ResultRelInfo *targetRelInfo,TupleTableSlot *slot)
{ModifyTable *node;int partidx;ResultRelInfo *partrel;HeapTuple tuple;/** Determine the target partition. If ExecFindPartition does not find a* partition after all, it doesnt return here; otherwise, the returned* value is to be used as an index into the arrays for the ResultRelInfo* and TupleConversionMap for the partition.*/partidx ExecFindPartition(targetRelInfo,proute-partition_dispatch_info,slot,estate);Assert(partidx 0 partidx proute-num_partitions);/** Get the ResultRelInfo corresponding to the selected partition; if not* yet there, initialize it.*/partrel proute-partitions[partidx];if (partrel NULL)partrel ExecInitPartitionInfo(mtstate, targetRelInfo,proute, estate,partidx);/** Check whether the partition is routable if we didnt yet** Note: an UPDATE of a partition key invokes an INSERT that moves the* tuple to a new partition. This check would be applied to a subplan* partition of such an UPDATE that is chosen as the partition to route* the tuple to. The reason we do this check here rather than in* ExecSetupPartitionTupleRouting is to avoid aborting such an UPDATE* unnecessarily due to non-routable subplan partitions that may not be* chosen for update tuple movement after all.*/if (!partrel-ri_PartitionReadyForRouting){/* Verify the partition is a valid target for INSERT. */CheckValidResultRel(partrel, CMD_INSERT);/* Set up information needed for routing tuples to the partition. */ExecInitRoutingInfo(mtstate, estate, proute, partrel, partidx);}/** Make it look like we are inserting into the partition.*/estate-es_result_relation_info partrel;/* Get the heap tuple out of the given slot. */tuple ExecMaterializeSlot(slot);/** If were capturing transition tuples, we might need to convert from the* partition rowtype to parent rowtype.*/if (mtstate-mt_transition_capture ! NULL){if (partrel-ri_TrigDesc partrel-ri_TrigDesc-trig_insert_before_row){/** If there are any BEFORE triggers on the partition, well have* to be ready to convert their result back to tuplestore format.*/mtstate-mt_transition_capture-tcs_original_insert_tuple NULL;mtstate-mt_transition_capture-tcs_map TupConvMapForLeaf(proute, targetRelInfo, partidx);}else{/** Otherwise, just remember the original unconverted tuple, to* avoid a needless round trip conversion.*/mtstate-mt_transition_capture-tcs_original_insert_tuple tuple;mtstate-mt_transition_capture-tcs_map NULL;}}if (mtstate-mt_oc_transition_capture ! NULL){mtstate-mt_oc_transition_capture-tcs_map TupConvMapForLeaf(proute, targetRelInfo, partidx);}/** Convert the tuple, if necessary.*/ConvertPartitionTupleSlot(proute-parent_child_tupconv_maps[partidx],tuple,proute-partition_tuple_slot,slot);/* Initialize information needed to handle ON CONFLICT DO UPDATE. */Assert(mtstate ! NULL);node (ModifyTable *) mtstate-ps.plan;if (node-onConflictAction ONCONFLICT_UPDATE){Assert(mtstate-mt_existing ! NULL);ExecSetSlotDescriptor(mtstate-mt_existing,RelationGetDescr(partrel-ri_RelationDesc));Assert(mtstate-mt_conflproj ! NULL);ExecSetSlotDescriptor(mtstate-mt_conflproj,partrel-ri_onConflict-oc_ProjTupdesc);}return slot;
}4.4 分区表删除
分区表的删除即为先删除其分区然后整体删除。
