索引相關操作 · 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`，對表進行重構，索引也會重建，同樣也會鎖表