主页 / dba / 问题 / 137917
Accepted
Alexis
Asked: 2016-05-09 10:10:53 +0800 CST

选择与左连接内的子查询

  • 772

通常我必须在获取其他不相关行的查询中返回某些行的计数。

例如一个表用户一个表评论和一个表图片

User:
id
nickname

Review:
id
to_user_id
from_user_id
rating

Picture:
id:
user_id
url

假设我想在一个查询中检索“给定”用户 ID 的所有图片 url 的昵称以及查看该用户的人数。

我在执行此查询时认为的第一个也是简单的方法是：

SELECT
  u.nickname
  (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount,
  p.url
FROM user
LEFT JOIN picture ON p.user_id = u.id
WHERE 
  u.id = 1

这样做的另一种方法是不使用该子选择，并在正确的 user_id 上加入评论表

SELECT 
 u.nickname,
 r.reviewCount,
 p.url
FROM user u 
LEFT JOIN (
    SELECT to_user_id, count(*) reviewCount FROM review GROUP BY to_user_id
 ) r ON r.to_user_id = u.id
LEFT JOIN picture ON p.user_id = u.id 
WHERE u.id = 1;

我不是数据库查询性能和调优方面的专家。如果一个解决方案比另一个更好，有人可以解释我吗？（或者如果有其他更好的解决方案）？

编辑： 抱歉忘了提。我正在使用最新的 MySQL

mysql performance

1 个回答

  1. Best Answer

    您没有指定正在使用的 RDBMS。我在这里写的大部分内容应该是相当独立的，但我主要有 MySQL 方面的经验，所以也许不同的系统允许一些其他优化。

    这(SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount是一个依赖子查询- 它将针对结果中的每一行执行。即使一次执行速度很快，也可能有数千次执行会使其变慢。

    中的一个JOIN是派生表- 它只会执行一次并具体化为一个临时表，然后将其连接到您的其他表。如果查询速度很快（可以使用 index on (to_user_id)），那很好。但在这种情况下，即使对于没有真正显示在结果中的用户，也会计算计数。但是..您可以将条件推入其中（to_user_id = 1而不是 GROUP BY）。

    但是为了让事情变得不那么简单，在较新的版本中存在一些优化。通过在 MariaDB 10（和 IIRC MySQL 5.7，但我没有验证）中使用子查询缓存，可以使依赖子查询更快。这意味着在您的情况下，结果中的所有行都有u.id = 1->to_user_id = 1并且子查询实际上只会执行一次，然后将使用缓存的结果。如果可用，则两个版本之间的差异将很小。

    就个人而言，我大多数时候更喜欢您的第二个版本，但在某些情况下，第一个版本会更快 - 我曾经有一个查询，其中不能简单地以正确的方式限制 JOINED 子查询中的行，而是切换到从属子查询实际上只读取了几个唯一的组合。

    • 6

相关问题