我有两个表ps_product和ps_category。 product表在本地数据库中大约有146690行，在远程数据库中大约有196000行，在btree和id_product上具有id_default_category索引。类别表在btree和id_category列上有大约851行和id_parent索引。
类别是最多6个级别的层次结构，我需要所有具有其所有类别的产品。因此，如果产品53属于类别67，而类别67又是50的子类别，又是43的子类别...依此类推，一直到1，这是根类别，我将得到53-> 67-> 50-> 43-> 20-> 1-> null。
我已经弄清楚了一个mysql查询，该查询使一次自我连接到ps_category 6次以获取数据，并且在本地db上花费大约0.8秒，在网络上花费5秒钟来执行。有什么我可以优化的方法吗？查询：
SELECT
p.id_product, c.id_category, c1.id_category, c2.id_category, c3.id_category, c4.id_category, c5.id_category, c6.id_category
FROM `ps_category` c
left join ps_product p on p.id_category_default = c.id_category
left join ps_category c1 on c1.id_category = c.id_parent
left join ps_category c2 on c2.id_category = c1.id_parent
left join ps_category c3 on c3.id_category = c2.id_parent
left join ps_category c4 on c4.id_category = c3.id_parent
left join ps_category c5 on c5.id_category = c4.id_parent
left join ps_category c6 on c6.id_category = c5.id_parent
最佳答案
可以尝试的一种选择是使用闭包表。
CREATE TABLE category_closure (
`a_catagory_id` SMALLINT UNSIGNED NOT NULL,
`d_category_id` SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (`a_category_id`,`d_category_id`)
) ENGINE=InnoDB
该表记录了层次结构中各个类别级别之间的关系。
a_category_id指关系中的祖先，而d_category_id指后代。为了使表正常工作，必须将每个类别作为其祖先和后代输入到表中。
INSERT INTO category_closure
(a_category_id, d_category_id)
SELECT
id_category,
id_category
FROM ps_category
然后，您可以从每个类别的
id_parent列中输入您的已知关系。INSERT INTO category_closure
(a_category_id, d_category_id)
SELECT
id_parent,
id_category
FROM ps_category
WHERE id_parent IS NOT NULL
最后，您需要连接点。在
do..while循环中运行以下SELECT，只要从中返回了行，请将这些行插入到闭包表中并继续循环。SELECT
cc1.a_category_id,
cc2.d_category_id
FROM category_closure cc1
INNER JOIN category_closure cc2
ON cc2.a_category_id = cc1.d_category_id
LEFT OUTER JOIN category_closure missing_cc
ON missing_cc.a_category_id = cc1.a_category_id
AND missing_cc.d_category_id = cc2.d_category_id
WHERE missing_cc.a_category_id IS NULL
该查询的作用是获取所有现有关系，并查找还应该存在的关系。例如，您具有以下链：
53> 67> 50
这意味着您将从前两个INSERT中获得以下记录：
（50,50）
（67,67）
（53,53）
（50,67）
（67,53）
（和别的）。
现在，我们需要的是（50,53），因为53是50的后代。在上面的SELECT查询中，
cc1将匹配（50,67）记录。 cc2将匹配（67,53）记录。这意味着missing_cc试图与cc1（a_category_id）中的50和cc2（d_category_id）中的53匹配。由于最初不存在这样的记录，所以SELECT语句将返回这两行，您可以将其插入并重复。这次，走的更远了。不需要您（或您的程序）知道有多少层，只需继续进行操作，直到SELECT找不到更多结果为止。
最后，一旦建立了关闭表，就可以选择相关信息：
SELECT
p.id_product,
c.id_category,
GROUP_CONCAT(cc.a_category_id) AS parent_category_ids
FROM ps_category c
LEFT OUTER JOIN ps_product p
ON p.id_category_default = c.id_category
LEFT OUTER JOIN category_closure cc
ON cc.d_category_id = c.id_category
AND cc.a_category_id != c.id_category
GROUP BY c.id_category, p.id_product
这将选择所有类别，每个类别中的所有产品，然后为这些组合中的每个组合提供以逗号分隔的祖先类别列表。
现在，这实际上是很多重复的信息，因为您可以将类别及其祖先与产品分开运行，但是实际上归结为您打算如何使用此数据，例如是否可以分开查询。
注意：如果要添加和删除类别，则每次删除所有当前闭包整体后，都必须重复此过程。有比此处显示的方法更好的方法，这些方法甚至可以使您放弃使用
id_parent列（特别是如果层次结构是非周期性的），但是这些问题不在此问题的范围内。这个答案至少应该能够让您尝试一些事情，而不必更改应用程序或任何现有数据。
关于php - 优化mysql递归联接查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36100176/