Postgres10高性能开发（1）索引

落日映苍穹つ 2023-07-10 12:46 21阅读 0赞

# 数据结构 #

## BTree ##

*  Postgres默认的索引类型
 *  可用于等于比较和范围查询
 *  Postgres8.3起可支持查找null值
 *  like ‘XXX%’
    
     *  如果数据库lc\_collate值为C，支持索引查询
     *  如果数据库lc\_collate值不是C（Postgres10默认不是C），可通过建立text\_pattern\_ops索引参数支持索引查询，例如
        
            create index idx_xx on tableXX (xxFiled text_pattern_ops)
    
    > text\_pattern\_ops，varchar\_pattern\_ops和bpchar\_pattern\_ops运算符支持按字符比较，而不是默认语言排序规则比较。可以用于BTree索引，但只支持等于比较，不再支持范围查询
    
        #查询lc_collate方法
        show LC_COLLATE

# Hash #

*  只能用于等于比较，查询性能高于BTree，建索引时间比BTree长，空间占用低于BTree
 *  不支持范围查询
 *  不支持null比较
 *  10以前数据库宕机后hash索引容易崩溃，可能需要手工重建
 *  10以后，Hash索引记录到WAL，因此数据库宕机无影响并支持复制

## GIN ##

#### 结构 ####

GIN索引是一种广义倒排索引，从索引对象生成Key(1或多个)，并存储<key,List>集合

#### 支持的运算符 ####

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>被索引字段类型</th> 
   <th>支持的比较方法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>array_ops</td> 
   <td>anyarray</td> 
   <td>&amp;&amp;, &lt;@, =, @&gt;</td> 
  </tr> 
  <tr> 
   <td>jsonb_ops</td> 
   <td>jsonb</td> 
   <td>?, ?&amp;, ?|, @&gt;</td> 
  </tr> 
  <tr> 
   <td>jsonb_path_ops</td> 
   <td>jsonb</td> 
   <td>@&gt;</td> 
  </tr> 
  <tr> 
   <td>tsvector_ops</td> 
   <td>tsvector</td> 
   <td>@@, @@@</td> 
  </tr> 
 </tbody> 
</table>

#### btree\_gin插件 ####

支持对int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr和enum类型建立索引

#### 特点 ####

*  可用于所用数组类型
 *  可用于模拟Oracle的位图索引，用于字段数据重复度很高的情况
 *  不支持null查询

## GiST ##

广义搜索树，内部采用BTree结构，支持自主扩展

# 高级应用 #

## 多列索引 ##

create index idx_xx on tableA (filedA,filedB)

*  可用于select \* from tableA where filedA=‘A’
 *  可用于select \* from tableA where filedA=‘A’ and filedB=‘B’
 *  不可用于select \* from tableA where filedB=‘B’
 *  当查询同时使用filedA和filedB区分度高，但单独查询fieldA或filedB区分度低时，多列索引效率明细高于单列索引以及多个单列索引进行Bitmap And
    
    > 区分度：假设表tableA有10000数据，filedA有100个distinct值，则filedA区分度为1/100,区分度越高对该字段等于查询的索引过滤效果越好。唯一索引的区分度为1  
    > \-BTree，GiST，GIN和BRIN索引支持，Hash不支持

## 索引组合 ##

#### 索引Bitmap And ####

对于Where A=1 and B=1，如果A和B字段都有索引，数据库有可能对A和B都使用索引查询，然后将结果生成位图，进行and操作

*  如果单个索引区分度很高，解释计划可能直接使用单个索引，不用组合索引
 *  如果两个索引区分度不高，解释计划更可能用组合索引

#### 索引Bitmap Or ####

对于Where A=1 or B=1，如果A和B字段都有索引，数据库有可能对A和B都使用索引查询，然后将结果生成Bitmap，进行or操作

*  如果单个索引区分度很高，解释计划可能直接使用单个索引，不用组合索引
 *  如果两个索引区分度不高，解释计划更可能用索引组合

## 部分索引 ##

CREATE INDEX access_log_client_ip_ix ON access_log(client_ip）
    WHERE NOT(client_ip> inet'192.168.100.0'AND 
               client_ip <inet'192.168.100.255')

#### 排除区分度低的值 ####

如果某个字段的某些值出现频率很高（值区分度低），可以通过部分索引排除这些值。

*  对于区分度低的值走索引的效果很低，即使索引存在，解释计划可能也不会走索引
 *  使用部分索引只保留区分度高的值后，可以降低索引大小，提高索引查询效率

#### 过滤掉固定查询值 ####

如果查询总是附带某些固定条件，如 status = ‘Y’ and filedA=?,其中status = 'Y’条件是不变的，因此可以对filedA建立索引并排除其他status值

create index idx_xx on tableA (filedA) WHERE status = 'Y'

## like查询索引 ##

#### like ‘XX%’ ####

前文提到 like 'XX%'可以使用BTree索引

#### like ‘%XX%’ 和 like ‘XX%’ ####

可以使用pg\_trgm插件,使用gin\_trgm\_ops操作符建立gin索引

pg\_trgm将对文本类型字段分解为三元组，从字符串中提取三字母时忽略非单词字符（非字母数字）。在确定字符串中包含的三元组时，每个单词被认为有两个空格前缀，一个空格后缀生成组。例如’abc defg’字符串将生成" a"," d"," ab"," de",abc,"bc ",“def”,“efg”,"fg "\}

> 可通过函数show\_trgm查询字符串生成的三元组结果

> 参考http://www.postgres.cn/docs/9.4/pgtrgm.html

create extension pg_trgm;
    
    create index idx_xx on tableA using gin (filedA gin_trgm_ops);

查询中必须有连续的三个明确的值才能使用索引

*  支持 ‘AA%’，因为AA在最前面,三元组会在前面补空格，等同于’ AA’
 *  支持 ‘%AAA%’,满足三个连续值条件
 *  不支持 ‘%AA%’
 *  不支持 ‘%AA%AA%’
 *  支持 ‘%AA%AA’,因为最后的AA,三元组会在后面补空格，等同于’AA ’

##### 中文字符限制 #####

*  当lc\_collate为C时，无法对中文使用索引查询，因为无法正常生成三元组（Postgres9.5默认为C）

#### 少于3个连续字符的like '%XX%'查询 ####

使用函数，参照三元组思想，自己构造一元组和二元组

CREATE OR REPLACE FUNCTION "crm"."split2arry_v2"(text)
      RETURNS "pg_catalog"."_text" AS $BODY$
    
    declare 
        res text[]; 
        len int;
    begin 
        res := regexp_split_to_array($1,''); 
        len := length($1);
        for i in 1..len-1 loop 
           res := array_append(res, substring($1,i,2)); 
        end loop; 
        if len > 2 then
           for j in 1..len-2 loop 
    					res := array_append(res, substring($1,j,3)); 
           end loop; 
        end if;
       return res; 
    end;
    
    $BODY$
      LANGUAGE plpgsql IMMUTABLE STRICT
      COST 100

索引

CREATE INDEX "idx_split_arry_cn_v2" ON "crm"."crm_t_archive_person" USING gin (
      crm.split2arry_v2(cl_name_cn::text) COLLATE "pg_catalog"."default" "pg_catalog"."_text_ops"
    );

查询语句

select * from crm_t_archive_person where split2arry_v2(cl_name_cn) @> array['AA'];

## 表达式索引 ##

CREATE INDEX test1_lower_col1_idx ON test1（lower（col1））;

## 并发创建索引 ##

> https://www.postgresql.org/docs/10/sql-createindex.html\#SQL-CREATEINDEX-CONCURRENTLY  
> 默认创建索引时，索引将锁定表，不会阻塞select，但会阻塞表create、update和delete这些写入操作。Postgres提供了CONCURRENTLY关键字避免阻塞写入。

CREATE INDEX CONCURRENTLY "idx_split_arry_cn_v2" ON "crm"."crm_t_archive_person" USING gin (
      crm.split2arry_v2(cl_name_cn::text) COLLATE "pg_catalog"."default" "pg_catalog"."_text_ops"
    );

*  使用CONCURRENTLY后，创建索引时会对表扫描两次，每次扫描前需要等待该表上已有的写入事物完成，索引建立速度会降低。
 *  最差的情况，索引建立可能会失败，索引处于invalid状态（索引将无法正常使用，但仍旧会在字段修改时生成索引数据）因此建立后需要检查索引状态。
    
        select indisvalid,indislive from pg_index  where indexrelid='idx_split_arry_cn_v2'::regclass
    
    如果索引建立失败，需要删除重建，或者使用REINDEX（阻塞写入）
 *  并发构件唯一索引时，在第二次表扫描前，已经启用了唯一约束，因此可能索引建立失败，但唯一约束依旧存在。