mysql - 需要建议以正确索引具有许多要搜索的字段的表

Question

我有一个包含很多列的用户表，它大致如下所示:

dname:             { type: string(255), notnull: true }
email:             { type: string(255), notnull: true, unique: true }
email_code:        { type: string(255) }
email_confirmed:   { type: boolean, default: false }
profile_filled:    { type: boolean, default: false }
password:          { type: string(255), notnull: true }
image_id:          { type: integer }
gender:            { type: enum, values: [male, female] }
description:       { type: string }
dob:               { type: date }
height:            { type: integer(3) }
looks:             { type: enum, values: [thin, average, athletic, heavy] }
looking_for:       { type: enum, values: [marriage, dating, friends] }
looking_for_age1:  { type: integer }
looking_for_age2:  { type: integer }
color_hair:        { type: enum, values: [black, brown, blond, red] }
color_eyes:        { type: enum, values: [black, brown, blue, green, grey] }
marital_status:    { type: enum, values: [single, married, divorced, widowed] }
smokes:            { type: enum, values: [no, yes, sometimes] }
drinks:            { type: enum, values: [no, yes, sometimes] }
has_children:      { type: enum, values: [no, yes] }
wants_children:    { type: enum, values: [no, yes] }
education:         { type: enum, values: [school, college, university, masters, phd] }
occupation:        { type: enum, values: [no, yes] }
country_id:        { type: integer }
city_id:           { type: integer }
lastlogin_at:      { type: timestamp }
deleted_at:        { type: timestamp }

我创建了一个包含大部分字段(枚举、国家/地区、城市)的表单，允许用户根据他们选择的字段生成 where 语句。因此，如果有人选择 smokes: no 和 country_id: 7 那么 sql where 语句可能如下所示:

SELECT id 
FROM user u 
WHERE u.deleted_t IS NULL AND u.profile_filled IS NOT NULL AND smokes = 'no' AND country_id = 7;

因为用户可以选择任何字段组合作为过滤依据，我不确定我应该如何为这个表建立索引，我应该只在所有可以过滤的字段上创建一个列索引吗？你有什么建议？

Answer 1

我有一个工作表，里面有同样的东西，有很多列和 1000 种不同的选择方式。这是一场噩梦。然而，我确实发现，有一些经常使用的过滤器组合。我会为那些创建索引并让其他很少使用的索引缓慢运行。在 MSSQL 中，我可以运行一个查询来显示对数据库运行的最昂贵的查询，mySQL 应该有类似的东西。一旦我有了它们，我就创建一个覆盖列的索引以加快它们的速度。最终，您将覆盖 90%。除非我用 AK47 指着我，否则我个人再也不会设计这样的 table 了。 (我的索引比表中的数据大 3 倍，如果您需要添加一堆或记录，这是非常不酷的)。我不确定如何重新设计表格，我的第一个想法是将表格分成两部分，但这会增加其他地方的麻烦。

用户表(用户ID、姓名)

1, Lisa
2, Jane
3, John

用户属性表(UserID, AttributeName,AttributeValue)

1, EYES, Brown
1, GENDER, Female
2, EYES, Blue
2, GENDER, Female
3  EYES, Blue
3, GENDER, Male

这将使识别属性更快，但会使您的查询不那么直接编写。

SELECT UserID, COUNT(*) as MatchingAttributes
FROM   UserAttributes 
WHERE  (UserAttributes.AttributeName = 'EYES' AND UserAttributes.AttributeValue = 'Blue') OR
       (UserAttributes.AttributeName = 'GENDER' AND UserAttributes.AttributeValue = 'Female') 

这应该返回以下内容

UserID, MatchingAttributes
1, 1
2, 2
3, 1

然后您需要做的就是将 HAVING COUNT(*) = 2 添加到查询以仅选择匹配的 ID。从中进行选择有点复杂，但它也提供了一个简洁的功能，假设您过滤 10 个属性，并返回所有具有 10 个匹配项的属性。很酷，但说没有一个匹配 100%。你可以说嘿，我没有找到匹配的，但这些有 10 个中有 9 个或 90% 匹配。 (请确保，如果我搜索一位蓝眼睛的金发女性，我不会收到一条消息说没有找到，但这是下一个最接近的匹配结果，其中包含蓝眼睛的金发男性，匹配率为 60%。那会非常不酷)

如果您选择拆分表格，则需要考虑更多的事情，例如如何将属性作为数字、日期和文本存储在单个列中？或者是这些单独的表或列。无论是宽表还是拆分表，都没有简单的答案。