找出並修正不一致的索引

資料庫索引不一致的原因有很多，包括軟體瑕疵、硬體問題，或排序順序變更等行為的基礎異動。

PostgreSQL 社群已建構相關工具，可找出並修正這類問題。這包括 amcheck 等工具，PostgreSQL 社群建議使用這類工具找出一致性問題，包括先前版本的 PostgreSQL 14 曾出現的問題。

如果 Cloud SQL 使用者遇到這些一致性問題，可以參考這份劇本。這份教戰手冊提供相關資訊，協助 PostgreSQL 使用者找出並修正不一致的索引。

如要解決索引不一致的問題，請按照下列步驟操作：

1. 事前準備。

   開始重新建立索引前，請先備份資料庫、設定正確的權限、確認 psql 用戶端版本，並下載 amcheck 擴充功能。

2. 檢查索引是否不一致。

   其中一個陳述式會找出違反唯一和主鍵的項目，另一個陳述式則會偵測各種其他不一致的項目。

3. 修正索引的不一致問題。

   重新建立索引可修正所有不一致的問題。您可能需要調整執行個體的記憶體設定，才能提升效能。

4. 監控重新建立索引作業。

   建議您監控重新建立索引作業的進度，確保作業順利進行，不會遭到封鎖。

5. 確認索引一致。

   成功重新建立索引後，建議您確認索引是否含有任何不一致之處。

事前準備

備份資料庫

為確保重新建立索引期間不會遺失資料，建議您備份資料庫。詳情請參閱建立按照需求執行的備份。

注意： 如果資料庫的索引不一致，還原備份時仍會出現不一致的情況。

設定 cloudsqlsuperuser 權限

如要完成這個頁面的步驟，您必須具備cloudsqlsuperuser權限。詳情請參閱 session_replication_role。

確認 psql 用戶端版本為 9.6 以上

如要完成本頁面的步驟，請務必確認您的 psql 用戶端版本為 9.6 以上。執行 psql --version 指令，驗證目前的 psql 用戶端版本。

安裝 amcheck 擴充功能

如要檢查索引不一致情形，請啟用「amcheck」擴充功能。

PostgreSQL 9.6

注意：您必須先安裝 POSTGRES_9_6_24.R20220710.01_12，才能安裝 amcheck_next。如要進一步瞭解如何安裝新的目標維護版本，請參閱「自助式維護」。

如要安裝 PostgreSQL 9.6 的 amcheck，請執行下列陳述式：

  CREATE EXTENSION amcheck_next;
  

如果收到 `Could not open extension control file...` 錯誤訊息，請確認您執行的目標維護版本正確無誤 (POSTGRES_9_6_24.R20220710.01_12)。

PostgreSQL 10 以上版本

如要安裝 PostgreSQL 10 以上版本的 amcheck，請執行下列陳述式：

  CREATE EXTENSION amcheck;
  

檢查索引是否不一致

以下各節說明如何檢查索引不一致的情形，包括檢查索引的不一致性，以及違反唯一鍵和主鍵的情形。

檢查不一致之處

在每個資料庫中，執行下列陳述式來檢查不一致情形：

程式碼範例

  DO $$
  DECLARE
    r RECORD;
    version varchar(100);
  BEGIN
    RAISE NOTICE 'Started relhasindex validation on database: %', current_database();
    FOR r IN
      SELECT indexrelid::regclass relname
        FROM pg_index
      WHERE indrelid NOT IN (SELECT oid FROM pg_class WHERE relhasindex) LOOP
        RAISE LOG 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
        RAISE WARNING 'Failed to check index %: %', r.relname, 'relhasindex is false, want true';
    END LOOP;
    RAISE NOTICE 'Finished relhasindex validation on database: %', current_database();

    RAISE NOTICE 'Started b-tree amcheck on database: %', current_database();
    SHOW server_version into version;
    SELECT split_part(version, '.', 1) into version;
    FOR r IN
      SELECT c.oid, c.oid::regclass relname, i.indisunique
        FROM pg_index i
        JOIN pg_opclass op ON i.indclass[0] = op.oid
        JOIN pg_am am ON op.opcmethod = am.oid
        JOIN pg_class c ON i.indexrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
      WHERE am.amname = 'btree'
        AND c.relpersistence != 't'
        AND c.relkind = 'i'
        AND i.indisready AND i.indisvalid LOOP
      BEGIN
        RAISE NOTICE 'Checking index %:', r.relname;
        IF version = '10' THEN
          PERFORM bt_index_check(index => r.oid);
        ELSE
          PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
        END IF;
      EXCEPTION
        WHEN undefined_function THEN
          RAISE EXCEPTION 'Failed to find the amcheck extension';
        WHEN OTHERS THEN
          RAISE LOG 'Failed to check index %: %', r.relname, sqlerrm;
          RAISE WARNING 'Failed to check index %: %', r.relname, sqlerrm;
      END;
    END LOOP;
    RAISE NOTICE 'Finished b-tree amcheck on database: %', current_database();
  END $$;
  

您會看到類似下方的輸出內容：

輸出

  WARNING:  Failed to check index t_i_key: relhasindex is false, want true
  NOTICE:  Checking index t_pkey:
  NOTICE:  Checking index t_i_key:
  WARNING:  Failed to check index t_i_key: item order invariant violated for index "t_i_key"
  NOTICE:  Checking index t_j_key:
  WARNING:  Failed to check index t_j_key: item order invariant violated for index "t_j_key"
  NOTICE:  Checking index ij:
  WARNING:  Failed to check index ij: item order invariant violated for index "ij"
  

警告： 如果輸出內容未顯示警告通知，可能仍存在獨特或重大違規事項。如要檢查違規事項，請按照「找出並修正不重複和主鍵違規事項

」一文中的程序操作。

如要進一步瞭解如何查看 PostgreSQL 記錄，請參閱「查看執行個體記錄」。

找出並修正違反不重複和主鍵限制的問題

本節說明如何檢查索引是否有違反唯一和主鍵的行為，以及如何修正這類問題。

找出違反唯一鍵的行為

您必須先修正違反唯一鍵的項目，才能重新建立索引。如要檢查所有違反唯一鍵的行為，請在每個資料庫中執行下列指令：

程式碼範例

  WITH q AS (
      /* this gets info for all UNIQUE indexes */
      SELECT indexrelid::regclass as idxname,
            indrelid::regclass as tblname,
            indcollation,
            pg_get_indexdef(indexrelid),
            format('(%s)',(select string_agg(quote_ident(attname), ', ')
                from pg_attribute a
                join unnest(indkey) ia(nr) on ia.nr = a.attnum
              where attrelid = indrelid)) as idxfields,
            COALESCE(substring(pg_get_indexdef(indexrelid) FROM '[)] (WHERE .*)$'), '') as whereclause
        FROM pg_index
      WHERE indisunique
      /* next line excludes indexes not affected by collation changes */
        AND trim(replace(indcollation::text, '0', '')) != ''
  )
  SELECT
  /* the format constructs the query to execute for each index */
  format(
  $sql$
  DO $$ BEGIN RAISE NOTICE 'checking index=%3$I    on   table=%1$I      key_columns=%2$I '; END;$$;
  SELECT this,
        prev,
        /* we detect both reversed ordering or just not unique */
        (CASE WHEN this = prev THEN 'DUPLICATE' ELSE 'BACKWARDS' END) as violation_type
    FROM (SELECT %2$s AS this,
                lag(%2$s) OVER (ORDER BY %2$s) AS prev
            FROM %1$s %4$s
          ) s
  WHERE this <= prev and this IS NOT NULL and prev IS NOT NULL; /* change to just '<' if looking for reverse order in index */
  $sql$, tblname, idxfields, idxname, whereclause
  )
    FROM q
  -- LIMIT 20 /* may use limit for testing */
  -- the next line tells psql to executes this query and then execute each returned line separately
  \gexec
  

指令碼的輸出內容會與下列內容類似：

輸出

  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
  NOTICE:  checking index=games_title_key on table=games  key_columns="(title)"
          this        |        prev        | violation_type
  --------------------+--------------------+----------------
  Game #16 $soccer 2  | Game #16 $soccer 2 | DUPLICATE
  Game #18 $soccer 2  | Game #18 $soccer 2 | DUPLICATE
  Game #2 $soccer 2   | Game #2 $soccer 2  | DUPLICATE
  Game #5 $soccer 2   | Game #5 $soccer 2  | DUPLICATE
  

在這個輸出內容中，表格標題 NOTICE 會顯示索引、資料欄和表格，如果輸出內容包含顯示 DUPLICATE 或 BACKWARDS 的資料列，表示索引已損毀，可能需要修正。資料列中的 BACKWARDS 表示可能隱藏重複值。如果表格中出現這兩項其中之一，請參閱「修正重複鍵違規問題」。

修正重複鍵違規問題

如果發現重複的專屬索引，或重新建立索引作業因重複鍵違規錯誤而失敗，請完成下列步驟，找出並移除重複的鍵。

注意： 您必須針對每個有索引鍵違規情形的索引重複下列步驟。

1. 從 NOTICE 表格標題中擷取 key_columns，如上述範例輸出內容所示。在以下範例中，鍵列為 email。

   程式碼範例

     NOTICE:  checking index=users_email_key on table=users key_columns="(email)"

   在步驟 3 的查詢中，使用 KEY_COLUMNS 中的這些值。

2. 找出資料表的結構定義。使用 psql 連線至資料庫，然後執行下列指令：

   程式碼範例

   \dt TABLE_NAME

   「schema」欄中的值是您在步驟 3 的查詢中，為「SCHEMA_NAME」使用的值。

   舉例來說，如果查詢如下：

    \dt games
    

   輸出結果會與下列內容相似：

    List of relations
    Schema  | Name  | Type  | Owner
    --------+-------+-------+----------
    public  | games | table | postgres
    (1 row)
    

3. 執行下列陳述式，強制進行完整資料表掃描，並取得重複的鍵。

   程式碼範例

   SET enable_indexscan = off;
   SET enable_bitmapscan = off;
   SET enable_indexonlyscan = off;
   
   SELECT KEY_COLUMNS, count(*)
     FROM SCHEMA_NAME.TABLE_NAME
   GROUP BY KEY_COLUMNS
   HAVING count(*) > 1;

   注意： 如果資料庫很大，這項陳述式可能需要很長時間才能完成。

   在上述陳述式中，KEY_COLUMNS 是您要檢查的資料表中，由不重複索引或主鍵涵蓋的一或多個資料欄。您在檢查違反唯一鍵限制的項目時，會發現這些項目。這項陳述式會傳回重複的鍵，以及每個鍵的重複次數。

   舉例來說，如果查詢如下：

     SELECT name,count(*)
       FROM public.TEST_NAMES
     GROUP BY name
     HAVING count(*) > 1;
     

   輸出結果會與下列內容相似：

     name                | count
     --------------------+-------
     Johnny              |     2
     Peter               |     2
     (2 rows)
     

   在這種情況下，請繼續下一個步驟，移除重複的鍵。

   如果 KEY_COLUMNS 中的任何資料欄為空值，您可以忽略這些資料欄，因為唯一限制不適用於空值資料欄。

   如果沒有重複的鍵，請前往「修正不一致的索引」。

4. 建議您執行以下非強制步驟：備份含有重複鍵的記錄。執行下列陳述式來建立備份記錄：

   程式碼範例

     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ((KEY_VALUES));
     

   在這個陳述式中，KEY_VALUES 是從上一步驟結果複製的值清單。例如：

   程式碼範例

     CREATE TABLE public.TEST_NAMES_bak
     AS SELECT * FROM public.TEST_NAMES
     WHERE (name) IN (('Johnny'),('Peter'))
     

   如果資料列數量龐大，建議您在 IN 陳述式中，將 ((KEY_VALUES)) 參數替換為步驟 2 中的 SELECT 陳述式 (不含 count 參數)，例如：

   程式碼範例

     CREATE TABLE SCHEMA_NAME.TABLE_NAME_bak
     AS SELECT * FROM SCHEMA_NAME.TABLE_NAME
     WHERE (KEY_COLUMNS)
     IN ( SELECT (KEY_COLUMNS)
     FROM SCHEMA_NAME.TABLE_NAME
     GROUP BY (KEY_COLUMNS)
     HAVING count(*) > 1);
     

5. 為使用者新增複寫角色，停用觸發程序：

   程式碼範例

     ALTER USER CURRENT_USER with REPLICATION;
     SET session_replication_role = replica;
     

6. 執行下列陳述式，刪除重複的鍵：

   程式碼範例

     BEGIN;
   
     DELETE FROM  SCHEMA_NAME.TABLE_NAME a
         USING  (
                 SELECT   min(ctid) AS ctid,
                         KEY_COLUMNS
                 FROM     SCHEMA_NAME.TABLE_NAME
                 GROUP BY KEY_COLUMNS
                         HAVING count(*) > 1 ) b
         WHERE a.KEY_COLUMNS = b.KEY_COLUMNS
         AND   a.ctid <> b.ctid;
     

   舉例來說，如果是多欄 KEY_COLUMNS：

   程式碼範例

     DELETE FROM public.test_random a
         USING (
                SELECT min(ctid) AS ctid,
                day, rnum
         FROM public.test_random
         GROUP BY day, rnum
                HAVING count(*) > 1 ) b
         WHERE a.day=b.day and a.rnum = b.rnum
         AND a.ctid <> b.ctid;
     

   其中 day 和 rnum 是 KEY_COLUMNS。

   執行這項陳述式後，系統會保留一列，並刪除每組重複資料列中的其他資料列。如要控管要刪除的資料列版本，請在刪除陳述式中執行下列篩選器：

   程式碼範例

     DELETE FROM  SCHEMA_NAME.TABLE_NAME
     WHERE ( KEY_COLUMNS, ctid) = (KEY_VALUES, CTID_VALUE);
     

   警告： 提交這項交易會變更資料庫，並移除重複的鍵。請務必不要刪除仍需使用的資料。從備份還原可能會導致索引不一致。

7. 請完成下列步驟，確認 DELETE 指令傳回的資料列數量符合預期，且沒有任何錯誤：

   1. 執行下列陳述式，找出資料表變更的資料列：

      程式碼範例

        SELECT schemaname, relname, n_tup_del, n_tup_upd
          FROM pg_stat_xact_all_tables
        WHERE n_tup_del+n_tup_upd > 0;
        

   2. 如果所有資料列都正確無誤，請提交 DELETE 交易：

      程式碼範例

        END;
        

   3. 如有錯誤，請還原變更來修正錯誤：

      程式碼範例

        ROLLBACK;
        

8. 刪除重複的鍵後，您可以重新為索引建立索引。

修正不一致的索引

下列各節說明如何修正執行個體中發現的索引不一致問題。

注意： 如果索引有違反唯一和主鍵的項目，您必須先修正這些項目，才能重新建立索引。

視資料庫的設定而定，您可能需要針對上一個步驟中識別的每個索引執行下列操作：

1. 準備重新建立索引。

2. 重新建立索引。

3. 如果重新建立索引作業因外鍵違規而失敗，請務必找出並修正這些違規事項。

4. 再次執行重新建立索引作業。

準備重新建立索引

找出索引大小

建立大型資料庫的索引所需時間，會比小型資料庫更長。如要加快大型資料庫的索引和重新建立索引作業，可以為這些作業分配更多記憶體和 CPU 效能。這是規劃重新建立索引作業的重要步驟。瞭解索引大小後，您可以設定重新建立索引作業使用的記憶體大小，以及設定平行工作站數量。

執行下列陳述式，找出要修正的索引大小 (以 KB 為單位)：

程式碼範例

  SELECT i.relname                                      AS index_name,
        pg_size_pretty(pg_relation_size(x.indexrelid)) AS index_size
  FROM   pg_index x
        JOIN pg_class i
          ON i.oid = x.indexrelid
  WHERE  i.relname = 'INDEX_NAME';
  

這項陳述式會輸出類似以下的結果：

輸出

  index_name  | index_size
  ------------+------------
  my_index    | 16 kB
  (1 row)
  

設定用於重新建立索引的記憶體大小

根據上一節中判斷的索引大小，請務必為 maintenance_work_mem 設定適當的值。這個參數會指定要用於重新建立索引作業的記憶體量。舉例來說，如果索引大小超過 15 GB，建議您調整維護記憶體。詳情請參閱「設定資料庫標記」。

注意： 系統會為每個工作階段設定 maintenance_work_mem 的值。 變更值後，您不需要重新啟動執行個體。不過，如果您在新工作階段中再次連線至資料庫，系統不會儲存先前的設定值。

建立大型資料庫的索引所需時間，會比小型資料庫更長。為提升索引和重新建立索引作業的速度，建議您在重新建立索引作業期間，將記憶體至少 4 GB 的執行個體 maintenance_work_mem 設為執行個體記憶體的 2% 以上。

設定平行工作站數量

如要增加重新建立索引的平行工作站數量，請在資料庫中設定 max_parallel_maintenance_workers 參數 (使用 PostgreSQL 11 以上版本)。這個參數的預設值為 2，但可以設為較高的值，增加重新建立索引的工作站數量。對於具備 8 顆以上 vCPU 核心的執行個體，建議將 max_parallel_maintenance_workers 旗標值設為 4。

詳情請參閱「設定資料庫標記」。

重新建立索引

您可以使用 pg_repack 公用程式重新建立索引，不必封鎖實際運作的工作負載。這項公用程式可自動執行並簡化並行重新建立索引程序，讓您重新建立索引時不必停機，特別是對於沒有 REINDEX CONCURRENTLY 作業的 PostgreSQL 11 和更早版本。請使用 pg_repack 1.4.7 版執行此程序。

如要使用 pg_repack 重新建立索引，請完成下列步驟：

1. 從 pg_repack 頁面下載、編譯及安裝 pg_repack 公用程式。

   Debian GNU/Linux 11

   為方便起見，建議 Debian Linux 使用者下載並安裝這個適用於 Linux x86_64 平台的預先建構可執行二進位檔。

   二進位檔的 sha256 總和檢查碼雜湊如下：

   ecfee54364a625d9365d86cb27940b458bfdb0d6ff63bb88063039256fbde96f

   如要確認 Linux 版本為 Debian GNU/Linux 11，請執行 hostnamectl 指令。

   自行編譯

   注意：如要執行這項程序，您應在 Linux 或 Unix 的終端機中執行指令，並使用可正常運作的 GNU 工具鍊。

   從pg_repack頁面下載、編譯及安裝 pg_repack 公用程式。

2. 建立 pg_repack 擴充功能：

   程式碼範例

     CREATE EXTENSION pg_repack;
     

3. 執行下列指令，同時重新建立索引：

   程式碼範例

     pg_repack -h HOSTIP -p 5432 -U USERNAME -d "DATABASE_NAME" -i "INDEX_NAME" --no-superuser-check --no-kill-backend --wait-timeout=3600
     

   注意： pg_repack 不允許您重新為具體化檢視區塊的索引建立索引。

   這個指令會輸出類似以下的結果：

   輸出

     INFO: repacking index "public.t_i_key"
     

   如果執行 pg_repack 時發生任何錯誤，請修正錯誤並重試。修正所有不重複鍵索引和主鍵索引後，請檢查是否有違反外部鍵的行為，並修正所有發現的違規事項。

找出並修正外鍵違規

如要瞭解如何找出並修正外鍵違規事項，請參閱「找出並修正外鍵違規事項」。

監控重新建立索引作業

有時，重新建立索引作業可能會遭到其他工作階段封鎖。建議您每 4 小時檢查一次。如果重新建立索引作業遭到封鎖，您可以取消封鎖工作階段，讓重新建立索引作業完成。

請完成下列步驟，找出封鎖和等待中的工作階段，然後在 INDEX 作業中取消這些工作階段：

1. 如要找出造成阻礙的工作階段，請執行下列查詢：

   程式碼範例

     SELECT pid,
           usename,
           pg_blocking_pids(pid) AS blocked_by,
           query                 AS blocked_query
     FROM   pg_stat_activity
     WHERE  cardinality(pg_blocking_pids(pid)) > 0;
     

2. 如要取消工作階段，請使用先前查詢中的封鎖工作階段 PID 執行下列查詢：

   程式碼範例

     SELECT pg_cancel_backend(PID);
     

確認索引一致

您必須繼續檢查每個不一致的索引，確認索引不一致。修正所有執行個體的不一致索引和鍵違規事項後，請按照先前章節的步驟檢查是否仍有問題：

• 檢查索引是否不一致

• 檢查是否有違反專屬鍵的行為