MySQL大量的内部连接查询导致响应缓慢

Question

我读过其他相关的问题，但我的问题是独特的，因为它的结构。

我的应用程序是存储关于10,000+用户的配置文件定义了许多参数(性别，体重，身高，头发颜色，眼睛颜色，舞蹈技巧.以此类推，大约100个属性，比如说)。

应用程序使用以下属性构造筛选器表单。用户使用此表单过滤数据库，因此构造一个包含多个子查询的查询，每个过滤器使用一个查询。

问题是，使用超过8-9个过滤器，发动机将崩溃进入一个非常长的响应(我不得不在30米的等待后杀死这个过程)。

所以，这就是数据库的结构

Table def_attributes (以下是属性定义)

• id -在值表中用作attr_id

表使用(用户定义，现在只使用列activ )

• id ->在其他表中被命名为user_id。
• 如果用户处于活动状态并将显示(列索引)，则and >为1。

Table val_atribute (存储每个用户的属性值)

• attr_id -->过滤器的attrID (列索引)
• 属性的attr_value ->值
• user_id (列索引)

例如，下面是由筛选表单构造的查询，该查询滞后：

SELECT DISTINCT Q1.user_id
FROM   (SELECT DISTINCT val_atribute.user_id
        FROM   val_atribute
        WHERE  attr_id = 45
               AND attr_value IN ( 'Actor', 'Actor Amator' )) Q1
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 46
                          AND Floor(Datediff(Curdate(), attr_value) / 365) >= '20') Q2
               ON Q1.user_id = Q2.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 46
                          AND Floor(Datediff(Curdate(), attr_value) / 365) <= '50') Q3
               ON Q2.user_id = Q3.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 47
                          AND attr_value IN ( 'feminin', 'masculin' )) Q4
               ON Q3.user_id = Q4.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 102
                          AND attr_value IN ( 'African', 'Asiatic', 'Caucazian', 'Metis' )) Q5
               ON Q4.user_id = Q5.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 103
                          AND attr_value >= 1) Q6
               ON Q5.user_id = Q6.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 103
                          AND attr_value <= 200) Q7
               ON Q6.user_id = Q7.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 104
                          AND attr_value >= 10) Q8
               ON Q7.user_id = Q8.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 104
                          AND attr_value <= 150) Q9
               ON Q8.user_id = Q9.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 107
                          AND attr_value IN ( 'Albastri', 'Caprui', 'Heterocrom', 'Verzi' )) Q10
               ON Q9.user_id = Q10.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 108
                          AND attr_value IN ( 'Blond', 'Brunet', 'Castaniu', 'Roscat', 'Saten' )) Q11
               ON Q10.user_id = Q11.user_id
       INNER JOIN (SELECT DISTINCT val_atribute.user_id
                   FROM   val_atribute
                   WHERE  attr_id = 109
                          AND attr_value IN ( 'Calvitie', 'Lung', 'Mediu', 'Scurt', 'Zero' )) Q12
               ON Q11.user_id = Q12.user_id
       INNER JOIN (SELECT DISTINCT utilizatori.id
                   FROM   utilizatori
                   WHERE  activ = 1) Q13
               ON Q12.user_id = Q13.id
GROUP  BY user_id

Q2正在计算年龄，因为我们只有属性出生日期和过滤器，Q2想要年龄> 20。

最后一个查询(在这里是Q13)总是从Table获取活动用户。

我认为这是笛卡尔级数的一个问题，但是的问题是：，我怎样才能使查询变得更快？非常感谢!

编辑/问题解决：

在Gordon Linoff的帮助下，我使用相同的过滤器构建了正确的查询：

SELECT u.id FROM utilizatori u WHERE EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 45 AND attr_value IN ( 'Actor', 'Actor Amator' )) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 46 AND Floor(Datediff(Curdate(), attr_value) / 365) >= 20) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 46 AND Floor(Datediff(Curdate(), attr_value) / 365) <= 50) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 47 AND attr_value IN ( 'feminin', 'masculin' )) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 102 AND attr_value IN ( 'African', 'Asiatic', 'Caucazian', 'Metis' )) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 103 AND attr_value >= 1) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 103 AND attr_value <= 200) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 104 AND attr_value >= 10) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 104 AND attr_value <= 150) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 107 AND attr_value IN ( 'Albastri', 'Caprui', 'Heterocrom', 'Verzi' )) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 108 AND attr_value IN ( 'Blond', 'Brunet', 'Castaniu', 'Roscat', 'Saten' )) AND EXISTS (SELECT 1 FROM val_atribute va WHERE va.user_id = u.id AND va.attr_id = 109 AND attr_value IN ( 'Calvitie', 'Lung', 'Mediu', 'Scurt', 'Zero' )) AND activ = 1

现在，查询大约需要0.0015秒才能运行。

Answer 1

MySQL中的子查询是有问题的-- select distinct使事情变得更糟。您正在使用and连接子查询。我建议使用exists来构造相同的逻辑。

所以：

select u.*
from users u
where exists (select 1
              from val_atribute va
              where va.user_id = u.user_id and
                    va.attr_id = 45 and
                    va.attr_value in ( 'Actor', 'Actor Amator' )
             ) and
      exists (select 1
              from val_atribute va
              where va.user_id = u.user_id and
                    va.attr_id = 46 and
                    Floor(Datediff(Curdate(), va.attr_value) / 365) >= 20) Q2
             ) and
      . . .

此版本的查询可以利用val_attribute(user_id, attr_id, attr_value)上的索引。它应该更快，并且具有更好的可伸缩性。

Answer 2

这是出了名的低效EAV模式设计的一个变体。

到目前为止，最好的解决方案(在这个问题中)包括对utilizatori进行全表扫描，并对用于筛选的属性表(val_atribute)进行多个探测。

为了提高效率，val_atribute需要PRIMARY KEY(user_id, attr_id)。不，这两列的单独索引不是很好。

为了提高效率，您需要提取少量常用的属性，并添加一个索引(Es)。这样就可以避免全表扫描(10K用户，加上大量的属性查找)，将其减少到其中的一小部分。

更多讨论：http://mysql.rjweb.org/doc.php/eav