AWS Athena分页查询S3数据集性能问题及优化方案咨询

• 海量数据集存储于S3，通过Python调用AWS Athena执行查询，查询接收3个入参：marketplaceId、startIndex、endIndex
• 初始测试查询50条记录总耗时达16秒，经时间戳调试确认：单条取数查询实际耗时6秒，总耗时偏高是因为每次请求同时执行2条SQL：1条分页取数语句、1条select count(*) from category_info统计总行数语句
• 待明确问题：
  • 当前实现的分页逻辑是否存在问题
  • 是否存在无需单独执行count语句即可获取总行数的方案，降低整体查询耗时

SELECT 
  dataset_date, 
  marketplace_id, 
  gl_product_group, 
  gl_product_group_desc, 
  browse_node_id, 
  browse_node_name, 
  root_browse_node_id, 
  browse_root_name, 
  wt_xref_id, 
  gt_xref_id, 
  node_path, 
  total_count_of_asins, 
  buyable_asin_count, 
  glance_view_count_t12m, 
  ord_cnt, 
  price_p50, 
  price_p90, 
  price_p100, 
  row_num 
FROM 
  (
    SELECT 
      dataset_date, 
      marketplace_id, 
      gl_product_group, 
      gl_product_group_desc, 
      browse_node_id, 
      browse_node_name, 
      root_browse_node_id, 
      browse_root_name, 
      wt_xref_id, 
      gt_xref_id, 
      node_path, 
      total_count_of_asins, 
      buyable_asin_count, 
      glance_view_count_t12m, 
      ord_cnt, 
      price_p50, 
      price_p90, 
      price_p100, 
      row_number() over (
        order by 
          browse_node_id, 
          gl_product_group, 
          glance_view_count_t12m desc
      ) as row_num 
    from 
      (
        select 
          * 
        from 
          category_info 
        WHERE 
          marketplace_id = '<marketplaceId>'
      )
  ) 
WHERE 
  row_num between '<startIndex>' 
  and '<endIndex>';

现有分页逻辑的问题 #

现有逻辑语义上可以实现按指定排序规则取对应区间的记录，但存在明显的性能缺陷：

• 属于Athena（基于Trino/Presto引擎）上典型的低效offset分页实现：无论查询的是哪一页的数据，每次执行都要先扫描过滤后marketplace_id对应的全量数据，完成全量排序、为所有行生成row_num后，再过滤出目标区间的记录。翻页深度越大，性能越差，哪怕仅取50条记录，也要完成全量数据的计算。
• 存在冗余写法增加不必要开销：内层嵌套的select * from category_info WHERE marketplace_id = '<marketplaceId>'属于多余嵌套，且select *会读取表中所有列的数据，但外层查询仅使用到部分字段，额外增加了S3扫描量和Athena计算负载。

免单独count查询获取总行数的方案 #

直接通过窗口函数将分页数据查询和总行数统计合并为单次查询即可，无需单独执行count语句重复扫描数据。
核心逻辑是在生成row_num的同一层计算逻辑中，增加无分区的count(*) over()窗口函数，直接在一次扫描计算中同时拿到分页行号和符合过滤条件的总记录数，同时去掉冗余嵌套、只读取需要的列，修改后的SQL如下：

SELECT 
  dataset_date, 
  marketplace_id, 
  gl_product_group, 
  gl_product_group_desc, 
  browse_node_id, 
  browse_node_name, 
  root_browse_node_id, 
  browse_root_name, 
  wt_xref_id, 
  gt_xref_id, 
  node_path, 
  total_count_of_asins, 
  buyable_asin_count, 
  glance_view_count_t12m, 
  ord_cnt, 
  price_p50, 
  price_p90, 
  price_p100, 
  row_num,
  total_count
FROM 
  (
    SELECT 
      dataset_date, 
      marketplace_id, 
      gl_product_group, 
      gl_product_group_desc, 
      browse_node_id, 
      browse_node_name, 
      root_browse_node_id, 
      browse_root_name, 
      wt_xref_id, 
      gt_xref_id, 
      node_path, 
      total_count_of_asins, 
      buyable_asin_count, 
      glance_view_count_t12m, 
      ord_cnt, 
      price_p50, 
      price_p90, 
      price_p100, 
      row_number() over (
        order by 
          browse_node_id, 
          gl_product_group, 
          glance_view_count_t12m desc
      ) as row_num,
      count(*) over () as total_count
    from category_info 
    WHERE marketplace_id = '<marketplaceId>'
  ) t
WHERE 
  row_num between '<startIndex>' and '<endIndex>';

该改法可将原来两次全表扫描的逻辑合并为一次，总耗时可直接降到单条查询的6秒区间，去掉select *的冗余读取后，耗时还可进一步降低。

长期性能优化建议 #

• 深翻页场景替换为键集分页（seek分页）：放弃row_number()类offset分页逻辑，每次翻页时传入上一页最后一条记录的排序字段值（即上一页最后一条的browse_node_id、gl_product_group、glance_view_count_t12m取值），下一页查询直接通过条件过滤加limit 50实现，无需为全量数据生成行号，翻页性能不会随页码升高下降。
• 表结构层面优化：将marketplace_id设置为表分区键，按常用排序字段设置数据排序、分桶，存储格式选用Parquet/ORC这类列式压缩格式，可大幅降低数据扫描量，查询速度可从秒级降至百毫秒级。
• 若数据更新频率低，可提前按marketplace_id预计算各维度的总记录数，存入独立的元数据表，查询时直接读取元数据获取总数，省去运行时count计算的开销。

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