Postgresql杂谈 04—Postgresql中的五种常规索引_postgres create index using

一、索引的分类

        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 —— 打印进度报告