PostgreSQL 10.1 手册_部分 II. SQL 语言_第 11 章 索引_11.2. 索引类型

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 11.2. 索引类型 PostgreSQL提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。

11.2. 索引类型

PostgreSQL提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下, CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

B-tree可以在可排序数据上的处理等值和范围查询。特别地,PostgreSQL的查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引:

<
<=
=
>=
>

将这些操作符组合起来,例如BETWEENIN,也可以用B-tree索引搜索实现。同样,在索引列上的IS NULLIS NOT NULL条件也可以在B-tree索引中使用。

优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE~ 的查询,前提是如果模式是一个常量且被固定在字符串的开头—例如:col LIKE 'foo%'或者col ~ '^foo', 但在col LIKE '%bar'上则不会。但是,如果我们的数据库没有使用C区域设置,我们需要创建一个具有特殊操作符类的索引来支持模式匹配查询,参见下面的第 11.9 节。同样可以将B-tree索引用于ILIKE~*,但仅当模式以非字母字符开始,即不受大小写转换影响的字符。

B-tree索引也可以用于检索排序数据。这并不会总是比简单扫描和排序更快,但是总是有用的。

Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较时,查询规划器将考虑使用一个Hash索引。下面的命令将创建一个Hash索引:

CREATE INDEX name ON table USING HASH (column);

GiST索引并不是一种单独的索引,而是可以用于实现很多不同索引策略的基础设施。相应地,可以使用一个GiST索引的特定操作符根据索引策略(操作符类)而变化。作为一个例子,PostgreSQL的标准捐献包中包括了用于多种二维几何数据类型的GiST操作符类,它用来支持使用下列操作符的索引化查询:

<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&

(这些操作符的含义见第 9.11 节表 62.1中给出了标准发布中所包括的 GiST 操作符类。contrib集合中还包括了很多其他GiST操作符类,可见第 62 章

GiST索引也有能力优化最近邻搜索,例如:

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

它将找到离给定目标点最近的10个位置。能够支持这种查询的能力同样取决于被使用的特定操作符类。 在表 62.1中,Ordering Operators列中列出了可以在这种方法中使用的操作符。

和GiST相似,SP-GiST索引为支持多种搜索提供了一种基础结构。SP-GiST 允许实现众多不同的非平衡的基于磁盘的数据结构,例如四叉树、k-d树和radix树。作为一个例 子,PostgreSQL的标准捐献包中包含了一个用于二维点的SP-GiST操作符类,它用于支持使用下列操作符的索引化查询:

<<
>>
~=
<@
<^
>^

(其含义见第 9.11 节表 63.1中给出了标准发布中所包括的 SP-GiST 操作符类。更多信息参见第 63 章

GIN 索引是倒排索引,它适合于包含多个组成值的数据值,例如数组。倒排索引中为每一个组成值都包含一个单独的项,它可以高效地处理测试指定组成值是否存在的查询。

与 GiST 和 SP-GiST相似, GIN可以支持多种不同的用户定义的索引策略和特定操作符,通过它一个GIN索引可以被根据索引策略被使用。作为一个例子,PostgreSQL的标准贡献包中包含了一个用于数组的GIN操作符类,它用于支持使用下列操作符的索引化查询:

<@
@>
=
&&

(这些操作符的含义见第 9.18 节表 64.1中给出了标准发布中所包括的 GIN 操作符类。在contrib集合中还有更多其他GIN操作符类,更多信息参见第 64 章

BRIN 索引(块范围索引的缩写)存储有关存放在一个表的连续物理块范围上的值摘要信息。与 GiST、SP-GiST 和 GIN 相似,BRIN可以支持很多种不同的索引策略,并且可以与一个 BRIN 索引配合使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型,被索引的数据对应于每个块范围的列中值的最小值和最大值,使用这些操作符来支持用到索引的查询:

<
<=
=
>=
>

包括在标准发布中的 BRIN 操作符类的文档在表 65.1中。更多信息请见第 65 章

本文转自PostgreSQL中文社区,原文链接:11.2. 索引类型

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
3天前
|
SQL 存储 数据库
SQL 撤销索引、撤销表以及撤销数据库
SQL 撤销索引、撤销表以及撤销数据库
15 4
|
2天前
|
SQL 存储 关系型数据库
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
MySQL数据库进阶第二篇(索引,SQL性能分析,使用规则)
|
14天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(4)-SQL性能分析-profile详情、explain(profile查看指令,explain执行计划中各个字段的含义)
MySQL数据库——索引(4)-SQL性能分析-profile详情、explain(profile查看指令,explain执行计划中各个字段的含义)
15 2
|
14天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
MySQL数据库——索引(3)-索引语法(创建索引、查看索引、删除索引、案例演示),SQL性能分析(SQL执行频率,慢查询日志)
17 2
|
6天前
|
SQL 关系型数据库 PostgreSQL
【sql】PostgreSQL物化视图表使用案例
【sql】PostgreSQL物化视图表使用案例
11 0
|
14天前
|
SQL 关系型数据库 MySQL
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
MySQL数据库——索引(5)-索引使用(上),验证索引效率、最左前缀法则、范围查询、索引失效情况、SQL提示
17 0
|
11月前
|
SQL Cloud Native 关系型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
ADBPG(AnalyticDB for PostgreSQL)是阿里云提供的一种云原生的大数据分析型数据库
897 1
|
11月前
|
数据可视化 关系型数据库 MySQL
将 PostgreSQL 迁移到 MySQL 数据库
将 PostgreSQL 迁移到 MySQL 数据库
1249 2
|
10月前
|
SQL 存储 自然语言处理
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
在当今社交媒体的时代,人们通过各种平台分享自己的生活、观点和情感。然而,对于平台管理员和品牌经营者来说,了解用户的情感和意见变得至关重要。为了帮助他们更好地了解用户的情感倾向,我们可以使用PostgreSQL中的pg_jieba插件对这些发帖进行分词和情感分析,来构建一个社交媒体情感分析系统,系统将根据用户的发帖内容,自动判断其情感倾向是积极、消极还是中性,并将结果存储在数据库中。
玩转阿里云RDS PostgreSQL数据库通过pg_jieba插件进行分词
|
10月前
|
关系型数据库 测试技术 分布式数据库
PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理. 如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等. 本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.
647 4