【資料庫核心分析系列】:資料庫索引的建立過程
文章目錄
- 普通表索引
-
- DefineInde
- Index_create函式
- index_build
- 分割槽表索引
資料庫索引可以提高資料的訪問速度,openGauss支援唯一索引、多欄位索引、部分索引和表示式索引。
- 行存表(ASTORE儲存引擎)支援的索引型別:btree(行存表預設值)、hash、gin、gist。行存表(USTORE儲存引擎)支援的索引型別:ubtree。
- 列存表支援的索引型別:Psort(列存表預設值)、btree、gin。
- 全域性臨時表不支援GIN索引和Gist索引。
如上一篇分析資料庫表的建立過程,standard_ProcessUtility函式會根據nodeTag(parsetree)的值來確定sql的操作型別,create table一般都是進入T_CreateStmt分支呼叫CreateCommand函式。create index則進入T_IndexStmt分支呼叫DefineIndex函式。在呼叫DefineIndex前會首先執行函式transformIndexStmt,如果語句沒有指定索引型別則會使用預設值。
if (stmt->accessMethod == NULL) {
if (!isColStore) {
/* row store using btree index by default */
if (!RelationIsUstoreFormat(rel)) {
stmt->accessMethod = DEFAULT_INDEX_TYPE; // 行存表ASTORE儲存引擎預設值btree
} else {
stmt->accessMethod = DEFAULT_USTORE_INDEX_TYPE; // 行存表USTORE儲存引擎預設值ubtree
}
……
} else {
/* column store using psort index by default */
stmt->accessMethod = DEFAULT_CSTORE_INDEX_TYPE; // 列存表預設值psort
}
}
#define DEFAULT_INDEX_TYPE "btree"
#define DEFAULT_HASH_INDEX_TYPE "hash"
#define DEFAULT_CSTORE_INDEX_TYPE "psort"
#define DEFAULT_GIST_INDEX_TYPE "gist"
#define CSTORE_BTREE_INDEX_TYPE "cbtree"
#define DEFAULT_GIN_INDEX_TYPE "gin"
#define CSTORE_GINBTREE_INDEX_TYPE "cgin"
#define DEFAULT_USTORE_INDEX_TYPE "ubtree"
普通表索引
DefineInde
DefineIndex為建立索引主入口函式。通常建立索引以Share鎖鎖定表,允許併發查詢,但禁上對錶進行修改。如果建立索引時指定關鍵字CONCURRENTLY以不阻塞DML的方式建立索引,即允許讀取和更新表,以ShareUpdateExclusiveLock鎖鎖定表。
lockmode = concurrent ? ShareUpdateExclusiveLock : ShareLock;
rel = heap_open(relationId, lockmode);
如果沒有指定索引名,ChooseIndexName根據規則生成索引名:
/* * Select name for index if caller didn't specify. */
indexRelationName = stmt->idxname;
if (indexRelationName == NULL) {
indexRelationName = ChooseIndexName(RelationGetRelationName(rel),
namespaceId,
indexColNames,
stmt->excludeOpNames,
stmt->primary,
stmt->isconstraint);
為index_create函式構造引數IndexInfo結構體:
/*
* Prepare arguments for index_create, primarily an IndexInfo structure.
* Note that ii_Predicate must be in implicit-AND format.
*/
indexInfo = makeNode(IndexInfo);
Index_create函式建立索引:
/*
* Make the catalog entries for the index, including constraints. Then, if
* not skip_build || concurrent, actually build the index.
*/
indexRelationId = index_create(rel,
……
關閉表並返回索引表id:
heap_close(rel, NoLock);
return indexRelationId;
Index_create函式
開啟系統表pg_class:
pg_class = heap_open(RelationRelationId, RowExclusiveLock);
heap_create建立relcache和索引物理檔案:
/* * create the index relation's relcache entry and physical disk file. (If * we fail further down, it's the smgr's responsibility to remove the disk * file again.) */
StorageType storage_type = RelationGetStorageType(heapRelation);
indexRelation = heap_create(indexRelationName, namespaceId, tableSpaceId, indexRelationId, relFileNode,
RELATION_CREATE_BUCKET(heapRelation) ? heapRelation->rd_bucketoid : InvalidOid, indexTupDesc, relKind,
relpersistence, isLocalPart, false, shared_relation, mapped_relation, allow_system_table_mods,
REL_CMPRS_NOT_SUPPORT, (Datum)reloptions, heapRelation->rd_rel->relowner, skip_create_storage,
isUstore ? TAM_USTORE : TAM_HEAP, /* XXX: Index tables are by default HEAP Table Type */
relindexsplit, storage_type, extra->crossBucket, accessMethodObjectId);
將索引表元資訊存入系統表pg_class:
/*
* store index's pg_class entry
*/
InsertPgClassTuple(
pg_class, indexRelation, RelationGetRelid(indexRelation), (Datum)0, reloptions, relKind, NULL);
/* done with pg_class */
heap_close(pg_class, RowExclusiveLock);
將索引表元資訊存入系統表pg_index:
UpdateIndexRelation(indexRelationId,
heapRelationId,
indexInfo,
……
Index_build建立索引:
} else if (extra && (!extra->isPartitionedIndex || extra->isGlobalPartitionedIndex)) {
/* support regular index or GLOBAL partition index */
index_build(heapRelation, NULL, indexRelation, NULL, indexInfo, isprimary, false, PARTITION_TYPE(extra));
}
index_build
index_build呼叫index_build_storage,如果建立的是btree索引最終呼叫btbuild,如果是hash索引最終呼叫hashbuild,如果是psort則最終呼叫psortbuild,更多索引訪問方法的資訊可檢視系統表pg_am。
stats = index_build_storage(targetHeapRelation, targetIndexRelation, indexInfo);
static IndexBuildResult* index_build_storage(Relation heapRelation, Relation indexRelation, IndexInfo* indexInfo)
{
RegProcedure procedure = indexRelation->rd_am->ambuild;
Assert(RegProcedureIsValid(procedure));
IndexBuildResult* stats = (IndexBuildResult*)DatumGetPointer(OidFunctionCall3(
procedure, PointerGetDatum(heapRelation), PointerGetDatum(indexRelation), PointerGetDatum(indexInfo)));
Assert(PointerIsValid(stats));
if (RELPERSISTENCE_UNLOGGED == heapRelation->rd_rel->relpersistence) {
index_build_init_fork(heapRelation, indexRelation);
}
return stats;
}
btree索引的procedure為btbuild。
#0 btbuild (fcinfo=0x7fb4f9c63920) at nbtree.cpp:63
#1 0x00000000011fc07d in OidFunctionCall3Coll (functionId=338, collation=0, arg1=140415366247440, arg2=140415366237480, arg3=140415402419864) at fmgr.cpp:1857
#2 0x0000000000c16b77 in index_build_storage (heapRelation=0x7fb50006b410, indexRelation=0x7fb500068d28, indexInfo=0x7fb5022ea698) at index.cpp:2475
#3 0x0000000000c18097 in index_build (heapRelation=0x7fb50006b410, heapPartition=0x0, indexRelation=0x7fb500068d28, indexPartition=0x0, indexInfo=0x7fb5022ea698, isPrimary=false, isreindex=false,
partitionType=INDEX_CREATE_NONE_PARTITION, parallel=true) at index.cpp:2834
以上函式呼叫棧如下:
#0 index_build_storage
#1 index_build
#2 index_create
#3 DefineIndex
#4 standard_ProcessUtility
#5 gsaudit_ProcessUtility_hook
#6 pgaudit_ProcessUtility
#7 hypo_utility_hook
#8 ProcessUtility
#9 PortalRunUtility
#10 PortalRun
#11 exec_simple_query
#12 PostgresMain
分割槽表索引
建立普通表索引語法如下:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [schema_name.]index_name ]
{ ON table_name [ USING method ] | [ USING method ] ON table_name }
({ { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] }[, …] )
[ index_option ]
[ WHERE predicate ];
建立分割槽表索引語法:
CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ]
{ ON table_name [ USING method ] | [ USING method ] ON table_name }
( { { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, …] )
[ LOCAL [ ( { PARTITION index_partition_name [ TABLESPACE index_partition_tablespace ] } [, …] ) ] | GLOBAL ]
[ index_option ]
兩者執行流程基本一致,分割槽表索引在DefineIndex中是遍歷每個分割槽呼叫partition_index_create。
openGauss: 一款高效能、高安全、高可靠的企業級開源關係型資料庫。
🍒如果您覺得博主的文章還不錯或者有幫助的話,請關注一下博主,如果三連點贊評論收藏就更好啦!謝謝各位大佬給予的支援!
- GaussDB資料型別轉換介紹
- 通過公網連線GaussDB資料庫例項
- GaussDB資料型別介紹
- 如何通過DAS連線GaussDB
- 企業級分散式資料庫 - GaussDB介紹
- GaussDB 資料庫實驗環境搭建指導
- Tableau連線openGauss實踐
- 以學校資料模型為例,掌握在DAS下使用GaussDB
- openGauss資料庫共享儲存特性簡介
- openGauss資料庫原始碼解析系列文章——備份恢復機制:openGauss增量備份技術(上)
- openGauss資料庫客戶端接入認證詳解
- Excel連線openGauss資料庫實操
- openGauss資料庫原始碼解析系列文章——備份恢復機制:openGauss全量備份技術
- 超市進銷存之openGauss資料庫的應用與實踐
- 在WPS表格裡製作連線到openGauss的實時重新整理報表
- openGauss資料庫PostGIS 安裝與使用
- openGauss中Schema賦權小試
- openGauss Cluster Manager RTO Test
- 【我和openGauss的故事】openGauss獲獎專案講解
- openGauss易知易會的幾個實用特性