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

空列和非空列的排序顺序。FIRST 表示 Aurora DSQL 应先对空列进行排序，然后再对非空列进行排序。LAST 表示 Aurora DSQL 应先对非空列进行排序，之后再对空列进行排序。

INCLUDE

要作为非键列包含在索引中的列的列表。您不能在索引扫描搜索限定条件中使用非键列。就索引的唯一性而言，Aurora DSQL 会忽略该列。

NULLS DISTINCT | NULLS NOT DISTINCT

指定 Aurora DSQL 是否应将空值视为唯一索引中的不同值。默认值为 DISTINCT，这意味着唯一索引可以在一列中包含多个空值。NOT DISTINCT 表示索引不能在一列中包含多个空值。

使用说明

请考虑以下准则：

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 操作受唯一性约束所制约，直至您删除索引。我们建议您删除无效的索引并重新创建它们。

创建索引：示例

以下示例演示如何创建架构、表和索引。

创建名为 test.departments 的文件。


CREATE SCHEMA test;

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

在表中插入一行。


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

创建异步索引。
```
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，这表示由于违反唯一性约束而无法构建唯一索引。

解决唯一索引构建失败

移除主表中与在唯一二级索引中指定的键有重复条目的所有行。
删除失败的索引。
发出新的创建索引命令。

检测主表中的唯一性违规

以下 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)

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

主键

系统表和命令