mysql - 通过在彼此之间嵌套子查询来解决 MySQL 中 61 个表 JOIN 的限制

Question

我发现你可以通过使用子查询来绕过 MySQL 中 61 个表连接的限制。 https://stackoverflow.com/a/20134402/2843690

我正在尝试弄清楚如何轻松在我正在开发的程序中使用它来从 Magento 获取详细的产品列表(但我认为这个问题的答案可能适用于很多情况都涉及到 eav)。需要连接的表如下所示:

catalog_product_entity
+-----------+----------------+
| entity_id | entity_type_id |
+-----------+----------------+
|         1 |              4 |
|         2 |              4 |
|         3 |              4 |
|         4 |              4 |
|         5 |              4 |
|         6 |              4 |
|         7 |              4 |
|         8 |              4 |
|         9 |              4 |
+-----------+----------------+

catalog_product_entity_int
+----------+----------------+--------------+-----------+-------+
| value_id | entity_type_id | attribute_id | entity_id | value |
+----------+----------------+--------------+-----------+-------+
|        1 |              4 |            2 |         1 |   245 |
|        2 |              4 |            3 |         1 |   250 |
|        3 |              4 |            4 |         1 |   254 |
|        4 |              4 |            2 |         2 |   245 |
|        5 |              4 |            3 |         2 |   249 |
|        6 |              4 |            4 |         2 |   253 |
|        7 |              4 |            2 |         3 |   247 |
|        8 |              4 |            3 |         3 |   250 |
|        9 |              4 |            4 |         3 |   254 |
+----------+----------------+--------------+-----------+-------+

eav_attribute
+--------------+----------------+----------------+--------------+
| attribute_id | entity_type_id | attribute_code | backend_type |
+--------------+----------------+----------------+--------------+
|            1 |              4 | name           | varchar      |
|            2 |              4 | brand          | int          |
|            3 |              4 | color          | int          |
|            4 |              4 | size           | int          |
|            5 |              4 | price          | decimal      |
|            6 |              4 | cost           | decimal      |
|            7 |              4 | created_at     | datetime     |
|            8 |              3 | name           | varchar      |
|            9 |              3 | description    | text         |
+--------------+----------------+----------------+--------------+

eav_attribute_option
+-----------+--------------+
| option_id | attribute_id |
+-----------+--------------+
|       245 |            2 |
|       246 |            2 |
|       247 |            2 |
|       248 |            3 |
|       249 |            3 |
|       250 |            3 |
|       251 |            4 |
|       252 |            4 |
|       253 |            4 |
|       254 |            4 |
+-----------+--------------+

eav_attribute_option_value
+----------+-----------+-------------------+
| value_id | option_id |       value       |
+----------+-----------+-------------------+
|       15 |       245 | Fruit of the Loom |
|       16 |       246 | Hanes             |
|       17 |       247 | Jockey            |
|       18 |       248 | White             |
|       19 |       249 | Black             |
|       20 |       250 | Gray              |
|       21 |       251 | Small             |
|       22 |       252 | Medium            |
|       23 |       253 | Large             |
|       24 |       254 | Extra Large       |
+----------+-----------+-------------------+

我正在编写的程序生成的 SQL 查询如下所示:

SELECT cpe.entity_id
, brand_int.value as brand_int, brand.value as brand
, color_int.value as color_int, color.value as color
, size_int.value as size_int, size.value as size

FROM catalog_product_entity as cpe

LEFT JOIN catalog_product_entity_int as brand_int
ON (cpe.entity_id = brand_int.entity_id
AND brand_int.attribute_id = 2)
LEFT JOIN eav_attribute_option as brand_option
ON (brand_option.attribute_id = 2
AND brand_int.value = brand_option.option_id)
LEFT JOIN eav_attribute_option_value as brand
ON (brand_option.option_id = brand.option_id)

LEFT JOIN catalog_product_entity_int as color_int
ON (cpe.entity_id = color_int.entity_id
AND color_int.attribute_id = 3)
LEFT JOIN eav_attribute_option as color_option
ON (color_option.attribute_id = 3
AND color_int.value = color_option.option_id)
LEFT JOIN eav_attribute_option_value as color
ON (color_option.option_id = color.option_id)

LEFT JOIN catalog_product_entity_int as size_int
ON (cpe.entity_id = size_int.entity_id
AND size_int.attribute_id = 4)
LEFT JOIN eav_attribute_option as size_option
ON (size_option.attribute_id = 4
AND size_int.value = size_option.option_id)
LEFT JOIN eav_attribute_option_value as size
ON (size_option.option_id = size.option_id)
;

编写生成查询的代码相对容易，而且查询也相当容易理解；然而，很容易达到 61 个表连接的限制，我用现实生活中的数据做到了这一点。我相信数学表明 21 个整数类型属性将超出限制，而且这是在我开始添加 varchar、文本和小数属性之前。

所以我想出的解决方案是使用子查询来克服 61 个表的限制。

一种方法是将连接分组到包含 61 个连接的子查询中。然后所有的小组都会加入。我想我可以弄清楚 sql 查询应该是什么样子，但编写代码来生成查询似乎很困难。还有一个进一步的(尽管是理论上的)问题，即如果有足够的属性，可能会再次违反 61 个表的限制。换句话说，如果我有62组61张表，就会出现MySQL错误。显然，可以通过将组分组为 61 来解决这个问题。但这只会使代码更加难以编写和理解。

我认为我想要的解决方案是将子查询嵌套在子查询中，这样每个子查询都使用 2 个表(或一个表和一个子查询)的单个联接。直观上，似乎为这种查询编写代码会更容易。不幸的是，思考这些查询应该是什么样子让我的大脑受伤。这就是我需要帮助的原因。

这样的 MySQL 查询会是什么样子？

Answer 1

您是对的，通过 EAV 设计连接太多属性可能会超出连接限制。即使在此之前，连接也可能存在实际限制，因为如此多的连接成本呈几何级数越来越高。这有多糟糕取决于您服务器的容量，但它可能会比 61 低很多。

因此，查询 EAV 数据模型以生成结果，就好像它存储在传统关系模型中(每个属性一列)是有问题的。

解决方案:不要使用每个属性的联接来执行此操作，这意味着您不能期望纯粹使用 SQL 以传统的每个实体行格式生成结果。

我不太熟悉 Magento 架构，但我可以从您的查询中推断出这样的事情可能有效:

SELECT cpe.entity_id
, o.value AS option
, v.value AS option_value
FROM catalog_product_entity AS cpe
INNER JOIN catalog_product_entity_int AS i 
  ON cpe.entity_id = i.entity_id AND i.attribute_id IN (2,3,4)
INNER JOIN eav_attribute_option AS o 
  ON i.value = o.option_id AND i.attribute_id = o.attribute_id
INNER JOIN eav_attribute_option_value AS v
  ON v.option_id = o.option_id;

IN(2,3,4,...) 谓词是您指定多个属性的地方。无需添加更多联接来获取更多属性。它们只是以行而不是列的形式返回。

这意味着您必须编写应用程序代码来获取此结果集的所有行并将它们映射到单个对象的字段中。

从 @Axel 的评论来看，听起来 Magento 提供了辅助函数来消耗结果集并将其映射到对象中。