在同一个表上使用多个内连接优化SQL查询
在同一个表上使用多个内连接优化SQL查询
提问于 2017-07-13 16:45:27
我遇到了一个性能问题：
商店有一个带有类别“颜色”、“大小”、“性别”和“特征”的商品过滤器。所有这些详细信息都存储在一个article_criterias表中，如下所示：
article_criterias的表格布局是；这个表格大约有36.000行：
article_id | group | option | option_val
100 | "size" | "35" | 35.00
100 | "size" | "36" | 36.00
100 | "size" | "36½" | 36.50
100 | "color" | "40" | 40.00
100 | "color" | "50" | 50.00
100 | "gender" | "1" | 1.00
101 | "size" | "40" | 40.00
...我们有一个动态构建的SQL查询，它是根据当前选择的条件构建的。该查询适用于2-3个条件，但当选择5个以上的选项时会变得非常慢(每个额外的内部连接大约会使执行时间加倍)
我们怎样才能让这个SQL更快，甚至可以用一个更高性能的概念来代替内部连接？
这是查询(逻辑是正确的，只是性能很差)：
-- This SQL is generated when the user selected the following criteria
-- gender: 1
-- color: 80 + 30
-- size 36 + 37 + 38 + 39 + 42 + 46
SELECT
criteria.group AS `key`,
criteria.option AS `value`
FROM articles
INNER JOIN article_criterias AS criteria ON articles.id = criteria.article_id
INNER JOIN article_criterias AS criteria_gender
ON criteria_gender.article_id = articles.id AND criteria_gender.group = "gender"
INNER JOIN article_criterias AS criteria_color1
ON criteria_color1.article_id = articles.id AND criteria_color1.group = "color"
INNER JOIN article_criterias AS criteria_size2
ON criteria_size2.article_id = articles.id AND criteria_size2.group = "size"
INNER JOIN article_criterias AS criteria_size3
ON criteria_size3.article_id = articles.id AND criteria_size3.group = "size"
INNER JOIN article_criterias AS criteria_size4
ON criteria_size4.article_id = articles.id AND criteria_size4.group = "size"
INNER JOIN article_criterias AS criteria_size5
ON criteria_size5.article_id = articles.id AND criteria_size5.group = "size"
INNER JOIN article_criterias AS criteria_size6
ON criteria_size6.article_id = articles.id AND criteria_size6.group = "size"
INNER JOIN article_criterias AS criteria_size7
ON criteria_size7.article_id = articles.id AND criteria_size7.group = "size"
WHERE
AND (criteria_gender.option IN ("1"))
AND (criteria_color1.option IN ("80", "30"))
AND (criteria_size2.option_val BETWEEN 35.500000 AND 36.500000)
AND (criteria_size3.option_val BETWEEN 36.500000 AND 37.500000)
AND (criteria_size4.option_val BETWEEN 37.500000 AND 38.500000)
AND (criteria_size5.option_val BETWEEN 38.500000 AND 39.500000)
AND (criteria_size6.option_val BETWEEN 41.500000 AND 42.500000)
AND (criteria_size7.option_val BETWEEN 45.500000 AND 46.500000)回答 3
Stack Overflow用户
回答已采纳
发布于 2017-07-14 07:57:14
键/值表真的很麻烦。但是，为了找到某些匹配的条件，可以聚合您的数据：
select
a.*,
ac.group AS "key",
ac.option AS "value"
from articles a
join article_criterias ac on ac.article_id = a.article_id
where a.article_id in
(
select article_id
from article_criterias
group by article_id
having sum("group" = 'gender' and option = '1') > 0
and sum("group" = 'color' and option in ('30','80')) > 0
and sum("group" = 'size' and option_val between 35.5 and 36.5) > 0
and sum("group" = 'size' and option_val between 36.5 and 37.5) > 0
and sum("group" = 'size' and option_val between 37.5 and 38.5) > 0
and sum("group" = 'size' and option_val between 38.5 and 39.5) > 0
and sum("group" = 'size' and option_val between 41.5 and 42.5) > 0
and sum("group" = 'size' and option_val between 45.5 and 46.5) > 0
)
order by a.article_id, ac.group, ac.option;这将为您提供性别为1、颜色为30和/或80的所有文章，以及列出的所有尺寸范围，以及它们的所有选项。(不过，尺寸范围有点奇怪；例如，36.5的尺寸将满足两个范围。)你得到的想法是:按article_id分组，并使用HAVING，以便只获得符合标准的article_ids。
至于你想要的索引
create index idx on article_criterias(article_id, "group", option, option_val);Stack Overflow用户
发布于 2017-07-13 18:29:33
正如@affan-pathan建议的那样，添加索引确实解决了这个问题：
CREATE INDEX text_option
ON `article_criterias` (`article_id`, `group`, `option`);
CREATE INDEX numeric_option
ON `article_criterias` (`article_id`, `group`, `option_val`);这两个索引将上述查询表单的执行时间缩短了近1分钟，减少到不到50毫秒！
Stack Overflow用户
发布于 2017-07-13 21:42:33
我知道你创建的索引解决了你的问题，但我只是想玩一个伪替代(避免多个内部连接)，你能尝试一下这样的东西吗？(我只测试了三个条件。您的条件应插入内部查询中。要只选择满足所有条件的记录，必须更改最后一个WHERE条件(WHERE max = 3，使用上面编写的条件数；因此，如果使用5个条件，则应编写WHERE max = 5)。(为了便于使用，我更改了列、组和选项的名称)。这只是一个想法，所以请做一些测试和性能检查，请让我知道…
CREATE TABLE CRITERIA (ARTICLE_ID INT, GROU VARCHAR(10), OPT VARCHAR(20), OPTION_VAL NUMERIC(12,2));
CREATE TABLE ARTICLES (ID INT);
INSERT INTO CRITERIA VALUES (100,'size','35',35);
INSERT INTO CRITERIA VALUES (100,'size','36',36);
INSERT INTO CRITERIA VALUES (100,'color','40',40);
INSERT INTO CRITERIA VALUES (100,'gender','1',1);
INSERT INTO CRITERIA VALUES (200,'size','36.2',36.2);
INSERT INTO CRITERIA VALUES (300,'size','36.2',36.2);
INSERT INTO ARTICLES VALUES (100);
INSERT INTO ARTICLES VALUES (200);
INSERT INTO ARTICLES VALUES (300);
-------------------------------------------------------
SELECT D.article_id, D.GROU, D.OPT
FROM (SELECT C.*
, @o:=CASE WHEN @h=ARTICLE_ID THEN @o ELSE cumul END max
, @h:=ARTICLE_ID AS a_id
FROM (SELECT article_id,
B.GROU, B.OPT,
@r:= CASE WHEN @g = B.ARTICLE_ID THEN @r+1 ELSE 1 END cumul,
@g:= B.ARTICLE_ID g
FROM CRITERIA B
CROSS JOIN (SELECT @g:=0, @r:=0) T1
WHERE (B.GROU='gender' AND B.OPT IN ('1'))
OR (B.GROU='color' AND B.OPT IN ('40', '30'))
OR (B.GROU='size' AND B.OPT BETWEEN 35.500000 AND 36.500000)
ORDER BY article_id
) C
CROSS JOIN (SELECT @o:=0, @h:=0) T2
ORDER BY ARTICLE_ID, CUMUL DESC) D
WHERE max=3
;输出：
article_id GROU OPT
100 gender 1
100 color 40
100 size 36页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/45075659
复制相关文章
相似问题
腾讯云开发者
