认识PostgreSQL中与众不同的索引

C

里认识PostgreSQL中与众不同的索引唐成中启乘数科技（杭州）有限公司联合创始人J里•索引总体介绍•

BRIN索引的例子•数组上建GIN索引的例子•快速查找某个IP是哪个地区•让like

'%XXX%'走索引•

GIN+JSON用户画像目录create

table

test01(id

int

primary

key,

k

int,t

text);

select

*

from

test01

order

by

k;create

unique

idx_test01_k

on

test01(k);中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商加速TUPLE排序*from

test01where

k=10;*from

test01where

k>100and

k<200;主键,

唯一约束索引的作用C

里云selectselect定位只能等值查询等值查询可能更快PG10之前，无法在主备之间同步WAL日志。块范围索引存储放在一个表的连续物理

块范围上的值摘要信息，如

最大值、最小值可以用于：

<、

<=、

=、

>=、

>不是一种索引类型，而是一种可以实现自定类型和策略的索引架构中包含了用于二维几何数据类型的GiST操作符类包含操作符:

@>图型没有重叠操作符号：

<<等值查询：

=、

IS

NULL，IN范围查询：

>、

<、

>=、

<=、

BETWEENAND、LIKE(开头匹配),

ILIKE

(大小写一致的字符开头匹配),~倒排索引，常用在全文检索中可高效地检测某值是否存在很多行中已实现了用于数组的GIN操作符类：

@>、

&&Hash索引BRIN索引GiST索引B-Tree索引GIN索引索引的分类：按算法分中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商C

里云CREATE

INDEX

idx_test01_part_kONtest01(k)where

k>1000and

k

<2000;索引的分类：其他分法表达式索引（函数索引）中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商部分索引多列索引唯一索引C

里云•

使用普通方式创建索引时，

PostgreSQL会锁定表以防止写入，在此过程中其他用户仍然可以读取表，但是DML等操作被一直阻塞，直到索引创建完毕。•

这在大多数的在线数据库中都是不可接受的行为。•

鉴于此，

PostgreSQL支持不长时间阻塞更新的情况下建立创建索引，•

这是通过在CREATE

INDEX中加CONCURRENTLY选项来实现的。•

当该选项被使用时，

PostgreSQL会执行表的两次扫描，因此该方法需要更

长一些的时间来建索引。尽管如此，这个选项也是很有用的一个功能。•

CREATE

INDEXCONCURRENTLY

idx_tab01_noteontesttab01(note);中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商

非阻塞式创建索引C

里云•

PG12版本之前：重建索引时不支持concurrently的参数•

可以在同样的列上用concurrently建一个不同名的新索引•

再删除旧索引非阻塞式重建索引中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商C

里云PCA（认证专员）PCP（认证专家）PCM（认证大师）PostgreSQL中文社区技术认证中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商请关注C

里云J里•

索引介绍•

BRIN索引的例子•数组上建GIN索引的例子•快速查找某个IP是哪个地区•让like

'%XXX%'走索引•

GIN+JSON用户画像目录•

createtabletest01(id

int,ttext);insert

intotest01(id,t)selectseq,

rpad('',50,'x')fromgenerate_series(1,3000000)ast(seq);•

create

index

idx_test01_k_brin_128ontest01

using

brin(id);•

create

index

idx_test01_k_brin_64ontest01

using

brin(id)with

(pages_per_range=64);•

create

index

idx_test01_k_brin_4ontest01

using

brin(id)with

(pages_per_range=4);•

create

index

idx_test01_k_btreeontest01(id);中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商

BRIN索引的例子C

里云pages_per_range不同值时BRIN索引通常在1MB以下，而普通索引为64M以

上中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商

BRIN索引的例子C

里云J里•

索引介绍•

BRIN索引的例子•数组上建GIN索引的例子•快速查找某个IP是哪个地区•让like

'%XXX%'走索引•

GIN+JSON用户画像目录•

联系人表：•CREATE

TABLE

contacts(•

id

int

primary

key,•

namevarchar(40),•

phonevarchar(32)[],

•address

text);数组上建GIN索引中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商C

里云•

insert

intocontactsselectseq,seq,

array[seq

seqfromgenerate_series(1,

500000,2)asseq;•

CREATE

INDEX

idx_contacts_phoneoncontacts

usinggin(phone);•

SELECT*

FROMcontactsWHERE

phone

@>

array[::varchar(32)];数组上建GIN索引中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商C

里云J里•

索引介绍•

BRIN索引的例子•数组上建GIN索引的例子•快速查找某个IP是哪个地区•让like

'%XXX%'走索引•

GIN+JSON用户画像目录快速查找某个IP是哪个地区的普通做法

-J8里•

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

ipdb1(id

int,ip_begin

inet,ip_end

inet,areatext,sp

text);中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商快速查找某个IP是哪个地区的普通做法

-J8里•给一个公网IP，查出在那个地区的SQL为：select*from

ipdb1where

'14'>=ip_beginand

'14'

<=ip_end;•

上面的SQL是全表扫描，

CPU占用高•

改进：加索引•

create

index

idx_ipdb1_ip_beginon

ipdb1(ip_begin);•

再加一个索引：•

create

index

idx_ipdb1_ip_endon

ipdb1(ip_end);•但还是索引范围扫描，占用资源多中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商•CREATETYPE

inetrangeAS

RANGE

(•

subtype

=

inet•);•CREATETABLE

ipdb2

(•

id

integer

NOT

NULL,•

ip_range

inetrange,•

areatext,•

sp

text•);•CREATE

INDEX

idx_ipdb2_ip_rangeON

ipdb2

USINGgist

(ip_range);•select*from

ipdb2where

ip_range

@>

'14'::inet;快速查找某个IP是哪个地区的终极解决方案

-J8

里中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商J里•

索引介绍•

BRIN索引的例子•数组上建GIN索引的例子•快速查找某个IP是哪个地区•让like

'%XXX%'走索引•

GIN+JSON用户画像目录like

'%xxx%'走索引的方法

-J8里•

createtabletest01(id

int,ttext);•

insert

intotest01selectseq,seq

from

generate_series(1,

1000000)as

seq;•analyze

test01;•

explainanalyzeselect*fromtest01where

t

like

'%99999%';•需要100~300毫秒，走全表扫描中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商•

createextension

pg_trgm;•

create

index

idx_test01_tontest01

usinggin(t

gin_trgm_ops);•现在走了索引，只需要2毫秒，like

'%xxx%'走索引的方法中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商C

里云like

'%xxx%'走索引的方法中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商C

里云J里•

索引介绍•

BRIN索引的例子•数组上建GIN索引的例子•快速查找某个IP是哪个地区•让like

'%XXX%'走索引•

GIN+JSON用户画像目录•标签模型•

职业：农民、工人、

IT工程师、理发师、医生、老师、美工、律师、公务员、官员•

爱好：游泳、乒乓球、羽毛球、网球、爬山、高尔夫球、滑雪、爬山、旅游•

学历：无学历、小学、初中、高中、中专、专科、本科、硕士、博士•

性格：外向、内向、谨慎、稳重、细心、粗心、浮躁、自信中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商用户画像系统C

里云•

建表•

CREATETABLE

user_tag(uidserial

primary

key,tag

jsonb);•

uid表示用户ID，•

tag字段中放了用户的所有标签，

tag字段类型为jsonb。中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商用户画像系统C

里云•造数据CREATEOR

REPLACE

FUNCTIONf_random_attr(attrtext[],

max_attr

int)

RETURNStext[]AS

$$DECLAREi

integer

:=

0;

r

integer

:=

0;

restext[];vtext;l

integer;num

integer;BEGINnum:=

(random()*max_attr)::int;IF

num

<

1THENnum

:=

1;END

IF;l:=

array_length(attr,

1);WHILE

i

<

num

LOOPr:=

round(random()*l)::int

+

1;v

:=

attr[r];IF

res

@>array[v]THENcontinue;ELSEres:=array_append(res,v);i

:=

i

+

1;END

IF;END

LOOP;return

res;END;$$

LANGUAGEplpgsql;中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商用户画像系统C

里云•造数据(续)INSERT

INTO

user_tag(uid,tag)SELECT

seq,json_build_object('职业',f_random_attr(array['农民','工人','IT工程师','理发师','医生','老师','美工','律师','公务员','官员'],

1),'爱好',f_random_attr(array['游泳','乒乓球','羽毛球','网球','爬山','高尔夫球','滑雪','爬山','旅游'],

5),

'学历',f_random_attr(array['无学历','小学','初中','高中','中专','专科','本科','硕士','博士'],

1),

'性格',f_random_attr(array['外向','内向','谨慎','稳重','细心','粗心','浮躁','自信'],

3))::jsonbFROM

generate_series(1,

100000)as

t(seq);中启乘数科技：阿里云合作伙伴，专业的数据库服务提供商用户画像系统C

里云•