node.js - 序列化查询花费太长时间

Question

我在 Sequelize 中有以下查询

Providers.findAll({
    attributes: [
        "provider_id"
    ],
    include: [{
            model: ProvidersInsurance,
            attributes: ["insurance_id"],
            include: [{
                model: Insurance,
                attributes: ["insurance_id", "insurance_name"]
            }, ]
        },
        {
            model: ProvidersClients,
            attributes: ["client_id"],
            include: [{
                model: Clients,
                attributes: ["client_id", "client_name"]
            }, ]
        },
        {
            model: ProvidersCounties,
            attributes: ["county_id"],
            include: [{
                model: Counties,
                attributes: ["county_id", "county_name"]
            }, ]
        },
        {
            model: ProvidersSpecialities,
            attributes: ["speciality_id"],
            include: [{
                model: Specialities,
                attributes: ["speciality_id", "speciality_name"]
            }, ]
        },
        {
            model: ProvidersCategories,
            attributes: ["category_id"],
            include: [{
                model: Categories,
                attributes: ["category_id", "category_name"]
            }, ]
        }, {
            model: Telehealth,
            attributes: ["telehealth_name"]
        }


    ],
    where: {
        updatedAt: {
            [Op.between]: [req.query.fromdate, toDate]
        }
    }

}).then(providerData => {
    console.log(providerData)

}).catch(err => {
    console.log(err);
    res.status(404).send({
        reason: err.message
    })
});

执行时间超过 4 分钟。上面的原始查询是

SELECT `providers`.`provider_id`,
       `providers`.`providerFirstName`,
       `providers`.`providerLastName`,
       `providers`.`email`,
       `providers`.`phoneNumber`,
       `providers`.`address`,
       `providers`.`agencyName`,
       `providers`.`practiceCounty`,
       `providers`.`city`,
       `providers`.`state`,
       `providers`.`zip`,
       `providers`.`mondayHours`,
       `providers`.`tuesdayHours`,
       `providers`.`wednesdayHours`,
       `providers`.`thursdayHours`,
       `providers`.`fridayHours`,
       `providers`.`saturdayHours`,
       `providers`.`sundayHours`,
       `providers`.`additionalInfo`,
       `providers`.`website`,
       `providers`.`newClientsAcceptance`,
       `providers`.`updatedBy`,
       `providers`.`otherCategories`,
       `providers`.`otherInsurances`,
       `providers`.`otherTelehealthServices`,
       `providers`.`createdAt`,
       `providers`.`updatedAt`,
       `ProviderInsurances`.`provider_insurance_id` AS `ProviderInsurances.provider_insurance_id`,
       `ProviderInsurances`.`insurance_id` AS `ProviderInsurances.insurance_id`,
       `ProviderInsurances->Insurance`.`insurance_id` AS `ProviderInsurances.Insurance.insurance_id`,
       `ProviderInsurances->Insurance`.`insurance_name` AS `ProviderInsurances.Insurance.insurance_name`,
       `ProviderClients`.`provider_client_id` AS `ProviderClients.provider_client_id`,
       `ProviderClients`.`client_id` AS `ProviderClients.client_id`,
       `ProviderClients->Client`.`client_id` AS `ProviderClients.Client.client_id`,
       `ProviderClients->Client`.`client_name` AS `ProviderClients.Client.client_name`,
       `ProviderCounties`.`provider_county_id` AS `ProviderCounties.provider_county_id`,
       `ProviderCounties`.`county_id` AS `ProviderCounties.county_id`,
       `ProviderCounties->County`.`county_id` AS `ProviderCounties.County.county_id`,
       `ProviderCounties->County`.`county_name` AS `ProviderCounties.County.county_name`,
       `ProviderSpecialities`.`provider_speciality_id` AS `ProviderSpecialities.provider_speciality_id`,
       `ProviderSpecialities`.`speciality_id` AS `ProviderSpecialities.speciality_id`,
       `ProviderSpecialities->Speciality`.`speciality_id` AS `ProviderSpecialities.Speciality.speciality_id`,
       `ProviderSpecialities->Speciality`.`speciality_name` AS `ProviderSpecialities.Speciality.speciality_name`,
       `ProviderCategories`.`provider_category_id` AS `ProviderCategories.provider_category_id`,
       `ProviderCategories`.`category_id` AS `ProviderCategories.category_id`,
       `ProviderCategories->Category`.`category_id` AS `ProviderCategories.Category.category_id`,
       `ProviderCategories->Category`.`category_name` AS `ProviderCategories.Category.category_name`,
       `Telehealths`.`telehealth_id` AS `Telehealths.telehealth_id`,
       `Telehealths`.`telehealth_name` AS `Telehealths.telehealth_name`
FROM `providers` AS `providers`
LEFT OUTER JOIN `provider_insurances` AS `ProviderInsurances` ON `providers`.`provider_id` = `ProviderInsurances`.`provider_id`
LEFT OUTER JOIN `insurance` AS `ProviderInsurances->Insurance` ON `ProviderInsurances`.`insurance_id` = `ProviderInsurances->Insurance`.`insurance_id`
LEFT OUTER JOIN `provider_clients` AS `ProviderClients` ON `providers`.`provider_id` = `ProviderClients`.`provider_id`
LEFT OUTER JOIN `clients` AS `ProviderClients->Client` ON `ProviderClients`.`client_id` = `ProviderClients->Client`.`client_id`
LEFT OUTER JOIN `provider_counties` AS `ProviderCounties` ON `providers`.`provider_id` = `ProviderCounties`.`provider_id`
LEFT OUTER JOIN `counties` AS `ProviderCounties->County` ON `ProviderCounties`.`county_id` = `ProviderCounties->County`.`county_id`
LEFT OUTER JOIN `provider_specialities` AS `ProviderSpecialities` ON `providers`.`provider_id` = `ProviderSpecialities`.`provider_id`
LEFT OUTER JOIN `specialities` AS `ProviderSpecialities->Speciality` ON `ProviderSpecialities`.`speciality_id` = `ProviderSpecialities->Speciality`.`speciality_id`
LEFT OUTER JOIN `provider_categories` AS `ProviderCategories` ON `providers`.`provider_id` = `ProviderCategories`.`provider_id`
LEFT OUTER JOIN `categories` AS `ProviderCategories->Category` ON `ProviderCategories`.`category_id` = `ProviderCategories->Category`.`category_id`
LEFT OUTER JOIN `telehealth` AS `Telehealths` ON `providers`.`provider_id` = `Telehealths`.`provider_id`
WHERE `providers`.`updatedAt` BETWEEN '2019-08-06 18:30:00' AND '2019-08-08 00:00:00';

当我在 mysql 服务器中执行上述查询时，它会在不到 1 秒的时间内给出结果。然而，当我通过 Node 执行并 Sequelize 时，它花费了太多时间，即大约 4 分钟

我想知道为什么sequelize查询花费的时间太长

Answer 1

如果您使用 sequlize 最新版本，您可以使用以下选项，这肯定会减少很多时间，因为 raw:true 将停止 sequlize 从结果构建模型实例:

{
    raw : true, //  sequelize will not try to format the results of the query, or build an instance of a model from the result
    nest : true // this will format your data
}

<小时/>

Providers.findAll({
    attributes: [
        ...
    ],
    include: [
        ....
    ],
    where: {
        ...
    },
    raw : true, // <---- HERE
    nest : true // <---- HERE
})
.then(providerData => {
    console.log(providerData)

}).catch(err => {
    console.log(err);
    res.status(404).send({
        reason: err.message
    })
});