我有forowing db shema(mysql 5.x),其中我在一个表中保存项目值,而在另一个表中保存每个属性的相应名称:
classified_attr(属于特定分类的属性)
cl_id | value
39393 | 173
cat_attr(属性名称):
attr_id | attr_de
173 green
123 available
这适用于我需要的所有情况,除一种情况外,我面临某些性能问题,因为我需要从一个项目中检索一个SQL查询所有值,包括名称。
。我正在通过为每个值多次jo张表来做到这一点。例如
SELECT
ca_power.value AS power_id,
catr_power.attr_de AS power,
ca_avail.value AS avail_id,
catr_avail.attr_de AS avail
FROM
classifieds AS c
LEFT JOIN classifieds_attr AS ca_power ON c.ID = ca_power.cl_id AND ca_power.attr_group_id = 19
LEFT JOIN cat_attr AS catr_power ON ca_power.attr_group_id = ca_power.attr_group_id AND catr_power.attr_id = ca_power.value
LEFT JOIN classifieds_attr AS ca_avail ON c.ID = ca_avail.cl_id AND ca_avail.attr_group_id = 17
LEFT JOIN cat_attr AS catr_avail ON ca_avail.attr_group_id = ca_avail.attr_group_id AND catr_avail.attr_id = ca_avail.value
对于这两个属性及其名称,它是可以忽略的,但是现在有10个属性,我正面临性能降低。
是否有一种方法可以更改我的SQL查询,以便在保持结构的同时以更快的方式检索所有这些值,包括名称?如果不是,那么存储这些价值的更好的侵略是什么?
好吧,您的查询有点奇怪,因为您自行将classifieds_attr
表加入本身多次(ca_power.attr_group_id = ca_power.attr_group_id
和ca_avail.attr_group_id = ca_avail.attr_group_id
),这是完全不必要的,尤其是在加入条件的一部分时完成到cat_attr
表。
此外,您的多个连接到classifieds_attr
,而cat_attr
表似乎仅通过attr_group_id列有所不同(上面的规格中不存在)。
您可以通过删除冗余加入并将其变成枢轴查询来简化查询:
SELECT
c.id as classified_id,
-- Power Group (19)
max(case when ca.attr_group_id = 19 then ca.value end) AS power_id,
max(case when ca.attr_group_id = 19 then catr.attr_de end) AS power,
-- Avail Group (17)
max(case when ca.attr_group_id = 17 then ca.value end) AS avail_id,
max(case when ca.attr_group_id = 17 then catr.attr_de end) AS avail
FROM
classifieds AS c
LEFT JOIN classifieds_attr AS ca
ON c.ID = ca.cl_id
AND ca.attr_group_id in (17,19)
LEFT JOIN cat_attr AS catr
ON catr.attr_id = ca.value
GROUP BY c.id
要添加其他列(组),简单地复制了汇总列(请注意上面的注释列)更改案例语句中的组ID,然后将适当的组ID添加到第一个加入的列表中。
假设ca.value
是必需的字段,并且是cat_attr
引用attr_id
的外键,则您也可以尝试将cat_attr
连接到classifieds_attr
。您没有提供太多的元数据,因此很难知道您的设置。无论如何,这是潜在的优化:
SELECT c.id as classified_id,
-- Power Group (19)
max(case when ca.attr_group_id = 19 then ca.value end) AS power_id,
max(case when ca.attr_group_id = 19 then catr.attr_de end) AS power,
-- Avail Group (17)
max(case when ca.attr_group_id = 17 then ca.value end) AS avail_id,
max(case when ca.attr_group_id = 17 then catr.attr_de end) AS avail
FROM classifieds AS c
LEFT JOIN classifieds_attr AS ca
JOIN cat_attr AS catr
ON catr.attr_id = ca.value
ON c.ID = ca.cl_id
AND ca.attr_group_id in (17,19)
GROUP BY c.id
如果您不在乎看到没有属性的classified.id
S,则还可以将第一个联接从外部加入到内部连接,并可能获得一些额外的性能。
我会添加以下索引(如果您还没有的话):
create index ix1 on classifieds_attr (cl_id, attr_group_id);
create index ix2 on cat_attr (attr_id);
这应该可以大大加快您的查询。尽管如此,您的查询上有一些可恶的东西,因为它具有两个多余的,无用的过滤条件。
您似乎有多个单列索引。但是,第一个 - 复合索引 - 对性能至关重要。