PostgreSQL百万级NFT数据末页查询缓慢问题及优化咨询

核心原因 #

末页查询慢的本质是大OFFSET导致数据库需要扫描并丢弃前面的千万条数据，再返回目标10条；同时默认的count查询也会额外消耗大量时间。

具体解决方案 #

1. 改用**键集分页（游标分页）**替代OFFSET分页

这是解决大OFFSET分页慢的最优方案，直接利用ID索引定位数据，避免扫描无关行：

• 原理：不再依赖页码和OFFSET，而是以上一页最后一条数据的ID作为查询条件，倒序查询时取id < 上一页最小ID的记录。
• Rails + Kaminari实现示例：

  # 第一页查询
  if params[:last_id].blank?
    @nfts = NFT.order(id: :desc).limit(10)
  else
    # 后续分页查询，直接用last_id过滤
    @nfts = NFT.where('id < ?', params[:last_id]).order(id: :desc).limit(10)
  end
  # 返回给前端时带上当前页的最小ID，用于下一页查询
  render json: { nfts: @nfts, last_id: @nfts.last&.id }
  

• 注意：该方式不支持直接跳转到任意页码，但如果业务仅需要上/下一页导航，完全可以替代传统分页，末页查询速度会和前页一致。

2. 优化或移除count查询

从日志看，count查询耗时近800ms，这部分可以大幅优化：

• 不需要总页数/条数：直接禁用Kaminari的count查询，添加.without_count即可：

  NFT.order(id: :desc).page(params[:page]).per(10).without_count
  

• 需要总条数：
  • 缓存总条数：将SELECT COUNT(*) FROM nfts的结果缓存到Redis或内存中，定期更新（比如每小时更新一次，适合数据增删不频繁的场景）。
  • 使用近似值：用PostgreSQL内置的近似计数，速度极快：

    SELECT reltuples::bigint AS approximate_count FROM pg_class WHERE relname = 'nfts';
    

    Rails中可以用：

    approximate_count = ActiveRecord::Base.connection.execute("SELECT reltuples::bigint FROM pg_class WHERE relname = 'nfts'").first['reltuples']
    

3. 确保索引被正确使用

虽然ID字段已加索引，但需要确认查询是否真的用到了索引：

• 执行EXPLAIN ANALYZE SELECT "nfts".* FROM "nfts" ORDER BY id desc LIMIT 10 OFFSET 9999910;查看执行计划，确认是否出现Index Scan using idx_nfts_id on nfts（假设索引名是idx_nfts_id）。
• 如果未走索引，执行ANALYZE nfts;更新表的统计信息，让PostgreSQL优化器选择正确的索引策略。

4. 调整PostgreSQL配置参数

针对大分页场景，优化数据库内存配置：

• 临时调整work_mem（适合排序、分组等操作）：

  SET work_mem = '64MB'; -- 可根据服务器内存调整，默认是4MB
  

  永久修改需编辑postgresql.conf，重启数据库生效。
• 调整shared_buffers：设置为服务器内存的1/4左右，确保索引和常用数据能被缓存。

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