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 或因超過 30 分鐘而狀態為 failed 的任務。因此，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)