DynamoDB查询缺失键架构元素问题：分区键与排序键使用咨询

问题根源 #

DynamoDB的Query API有硬性约束：必须在keyConditionExpression中指定分区键的精确匹配条件。你当前的查询仅使用非键属性REJECTION_DATE作为条件，完全未涉及表的分区键iapk，直接违反了Query的规则，因此触发报错。

正确使用分区键和排序键的规则 #

• 分区键（iapk）必须出现在keyConditionExpression中，且只能用=做精确匹配
• 排序键（rejection no）可选搭配使用，支持=、BETWEEN、>、<等范围条件，但必须跟在分区键条件之后
• 若需按REJECTION_DATE这类非键属性查询，有两种可行方案：
  1. 给REJECTION_DATE创建全局二级索引（GSI），再对GSI执行Query
  2. 使用Scan操作（不推荐，大数据量下性能极差）

修正后的代码示例 #

示例1：按分区键+排序键查询 #

如果业务需求是查询特定iapk下，排序键在指定范围的数据：

public class DynamoDBExample {
    public static void main(String[] args) {

        cred Cred = new cred();
        var akey = Cred.getkey();
        var aid = Cred.getid();
        var region = Region.AP_SOUTH_1;

        AwsCredentials Credentials = AwsBasicCredentials.create(akey, aid);
        var dynamoDBClient = DynamoDbClient.builder()
                .region(region)
                .endpointOverride(URI.create("http://localhost:8000"))
                .credentialsProvider(StaticCredentialsProvider.create(Credentials))
                .build();

        // 目标分区键值
        String targetIapk = "your-target-iapk-value";
        // 排序键范围示例
        String startRejectionNo = "001";
        String endRejectionNo = "100";

        Map<String, AttributeValue> expressionAttributeValues = new HashMap<>();
        expressionAttributeValues.put(":iapkVal", AttributeValue.builder().s(targetIapk).build());
        expressionAttributeValues.put(":startRejNo", AttributeValue.builder().s(startRejectionNo).build());
        expressionAttributeValues.put(":endRejNo", AttributeValue.builder().s(endRejectionNo).build());

        var queryRequest = QueryRequest.builder()
                .tableName("IA_REJECTION")
                // 必须包含分区键精确匹配，可选添加排序键范围条件
                .keyConditionExpression("iapk = :iapkVal AND \"rejection no\" BETWEEN :startRejNo AND :endRejNo")
                .expressionAttributeValues(expressionAttributeValues)
                .build();

        var starttime = System.currentTimeMillis();
        QueryResponse response = dynamoDBClient.query(queryRequest);
        var endtime = System.currentTimeMillis();

        response.items().forEach(item -> {
            item.forEach((key, value) -> System.out.println(key + " --> " + value));
            System.out.println();
        });

        var timetaken = endtime - starttime;
        System.out.println("Total Time Taken To Run This Code: " + timetaken + "ms");
    }
}

示例2：按REJECTION_DATE查询（需先创建GSI） #

若必须按日期范围查询，先给表创建全局二级索引（索引分区键设为REJECTION_DATE），再修改代码查询该GSI：

public class DynamoDBExample {
    public static void main(String[] args) {

        cred Cred = new cred();
        var akey = Cred.getkey();
        var aid = Cred.getid();
        var region = Region.AP_SOUTH_1;

        AwsCredentials Credentials = AwsBasicCredentials.create(akey, aid);
        var dynamoDBClient = DynamoDbClient.builder()
                .region(region)
                .endpointOverride(URI.create("http://localhost:8000"))
                .credentialsProvider(StaticCredentialsProvider.create(Credentials))
                .build();

        String startDate = "2023-01-01";
        String endDate = "2023-06-30";

        Map<String, AttributeValue> expressionAttributeValues = new HashMap<>();
        expressionAttributeValues.put(":startDate", AttributeValue.builder().s(startDate).build());
        expressionAttributeValues.put(":endDate", AttributeValue.builder().s(endDate).build());

        var queryRequest = QueryRequest.builder()
                .tableName("IA_REJECTION")
                // 指定要查询的GSI名称（需提前创建）
                .indexName("REJECTION_DATE-index")
                // 对GSI的分区键使用范围条件
                .keyConditionExpression("REJECTION_DATE BETWEEN :startDate AND :endDate")
                .expressionAttributeValues(expressionAttributeValues)
                .build();

        var starttime = System.currentTimeMillis();
        QueryResponse response = dynamoDBClient.query(queryRequest);
        var endtime = System.currentTimeMillis();

        response.items().forEach(item -> {
            item.forEach((key, value) -> System.out.println(key + " --> " + value));
            System.out.println();
        });

        var timetaken = endtime - starttime;
        System.out.println("Total Time Taken To Run This Code: " + timetaken + "ms");
    }
}

注意：创建GSI时需确保索引键设计匹配查询模式，同时要考虑GSI的存储与读写成本。

内容的提问来源于stack exchange，提问作者Ashok S