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

Question

我有三个表:

1. 用户:user_id(pk)；用户名
2. FIELD:field_id(pk)；姓名
3. 元数据:metadata_id (pk)；字段_id(索引)；用户id(索引)；值

这样做的原因是应用程序允许为每个用户创建自定义字段。为了显示用户的所有信息，我正在构建一个动态查询(通过 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;
}