Início / dba / Perguntas / 173682
Accepted
Jeffrey
Asked: 2017-05-17 02:23:08 +0800 CST

Velocidade de consulta recursiva pai filho

  • 772

Consultas recursivas para encontrar a estrutura de árvore (pai-filho) em uma tabela são comumente encontradas aqui, no entanto, a maioria tem o problema de que o childID deve ser maior que o parentID.

Uma possível solução para isso é usar uma consulta como (dado o pai X, encontre todos os filhos)

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

OU Dado o filho X, encontre todos os pais

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

No entanto, com uma tabela de mais de 500 mil registros, isso é um pouco lento... (eufemismo)

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`);

mesmo com índices definidos e apenas 10 mil registros, a consulta demora mais de 80 segundos para ser executada.

Portanto, esta consulta (corrija-me se estiver errado) é tão lenta porque a parte interna retorna NULL para quase todas as linhas, que (eu acho) são armazenadas em uma tabela temporária e filtradas pela parte externa da consulta. Eu acho que a consulta seria acelerada se os NULLs fossem filtrados anteriormente, mas isso é possível?

Minha árvore tem um comprimento dinâmico, mas principalmente não mais profundo do que 3-10 camadas. Devo pegar minhas perdas e resolver isso programaticamente, ou existe um método para acelerar isso?

mysql recursive

1 respostas

  1. Best Answer

    Bem, com a ajuda do comentário de Rick James, consegui encontrar a resposta. Instalei o MariaDB 10.2.5 (necessário 10.2.2 ou superior) e importei todos os arquivos de dados do MySQL apenas copiando os arquivos do diretório de dados para o diretório de dados do MariaDB e executando mysql_update no banco de dados MariaDB.

    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

    É realmente tão simples :|

    O que são CTEs

    • 1

relate perguntas