主页 / dba / 问题 / 173682
Accepted
Jeffrey
Asked: 2017-05-17 02:23:08 +0800 CST

递归查询父子速度

  • 772

在表中查找树结构（父子结构）的递归查询很常见，但大多数问题是 childID 必须高于 parentID。

一个可能的解决方案是使用类似的查询（给定父母 X，找到所有孩子）

SELECT * FROM (
    SELECT @pv:=(
        SELECT GROUP_CONCAT( child_id SEPARATOR "," ) FROM link_parent_child WHERE FIND_IN_SET( parent_id, @pv )
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$parentStartID$) tmp
    ) a
WHERE lv IS NOT NULL

或给定孩子 X，找到所有父母

SELECT * FROM (
    SELECT @pv:=(
        SELECT parent_id FROM link_parent_child WHERE child_id = @pv
        ) AS lv FROM link_parent_child
        JOIN
        (SELECT @pv:=$ChildStartID$) tmp
    ) a
WHERE lv IS NOT NULL

但是，对于超过 50 万条记录的表，这有点慢......（轻描淡写）

CREATE TABLE `link_parent_child` (
    `parent_id` int(11) NOT NULL,
    `child_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
ALTER TABLE `link_parent_child`
    ADD PRIMARY KEY (`child_id`,`parent_id`);
    ADD KEY `child_id` (`child_id`),
    ADD KEY `parent_id` (`parent_id`),
    ADD KEY `parent_id_2` (`parent_id`,`child_id`);

即使设置了索引，并且只有 10k 条记录，查询也需要 80 多秒才能运行。

所以，这个查询（如果我错了，请纠正我）非常慢，因为内部几乎所有行都返回 NULL，（我猜）这些行存储在临时表中并被查询的外部过滤掉。我猜想如果早先过滤掉NULL，查询会加快速度，但这可能吗？

我的树有一个动态长度，但大多不超过 3-10 层。我应该承担损失并以编程方式解决这个问题，还是有办法加快速度？

mysql recursive

1 个回答

  1. Best Answer

    好吧，在 Rick James 的评论的帮助下，我设法找到了答案。我安装了 MariaDB 10.2.5（需要 10.2.2 或更高版本）并通过将 Data 目录中的文件复制到 MariaDB Data 目录并在 MariaDB 数据库上运行 mysql_update 来导入所有 MySQL 数据文件。

    WITH RECURSIVE tree (parent_id) AS (
    SELECT parent_id FROM link_parent_child WHERE child_id = $ChildStartID$

    UNION ALL

    SELECT c.parent_id FROM link_parent_child lpc
    JOIN tree t ON t.parent_id = lpc.child_id
)
SELECT parent_id FROM tree

    真的很简单：|

    什么是 CTE

    • 1

相关问题