如何在应用多个过滤器时提高查询性能？

Question

我有一个三元关系，叫做ternary：

id_Offer    -   id_Profile  -   id_Skill
1           -   1           -   1
1           -   2           -   1

[and so on, there would be more registers for each id_Offer from Offer but I want to limit the example]

表配置文件如下所示(profile_interest是一个建立概要文件与兴趣之间关系的表，仅此而已)：

id_Profile -   profile_name
1          -   profile-1
2          -   profile-2
3          -   profile-3

因此，当我进行以下查询时，我添加的OR子句越多，查询的执行情况就越差，从~0.1秒到0.2秒开始，这是我所做的任何其他查询的结果，最多1.5秒。

SELECT DISTINCT ternary_table.id_profile, COUNT(distinct profile_interest.id_interest) as matching 
FROM ternary_table INNER JOIN profile ON ternary_table.id_profile=profile.id_profile 
INNER JOIN profile_interest ON profile.id_profile=profile_interest.id_profile 
WHERE profile_interest.id_interest= '1' 
 OR profile_interest.id_interest = '2' 
 OR profile_interest.id_interest = '3'
 OR profile_interest.id_interest = '14'
 OR profile_interest.id_interest = '15'
 OR profile_interest.id_interest = '16'
GROUP BY(ternary_table.id_profile) 
ORDER BY matching DESC; 

我尝试将字段profile_interest.id_interest作为索引列，并使用：

CREATE INDEX filter_interest ON profile_interest(id_interest );

一点进步都没有。数据库的重量小于千兆字节，是一个非常小的数据库，有15个表，所以我想知道是否有任何方法来缩短查询滞后。

编辑:要添加更多信息，我之所以担心这一点，是因为这些数据的唯一目的是连接到API，因此SQL中的任何延迟都会延迟对该数据的每次调用。

Edit1:添加了解释输出，并删除了第一个不同的输出，因为它是不必要的

+----+-------------+---------------------+------------+--------+------------------------------------------------+------------+---------+------------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table               | partitions | type   | possible_keys                                  | key        | key_len | ref                                | rows | filtered | Extra                                                     |
+----+-------------+---------------------+------------+--------+------------------------------------------------+------------+---------+------------------------------------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | profile_interest      | NULL       | range  | PRIMARY,id_interest,filter_interest | id_interest | 202 | NULL                               |   40 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | perfil              | NULL       | eq_ref | PRIMARY                                        | PRIMARY    | 202     | BBDD.profile_interest.id_perfil    |    1 |   100.00 | Using index                                               |
|  1 | SIMPLE      | oferta_skill_perfil | NULL       | ref    | PRIMARY,id_skill,id_perfil                     | id_perfil  | 202     | BBDD.profile_interest.id_perfil    | 4609 |   100.00 | Using index                                               |
+----+-------------+---------------------+------------+--------+------------------------------------------------+------------+---------+------------------------------------+------+----------+-----------------------------------------------------------+

编辑2:在每个请求中添加表创建

SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE profile (
    id_profile VARCHAR(200) NOT NULL,
    name_profile VARCHAR(200),
    type_profile VARCHAR(200),
    PRIMARY KEY (id_profile)
);


CREATE TABLE ternary (
    id_oferta VARCHAR(200) NOT NULL,
    id_skill VARCHAR(200) NOT NULL,
    id_profile VARCHAR(200) NOT NULL,
    ranking_skill DOUBLE NOT NULL,
    PRIMARY KEY (id_oferta, id_skill, id_profile),
    FOREIGN KEY (id_oferta) REFERENCES oferta(id_oferta),
    FOREIGN KEY (id_skill) REFERENCES skill(id_skill),
    FOREIGN KEY (id_profile) REFERENCES profile(id_profile)
);

 CREATE TABLE interest (
    id_interest VARCHAR(200) NOT NULL,
    name_interes VARCHAR(200),
    PRIMARY KEY (id_interest)
 );


CREATE TABLE profile_interest (
    id_profile VARCHAR(200) NOT NULL,
    id_interest VARCHAR(200) NOT NULL, 
    PRIMARY KEY (id_profile, id_interest),
    FOREIGN KEY (id_profile) REFERENCES profile(id_profile),
    FOREIGN KEY (id_interest) REFERENCES interes(id_interest)
);

Answer 1

你可以试着把它写成：

select tt.id_profile,
       (select count(distinct pi.id_interest)
        from profile_interest pi
        where tt.id_profile = pi.id_profile and
              pi.id_interest in (1, 2, 3, 14, 15, 16)
       ) as matching
from ternary_table tt;

要使其工作，您需要在profile_interest(id_profile, id_interest)上建立一个索引。

编辑：

如果只需要匹配的行，则可以添加：

having matching > 0

Answer 2

SELECT  id_profile,
        COUNT(id_interest) as matching
    FROM  profile_interest AS pi
    WHERE  id_interest IN (1,2,4,14,15,16)
      AND EXISTS ( SELECT * FROM oferta_skill_perfil
                                          WHERE id_profile = pi.id_profile )
      AND EXISTS ( SELECT * FROM profile  WHERE id_profile = pi.id_profile )
    GROUP BY id_profile
    ORDER BY  matching DESC;

profile_interest按这个顺序需要INDEX(id_interest, id_profile)。

我之所以使用EXISTS，只是因为这似乎是接触其他表的真正目的。但也许他们不需要被碰？

经济放缓是由我所说的“爆炸-内爆”或“膨胀-泄气”造成的。这发生在JOIN某些表(这会导致更多中间行)时，然后GROUP BY会缩回原来的位置。摆脱它的技术是首先集中精力完成聚合(在您的例子中是COUNT)，然后是需要的JOIN。

当您只需要检查是否存在时，EXISTs要快得多，而不是实际找到所有的4609行。

profile_interest似乎是一个多到多的映射表。见我的提示http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table。

请注意，它推荐了我上面建议的索引。

通常，ids是整数；为什么有VARCHAR(200)？这样长的字符串没有明显的来源。

Answer 3

没有答案。太久不能发表评论..。

我觉得这个容易读懂..。

SELECT x.id_profile
     , COUNT(DISTINCT i.id_interest) matching 
  FROM ternary_table x
  JOIN profile p 
    ON p.id_profile = x.id_profile
  JOIN profile_interest i
    ON i.id_profile = p.id_profile
 WHERE i.id_interest IN(1,2,3,14,15,16)
 GROUP 
    BY x.id_profile
 ORDER 
    BY matching DESC; 

现在，如果我们能够看到显示CREATE语句以及对此的解释就好了。