Golang中DynamoDB GSI按用户邮箱查询无结果问题求助

刚接触DynamoDB，尝试通过Global Secondary Index（GSI）按用户邮箱查询数据，但始终返回0条结果，查文档没找到问题关键，附上相关配置与代码，求排查解决。

表及GSI创建配置 #

{
    "TableName": "BlogTable",
    "KeySchema": [
        {
            "AttributeName": "PK",
            "KeyType": "HASH"
        },
        {
            "AttributeName": "SK",
            "KeyType": "RANGE"
        }
    ],
    "AttributeDefinitions": [
        {
            "AttributeName": "PK",
            "AttributeType": "S"
        },
        {
            "AttributeName": "SK",
            "AttributeType": "S"
        }
    ],
    "GlobalSecondaryIndexes": [
        {
            "IndexName": "BlogGlobalIndex1",
            "KeySchema": [
                {
                    "AttributeName": "PK",
                    "KeyType": "HASH"
                },
                {
                    "AttributeName": "SK",
                    "KeyType": "RANGE"
                }
            ],
            "Projection": {
                "ProjectionType": "ALL"
            },
            "ProvisionedThroughput": {
                "ReadCapacityUnits": 5,
                "WriteCapacityUnits": 5
            }
        }
    ],
    "ProvisionedThroughput": {
        "ReadCapacityUnits": 5,
        "WriteCapacityUnits": 5
    }
}

Golang写入用户数据代码 #

// After this I can find item by user ID.
func (d DynamoDB) CreateUser(ctx context.Context, model store.User) error {
    model.PrimaryKey = store.PrimaryKey{
        PK: "user#" + model.ID,
        SK: "user#" + model.ID,
    }

    item, err := attributevalue.MarshalMap(model)
    if err != nil {
        return fmt.Errorf("marshal map: %w", err)
    }

    _, err = d.Client.PutItem(ctx, &dynamodb.PutItemInput{
        TableName: aws.String(d.Table),
        Item:      item,
    })
    if err != nil {
        return fmt.Errorf("put item: %w", err)
    }

    return nil
}

Golang查询GSI代码 #

func (d DynamoDB) FindUserByEmail(ctx context.Context, email string) (store.User, error) {
    cond1 := expression.Name("PK").Equal(expression.Value("user#" + email))
    cond2 := expression.Name("SK").Equal(expression.Value("user#" + email))
    expr, err := expression.NewBuilder().WithCondition(cond1.And(cond2)).Build()
    if err != nil {
        return store.User{}, fmt.Errorf("expression build: %w", err)
    }

    res, err := d.Client.Query(ctx, &dynamodb.QueryInput{
        TableName: aws.String(d.Table),
        IndexName: aws.String(d.GlobalIndex),

        ExpressionAttributeNames:  expr.Names(),
        ExpressionAttributeValues: expr.Values(),
        FilterExpression:          expr.Filter(),
        KeyConditionExpression:    expr.Condition(),
        ProjectionExpression:      expr.Projection(),

        // KeyConditionExpression: aws.String("PK = :PK, SK = :SK"),
        // ExpressionAttributeValues: map[string]types.AttributeValue{
        //  "PK": &types.AttributeValueMemberS{Value: "user#" + email},
        //  "SK": &types.AttributeValueMemberS{Value: "user#" + email},
        // },
        // Limit: aws.Int32(1),
    })
    if err != nil {
        return store.User{}, fmt.Errorf("get item: %w", err)
    }

    fmt.Println(res.Count) // 0
    fmt.Println(len(res.Items)) // 0

    var model store.User
// ...
    return model, nil
}

示例数据 #

{
    "Items": [
        {
            "SK": {
                "S": "user#1722198720607873"
            },
            "Email": {
                "S": "Shaylee54@gmail.com"
            },
            "PK": {
                "S": "user#1722198720607873"
            },
            "ID": {
                "S": "1722198720607873"
            },
            "Password": {
                "S": "5O1AMjIG8hRFeS_"
            },
            "Name": {
                "S": "Marty Ferry III"
            }
        },
        {
            "SK": {
                "S": "user#1722178078008670"
            },
            "ID": {
                "S": "1722178078008670"
            },
            "Email": {
                "S": "Carissa11@yahoo.com"
            },
            "PK": {
                "S": "user#1722178078008670"
            },
            "Name": {
                "S": "Jana Rice"
            },
            "Password": {
                "S": "OM9AfTTuwYPw_bD"
            }
        }
    ],
    "Count": 2,
    "ScannedCount": 2,
    "ConsumedCapacity": null
}

问题根源与解决方案 #

1. 现有GSI完全无效

你当前创建的GSI和主表的KeySchema完全一致（都是PK作为HASH、SK作为RANGE），这相当于复制了主表的结构，根本无法支持按邮箱查询。因为你的数据中PK是user#用户ID，而不是user#邮箱，查询时用user#邮箱作为PK值，GSI里根本不存在这条数据，自然返回0条结果。

2. 正确配置支持邮箱查询的GSI

要实现按邮箱查询，需要创建以Email为HASH键的GSI，修改后的表配置如下：

{
    "TableName": "BlogTable",
    "KeySchema": [
        {
            "AttributeName": "PK",
            "KeyType": "HASH"
        },
        {
            "AttributeName": "SK",
            "KeyType": "RANGE"
        }
    ],
    "AttributeDefinitions": [
        {
            "AttributeName": "PK",
            "AttributeType": "S"
        },
        {
            "AttributeName": "SK",
            "AttributeType": "S"
        },
        {
            "AttributeName": "Email",
            "AttributeType": "S"
        }
    ],
    "GlobalSecondaryIndexes": [
        {
            "IndexName": "EmailIndex",
            "KeySchema": [
                {
                    "AttributeName": "Email",
                    "KeyType": "HASH"
                }
            ],
            "Projection": {
                "ProjectionType": "ALL"
            },
            "ProvisionedThroughput": {
                "ReadCapacityUnits": 5,
                "WriteCapacityUnits": 5
            }
        }
    ],
    "ProvisionedThroughput": {
        "ReadCapacityUnits": 5,
        "WriteCapacityUnits": 5
    }
}

• 新增Email的属性定义（字符串类型）
• GSI的KeySchema设置Email为HASH键（因为邮箱是唯一值，不需要RANGE键）
• 给GSI起个直观的名字，比如EmailIndex

3. 修改查询代码

查询时直接匹配GSI的HASH键Email即可，不需要再用PK和SK：

func (d DynamoDB) FindUserByEmail(ctx context.Context, email string) (store.User, error) {
    cond := expression.Name("Email").Equal(expression.Value(email))
    expr, err := expression.NewBuilder().WithCondition(cond).Build()
    if err != nil {
        return store.User{}, fmt.Errorf("expression build: %w", err)
    }

    res, err := d.Client.Query(ctx, &dynamodb.QueryInput{
        TableName:                 aws.String(d.Table),
        IndexName:                 aws.String("EmailIndex"), // 对应新的GSI名称
        KeyConditionExpression:    expr.Condition(),
        ExpressionAttributeNames:  expr.Names(),
        ExpressionAttributeValues: expr.Values(),
        Limit:                     aws.Int32(1), // 邮箱唯一，限制返回1条
    })
    if err != nil {
        return store.User{}, fmt.Errorf("query item: %w", err)
    }

    if len(res.Items) == 0 {
        return store.User{}, fmt.Errorf("user not found with email: %s", email)
    }

    var model store.User
    err = attributevalue.UnmarshalMap(res.Items[0], &model)
    if err != nil {
        return store.User{}, fmt.Errorf("unmarshal item: %w", err)
    }

    return model, nil
}

4. 注意事项

• 如果表已经创建，无法直接修改GSI，需要删除原有GSI后新增，或者重新创建表
• 确保写入数据时Email字段被正确写入（从你的示例数据看，Email已经存在，这部分没问题）

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