如何缩短PostgreSQL单条视频数据查询耗时？当前查询需14-16秒

第一步：先定位耗时根因 #

• 把业务用到的查询SQL直接复制到PostgreSQL客户端，加EXPLAIN ANALYZE执行看执行计划，先确认SQL本身的执行耗时是否达到10秒以上，如果是优先优化数据库侧逻辑
• 给Java代码加分段耗时埋点：分别统计「数据库连接建立耗时」「SQL执行耗时」「结果集转Java对象耗时」「跨网络传输耗时」四个节点，精准定位慢在哪一环

第二步：数据库侧优化（大概率为核心瓶颈） #

• 检查查询条件涉及的字段是否添加了合适的B树索引，比如视频主键、业务筛选用到的分类ID、上下架状态字段等，注意如果查询逻辑里存在函数包裹索引字段、字段类型隐式转换的情况，索引会直接失效
• 大字段拆分：不要把视频二进制流、高清封面这类大容量字段和视频基础信息存在同一张主表，把大字段单独拆到关联表中，列表/单条基础信息查询时不关联大字段表，仅当用户触发查看视频详情的操作时，再单独查询大字段内容
• 检查PostgreSQL基础配置：work_mem、shared_buffers参数如果设置过小，大表查询时会产生大量落盘临时排序，性能会暴跌，可根据服务器内存容量调整参数值
• 热点数据加缓存：如果查询的是访问频率高的视频内容，用Redis缓存对应视频的业务数据，缓存过期时间根据内容更新频率调整，避免每次请求都查库

第三步：Java代码侧优化 #

• 检查FreeClassesAdapter.java的数据库连接逻辑：不要每次查询都新建数据库连接，接入HikariCP等成熟的数据库连接池复用连接，可避免每次新建连接产生的3-5秒额外开销
• 不要用SELECT *做查询，只取业务需要的字段，减少不必要的大字段拉取和数据传输开销
• 检查结果集映射逻辑：避免在结果集循环里嵌套执行子查询，手动写的映射逻辑如果存在大量反射调用，可切换为MyBatis等成熟ORM框架的结果映射能力，性能更稳定
• 检查UiUxActivity.java的调用逻辑：不要在主线程直接执行数据库查询，虽然不会减少查询本身的耗时，但会导致界面卡顿，建议把查询逻辑放到子线程执行，同时增加加载状态提示

第四步：网络侧优化 #

• 确保应用服务和PostgreSQL服务部署在同一内网可用区，走内网传输数据，避免跨公网传输大字段产生的额外耗时
• 大字段传输开启gzip压缩，降低传输的数据体积

内容的提问来源于stack exchange，提问作者Prathamesh S Pai