DynamoDB使用ORDER BY查询报ValidationException错误原因

你遇到的报错核心来自DynamoDB PartiQL查询的底层执行逻辑限制，官方给出的错误提示存在一定误导性，具体规则如下：

• DynamoDB的查询分两类底层操作：Query和Scan
  • Scan是全量扫描表/指定索引的所有数据，支持任意字段的过滤条件（包括你写的begins_with前缀匹配），但完全不支持ORDER BY排序，因为DynamoDB不会为Scan操作做全量结果的内存排序，性能无法支撑。你去掉ORDER BY后语句能正常执行，就是因为DynamoDB自动把该语句判定为Scan操作执行。
  • Query是按索引定位查询，强制要求WHERE子句中必须包含索引分区键（HASH键）的精确等值匹配条件（只能用=，不支持begins_with、IN、>/<这类范围/多值匹配），Query的结果天然按照索引的排序键（RANGE键）预排序存储，因此只有Query操作支持ORDER BY，且ORDER BY后只能跟该索引的排序键字段，正序倒序均可。
    你加了ORDER BY后，DynamoDB要求语句必须走Query执行，但你的WHERE子句里对composite_pk_1用了begins_with范围匹配，没有提供分区键的精确等值条件，因此触发了报错。
    另外就算你补上了分区键等值条件，如果expected_delivery不是你查询所用索引的排序键，语句依然会执行失败——DynamoDB不支持对非索引排序键的任意字段做原生排序。

按照以下步骤调整即可：

• 先确认当前查询的composite_pk_1-index二级索引的键配置，明确哪个字段是分区键、哪个是排序键。
• 调整索引设计匹配你的查询+排序需求：
  • 如果你需要按expected_delivery字段排序，必须将expected_delivery设置为所用索引的排序键
  • 把你筛选逻辑中固定等值匹配的部分拆成单独字段，设置为该索引的分区键。你现在用begins_with匹配的composite_pk_1是拼接复合字段，不能直接作为分区键做前缀匹配，需要把前缀中固定不变的部分拆出来单独作为分区键字段，剩余可变部分如果需要做范围匹配，可以拼接到排序键中。
• 调整查询语句符合Query操作要求：
  • WHERE子句第一部分必须是索引分区键的=精确匹配条件
  • 其余范围过滤、属性过滤条件追加在分区键条件之后
  • ORDER BY后仅保留该索引的排序键字段（也就是你要排序的expected_delivery）
    符合规则的语句示例：

  SELECT * FROM "transactions"."new_delivery_index" 
  WHERE fixed_partition_field = '0#3#435634652' -- 分区键必须精确等值
  AND begins_with(sort_key_field, '69992528') -- 排序键支持范围匹配
  ORDER BY expected_delivery ASC
  

• 如果你暂时没法调整索引结构，也没有固定等值的分区键条件可以用，就不能用DynamoDB原生ORDER BY：可以先通过Scan查询出所有符合前缀条件的数据，在业务应用代码层面对expected_delivery字段做排序，注意该方案仅适合数据量较小的场景，数据量大时扫描+内存排序的性能会非常差。

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