close

https://docs.postgresql.tw/reference/sql-commands/reindex

PostgreSql重建索引

重建單一索引：

REINDEX INDEX my_index;

 

REINDEX

REINDEX — 重建索引

大綱

REINDEX [ ( option[, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ]name

其中选项可以是以下之一：

    VERBOSE

描述

REINDEX使用索引的表裡儲存的資料重建一個索引， 並且替換該索引的舊拷貝。有一些場景需要使用REINDEX：

  • 一個索引已經損壞，並且不再包含合法資料。儘管理論上這不會發生， 實際上索引會因為軟體缺陷或硬體失效而損壞。 REINDEX提供了一種恢復方法。

  • 一個索引變得「臃腫」，其中包含很多空的或近乎為空的頁面。 PostgreSQL中的B-樹索引在特定的非常規存取模式下可能會發生這種情況。REINDEX 提供了一種方法來減少索引的空間消耗，即製造一個新版本的索引，其中沒有死亡頁面。詳見第 24.2 節。

  • 修改了一個索引的儲存參數（例如填充因子），並且希望確保這種修改完全生效。

  • 如果索引在以CONCURRENTLY選項建立失敗，則該索引保留為一個「invalid」。 這類索引是無用的，但是可以方便的REINDEX用來重建它們。注意，只有REINDEX INDEX可以在無效的索引上執行並發建立。

參數

INDEX

重新建立指定的索引。

TABLE

重新建立指定表的所有索引。如果該表有一個二級 「TOAST」表，它也會被重索引。

SCHEMA

重建指定方案的所有索引。如果這個方案中的一個表有次級的「TOAST」表，它也會被重建索引。共享系統目錄上的索引也會被處理。這種形式的REINDEX不能在事務塊內執行。

DATABASE

重新建立目前資料庫內的所有索引。共享的系統目錄上的索引也會被處理。這種形式的REINDEX不能在一個事務區塊內執行。

SYSTEM

重新建立目前資料庫中在系統目錄上的所有索引。共享系統目錄上的索引也被包含在內。用戶表上的索引則不會被處理。這種形式的 REINDEX不能在一個事務區塊內執行。

name

要被重索引的特定索引、表格或資料庫的名字。索引和表名可以被模式限定。目前，REINDEX DATABASE和 REINDEX SYSTEM只能重索引當前資料庫，因此它們的參數必須匹配當前資料庫的名稱。

CONCURRENTLY

使用此選項時，PostgreSQL將重建索引，而不在表上採取任何阻止並發插入、更新或刪除的鎖定; 標準的索引重建將會鎖定表上的寫入操作(而不是讀取操作)，直到它完成。 使用此選項—時，有幾個事項需要注意；請參閱下面的Rebuilding Indexes Concurrently。

對於臨時表，REINDEX始終是非並發的，因為沒有其他會話可以訪問它們，並且非並發重新索引更便宜。

VERBOSE

在每個索引被重建時列印進度報告。

註解

如果懷疑一個使用者資料表上的索引損壞，可以使用 REINDEX INDEX或 REINDEX TABLE簡單地重建該索引或資料表上的所有索引。

如果你需要從一個系統表上的索引損壞中恢復，就更困難一些。在這種情況下，對系統來說重要的是沒有使用過任何可疑的索引本身（ 實際上，在這種場景中，你可能會發現伺服器進程會在啟動時立刻崩潰， 這是因為對於損壞的索引的依賴）。要安全地恢復，伺服器必須用 -P選項啟動，這將阻止它使用索引來進行系統目錄查找。

這樣做的一種方法是關閉伺服器，並且啟動一個單一使用者的 PostgreSQL伺服器，在其命令列中包含-P選項。然後，可以發出 REINDEX DATABASE、REINDEX SYSTEM、 REINDEX TABLE或者REINDEX INDEX， 具體使用哪個指令取決於你想要重構多少東西。如果有疑問，可以使用 REINDEX SYSTEM來選擇重建資料庫中的所有系統索引。 然後退出單一用戶伺服器會話並且重新啟動常規的伺服器。更多關於如何與單一使用者伺服器介面互動的內容請參閱postgres參考頁。

在另一種方法中，可以開始一個常規的伺服器會話，在其命令列選項中包含-P。這樣做的方法與客戶端有關，但是在所有基於libpq的客戶端中都可以在開始客戶端之前設定PGOPTIONS環境變數為-P。 注意雖然這種方法不要求用鎖排斥其他客戶端，在修復完成之前避免其他使用者連接到受損的資料庫才是更明智的。

REINDEX類似於刪除索引並且重建索引，在其中索引內容會被從頭開始建立。不過，鎖定方面的考量卻相當不同。 REINDEX會用鎖定排斥寫，但不會排斥在索引的父表上的讀。 它也會在被處理的索引上取得一個排他鎖，該鎖將會阻塞對該索引的使用嘗試。 相反，DROP INDEX會暫時在附表上取得一個排他鎖，阻塞寫和讀。後續的CREATE INDEX會排斥寫入但不排斥讀，由於該索引不存在，所以不會有讀取它的嘗試，這意味著不會有阻塞但是讀取操作可能被強製成昂貴的順序掃描。

重索引單獨一個索引或表格要求使用者是該索引或資料表的擁有者。對方案或資料庫重建索引要求是該方案或資料庫的擁有者。請特別注意，因此非超級使用者有時無法重建其他使用者擁有的表上的索引。不過，作為一種特例，當一個非超級使用者發出REINDEX DATABASE、REINDEX SCHEMA或REINDEX SYSTEM時，共享目錄上的索引將被跳過，除非該使用者擁有該目錄（通常不會是這樣）。當然，超級用戶總是可以重建所有的索引。

不支援重建分區表的索引或分區索引。不過可以單獨為每個分割區重建索引。

Rebuilding Indexes Concurrently

重建索引可能會影響資料庫的常規操作。通常PostgreSQL會鎖定重建的表以防止寫入操作，並透過單次掃描表來執行整個索引建置。 其他事務仍可以讀取表，但如果它們嘗試在表中插入、更新或刪除行，它們將被阻止，直到索引重建完成。 如果系統是即時生產資料庫，這可能會產生嚴重影響。非常大的表可能需要幾個小時才能編制索引，即使對於較小的表，索引重建也會鎖定編寫器，這些時間段對於生產系統來說是不可接受的。

PostgreSQL支援以最少的寫入鎖定來重建索引。此方法透過指定REINDEX的CONCURRENTLY選項來呼叫。 使用此選項時，PostgreSQL必須對需要重新產生的每個索引執行兩次表掃描，並等待可能使用索引的所有現有事務的終止。 此方法需要比標準索引重建更大的工作量，並且需要相當長的時間才能完成，因為它需要等待可能修改索引的未完成的事務。 但是，由於它允許在重建索引時繼續正常操作，因此此方法可用於在生產環境中重建索引。當然，重建索引所需的額外CPU、記憶體和I/O 負載可能會減慢其他操作的速度。

以下步驟發生在並發重建索引。 每個步驟在單獨的事務中運作。 如果要重建多個索引，則每個步驟在進入到下一個步驟之前都要循環遍歷所有索引。

  1. 新的臨時索引定義將會加入到目錄pg_index中。 此定義將用於取代舊索引。 一個SHARE UPDATE EXCLUSIVE會話層級的鎖定將放在要重建的索引以及其關聯的表上，以防止處理時的任何模式修改。

  2. 為每個新索引完成產生索引的首個操作。 產生索引後，其標誌pg_index.indisready切換到「true」使其準備好插入，使其在執行產生的事務完成後對其他會話可見。 此步驟在每個索引的單獨事務中完成。

  3. 然後執行第二個操作以新增在第一個操作運行時新增的元組。此步驟也在每個索引的單獨事務中完成。

  4. 引用索引的所有約束都已變更以引用新的索引定義，索引名稱也已變更。 此時，pg_index.indisvalid會為新索引切換到「true」，以及為舊索引切換到「false」，且快取無效會導致引用舊索引的所有會話失效。

  5. 舊索引有pg_index.indisready切換到「false」以防止任何新的元組插入，在等待可能引用舊索引的查詢之後完成。

  6. 舊索引被丟棄。索引和表的SHARE UPDATE EXCLUSIVE會話鎖定被釋放。

 

如果在重建索引時出現問題，例如唯一索引中的唯一性衝突，REINDEX命令將失敗，但會留下一個「invalid」新索引，在已經存在的索引之外。 出於查詢目的此索引將被忽略，因為它可能不完整;但是它仍將消耗更新開銷。psql \d指令將此類索引回報為INVALID:

postgres=# \d tab
       Table "public.tab"
 Column | Type | Modifiers
--------+---------+-----------
 col | integer |
Indexes:
    "idx" btree (col)
    "idx_ccnew" btree (col) INVALID

如果標記為INVALID的索引後綴為ccnew，那麼它對應的是並發操作時創建的臨時索引，建議的恢復方法是使用DROP INDEX刪除，然後再次嘗試REINDEX CONCURRENTLY。 如果無效索引改為後綴ccold，則對應於無法刪除的原始索引； 建議的復原方法是刪除所述索引，因為正確的重建已經成功。

常規索引建立允許在同一表上的其他常規索引建立同時發生，但在一個表上一次只能發生一個並發索引建立。在這兩種情況下，不允許同時對錶上其他類型的模式進行修改。 另一個區別是，常規REINDEX TABLE或REINDEX INDEX命令可以在事務區塊中執行，但REINDEX CONCURRENTLY不能執行。

REINDEX SYSTEM不支援CONCURRENTLY因為系統目錄不能並發重新索引。

此外，排除約束的索引不能並發重新編制索引。 如果此命令中直接命名了這樣的索引，則會引發錯誤。 如果並發重新編制具有排除約束索引的資料表或資料庫，則會跳過這些索引。 （它可以不使用CONCURRENTLY選項來重新編制這樣的索引）。

範例

重建單一索引：

REINDEX INDEX my_index;

 

重建表my_table上的所有索引：

REINDEX TABLE my_table;

 

重建一個特定資料庫中的所有索引，且不假設系統索引已經可用：

$ export PGOPTIONS="-P"
$psql broken_db
…
broken_db=> REINDEX DATABASE broken_db;
broken_db=> \q

重建表的索引，在重建索引過程中不阻止對相關關係進行讀寫操作：

REINDEX TABLE CONCURRENTLY my_broken_table;

相容性

在SQL 標準中沒有REINDEX指令。

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 dizzy03 的頭像
    dizzy03

    碎碎念

    dizzy03 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄