如何使用多排序键查询DynamoDB并实现双属性排序？

Got it, let's break this down for you. First, let's tackle your specific query for the github_repository table, then dive into how multi-attribute sorting works in DynamoDB overall.

Step 1: Create a Global Secondary Index (GSI) #

DynamoDB doesn't support native multi-column sorting on the base table—you need to encode your sorting priorities into a single sort key via an index. Here's how to set it up:

• Partition Key: Use a fixed constant (like all_repos) as the GSI's partition key. This groups all your repository data into one partition, enabling global sorting across all items.
• Sort Key: Create a composite numeric attribute (let's call it sort_stars_forks) calculated as -stars * 1000000 - forks. The negative values let us use DynamoDB's default ascending sort to get descending order for both stars and forks, and multiplying stars by a large number ensures it takes priority over forks.

Example Table/GSI Setup (using boto3) #

import boto3

dynamodb = boto3.resource('dynamodb')
table = dynamodb.create_table(
    TableName='github_repository',
    # Adjust base table primary key if your existing setup differs
    KeySchema=[
        {'AttributeName': 'owner', 'KeyType': 'HASH'},
        {'AttributeName': 'name', 'KeyType': 'RANGE'}
    ],
    AttributeDefinitions=[
        {'AttributeName': 'owner', 'AttributeType': 'S'},
        {'AttributeName': 'name', 'AttributeType': 'S'},
        {'AttributeName': 'global_partition', 'AttributeType': 'S'},
        {'AttributeName': 'sort_stars_forks', 'AttributeType': 'N'}
    ],
    GlobalSecondaryIndexes=[
        {
            'IndexName': 'stars-forks-sort-index',
            'KeySchema': [
                {'AttributeName': 'global_partition', 'KeyType': 'HASH'},
                {'AttributeName': 'sort_stars_forks', 'KeyType': 'RANGE'}
            ],
            'Projection': {'ProjectionType': 'ALL'},  # Include all attributes in the index
            'ProvisionedThroughput': {'ReadCapacityUnits': 5, 'WriteCapacityUnits': 5}
        }
    ],
    ProvisionedThroughput={'ReadCapacityUnits': 5, 'WriteCapacityUnits': 5}
)

Step 2: Insert/Update Data with the Composite Sort Key #

When adding or updating repository items, include the global_partition (set to all_repos) and calculate the sort_stars_forks value:

table.put_item(
    Item={
        'owner': 'octocat',
        'name': 'Hello-World',
        'watches': 1234,
        'stars': 56789,
        'forks': 12345,
        'global_partition': 'all_repos',
        'sort_stars_forks': -56789 * 1000000 - 12345
    }
)

Step 3: Query the Top 10 Items #

Now you can query the GSI to get your sorted results:

response = table.query(
    IndexName='stars-forks-sort-index',
    KeyConditionExpression='global_partition = :gp',
    ExpressionAttributeValues={':gp': 'all_repos'},
    ScanIndexForward=True,  # Ascending sort on negative values = descending on original stars/forks
    Limit=10
)

# Clean up results to remove index-specific attributes
top_repos = [{k: v for k, v in item.items() if k not in ['global_partition', 'sort_stars_forks']} 
             for item in response['Items']]

DynamoDB only supports sorting by a single sort key per index, so you need to encode your multi-column sorting logic into that one attribute. Here's the playbook:

1. Define Sort Priorities
   First, decide which attribute takes precedence (e.g., stars first, then forks).

2. Create a Composite Sort Key
   Combine your attributes into a single value that preserves your desired order:

   • Numeric Composite Key: For numeric attributes, multiply higher-priority attributes by a large enough factor to ensure they dominate the sort, then negate if you need descending order. Example: sort_key = -primary_attr * 10^9 - secondary_attr.
   • String Composite Key: For string attributes (or mixed types), format each attribute to a fixed length (e.g., pad numbers with leading zeros) so dictionary order works as expected. For descending order, use reverse logic (e.g., 9999999999 - num converted to a string).

3. Choose the Right Index

   • GSI: Use this if you need to sort across all items in the table (global scope). Use a fixed partition key to group all items into one partition.
   • LSI: Use this if you only need to sort within a specific base table partition (e.g., all repos owned by a single user). Attach the LSI to the base table's partition key.

4. Query with the Index

   • Use ScanIndexForward=True (default) if your composite key is encoded for ascending sort (or reverse-encoded for descending).
   • Use ScanIndexForward=False to reverse the sort order if needed.

Key Notes #

• Avoid using Scan for multi-attribute sorting—it’s inefficient for large tables, as it scans every item.
• Make sure your composite key factor is large enough to prevent lower-priority attributes from overriding higher ones. For example, if stars can go up to 100 million, multiply by 1 billion to ensure forks (maxing out at 10 million) won’t affect the stars sort order.

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