MySQL日期范围查询优化：预订系统可用性查询提速求助

嘿，这个问题我太熟了——预订系统里的可用性查询本来就是性能重灾区，尤其是关联预订记录表之后速度暴跌的情况，我帮过好几个项目解决过类似问题。咱们一步步来拆解优化方向：

1. 优先排查并优化索引 #

这绝对是解决这类问题的第一要务，你说不加booking_properties表时仅0.17秒，加了就变慢，大概率是这个表的索引没跟上。

• 给booking_properties表创建复合索引：(property_id, start_date, end_date)。因为查询可用性时，肯定是先锁定某房源，再检查日期是否冲突，这个复合索引能让数据库快速定位到目标房源的所有预订记录，彻底避免全表扫描。
• 同步检查property_bands表的索引：如果你的查询是基于房源ID+日期范围匹配，那同样需要(property_id, start_date, end_date)的复合索引，和dates表关联时能快速筛选出符合条件的可用日期段。
• 确保dates表的date字段有主键或唯一索引，避免关联时的无意义全表扫。

2. 重构查询逻辑，换用更高效的过滤方式 #

很多时候，JOIN的写法会让数据库做大量的关联计算，试试改用NOT EXISTS来排除已预订的日期，性能可能会有惊喜：

SELECT p.property_id, d.date
FROM property_bands p
JOIN dates d ON d.date BETWEEN p.start_date AND p.end_date
WHERE NOT EXISTS (
    SELECT 1
    FROM booking_properties b
    WHERE b.property_id = p.property_id
      AND d.date BETWEEN b.start_date AND b.end_date
)

这种写法的优势在于，数据库一旦找到匹配的预订记录，就会终止当前行的子查询，不用把所有预订记录都关联完再过滤。

3. 严格限制查询的日期范围 #

别让dates表把2015到2030的所有日期都卷进来！如果用户查询的是近期的可用性（比如未来90天），一定要在查询里加上日期过滤条件：

AND d.date >= CURDATE() 
AND d.date <= DATE_ADD(CURDATE(), INTERVAL 90 DAY)

减少要处理的数据量，性能提升会非常明显。

4. 处理大表数据的冗余与拆分 #

如果booking_properties表的数据量特别大（比如百万级以上）：

• 考虑分表策略：比如按年份分表，或者按property_id的哈希值分表，这样每次查询只需要访问部分数据，而非全表。
• 检查是否有不必要的字段被SELECT出来，只保留业务需要的字段，避免大结果集的传输和处理开销。

5. 用EXPLAIN分析执行计划，定位瓶颈 #

一定要跑EXPLAIN看看你的查询执行计划：

• 如果看到type列是ALL，那就是全表扫描，说明索引没生效，得回去调整索引。
• 观察rows列的数值，看看数据库预估要扫描多少行，数值越大说明性能越差。
• 如果数据库的关联顺序不合理（比如先扫大表再过滤），可以用STRAIGHT_JOIN强制指定关联顺序，先扫小表再关联大表。

6. 缓存高频查询结果 #

对于热门房源的可用性查询，可以把结果缓存起来（比如用Redis），每天凌晨定时更新一次缓存。这样大部分请求不用直接访问数据库，性能能提升一个量级。

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