【技术文章】浅析PostgreSQL索引（1）

1、索引概述

• 系统会在表更新时更新索引，而且会在它觉得使用索引比顺序扫描表效率更高时使用索引

• 我们可能需要定期地运行ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定

• 索引也会使带有搜索条件的UPDATE和DELETE命令受益；此外索引还可以在连接搜索中使用

• 并发构建索引允许并行的写，但是有些警告需要注意

• 一个索引被创建后，系统必须保持它与表同步。这增加了数据操作的负担

• 那些很少或从不在查询中使用的索引应该被移除。

2、索引类型

• PostgreSQL提供了多种索引类型，主要的四种：B-Tree、Hash、GiST和GIN

• 每种索引使用了不同的算法，都有其适合的查询类型

• 缺省时，CREATE INDEX命令将创建B-Tree索引

CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

CREATE INDEX name ON table USING hash (column);

<< --严格在左侧，例如circle’((0,0),1)’ << circle’((5,0),1)’ &< --表示左边的平面体不会扩展到超过右边的平面体的右边. 例如box '((0,0),(1,1))' &< box '((0,0),(2,2))’ &>  --表示左边的平面体不会扩展到超过右边的平面体的左边. 例如box '((0,0),(3,3))' &> box '((0,0),(2,2))’
>> -- 严格在右
<<| --严格在下 &<| --不会扩展到超出上面 |&>  --不会扩展到超出下面
|>> -- 严格在上
@>  --包含
<@ --被包含 ~= --相同 && --相交

4、SP-GisST

• SP-GisST与Gist类似，也是一种索引框架

• 支持基于磁盘存储的非平衡数据结构如四叉树，k-d树，radix树

例如二维的point类型，Sp-Gist索引支持的操作符如下：
•      << • >>
•      ~=
•      <@ • <^ -- 在下面, circle '((0,0),1)' <^ circle '((0,5),1)’ • >^ -- 在上面, circle '((0,5),1)' >^ circle '((0,0),1)'

5、GIN

• GIN索引是“倒排索引”，它可以处理包含多个键的值(比如数组)

• 与GiST类似，GIN可以支持多种不同的用户定义的索引策略和特定操作符

• 可以使GIN索引根据不同的索引策略，使用特定的操作符类型

• 作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型

6、多列索引

• PostgreSQL中的索引可以定义在数据表的多个字段上

CREATE TABLE test2 (major int, minor int,name varchar）；
CREATE INDEX test2_mm_idx ON test2 (major, minor);

• 目前，只有B-tree、GiST、GIN 和 BRIN 支持多列索引，其中最多可以声明32个字段

• 使用多列索引需要谨慎