VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
optimizer_switch, então os seus números
serão diferentes. A forma de cada plano é a parte que se mantém.
A regra: mesclável ou não
Uma CTE é incorporada à consulta externa ou construída primeiro como uma tabela temporária, e qual das duas você obtém é decidido pelo que a CTE contém, não por quantas vezes você a nomeia. Uma CTE cujo corpo é umSELECT simples com WHERE e JOIN é mesclada e
desaparece do plano por completo:
GROUP BY, DISTINCT, LIMIT, UNION ou uma função de janela não pode ser
mesclada, porque as condições da consulta externa não são equivalentes quando
aplicadas antes dessas operações. Uma CTE assim é materializada.
Os planos abaixo mostram esse segundo caso. Leia-os como sendo sobre o conteúdo
da CTE, não sobre a palavra WITH.
Verifique qual dos dois casos você obteve
EXPLAIN FORMAT=TREE diz isso com todas as letras. O EXPLAIN padrão não diz:
a coluna Extra dele não traz a palavra nas consultas desta página.
Materialize CTE counts, embora a CTE seja nomeada uma única vez. O GROUP BY
dentro dela é o que força isso, e o servidor grava as linhas em uma tabela
temporária e as lê de volta.
Uma tabela derivada que contém o mesmo GROUP BY recebe o mesmo plano.
Escrita como FROM (SELECT rating, COUNT(*) ... GROUP BY rating) AS counts, a
árvore é idêntica exceto pelo nome do nó, e o custo é o mesmo. Então, em uma
consulta com uma única referência, a CTE não supera em nada a tabela derivada:
as 2 formas diferem em como são lidas, e o otimizador as trata do mesmo jeito.
Faça referência à mesma CTE duas vezes e o texto muda para if needed, com a
segunda referência reutilizando o mesmo resultado materializado:
O que a materialização custa a você
Uma CTE materializada é uma tabela temporária, o que significa 2 coisas que vale a pena saber. Ela abre mão dos índices das tabelas base. Uma vez que as linhas estão em uma tabela temporária, a consulta externa varre essa tabela. O servidor pode construir umaauto_key na hora, como fez para o join acima, e esse não é o
índice que você tinha na tabela real. Compare com o plano mesclado no topo
desta página, onde a condição externa chegou à PRIMARY.
O pushdown suaviza isso. derived_condition_pushdown está ativado por padrão e
move para dentro o que pode, e é por isso que Filter: (count(0) > 200) aparece
dentro do nó Materialize acima, e não fora dele. Ele não consegue mover
tudo, então quando um plano mostra a CTE produzindo muito mais linhas do que o
resultado mantém, escreva a restrição dentro da CTE você mesmo.
Quando uma CTE é a forma errada
- Uma única etapa curta. Uma tabela derivada a mantém onde ela é usada. Nomear uma subconsulta de 1 linha adiciona um salto para o leitor.
- Um filtro que o otimizador não empurrou para dentro. Se o plano mostra a CTE materializando todas as linhas e a consulta externa descartando a maioria delas, escreva o filtro dentro.
- Trabalho linha a linha contra um índice. Uma subconsulta correlacionada ou um join pode usar o índice em cada busca. Uma CTE materializada já abriu mão disso.
FROM para executá-la à mão é o mesmo trabalho feito
manualmente todas as vezes.
CTEs recursivas em hierarquias reais
O Sakila não tem hierarquia, então esta seção usa uma tabela própria. Crie-a ao lado dos dados de exemplo e executeDROP TABLE employees; quando terminar de
usá-la:
depth é ao mesmo tempo a resposta e a guarda.
WHERE oc.depth < 100 na consulta acima é o que evita isso. Ele transforma um
percurso sem limite em um percurso limitado, e funciona quer os dados estejam
limpos ou não.
A mensagem de erro sugere aumentar cte_max_recursion_depth, o que é correto
apenas quando a recursão realmente termina e precisa de mais rodadas do que o
padrão de 1.000. Uma série legítima de 1.500 passos atinge o mesmo erro, e
SET SESSION cte_max_recursion_depth = 5000 é a resposta correta nesse caso.
Em dados cíclicos é a resposta errada: a consulta continua sem nunca terminar,
e falha mais tarde em vez disso.
Disponibilidade e terminologia
As CTEs chegaram no MySQL 8.0 e estão presentes em todas as versões desde então, incluindo a 8.4 e a 9.x. Um servidor que respondeERROR 1064 a uma
cláusula WITH é anterior a elas; verifique com SELECT VERSION().
O padrão chama a primeira parte de uma CTE recursiva de membro âncora e a
segunda de membro recursivo. O tutorial chama a primeira de semente, que é
a mesma coisa, e as próprias mensagens do MySQL usam os termos do padrão.
CTEs funcionam em INSERT, UPDATE e DELETE
Solução de Problemas
Veja também
- Subconsultas vs JOINs no MySQL — a outra forma com a qual esta compete
- Desempenho de GROUP BY no MySQL — a tabela temporária que uma CTE pode ajudar você a evitar
- Views no MySQL — persistir uma consulta nomeada como um objeto do esquema em vez disso
- Expressões de tabela comuns — o tutorial, se você quiser a sintaxe primeiro