Dynamoose查询DynamoDB性能劣化问题及优化方案咨询

问题背景 #

表中仅约270条数据，但使用Dynamoose查询耗时8-10秒，期望将耗时降至1秒以内。查询代码与Schema如下：

查询代码 #

const beforeBatchsQuery = new Date();
console.log("query before time", beforeBatchsQuery);

let batchs = await BatchsModel.query("statusValue")
.eq(statusValue)
.using("statusValue-sortKey-index")
.where("sortKey")
.ge(sortKey)
.all()
.exec();

const afterBatchsQuery = new Date();
console.log("query after time", afterBatchsQuery);

Schema定义 #

const schema = new dynamoose.Schema(
  {
    pk: {
      type: String,
      hashKey: true,
    },
    sk: {
      type: String,
      rangeKey: true,
    },
    afterTuition: {
      type: String,
      required: false,
    },
    applicationProcess: {
      type: Array,
      schema: [
        {
          type: Array,
          schema: [String],
          required: false,
        },
      ],
      required: false,
    },
    applicationProcessText: String,

    batchId: {
      type: String,
      required: false,
    },
    batchName: {
      type: String,
      required: false,
    },
    bootOption: {
      type: String,
      required: false,
    },
    brandName: {
      type: String,
      required: false,
    },
    campId: {
      type: String,
      required: false,
    },
    campUrl: {
      type: String,
      required: false,
    },
    categories: {
      type: Array,
      schema: [String],
      required: false,
    },
    challengeWay: {
      type: String,
      required: false,
    },
    city: {
      type: Array,
      schema: [String],
      required: false,
    },
    classTypeOption: {
      type: Array,
      schema: [String],
      required: false,
    },
    company: {
      type: String,
      required: false,
    },
    companyId: {
      type: String,
      required: false,
    },
    companyIds: {
      type: Array,
      schema: [
        {
          type: Object,
          schema: {
            companyId: String,
            companyRole: String,
            companyName: String,
          },
        },
      ],
    },
    computer: {
      type: String,
      required: false,
    },
    computerOption: {
      type: String,
      required: false,
    },
    costOption: {
      type: String,
      required: false,
    },
    courseValue: {
      type: Number,
      required: false,
    },
    createdAt: {
      type: String,
      required: false,
    },
    createDate: {
      type: String,
      required: false,
    },
    curriculumFile: {
      type: String,
      required: false,
    },

    curriculumUrl: {
      type: String,
      required: false,
    },
    cyclic: {
      type: Boolean,
      required: false,
    },
    dateFixed: {
      type: Object,
      schema: {
        regDate: String,
        regEndDate: String,
        endDate: String,
        startDate: String,
      },
    },
    dateStrings: {
      type: Object,
      schema: {
        regDate: String,
        regEndDate: String,
        endDate: String,
        startDate: String,
      },
    },
    deleted: {
      type: Boolean,
      required: false,
    },
    description: {
      type: [Object, String],
      schema: {
        benefits: {
          type: Array,
          schema: [String],
          required: false,
        },
        targets: {
          type: Array,
          schema: [String],
          required: false,
        },
        goals: {
          type: Array,
          schema: [String],
          required: false,
        },
      },
      schema: String,
      required: false,
    },

    earlyBirdEndDate: {
      type: String,
      required: false,
    },
    endDate: {
      type: String,
      required: false,
    },
    endDateFixed: {
      type: String,
      required: false,
    },
    entryFee: {
      type: [Number, dynamoose.type.NULL],
      required: false,
    },
    etcComputerChecked: {
      type: Boolean,
      required: false,
    },
    etcSubsidyChecked: {
      type: Boolean,
      required: false,
    },
    etcSubsidyText: {
      type: String,
      required: false,
    },
    govtCost: {
      type: Boolean,
      required: false,
    },
    govtCostValue: {
      type: String,
      required: false,
    },
    hasTracks: {
      type: Boolean,
      required: false,
    },
    hrdSeperated: {
      type: Boolean,
      required: false,
    },
    image: {
      type: String,
      required: false,
    },
    jobOpportunityText: String,

    jobSearchingServiceValues: {
      type: Array,
      schema: [Number],
    },
    keywords: {
      type: Array,
      schema: [String],
    },

    nbcardBalance: Boolean,
    nbcardRequired: Boolean,
    offlineRequired: Boolean,
    onoff: {
      type: String,
      required: false,
    },
    openCycle: {
      type: String,
      required: false,
    },

    partTime: Boolean,
    passRequired: Boolean,
    period: Number,
    place: {
      type: String,
      required: false,
    },
    preCourseOption: {
      type: Array,
      schema: [String],
    },
    preferentialTreatment: String,
    preNotice: Boolean,
    preNoticeEndDate: String,
    prepLevel: {
      type: Array,
      schema: [String],
    },
    projectValues: {
      type: Array,
      schema: [Number],
    },
    qualification: String,
    quota: Number,
    recommendedPreKnowledge: String,
    redirectCamp: {
      type: Object,
      schema: {
        batchId: String,
        title: String,
        campId: String,
      },
      required: false,
    },
    regDate: {
      type: String,
      required: false,
    },
    regDateFixed: {
      type: String,
      required: false,
    },
    regEndDate: {
      type: String,
      required: false,
    },
    regEndDateFixed: {
      type: String,
      required: false,
    },
    remark: {
      type: String,
      required: false,
    },
    requiredPreKnowledge: String,
    returnFee: {
      type: [Number, dynamoose.type.NULL],
      required: false,
    },
    reviews: {
      type: [String, dynamoose.type.NULL],
      required: false,
    },
    searchKeywords: {
      type: Object,
      schema: {
        keyword: String,
        dateRestrict: Number,
        includeKeyword: String,
      },
    },    
    sortKey: {
      type: String,
      required: true
    },
    startDate: {
      type: String,
      required: false,
    },
    startDateFixed: {
      type: String,
      required: false,
    },
    statusValue: {
      type: Number,
      required: true,
      index: {
        global: true,
        name: "statusValue-sortKey-index",
        hashKey: "statusValue",
        rangeKey: "sortKey",
        project: true,
        throughput: "ON_DEMAND",
      },
    },
    studyDayOption: Number,
    studyTime: {
      type: Array,
      schema: [
        {
          type: Object,
          schema: {
            startTime: String,
            endTime: String,
          },
        },
      ],
      required: false,
    },
    studyTimeFixed: {
      type: Array,
      schema: [Boolean],
    },
    studyTimePerUnit: {
      type: [String, Number],
      required: false,
    },
    studyTimeUnit: {
      type: String,
      required: false,
    },
    subsidyValues: Array,
    tags: {
      type: Array,
      schema: [String],
      required: false,
    },
    timeSpecified: {
      type: Object,
      schema: {
        regEndDate: Boolean,
        regDate: Boolean,
      },
      required: false,
    },
    timeStrings: {
      type: Object,
      schema: {
        regEndDate: String,
        regDate: String,
      },
      required: false,
    },
    title: {
      type: String,
      required: false,
    },
    tracks: {
      type: Array,
      schema: [
        {
          type: Object,
          schema: {
            category: String,
            keywords: {
              type: Array,
              schema: [String],
              required: false,
            },
            quota: Number,
            tags: {
              type: Array,
              schema: [String],
              required: false,
            },
          },
        },
      ],
      required: false,
    },
    tuition: Number,

    updatedAt: {
      type: String,
      required: false,
    },
  },
  {
    timestamps: false,
    saveUnknown: ["studyTimes.**", "assets.**", "jobOpportunityValues.**", "hrd.**", "earlyBird.**" ],
    indexes: {
      "statusValue-sortKey-index": { 
          type: 'global',
          hashKey: 'statusValue',
          rangeKey: 'sortKey',
          project: true,
          // throughput: "ON_DEMAND"
      }
    }
  }
);

const BatchsModel = dynamoose.model("abcd", schema, {
  create: false,
  waitForActive: false
});

module.exports = BatchsModel;

性能劣化原因分析 #

1. 全局索引重复定义：Schema中statusValue字段内部和顶层indexes配置中重复定义了同名索引，可能导致Dynamoose处理逻辑冲突，索引无法正确生效，查询退化为全表扫描。
2. 全属性投影开销：索引配置project: true返回所有字段，而Schema包含大量嵌套数组、对象，数据序列化/反序列化及网络传输会占用大量时间。
3. 索引状态未就绪：模型配置waitForActive: false，若索引处于未激活状态，查询会自动 fallback 到全表扫描，大幅增加耗时。
4. 不必要的数据序列化：saveUnknown配置允许保存未知字段，返回结果可能包含大量额外数据，加重序列化负担。
5. 参数类型不匹配：若查询时statusValue或sortKey参数类型与Schema定义不符，DynamoDB会做隐式类型转换，导致索引失效。

优化方案 #

1. 清理重复索引定义 #

删除statusValue字段内的index配置，仅保留顶层indexes中的定义，确保索引唯一生效：

// 移除statusValue字段内的index配置
statusValue: {
  type: Number,
  required: true,
},
// 保留顶层indexes配置
indexes: {
  "statusValue-sortKey-index": { 
      type: 'global',
      hashKey: 'statusValue',
      rangeKey: 'sortKey',
      // 改为投影必要字段
      project: ["batchId", "batchName", "sortKey", "statusValue"], // 按需添加业务所需字段
      throughput: "ON_DEMAND"
  }
}

2. 投影必要字段 #

将project: true改为明确指定业务需要的字段，减少数据传输和序列化时间，避免返回全量冗余数据。

3. 确保索引处于激活状态 #

• 登录AWS控制台，确认statusValue-sortKey-index状态为ACTIVE。
• 模型配置中暂时设置waitForActive: true，确保查询时索引已就绪（生产环境可根据实际情况调整）：

const BatchsModel = dynamoose.model("abcd", schema, {
  create: false,
  waitForActive: true
});

4. 限制未知字段返回 #

若业务不需要saveUnknown对应的字段，直接关闭该配置；若必须保留，缩小匹配范围：

saveUnknown: false, // 或仅保留必要的路径

5. 校验查询参数类型 #

确保statusValue为Number类型，sortKey为String类型，与Schema定义一致，避免隐式类型转换导致索引失效：

// 示例：强制转换参数类型
const statusValue = Number(req.query.statusValue);
const sortKey = String(req.query.sortKey);

6. 启用Dynamoose缓存 #

对于不频繁更新的数据，开启缓存减少重复查询开销：

const BatchsModel = dynamoose.model("abcd", schema, {
  create: false,
  waitForActive: true,
  cache: true // 启用缓存
});

7. 检查网络区域 #

确保应用部署区域与DynamoDB表所在区域一致，跨区域查询会增加网络延迟。

验证优化效果 #

优化后重新执行查询，观察耗时变化。若仍未达标，可通过AWS CloudWatch监控QueryLatency等指标，或开启Dynamoose调试日志排查瓶颈：

dynamoose.logger.providers.set(dynamoose.logger.providers.console());

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