本頁面由 Cloud Translation API 翻譯而成。

找出並修正不一致的索引

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

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

本手冊適用於遇到這些一致性問題的 PostgreSQL 適用的 AlloyDB 使用者。這份教戰手冊提供相關資訊，協助 PostgreSQL 使用者找出並修正不一致的索引。

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

事前準備。

開始重新建立索引前，請先備份資料庫、設定正確的權限、確認 psql 用戶端版本，並啟用 amcheck 擴充功能。
檢查索引是否不一致。

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

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

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

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

事前準備

備份 AlloyDB 叢集的資料

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

設定 `alloydbsuperuser` 權限

如要完成這個頁面的步驟，您必須具備alloydbsuperuser權限。詳情請參閱「AlloyDB 預先定義的 PostgreSQL 角色」。

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

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

啟用 amcheck 擴充功能

如要檢查索引不一致的情形，請啟用 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;
      PERFORM bt_index_check(index => r.oid, heapallindexed => r.indisunique);
    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"

」一文中的程序操作。

如要進一步瞭解如何查看記錄，請參閱「使用記錄檔探索工具查看記錄檔」。

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

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

找出違反唯一鍵的行為

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

程式碼範例

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%2$I on table %1$I %4$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 表示可能隱藏重複值。如果表格中出現這兩項其中一項，請參閱「修正重複鍵違規問題」。

修正重複鍵違規問題

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

如上述範例輸出內容所示，從 NOTICE 表格標頭中擷取 key_columns。在以下範例中，鍵列為 email。
輸出
```
  NOTICE:  checking index=users_email_key on table=users key_columns="(email)"
```
在步驟 3 的查詢中，於 KEY_COLUMNS 使用這些值。
找出資料表的結構定義。使用 psql 連線至資料庫，然後執行下列指令：
程式碼範例
```
\dt TABLE_NAME
```
「schema」欄中的值是您在步驟 3 的查詢中，為「SCHEMA_NAME」使用的值。

舉例來說，如果查詢如下：
```
 \dt games
 
```
輸出結果會與下列內容相似：
```
 List of relations
 Schema  | Name  | Type  | Owner
 --------+-------+-------+----------
 public  | games | table | postgres
 (1 row)
 
```
執行下列陳述式，強制進行完整資料表掃描，並取得重複的鍵。
程式碼範例
```
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 中的任何資料欄為空值，您可以忽略這些資料欄，因為唯一限制不適用於空值資料欄。

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

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

程式碼範例

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);

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

程式碼範例

ALTER USER CURRENT_USER with REPLICATION;
SET session_replication_role = replica;

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

程式碼範例

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);

請完成下列步驟，確認 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;
刪除重複的鍵後，您可以重新為索引建立索引。

修正不一致的索引

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

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

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

準備重新建立索引

找出索引大小

建立大型資料庫的索引所需時間，會比小型資料庫更長。如要加快大型資料庫的索引和重新建立索引作業，可以為這些作業分配更多記憶體和 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：

程式碼範例

SET maintenance_work_mem TO "1GB";

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

設定平行工作站數量

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

以下說明如何找出這些參數設定的值：

程式碼範例

SHOW max_parallel_maintenance_workers;
SHOW max_worker_processes;
SHOW max_parallel_workers;

max_parallel_maintenance_workers 參數是 max_worker_processes 的子集，且受限於 max_parallel_workers。如需更多平行工作站，請增加 max_worker_processes 和 max_parallel_workers 的值。

以下範例說明如何設定 max_parallel_maintenance_workers：

程式碼範例

SET max_parallel_maintenance_workers TO 4;

max_parallel_maintenance_workers 參數無法保證工作人員的分配。如要確認重新建立索引作業已啟動多個並行工作者，請在啟動重新建立索引作業後，從另一個工作階段執行下列查詢：

程式碼範例

SELECT
leader.leader_pid,leader.pid "worker_pid",leader.query
FROM
pg_stat_activity leader, pg_stat_activity worker
WHERE leader.leader_pid = worker.pid;

重新建立索引

您可以使用 pg_repack 公用程式重新建立索引，不必封鎖實際運作的工作負載。這項公用程式可自動執行並簡化並行重新建立索引程序，讓您在重新建立索引時不必停機。請使用 pg_repack 1.4.7 版執行此程序。

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

從 pg_repack 頁面下載、編譯及安裝 pg_repack 公用程式。
建立 pg_repack 擴充功能：
程式碼範例
```
CREATE EXTENSION pg_repack;
```
執行下列指令，同時重新建立索引：
程式碼範例
```
  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 作業中取消這些工作階段：

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

程式碼範例

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;

如要取消工作階段，請使用先前查詢中的封鎖工作階段 PID 執行下列查詢：
程式碼範例
```
SELECT pg_cancel_backend(PID);
```

確認索引一致

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

檢查索引是否不一致
檢查是否有違反專屬鍵的行為

找出並修正不一致的索引 透過集合功能整理內容 你可以依據偏好儲存及分類內容。

事前準備

備份 AlloyDB 叢集的資料

設定 alloydbsuperuser 權限

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

啟用 amcheck 擴充功能

程式碼範例

檢查索引是否不一致

檢查不一致之處

程式碼範例

程式碼範例

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

找出違反唯一鍵的行為

程式碼範例

輸出

修正重複鍵違規問題

輸出

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

程式碼範例

修正不一致的索引

準備重新建立索引

找出索引大小

程式碼範例

輸出

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

程式碼範例

設定平行工作站數量

程式碼範例

程式碼範例

程式碼範例

重新建立索引

程式碼範例

程式碼範例

輸出

找出並修正外鍵違規

監控重新建立索引作業

程式碼範例

程式碼範例

確認索引一致

找出並修正不一致的索引

設定 `alloydbsuperuser` 權限

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