开发者学堂课程【PostgreSQL 实战进阶：认识 PostgreSQL 中与众不同的索引（一）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/112/detail/1906
认识 PostgreSQL 中与众不同的索引（一）
内容介绍：
一、PostgreSQL 中索引的总体介绍
二、BRIN 索引的使用案例
三、数组上建立 GIN 索引
四、快速查找某IP所属地区
五、让 like“%XXX%”中的“XXX”进入索引
六、GIN+JSON 数据类型形成用户画像
索引是 PostgreSQL 数据库中最强大的功能之一。本节课的内容主要包括以下几个部分：第一部分，PostgreSQL 中索引的总体介绍；第二部分，借用一个案例说明BRIN 索引的使用的方法及优点；第三部分，介绍在数组上如何使用 GIN 索引；第四部分，通过案例讲解如何快速查找某个 IP 地址的所属地区，该部分实际上是对GiST 用途的演示；第五部分，讲解在 PostgreSQL 中，如何让like“%XXX%”中的“XXX”进入索引，这个功能是在其他数据库中都无法企及的，而 PostgreSQL 可以做到快速索引；最后，使用 GIN+JSON 数据类型实现简单的用户画像场景，以说明 GIN 和 JSON 数据类型的使用方法。
一、PostgreSQL 中索引的总体介绍
1、索引的作用
（1）最主要的作用在于加快找到某一行数据
这行数据在 PostgreSQL 中叫作 TUPLE，即加快 TUPLE 的定位。
（2）主键，唯一索引约束作用
（3）作为排序
在已经存在索引的情况下，不需要重新排序，直接访问索引就可以得到排序的结果。
2、索引的分类
（1）根据索引的算法类型，可以分为：
①B-Tree 索引
是最为常用的索引，既能做等值查询，也能做范围查询。
②Hash 索引
只能做等值查询，但可能比 B-Tree 索引更快。
③GiST 索引
其实它不只是索引类型，还是 PostgreSQL 中实现的索引框架，只要实现一些函数就可以实现自己用途的一些索引。在 PostgreSQL 中，最主要的用在 GiST 索引时，在二维和三维的数据的一些特殊的索引中，如包含操作符和图形有没有重叠的操作符。
④GIN索引
实际上就是我们常说的倒排索引，常用于全文检索。在 PostgreSQL 中，它可以用在数组，或者 JSON 数据类型。
⑤BRIN 索引
实际上是物理块范围的索引，存储在一个表的连续的物理块的范围上的摘要信息。如查询一些连续的物理块的最大值和最小值，当我们查询值时，若值不在范围内，就可以迅速跳过这些物理块，就可以实现快速过滤大量的物理块。通常其他数据库没有 BRIN 索引，是 PostgreSQL 的亮点功能。
（2）其他分类方法
PostgreSQL 可以分为唯一索引、部分索引、多列索引、表达式索引（函数索引）等。
3、在 PostgreSQL 中创建索引的优势
这种优势可以概括为非阻塞式创建索引。
在有些数据库中，当创建索引时会把 DML 语句阻塞，但在 PostgreSQL 中则不会阻塞 DML 语句，其实际上是通过 create index +concurrently 选项实现的。该选项在使用时，PostgreSQL 会执行表的两次扫描，速度略慢，但不会阻塞 DML 语句，这也是PostgreSQL 的亮点功能。
在 PostgreSQL12之前，重建索引时不支持 concurrently 参数，在 PostgreSQL12之后，就实现了 concurrently 的支持，当然，在 PostgreSQL12之前，可以在同样的列上使用 concurrently 建不同名的新索引，再把旧索引删除，同样不阻塞 DML 语句。
二、BRIN 索引的使用案例
建立一张表，在表中插入数据，再建立不同的BRIN索引
1、创建表
create table test01(id int,t text);insert into test01(id,t)select seq,rpad(' ',50,'x') from generate_series(1,3000000) as t(seq);
//创建了一张表，同时把表里面的顺序插入300万行记录
2、创建索引
默认情况下，索引实际上是在128个物理块上面建立最大值、最小值的摘要信息
create index idx_test01_k_brin_128 on test01 using brin(id);
//在默认情况下建立一个 BRIN 索引
create index idx_test01_k_brin_64 on test01 using brin(id) with(pages_per_range=64);
//在64个数据块上建立一个 BRIN 索引
create index idx_test01_k_brin_4 on test01 using brin(id) with(pages_per_range=4);
//在4个数据块上建立一个BRIN索引
create index idx_test01_k btree on test01(id);
//建立一个普通的 B-Tree 索引
3、查看创建索引的大小
按照 select pg_relation_size('idx_test01_k_brin_128')的格式输入查询建立的各个索引的大小，根据查询结果可以看到，不论何种情况下 BRIN 索引的大小都大大小于B-Tree 索引的大小。
该查询结果中显示，B-Tree 索引大小为67兆左右，但在4个数据块上面建摘要信息，大小仅有212k，两者的大小相差了几十倍。说明 BRIN 索引要比普通索引小很多，所以在插入的数据有一定次序，或者在物理块上有一定的数据的范围次序，则用 BRIN 索引会有很大的作用。
仅保留在4个数据块上建立的 BRIN 索引，将些其他的索引先删除。相继输入 select * from test01 where id=100和 timing，可以发现也可以很快查询出结果。
输入 explain analyze select * from test01 where id=100查询执行计划：
可以看出执行时间也很快也进入了 BRIN 索引。由此可见，BRIN 索引的用处是PostgreSQL 数据库很大的亮点。
三、数组上建立 GIN 索引
假设先建关于联系人的表，其中应包括以下字段：
CREATE TABLE contacts(
id int primary key，
name varchar(40),
phone varchar(32)[],
address text);
但当今许多人的手机联系电话不仅一个，因此我们将其建立为数组。在数组的情况下，建立普通索引是行不通的，但在 PostgreSQL 中，对数组来说假设要查某电话是谁的手机号，则可以通过在数组上建 GIN 索引来完成：
1、创建数据
insert into contacts select seq,seq,array[seq+13600000000, seq+13600000001] from generate_series(1,500000,2) as seq;
大约创建了25万行数据
2、创建 GIN 索引
CREATE INDEX idx_contacts_phone on contacts using gin(phone);
3、查询
SELECT * FROM contacts WHERE phone @ >
array['13600006688 ': 'varchar(32)];
//由于该数据处于数组phone中，因此应使用“ @ >”，其含义是包含，即 phone中包含某一固定电话，后面输入的内容也必须是数组，这样就可以查出该联系电话所属的对象。
输入命令后很快就可以查出该记录：
输入explain analyze SELECT * FROM contacts WHERE phone @ > array['13600006688 ': 'varchar(32)]收集表的统计信息：
可以发现该查询也是通过访问索引完成的，且执行时间仅为0.1毫秒。由此可知，在数组中可以建立 GIN 索引，也可以进行搜索。在 PostgreSQL 中，使用数组是一个很好的方案，当我们需要快速查找数组时，就在上面建一个 GIN 索引。
四、快速查找某 IP 所属地区
删除先前创建的表。假设有一张表记录了 IP 地址范围对应的地区，则给定一个公网IP，就可以通过张表的查询其所属的地区。通常情况下，这张表的格式如下：
create table ipdb1
(
id int,
ip_begin inet,
ip_end inet,
area text,
sp text);
即包括 IP 的 id、起始地址、结束地址（inet 是 PostgreSQL 中的 IP 地址的范围）、地区和运营商。以下为课堂上给定的相关表 ipdb1：
可以看到表中包括了每个的起始地址、结束地址等信息，如 IP 地址为1.0.1.0-1.0.3.225，其属于“福建省”，运营商为“电信”。有了以上地址库，我们就可以进行 IP 地址所属区域的查询。
查询方法如下：
1、普通做法
（1）给定一个公网 IP36.22.250.214，查出在那个地区的 SQL 为:
select * from ipdb1 where '36.22.250.214’ > = ip_begin and '36.22.250.214' < = ip_end；
查询结果显示该 IP 地址属于“浙江省”，运营商为“电信”
查询时间约为308ms，查询时间较长，速度较慢，查询其执行计划：
explain select * from ipdb1 where '36.22.250.214’ > = ip_begin and '36.22.250.214' < = ip_end；
查询结果显示：
发现该查询过程是全表扫描，而且是 B 型。
（2）改进:加索引
在 IP 起始地址上添加一个索引：
create index idx_ipdb1_ip_begin on ipdb1(ip_begin);
查询其执行计划：
explain select * from ipdb1 where '36.22.250.214’ > = ip_begin and '36.22.250.214' < = ip_end；
查询结果显示：
速度变快。
（3）再加一个索引
在 IP 结束地址上再添加一个索引：
create index idx ipdb1_ip_end on ipdb1(ip_end)
查询其执行计划：
explain select * from ipdb1 where '36.22.250.214’ > = ip_begin and '36.22.250.214' < = ip_end；
查询结果显示：
因为该查询过程实际上还是在做索引范围查询，占用资源多，cost 值达到了267。而又在结束地址上建立索引，其实两个索引只能有一个索引起作用。除非把开始的索引删掉，但是 cost 值也有268，效率仍旧低下。本质上还是在进行索引的范围扫描。
2、PostgreSQL 中的终极解决方案
创建 RANGE 类型，其表明了起始时间和结束时间，然后把 IP 地址的开始和结束地址都放在字段里面，并在字段里建立 GiST 索引，再查询包含这个 IP 地址的范围，效率会大大提高，相当于是等值查询：
（1）创建表
CREATE TYPE inetrange AS RANGE(
subtype = inet
):
CREATE TABLE ipdb2(
id integer NOT NULL，
ip_range inetrange,
area text,
sp text
)；
以下为创建完成的相关表：
可以看到其中 ip_range 字段包含了起始和结束的 IP 地址，把起始，结束都放在里面。
（2）创建 GiST 类型的索引
CREATE INDEX idx_ipdb2_ip_range ON ipdb2 USING gist (ip_range);
（3）查询
select* from ipdb2 where ip_range @> ’36.22.250.214’=inet;
查询其执行计划：
explain select * from ipdb2 where ip_range @> ’36.22.250.214’=inet;
查询结果显示：
仅需要0.5-0.6ms，cost 值也降到8.3，相对于之前而言，相当假设有大量的系统要来查询 IP 地址，可以大大加速程序，同时降低 cpu 消耗。
