时间戳

首页 > 解决方案 > Mysql 慢速多连接查询 - 5.7 版

mysql-5.7 - Mysql 慢速多连接查询 - 5.7 版

问题描述

我有以下查询：

SELECT *
        FROM `ResearchEntity` AS `ResearchEntity`
          LEFT OUTER JOIN `UserEntity` AS `createdBy` ON `ResearchEntity`.`createdById` = `createdBy`.`id`
          LEFT OUTER JOIN `Item1Entity` AS `item1` ON `ResearchEntity`.`id` = `Item1`.`researchId`
          LEFT OUTER JOIN `Item2Entity` AS `item2` ON `ResearchEntity`.`id` = `Item2`.`researchId`
          LEFT OUTER JOIN `Item3Entity` AS `item3` ON `ResearchEntity`.`id` = `Item3`.`researchId`
          LEFT OUTER JOIN `Item4Entity` AS `item4` ON `ResearchEntity`.`id` = `Item4`.`researchId`
          LEFT OUTER JOIN `Item5Entity` AS `item5` ON `ResearchEntity`.`id` = `Item5`.`researchId`
          LEFT OUTER JOIN `Item6Entity` AS `item6` ON `ResearchEntity`.`id` = `Item6`.`researchId`
          LEFT OUTER JOIN `Item7Entity` AS `item7` ON `ResearchEntity`.`id` = `Item7`.`researchId`
          LEFT OUTER JOIN `Item8Entity` AS `item8` ON `ResearchEntity`.`id` = `Item8`.`researchId`
          LEFT OUTER JOIN `Item9Entity` AS `item9` ON `ResearchEntity`.`id` = `Item9`.`researchId`
          LEFT OUTER JOIN `Item10Entity` AS `item10` ON `ResearchEntity`.`id` = `Item10`.`researchId`
ORDER BY `ResearchEntity`.`id` DESC
LIMIT 20, 40;

UserEntity有 4 条记录 Item*Entity，每条有 15 条记录

当我删除第一个连接时，UserEntity连接查询运行得非常快，但它在 50 秒内运行。

查询是使用 ORM 在运行时动态构建的。

为什么UserEntity会造成这么多麻烦？

谢谢

标签： mysql-5.7

解决方案

尝试更改您的 select * 以从每个实体中选择特定字段，您的查询负载将减少并且运行时间应该更快。

就像是

SELECT ResearchEntity.field1 as ReField1, item1.name as Item1Name, ...

推荐阅读