索引相关操作 · PHP知识总结

[TOC] # **索引管理：** ## **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 ``` * CASCADE：自动删除依赖于该索引的对象 * 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`可以在无效的索引上执行并发创建 **参数：** * INDEX：重新创建指定的索引 * TABLE：重新创建指定表的所有索引。如果该表有一个二级“TOAST”表，它也会被重索引 * SCHEMA：重建指定方案的所有索引。如果这个方案中的一个表有次级的“TOAST”表，它也会被重建索引。共享系统目录上的索引也会被处理。这种形式的`REINDEX`不能在事务块内执行 * DATABASE：重新创建当前数据库内的所有索引。共享的系统目录上的索引也会被处理。这种形式的`REINDEX`不能在一个事务块内执行。 * SYSTEM：重新创建当前数据库中在系统目录上的所有索引。共享系统目录上的索引也被包括在内。用户表上的索引则不会被处理。这种形式的`REINDEX`不能在一个事务块内执行。 * name：要被重建索引的特定索引、表或者数据库的名字。索引和表名可以被模式限定。当前，`REINDEX DATABASE`和`REINDEX SYSTEM`只能重建当前数据库的索引，因此它们的参数必须匹配当前数据库的名称 * 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 ``` ## **3. 重建索引** 1. 删除旧索引，然后重新创建新索引 2. 使用reindex进行重建索引，如果不使用`CONCURRENTLY`选项，重建索引期间会锁表 3. `vacuum full`，对表进行重构，索引也会重建，同样也会锁表