PostgreSQL 14种索引的原理和应用场景
2018-11-20 本文已影响38人
成功的失败者
一、btree
- 原理
《深入浅出PostgreSQL B-Tree索引结构》 - 应用场景
b-tree适合所有的数据类型，支持排序，支持大于、小于、等于、大于或等于、小于或等于的搜索。
索引与递归查询结合，还能实现快速的稀疏检索。
二、hash
- 原理
src/backend/access/hash/README
（hash index entries store only the hash code, not the actual data value, for each indexed item. ） - 应用场景
hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。
hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index
三、gin
- 原理
gin是倒排索引，存储被索引字段的VALUE或VALUE的元素，以及行号的list或tree。 - 应用场景
1、当需要搜索多值类型内的VALUE时，适合多值类型，例如数组、全文检索、TOKEN。（根据不同的类型，支持相交、包含、大于、在左边、在右边等搜索）
2、当用户的数据比较稀疏时，如果要搜索某个VALUE的值，可以适应btree_gin支持普通btree支持的类型。（支持btree的操作符）
3、当用户需要按任意列进行搜索时，gin支持多列展开单独建立索引域，同时支持内部多域索引的bitmapAnd, bitmapOr合并，快速的返回按任意列搜索请求的数据。
四、gist
- 原理
GiST stands for Generalized Search Tree.
It is a balanced, tree-structured access method, that acts as a base template in which to implement arbitrary indexing schemes.
B-trees, R-trees and many other indexing schemes can be implemented in GiST. - 应用场景
GiST是一个通用的索引接口，可以使用GiST实现b-tree, r-tree等索引结构。
不同的类型，支持的索引检索也各不一样。例如：
1、几何类型，支持位置搜索（包含、相交、在上下左右等），按距离排序。
2、范围类型，支持位置搜索（包含、相交、在左右等）。
3、IP类型，支持位置搜索（包含、相交、在左右等）。
4、空间类型（PostGIS），支持位置搜索（包含、相交、在上下左右等），按距离排序。
5、标量类型，支持按距离排序。
五、sp-gist
- 原理
SP-GiST类似GiST，是一个通用的索引接口，但是SP-GIST使用了空间分区的方法，使得SP-GiST可以更好的支持非平衡数据结构，例如quad-trees, k-d tree, radis tree. - 应用场景
1、几何类型，支持位置搜索（包含、相交、在上下左右等），按距离排序。
2、范围类型，支持位置搜索（包含、相交、在左右等）。
3、IP类型，支持位置搜索（包含、相交、在左右等）。
六、brin
- 原理
BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。
BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果非常的好。
例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很棒。 - 应用场景
《PostgreSQL 聚集存储 与 BRIN索引 - 高并发行为、轨迹类大吞吐数据查询场景解说》
七、rum
- 原理
https://github.com/postgrespro/rum
rum 是一个索引插件，由Postgrespro开源，适合全文检索，属于GIN的增强版本。
增强包括：
1、在RUM索引中，存储了lexem的位置信息，所以在计算ranking时，不需要回表查询（而GIN需要回表查询）。
2、RUM支持phrase搜索，而GIN无法支持。
3、在一个RUM索引中，允许用户在posting tree中存储除ctid（行号）以外的字段VALUE，例如时间戳。
这使得RUM不仅支持GIN支持的全文检索，还支持计算文本的相似度值，按相似度排序等。同时支持位置匹配，例如（速度与激情，可以采用"速度" <2> "激情" 进行匹配，而GIN索引则无法做到） - 应用场景
《PostgreSQL 全文检索加速 快到没有朋友 - RUM索引接口(潘多拉魔盒)》
八、bloom
- 原理
bloom索引接口是PostgreSQL基于bloom filter构造的一个索引接口，属于lossy索引，可以收敛结果集(排除绝对不满足条件的结果，剩余的结果里再挑选满足条件的结果)，因此需要二次check，bloom支持任意列组合的等值查询。
bloom存储的是签名，签名越大，耗费的空间越多，但是排除更加精准。有利有弊。 - 应用场景
bloom索引适合多列任意组合查询
《PostgreSQL 9.6 黑科技 bloom 算法索引，一个索引支撑任意列组合查询》
九、zombodb
- 原理
zombodb是PostgreSQL与ElasticSearch结合的一个索引接口，可以直接读写ES。
https://github.com/zombodb/zombodb - 应用场景
与ES结合，实现SQL接口的搜索引擎，实现数据的透明搜索。
十、bitmap
- 原理
bitmap索引是Greenplum的索引接口，类似GIN倒排，只是bitmap的KEY是列的值，VALUE是BIT（每个BIT对应一行），而不是行号list或tree。
《Greenplum 最佳实践 - 什么时候选择bitmap索引》 - 应用场景
当某个字段的唯一值个数在100到10万之间(超出这个范围，不建议使用bitmap)时，如果表的记录数特别多，而且变更不频繁（或者是AO表），那么很适合BITMAP索引，bitmap索引可以实现快速的多个或单个VALUE的搜索。因为只需要对行号的BITMAP进行BIT与或运算，得到最终的BITMAP，从最终的BITMAP映射到行进行提取。
bitmap与btree一样，都支持 等于，大于，小于，大于等于，小于等于的查询。
十一、varbitx
- 原理
varbitx是阿里云RDS的扩展包，丰富bit类型的函数接口，实际上并不是索引接口，但是在PostgreSQL中使用varbitx可以代替bitmap索引，达到同样的效果。 - 应用场景
《基于 阿里云 RDS PostgreSQL 打造实时用户画像推荐系统》
十二、部分索引
- 应用场景
PostgreSQL允许用户创建部分索引，例如业务上只关心激活用户，所以可以只对激活用户创建索引。
十三、表达式索引
- 应用场景
表达式索引也是PostgreSQL特有的特性，例如用户的数据需要转换后查询，例如某些设备上传的地理坐标的坐标系不符合国标，需要转换为国内的空间坐标来查询。
那么可以针对这类字段，创建表达式索引，将转换过程放到表达式中，查询时也使用表达式进行查询。
十四、内部窥视索引存储
- 应用场景
通过pageinspect插件，可以读取索引页的内容。
