使用LSI查询DynamoDB时未按roleType排序的问题排查

API 探路者

2026-8-13

DynamoDB LSI查询未按roleType排序问题

问题描述

我有一个DynamoDB表用于存储用户及其roleType（可选值为['PO','CCM','SU']），表的分区键是名为fakeKey的虚拟属性，所有条目的fakeKey值均相同。我创建了一个以roleType为排序键的本地二级索引（LSI），期望查询时能获取按roleType排序的全部数据，但实际未生效。

查询代码（Node.js）

dynamoDB.query({
  TableName: process.env.TABLE_USER_V2 as string,
  indexName: sortBy,
  ScanIndexForward: ascending,
  KeyConditionExpression: "fakeKey = :fake",
  ExpressionAttributeValues: {
    ":fake": 'fake',
  },
},
 (err: any, data: any) => {
  if (err) reject(err);
  else {
    resolve(data);
  }
  console.log("scanResult--", data);
});

返回的数据是随机顺序，完全未按roleType排序。

补充信息

共创建了5个以fakeKey为分区键的LSI，排序键分别为：
- createdBy（字符串类型）
- createdDate（数值类型）
- email（字符串类型）
- modifiedDate（数值类型）
- roleType（字符串类型）

查询返回的无序数据示例

(17) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]
0:
{roleType: 'CCM', createdDate: 1667914128541, modifiedDate: 1667914128541, email: 'Candra Aubry@se.com', fakeKey: 'fake', …}
1:
{roleType: 'PO', createdDate: 1667914128445, modifiedDate: 1667914128445, email: 'Charlot Clair@se.com', fakeKey: 'fake', …}
2:
{roleType: 'CCM', createdDate: 1667914128500, modifiedDate: 1667914128500, email: 'Dagmar Doretta@se.com', fakeKey: 'fake', …}
3:
{roleType: 'CCM', createdDate: 1667914128507, modifiedDate: 1667914128507, email: 'Deny Helsa@se.com', fakeKey: 'fake', …}
4:
{roleType: 'CCM', createdDate: 1667914128454, modifiedDate: 1667914128454, email: 'Druci Beitris@se.com', fakeKey: 'fake', …}
5:
{roleType: 'CCM', createdDate: 1667914128568, modifiedDate: 1667914128568, email: 'Eirena Mariquilla@se.com', fakeKey: 'fake', …}
6:
{roleType: 'CCM', createdDate: 1667914128417, modifiedDate: 1667914128417, email: 'Evangeline Marquita@se.com', fakeKey: 'fake', …}
7:
{roleType: 'CCM', createdDate: 1667914128018, modifiedDate: 1667914128018, email: 'Eve Eryn@se.com', fakeKey: 'fake', …}
8:
{roleType: 'SU', createdDate: 1667832856296, modifiedDate: 1667832856296, lastModifiedBy: 'ADM694685', email: 'harit@se.com', …}
9:
{roleType: 'CCM', createdDate: 1667914128439, modifiedDate: 1667914128439, email: 'Isabelita Miriam@se.com', fakeKey: 'fake', …}
10:
{roleType: 'CCM', createdDate: 1667914128394, modifiedDate: 1667914128394, email: 'Keely Breanne@se.com', fakeKey: 'fake', …}
11:
{roleType: 'CCM', createdDate: 1667914128475, modifiedDate: 1667914128475, email: 'Leyla Barbaraanne@se.com', fakeKey: 'fake', …}
12:
{roleType: 'CCM', createdDate: 1667914128518, modifiedDate: 1667914128518, email: 'Lucie Rena@se.com', fakeKey: 'fake', …}

表描述信息

{
  "Table": {
    "AttributeDefinitions": [
      {
        "AttributeName": "admId",
        "AttributeType": "S"
      },
      {
        "AttributeName": "createdBy",
        "AttributeType": "S"
      },
      {
        "AttributeName": "createdDate",
        "AttributeType": "N"
      },
      {
        "AttributeName": "email",
        "AttributeType": "S"
      },
      {
        "AttributeName": "fakeKey",
        "AttributeType": "S"
      },
      {
        "AttributeName": "modifiedDate",
        "AttributeType": "N"
      },
      {
        "AttributeName": "name",
        "AttributeType": "S"
      },
      {
        "AttributeName": "roleType",
        "AttributeType": "S"
      }
    ],
    "TableName": "e-system_users_roles_v3-dmsm_data-dev",
    "KeySchema": [
      {
        "AttributeName": "fakeKey",
        "KeyType": "HASH"
      },
      {
        "AttributeName": "name",
        "KeyType": "RANGE"
      }
    ],
    "TableStatus": "ACTIVE",
    "CreationDateTime": {
    },
    "ProvisionedThroughput": {
      "NumberOfDecreasesToday": 0,
      "ReadCapacityUnits": 0,
      "WriteCapacityUnits": 0
    },
    "TableSizeBytes": 2052,
    "ItemCount": 17,
    "TableArn": "arn:aws:dynamodb:us-east-1:xxxxxxxxxxxx:table/e-system_users_roles_v3-dmsm_data-dev",
    "TableId": "089c5a70-6819-47be-85c0-7e612faccf56",
    "BillingModeSummary": {
      "BillingMode": "PAY_PER_REQUEST",
      "LastUpdateToPayPerRequestDateTime": {
      }
    },
    "LocalSecondaryIndexes": [
      {
        "IndexName": "modifiedDate-index",
        "KeySchema": [
          {
            "AttributeName": "fakeKey",
            "KeyType": "HASH"
          },
          {
            "AttributeName": "modifiedDate",
            "KeyType": "RANGE"
          }
        ],
        "Projection": {
          "ProjectionType": "ALL"
        },
        "IndexSizeBytes": 2017,
        "ItemCount": 17,
        "IndexArn": "arn:aws:dynamodb:us-east-1:xxxxxxxxxxxx:table/e-system_users_roles_v3-dmsm_data-dev/index/modifiedDate-index"
      },
      {
        "IndexName": "createdDate-index",
        "KeySchema": [
          {
            "AttributeName": "fakeKey",
            "KeyType": "HASH"
          },
          {
            "AttributeName": "createdDate",
            "KeyType": "RANGE"
          }
        ],
        "Projection": {
          "ProjectionType": "ALL"
        },
        "IndexSizeBytes": 2017,
        "ItemCount": 17,
        "IndexArn": "arn:aws:dynamodb:us-east-1:xxxxxxxxxxxx:table/e-system_users_roles_v3-dmsm_data-dev/index/createdDate-index"
      },
      {
        "IndexName": "roleType-index",
        "KeySchema": [
          {
            "AttributeName": "fakeKey",
            "KeyType": "HASH"
          },
          {
            "AttributeName": "roleType",
            "KeyType": "RANGE"
          }
        ],
        "Projection": {
          "ProjectionType": "ALL"
        },
        "IndexSizeBytes": 2017,
        "ItemCount": 17,
        "IndexArn": "arn:aws:dynamodb:us-east-1:xxxxxxxxxxxx:table/e-system_users_roles_v3-dmsm_data-dev/index/roleType-index"
      },
      {
        "IndexName": "createdBy-index",
        "KeySchema": [
          {
            "AttributeName": "fakeKey",
            "KeyType": "HASH"
          },
          {
            "AttributeName": "createdBy",
            "KeyType": "RANGE"
          }
        ],
        "Projection": {
          "ProjectionType": "ALL"
        },
        "IndexSizeBytes": 147,
        "ItemCount": 1,
        "IndexArn": "arn:aws:dynamodb:us-east-1:xxxxxxxxxxxx:table/e-system_users_roles_v3-dmsm_data-dev/index/createdBy-index"
      },
      {
        "IndexName": "email-index",
        "KeySchema": [
          {
            "AttributeName": "fakeKey",
            "KeyType": "HASH"
          },
          {
            "AttributeName": "email",
            "KeyType": "RANGE"
          }
        ],
        "Projection": {
          "ProjectionType": "ALL"
        },
        "IndexSizeBytes": 2017,
        "ItemCount": 17,
        "IndexArn": "arn:aws:dynamodb:us-east-1:xxxxxxxxxxxx:table/e-system_users_roles_v3-dmsm_data-dev/index/email-index"
      }
    ],
    "GlobalSecondaryIndexes": [
      {
        "IndexName": "admId-email-index",
        "KeySchema": [
          {
            "AttributeName": "admId",
            "KeyType": "HASH"
          },
          {
            "AttributeName": "email",
            "KeyType": "RANGE"
          }
        ],
        "Projection": {
          "ProjectionType": "ALL"
        },
        "IndexStatus": "ACTIVE",
        "ProvisionedThroughput": {
          "NumberOfDecreasesToday": 0,
          "ReadCapacityUnits": 0,
          "WriteCapacityUnits": 0
        },
        "IndexSizeBytes": 2017,
        "ItemCount": 17,
        "IndexArn": "arn:aws:dynamodb:us-east-1:xxxxxxxxxxxx:table/e-system_users_roles_v3-dmsm_data-dev/index/admId-email-index"
      }
    ],
    "TableClassSummary": {
      "TableClass": "STANDARD"
    }
  }
}

问题原因及解决方案

核心问题

从表描述可以看到，你创建的LSI索引名称是roleType-index，但查询代码中使用的indexName变量是sortBy。如果sortBy的值不是roleType-index，而是roleType，那么查询会默认使用主表，而非指定的LSI，自然不会按roleType排序。

解决方案

确认sortBy变量的值：确保sortBy被赋值为roleType-index（即表描述中LSI的实际名称），而不是roleType。
修正查询代码：如果sortBy的取值逻辑有误，直接指定索引名称：

dynamoDB.query({
  TableName: process.env.TABLE_USER_V2 as string,
  IndexName: "roleType-index", // 替换为实际的LSI名称
  ScanIndexForward: ascending,
  KeyConditionExpression: "fakeKey = :fake",
  ExpressionAttributeValues: {
    ":fake": 'fake',
  },
},
 (err: any, data: any) => {
  if (err) reject(err);
  else {
    resolve(data);
  }
  console.log("scanResult--", data);
});

验证索引生效：查询后检查返回结果，DynamoDB的LSI查询会严格按照索引的排序键（roleType）的字符串字典序返回数据，顺序应为CCM → PO → SU。

额外说明

所有条目使用相同的fakeKey会导致所有数据存储在同一个分区，当数据量增大时会引发分区热点问题，建议重新设计分区键策略，避免单分区过载。
字符串排序是基于UTF-8字典序，CCM的首字母C在P和S之前，所以升序排列时会先返回所有CCM，再是PO，最后是SU。

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

使用LSI查询DynamoDB时未按roleType排序的问题排查

问题描述 #

查询代码（Node.js） #

补充信息 #

查询返回的无序数据示例 #

表描述信息 #

问题原因及解决方案 #

核心问题 #

解决方案 #

额外说明 #