内容简要：

一、索引总体介绍

二、BRIN索引的例子

三、数组上建GIN索引的例子

四、快速查找某个IP是哪个地区

五、让Like '%XXX%'走索引

六、GIN+JSON用户画像

 

 

一、索引总体介绍

（一）索引的作用

l  索引主要有三个作用：

1）加速TUPLE定位

select * from test01 where k=10;

select * from test01 where k>100 and k<200;

2）主键, 唯一约束作用

create table test01(id int primary key, k int, t text);

create unique idx_test01_k on test01(k);

3）排序，有索引情况下，不需要重新排序，可以直接访问用。

select * from test01 order by k;

 

（二）索引的分类

1.按算法分

按算法分类，索引可分为B-Tree索引、Hash索引、GiST索引、GIN索引与BRIN索引。

l  B-Tree索引（最常见索引）

等值查询：=、IS NULL，IN；

范围查询：>、< 、>=、 <=、BETWEEN AND、

LIKE(开头匹配),  ILIKE (大小 写一致的字符开头匹配),~

l  Hash索引

只能等值查询；

等值查询可能B-Tree索引更快；

PG10之前，无法在主备之间同步WAL日志。

l  GiST索引

不是一种索引类型，而是一种可以实现自定类型和策略的索引架构；

包含了用于二维几何数据类型的 GiST 操作符类；

包含操作符: @> 图型没有重叠操作符号：<<

l  GIN索引

倒排索引，常用在全文检索中；

可高效地检测某值是否存在很多行中；

已实现了用于数组的GIN操作符类：@>、&&

l  BRIN索引

块范围索引；

存储放在一个表的连续物理块范围上的值摘要信息，如最大值、最小值；

可以用于：<、<=、=、>=、 >

通常其他数据库没有BRIN索引，是PG的亮点功能。

 

2.其他分类

PG索引按照其他分类也可分为：唯一索引，部分索引，多列索引和表达式索引，这里不展开作详细介绍。

（三）非阻塞式创建索引

非阻塞式创建索引是PostgreSQL的一大优势。

使用普通方式创建索引时，PostgreSQL会锁定表以防止写入，在此过程中 其他用户仍然可以读取表，但是DML等操作被一直阻塞，直到索引创建完毕，这在大多数的在线数据库中都是不可接受的行为。

鉴于此，PostgreSQL支持不长时间阻塞更新的情况下建立创建索引，这是通过“CREATE INDEX CONCURRENTLY idx_tab01_note on testtab01(note);”选项来实现的。

当该选项被使用时，PostgreSQL会执行表的两次扫描，因此该方法需要更 长一些的时间来建索引，尽管如此，这个选项也是很有用的一个功能。

 

（四）非阻塞式重建索引

在PostgreSQL的12版本之前，重建索引时不支持Concurrently的参数，可以在同样的列上用Concurrently建一个不同名的新索引，再把旧索引删除，这样也不阻塞DML等语句。

 

（五）PostgreSQL中文社区技术认证

目前PostgreSQL中文社区技术认证有三级认证，分别为PCA（认证专员）、PCP（认证专家） PCM（认证大师），可在社区网站“http://www.postgres.cn”查看。

 

 

二、BRIN索引的例子

上图为BRIN索引的一个例子，我们创建一张表，并顺序插入3000000条记录，然后“create index idx_test01_k_brin”创建一个索引。默认情况下索引有128个物理块，上面建一个最大值与最小值的摘要信息。除了默认情况，我们又建了64个数据块与4个数据块的索引，同时我们建了一个普通的B树索引。

如上图所示，此时我们可以查看索引大小，pages_per_range不同值时BRIN 索引通常在1MB以下，而普通索引为64M以上。可以看到，用BRIN创建的索引，无论在哪种情况下，索引的大小都远远小于用B-Tree方式创建的索引。

三、数组上建GIN索引的例子

下面是一个用GIN索引查找电话号码号主的例子。

假设我们先建一个联系人的表，有上图5个字段。由于每个人可能存在多个联系电话，于是我们将这些信息建成一个数组。在数组的情况下，无法建立普通索引，但在PostgreSQL中可在数组上建立GIN索引。

在这里我们建了250000行数据，然后我们再给它建了一个GIN索引，用“@>”表示这个数组中包含某个固定电话，这样就可以查出号码对应的号主。

通过执行计划可以看到，通过在PostgreSQL的数组上建立GIN索引来查找数值时，所需时间非常短，仅需0.108ms。

 

 

四、快速查找某个IP是哪个地区

假设我们有一张表，记录了IP地址范围对应的地区，给一个公网IP就可以查询出这个IP地址所对应的地区。

 

（一）普通解决方案

如上图所示，该格式包含IP的ID，IP的起始地址与结束地址，IP所在地区，IP对应的运营商，Inet表示PostgreSQL里IP地址的范围，例表如下：

可以看到，例如IP地址1.0.1.0到1.0.3.255是来自福建电信。有了这么一个地址库，我们就可以快速查询一个IP所对应的相关信息。

例如我们想查询36.22.250.214来自哪里，可以输入：

select * from ipdb1 where '36.22.250.214'>=ip_begin and '36.22.250.214' <=ip_end;

耗时308ms，得到结果如下，可以看到这个地址来自浙江电信。

通过执行计划可以看到，该SQL是一个并行的全表扫描，CPU占用高。

这种情况的改进方法，是在起始地址上加一个索引：

create index idx_ipdb1_ip_begin on ipdb1(ip_begin);

通过执行计划可看到，加了该索引之后，耗时大幅减少。

此时可以在结束地址上也加一个索引：

create index idx_ipdb1_ip_end on ipdb1(ip_end);

由于索引还是做了范围查询，因此占用资源较多。

（二）终极解决方案

如上方所示，该方案创建一个RANGE类型，RANGE类型表明起始时间与结束时间，然后将IP地址的开始与结束都放在一个字段中，然后在该字段中建一个GIST索引。

然后在查的范围是包含了某个IP地址，这时走的索引的效率远高于之前的范围查询索引，相当于是一个等值查询。

可以看到，ip_range字段包含了表的起始与截至，此时加入输入：

select * from ipdb2 where ip_range @> '36.22.250.214'::inet;

查询 IP 36.22.250.214，可以快速查到对应信息浙江电信。

从上方的执行计划可以看到耗时大幅减少，并且Cost值为8.3，对比之前的268大幅降低。

通过这种方式，当有大量系统要来查询IP地址时，可以有效减少耗时，并降低CPU占用，以上就是GIST用RANGE使用的一个例子。

 

 

五、让like %XXX%走索引

PostgreSQL中还有一个黑科技——让Like在'%XXX%'走索引，下面举例说明。

如上方所示，首先我们建一张表，插入1000000条测试数据，接着收集统计信息。由于现在表中可能没有索引，走的并行做全盘扫描，此时执行时间为100~300毫秒。如果关掉并行，执行时间还会更长。

在其他数据库中， like是要找两个%中间的数，通常是无能为力，但在PostgreSQL中可以解决这个问题。

首先先装入插件create extension pg_trgm;，之后建一个GIN索引，让Like走'%99999%'。通过执行计划可以看到，这次执行时间为2ms，效率很高，解决了其他数据库遇到的难题。

 

 

六、GIN+JSON用户画像

最后我们来看，如何用GIN索引在JSON上做用户画像系统。

 

1.标签模型

首先建立一个简单的标签模型如上，总共分为四类：职业、爱好、学历和性格。

 

2.建表

CREATE TABLE user_tag(uid serial primary key, tag jsonb);

第二步通过我们建立一张表，第一个字段UID表示用户ID，第二个TAG是打标签，此处打一个JSONB的数据类型。

 

3.造数据

建完表后，为了查看效果需要造数据，我们写了一些辅助的函数来完成，函数如下：

4.造数据（续）

接着开始造入100000条记录的数据，由于标签是造的数据，所以是随机生成的。

 

5. 建GIN索引

CREATE INDEX idx_user_tag_tag on user_tag using gin(tag);

造数据完成后，在列上建GIN索引，建立完成后，可在表中快速查询到相应信息。例如查询性格为“外向”和“细心”的老师，可以通过语句：

select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["老师"]}';

可以很快查到，如下方所示：

如果要查询更为详细的信息，例如性格为“外向”和“细心”而又喜欢“滑雪”和“游泳”的医生，可以通过语句：

select * from user_tag where tag @> '{"性格":["外向","细心"]}' and tag @> '{"职业":["医生"]}' and tag @>'{"爱好":["滑雪", "游泳"]}';

很快查到，如下方所示：

如果我们给用户打了这么一个标签，就可通过SQL很快查出对应的标签信息，以上就是用GIN索引做用户画像的一个简单示例。

更多阿里云PostgreSQL图像识别、人脸识别、相似特征检索、相似人群圈选等精选案例可在https://developer.aliyun.com/article/747642查看。