MySQL 连接优化

Question

你能帮我优化这个查询吗？我使用此查询来获取 friend 列表以及他们的详细信息和状态。在 Athlon X2 6000 上处理这个大约需要 0.08 秒

我也不能使用物化 View ，因为它经常变化。

SELECT p.userid, p.firstname, p.lastname, p.gender, p.dob, x.relationship,
 IF(p.picture !=1, 
   IF(p.gender != 'm','/sc/f-t.jpg','/sc/m-t.jpg'),
   concat('/sc/pthumb/', p.userid, '.jpg' )) AS picture
  FROM `social` AS p
  LEFT JOIN `friendlist` AS f1 ON (f1.`userid` = p.`userid` AND f1.`friendid` = 1 AND `f1`.`status` = 1)
  LEFT JOIN `friendlist` AS f2 ON (f2.`friendid` = p.`userid` AND f2.`userid` = 1 AND `f2`.`status` = 1)
  LEFT JOIN `x_relationship` AS x ON (x.`id` = p.`relationship`)
  LEFT JOIN `auth` as a ON (a.`userid` = p.`userid`) 
  WHERE 1 AND (f1.`userid` IS NOT NULL OR f2.`userid` IS NOT NULL AND ((a.`banned` != 1 AND a.`deleted` != 1)))
  ORDER BY RAND() LIMIT 0,10

Answer 1

这是您的原始查询，已格式化。下面是一些想法。

SELECT 
  p.userid, 
  p.firstname, 
  p.lastname, 
  p.gender, 
  p.dob, 
  x.relationship,
  IF(p.picture !=1, IF(p.gender != 'm', '/sc/f-t.jpg', '/sc/m-t.jpg'), concat('/sc/pthumb/', p.userid, '.jpg' )) AS picture
FROM 
  `social` AS p
  LEFT JOIN `friendlist`     AS f1 ON (f1.`friendid` = 1          AND f1.`userid` = p.`userid` AND `f1`.`status` = 1)
  LEFT JOIN `friendlist`     AS f2 ON (f2.`friendid` = p.`userid` AND f2.`userid` = 1          AND `f2`.`status` = 1)
  LEFT JOIN `x_relationship` AS  x ON (x.`id` = p.`relationship`)
  LEFT JOIN `auth`           AS  a ON (a.`userid` = p.`userid`) 
WHERE 
  1 
  AND (
    f1.`userid` IS NOT NULL 
    OR f2.`userid` IS NOT NULL 
    AND (a.`banned` != 1 AND a.`deleted` != 1)
  )
ORDER BY 
  RAND() 
LIMIT 
  0,10

想想你的一些左连接是否可以是内部连接。如果是这样，请更改它们。

您的 WHERE 子句一团糟。您正在混合使用 AND 和 OR，而没有使用括号正确地确定优先级。尝试:

WHERE 
  a.`banned` != 1 
  AND a.`deleted` != 1
  AND (
    f1.`userid` IS NOT NULL 
    OR f2.`userid` IS NOT NULL
  )

你也可以试试:

  INNER JOIN `auth` AS  a ON (
    a.`userid` = p.`userid` 
    AND a.`banned` != 1 
    AND a.`deleted` != 1
  ) 
WHERE 
  f1.`userid` IS NOT NULL 
  OR f2.`userid` IS NOT NULL

您加入好友列表 似乎只是为了检查记录是否存在。您可能也想尝试一下:

FROM 
  `social` AS p
  INNER JOIN `auth`           AS  a ON (
    a.`userid` = p.`userid`
    AND a.`banned` != 1 
    AND a.`deleted` != 1
  ) 
  LEFT  JOIN `x_relationship` AS  x ON (
    x.`id` = p.`relationship`
  )
WHERE 
  EXISTS (
    SELECT 1 FROM `friendlist` WHERE `friendid` = 1 AND `userid` = p.`userid` AND `status` = 1
  ) 
  OR EXISTS (
    SELECT 1 FROM `friendlist` WHERE `friendid` = p.`userid` AND `userid` = 1 AND `status` = 1
  )

ORDER BY RAND() 可能不是世界上最快的东西，但如果这是您需要的...尝试按索引列排序以查看有多大影响 ORDER BY RAND() 有。

索引:

• 您应该在 friendlist(friendid, userid, status) 上创建一个复合索引。
• 确保 relationship(id) 上有索引
• 确保 auth(userid) 上有索引