什么是最佳实践,什么提供最佳性能？
我目前有一个查询,其中包含许多LEFT JOIN,用于获取用户及其所有数据,如朋友,朋友请求等：
SELECT
`user`.`id` AS `user_id`,
`user`.`name` AS `user_name`,
`manager`.`id` AS `manager_id`,
`competition`.`id` AS `manager_competition_id`,
`competition`.`name` AS `manager_competition_name`,
`competition`.`week` AS `manager_competition_week`,
`country`.`id` AS `manager_competition_country_id`,
`country`.`name` AS `manager_competition_country_name`,
`club_template`.`id` AS `manager_club_template_id`,
`club_template`.`name` AS `manager_club_template_name`,
`club`.`id` AS `manager_club_id`,
`club`.`name` AS `manager_club_name`,
`club`.`ready` AS `manager_club_ready`,
`friend`.`friend_id` AS `friend_id`,
`friend_user`.`name` AS `friend_name`
FROM
`users` AS `user`
LEFT JOIN
`managers` AS `manager`
ON
`manager`.`user_id` = `user`.`id`
LEFT JOIN
`competitions` AS `competition`
ON
`competition`.`id` = `manager`.`competition_id`
LEFT JOIN
`countries` AS `country`
ON
`country`.`id` = `competition`.`country_id`
LEFT JOIN
`club_templates` AS `club_template`
ON
`club_template`.`id` = `manager`.`club_template_id`
LEFT JOIN
`clubs` AS `club`
ON
`club`.`id` = `manager`.`club_id`
LEFT JOIN
`friends` AS `friend`
ON
`friend`.`user_id` = `user`.`id`
LEFT JOIN
`users` AS `friend_user`
ON
`friend_user`.`id` = `friend`.`friend_id`
WHERE
`user`.`id` = 1
如您所见,这是一个非常大的查询.我的理由是,最好只有一个查询可以在一个API请求中完成,比如这个……
/api/users/1
…相对于一些查询,每个查询都在他们自己的API请求中,就像这样……
/api/users/1
/api/users/1/friends
/api/users/1/friend_requests
/api/users/1/managers
但是现在我很担心,因为它变得如此庞大的查询,它实际上会损害性能,而不是在单独的API请求中将其拆分.
什么会更好地扩展？
更新
我已将查询更改为完整查询.这不是最终的查询;我计划添加更多联接(或不是,取决于答案).
每个表都有一个id的PRIMARY KEY.所有关联列(competition_id,club_id等)都有常规INDEX.数据库引擎是InnoDB.
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
