JetBrains Exposed框架中Eager Loading的load函数未实现查询优化的问题咨询

我最近在使用JetBrains Exposed框架的DAO关联功能时遇到了一个困惑：我想通过load函数实现预加载（Eager Loading），一次性查询出处方及其关联的药品、咨询记录等数据，避免N+1查询问题，但实际日志显示还是触发了多次单独的SQL查询，想请教这是不是正常现象？load函数的作用是不是只是提前触发懒加载，而不会真的优化查询为单条或更少的SQL语句？

我的代码实现 #

1. 数据库表定义

object PrescriptionTable : ULongIdTable("prescription") {
    val consultation = reference("consultation", ConsultationTable.id)
    val instructions = text("instructions")
    val warnings = text("warnings")
    val file = reference("file", FileTable.id, onDelete = ReferenceOption.CASCADE).uniqueIndex()
}

object MedicationTable : ULongIdTable("medications") {
    val name = varchar("name", 255)
    val frequency = varchar("frequency", 255)
    val duration = varchar("duration", 255)
    val instructions = text("instructions")
    val prescription = reference("prescription", PrescriptionTable.id, onDelete = ReferenceOption.CASCADE)
}

2. DAO实体类

class PrescriptionDAO(id: EntityID<ULong>) : ULongEntity(id) {
    companion object : ULongEntityClass<PrescriptionDAO>(PrescriptionTable)
    
    var consultation by ConsultationDAO referencedOn PrescriptionTable.consultation
    var instructions by PrescriptionTable.instructions
    var warnings by PrescriptionTable.warnings
    var file by FileDAO referencedOn PrescriptionTable.file
    val medications by MedicationDAO referrersOn MedicationTable.prescription
}

class MedicationDAO(id: EntityID<ULong>) : ULongEntity(id) {
    companion object : ULongEntityClass<MedicationDAO>(MedicationTable)
    
    var name by MedicationTable.name
    var frequency by MedicationTable.frequency
    var duration by MedicationTable.duration
    var instructions by MedicationTable.instructions
    var prescription by PrescriptionDAO referencedOn MedicationTable.prescription
    var file by FileDAO referencedOn PrescriptionTable.file // 注：这里疑似笔误，应该关联MedicationTable的file字段？
}

3. 仓库层查询函数

override suspend fun getPrescriptionById(prescriptionId: ULong): Either<DomainError, Prescription> = either {
    suspendTransaction {
        PrescriptionDAO.findById(prescriptionId)?.load(
            PrescriptionDAO::medications,
            PrescriptionDAO::consultation,
            ConsultationDAO::patient,
            ConsultationDAO::doctor,
            PrescriptionDAO::file
        )?.let { daoToPrescriptionModel(it) }
        ?: raise(PrescriptionNotFound)
    }
}

实际SQL日志输出 #

2025-08-28 01:48:39.620 [DefaultDispatcher-worker-1] DEBUG Exposed - SELECT prescription.id, prescription.consultation, prescription.instructions, prescription.warnings, prescription.file FROM prescription WHERE prescription.id = 1
2025-08-28 01:48:43.629 [DefaultDispatcher-worker-1] DEBUG Exposed - SELECT consultation.id, consultation."date", consultation.doctor, consultation.patient, consultation."onCall", consultation.completed FROM consultation WHERE consultation.id = 2
2025-08-28 01:48:43.636 [DefaultDispatcher-worker-1] DEBUG Exposed - SELECT file.id, file."name", file.e_tag, file."key", file."size" FROM file WHERE file.id = 1
2025-08-28 01:48:43.644 [DefaultDispatcher-worker-1] DEBUG Exposed - SELECT medications.id, medications."name", medications.frequency, medications.duration, medications.instructions, medications.prescription FROM medications WHERE medications.prescription = 1
2025-08-28 01:48:43.683 [DefaultDispatcher-worker-1] DEBUG Exposed - SELECT "user".id, "user".created_at, "user"."password", "user".cpf, "user"."type" FROM "user" WHERE "user".id = 1
2025-08-28 01:48:43.686 [DefaultDispatcher-worker-1] DEBUG Exposed - SELECT "user".id, "user".created_at, "user"."password", "user".cpf, "user"."type" FROM "user" WHERE "user".id = 8
2025-08-28 01:48:44.785 [DefaultDispatcher-worker-1] DEBUG Exposed - SELECT on_call.id, on_call."name", on_call.cod, on_call.description, on_call.active, on_call.icon, on_call.price FROM on_call WHERE on_call.id = 1

我的疑问 #

从日志能看到，每个关联实体都触发了单独的SQL查询，和我预期的“一次查询拉取所有数据”不符。想确认：

1. 这是不是load函数的正常行为？它是不是只负责提前触发懒加载，而不会优化查询为JOIN语句？
2. 如果我想实现真正的预加载（用JOIN减少查询次数），在Exposed的DAO模式下应该怎么操作？

内容来源于stack exchange