当前位置：首页 > PHP实战中知识总结 > PgSQL - 四种索引类型解析

PHP实战中知识总结 / PgSQL - 四种索引类型解析

索引是对数据库表中的一列或者多列的数据进行排序的一种结构，使用索引可快速访问数据表中的特定信息。

pgsql提供了B-tree、Hash、Gin等索引类型，每种索引类型因采用不同的算法而适用于不同的查询场景。在未指定索引类型时，CREATE INDEX命令将创建B-tree 索引。

一、基本术语

（1）元组 - tuple，指的是行(包括heap tuple, index tuple等，都可以叫元组)。

（2）tid, ctid - 指行号(block_number, item pointer)，包括HEAP表的BLOCK NUMBER，以及在BLOCK中指向行的item pointer。

（3）键值 - 指索引结构中的KEY

（4）基表 - 创建索引的表

二、索引的扫描方式

（1）Indexscan：根据查询索引找到匹配记录的ctid，再通过ctid去查堆表把相应的数据读出来

（2）Bitmapscan：通过索引找到匹配记录的ctid集合，在内存中建一个bitmap对ctid做集合运算和排序等，最后再根据bitmap中的数据文件把对应数据读取出来。

（3）Seq Scan：顺序扫描也叫做全表扫描，数据库的一种扫描方式，跟索引没啥关系，当语句是这种扫描方式时，说明该查询语句并没有走索引。

（4）Index Only Scan：这种索引方式说明索引字段中包含了所有返回字段，对vm文件中全为可见的数据块，不查堆表直接返回索引中的值

VM文件是可见性映射文件，如果存在vm文件，则表示该数据块没有需要清理的行，即该表做了vacuum操作。vaccum命令时用来对表进行操作，回收已经删除元祖占据的存储空间

三、索引分类

1、从索引的类型上区分

（1）唯一索引：唯一索引不允许任何重复的值插入到表。格式为：

CREATE UNIQUE INDEX index_name on table_name (column_name)

（2）单列索引：只基于表的一个列上创建的索引。格式为：

CREATE INDEX index_name ON table_name (column_name);

（3）组合索引：基于表的多列上创建的索引。格式为：

CREATE INDEX index_name ON table_name (column1_name, column2_name)

（4）表达式索引：字段进行某种运算之后的结果创建索引、格式为：

CREATE INDEX index_name ON table_name(expression|column_name)

2、从索引的实现方法上区分

（1）B-tree：创建索引时未指定类型时，默认创建B-tree索引

（2）Hash：此索引类型只能处理简单的等值比较

（3）Gin：通用倒排索引，可以与pg_trgm扩展配合使用以实现字符串匹配的全文检索，即全模糊搜索

（4）Gist：通用的搜索树(Generalized Search Tree)。它是一种平衡树结构的访问方法，在系统中作为一个基本模版，可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现

索引类型	说明	适用场景	特性
B-tree	默认类型。使用btree索引，where条件必须包含第一个索引列	B-tree索引适合用于存储排序的数据，并支持=，>,>=，<,<=，以及between，in，is null等操作符和以字符串开头的模糊查询(前模糊)	1、在PostgreSQL13中，Btree索引新增了Deduplication特性，使得PG数据库有了新的方式去处理重复的索引键值，这大大减小了btree索引所占用的空间，提升了索引扫描的性能，deduplication涉及到底层索引存储结构的变更。在13版本之前，每一个重复的数据都会占用索引的一个叶子元组leaf，这些重复的key值在索引页面中重复存储，带来很大的空间浪费。通过deduplicate_items 参数开启（默认开启） 2、在PostgreSQL当前支持的索引类型中，只有B-tree可以产生排序的输出，当ORDER BY与LIMIT n组合：显式排序将必须处理所有数据以识别前n行，但如果存在与ORDER BY匹配的索引，则可以直接检索前n行，而不扫描其余部分。升序默认null值放在最后，可以使用NULLS FIRST和/或NULLS LAST选项来进行调整。 3、支持多列索引，最多可以指定32列，使用最左匹配原则。
Hash	此索引类型只能处理简单的等值比较	= 操作符	1、由于hash函数没有特定的排序规则，所以一般的hash索引只支持等值查。 2、hash索引也不会处理null值，所以不会标记null值。3、hash索引不存储索引键，只存储hash码，所以不会 index-only扫描，也不支持多列创建hash索引。4、从10版本开始，可以通过pageinspect插件查看hash索引的内部情况 `create extension pageinspect`。
Gin	通用倒排索引，是一个存储对(key, posting list)集合的索引结构，其中key是一个键值，而posting list 是一组出现过key的位置	1、多值类型(数组、全文检索、枚举、网络地址类型)：包含、相交。 2、JSON类型。 3、普通类型(通过btree_gin 插件支持)：与B-Tree类似。 4、字符串(通过pg_trgm 插件支持)：模糊查询、相似查询。 5、多列：任意列组合查询。	1、在表中的每一个属性，在建立索引时，都可能会被解析为多个键值。2、通过这种索引结构可以快速的查找到包含指定关键字的元组，因此GIN索引特别适用于多值类型的元素搜索，比如支持全文搜索，数组中元素的搜索。3、Gin索引创建耗时比btree索引长约10倍，对频繁更新的表字段不建议使用。
Gist	通用搜索树，一种平衡树结构的访问方法,在系统中作为一个基本模版,可以使用它实现任意索引模式。B-trees, R-trees和许多其它的索引模式都可以用GiST实现。	1、空间类型：方位（上、下、左、右），空间关系（相交、包含），空间距离排序(KNN)。 2、范围数据：=, &&, , >, -/-, & 3、普通类型：与B-Tree类似，增加空间类型类似操作符。 4、数组类型。 5、多列：任意列组合查询	1、可通过btree_gist扩展，支持多种数据类型的 B 树等效功能。 2、可通过cube扩展，支持多维立方体的索引。 3、可通过hstore扩展，支持存储键值对模块的索引。 4、可通过intarray扩展，支持一维 int4 值数组的 RD 树的索引。 5、可通过ltree扩展，支持树状结构的索引。 6、可通过pg_trgm扩展，支持全文检索，模糊搜索。 7、可通过seg扩展，支持“float ranges”的索引。