基于SQLAlchemy与PostgreSQL的跨表预约计数查询性能优化

1. 关联逻辑硬伤（380ms耗时的核心原因） #

原查询虽然通过SQLAlchemy自动识别外键，完成了Appointment.slot_id = AppointmentSlot.id的正确join，但额外加了完全错误的过滤条件AppointmentSlot.id == Appointment.id：

• 该条件本质是拿两张表的无关联主键做等值匹配，会过滤掉99%的正确预约数据，且因为匹配字段没有关联索引，PG只能逐行扫描做条件校验，完全无法利用外键索引加速
• 即使8条测试数据，逐行做等值匹配的解析开销都会把查询耗时拉高到数百毫秒级别，后续数据量增长后会直接出现查询超时

2. 模型配置冗余带来的额外开销 #

AppointmentSlot模型的关联关系配置和业务逻辑完全矛盾：

• 配置了uselist=False（一对一关联），但业务上一个时段（如08:00-09:00）可以对应多条预约记录，属于典型的一对多关系
• 配置了lazy='joined'，只要查询关联到AppointmentSlot，SQLAlchemy会强制把关联表的所有字段加载到内存，哪怕做统计查询只需要匹配ID，也会拉取无意义的字段，增加查询和网络传输开销

1. 先修正模型配置错误 #

class AppointmentSlot(ResourceMixin, db.Model):                                                   
    __tablename__ = 'appointment_slots'                                                            
    id = db.Column(db.Integer, primary_key=True)                                                                         
    # 修正关联关系：一对多，关闭强制join加载，改为动态查询支持聚合
    appointments = db.relationship('Appointment', uselist=True,                                  
                                   backref='appointment_slot', lazy='dynamic', passive_deletes=True)   
    start_time = db.Column(db.String(5), nullable=False, server_default='08:00')                                                                                             
    end_time = db.Column(db.String(5), nullable=False, server_default='17:00')

2. 优化统计查询写法，规避冗余字段加载 #

采用半连接写法替代传统两表join，只关联必要的ID字段，让PG优化器可以直接走索引扫描：

# 定义目标时段的子查询，仅取时段ID字段，不拉取冗余数据
target_slot_id_subq = db.session.query(AppointmentSlot.id)\
    .filter(AppointmentSlot.start_time == target_time).subquery()

# 统计预约数：直接通过预约表的索引过滤日期和slot_id，不需要全字段join
appointment_count = db.session.query(func.count(Appointment.id))\
    .filter(
        Appointment.appointment_date == target_date,
        Appointment.slot_id.in_(db.session.query(target_slot_id_subq.c.id))
    ).scalar()

优化后查询特性：

• 不需要加载AppointmentSlot的全字段，只做ID的存在性校验
• PG会自动将该查询转换为半连接执行，扫描开销比传统join低一个数量级

3. 配套数据库索引，支撑数十万级数据量 #

针对查询逻辑建匹配的联合索引，避免全表扫描：

• 预约表appointments建联合索引：(appointment_date, slot_id)，覆盖按日期过滤、匹配时段ID的查询路径
• 时段表appointment_slots建普通索引：(start_time)，如果需要按时段范围查询可扩展为(start_time, end_time)联合索引
• 存储的start_time是HH:MM格式字符串，只要传入的查询参数格式和存储值完全一致，就可以直接命中索引，不需要额外做类型转换

在修正关联逻辑、配置正确索引后，数十万级数据量下的单条统计查询耗时可稳定控制在10ms以内，完全可支撑高并发场景。

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