那么，这里有一个问题可能会让SQL专家跳起来说我懒惰，但是我被难住了。我们的网店今天早上崩溃了，这是可疑的查询。我一整天都在思考这个问题，但还没有想出任何天才的优化方法。能帮我一下吗?有什么关键指标吗?如何重组?我意识到这就像问墙的另一边是什么，然后给你一个指向另一个方向的望远镜，但我觉得值得一试:
SELECT DISTINCT (SELECT filename FROM (SELECT DISTINCT y.value AS label, x.value AS filename
FROM `catalog_product_super_link` AS z
INNER JOIN `catalog_product_entity_varchar` AS y
ON z.product_id = y.entity_id
INNER JOIN `catalog_product_entity_varchar` AS x
ON z.product_id = x.entity_id
WHERE parent_id = (SELECT entity_id
FROM `catalog_product_entity`
WHERE sku LIKE 'F11-ARC-7710%'
LIMIT 0, 1)
AND y.attribute_id = (SELECT attribute_id
FROM `eav_attribute`
WHERE attribute_code = 'image_label'
AND entity_type_id = (SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = 'catalog_product') LIMIT 0, 1)
AND x.attribute_id = (SELECT attribute_id
FROM `eav_attribute`
WHERE attribute_code = 'image'
AND entity_type_id = (SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = 'catalog_product'))) AS images WHERE c.value LIKE CONCAT(label,'%') LIMIT 0, 1) AS image,
pricing_value,
is_percent,
value_index,
c.value AS label,
d.sort_order AS sort_order
FROM `catalog_product_super_attribute_pricing` AS a
INNER JOIN `catalog_product_super_attribute_label` AS b
ON a.product_super_attribute_id = b.product_super_attribute_id
INNER JOIN `eav_attribute_option_value` AS c
ON value_index = c.option_id
INNER JOIN `eav_attribute_option` AS d
ON c.option_id = d.option_id
WHERE a.product_super_attribute_id = (SELECT product_super_attribute_id
FROM `catalog_product_super_attribute`
WHERE product_id = 5928
AND attribute_id = 143 LIMIT 0, 1)
UNION ALL
SELECT DISTINCT (SELECT filename FROM (SELECT DISTINCT y.value AS label, x.value AS filename
FROM `catalog_product_super_link` AS z
INNER JOIN `catalog_product_entity_varchar` AS y
ON z.product_id = y.entity_id
INNER JOIN `catalog_product_entity_varchar` AS x
ON z.product_id = x.entity_id
WHERE parent_id = (SELECT entity_id
FROM `catalog_product_entity`
WHERE sku LIKE 'F11-ARC-7710%'
LIMIT 0, 1)
AND y.attribute_id = (SELECT attribute_id
FROM `eav_attribute`
WHERE attribute_code = 'image_label'
AND entity_type_id = (SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = 'catalog_product') LIMIT 0, 1)
AND x.attribute_id = (SELECT attribute_id
FROM `eav_attribute`
WHERE attribute_code = 'image'
AND entity_type_id = (SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = 'catalog_product'))) AS images
WHERE label LIKE CONCAT((SELECT value FROM `eav_attribute_option_value` WHERE option_id = c.value LIMIT 0, 1),'%') LIMIT 0, 1) AS image,
0 AS pricing_value,
0 AS is_percent,
c.value AS value_index,
(SELECT value FROM `eav_attribute_option_value` WHERE option_id = c.value LIMIT 0, 1) AS label,
(SELECT sort_order FROM `eav_attribute_option` WHERE option_id = c.value LIMIT 0, 1) AS sort_order
FROM `catalog_product_entity` AS a
INNER JOIN `cataloginventory_stock_status` AS b
ON a.entity_id = b.product_id
INNER JOIN `catalog_product_entity_int` AS c
ON a.entity_id = c.entity_id
INNER JOIN `cataloginventory_stock_item` AS d
ON a.entity_id = d.product_id
WHERE c.attribute_id = (SELECT attribute_id
FROM `eav_attribute`
WHERE attribute_code = 'choose_size'
AND entity_type_id = (SELECT entity_type_id FROM `eav_entity_type` WHERE entity_type_code = 'catalog_product') LIMIT 0, 1)
AND a.entity_id IN (SELECT DISTINCT product_id
FROM `catalog_product_super_link`
WHERE parent_id = (SELECT entity_id FROM `catalog_product_entity` WHERE sku LIKE 'F11-ARC-7710%' LIMIT 0, 1))
AND (b.qty > 0 OR d.manage_stock = 0)
AND (SELECT value
FROM `eav_attribute_option_value`
WHERE option_id = c.value
LIMIT 0, 1) NOT IN (SELECT c.value
FROM `catalog_product_super_attribute_pricing` AS a
INNER JOIN `catalog_product_super_attribute_label` AS b ON a.product_super_attribute_id = b.product_super_attribute_id
INNER JOIN `eav_attribute_option_value` AS c ON value_index = c.option_id
WHERE a.product_super_attribute_id = (SELECT product_super_attribute_id FROM `catalog_product_super_attribute`
WHERE product_id = 5928
AND attribute_id = 143))
ORDER BY sort_order
提前感谢!
如果没有更多的信息，甚至没有你的测试平台，这几乎是不可能的…
但是，除了运行explain计划和按照其他人的建议添加索引之外，我看到了一些可能可以从中抽出来改进的东西:
例如，你有重复的子选择:像这样:
(SELECT entity_id
FROM `catalog_product_entity`
WHERE sku LIKE 'F11-ARC-7710%'
LIMIT 0, 1)
考虑将这些提取到FROM子句的最高级别，然后连接以获得结果。一次不是很多次。如果您为3或4个变体执行此操作，您应该会看到改进。
另一个看起来不太对的东西是在子选择中嵌套得很深的DISTINCT…你可能不需要这些，它们可能会增加开销。
快速回复
你能在PLSQL的等效中做到吗?你能消除这些子查询吗?
先执行这些操作，然后存储结果。假设它们返回单行。
然后，编写一个较小的查询，传入变量
天哪
我没有什么可提供的，除了在WHERE子句和EXPLAIN PLAN中对每个列进行索引，看看是否有TABLE扫描。
我的经验法则是，7个或更多的join将会执行得很差。我计算的INNER JOIN子句比你的要多。我对这个问题非常保留意见。有可能去正规化吗?VIEW怎么样?这对你有帮助吗?你比我更了解你的模式和问题
我前几天回答的这个问题可能会对你有所帮助:
我如何用嵌套的SELECT's优化这个SQL查询?
它是关于使嵌套查询更有效。我怀疑，如果您分别处理嵌套的选择，然后使用结果来形成一个更简单的查询，这将有所帮助。当然旅游查询要复杂得多，所以可能会有更多的内容，但希望这是一个开始。