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

Question

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

我在问类似的问题，如年龄范围，宗教偏好，吸烟偏好的用户偏好。

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

现在，我想向用户显示基于首选项集的匹配。我想知道我应该如何设计MySQL表和索引。

我应该创建一个大的user_preferences表，并有所有的偏好索引。应该是多列索引或合并索引。

我是否应该将一组问题保存在不同的表中，并在获取数据时将它们连接起来？我

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。如果匹配的是用户表中的数据(例如用户的年龄是否在其他用户的首选年龄范围内)，您可能仍然需要进行特殊处理。

请注意旨在帮助P1.NAME = P2.NAME AND P1.VALUE = P2.VALUE的{NAME, VALUE}上的索引。InnoDB表是clustered的，一个后果是二级索引包含PK字段的副本-在这种情况下，这会导致索引I1完全cover表。MySQL是否会真正使用它则是另一回事--一如既往地查看查询计划和对代表性数据的度量……

Answer 2

我看到的东西是这样的：

​

​

questions是要回答的问题列表。question_type是一个枚举，它指明了期望的答案类型(例如，从question_choices中查找、日期、数字、文本等)-您希望输入的任何类型的数据。这与该表中的其他列一起，可以驱动您的输入表单。

question_answers包含一个预定义的问题答案列表(例如预定义的宗教列表、头发颜色或眼睛颜色等)。这可用于在输入表单上构建值的下拉列表。

users非常不言自明。

user_characteristics包含我对调查问卷的回答列表。weight专栏指出，对我来说，找我的人得到同样的答案是多么重要。如果答案来自从question_choices表构建的选择列表，则将填充question_choices_id。否则，question_choices_id将为空。对于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和user_id)和user_preferences索引(id、question_id、question_choices_id、value和user_id)。

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

进一步的调整可能只包括那些与我一样看重答案或比我更看重答案的人(即他们的特征权重是>= my weight preference weight。