文章目录
0. 简介
本文主要介绍PG的索引技术,包含PG支持的索引类型,语法,查看方式,以及其中B-Tree索引的原理解析和源码解读。
1.PG索引类型介绍
PG支持多种索引类型:B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。不同的索引类型使用不同的算法来适应不同类型的查询,下面是其具体适用情况:
1)B-tree索引:是一种自平衡树,支持O(logn)的插入,删除,访问。
2)Hash索引:通过hash运算查找,只支持等于查找,不支持范围。
3)Gist索引:Gist是通用搜索树(generalized search tree)的缩写,和之前介绍的btree类似(一种平衡树)。但是它和btree不同的是,btree索引常常用来进行例如大于、小于、等于这些操作中,而在实际生活中很多数据其实不适用这种场景,例如地理数据、图像等等。因为Gist索引允许定义规则来将任意类型的数据分布到一个平衡的树中,并且允许定义一个方法使用此表示形式来让某些运算符访问。例如,对于空间数据,GiST索引可以使用 R树,以支持相对位置运算符(位于左侧,右侧,包含等),而对于树形图,R树可以支持相交或包含运算符。
4)SP-GiST索引:SP-GiST 代表空间分区 GiST,主要用于 GIS、多媒体、电话路由以及 IP 路由等数据的索引。
5)GIN索引: 倒排索引,主要用于搜索特定值是不是存在。
6)BRIN索引:BRIN 代表块区间索引(block range indexes),存储了连续物理范围区间内的数据摘要信息。BRIN 也相比 B-树索引要小很多,维护也更容易。对于不进行水平分区就无法使用 B-树索引的超大型表,可以考虑 BRIN。
2. PG创建索引说明及索引属性查看
2.1 创建说明
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
主要参数说明:
UNIQUE:唯一索引,创建索引的列数据不能重复。
CONNCURRENTLY:构建索引时不会阻塞该表正在进行的插入,更新,删除。
METHOD:要使用的索引方法,如btree,hash等。
ASC:升序。
DESC:降序。
2.2 查看方式
2.2.1 查看PG默认支持的索引及对应的Handler类型
select * , obj_description(oid,'pg_am') from pg_am order by 1;
2.2.2 查看B树索引属性
select a.amname, p.name, pg_indexam_has_property(a.oid, p.name) from pg_am a, unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name) where a.amname='btree' order by a.amname;
3. 索引选择
索引选择可以分两步进行考虑:1.是否建立索引:主要考虑索引的资源占用,对插入和更新的影响以及备份恢复的影响;2.索引类型选择:考虑创建索引以及使用查询的速度,索引大小,索引支持的类型等。
3.1 查看索引情况
1)查看索引
\di
2)查看所有和磁盘占用
select relname, pg_size_pretty(pg_relation_size(oid)) finsertrom pg_class where relname like 't\_%' or relname='t1' order by relname;
3)查看索引支持的类型
select opfname from pg_opfamily, pg_am where opfmethod = pg_am.oid and amname='btree' order by 1;
4)查看所有支持的操作符
select amop.amopopr::regoperator as opfamily_operator, amop.amopstrategy from pg_am am, pg_opfamily opf, pg_amop amop where opf.opfmethod = am.oid and amop.amopfamily =opf.oid and am.amname='btree' and opf.opfname='bool_ops' order by amopstrategy;
4.PG中B-Tree索引原理
PG中的BTree来源于论文《Efficient locking for concurrent operations on B-trees》,论文中是一种B+树的变形,增加了非叶子节点的右侧的连接,同时引入了引入了“High Key”(下述HK)用于描述当前节点子节点的最大值,PG在此基础上,增加了左侧兄弟节点的连接,对于并发更加友好(并发控制在后续并发控制章节介绍),其结构和特点如图:
1)树是平衡的
2)支持范围和等值查询以及排序操作
3)是多分支的,深度不会太深,大表4-5层就足够
4)双向互联,可以内部遍历,不需要回到根节点
4.1 页存储结构
PG的索引存储结构和其他页面存储结构一致:
linp用于索引itup,其存储了每个itup在页面中的实际位置。根据PostgreSQL中对BTree索引结构的描述,分为当前节点是否是最右节点两种类型。由于非最右节点需要一个字段来保存HK,故当对一个页面进行填充时,存在着以下两种方式:
(1)当前节点为非最右节点
1.将首先将itup3(最大的索引元组)复制到当前节点的右兄弟节点,然后将linp0指向itup3(HK)。
2.去掉linp3。使用linp0来指向页面中的HK。
(2)当前节点为最右节点
最右节点不需要HK,所以每个linp减一,linp3不需要使用
整体结构
(1)对于非叶子节点,itup指向下一个节点,而对于叶子节点,itup指向实际物理存储的位置。
(2)Special space中,实现了两个指针,分配用于指向左右兄弟节点。
(3)根据BTree的特性,索引元组为有序,第一个叶子节点中itup3实际为最大索引元组,即HK,第二个叶子节点中itup1实际为最小索引元组,两者相同,故指向了同一物理存储位置。
5.索引代码分析
5.1 不同索引结构解析
5.1.1 索引的Handler结构
每种索引会初始化不同的handler,定义其属性和行为,如创建时的操作,插入时的操作,新加一种索引可以定义不同的hanlder,这也体现了PG的良好的可扩展性。
typedef struct IndexAmRoutine
{
NodeTag type;
/*
* Total number of strategies (operators) by which we can traverse/search
* this AM. Zero if AM does not have a fixed set of strategy assignments.
*/
uint16 amstrategies;
/* total number of support functions that this AM uses */
uint16 amsupport;
/* opclass options support function number or 0 */
uint16 amoptsprocnum;
/* does AM support ORDER BY indexed column's value? */
bool amcanorder;
/* does AM support ORDER BY result of an operator on indexed column? */
bool amcanorderbyop;
/* does AM support backward scanning? */
bool amcanbackward;
/* does AM support UNIQUE indexes? */
bool amcanunique;
/* does AM support multi-column indexes? */
bool amcanmulticol;
/* does AM require scans to have a constraint on the first index column? */
bool amoptionalkey;
/* does AM handle ScalarArrayOpExpr quals? */
bool amsearcharray;
/* does AM handle IS NULL/IS NOT NULL quals? */
bool amsearchnulls;
/* can index storage data type differ from column data type? */
bool amstorage;
/* can an index of this type be clustered on? */
bool amclusterable;
/* does AM handle predicate locks? */
bool ampredlocks;
/* does AM support parallel scan? */
bool amcanparallel;
/* does AM support parallel build? */
bool amcanbuildparallel;
/* does AM support columns included with clause INCLUDE? */
bool amcaninclude;
/* does AM use maintenance_work_mem? */
bool amusemaintenanceworkmem;
/* does AM store tuple information only at block granularity? */
bool amsummarizing;
/* OR of parallel vacuum flags. See vacuum.h for flags. */
uint8 amparallelvacuumoptions;
/* type of data stored in index, or InvalidOid if variable */
Oid amkeytype;
/*
* If you add new properties to either the above or the below lists, then
* they should also (usually) be exposed via the property API (see
* IndexAMProperty at the top of the file, and utils/adt/amutils.c).
*/
/* interface functions */
ambuild_function ambuild;
ambuildempty_function ambuildempty;
aminsert_function aminsert;
aminsertcleanup_function aminsertcleanup;
ambulkdelete_function ambulkdelete;
amvacuumcleanup_function amvacuumcleanup;
amcanreturn_function amcanreturn; /* can be NULL */
amcostestimate_function amcostestimate;
amoptions_function amoptions;
amproperty_function amproperty; /* can be NULL */
ambuildphasename_function ambuildphasename; /* can be NULL */
amvalidate_function amvalidate;
amadjustmembers_function amadjustmembers; /* can be NULL */
ambeginscan_function ambeginscan;
amrescan_function amrescan;
amgettuple_function amgettuple; /* can be NULL */
amgetbitmap_function amgetbitmap; /* can be NULL */
amendscan_function amendscan;
ammarkpos_function ammarkpos; /* can be NULL */
amrestrpos_function amrestrpos; /* can be NULL */
/* interface functions to support parallel index scans */
amestimateparallelscan_function amestimateparallelscan; /* can be NULL */
aminitparallelscan_function aminitparallelscan; /* can be NULL */
amparallelrescan_function amparallelrescan; /* can be NULL */
} IndexAmRoutine;
下面简单看btree的handler初始化
Datum
bthandler(PG_FUNCTION_ARGS)
{
IndexAmRoutine *amroutine = makeNode(IndexAmRoutine);
amroutine->amstrategies = BTMaxStrategyNumber;
amroutine->amsupport = BTNProcs;
amroutine->amoptsprocnum = BTOPTIONS_PROC;
amroutine->amcanorder = true;
amroutine->amcanorderbyop = false;
amroutine->amcanbackward = true;
amroutine->amcanunique = true;
amroutine->amcanmulticol = true;
amroutine->amoptionalkey = true;
amroutine->amsearcharray = true;
amroutine->amsearchnulls = true;
amroutine->amstorage = false;
amroutine->amclusterable = true;
amroutine->ampredlocks = true;
amroutine->amcanparallel = true;
amroutine->amcanbuildparallel = true;
amroutine->amcaninclude = true;
amroutine->amusemaintenanceworkmem = false;
amroutine->amsummarizing = false;
amroutine->amparallelvacuumoptions =
VACUUM_OPTION_PARALLEL_BULKDEL | VACUUM_OPTION_PARALLEL_COND_CLEANUP;
amroutine->amkeytype = InvalidOid;
amroutine->ambuild = btbuild;
amroutine->ambuildempty = btbuildempty;
amroutine->aminsert = btinsert;
amroutine->aminsertcleanup = NULL;
amroutine->ambulkdelete = btbulkdelete;
amroutine->amvacuumcleanup = btvacuumcleanup;
amroutine->amcanreturn = btcanreturn;
amroutine->amcostestimate = btcostestimate;
amroutine->amoptions = btoptions;
amroutine->amproperty = btproperty;
amroutine->ambuildphasename = btbuildphasename;
amroutine->amvalidate = btvalidate;
amroutine->amadjustmembers = btadjustmembers;
amroutine->ambeginscan = btbeginscan;
amroutine->amrescan = btrescan;
amroutine->amgettuple = btgettuple;
amroutine->amgetbitmap = btgetbitmap;
amroutine->amendscan = btendscan;
amroutine->ammarkpos = btmarkpos;
amroutine->amrestrpos = btrestrpos;
amroutine->amestimateparallelscan = btestimateparallelscan;
amroutine->aminitparallelscan = btinitparallelscan;
amroutine->amparallelrescan = btparallelrescan;
PG_RETURN_POINTER(amroutine);
}
对于不同索引对应的函数和属性在系统初始化时,创建到pg_am、pg_opfamily等系统表中
# Index access method handlers
{ oid => '330', descr => 'btree index access method handler',
proname => 'bthandler', provolatile => 'v', prorettype => 'index_am_handler',
proargtypes => 'internal', prosrc => 'bthandler' },
{ oid => '331', descr => 'hash index access method handler',
proname => 'hashhandler', provolatile => 'v',
prorettype => 'index_am_handler', proargtypes => 'internal',
prosrc => 'hashhandler' },
{ oid => '332', descr => 'gist index access method handler',
proname => 'gisthandler', provolatile => 'v',
prorettype => 'index_am_handler', proargtypes => 'internal',
prosrc => 'gisthandler' },
5.2 BTree关键流程解析
5.2.1 构造函数btbuild
5.2.2 插入流程btinsert