通过相互嵌套子查询来解决MySQL中61个表的JOIN限制 [英] Work around the 61 table JOIN limit in MySQL by nesting subqueries within each other
问题描述
我发现您可以通过使用子查询来绕过MySQL中61个表的连接限制. https://stackoverflow.com/a/20134402/2843690
I figured out that you can get around the 61 table join limit in MySQL by using subqueries. https://stackoverflow.com/a/20134402/2843690
我试图弄清楚如何在正在开发的程序中轻松地使用此功能，以便从Magento获取详细的产品列表(但我认为此问题的答案可能适用于很多涉及eav的情况).需要连接的表如下所示:
I'm trying to figure out how to easily use this in a program I'm working on to get a detailed product list from Magento (but I think the answer to this question could apply to a lot of situations where eav is involved). The tables that need to be joined look something like this:
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查询:
The program that I'm writing generated sql queries that looked something like this:
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，text和decimal属性之前.
It was relatively easy to write the code to generate the query, and the query was fairly easy to understand; however, it's pretty easy to hit the 61 table join limit, which I did with my real-life data. I believe the math says 21 integer-type attributes would go over the limit, and that is before I even start adding varchar, text, and decimal attributes.
所以我想出的解决方案是使用子查询来克服61个表的限制.
So the solution I came up with was to use subqueries to overcome the 61 table limit.
一种实现方法是将联接分组为61个联接的子查询.然后所有的小组都将加入.我想我可以弄清楚sql查询应该是什么样子，但是似乎很难编写代码来生成查询.还有一个(尽管是理论上的)问题，即如果有足够的属性，则可能再次违反61表限制.换句话说，如果我有62组61个表，则将出现MySQL错误.显然，可以通过将组分为61个来解决此问题.但这只会使代码更加难以编写和理解.
One way to do it is to group the joins in subqueries of 61 joins. And then all of the groups would be joined. I think I can figure out what the sql queries should look like, but it seems difficult to write the code to generate the queries. There is a further (albeit theoretical) problem in that one could again violate the 61 table limit if there were enough attributes. In other words, if I have 62 groups of 61 tables, there will be a MySQL error. Obviously, one could get around this by then grouping the groups of groups into 61. But that just makes the code even more difficult to write and understand.
我认为我想要的解决方案是将子查询嵌套在子查询中，以使每个子查询使用2个表(或1个表和1个子查询)的单个联接.直观地讲，对于这种查询来说，似乎代码更容易编写.不幸的是，思考这些查询应该是什么样子使我的大脑受伤.这就是为什么我需要帮助.
I think the solution I want is to nest subqueries within subqueries such that each subquery is using a single join of 2 tables (or one table and one subquery). Intuitively, it seems like the code would be easier to write for this kind of query. Unfortunately, thinking about what these queries should look like is making my brain hurt. That's why I need help.
这样的MySQL查询是什么样的?
What would such a MySQL query look like?
推荐答案
您是正确的，通过EAV设计连接过多的属性很可能会超出连接的限制.甚至在此之前，连接的实际限制还可能存在，因为如此多的连接的成本在几何上越来越高.这有多严重取决于服务器的容量，但可能比61低很多.
You're right that joining too many attributes through an EAV design is likely to exceed the limit of joins. Even before that, there's probably a practical limit of joins because the cost of so many joins gets higher and higher geometrically. How bad this is depends on your server's capacity, but it's likely to be quite a bit lower than 61.
因此，查询EAV数据模型以产生好像存储在常规关系模型中一样的结果(每个属性一列)是有问题的.
So querying an EAV data model to produce a result as if it were stored in a conventional relational model (one column per attribute) is problematic.
解决方案:不要使用每个属性的联接来实现，这意味着您不能指望仅使用SQL以常规的按实体行格式生成结果.
Solution: don't do it with a join per attribute, which means you can't expect to produce the result in a conventional row-per-entity format purely with SQL.
我对Magento模式并不十分熟悉，但是我可以从您的查询中推断出类似的方法可能会起作用:
I'm not intimately familiar with the Magento schema, but I can infer from your query that something like this might work:
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,...)谓词是您指定多个属性的地方.无需添加更多的联接即可获取更多的属性.它们只是作为行而不是列返回.
The IN(2,3,4,...) predicate is where you specify multiple attributes. There's no need to add more joins to get more attributes. They're simply returned as rows rather than columns.
这意味着您必须编写应用程序代码以获取此结果集的所有行并将它们映射到单个对象的字段中.
This means you have to write application code to fetch all the rows of this result set and map them into fields of a single object.
在@Axel的评论中，听起来像Magento提供了辅助功能来完成对结果集的消费，并将其映射到对象中.
From comments by @Axel, is sounds like Magento provides helper functions to do this consuming of a result set and mapping it into an object.
这篇关于通过相互嵌套子查询来解决MySQL中61个表的JOIN限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
