约会门户网站的Mysql表格和索引设计

Question

我正在创建一个约会门户网站，我们将询问用户大约40-50个问题，例如宗教、种姓、出生日期、食品偏好、吸烟/非吸烟等。

我正在询问用户偏好方面的类似问题，例如年龄范围、宗教偏好、吸烟偏好。

我有大约30-40个这样的偏好。

现在我想根据设置的偏好向用户展示匹配项。我想知道如何设计MySQL表和索引。

我应该创建一个名为“user_preferences”的大表，并拥有所有偏好的索引吗？它应该是多列索引还是合并索引？

我应该将问题集保留在不同的表中，并在获取数据时进行连接吗？

- shashuec

---

这里有关于EAV的讨论 - 讨论从第16页开始。 - Benny Hill

Answer 1

我认为这可能是 EAV 的一个案例：

你应该能够按照匹配程度降序（从最匹配到最不匹配）获取匹配的用户对，类似于这样：

SELECT *
FROM (
    SELECT U1.USER_ID, U2.USER_ID, COUNT(*) MATCH_COUNT
    FROM USER U1
        JOIN USER_PREFERENCE P1
            ON (U1.USER_ID = P1.USER_ID)
        JOIN USER_PREFERENCE P2
            ON (P1.NAME = P2.NAME AND P1.VALUE = P2.VALUE)
        JOIN USER U2
            ON (P2.USER_ID = U2.USER_ID)
    WHERE U1.USER_ID < U2.USER_ID -- To avoid matching the user with herself and duplicated pairs with flipped user IDs.
    GROUP BY U1.USER_ID, U2.USER_ID
) Q
ORDER BY MATCH_COUNT DESC

这只是根据其确切值匹配偏好设置。您可能需要为范围或枚举值创建附加的“偏好”表，并相应地替换P1.VALUE = P2.VALUE。如果匹配的数据在USER表中（例如用户年龄是否落入其他用户的首选年龄范围），则仍然可能需要进行特殊处理。

请注意，索引{NAME, VALUE}旨在帮助P1.NAME = P2.NAME AND P1.VALUE = P2.VALUE。InnoDB表是聚集的，其中一个后果是二级索引包含PK字段的副本-在这种情况下导致索引I1完全覆盖表格。MySQL是否实际使用它是另一回事-始终查看查询计划并在代表性数据上进行测量...

- Branko Dimitrijevic

Answer 2

我看到类似这样的内容：

我看到类似这样的内容：

questions是问题列表，需要回答。 question_type是一个枚举值，表示期望的答案类型（例如从question_choices中查找、日期、数字、文本等）- 无论您期望输入哪些数据类型。这个以及表格中的其他列可以驱动您的输入表单。 question_answers包含预定义的问题答案列表（例如预定义的宗教、发色或眼睛颜色列表等）。这可以用于在您的输入表单上构建下拉值列表。 users相当容易理解。

user_characteristics 包含了我对问卷的回答列表。 weight 列指示了寻找我的人拥有相同答案的重要程度。如果答案来自于 question_choices 表中构建的选择列表，则 question_choices_id 将被填充。否则，question_choices_id 将为 NULL。对于 value 列，情况恰好相反。如果答案来自于 question_choices 表中构建的选择列表，则 value 将为 NULL。否则，value 将包含用户手工回答的问题。

user_preferences 包含了我寻找的对象对问卷的回答。 weight 列指示了对于我来说，拥有相同答案的人的重要程度。question_choices_id 和 value 列与 user_characteristics 表中的行为相同。

查找我的匹配项的 SQL 可能如下所示：

SELECT uc.id
      ,SUM(up.weight)                   AS my_weighted_score_of_them
      ,SUM(uc.weight)                   AS their_weighted_score_of_me
      ,SUM(up.weight) + SUM(uc.weight)  AS combined_weighted_score
  FROM user_preferences        up
  JOIN user_characteristics    uc
    ON uc.questions_id           = up.questions_id
   AND uc.question_choices_id    = up.question_choices_id
   AND uc.value                  = up.value
   AND uc.users_id              != up.users_id
 WHERE up.users_id               = me.id
 GROUP BY uc.id
 ORDER BY SUM(up.weight) + SUM(uc.weight) DESC
         ,SUM(up.weight) DESC
         ,SUM(uc.weight) DESC

为了提高性能，建议在user_characteristics(id, question_id, question_choices_id, value, and user_id)和user_preferences(id, question_id, question_choices_id, value, and user_id)上创建索引。

请注意，上述SQL语句将为除发出请求的用户之外的每个用户返回一行。这显然是不可取的。因此，可以考虑添加HAVING SUM(up.weight) + SUM(uc.weight) > :some_minimum_value或其他方式进一步过滤结果。

进一步的调整可能包括仅返回将答案视为与我的偏好权重相同或更重要的人（即其特征权重>=我的权重偏好权重）。

- gwc