我的问题是当在同一个表上使用JOIN两次时，我的查询非常慢。
我想从给定的类别中检索所有产品。但由于产品可以分为多个类别，我还希望获得（c.canonical）类别，该类别应提供url基。因此我在JOIN和categories AS c上有两个额外的categories_products AS cp2。
原始查询
SELECT p.product_id
FROM products AS p
JOIN categories_products AS cp
ON p.product_id = cp.product_id
JOIN product_variants AS pv
ON pv.product_id = p.product_id
WHERE cp.category_id = 2
AND p.status = 2
GROUP BY p.product_id
ORDER BY cp.product_sortorder ASC
LIMIT 0, 40
解释
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
|----|-------------|-------|--------|------------------------|------------------------|---------|-------------------------|------|----------------------------------------------|
| 1 | SIMPLE | cp | ref | FK_categories_products | FK_categories_products | 4 | const | 1074 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | superlove.cp.product_id | 1 | Using where |
| 1 | SIMPLE | pv | ref | FK_product_variants | FK_product_variants | 4 | superlove.p.product_id | 1 | Using where |
慢查询
SELECT p.product_id, c.category_id
FROM products AS p
JOIN categories_products AS cp
ON p.product_id = cp.product_id
JOIN categories_products AS cp2 // Extra line
ON p.product_id = cp2.product_id // Extra line
JOIN categories AS c // Extra line
ON cp2.category_id = c.category_id // Extra line
JOIN product_variants AS pv
ON pv.product_id = p.product_id
WHERE cp.category_id = 2
AND p.status = 2
AND c.canonical = 1 // Extra line
GROUP BY p.product_id
ORDER BY cp.product_sortorder ASC
LIMIT 0, 40
解释
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
|----|-------------|-------|--------|------------------------|------------------------|---------|--------------------------|------|----------------------------------------------|
| 1 | SIMPLE | c | ALL | PRIMARY | (null) | (null) | (null) | 221 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | cp2 | ref | FK_categories_products | FK_categories_products | 4 | superlove.c.category_id | 33 | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | superlove.cp2.product_id | 1 | Using where |
| 1 | SIMPLE | pv | ref | FK_product_variants | FK_product_variants | 4 | superlove.p.product_id | 1 | Using where |
| 1 | SIMPLE | cp | ref | FK_categories_products | FK_categories_products | 4 | const | 1074 | Using where |
最佳答案
mysql优化器似乎对此查询有问题。我得到的印象是，只有很少的产品属于请求的类别，但可能会有许多规范的类别。然而，优化器显然无法判断cp.category_id = 2是比c.canonical = 1强的条件，因此它用c而不是cp启动新的查询，从而导致一路上有很多多余的行。
向优化器提供数据
您的第一次尝试应该是向优化器提供所需的数据：使用ANALYZE TABLE命令，您可以收集有关密钥分布的信息。要想让它起作用，你必须有合适的钥匙。因此，也许您应该在categories.canonical上添加一个键。然后mysql就会知道（如果我理解正确的话）该列只有两个不同的值，甚至可能是每列有多少行。如果运气好的话，这说明使用c.canonical = 1作为起点是个糟糕的选择。
强制联接顺序
如果没有帮助，那么我建议您使用STRAIGHT_JOIN强制执行订单。特别是，您可能希望强制将cp作为第一个表，就像您的原始（快速）查询拥有它一样。如果这解决了问题，你可以坚持这个解决方案。如果没有，那么您应该提供一个新的EXPLAIN输出，这样我们就可以看到该方法失败的地方。
架构注意事项
还有一件事需要考虑：您的问题意味着，对于每一个产品，都有一个与之相关的规范类别。但是您的数据库模式并没有反映这一事实。您可能需要考虑修改模式以反映这一事实的方法。例如，可以在canonical_category_id表中有一个名为products的列，并且只对非规范类别使用categories_products。如果您使用这样的设置，您可能希望create a VIEW使用这样的UNION将产品连接到其所有类别，包括规范类别和非规范类别：
CREATE VIEW products_all_categories AS
SELECT product_id, canonical_category_id AS category_id
FROM products
UNION ALL
SELECT product_id, category_id
FROM categories_products
在那些不关心某个类别是否规范的地方，可以使用它而不是
categories_products。您甚至可以重命名表并将视图命名为categories_products，这样现有查询就可以像以前那样工作了。您应该在此查询中使用的products中的两列上添加索引。甚至可能有两个索引，一个用于这些列的任意顺序。不确定整个设置是否可以在您的应用程序中接受。不确定它是否真的能带来预期的速度增益。最后，除了引用
products.canonical表中的规范类别之外，还可能被迫维护冗余数据，如categories_products列。我知道从设计的角度来看，冗余数据是丑陋的，但是为了提高性能，可能有必要避免长时间的计算。至少在不支持materialized views的rdbms上。您可能可以使用触发器来保持数据的一致性，尽管我在这方面没有实际经验。