本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # 重建索引 PostgreSQL [REINDEX](https://www.postgresql.org/docs/current/sql-reindex.html) 命令會使用儲存在索引資料表中的資料來重建索引，並取代索引的舊複本。我們建議您`REINDEX`在下列案例中使用 ： + 當索引損毀且不再包含有效資料時。這可能因軟體或硬體故障而發生。 + 當先前使用索引的查詢停止使用時。 + 當索引以大量空白或幾乎空白的頁面膨脹時。當膨脹百分比 (`bloat_pct`) 大於 20 `REINDEX`時，您應該執行 。 下列查詢可協助您尋找 `bloat_pct`： ``` SELECT current_database(), nspname AS schemaname, tblname, idxname, bs*(relpages)::bigint AS real_size, bs*(relpages-est_pages)::bigint AS extra_size, 100 * (relpages-est_pages)::float / relpages AS extra_pct, fillfactor, CASE WHEN relpages > est_pages_ff THEN bs*(relpages-est_pages_ff) ELSE 0 END AS bloat_size, 100 * (relpages-est_pages_ff)::float / relpages AS bloat_pct, is_na -- , 100-(pst).avg_leaf_density AS pst_avg_bloat, est_pages, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples, relpages -- (DEBUG INFO) FROM ( SELECT coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)/(4+nulldatahdrwidth)::float)), 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth) ) AS est_pages, coalesce(1 + ceil(reltuples/floor((bs-pageopqdata-pagehdr)*fillfactor/(100*(4+nulldatahdrwidth)::float))), 0 ) AS est_pages_ff, bs, nspname, tblname, idxname, relpages, fillfactor, is_na -- , pgstatindex(idxoid) AS pst, index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, reltuples -- (DEBUG INFO) FROM ( SELECT maxalign, bs, nspname, tblname, idxname, reltuples, relpages, idxoid, fillfactor, ( index_tuple_hdr_bm + maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign ELSE index_tuple_hdr_bm%maxalign END + nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN WHEN nulldatawidth = 0 THEN 0 WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign ELSE nulldatawidth::integer%maxalign END )::numeric AS nulldatahdrwidth, pagehdr, pageopqdata, is_na -- , index_tuple_hdr_bm, nulldatawidth -- (DEBUG INFO) FROM ( SELECT n.nspname, i.tblname, i.idxname, i.reltuples, i.relpages, i.idxoid, i.fillfactor, current_setting('block_size')::numeric AS bs, CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS maxalign, /* per page header, fixed size: 20 for 7.X, 24 for others */ 24 AS pagehdr, /* per page btree opaque data */ 16 AS pageopqdata, /* per tuple header: add IndexAttributeBitMapData if some cols are null-able */ CASE WHEN max(coalesce(s.null_frac,0)) = 0 THEN 8 -- IndexTupleData size ELSE 8 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8) END AS index_tuple_hdr_bm, /* data len: we remove null values save space using it fractionnal part from stats */ sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 1024)) AS nulldatawidth, max( CASE WHEN i.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na FROM ( SELECT ct.relname AS tblname, ct.relnamespace, ic.idxname, ic.attpos, ic.indkey, ic.indkey[ic.attpos], ic.reltuples, ic.relpages, ic.tbloid, ic.idxoid, ic.fillfactor, coalesce(a1.attnum, a2.attnum) AS attnum, coalesce(a1.attname, a2.attname) AS attname, coalesce(a1.atttypid, a2.atttypid) AS atttypid, CASE WHEN a1.attnum IS NULL THEN ic.idxname ELSE ct.relname END AS attrelname FROM ( SELECT idxname, reltuples, relpages, tbloid, idxoid, fillfactor, indkey, pg_catalog.generate_series(1,indnatts) AS attpos FROM ( SELECT ci.relname AS idxname, ci.reltuples, ci.relpages, i.indrelid AS tbloid, i.indexrelid AS idxoid, coalesce(substring( array_to_string(ci.reloptions, ' ') from 'fillfactor=([0-9]+)')::smallint, 90) AS fillfactor, i.indnatts, pg_catalog.string_to_array(pg_catalog.textin( pg_catalog.int2vectorout(i.indkey)),' ')::int[] AS indkey FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class ci ON ci.oid = i.indexrelid WHERE ci.relam=(SELECT oid FROM pg_am WHERE amname = 'btree') AND ci.relpages > 0 ) AS idx_data ) AS ic JOIN pg_catalog.pg_class ct ON ct.oid = ic.tbloid LEFT JOIN pg_catalog.pg_attribute a1 ON ic.indkey[ic.attpos] <> 0 AND a1.attrelid = ic.tbloid AND a1.attnum = ic.indkey[ic.attpos] LEFT JOIN pg_catalog.pg_attribute a2 ON ic.indkey[ic.attpos] = 0 AND a2.attrelid = ic.idxoid AND a2.attnum = ic.attpos ) i JOIN pg_catalog.pg_namespace n ON n.oid = i.relnamespace JOIN pg_catalog.pg_stats s ON s.schemaname = n.nspname AND s.tablename = i.attrelname AND s.attname = i.attname GROUP BY 1,2,3,4,5,6,7,8,9,10,11 ) AS rows_data_stats ) AS rows_hdr_pdg_stats ) AS relation_stats ORDER BY nspname, tblname, idxname; ``` 將回收完全空白的索引頁面以供重複使用。不過，如果頁面上的索引鍵已刪除，但仍配置空間，建議您定期重新索引。 重新建立索引有助於提供更好的查詢效能。您可以透過三種方式重新建立索引，如下表所述。 | | | | | --- |--- |--- | | **方法** | **描述** | **限制** | | `CREATE INDEX` 和 `DROP INDEX` 搭配 `CONCURRENTLY`選項 | 建立新的索引並移除舊的索引。最佳化工具會使用新建立的索引而非舊的索引來產生計劃。在低尖峰時段，您可以捨棄舊索引。 | 當您使用 `CONCURRENTLY`選項時，索引建立需要更多時間，因為它必須追蹤所有傳入的變更。凍結變更時，程序會標示為完成。 | | `REINDEX` 使用 `CONCURRENTLY`選項 | 在重建程序期間鎖定寫入操作。PostgreSQL 第 12 版** **和更新版本提供 `CONCURRENTLY`選項，可避免這些鎖定。 | 使用 `CONCURRENTLY`需要更長的時間來重建索引。 | | `pg_repack` 延伸模組 | 從資料表清除膨脹並重建索引。 | 您必須從連接到資料庫的 EC2 執行個體或本機電腦執行此擴充功能。 | ## 建立新的索引 `DROP INDEX` 和 `CREATE INDEX`命令一起使用時，請重建索引： ``` DROP INDEX CREATE INDEX ON TABLE ([,]) ``` 此方法的缺點是其對資料表的專屬鎖定，這會影響此活動期間的效能。`DROP INDEX` 命令會取得專屬鎖定，以封鎖資料表上的讀取和寫入操作。`CREATE INDEX` 命令會封鎖資料表上的寫入操作。它允許讀取操作，但在索引建立期間這些操作非常昂貴。 ## 重建索引 `REINDEX` 命令可協助您維持一致的資料庫效能。當您對資料表執行大量 DML 操作時，這會導致資料表和索引膨脹。索引用於加速查詢資料表，以改善查詢效能。索引膨脹會影響查詢和查詢效能。因此，建議您對具有大量 DML 操作的資料表執行重新索引，以維持查詢效能的一致性。 `REINDEX` 命令會透過鎖定基礎資料表上的寫入操作，從頭開始重建索引，但允許資料表上的讀取操作。不過，它會封鎖索引上的讀取操作。使用對應索引的查詢會遭到封鎖，但其他查詢不會遭到封鎖。 PostgreSQL 第 12 版推出新的選用參數 `CONCURRENTLY`，該參數會從頭開始重建索引，但不會鎖定資料表或使用索引的查詢上的寫入或讀取操作。不過，使用此選項時，完成程序需要較長的時間。 ## 範例 **建立和捨棄索引** 使用 `CONCURRENTLY`選項建立新的索引： ``` create index CONCURRENTLY on table(columns) ; ``` 使用 `CONCURRENTLY`選項捨棄舊索引： ``` drop index CONCURRENTLY ; ``` **重建索引** 若要重建單一索引： ``` reindex index ; ``` 若要重建資料表中的所有索引： ``` reindex table ; ``` 若要重建結構描述中的所有索引： ``` reindex schema ; ``` **同時重建索引** 若要重建單一索引： ``` reindex index CONCURRENTLY ; ``` 若要重建資料表中的所有索引： ``` reindex table CONCURRENTLY ; ``` 若要重建結構描述中的所有索引： ``` reindex schema CONCURRENTLY ; ``` **僅重建或重新放置索引** 若要重建單一索引： ``` pg_repack -h -d -i -k ``` 若要重建所有索引： ``` pg_repack -h -d -x -t -k ```