Postgresql杂谈 04—Postgresql中的四种常规索引
2021/7/18 19:40:57
本文主要是介绍Postgresql杂谈 04—Postgresql中的四种常规索引，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
一、索引的分类
Postgresql中索引一共分为5种，每一种都有它合适的应用场景，我们在使用时要根据不同业务的特点，选择合适的索引，这样才能加快sql语句的查询效率。下面，我们将就每种不同的索引，介绍其特点。
2.1 B树索引
这是我们最常用的索引结构了，B树是一颗多路平衡查找树，每个节点包含多个键，而且这些键对应的指针一般指向磁盘上同一个数据块，目的是一次从磁盘读取一个数据块，减少磁盘IO操作，加快查询的效率。
B树索引的结构如下所示：
接下来，我们将介绍B树索引的用法。在此之前，先介绍了要用到的数据环境，我们要在一个名为test的表上建立B树索引，表的结构如下：
stock_analysis_data=# \d+ test
Table "public.test"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
fund_code | character varying(256) | | | | extended | |
fund_name | character varying(256) | | | | extended | |
record_time | timestamp without time zone | | | | plain | |
该表中目前大概有500W+的数据，数据类似如下：
stock_analysis_data=# select * from test limit 10; fund_code | fund_name | record_time -----------+--------------------------+------------------------- 160630 | 鹏华中证国防指数分级 | 2020-08-04 05:54:16.313 001838 | 国投瑞银国家安全混合 | 2020-08-04 05:54:16.313 160643 | 鹏华空天军工指数(LOF) | 2020-08-04 05:54:16.313 002703 | 长城久源灵活配置混合 | 2020-08-04 05:54:16.313 164402 | 前海开源中航军工 | 2020-08-04 05:54:16.313 161628 | 融通军工分级 | 2020-08-04 05:54:16.313 161024 | 富国中证军工指数分级 | 2020-08-04 05:54:16.313 163115 | 申万菱信中证军工指数分级 | 2020-08-04 05:54:16.313 003017 | 广发中证军工ETF联接A | 2020-08-04 05:54:16.313 005693 | 广发中证军工ETF联接C | 2020-08-04 05:54:16.313
在建立B树索引之前，我们先用explain查看下查询fund_code为160630的数据执行情况：
stock_analysis_data=# explain analyze verbose select * from test where fund_code='160630';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..73637.05 rows=1022 width=42) (actual time=0.288..2625.485 rows=1024 loops=1)
Output: fund_code, fund_name, record_time
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on public.test (cost=0.00..72534.85 rows=426 width=42) (actual time=16.141..2615.515 rows=341 loops=3)
Output: fund_code, fund_name, record_time
Filter: ((test.fund_code)::text = '160630'::text)
Rows Removed by Filter: 1685163
Worker 0: actual time=31.975..2614.851 rows=310 loops=1
Worker 1: actual time=16.427..2612.026 rows=371 loops=1
Planning Time: 0.070 ms
Execution Time: 2626.203 ms
整个SQL语句在没有建立索引的情况下，耗时2626ms，扫描出了1024条数据。现在我们就在fund_code字段上建立索引。
stock_analysis_data=# create index mybtindex on test(fund_code); CREATE INDEX
可以看到，索引创建成功了。接下来，再去执行建立索引前的查询语句：
stock_analysis_data=# explain analyze verbose select * from test where fund_code='160630';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..73637.05 rows=1022 width=42) (actual time=0.231..534.786 rows=1024 loops=1)
Output: fund_code, fund_name, record_time
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on public.test (cost=0.00..72534.85 rows=426 width=42) (actual time=0.450..512.214 rows=341 loops=3)
Output: fund_code, fund_name, record_time
Filter: ((test.fund_code)::text = '160630'::text)
Rows Removed by Filter: 1685163
Worker 0: actual time=0.758..494.974 rows=340 loops=1
Worker 1: actual time=0.579..508.170 rows=330 loops=1
Planning Time: 0.061 ms
Execution Time: 535.335 ms
整个查询走了索引，而且耗时在535.335ms，比之前的耗时小了很多。
2.2 Hash索引
Hash索引主要用来进行等值查询，它的原理其实就是将索引值进行hash，然后将hash值作为键，对应的数据行id（TID）作为值存到hash表（bucket）里面，因为存在hash冲突或者多个行含有相同的索引字段值，所以会存在同一个hash值对应多个TID的情况。Hash索引基本的存储结构如下：
建立Hash索引的方式是在创建索引时显式的使用using指定索引类型为hash，还是以上一小节的test表为例建立Hash索引：
stock_analysis_data=# create index myhsindex on test using hash(fund_code); CREATE INDEX
在看postgresql很多关于Hash索引的资料时，一般都会强调：Hash索引操作目前不被WAL记录，因此存在未写入修改，在数据库崩溃后需要用REINDEX命令重建Hash索引，因此不建议使用。但是实际上，在Postgresql10之后，Hash索引逐步解决了这个问题，目前索引的更新操作也会写入WAL日志。
我们看下使用Hash索引查询：
stock_analysis_data=# explain (analyze,verbose) select * from test where fund_code='160630';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.test (cost=35.92..3643.99 rows=1022 width=42) (actual time=0.343..2.112 rows=1024 loops=1)
Output: fund_code, fund_name, record_time
Recheck Cond: ((test.fund_code)::text = '160630'::text)
Heap Blocks: exact=1024
-> Bitmap Index Scan on myhsindex (cost=0.00..35.66 rows=1022 width=0) (actual time=0.223..0.223 rows=1024 loops=1)
Index Cond: ((test.fund_code)::text = '160630'::text)
Planning Time: 0.072 ms
Execution Time: 2.714 ms
(8 rows)
可以看到（第8行）本次的查询使用了我们新创建的hash索引。而如果我们将查询条件中的等值查询换成非等值查询之后，就不再走Hash索引了：
stock_analysis_data=# explain (analyze,verbose) select * from test where fund_code<'160630';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.test (cost=0.00..109398.40 rows=3912272 width=42) (actual time=0.018..3518.017 rows=3895296 loops=1)
Output: fund_code, fund_name, record_time
Filter: ((test.fund_code)::text < '160630'::text)
Rows Removed by Filter: 1161216
Planning Time: 0.111 ms
Execution Time: 5585.695 ms
2.3 Gist索引
Gist索引不同于前面两种索引，它实际上是一种索引的框架，也可以理解成索引的抽象，而可以有不同的实现。比如在postgresql的扩展安装中有一种btree_gist就是官方提供的的其中一种实现。它与B Tree索引最大的不同点在于：
（1）在多字段组成的复合索引中，查询条件中不包含索引的第一个字段时，是不使用B Tree索引扫描的，但是btree_gist可以支持使用任意字段都使用索引扫描。
（2）相比于B Tree索引，btree_gist创建耗时较长，占用空间较大，而且效率更低。
下面，我们来演示下btree_gist索引的用法，在使用之前，要先使用create extension进行btree_gist的创建，如果出现下面的错误，则需要进行btree_gist拓展的安装。
stock_analysis_data=# create extension btree_gist; ERROR: could not open extension control file "/usr/pgsql-11/share/extension/btree_gist.control": No such file or directory
进行btree_gist拓展安装的方法是进入psotgresql的安装包下/contrib/btree_gist/目录，执行make&&make install（如果采用yum安装的数据库，还要先在安装包的主目录下执行./configure --profix="pg安装的主目录"），进行完上述操作之后，再进行btree_gist的创建，发现可以创建成功了。
stock_analysis_data=# create extension btree_gist; CREATE EXTENSION
接下来，创建一个gist索引，发现可以创建成功了，但是同时也看到，为含有500W行数据的数据表添加索引，耗时736秒。这个耗时比创建B Tree索引要大好多。
本文旨在介绍Gist和SP-Gist索引简单的创建方法，在实际过程中，这两种索引都有其合适的字段类型，并不建议在int等这些基础类型字段之上建立该两种索引。
stock_analysis_data=# create index mygistinx on test using gist(fund_code,record_time); CREATE INDEX Time: 736719.549 ms (12:16.720)
然后使用索引的第一个字段作为查询字段进行查询：
stock_analysis_data=# explain (analyze,verbose) select * from test where fund_code='160630';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.test (cost=52.34..3660.40 rows=1022 width=42) (actual time=27.033..986.891 rows=1024 loops=1)
Output: fund_code, fund_name, record_time
Recheck Cond: ((test.fund_code)::text = '160630'::text)
Heap Blocks: exact=1024
-> Bitmap Index Scan on mygistinx (cost=0.00..52.08 rows=1022 width=0) (actual time=21.551..21.552 rows=1024 loops=1)
Index Cond: ((test.fund_code)::text = '160630'::text)
Planning Time: 5.810 ms
Execution Time: 988.152 ms
(8 rows)
Time: 996.606 ms
我们看到查询过程中fund_code字段参与了索引扫描，但是查询效率比B Tree索引的查询效率要低。接下来，再来使用索引的第二个字段作为where条件：
stock_analysis_data=# explain (analyze,verbose) select * from test where record_time='2020-08-04 05:54:16.888';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using mygistinx on public.test (cost=0.41..8.43 rows=1 width=42) (actual time=171.587..197.805 rows=1024 loops=1)
Output: fund_code, fund_name, record_time
Index Cond: (test.record_time = '2020-08-04 05:54:16.888'::timestamp without time zone)
Planning Time: 2.326 ms
Execution Time: 198.430 ms
(5 rows)
Time: 201.279 ms
可以看到，即使没有使用索引的第一个字段参与where条件，照样参与了索引的扫描。
2.4 SP-Gist索引
SP-Gist索引是空间分区的Gist索引，可以说是为Gist索引的优化版本。简单来理解，化繁为简，把整棵索引树按照索引值划分成了若干个不相交的值域，在查找时按照值域的划分标准，按照域进行查找。创建SP-Gist索引的方法如下：
stock_analysis_data=# create index myspgistinx on test using spgist(fund_code); CREATE INDEX Time: 77385.665 ms (01:17.386)
使用SP-Gist索引进行查询：
stock_analysis_data=# explain (analyze,verbose) select * from test where fund_code='160630';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.test (cost=28.34..3636.41 rows=1022 width=42) (actual time=2.407..1001.485 rows=1024 loops=1)
Output: fund_code, fund_name, record_time
Recheck Cond: ((test.fund_code)::text = '160630'::text)
Heap Blocks: exact=829
-> Bitmap Index Scan on myspgistinx (cost=0.00..28.08 rows=1022 width=0) (actual time=0.267..0.268 rows=1024 loops=1)
Index Cond: ((test.fund_code)::text = '160630'::text)
Planning Time: 8.806 ms
Execution Time: 1002.603 ms
(8 rows)
Time: 1012.235 ms (00:01.012)
2.5 GIN索引
我们来看下Postgresql中最后一种索引类型——GIN索引，也就是反转索引。反转索引和Gist索引类似，也是可以进行自定义的。但是Postgresql的标准发布中也包含了用于一维数组的GIN操作符，基本满足了大部分的应用场景。
这里先解释下什么是反转索引，以及它常用的业务场景。比如我们在描述用户画像时，通常会给用户贴上各种各样的tag，而通常这些tag会存到数据库中一个数组类型的字段中。当我们需要根据某个tag快速找到所有满足这个tag的用户时，反转索引就派上了用场。如果没有反转索引，我们通常的做法就是遍历所有的数据，然后获取具有相同tag的用户，在查找性能上可见一斑。
其实GIN索引的实现原理也不算复杂，它的底层结构就是一个键值对的集合，如(‘java', '10:2 2:54')中。键是每个tag，而值是元组的TID（行号，包括数据块ID，大小为32 bit；以及item point，大小为16 bit）。
笔者使用一个新表User来演示GIN索引的用法。User的结构如下所示：
stock_analysis_data=# \d+ users Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('users_id_seq'::regclass) | plain | |
name | character varying(40) | | | | extended | |
tag | character varying(32)[] | | | | extended | |
在users表中创建gin索引：
stock_analysis_data=# create index mygininx on users using gin(tag); CREATE INDEX Time: 10.317 ms
通过Gin索引进行查询：
stock_analysis_data=# select * from users where tag @> array['java'::varchar(32)];
id | name | tag
----+------+---------------------
1 | 张三 | {java,c#,后端,前端}
3 | 李四 | {java,架构}
(2 rows)
Time: 14.935 ms
二、索引的创建
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
1. 关键字【UNIQUE】
#创建唯一索引；主键就是一种唯一索引
CREATE UNIQUE INDEX ind_t_id_1 on t (id);
2. 关键字【CONCURRENTLY】
# 这是并发创建索引。跟oracle的online创建索引作用是一样的。 # 创建索引过程中；不会阻塞表更新，插入，删除操作。当然创建的时间就会很漫长。 CREATE INDEX CONCURRENTLY ind_t_id_2 on t (id);
3. 关键字【IF NOT EXISTS】
# 用该命令是用于确认索引名是否存在。若存在；也不会报错。 CREATE INDEX IF NOT EXISTS ind_t_id_3 on t (id);
4. 关键字【USING】
# 创建哪种类型的索引。 默认是B-tree。 CREATE INDEX ind_t_id_4 on t using btree (id);
5 关键字【[ ASC | DESC ] [ NULLS { FIRST | LAST]】
# 创建索引是采用降序还是升序。 若字段存在null值，是把null值放在前面还是最后： # 例如采用降序，null放在前面。 CREATE INDEX ind_t_id_5 on t (id desc nulls first)
6. 关键字【WITH ( storage_parameter = value)】
#索引的填充因子设为。例如创建索引的填充因子设为75 CREATE INDEX ind_t_id_6 on t (id) with (fillfactor = 75);
7. 关键字【TABLESPACE】
#是把索引创建在哪个表空间。 CREATE INDEX ind_t_id_7 on t (id) TABLESPACE tsp_lottu;
8. 关键字【WHERE】
# 只在自己感兴趣的那部分数据上创建索引，而不是对每一行数据都创建索引， # 此种方式创建索引就需要使用WHERE条件了。 CREATE INDEX ind_t_id_8 on t (id) WHERE id < 1000;
三、索引的修改
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name ALTER INDEX name DEPENDS ON EXTENSION extension_name ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter = value [, ... ] ) ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] ) ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] SET TABLESPACE new_tablespace [ NOWAIT ]
- RENAME —— RENAME形式更改该索引的名称，这对已存储的数据没有 影响。
- SET TABLESPACE —— 更改索引的表空间为指定的表空间
- DEPENDS ON EXTENSION —— 这种形式把该索引标记为依赖于扩展，这样如果该扩展被删除，该索引也将被 自动删除。
- SET —— 这种形式为该索引更改一个或者多个索引方法相关的存储参数。
- RESET —— 这种形式把一个或者多个索引方法相关的存储参数重置为其默认值。正如 SET一样，可能需要一次REINDEX来完全更新 该索引。
四、索引的删除
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
- [ CONCURRENTLY ] —— 并发选项，在删除索引时不阻塞表上的插入、更新和删除。
- CASCADE —— 如果索引的字段作为了其它表的外键，则连同其它表的数据一起删除。
- RESTRICT —— 默认选项，如果索引字段作为了其它表的外键，则拒绝删除。
五、索引的重建
索引重建的含义是说：使用表中存储的数据重建一个索引，并覆盖掉旧的索引。遇到以下几种场景时，需要考虑进行索引的重建：
（1）索引由于软件或者硬件的原因已经失效或者损坏。
（2）索引包含了很多空的页，导致整个索引非常臃肿，空间利用率不高。
（3）索引修改了填充因子，希望通过索引重建即时生效。填充因子是索引的每个叶级页数据的填充比例，值从0到100不等。通过不满100的填充因子预留部分空间，是为了对将来数据的存储容量进行扩充。
（4）使用CONCURRENTLY并发创建索引失败时，留下了一个无效的索引，可以再重建它们。但是需要注意的是，在索引重建时无法重建并发索引，也就是CONCURRENTLY选项无效，如果想要在线重建索引，只能先删除索引再进行创建。
索引重建的语法如下：
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } name
- INDEX —— 重新创建指定的索引。
- TABLE —— 重新创建该表下面的所有索引
- SCHEMA —— 重新创建该模式下的所有索引
- DATABASE —— 重新创建该数据库下的所有索引
- SYSTEM —— 重新创建该数据库所在系统目录上的所有索引
- VERBOSE —— 打印进度报告
这篇关于Postgresql杂谈 04—Postgresql中的四种常规索引的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-01-05快速清空 PostgreSQL 数据库中的所有表格，让你的数据库重新焕然一新！
- 2024-01-04在PostgreSQL中创建角色：判断角色是否存在并创建
- 2023-05-16PostgreSQL一站式插件推荐 -- pg_enterprise_views
- 2022-11-22PostgreSQL 实时位置跟踪
- 2022-11-22如何将PostgreSQL插件移植到openGauss
- 2022-11-11PostgreSQL：修改数据库用户的密码
- 2022-11-06Windows 环境搭建 PostgreSQL 物理复制高可用架构数据库服务
- 2022-10-27Windows 环境搭建 PostgreSQL 逻辑复制高可用架构数据库服务
- 2022-10-11PostgreSql安装(Windows10版本)
- 2022-09-13PostgreSQL-Network Address类型操作和函数
