开发者学堂课程【PostgreSQL快速入门：4 PostgreSQL 索引，全文检索，模糊匹配，近似度匹配（一）】学习笔记与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/16/detail/90
4 PostgreSQL 索引，全文检索，模糊匹配，近似度匹配（一）
内容介绍：
一、课程内容
二、PostgreSQL索引介绍
三、PostgreSQL索引使用场景举例
四、是否使用索引和什么有关?
五、多列索引的使用
一、课程内容
1、PostgreSQL索引介绍
（1）熟悉b-tree，hash，gist，gin，sp-gist几种索引类型的概念，以及在什么样的场景应该使用何种索引。
（2）了解全文检索的应用。
2、PostgreSQL查询优化
（1）了解explian SQL分析工具的使用，理解explain的代价计算原理，并根据数据库硬件环境校准代价因子。
（2）理解explain输出的含义(如组合行集，节点处理，合并连接，哈希连接等)，并可以结合explain的输出优化SQL。
3、连接池及数据库高速缓存
（1）以pgbouncer为例，理解数据库连接池在短连接环境下的好处，连接池的几种模式和使用场景。
（2）本地和异地高速缓存的介绍，如本地os层缓存pgfincore，异地K-V缓存pgmemcached的使用。
4、数据库扩展及复制
了解数据库集群级流复制，数据库热备份，表级复制，数据库在虚拟化环境下的使用注意事项。
二、PostgreSQL索引介绍
1、使用索引的好处
（1）利用索引进行排序减少CPU开销
（2）加速带条件的查询，删除，更新
表有一百万条记录，检索其中一条记录，没有索引情况下，一百万条记录都要扫一边，再判断条件是否满足，如果有索引情况下，只需要扫描索引的块，再扫描表就可以输出。在没有索引的情况下，全部取出来再做排序的操作，有索引可以通过索引进行排序。
（3）加速JOIN操作
（4）加速外键约束更新和删除操作
（5）加速唯一值约束，排他约束
2、索引带来的弊端
索引随着表的记录块的变迁需要更新，即使更新的因此会对这类操作带来一定的性能影响 (块不变更的情况下触发HOT特性，可以不需要更新索引)。记录在变更时，有可能这个字段会变更到另外一个数据块上面，发生数据块迁移，索引要更新，更新的记录字段的值不是索引的字段，更新之后它还在原先的数据块里面，索引不需要变更，索引越多带来的变更越多，变更的机会也越多，所以写多读少的情况，索引太多不好。
3、使用索引的注意事项
（1）正常创建索引时，会阻断除查询以外的其他操作。
（2）使用并行CONCURRENTLY选项后，可以允许同时对表的DML操作，但是对于频繁DML的表，这种创建索引的时间非常长，相当于对索引做合并的操作，
（3）某些索引不记录WAL，所以如果有利于WAL进行数据恢复的情况(如crash recovery，流复制，warm standby等)，这类索引在使用前需要重建。(HASH索引)
4、索引类型
(1)根据不同的索引算法，PostgreSQL的算法分为B-tree，Hash，GiST，SP-GiST，GIN，9.3支持五种索引。
(2)select amname from pg_ am;
5、索引应用场景
PostgreSQL不同的索引类别支持的索引访问操作符也有区别，以下为不同的索引类型对应的系统默认的索引策略。
6、Btree，同时还支持前导模糊查询(like 'xxx%'或~’^xxx')，忽略大小写字符前导模糊查询(ILIKE‘xxx%'或~* ‘^xxx')
<
<=
=
>=
>
7、Hash
=
8、Gin，支持多值列的索引，例如数组类型，全文检索类型，例如以下为一维数组类型对应的GIN索引已实现的访问策略操作符。
(1)<@ 被包含array[1，2，3] <@ array[2，3，1]
(2)@> 包含array[1，2，3] @> array[2]
(3)= 相等array[1，2，3] = array[1，2，3]
(4)&& 相交array[1，2，3] && array[2]
9、Gist，并不是单类的索引，可以认为它是一种索引框架，支持许多不同的索引策略(operator class)。可以基于Gist创建索引的操作符，进行二次开发。
10、例如二维几何类型的以下操作符支持通过Gist索引访问，针对创建的数据类型使用gist的框架创建索引访问函数。
<< 严格在左侧，例如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))'
>> 严格在右
<<| 严格在下
&<| 不会扩展到超出上面
|&> 不会扩展到超出下面
|>> 严格在上
@> 包含
<@ 被包含
～= 相同
&& 相交
可以通过网址详细查询http://www.postgresq.org/docs/9.3/static/functions-geometry.html
除此之外，gist索引还支持近邻排序，例如
SELECT * FROM places ORDER BY location <-> point (101，456)' LIMIT 10;跟它相近的符号取出前十，在表里面存储了一些数据
另外contrib中也提供了一些gist索引策略。
Sp-Gist，与gist类似，也是一种索引框架，支持基于磁盘存储的非平衡数据结构，如四叉树，k-d树，radix树。
例如二维的point类型，gist索引支持的操作符如下
<<
>>
~=
<@
<^ 在下面，circle’ ((0，0)，1)’ <^ circle’ ((0，5)，1)'
>^ 在上面，circle’ ((0，5)，1)' >^ circle ‘((0，0)，1)'
三、PostgreSQL索引使用场景举例
1、利用索引进行排序减少CPU开销
查询条件就是索引列
digoal=# create table test(id int，info text，crt _time timestamp);创建表
CREATE TABLE
digoal=# insert into test select generate_series(1，10000)，md5(random()::text)，clock_ timestamp();插入一万条记录
INSERT 0 10000
digoal= # create index idx_ test_ 1 on test(id);在id上创建索引，
CREATE INDEX
digoal=# explain analyze select * from test where id<100 order by id;使用orderbyid检索时可以使用索引扫描
QUERY PLAN
Index Scan using idx_ test_ 1 on test (cost=0.29..162.61 rows= 3333 width=44) (actual time=0.036..0.069 rows- =99 loops=1)
Index Cond: (id < 100)
Total runtime: 0.107 ms
(3 rows)
2、利用索引进行排序减少CPU开销
查询条件不是索引列
digoal= # explain analyze select * from test where info= '620f5eaeaf0d7cf48cdl fa6c410bad49' order by id;取另外的值，字段上面没有索引，进行全链扫描，做人为的排序，额外的排序
QUERY PLAN
Sort (cost= 219.01..219.01 rows=1 width= =45) (actual time= 2.240..240 rows=1 loops =1)
Sort Key: id
Sort Method: quicksort Memory: 25kB排序的节点
-> Seq Scan on test (cost=0.00.219.00 rows=1 width=45) (actual time=0.016.2.201 rows=1 loops=1)
Filter: (info = '620fSeaeaf0d7cf48cdl fa6c410bad49'::text)
Rows Removed by Filter: 9999过滤，取消一行
Total runtime: 2.273 ms
(7 rows)
3、利用索引进行排序减少CPU开销
查询条件不是索引列
digoal=# set enable. seqscat off;强制关掉
SET
Digoal= # explain analyze select * from test where info='620f5eaeaf0d7cf48cdl fa6c4I0bad49' order by id;没有索引
QUERY PLAN
Index Scan using idx _test_ 1 on test (cost=0.29..299.29 rows=I width- 45)(actual time =0.027..3.628 rows=I loops=1)
Fiter: (info = '620f5eaeaf0d7cf48cd 1 fa6c410bad49::text)把整个索引全部扫描一遍，没有排序，额外的排序不需要，按照索引的顺序取数据，按照过滤条件把匹配的数据匹配出来
Rows Removed by Filter: 9999
Total runtime: 3.661 ms
(4 rows)
这个只是例子，不一定适合实际应用场景。
如果info的选择性好的话，在info上面加索引时比较妥当的。
4、加速带条件的查询，删除，更新
digoal=# explain analyze select * from test where id=1;
QUERY PLAN
Index Scan using idx_ test_ 1 on test (cost=0.29..2.30 rows: =1 width= =45) (actual time: =0.014..0.015 rows- =1 loops= =1)
Index Cond: (id= 1)
Total runtime: 0.039 ms
(3 rows)
在没有索引的情况下的查询效率
set enable_indexscan off;没有端点全链扫描，强制关闭索引和 bitmapscan，再查询时发现是全链扫描
set enable_ bitmapscan= off;
digoal=# explain analyze select * from test where id=1;
QUERY PLAN
Seq Scan on test (cost=0.00..219.00 rows=1 width=45) (actual time=0.017..1.744 rows=1 loops=1)
Filter: (id= 1)
Rows Removed by Filter: 9999
Total runtime: 1.773 ms
(4 rows)
5、加速JOIN操作
digoal= # create table testl(id int，info text，crt_ time timestamp);
CREATE TABLE
digoal=# insert into testl select generate_ series(1，10000)，md5(random0::text)，clock_ timestampO;
INSERT 0 10000
testl表没有索引时
digoal=# explain analyze select t1.*，t2.* frgm test t1 join testl t2 on (t1.id=t2.id and t2.id=1);
QUERY PLAN
Nested Loop (cost=0.29..221 .31 rows=1 width=90) (actual time=0.028..1.708 rows=1 loops=1)如果t2表没有索引，进行全链扫描，嵌套循环，loop的次数
> Index Scan using idx_test_1 on test t1 (cost=0.29..2.30 rows=1 width=45) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (id= 1)左表只需要循环一次，左表跟据右表查出来的结果做循环
> Seq Scan on testl t2 (cost=0.00..219.00 rows=1width=45) (actual time =0.010..1.686 rows=1 loops =1)
Filter:(id= 1)
Rows Removed by Filter: 9999
Total runtime: 1.768 ms
(7 rows)
数据在内存里面
6、加速JOIN操作
digoal=# create index idx_ test1_ 1 on test1(id);
CREATE INDEX
digoal=# explain analyze select t1.*，t2.* from test tl join test1 t2 on (t1.id=t2.id and t2.id=1);
QUERY PLAN
Nested Loop (cost: =0.57..4.61 rows =1 width= =90) (actual time =0.045..0.046 rows= =1 loops= =1)
-> Index Scan using idx_ test_ 1 on test t1 (cost=0.29..2.30 rows=1 width=45) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (id= 1)
-> Index Scan using idx_ test1_1 on test1 t2 (cost=0.29..2.30 rows=1 width=45) (actual time=0.029..0.030 rows=1 loops=1)
Index Cond: (id= 1)
Total runtime: 0.089 ms总的时间比较短
(6 rows)
T2表有索引，两个都使用索引，成本很低，实际时间很短，从索引里面取没有内存里，所以看到的时间有差别。
7、加速JOIN操作
MERGE JOIN也能用到索引，MERGE JOIN是两个表做好排序之后，按照JOIN列做好排序，进行MERGE JOIN操作。T1表和t2表可以使用索引。
digoal=# explain analyze select t1.*，t2.* from test t1 join test1 t2 on (t1.d=t2.id);
QUERY PLAN
Merge Join (cost=0.57..698.57 rows=10000 width=90) (actual time =0.024..14.468 rows= 10000 loops=1)
Merge Cond: (t1.id = t2.id)
> Index Scan using idx _test_ 1 on test t1 (cost=0.29..274.29 rows=10000 width= =45) (actual time: =0.010. .3.754 rows= 10000 loops=1)
> Index Scan using idx_ testl_ 1 on test1 t2 (cost=0.29..274.29 rows=10000 width=45) (actual time=0.007..3.715 rows=1000 loops =1)
Total runtime: 15.429 ms
(5 rows)
8、加速JOIN操作
在没有索引的情况下，merge join增加排序开销
digoal=# explain analyze select t1.*，t2.* from test t1 join test1 t2 on (t1.id=t2.id);
QUERY PLAN
Merge Join (cost=1716.77..1916.77 rows= 10000 width=90) (actual time: =8.220..17.291 rows=10000 loops: =1)
Merge Cond: (t1.id = t2.id)
> Sort (cost- =858.39..883.39 rows= 10000 width= 45) (actual time 4.177...21 rows=10000 loops=1)
Sort Key: t1.id
Sort Method: quicksort Memory: 1018kB
> Seq Scan on test t1 (cost=0.00.. 194.00 rows=10000 width= s45) (actual time =0.008..1.757 rows= 10000 loops=1)
-> Sort (cost=858.39..883.39 rows= 10000 width=45) (actual time 4.03..5.300 rows=10000 loops=1)
Sort Key: t2.id
Sort Method: quicksort Memory: 1018kB
-> Seq Scan on testl t2 (cost=0.00.. 194.00 rows- =10000 width=45) (actual time=0.006..1.752 rows= 10000 loops=1)
Total runtime: 18.420 ms
(11 rows)
两个表全链扫描根据 t1.id字段进行排序，条件是t1.id和 t2.id。
9、加速外键约束更新和删除操作
digoal=# create table p(id int primary key，info text，crt_ time timestamp);
CREATE TABLE
digoal=# create table f(id int primary key，p_ .id int references p(id) on delete cascade on update cascade，info text，crt_time timestamp);
CREATE TABLE
digoal=# insert into p select generate_ series(1，10000)，mdS(random0::text)，clock_ timestamp();
INSERT 010000
digoal=# insert into f select generate_ series(1，10000)，generate. _series(1，10000)，md5(random0::text)，clock. _timestamp();
INSERT 0 10000
创建一个f表，一个p表，P表是父表的意思，当p表id值删除某一个值时，也会影响f表，相当于f表的值被删掉。先查10000万条p表，再查10000万条f表。update会牵连到f表的update。当p表进行update时，f表也会进行相应的update，字段不会有变更。
10、加速外键约束更新和删除操作
在f表的p_ id未加索引时，更新p.id
digoal= =# explain (analyze，verbose，costs，buffers，timing) update p set id=0 where id=1;
QUERY PLAN
Update on postgres.p (cost=0.29..2.30 rows=l width=47) (actual time=0.082..0.082 rows=0 loops=1)
Buffers: shared hit=8
> Index Scan using p_ pkey on postgres.p (cost=0.29..2.30 rows=1 width=47) (actual time=0.021..0.022 rows=1 loops=1)
Output: 0，info，crt_ time，ctid
Index Cond: (p.id= 1)
Buffers: shared hit=3
Trigger RI_ ConstraintTrigger a_ 92560 for constraintf_ p. _id_fkey on p: time= 2.630 calls=1p表上面的触发器，执行时间是2.63毫秒
Trigger RI_ ConstraintTrigger_ e_ 92562 for constraintf _p1 _id_ fkey on f: time=0.059 calls=1
Total runtime: 2.820 ms
(9 rows)
在做update时，f表也进行update，通过触发器完成，触发器在有索引的情况下，和没有索引的情况下执行时间差很大。
11、增加f表的p_ jid列上的索引
digoal= =# create index idx_ f_，lonf(p_ id);
CREATE INDEX
digoal=# explain (analyze，verbose，costs，buffers，timing) update p set id=1 where id=0;
QUERY PLAN
Update on postgres.p (cost=0.29.2.30 rows=l width=47) (actual time=0.067..0.067 rows=0 loops=1)
Buffers: shared hit=8
> Index Scan using p_ pkey on postgres.p (cost=0.29..2.30 rows=1 width=47) (actual time= =0.018..0.020 rows=1 loops=1)
Output: 1，info，crt_ time，ctid
Index Cond: (p.id = 0)
Buffers: shared hit= =3
Trigger RI_ ConstraintTrigger _a 92560 for constraintf _p_ id_ fkey on p: time=0.471 calls=1 加了索引之后，时间是0.471毫秒
Trigger RI_ ConstraintTrigger _c 92562 for constraintf _p_ id_ fkey on f: time =0.053 calls=1
Total runtime: 0.636 ms
(9 rows)
12、索引在排他约束中的使用
对排他操作符的要求，左右操作数互换对结果没有影响，例如x=y，y=x结果都为true或unknown。
用法举例
digoal=# CREATE TABLE test(id int，geo point，EXCLUDE USING btree (id WITH pg_ catalog.=));
CREATE TABLE
digoal=# insert into test (id) values (1);
INSERTO 1
digoal=# insert into test (id) values (1);
ERROR: 23P01: conflicting key value violates exclusion constraint "test_ id_excl"
DETAIL: Key (id)=(1) conflicts with existing key (id)=(1).
SCHEMA NAME: postgres
TABLE NAME: test
CONSTRAINT NAME: test_ id_excl
LOCATION: check_ exclusion constraint，execUtils.c:1337
排他约束用的是操作符，比如创建test表，表上面有id字段，创建exclude约束，索引访问方法用btree，也可以用其它的，只要可以满足操作符，左右对调之后是一样的即可。排除x=y的动作，插同样的值1，就会发现违反约束，1和1是相等的，得到的结果是ture，只要结果满足，就把它排除掉，相当于模拟unique key的操作。
13、加速唯一值约束
Primary KEY
Unique KEY
digoal=# CREATE TABLE test(id int，geo point，EXCLUDE USING spGIST (geo WITH pg，_catalog.~=));用spGIST索引，约等相当于两个值完全相等的意思
CREATE TABLE
索引如下:
digoal=# \d test
Table "postgres.test"
Column｜Type｜Modifiers
Id｜integer｜
Geo｜point｜
Indexes:
"test_ geo_ excl" EXCLUDE USING spgist (geo WITH ~=)
在点的类型上存了完全两个相等的点就会报错。
14、CREATE TABLE test(id int，geo circle，EXCLUDE USING GIST (geo WITH pg_ catalog.&&));排除，违反约束
INSERT INTO test values(1，'<(0，0)，2> ':circle);直径等于2，中心点是0和0
INSERT INTO test values(1，'<(4.1，0)，2>'::circle);
INSERT INTO test values(1，'<(-1.9，0)，2>'::circle);
ERROR: conflicting key value violates exclusion constraint "test_geo_ excl"
DETAIL: Key (geo)=(<(-1.9，0)，2>) conflicts with existing key (geo)=(<(0，0)，2>)
圆的类型，用的符号是相交，相当于两个圆使用与的操作数，操作时两个圆对调，也没有关系，跟任何一个操作数对调都没有关系，得到的结果都是一样的。第三个圆存进去时跟第一个圆相交，得到的值是触。
15、digoal=# CREATE TABLE test(id int，geo circle，EXCLUDE USING GIST (geo WITH pg_ catalog.&));
CREATE TABLE
digoal= # CREATE TABLE test(id int，geo circle，EXCLUDE USING GIST (geo WITH pg_ catalog.~=));
CREATE TABLE
以下例子左右操作数互换后得到的结果不一致，所以这类操作符不允许创建排他索引。
digoal=# CREATE TABLE test(id int，geo point，EXCLUDE USING spGIST (geo WlTH pg. catalog.<));一个圆在另一个圆的下面，这种操作不允许，因为数对调，得到的数相反，得到的值不一样，不允许这么创建
ERROR: 42809: operaor<(point，point) is not commutative
DETAIL: Only commutative operators can be used in exclusion constraints.
LOCATION: ComputelndexAttrs，indexcmds.c:1132
digoal=# CREATE TABLE test(id int，geo point，EXCLUDE USING btree (id WITH pg_ catalog.>));比如a大于b和b大雨a两个结果不一样，不允许创建排他约束的索引
ERROR: 42809: operator >(integer，integer) is not commutative
DETAIL: Only commutative operators can be used in exclusion constraints.
LOCATION: ComputeIndexAttrs，indexcmds.c:1132
四、是否使用索引和什么有关?
1、首先是前面提到的Access Method，然后是使用的operator class，以及opc中定义的operator或function。在sql语句中看operator是否在Access Method支持的opc中。
2、这些都满足后，还要遵循CBO的选择。只有成本符合的情况下，才使用索引，使用索引的成本和随机页面扫描相关，页面的扫描是随机页面的扫描。
#seq_ page_ cost = 1.0
#random _page_ cost = 4.0
#cpu_ tuple_ cost = 0.01
#cpu_ jindex_tuple_ cost = 0.005索引扫描带来的索引tuple的成本开销
#cpu_ operator_ cost = 0.0025
#effective_ cache_ size = 128MB当数值越大，更倾向于使用索引扫描，数值越大，数据在operator中，随机页面成本降低，即使是随机扫描，在 cache里命中，成本也是很低
3、遵循完CBO的选择，还需要符合当前配置的planner配置。是否使用索引跟选择器有关，如果是off，就不能使用索引。即使成本很低也不会走索引。
#enable_ bitmapscan = on
#enable_ hashagg = on
#enable_ hashjoin = on
#enable_ indexscan = on
#enable_ material = on
#enable_ mergejoin = on
#enable_ nestloop = on
#enable_ seqscan = on
#enable_ sort = on
#enable_ tidscan = on
五、多列索引的使用
Multicolumn Index
多列索引，使用任何列作为条件，只要条件中的操作符或函数能满足opclass的匹配，都可以使用索引，索引被扫描的部分还是全部基本取决于条件中是否有索引的第一列作为条件之一。
在索引上面两个列上面可以走驱动列非驱动列，驱动列是c1，c2是非驱动列，扫描时默认不走索引，把全链扫描关掉也可以走索引，只不过索引会全扫描一遍。
例子
postgres- # create table test (cl int，c2 int);
postgres- # insert into test select 1，generate_ series(1，100000);
postgres- # create index idx_test_ 1 on test(c1，c2);
注意过滤条件不是驱动列，看似不能走索引
postgres- =# analyze test;
postgres=# explain selet * from test wherec2=100;。0。
Seq Scan on test (cost=0.00..1693.00 rows=1 width=8)
Filter: (c2 = 100)
postgres- # setenable seqscan=off;
postgres= # explain analyze select * from test where c2=100;
Index Scan using idx_ test_ 1 on test (cost=0.00..1858.27 rows=1 width=8) (actual
time-0.104..7.045 rows=1 loops=1)
Index Cond: (c2= 100)
