有没有比执行多个 JOINS (MySQL) 更有效的方法？

Question

我有三个表：

1. USER: user_id (pk);用户名
2. FIELD: field_id (pk);名称
3. 元数据：metadata_id (pk);字段_id（索引）；用户id（索引）； value

这样做的原因是应用程序允许为每个用户创建自定义字段。为了显示用户的所有信息，我正在构建一个动态查询（通过 PHP），最终看起来像这样：

SELECT
  u.username, m1.value AS m1value, m2.value AS m2value
FROM user AS u
LEFT JOIN metadata AS m1
  ON (u.user_id=m1.user_id AND m1.field_id=1)
LEFT JOIN metadata AS m2
  ON (u.user_id=m2.user_id AND m2.field_id=2)

这个示例只有 2 个用户元数据字段，但是您知道如果有十几个字段，这会是什么样子。

还有另一种更好的方法来编写这个查询吗？随着用户和元数据字段的增长，我担心此查询的性能。

编辑： 我希望返回的结果中每行有一个用户。

Answer 1

为什么不一次把它们全部抓住呢？

SELECT u.user_id,u.username, m.field_id,m.value FROM user u
LEFT JOIN metadata m
ON u.user_id=m.user_id 
WHERE 1 ORDER BY user_id

或者对于特定用户：

SELECT u.user_id,u.username, m.field_id,m.value FROM user u
LEFT JOIN metadata m
ON u.user_id=m.user_id 
WHERE user_id = ? ORDER BY user_id

除了被索引之外，还要确保两个表之间的 user_id 的类型和长度完全相同，否则您最终仍然会进行表扫描。

你的服务器代码是什么语言？

为每个用户获取一行（有点）的一种简单方法是在循环中返回行，检查每个 user_id 是否与最后一个相同。如果没有，则新行。

while ( $row = $sth->fetch_object() ) {
  $previous_user_id = '';
  if ( $row->user_id != $previous_user_id ) {
    # new row
  } else {
    # not new row
  }
  $previous_user_id = $row->user_id;
}

Answer 2

您将必须有两个查询。一个用于用户检索（SELECT * FROM users），另一个用于提取用户的自定义字段（SELECT * FROM fields WHERE users_id = user_id）。

不过，在某些情况下，您可以通过一个查询来完成它......告诉我们更多关于您想要的结果到底是什么。

Answer 3

您通常会每行返回一个元数据元素，如下所示：

SELECT u.username, mi.field_id, m1.value
FROM user AS u
LEFT JOIN metadata AS m1 ON u.user_id = m1.user_id

这对于数千个用户来说应该表现良好。

Answer 4

您可能会尝试类似...

SELECT
    u.username,
   (SELECT TOP 1 m.value
    FROM metadata m
    WHERE u.user_id=m.user_id AND m.field_id=1),
   (SELECT TOP 1 m.value
    FROM metadata m
    WHERE u.user_id=m.user_id AND m.field_id=2)
FROM user AS u

...但性能可能与您所拥有的类似（并且可能更差）。如果遇到性能问题，请检查以确保 user_id 和 field_id 均已编入索引。

Answer 5

（正如评论者指出的，这在 MySQL 中无效，所以，抱歉。但如果您感兴趣的话：）

按照上面的建议对元数据表进行一次 JOIN，然后使用 PIVOT 将每个用户的许多行更改为一行有多列，每个字段一个。我认为这在 SQL Server 2005 及更高版本中有效。