如何加快我的“好友列表”MySQL查询？

Question

我正试图从我的数据库中获得一个好友列表，我的查询正在工作，但是加载结果需要花费大量时间(有时大约是10秒)。

下面是：

SELECT F.status, U.username, U.email, UI.country, UI.birthday, P.thumb_url
FROM user U, relation F, user_info UI, photo P 
WHERE
CASE
WHEN F.leader = 'USER_ID'
THEN F.subscriber = U.id
WHEN F.subscriber= 'USER_ID'
THEN F.leader= U.id
END
AND
U.id = UI.user_id
AND
UI.main_photo = P.id
AND
F.status='1';

我的“关系”表如下：

CREATE TABLE IF NOT EXISTS `relation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leader` int(11) NOT NULL,
  `subscriber` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT,
  `status` int(11) NOT NULL DEFAULT '0',
  `seen` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

所以我的问题是:为了改进我的查询，你有什么好的建议吗？

谢谢!

Answer 1

谢谢你的回答。

因此，我找到了一个很好的解决方案，使用的是联接语法，而不是这种情况。我把它张贴在这里，因为它可以帮助你们中的一些人。

SELECT username, email, ui.gender, ui.country, ui.birthday, p.thumb_url
FROM relation
RIGHT JOIN user ON
(user.`id` = relation.`leader`
OR
user.`id` = relation.`subscriber`)
AND
user.`id` != '?'
AND
relation.`status` = '1'
JOIN user_info ui, photo p
WHERE
(relation.`leader` = '?'
OR
relation.`subscriber` = '?')
AND user.`id` = ui.`user_id`
AND ui.`main_photo` = p.`id`;

这个查询只需要大约100 was来加载结果，而对于我发布的第一个查询，大约需要5秒.

Answer 2

经验法则是，如果您在SQL中使用一个案例(或任何其他if /那么逻辑)，那么您可能做错了。SQL是一种声明性语言，这意味着您要问它问题。如果你告诉它如何回答这个问题，它可能会很糟糕(或缓慢)。关键是将约束的用户ID放在WHERE子句中，并将其排除在联接之外。这将从告诉数据库如何完成其工作改为询问数据库。数据库会处理剩下的。

使用JOIN语法可以更清楚地了解什么是联接，什么是约束。当您希望将多个表中的行拼接在一起时，可以使用SQL联接。数据库知道如何优化它们。

第二步是使用解释，告诉您MySQL是如何执行查询的，查询在哪里执行得很慢。这通常表示需要索引的位置或需要重写查询的哪一部分。

我将假设Relationship.leader和Relationship.subscriber都是User.id上的外键，描述了谁在跟踪该用户以及他们在跟踪谁，您希望向每个与给定USER_ID相关的用户显示。不需要这种情况，您只需检查这两种情况是否匹配(在这一种情况下请检查我)。

SELECT F.status, U.username, U.email, UI.country, UI.birthday, P.thumb_url
FROM user U
JOIN user_info UI
  ON U.id = UI.user_id
JOIN photo P
  ON UI.main_photo = P.id
JOIN relation F
  ON F.leader = U.id OR F.subscriber = U.id
WHERE F.status='1'
  AND U.id = ?

UPDATE：现在理解它应该是用户的朋友列表，更改U.id = ?以检查用户是否处于关系中。

SELECT F.status, U.username, U.email, UI.country, UI.birthday, P.thumb_url
FROM user U
JOIN user_info UI
  ON U.id = UI.user_id
JOIN photo P
  ON UI.main_photo = P.id
JOIN relation F
  ON F.leader = U.id OR F.subscriber = U.id
WHERE F.status='1'
  AND F.leader = ? OR F.subscriber = ?

我们需要一个关系列表，但是我们使用用户作为主表。有点尴尬。@fraxool的查询来自关系更有意义，但它可能需要对约束的位置进行一些清理。连接只是关于啮合的桌子。何去何从限制返回的总体内容。将这些原则应用到@fraxool的新查询中，我们得到.

SELECT username, email, ui.gender, ui.country, ui.birthday, p.thumb_url
FROM relation R
JOIN user U
  ON U.id = R.leader OR U.id = R.subscriber
JOIN user_info UI
  ON U.id = UI.user_id
JOIN photo P
  ON UI.main_photo = P.id
WHERE R.status = 1
  AND (R.leader = ? OR R.subscriber = ?)

U.id != ?的附加约束是不必要的，除非您可以成为自己的朋友。这是应该在relation.leader和relation.subscriber上的列约束中处理的。