背景

postgresql拥有众多开放特性,例如

  1. 开放的数据类型接口,使得pg支持超级丰富的数据类型,除了传统数据库支持的类型,还支持gis,json,range,ip,isbn,图像特征值,化学,dna等等扩展的类型,用户还可以根据实际业务扩展更多的类型。
  2. 开放的操作符接口,使得pg不仅仅支持常见的类型操作符,还支持扩展的操作符,例如 距离符,逻辑并、交、差符号,图像相似符号,几何计算符号等等扩展的符号,用户还可以根据实际业务扩展更多的操作符。
  3. 开放的外部数据源接口,使得pg支持丰富的外部数据源,例如可以通过fdw读写mysql,redis,mongo,oracle,sqlserver,hive,www,hbase,ldap,等等只要你能想到的数据源都可以通过fdw接口读写。
  4. 开放的语言接口,使得pg支持几乎地球上所有的编程语言作为数据库的函数、存储过程语言,例如plpython,plperl,pljava,plr,plcuda,plshell等等。用户可以通过language handler扩展pg的语言支持。
  5. 开放的索引接口,使得pg支持非常丰富的索引方法,例如btree,hash,gin,gist,sp-gist,brin,bloom,rum,zombodb,bitmap(greenplum extend),用户可以根据不同的数据类型,以及查询的场景,选择不同的索引。
  6. pg内部还支持bitmapand,bitmapor的优化方法,可以合并多个索引的扫描操作,从而提升多个索引数据访问的效率。

概述

btree

postgresql b-tree是一种变种(high-concurrency b-tree management algorithm)。

btree适合所有的数据类型,支持排序,支持大于、小于、等于、大于或等于、小于或等于的搜索。索引与递归查询结合还能实现快速的稀疏检索。

hash

hash索引存储的是被索引字段value的哈希值,只支持等值查询。hash索引特别适用于字段value非常长(不适合b-tree索引,因为b-tree一个page至少要存储3个entry,所以不支持特别长的value)的场景,例如很长的字符串,并且用户只需要等值搜索,建议使用hash index。

gin

gin是倒排索引,存储被索引字段的value或value的元素,以及行号的list或tree。

  • 当需要搜索多值类型内的value时,适合多值类型,例如数组、全文检索、token。(根据不同的类型,支持相交、包含、大于、在左边、在右边等搜索)。
  • 当用户的数据比较稀疏时,如果要搜索某个value的值,可以适应btree_gin支持普通btree支持的类型。(支持btree的操作符)。
  • 当用户需要按任意列进行搜索时,gin支持多列展开单独建立索引域,同时支持内部多域索引的bitmapand,bitmapor合并,快速的返回按任意列搜索请求的数据。

gist

gist是一个通用的索引接口,可以使用gist实现b-tree,r-tree等索引结构。不同的类型,支持的索引检索也各不一样。

  • 几何类型,支持位置搜索(包含、相交、在上下左右等),按距离排序。
  • 范围类型,支持位置搜索(包含、相交、在左右等)。
  • ip类型,支持位置搜索(包含、相交、在左右等)。
  • 空间类型(postgis),支持位置搜索(包含、相交、在上下左右等),按距离排序。
  • 标量类型,支持按距离排序。

sp-gist

sp-gist类似gist,是一个通用的索引接口,但是sp-gist使用了空间分区的方法,使得sp-gist可以更好的支持非平衡数据结构,例如quad-trees,k-d tree,radis tree。

  • 几何类型,支持位置搜索(包含、相交、在上下左右等),按距离排序。
  • 范围类型,支持位置搜索(包含、相交、在左右等)。
  • ip类型,支持位置搜索(包含、相交、在左右等)。

brin

brin索引是块级索引,有别于b-tree等索引,brin记录并不是以行号为单位记录索引明细,而是记录每个数据块或者每段连续的数据块的统计信息。因此brin索引空间占用特别的小,对数据写入、更新、删除的影响也很小。 brin属于lossly索引,当被索引列的值与物理存储相关性很强时,brin索引的效果非常的好。例如时序数据,在时间或序列字段创建brin索引,进行等值、范围查询时效果很棒。

rum

rum是一个索引插件,由postgrespro开源,适合全文检索,属于gin的增强版本。

  • 在rum索引中,存储了lexem的位置信息,所以在计算ranking时,不需要回表查询(而gin需要回表查询)。
  • rum支持phrase搜索,而gin无法支持。
  • 在一个rum索引中,允许用户在posting tree中存储除ctid(行号)以外的字段value,例如时间戳。

这使得rum不仅支持gin支持的全文检索,还支持计算文本的相似度值,按相似度排序等。同时支持位置匹配,例如(速度与激情,可以采用”速度” <2> “激情” 进行匹配,而gin索引则无法做到)。

bloom

bloom索引接口是postgresql基于bloom filter构造的一个索引接口,属于lossy索引,可以收敛结果集(排除绝对不满足条件的结果,剩余的结果里再挑选满足条件的结果),因此需要二次check,bloom支持任意列组合的等值查询。 bloom存储的是签名,签名越大,耗费的空间越多,但是排除更加精准,有利有弊。

zombodb

zombodb是postgresql与elasticsearch结合的一个索引接口,可以直接读写es。

bitmap

bitmap索引是greenplum的索引接口,类似gin倒排,只是bitmap的key是列的值,value是bit(每个bit对应一行),而不是行号list或tree。

当某个字段的唯一值个数在100到10万之间(超出这个范围,不建议使用bitmap)时,如果表的记录数特别多,而且变更不频繁(或者是ao表),那么很适合bitmap索引,bitmap索引可以实现快速的多个或单个value的搜索。 因为只需要对行号的bitmap进行bit与或运算,得到最终的bitmap,从最终的bitmap映射到行进行提取。bitmap与btree一样,都支持等于,大于,小于,大于等于,小于等于的查询。

varbitx

varbitx是阿里云rds的扩展包,丰富bit类型的函数接口,实际上并不是索引接口,但是在postgresql中使用varbitx可以代替bitmap索引,达到同样的效果。

部分索引

postgresql允许用户创建部分索引,create index后面可以跟where子句,限制建索引的条件。

表达式索引

表达式索引也是postgresql特有的特性,例如用户的数据需要转换后查询,例如某些设备上传的地理坐标的坐标系不符合国标,需要转换为国内的空间坐标来查询。 那么可以针对这类字段,创建表达式索引,将转换过程放到表达式中,查询时也使用表达式进行查询。

记录对慢查询sql的优化

  1. 表a只创建记录并读取,不会有任何更新操作。业务目的,基于自增idcreated_at均会随着时间的流逝而变大并且不可逆(人为操作除外)。
    • 优化方案:order by created_at desc = order by id desc。主键索引比时间(块)索引快得多。
  2. 表b原始查询语句,套太多层,并且能大量过滤数据的时间过滤条件放在了最外层。时间是块状索引,每次过滤可以排除大量数据,应该被先使用。
    • 优化方案:时间字段放入内部子查询
  3. 在某些表的索引创建时,考虑了耗空间之类。所以在时间索引等方面,选择了brin索引,查询效率不如btree。
    • 优化方案:使用btree索引
  4. 查询报错示例:user query might have needed to see row versions that must be removed,这是因为在查询热备库数据时,主库有更新。并且在一定时限内,有冲突的查询未能查询完毕。
    • 优化方案:从热备库统计数据看,从19.5 以来冲突不多,并且上次数据库出问题,导致了很大一部分这个错误。故优化里可以如此设置: max_standby_archive_delay = -1max_standby_streaming_delay = -1。让有冲突的查询查完。(正常情况下没啥副作用,除非库挂了)
  5. 数据端使用sql时,没使用任何框架,没有连接池。读数据是通过resultset.next游标。通常在数据量特别大、并且在循环里添加了业务的时候,游标读有额外开销。
    • 优化方案:修改读取程序,在读取数据库数据后,先循环mapping成对象,然后对对象进行操作。以此缩短事务,减轻数据库压力。