MySQL 在复杂查询中连接小表和大表 - 它们有多消耗？

Question

假设数据库很大。我对搜索结果页面有一个非常复杂的查询。在下面的查询中，您可以看到我从 user_profile 表中检索了一些属性值 id，例如教育就是一个属性。当我有属性教育的值 id 时，我从数组(php 代码)中检索该 id 的标签名称，其中 id 是数组键。

  public static $education        = array(0 => 'No answer', 
                                          1 => 'High school',
                                          2 => 'Some college',
                                          3 => 'In college',
                                          4 => 'College graduate',
                                          5 => 'Grad / professional school',                                    
                                          6 => 'Post grad');     

大约 10 个其他属性也类似。否则我的查询会更加复杂，我需要创建表 attribute_id_label 并为每个属性创建另一个联接以检索每个属性的值 id 的标签名称。这意味着额外的 10 个连接可能会减慢查询速度。但这仍然是正确的方法。

所以我的问题是:如果表 attribute_id_label 只有大约 500 条记录。由于该表非常小，因此对该表进行 10 次连接是否会产生很大的影响？即使表 user_profile 非常大并且查询已经相当复杂(如您所见)？

这是我的查询:

    SELECT 
    group_concat(DISTINCT looking.looking_for SEPARATOR ',') as lookingFor, 
    group_concat(DISTINCT photo.photo ORDER BY photo.photo_id DESC SEPARATOR ',') as photos, 
    profile.user_id as userId, 
    url as profileUrl, 
    nickname, 
    avatar.photo, 
    city, 
    ethnicity, 
    education, 
    occupation, 
    income, 
    //and 10 more fields like education, occupation, ethnicity...
    FROM user_profile profile 
    LEFT JOIN user_profile_photo photo ON photo.user_id=profile.user_id 
    LEFT JOIN user_profile_photo avatar ON avatar.photo_id=profile.photo_id 
    INNER JOIN user_profile_looking_for looking ON looking.user_id=profile.user_id 
    LEFT JOIN user_profile_txt txt ON txt.user_id = profile.user_id 
    INNER JOIN place a ON a.place_id=profile.place_id 
    INNER JOIN (SELECT lat, lon FROM place WHERE place_id = :place_id) b ON (3959 * acos( cos( radians(b.lat) ) * cos( radians( a.lat ) ) * cos( radians( a.lon ) - radians(b.lon) ) + sin( radians(b.lat) ) * sin( radians( a.lat ) ) ) ) < :within 
    GROUP BY profile.user_id LIMIT 0,12 

大多数属性不会由用户填充，既然您建议不可为空，那么对于那些未填充的属性最好使用什么？我可以为每个属性使用额外字段没有答案。每个属性都会有额外的值(value) 没有答案。让我们以教育和愿望属性为例。属性 education 有 id 1，want 是 2。

eav_attribute_option 
option_id | attr_id | label 
1 | 1 | No answer 
2 | 1 | High school 
3 | 1 | ...  
4 | 2 | No answer 
5 | 2 | Opportunities 
6 | 2 | ... 

但现在问题又重复了 每个属性都没有答案值。但这是避免 NULL 值的方法。我不确定这是否正确。

Answer 1

我做过很多此类代码表工作。它通常对性能的帮助大于损害。 @alxklx 指出了事实:您必须确保您的代码表(例如教育)格式良好。也就是说，

• education_id 列必须是 codelist 表中的唯一主键。
• education_id 列应该是简单的原始数据类型。也就是说，将其设置为 int，而不是 decimal 或 varchar。
• 当 education_id 显示在您的数据表中时，它必须与您在 codelist 表中使用的数据类型相同，并且必须不可为 NULL。换句话说，不要在数据表中使用 NULL 来指示丢失的数据。

如果你做了这些事情，你的 JOIN 就会看起来很简单

  FROM people p
  JOIN education e ON p.education_id = e.education_id

RDBMS 的优化器知道它们是简单的 1:1 连接。

总而言之，任何复杂的查询在将其放入实时系统之前都需要检查其功能和性能。

如果您的人员中缺少数据，请使用 0 或 1 的 education_id(或其他一些 attribute_id)。在每个代码列表表中放置一行，id 为零或一，值为“未知”或“用户没有告诉我们”或任何有意义的值。 (您可以根据应用的便利性选择零或一。我更喜欢零，但这只是个人喜好。)