Sequelize查询优化：关联表条件匹配或无关联时返回主表

需求说明 #

返回DoctorBio表记录，满足以下任一条件：

• 关联的User表（别名userRegistered）不存在
• 关联的User表存在，且email_verified为null/0 或 doctor_verified为null/0
  同时保留原有的姓名、专科的模糊查询逻辑，以及关联UserMap、DoctorLocation、DoctorDetail的需求。

原实现问题 #

原代码逻辑正确，但查询速度极慢，核心问题在于：

1. 主查询中直接过滤关联表字段，导致数据库无法高效利用索引，触发全表扫描
2. 查询返回了大量不必要的字段，增加数据传输和处理开销
3. 额外触发了不必要的子查询（如User的address、specialties关联查询）

原代码 #

const users = await this.doctorsRepository.findAll({
  where: {
    [Op.or]: [
      {
        first_name: {
          [Op.or]: [
            { [Op.like]: `%${primary}%` },
            { [Op.like]: `%${nameSplit[0]}%` },
          ],
        },
      },
      {
        last_name: {
          [Op.or]: [
            { [Op.like]: `%${primary}%` },
            { [Op.like]: `%${nameSplit[1]}%` },
          ],
        },
      },
      { specialty: { [Op.like]: `%${primary}%` } },
    ],
    [Op.or]: [
      {
        '$userRegistered.email_verified$': {
          [Op.or]: [null, 0],
        },
      },
      {
        '$userRegistered.doctor_verified$': {
          [Op.or]: [null, 0],
        },
      },
    ],
  },
  include: [
    {
      model: User,
      as: 'userRegistered',
    },
    {
      model: UserMap,
      where: userMapWhere,
      include: [
        {
          model: DoctorLocation,
          required: location ? true : false,
          where: locWhere,
          attributes: [
            'city',
            'address',
            'name',
            'postal_code',
            'state',
            'full_address',
            'latitude',
            'longitude',
            'primary',
          ],
        },
        {
          model: DoctorDetail,
          attributes: ['suffix', 'image_url', 'health_center', 'specialty'],
        },
      ],
    },
  ],
  limit: limit,
  offset: offset,
  subQuery: false,
});

原生成SQL（关键片段） #

SELECT `DoctorBio`.`index`, `DoctorBio`.`first_name`, ... -- 大量字段
FROM `scraped_user_final` AS `DoctorBio` 
LEFT OUTER JOIN `users` AS `userRegistered` ON `DoctorBio`.`userId` = `userRegistered`.`health_center_id` AND (`userRegistered`.`deletedAt` IS NULL) 
INNER JOIN `user_map` AS `userMap` ON `DoctorBio`.`userId` = `userMap`.`userId` 
LEFT OUTER JOIN `agg_locations_enriched` AS `userMap->doctorLocation` ON `userMap`.`health_center_id` = `userMap->doctorLocation`.`health_center_id` 
LEFT OUTER JOIN `agg_detailed_bio` AS `userMap->doctorDetail` ON `userMap`.`health_center_id` = `userMap->doctorDetail`.`health_center_id` 
WHERE ((`DoctorBio`.`first_name` LIKE '%Addiction Medicine%' OR ...) LIMIT 1, 20;
-- 额外触发的不必要子查询
SELECT `id`, `role`, ... FROM `users` AS `User` WHERE (`User`.`deletedAt` IS NULL AND `User`.`id` = 9);

表结构（关键片段） #

DoctorBio表

export class DoctorBio extends Model {
  @PrimaryKey
  @Column
  index: number;

  @Column
  first_name: string;

  @Column
  last_name: string;

  @Column
  userId: string;

  @Column
  specialty: string;

  @HasOne(() => User, {
    foreignKey: 'health_center_id',
    as: 'userRegistered',
    sourceKey: 'userId',
  })
  userRegistered: User;
}

User表

export class User extends Model {
  @PrimaryKey
  @AutoIncrement
  @Column
  id: number;

  @ForeignKey(() => DoctorBio)
  @Column
  health_center_id: string;

  @Column
  email_verified: Status;

  @Column
  doctor_verified: Status;
}

优化方案 #

1. 调整关联筛选条件的位置 #

将对userRegistered的筛选条件从主查询where移到include的where中，配合required: false，确保关联条件被整合到LEFT JOIN的ON子句中，既保留无关联的DoctorBio记录，又避免主查询过滤带来的性能损耗。

2. 精简查询字段 #

只返回业务需要的字段，减少数据传输和数据库处理开销，同时避免返回敏感字段（如User的password）。

3. 优化索引（数据库层面） #

确保以下字段存在索引：

• DoctorBio.userId（关联User和UserMap的核心字段）
• User.health_center_id（关联DoctorBio的外键）
• User.email_verified、User.doctor_verified（筛选条件字段）
• DoctorBio.first_name、DoctorBio.last_name、DoctorBio.specialty（模糊查询字段，若支持可创建全文索引替代LIKE）

4. 避免不必要的关联 #

检查是否需要User表的子关联（如address、specialties），原SQL中额外触发了这些查询，若业务不需要则移除。

优化后的代码 #

const users = await this.doctorsRepository.findAll({
  // 只返回DoctorBio需要的字段
  attributes: ['index', 'first_name', 'last_name', 'health_center', 'userId', 'specialty', 'is_active', 'load_date'],
  where: {
    [Op.or]: [
      {
        first_name: {
          [Op.or]: [
            { [Op.like]: `%${primary}%` },
            { [Op.like]: `%${nameSplit[0]}%` },
          ],
        },
      },
      {
        last_name: {
          [Op.or]: [
            { [Op.like]: `%${primary}%` },
            { [Op.like]: `%${nameSplit[1]}%` },
          ],
        },
      },
      { specialty: { [Op.like]: `%${primary}%` } },
    ],
  },
  include: [
    {
      model: User,
      as: 'userRegistered',
      required: false, // 保留无关联的DoctorBio记录
      where: {
        [Op.or]: [
          { email_verified: { [Op.or]: [null, 0] } },
          { doctor_verified: { [Op.or]: [null, 0] } },
        ],
      },
      // 只返回User需要的字段
      attributes: ['id', 'email', 'email_verified', 'doctor_verified', 'firstName', 'lastName'],
    },
    {
      model: UserMap,
      where: userMapWhere,
      // 精简UserMap字段
      attributes: ['index', 'userId', 'health_center_id'],
      include: [
        {
          model: DoctorLocation,
          required: location ? true : false,
          where: locWhere,
          attributes: [
            'city',
            'address',
            'name',
            'postal_code',
            'state',
            'full_address',
            'latitude',
            'longitude',
            'primary',
          ],
        },
        {
          model: DoctorDetail,
          attributes: ['suffix', 'image_url', 'health_center', 'specialty'],
        },
      ],
    },
  ],
  limit: limit,
  offset: offset,
  subQuery: false,
});

额外优化建议 #

• 若模糊查询性能仍不达标，考虑改用全文索引（如MySQL FULLTEXT）或专门的搜索引擎（如Elasticsearch）替代LIKE %xxx%
• 对常用筛选字段创建复合索引，例如DoctorBio(first_name, last_name, specialty)
• 用EXPLAIN命令分析查询执行计划，定位剩余性能瓶颈

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