PostgreSQL技术内幕6：PostgreSQL索引技术

2024-09-02 05:20
文章标签 技术 postgresql 索引 内幕

本文主要是介绍PostgreSQL技术内幕6：PostgreSQL索引技术，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

    • 0. 简介
    • 1.PG索引类型介绍
    • 2. PG创建索引说明及索引属性查看
      • 2.1 创建说明
      • 2.2 查看方式
        • 2.2.1 查看PG默认支持的索引及对应的Handler类型
        • 2.2.2 查看B树索引属性
    • 3. 索引选择
      • 3.1 查看索引情况
    • 4.PG中B-Tree索引原理
      • 4.1 页存储结构
    • 5.索引代码分析
      • 5.1 不同索引结构解析
        • 5.1.1 索引的Handler结构
      • 5.2 BTree关键流程解析
      • 5.2.1 构造函数btbuild

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
在这里插入图片描述

这篇关于PostgreSQL技术内幕6：PostgreSQL索引技术的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1129107。

相关文章

性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
【专题】2024飞行汽车技术全景报告合集PDF分享（附原数据表）

【专题】2024飞行汽车技术全景报告合集PDF分享（附原数据表）

原文链接： https://tecdat.cn/?p=37628 6月16日，小鹏汇天旅航者X2在北京大兴国际机场临空经济区完成首飞，这也是小鹏汇天的产品在京津冀地区进行的首次飞行。小鹏汇天方面还表示，公司准备量产，并计划今年四季度开启预售小鹏汇天分体式飞行汽车，探索分体式飞行汽车城际通勤。阅读原文，获取专题报告合集全文，解锁文末271份飞行汽车相关行业研究报告。 据悉，业内人士对飞行汽车行业
阅读更多...
金融业开源技术 术语

金融业开源技术 术语

金融业开源技术  术语 1  范围 本文件界定了金融业开源技术的常用术语。 本文件适用于金融业中涉及开源技术的相关标准及规范性文件制定和信息沟通等活动。
阅读更多...
AI(文生语音)-TTS 技术线路探索学习:从拼接式参数化方法到Tacotron端到端输出

AI(文生语音)-TTS 技术线路探索学习:从拼接式参数化方法到Tacotron端到端输出

AI(文生语音)-TTS 技术线路探索学习:从拼接式参数化方法到Tacotron端到端输出 在数字化时代，文本到语音（Text-to-Speech, TTS）技术已成为人机交互的关键桥梁，无论是为视障人士提供辅助阅读，还是为智能助手注入声音的灵魂，TTS 技术都扮演着至关重要的角色。从最初的拼接式方法到参数化技术，再到现今的深度学习解决方案，TTS 技术经历了一段长足的进步。这篇文章将带您穿越时
阅读更多...
系统架构设计师： 信息安全技术

系统架构设计师： 信息安全技术

简简单单 Online zuozuo： 简简单单 Online zuozuo 简简单单 Online zuozuo 简简单单 Online zuozuo 简简单单 Online zuozuo ：本心、输入输出、结果 简简单单 Online zuozuo ： 文章目录 系统架构设计师： 信息安全技术前言信息安全的基本要素：信息安全的范围：安全措施的目标：访问控制技术要素：访问控制包括：等保
阅读更多...
前端技术(七)——less 教程

前端技术(七)——less 教程

一、less简介 1. less是什么？ less是一种动态样式语言，属于css预处理器的范畴，它扩展了CSS语言，增加了变量、Mixin、函数等特性，使CSS 更易维护和扩展LESS 既可以在 客户端 上运行 ，也可以借助Node.js在服务端运行。 less的中文官网:https://lesscss.cn/ 2. less编译工具 koala 官网 http://koala-app.
阅读更多...
PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL核心功能特性与使用领域及场景分析

PostgreSQL有什么优点？ 开源和免费 PostgreSQL是一个开源的数据库管理系统，可以免费使用和修改。这降低了企业的成本，并为开发者提供了一个活跃的社区和丰富的资源。 高度兼容 PostgreSQL支持多种操作系统（如Linux、Windows、macOS等）和编程语言（如C、C++、Java、Python、Ruby等），并提供了多种接口（如JDBC、ODBC、ADO.NET等
阅读更多...
Spring的设计⽬标——《Spring技术内幕》

Spring的设计⽬标——《Spring技术内幕》

读《Spring技术内幕》第二版，计文柯著。 如果我们要简要地描述Spring的设计⽬标，可以这么说，Spring为开发者提供的是⼀个⼀站式的轻量级应⽤开发框架（平台）。 作为平台，Spring抽象了我们在 许多应⽤开发中遇到的共性问题；同时，作为⼀个轻量级的应⽤开发框架，Spring和传统的J2EE开发相⽐，有其⾃⾝的特点。 通过这些⾃⾝的特点，Spring充分体现了它的设计理念：在
阅读更多...
java线程深度解析（六）——线程池技术

java线程深度解析（六）——线程池技术

http://blog.csdn.net/Daybreak1209/article/details/51382604 一种最为简单的线程创建和回收的方法： [html]  view plain copy new Thread(new Runnable(){                @Override               public voi
阅读更多...
java线程深度解析（二）——线程互斥技术与线程间通信

java线程深度解析（二）——线程互斥技术与线程间通信

http://blog.csdn.net/daybreak1209/article/details/51307679      在java多线程——线程同步问题中，对于多线程下程序启动时出现的线程安全问题的背景和初步解决方案已经有了详细的介绍。本文将再度深入解析对线程代码块和方法的同步控制和多线程间通信的实例。 一、再现多线程下安全问题 先看开启两条线程，分别按序打印字符串的
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2