当前位置：首页 > PHP实战中知识总结 > PgSQL - 索引相关操作

PHP实战中知识总结 / PgSQL - 索引相关操作

一、索引管理

1、创建索引

postgres=# \h create index
Command:   CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
  ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
  [ INCLUDE ( column_name [, ...] ) ]
  [ WITH ( storage_parameter [= value] [, ... ] ) ]
  [ TABLESPACE tablespace_name ]
  [ WHERE predicate ]
URL: https://www.postgresql.org/docs/13/sql-createindex.html

说明：

（1）创建索引可能会干扰数据库的常规操作。通常PostgreSQL会锁住要被索引的表，让它不能被写入，并且用该表上的一次扫描来执行整个索引的构建。其他事务仍然可以读取表，但是如果它们尝试在该表上进行插入、更新或者删除，它们会被阻塞直到索引构建完成。

（2）PostgreSQL支持构建索引时不阻塞写入。这种方法通过指定CREATE INDEX的CONCURRENTLY选项实现。

命令| 解释
CREATE UNIQUE INDEX unique_index_id on test (id);| 关键字【UNIQUE】，创建唯一索引。
CREATE INDEX CONCURRENTLY concurrent_index_id on t (id);| 关键字【CONCURRENTLY】，不阻塞事务创建索引，建索引过程中；不会阻塞表更新，插入，删除操作。
CREATE INDEX IF NOT EXISTS index_test_id on test (id);| 关键字【IF NOT EXISTS】，创建索引时确认索引名是否存在。若存在；也不会报错。
CREATE INDEX index_gin_id on test using gin (id);| 关键字【USING】，指定创建索引的类型。
CREATE INDEX index_order_id on test (id desc nulls first);| 关键字【[ ASC | DESC ] [ NULLS { FIRST | LAST]】，创建索引是采用降序还是升序。 若字段存在null值，是把null值放在前面还是最后：例如采用降序，null放在前面。
CREATE INDEX index_storage_id on test (id) with (fillfactor = 75);| 关键字【WITH ( storage_parameter = value)】，创建索引时指定条件。例如：设置创建索引时的填充因子为75。
CREATE INDEX index_test_id on test (id) TABLESPACE tsp_lottu;| 关键字【TABLESPACE】，指定索引创建在哪个表空间。
CREATE INDEX index_test_id on test (id) WHERE id > 1000;| 关键字【WHERE】，创建索引时指定条件，而不是对每一行数据都创建索引。

2、查看索引

可以通过系统表pg_indexes表来查看对应的表中已创建的索引。

pg_indexes表提供了对数据库中每个索引的有用信息的访问。字段为：schemaname（模式名）、tablename（表名）、indexname（索引名）、tablespace（表空间）、indexdef（索引定义）

postgres=# select * from pg_indexes where tablename='test';
 schemaname | tablename |  indexname  | tablespace |                indexdef
------------+-----------+---------------+------------+-------------------------------------------------------------------------
 public   | test   | test_pkey   |      | CREATE UNIQUE INDEX test_pkey ON public.test USING btree (id)
 public   | test   | t_index_union |      | CREATE INDEX t_index_union ON public.test USING btree (name, age, sale)
(2 rows)

3、修改索引

postgres=# \h alter index
Command:   ALTER INDEX
Description: change the definition of an index
Syntax:
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_name
ALTER INDEX name ATTACH PARTITION index_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 [ IF EXISTS ] name ALTER [ COLUMN ] column_number
  SET STATISTICS integer
ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
  SET TABLESPACE new_tablespace [ NOWAIT ]
URL: https://www.postgresql.org/docs/13/sql-alterindex.html

命令	解释
ALTER INDEX [ IF EXISTS ] name RENAME TO new_name	重命名索引
ALTER INDEX [ IF EXISTS ] name SET TABLESPACE tablespace_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 ]	把表空间中索引迁移到新表空间。

4、删除索引

postgres=# \h drop index
Command:   DROP INDEX
Description: remove an index
Syntax:
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
URL: https://www.postgresql.org/docs/13/sql-dropindex.html

（1）CASCADE：自动删除依赖于该索引的对象。

（2）RESTRICT：如果有依赖对象存在，则拒绝删除该索引。这个是缺省。

说明：

（1）删除索引并不阻塞在索引基表上的并发选择、插入、更新和删除操作。

（2）一个普通的DROP INDEX会要求该表上的排他锁，这样会阻塞其他访问直至索引删除完成。

命令	解释
DROP INDEX CONCURRENTLY t_index_union CASCADE	删除索引，并自动删除依赖于该索引的对象。

5、重建索引

postgres=# \h reindex;
Command:   REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
where option can be one of:
  VERBOSE
URL: https://www.postgresql.org/docs/13/sql-reindex.html

使用场景：

（1）一个索引已经损坏，并且不再包含合法数据。尽管理论上这不会发生，实际上索引会因为软件缺陷或硬件失效损坏。

（2）一个索引变得“臃肿”，其中包含很多空的或者近乎为空的页面。PostgreSQL中的 B-树索引在特定的非常规访问模式下可能会发生这种情况。REINDEX提供了一种方法来减少索引的空间消耗。

（3）修改了一个索引的存储参数（例如填充因子），并且希望确保这种修改完全生效。

（4）如果索引在用CONCURRENTLY选项创建失败，该索引保留为一个“invalid”。这类索引是无用的，但是可以方便的用REINDEX来重建它们。注意，只有REINDEX INDEX可以在无效的索引上执行并发创建。

参数：

（1）INDEX：重新创建指定的索引。

（2）TABLE：重新创建指定表的所有索引。如果该表有一个二级“TOAST”表，它也会被重索引。

（3）SCHEMA：重建指定方案的所有索引。如果这个方案中的一个表有次级的“TOAST”表，它也会被重建索引。共享系统目录上的索引也会被处理。这种形式的REINDEX不能在事务块内执行。

（4）DATABASE：重新创建当前数据库内的所有索引。共享的系统目录上的索引也会被处理。这种形式的REINDEX不能在一个事务块内执行。

（5）SYSTEM：重新创建当前数据库中在系统目录上的所有索引。共享系统目录上的索引也被包括在内。用户表上的索引则不会被处理。这种形式的REINDEX不能在一个事务块内执行。

（6）name：要被重建索引的特定索引、表或者数据库的名字。索引和表名可以被模式限定。当前，REINDEX DATABASE和REINDEX SYSTEM只能重建当前数据库的索引，因此它们的参数必须匹配当前数据库的名称。

（7）CONCURRENTLY：使用此选项时，PostgreSQL将重建索引，而不在表上采取任何阻止并发插入、更新或删除的锁; 标准的索引重建将会锁定表上的写操作(而不是读操作)，直到它完成。

说明：

（1）REINDEX不支持重建分区表的索引或者分区索引，不过可以单独为每个分区重建索引。

（2）REINDEX重建索引期间，PostgreSQL会锁定重建的表以防止写操作，并通过单次扫描表来执行整个索引构建。其他事务仍可以读取表。

（3）PostgreSQL支持以最少的写入锁定来重建索引。此方法通过指定REINDEX的CONCURRENTLY选项来调用。使用此选项时，PostgreSQL必须对需要重新生成的每个索引执行两次表扫描，并等待可能使用索引的所有现有事务的终止。此方法需要比标准索引重建更大的工作量，并且需要相当长的时间才能完成，因为它需要等待可能修改索引的未完成的事务。

（4）如果要恢复系统表损坏的索引，服务器必须用-P选项启动，然后使用REINDEX来重建索引。

命令	解释
REINDEX INDEX my_index;	重建单个索引。
REINDEX TABLE my_table;	重建表my_table上的所有索引。
REINDEX TABLE CONCURRENTLY my_broken_table;	重建表的索引，在重建索引过程中不阻止对相关关系进行读写操作。
$ export PGOPTIONS="-P" $ psql broken_db ... broken_db=> REINDEX DATABASE broken_db;	重建一个特定数据库中的所有索引。

二、索引维护

1、查看索引大小

// 查看alerts表的索引所占磁盘空间大小
postgres=# select pg_size_pretty(pg_indexes_size('alerts'));
 pg_size_pretty
----------------
 149 MB
(1 row)
// 查看指定索引所占磁盘空间大小
postgres=# select pg_size_pretty(pg_relation_size('index_attack_alarm_event_desc'));
 pg_size_pretty
----------------
 23 MB
(1 row)

2、查看索引的利用率

// 可以通过`pg_stat_user_indexes`表查看索引的扫描次数
postgres=# select * from pg_stat_user_indexes;
 relid | indexrelid | schemaname | relname |      indexrelname       | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+---------+-------------------------------------+----------+--------------+---------------
 16384 |   16387 | public   | test  | test_pkey              |   2005 |    202003 |    202003
 16391 |   16400 | public   | alerts | index_attack_alarm_attack_result  |    2 |    20169 |      40
 16391 |   16401 | public   | alerts | index_attack_alarm_attack_type   |    0 |      0 |       0