内容简要：
一、索引总体介绍
二、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查看。
