Aurora DSQL 中的非同步索引 - Amazon Aurora DSQL
語法參數使用須知建立索引查詢索引唯一索引建置失敗唯一性違規

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

Aurora DSQL 中的非同步索引

CREATE INDEX ASYNC 命令會在指定資料表的一或多個資料欄上建立索引。此命令是一種非同步 DDL 操作，不會封鎖其他交易。當您執行 時CREATE INDEX ASYNC，Aurora DSQL 會立即傳回 job_id。

您可以使用sys.jobs系統檢視來監控此非同步任務的狀態。當索引建立任務正在進行時，您可以使用下列程序和命令：

sys.wait_for_job(job_id)'your_index_creation_job_id'

封鎖目前的工作階段，直到指定的任務完成或失敗為止。傳回布林值，指出成功或失敗。

DROP INDEX

取消進行中索引建置任務。

當非同步索引建立完成時，Aurora DSQL 會更新系統目錄，將索引標記為作用中。

注意

請注意，在此更新期間存取相同命名空間中物件的並行交易可能會遇到並行錯誤。

當 Aurora DSQL 完成非同步索引任務時，它會更新系統目錄，以顯示索引處於作用中狀態。如果其他交易目前參考相同命名空間中的物件，您可能會看到並行錯誤。

語法

CREATE INDEX ASYNC 使用以下語法。

CREATE [ UNIQUE ] INDEX ASYNC [ IF NOT EXISTS ] name ON table_name 
     ( { column_name } [ NULLS { FIRST | LAST } ] ) 
     [ INCLUDE ( column_name [, ...] ) ] 
     [ NULLS [ NOT ] DISTINCT ]

參數

UNIQUE

指示 Aurora DSQL 在每次新增資料時，檢查資料表中是否有重複的值。如果您指定此參數，插入和更新會導致重複項目的操作會產生錯誤。

IF NOT EXISTS

如果具有相同名稱的索引已存在，表示 Aurora DSQL 不應擲回例外狀況。在這種情況下，Aurora DSQL 不會建立新的索引。請注意，您嘗試建立的索引可能與已存在的索引具有非常不同的結構。如果您指定此參數，則需要索引名稱。

name

索引的名稱。您無法在此參數中包含結構描述的名稱。

Aurora DSQL 會在與其父資料表相同的結構描述中建立索引。索引的名稱必須與結構描述中任何其他物件的名稱不同，例如資料表或索引。

如果您未指定名稱，Aurora DSQL 會根據父資料表和索引資料欄的名稱自動產生名稱。例如，如果您執行 CREATE INDEX ASYNC on table1 (col1, col2)，Aurora DSQL 會自動命名索引 table1_col1_col2_idx。

NULLS FIRST | LAST

Null 和非 Null 資料欄的排序順序。 FIRST表示 Aurora DSQL 應該在非 Null 資料欄之前排序 Null 資料欄。 LAST表示 Aurora DSQL 應該在非 Null 資料欄之後排序 Null 資料欄。

INCLUDE

要作為非索引鍵資料欄包含在索引中的資料欄清單。您無法在索引掃描搜尋資格中使用非索引鍵資料欄。Aurora DSQL 會根據索引的唯一性忽略資料欄。

NULLS DISTINCT | NULLS NOT DISTINCT

指定 Aurora DSQL 是否應將 null 值視為唯一索引中的不同值。預設值為 DISTINCT，表示唯一索引可以包含資料欄中的多個 null 值。 NOT DISTINCT表示索引不能包含資料欄中的多個 null 值。

使用須知

請考量下列準則：

  • CREATE INDEX ASYNC 命令不會引入鎖定。它也不會影響 Aurora DSQL 用來建立索引的基本資料表。

  • 在結構描述遷移操作期間， sys.wait_for_job(job_id)'your_index_creation_job_id' 程序很有用。它可確保後續的 DDL 和 DML 操作以新建立的索引為目標。

  • 每次 Aurora DSQL 執行新的非同步任務時，都會檢查sys.jobs檢視並刪除狀態為 completed或 failed超過 30 分鐘的任務。因此， sys.jobs主要會顯示進行中的任務，且不包含舊任務的相關資訊。

  • 如果 Aurora DSQL 無法建立非同步索引，則索引會保留 INVALID。對於唯一索引，DML 操作會受到唯一性限制，直到您捨棄索引為止。我們建議您捨棄無效索引並重新建立索引。

建立索引：範例

下列範例示範如何建立結構描述、資料表，以及索引。

  1. 建立名為 的資料表test.departments。

    CREATE SCHEMA test;

 CREATE TABLE test.departments (name varchar(255) primary key NOT null, 
     manager varchar(255), 
     size varchar(4));

  2. 將資料列插入資料表。

    INSERT INTO test.departments VALUES ('Human Resources', 'John Doe', '10')

  3. 建立非同步索引。

    CREATE INDEX ASYNC test_index on test.departments(name, manager, size);

    CREATE INDEX 命令會傳回任務 ID，如下所示。

    job_id 
-------------------------- 
jh2gbtx4mzhgfkbimtgwn5j45y

    job_id 表示 Aurora DSQL 已提交新任務來建立索引。您可以使用 程序sys.wait_for_job(job_id)'your_index_creation_job_id'來封鎖工作階段的其他工作，直到工作完成或逾時為止。

查詢索引建立的狀態：範例

查詢sys.jobs系統檢視以檢查索引的建立狀態，如下列範例所示。

SELECT * FROM sys.jobs

Aurora DSQL 會傳回類似以下的回應。

           job_id           |   status   | details 
----------------------------+------------+---------
 vs3kcl3rt5ddpk3a6xcq57cmcy | completed  | 
 ihbyw2aoirfnrdfoc4ojnlamoq | processing |

狀態欄可以是下列其中一個值。

submitted processing failed completed
任務已提交，但 Aurora DSQL 尚未開始處理。 Aurora DSQL 正在處理任務。 任務失敗。如需詳細資訊，請參閱詳細資訊欄。如果 Aurora DSQL 無法建置索引，Aurora DSQL 不會自動移除索引定義。您必須使用 DROP INDEX命令手動移除索引。 Aurora DSQL

您也可以透過目錄資料表pg_index和 查詢索引的狀態pg_class。具體而言， 屬性 indisvalid 和 indisimmediate可以告訴您索引的狀態。當 Aurora DSQL 建立您的索引時，其初始狀態為 INVALID。索引的 indisvalid旗標會傳回 FALSE或 f，表示索引無效。如果旗標傳回 TRUE或 t，表示索引已就緒。

SELECT relname AS index_name, indisvalid as is_valid, pg_get_indexdef(indexrelid) AS index_definition
from pg_index, pg_class
WHERE pg_class.oid = indexrelid AND indrelid = 'test.departments'::regclass;
    index_name    | is_valid |                                                 index_definition                                                  
------------------+----------+-------------------------------------------------------------------------------------------------------------------
 department_pkey  |     t    | CREATE UNIQUE INDEX department_pkey ON test.departments USING btree_index (title) INCLUDE (name, manager, size)
 test_index1      |     t    | CREATE INDEX test_index1 ON test.departments USING btree_index (name, manager, size)

唯一索引建置失敗

如果您的非同步唯一索引建置任務顯示失敗狀態與詳細資訊 Found duplicate key while validating index for UCVs，這表示由於唯一性限制違規而無法建置唯一索引。

解決唯一的索引建置失敗

  1. 針對唯一次要索引中指定的索引鍵，移除主要資料表中具有重複項目的任何資料列。

  2. 捨棄失敗的索引。

  3. 發出新的建立索引命令。

在主資料表中偵測唯一性違規

下列 SQL 查詢可協助您識別資料表指定欄中的重複值。當您需要在目前未設定為主索引鍵或沒有唯一限制的資料欄上強制執行唯一性時，此功能特別有用，例如使用者資料表中的電子郵件地址。

以下範例示範如何建立範例使用者資料表、將包含已知複本的測試資料填入其中，然後執行偵測查詢。

定義資料表結構描述 

-- Drop the table if it exists
DROP TABLE IF EXISTS users;

-- Create the users table with a simple integer primary key
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    email VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

插入包含一組重複電子郵件地址的範例資料

-- Insert sample data with explicit IDs
INSERT INTO users (user_id, email, first_name, last_name) VALUES
    (1, 'john.doe@example.com', 'John', 'Doe'),
    (2, 'jane.smith@example.com', 'Jane', 'Smith'),
    (3, 'john.doe@example.com', 'Johnny', 'Doe'),
    (4, 'alice.wong@example.com', 'Alice', 'Wong'),
    (5, 'bob.jones@example.com', 'Bob', 'Jones'),
    (6, 'alice.wong@example.com', 'Alicia', 'Wong'),
    (7, 'bob.jones@example.com', 'Robert', 'Jones');

執行重複的偵測查詢 

-- Query to find duplicates
WITH duplicates AS (
    SELECT email, COUNT(*) as duplicate_count
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
)
SELECT u.*, d.duplicate_count
FROM users u
INNER JOIN duplicates d ON u.email = d.email
ORDER BY u.email, u.user_id;

檢視具有重複電子郵件地址的所有記錄 

 user_id |         email          | first_name | last_name |         created_at         | duplicate_count 
---------+------------------------+------------+-----------+----------------------------+-----------------
       4 | akua.mansa@example.com | Akua       | Mansa     | 2025-05-21 20:55:53.714432 |               2
       6 | akua.mansa@example.com | Akua       | Mansa     | 2025-05-21 20:55:53.714432 |               2
       1 | john.doe@example.com   | John       | Doe       | 2025-05-21 20:55:53.714432 |               2
       3 | john.doe@example.com   | Johnny     | Doe       | 2025-05-21 20:55:53.714432 |               2
(4 rows)

如果我們現在嘗試索引建立陳述式，將會失敗： 

postgres=> CREATE UNIQUE INDEX ASYNC idx_users_email ON users(email);
      job_id      
----------------------------
 ve32upmjz5dgdknpbleeca5tri
(1 row)

postgres=> select * from sys.jobs;
           job_id           |  status   |                       details                       |  job_type   | class_id | object_id |      object_name       |       start_time       |      update_time       
----------------------------+-----------+-----------------------------------------------------+-------------+----------+-----------+------------------------+------------------------+------------------------
 qpn6aqlkijgmzilyidcpwrpova | completed |                                                     | DROP        |     1259 |     26384 |                        | 2025-05-20 00:47:10+00 | 2025-05-20 00:47:32+00
 ve32upmjz5dgdknpbleeca5tri | failed    | Found duplicate key while validating index for UCVs | INDEX_BUILD |     1259 |     26396 | public.idx_users_email | 2025-05-20 00:49:49+00 | 2025-05-20 00:49:56+00
(2 rows)