多表关联的复杂酒店查询场景下更优的分页实现方案有哪些？

现有方案问题分析 #

你当前考虑的两个方案都存在明确的缺陷：

• 分多次查ID再内存拼接的方案，除了你提到的IO开销大、分页不准的问题外，还有潜在的OOM风险：如果符合价格条件的房型数量较多，全量拉取中间ID到服务内存，数据量上来后完全扛不住。
• 四表关联SQL分页的方案，只要索引设计合理，性能其实没有你想象的差，但确实和后续可用性数据迁缓存的规划不兼容，扩展性很差，不适合作为长期方案。

解决方案推荐 #

你可以根据当前业务阶段和数据规模二选一，也可以按迭代节奏逐步升级：

短期过渡方案（数据量<100万条房间记录，快速上线） #

直接用单SQL关联查询，配合合理的索引设计，性能足够支撑中小规模业务，响应速度可以稳定在毫秒级：

SELECT DISTINCT h.id, h.* -- 取需要的酒店字段
FROM Hotel h
INNER JOIN Room r ON h.id = r.hotel_id
INNER JOIN RoomConfig rc ON r.config_id = rc.id
INNER JOIN Availability a ON r.id = a.room_id
WHERE 
  h.location = ? -- 优先走位置索引缩小数据集
  AND rc.price BETWEEN ? AND ? -- 价格过滤
  AND a.date BETWEEN '入住日期' AND '退房日期'
GROUP BY h.id, r.id
-- 统计日期数等于入住天数，说明该房间整个周期都可预订
HAVING COUNT(a.date) = DATEDIFF('退房日期', '入住日期')
LIMIT 偏移量, 每页条数

需要配套创建的索引：

• Hotel表：给location字段建普通索引
• Room表：给hotel_id、config_id建联合索引
• RoomConfig表：给price字段建普通索引
• Availability表：给room_id、date建联合覆盖索引，统计时无需回表

长期最优方案（支持高并发、兼容缓存扩展） #

分层做过滤，各层逻辑解耦，后续可用性数据迁到Redis不需要改动上层逻辑，还能彻底解决分页问题：

1. 第一层：过滤符合基础条件的酒店列表
   关联Hotel、Room、RoomConfig三张表，查询所有符合位置、价格条件的酒店ID，按酒店ID排序。这部分数据属于低频变更数据（酒店位置、房型价格不会频繁调整），可以直接缓存1~24小时，大部分请求不需要查库。
2. 第二层：校验房间可用性
   按顺序遍历第一层拿到的酒店ID列表，逐个查询酒店下符合价格的房间的可用性（不管是查SQL的Availability表，还是后续迁到Redis存房间可预订日期集合，这层逻辑都可以无缝切换），只要酒店下有至少1个房间整个入住周期都可用，就把该酒店加入结果集。
3. 第三层：实现精准分页
   收集到limit个符合条件的酒店就停止遍历，分页时记录上一页最后一个符合条件的酒店ID，下一页直接从该ID之后的列表开始遍历，完全避免深度分页问题。
   该方案的优势：

• IO次数可控，最多只需要2次交互（1次拉取基础酒店列表，1次批量查可用性），比多次查中间ID的开销小很多
• 扩展性强，后续可用性存储换Redis、加其他筛选条件都可以在对应层改造，不影响整体逻辑
• 分页完全精准，不会出现凑不够一页的问题

选择建议 #

如果当前业务刚起步、数据量小，优先选短期方案快速上线，后续用户量上来后再迭代到长期方案即可，不需要一开始就做过重的设计。

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