我有许多连接查询，我正在为它搜索优化。 这是关于计算机的:例如，我有:联想8GBRAM 1TB core i5 ips等（所有这些在品牌名称之后都是属性）我有配置在哪里，我想把属性8GBRAM更改为16GBRAM，我必须搜索具有所有这些属性和16GBRAM的其他项目
两张表:
**st_item**
- id
- name
...
**st_item_specification_attribute**
- id
- st_item_id
- attribute_id
- attribute_value_id
...
我的问题是我的物品有15个属性。 当我用较低数量的属性进行测试时，我使用了这种查询结构，它可以工作，但是现在系统有85K项和1KK项属性，这就是查询:
SELECT `st_item`.id FROM `st_item`
LEFT JOIN `st_item_specification_attribute` `sisa_36590` ON st_item.id = sisa_36590.item_id AND sisa_36590.attribute_id = 365
LEFT JOIN `st_item_specification_attribute` `sisa_367910` ON st_item.id = sisa_367910.item_id AND sisa_367910.attribute_id = 367
LEFT JOIN `st_item_specification_attribute` `sisa_374641` ON st_item.id = sisa_374641.item_id AND sisa_374641.attribute_id = 374
LEFT JOIN `st_item_specification_attribute` `sisa_378366` ON st_item.id = sisa_378366.item_id AND sisa_378366.attribute_id = 378
LEFT JOIN `st_item_specification_attribute` `sisa_382500` ON st_item.id = sisa_382500.item_id AND sisa_382500.attribute_id = 382
LEFT JOIN `st_item_specification_attribute` `sisa_372134` ON st_item.id = sisa_372134.item_id AND sisa_372134.attribute_id = 372
LEFT JOIN `st_item_specification_attribute` `sisa_41268` ON st_item.id = sisa_41268.item_id AND sisa_41268.attribute_id = 412
LEFT JOIN `st_item_specification_attribute` `sisa_413368` ON st_item.id = sisa_413368.item_id AND sisa_413368.attribute_id = 413
LEFT JOIN `st_item_specification_attribute` `sisa_414929` ON st_item.id = sisa_414929.item_id AND sisa_414929.attribute_id = 414
LEFT JOIN `st_item_specification_attribute` `sisa_418496` ON st_item.id = sisa_418496.item_id AND sisa_418496.attribute_id = 418
LEFT JOIN `st_item_specification_attribute` `sisa_385748` ON st_item.id = sisa_385748.item_id AND sisa_385748.attribute_id = 385
LEFT JOIN `st_item_specification_attribute` `sisa_36625` ON st_item.id = sisa_36625.item_id AND sisa_36625.attribute_id = 366
LEFT JOIN `st_item_specification_attribute` `sisa_366355` ON st_item.id = sisa_366355.item_id AND sisa_366355.attribute_id = 366
LEFT JOIN `st_item_specification_attribute` `sisa_366816` ON st_item.id = sisa_366816.item_id AND sisa_366816.attribute_id = 366
LEFT JOIN `st_item_specification_attribute` `sisa_366370` ON st_item.id = sisa_366370.item_id AND sisa_366370.attribute_id = 366
WHERE (`parent_id`=1032) AND
(sisa_36590.attribute_value_id = 2230) AND
(sisa_367910.attribute_value_id = 2451) AND
(sisa_374641.attribute_value_id = 3793) AND
(sisa_378366.attribute_value_id = 2955) AND
(sisa_382500.attribute_value_id = 3879) AND
(sisa_372134.attribute_value_id = 2780) AND
(sisa_41268.attribute_value_id = 3363) AND
(sisa_413368.attribute_value_id = 3373) AND
(sisa_414929.attribute_value_id = 3378) AND
(sisa_418496.attribute_value_id = 3844) AND
(sisa_385748.attribute_value_id = 3036) AND
(sisa_36625.attribute_value_id = 2315) AND
(sisa_366355.attribute_value_id = 2408) AND
(sisa_366816.attribute_value_id = 2412) AND
(sisa_366370.attribute_value_id = 2420)
查询必须比较特定对attribute_id=>； attribute_value_id，这就是我的“on子句”与item_id和attribute_id以及特定的别名一起使用的原因
您可以使用聚合:
select i.id
from st_item i join
st_item_specification_attribute sisa
ON sisa.item_id = i.item_id
where i.parent_id = 1032 and
(sisa.attribute_id, attribute_value_id) in ( (365, 2230), (367, 2451), . . .)
group by i.id
having count(*) = 15;
您可以将where条件移动到on条件中，并将左联接更改为内联接。
SELECT `st_item`.id FROM `st_item`
JOIN `st_item_specification_attribute` `sisa_36590`
ON st_item.id = sisa_36590.item_id AND sisa_36590.attribute_id = 365
AND sisa_36590.attribute_value_id = 2230
JOIN `st_item_specification_attribute` `sisa_367910`
ON st_item.id = sisa_367910.item_id AND sisa_367910.attribute_id = 367
AND sisa_367910.attribute_value_id = 2451
...
WHERE `parent_id`=1032
SELECT `st_item`.id FROM `st_item`
JOIN `st_item_specification_attribute` `sisa`
ON st_item.id = sisa.item_id AND
(
(sisa.attribute_id = 365 AND sisa.attribute_value_id = 2230)
OR
(sisa.attribute_id = 367 AND sisa.attribute_value_id = 2451)
...
)
WHERE `parent_id`=1032
GROUP BY `st_item`.id
HAVING COUNT(*) = 15
我无法预测性能，但我想你可以在所有的连接中选择一个
（我假定atribute_id和Attribute_value_id对每个item_id都是唯一的）
SELECT `st_item`.id FROM `st_item`
WHERE (`parent_id`=1032) AND
15 = (SELECT COUNT(*) FROM st_item_specification_attribute attr
WHERE `st_item`.id = attr.item_id
AND ( attribute_id = 365 AND attribute_value_id = 2230 OR
...
)
