DynamoDB本地二级索引 #

LSI特点：
├── 与表共享分区键
├── 使用不同的排序键
├── 创建表时定义
├── 支持强一致性读取
├── 共享表的容量
└── 最多5个/表

aws dynamodb create-table \
  --table-name Orders \
  --attribute-definitions \
    AttributeName=CustomerId,AttributeType=S \
    AttributeName=OrderId,AttributeType=S \
    AttributeName=OrderDate,AttributeType=S \
  --key-schema \
    AttributeName=CustomerId,KeyType=HASH \
    AttributeName=OrderId,KeyType=RANGE \
  --local-secondary-indexes \
    '[
      {
        "IndexName": "OrderDateIndex",
        "KeySchema": [
          {"AttributeName": "CustomerId", "KeyType": "HASH"},
          {"AttributeName": "OrderDate", "KeyType": "RANGE"}
        ],
        "Projection": {"ProjectionType": "ALL"}
      }
    ]' \
  --billing-mode PAY_PER_REQUEST

const { CreateTableCommand } = require('@aws-sdk/client-dynamodb');

const command = new CreateTableCommand({
  TableName: 'Orders',
  AttributeDefinitions: [
    { AttributeName: 'CustomerId', AttributeType: 'S' },
    { AttributeName: 'OrderId', AttributeType: 'S' },
    { AttributeName: 'OrderDate', AttributeType: 'S' },
    { AttributeName: 'TotalAmount', AttributeType: 'N' }
  ],
  KeySchema: [
    { AttributeName: 'CustomerId', KeyType: 'HASH' },
    { AttributeName: 'OrderId', KeyType: 'RANGE' }
  ],
  LocalSecondaryIndexes: [
    {
      IndexName: 'OrderDateIndex',
      KeySchema: [
        { AttributeName: 'CustomerId', KeyType: 'HASH' },
        { AttributeName: 'OrderDate', KeyType: 'RANGE' }
      ],
      Projection: {
        ProjectionType: 'ALL'
      }
    },
    {
      IndexName: 'TotalAmountIndex',
      KeySchema: [
        { AttributeName: 'CustomerId', KeyType: 'HASH' },
        { AttributeName: 'TotalAmount', KeyType: 'RANGE' }
      ],
      Projection: {
        ProjectionType: 'INCLUDE',
        NonKeyAttributes: ['OrderId', 'OrderDate', 'Status']
      }
    }
  ],
  BillingMode: 'PAY_PER_REQUEST'
});

创建LSI必须满足：
├── 表必须有复合主键（分区键+排序键）
├── LSI分区键必须与表相同
├── LSI排序键必须与表不同
├── 只能在创建表时定义
└── 排序键属性必须定义在AttributeDefinitions中

{
  IndexName: 'OrderDateIndex',
  KeySchema: [
    { AttributeName: 'CustomerId', KeyType: 'HASH' },
    { AttributeName: 'OrderDate', KeyType: 'RANGE' }
  ],
  Projection: {
    ProjectionType: 'KEYS_ONLY'
  }
}

{
  IndexName: 'OrderDateIndex',
  KeySchema: [
    { AttributeName: 'CustomerId', KeyType: 'HASH' },
    { AttributeName: 'OrderDate', KeyType: 'RANGE' }
  ],
  Projection: {
    ProjectionType: 'INCLUDE',
    NonKeyAttributes: ['OrderId', 'TotalAmount', 'Status']
  }
}

{
  IndexName: 'OrderDateIndex',
  KeySchema: [
    { AttributeName: 'CustomerId', KeyType: 'HASH' },
    { AttributeName: 'OrderDate', KeyType: 'RANGE' }
  ],
  Projection: {
    ProjectionType: 'ALL'
  }
}

const { QueryCommand } = require('@aws-sdk/lib-dynamodb');

const response = await docClient.send(new QueryCommand({
  TableName: 'Orders',
  IndexName: 'OrderDateIndex',
  KeyConditionExpression: 'CustomerId = :cid AND OrderDate BETWEEN :start AND :end',
  ExpressionAttributeValues: {
    ':cid': 'customer001',
    ':start': '2024-01-01',
    ':end': '2024-12-31'
  }
}));

const response = await docClient.send(new QueryCommand({
  TableName: 'Orders',
  IndexName: 'OrderDateIndex',
  KeyConditionExpression: 'CustomerId = :cid',
  ExpressionAttributeValues: {
    ':cid': 'customer001'
  },
  ConsistentRead: true  // LSI支持强一致性
}));

// 按OrderDate升序
const response = await docClient.send(new QueryCommand({
  TableName: 'Orders',
  IndexName: 'OrderDateIndex',
  KeyConditionExpression: 'CustomerId = :cid',
  ExpressionAttributeValues: {
    ':cid': 'customer001'
  },
  ScanIndexForward: true  // 升序
}));

// 按TotalAmount降序
const response = await docClient.send(new QueryCommand({
  TableName: 'Orders',
  IndexName: 'TotalAmountIndex',
  KeyConditionExpression: 'CustomerId = :cid',
  ExpressionAttributeValues: {
    ':cid': 'customer001'
  },
  ScanIndexForward: false  // 降序
}));

// 表设计
// PK: CustomerId
// SK: OrderId
// LSI1: OrderDate
// LSI2: TotalAmount

// 按订单ID查询（表主键）
await docClient.send(new QueryCommand({
  TableName: 'Orders',
  KeyConditionExpression: 'CustomerId = :cid AND OrderId = :oid'
}));

// 按日期排序查询（LSI1）
await docClient.send(new QueryCommand({
  TableName: 'Orders',
  IndexName: 'OrderDateIndex',
  KeyConditionExpression: 'CustomerId = :cid',
  ScanIndexForward: false  // 最新订单在前
}));

// 按金额排序查询（LSI2）
await docClient.send(new QueryCommand({
  TableName: 'Orders',
  IndexName: 'TotalAmountIndex',
  KeyConditionExpression: 'CustomerId = :cid',
  ScanIndexForward: false  // 最高金额在前
}));

// 表设计
// PK: DeviceId
// SK: EventId
// LSI: Timestamp

// 按时间范围查询
const response = await docClient.send(new QueryCommand({
  TableName: 'Events',
  IndexName: 'TimestampIndex',
  KeyConditionExpression: 'DeviceId = :did AND Timestamp BETWEEN :start AND :end',
  ExpressionAttributeValues: {
    ':did': 'device001',
    ':start': '2024-01-01T00:00:00Z',
    ':end': '2024-01-01T23:59:59Z'
  }
}));

// 需要强一致性的场景
const response = await docClient.send(new QueryCommand({
  TableName: 'Inventory',
  IndexName: 'ProductCodeIndex',
  KeyConditionExpression: 'WarehouseId = :wid AND ProductCode = :code',
  ExpressionAttributeValues: {
    ':wid': 'warehouse001',
    ':code': 'PROD001'
  },
  ConsistentRead: true  // 强一致性
}));

限制：
├── 每表最多5个LSI
├── 只能在创建表时定义
├── 无法删除或修改
└── 排序键属性必须定义

大小限制：
├── 索引键大小：最大2048字节
├── 每个分区键值对应的项目总大小：最大10GB
└── 单个项目大小：最大400KB（与表相同）

项目集合：
├── 同一分区键下的所有项目
├── 包括表项目和所有LSI项目
├── 总大小不超过10GB
└── 超过限制会导致写入失败

选择LSI：
├── 需要强一致性读取
├── 同一分区键下需要多种排序
├── 项目大小可控（<400KB）
├── 分区键下数据量可控（<10GB）
└── 查询模式固定

选择GSI：
├── 需要不同的分区键
├── 项目可能超过400KB
├── 分区键下数据量大
├── 需要灵活添加索引
└── 可以接受最终一致性

设计建议：
├── 仅创建必要的LSI
├── 评估项目集合大小
├── 选择合适的投影类型
├── 考虑未来扩展需求
└── 监控索引使用情况

// 不好的设计：可能导致项目集合过大
{
  PK: 'STATUS#ACTIVE',  // 低基数分区键
  SK: 'ORDER#001'
}

// 好的设计：分散数据
{
  PK: 'CUSTOMER#001',  // 高基数分区键
  SK: 'ORDER#001'
}

投影类型选择：
├── KEYS_ONLY
│   ├── 仅需要回表查询
│   └── 存储成本敏感
├── INCLUDE
│   ├── 需要部分属性
│   └── 平衡存储和性能
└── ALL
    ├── 需要所有属性
    └── 避免回表查询

// 创建表
const command = new CreateTableCommand({
  TableName: 'Orders',
  AttributeDefinitions: [
    { AttributeName: 'CustomerId', AttributeType: 'S' },
    { AttributeName: 'OrderId', AttributeType: 'S' },
    { AttributeName: 'OrderDate', AttributeType: 'S' },
    { AttributeName: 'TotalAmount', AttributeType: 'N' },
    { AttributeName: 'Status', AttributeType: 'S' }
  ],
  KeySchema: [
    { AttributeName: 'CustomerId', KeyType: 'HASH' },
    { AttributeName: 'OrderId', KeyType: 'RANGE' }
  ],
  LocalSecondaryIndexes: [
    {
      IndexName: 'OrderDateIndex',
      KeySchema: [
        { AttributeName: 'CustomerId', KeyType: 'HASH' },
        { AttributeName: 'OrderDate', KeyType: 'RANGE' }
      ],
      Projection: { ProjectionType: 'ALL' }
    },
    {
      IndexName: 'TotalAmountIndex',
      KeySchema: [
        { AttributeName: 'CustomerId', KeyType: 'HASH' },
        { AttributeName: 'TotalAmount', KeyType: 'RANGE' }
      ],
      Projection: { ProjectionType: 'INCLUDE', NonKeyAttributes: ['OrderId', 'OrderDate', 'Status'] }
    }
  ],
  BillingMode: 'PAY_PER_REQUEST'
});

// 查询客户订单（按日期）
async function getCustomerOrdersByDate(customerId, limit = 10) {
  return await docClient.send(new QueryCommand({
    TableName: 'Orders',
    IndexName: 'OrderDateIndex',
    KeyConditionExpression: 'CustomerId = :cid',
    ExpressionAttributeValues: { ':cid': customerId },
    ScanIndexForward: false,
    Limit: limit
  }));
}

// 查询客户订单（按金额）
async function getCustomerOrdersByAmount(customerId, minAmount) {
  return await docClient.send(new QueryCommand({
    TableName: 'Orders',
    IndexName: 'TotalAmountIndex',
    KeyConditionExpression: 'CustomerId = :cid AND TotalAmount >= :min',
    ExpressionAttributeValues: {
      ':cid': customerId,
      ':min': minAmount
    },
    ScanIndexForward: false
  }));
}