Otimização da consulta
Content
Considerações gerais
Há uma troca entre a quantidade de tempo gasto descobrindo o melhor plano de consulta e a qualidade da escolha; O otimizador pode não escolher a melhor resposta por conta própria. Diferentes qualidades dos sistemas de gerenciamento de banco de dados têm diferentes maneiras de equilibrar esses dois. O Otimizadores de consultas baseadas em custos avaliam a pegada de recursos de vários planos de consulta e usam isso como base para a seleção do plano. Eles atribuem um "custo" estimado a cada plano de consulta possível e escolha o plano com o menor custo. Os custos são usados para estimar o custo de tempo de execução da avaliação da consulta, em termos do número de operações de E/S necessárias, comprimento do caminho da CPU, quantidade de espaço de buffer de disco, tempo de serviço de armazenamento em disco e uso de interconexão entre unidades de paralelismo e outros Fatores determinados a partir do dicionário de dados. O conjunto de planos de consulta examinado é formado examinando os possíveis caminhos de acesso (por exemplo, acesso ao índice primário, acesso ao índice secundário, varredura de arquivo completa) e várias técnicas de junção da tabela relacional (por exemplo, junção de mesclagem, junção de hash, junção do produto). O espaço de pesquisa pode se tornar bastante grande, dependendo da complexidade da consulta SQL. Existem dois tipos de otimização. Eles consistem em otimização lógica - que gera uma sequência de álgebra relacional para resolver a consulta - e a otimização física - que é usada para determinar os meios para realizar cada operação.
Implementação
A maioria dos otimizadores de consultas representa os planos de consulta como uma árvore de "nós do plano". Um nó de plano encapsula uma única operação necessária para executar a consulta. Os nós são dispostos como uma árvore, na qual os resultados intermediários fluem do fundo da árvore para o topo. Cada nó possui zero ou mais nós filhos - esses são nós cuja saída é alimentada como entrada para o nó pai. Por exemplo, um nó de junção terá dois nós filhos, que representam os dois operandos de junção, enquanto um nó de classificação teria um único nó filho (a entrada a ser classificada). As folhas da árvore são nós que produzem resultados digitalizando o disco, por exemplo, executando uma varredura de índice ou uma varredura seqüencial.
Junte -se à ordem
O desempenho de um plano de consulta é determinado em grande parte pela ordem em que as tabelas são unidas. Por exemplo, ao ingressar em 3 tabelas A, B, C de linhas de tamanho 10, 10.000 linhas e 1.000.000 linhas, respectivamente, um plano de consulta que se junta a B e C primeiro pode levar várias ordens de magnitude mais tempo para executar do que uma que Junta -se a e C primeiro. A maioria dos otimizadores de consulta determina a ordem de junção por meio de um algoritmo de programação dinâmico, pioneiro no projeto do banco de dados do System R da IBM [citação necessária]. Este algoritmo funciona em duas etapas:
First, all ways to access each relation in the query are computed. Every relation in the query can be accessed via a sequential scan. If there is an index on a relation that can be used to answer a predicate in the query, an index scan can also be used. For each relation, the optimizer records the cheapest way to scan the relation, as well as the cheapest way to scan the relation that produces records in a particular sorted order.The optimizer then considers combining each pair of relations for which a join condition exists. For each pair, the optimizer will consider the available join algorithms implemented by the DBMS. It will preserve the cheapest way to join each pair of relations, in addition to the cheapest way to join each pair of relations that produces its output according to a particular sort order.Then all three-relation query plans are computed, by joining each two-relation plan produced by the previous phase with the remaining relations in the query.A ordem de classificação pode evitar uma operação de classificação redundante posteriormente no processamento da consulta. Segundo, uma ordem de classificação específica pode acelerar uma junção subsequente, pois agrupa os dados de uma maneira específica.
Planejamento de consultas para consultas SQL aninhadas
Uma consulta SQL para um DBMS relacional moderno faz mais do que apenas seleções e se junta. Em particular, as consultas SQL geralmente aninham várias camadas de blocos SPJ (seleção-projeto-joa), por meio de grupo, existe e não existe operadores. Em alguns casos, essas consultas SQL aninhadas podem ser achatadas em uma consulta de joa de projeto selecionada, mas nem sempre. Os planos de consulta para consultas SQL aninhadas também podem ser escolhidos usando o mesmo algoritmo de programação dinâmica usado para a pedidos de junção, mas isso pode levar a uma enorme escalada no tempo de otimização da consulta. Portanto, alguns sistemas de gerenciamento de banco de dados usam uma abordagem alternativa baseada em regras que usa um modelo de gráfico de consulta.
Estimativa de custo
Um dos problemas mais difíceis na otimização de consultas é estimar com precisão os custos de planos de consulta alternativos. Os otimizadores custam planos de consulta usando um modelo matemático de custos de execução de consulta que depende muito das estimativas da cardinalidade ou do número de tuplas, fluindo através de cada borda em um plano de consulta. A estimativa da cardinalidade, por sua vez, depende das estimativas do fator de seleção dos predicados na consulta. Tradicionalmente, os sistemas de banco de dados estimam as seletividades por meio de estatísticas bastante detalhadas sobre a distribuição de valores em cada coluna, como histogramas. Essa técnica funciona bem para a estimativa de seletividades de predicados individuais. No entanto, muitas consultas têm conjunções de predicados, como a contagem selecionada (*) de r onde r.make = 'honda' e r.model = 'Accord'. Os predicados de consulta geralmente são altamente correlacionados (por exemplo, modelo = 'Accord' implica make = 'honda') e é muito difícil estimar a seletividade do conjunto em geral. Estimativas de pouca cardinalidade e correlação não capturada são uma das principais razões pelas quais os otimizadores de consultas escolhem planos de consulta ruins. Essa é uma das razões pelas quais um administrador de banco de dados deve atualizar regularmente as estatísticas do banco de dados, especialmente após as principais cargas/descarregamentos de dados.
Extensões
A otimização clássica da consulta pressupõe que os planos de consulta sejam comparados de acordo com uma única métrica de custo, geralmente o tempo de execução e que o custo de cada plano de consulta pode ser calculado sem incerteza. Às vezes, ambas as suposições são violadas na prática e múltiplas extensões de otimização de consultas clássicas foram estudadas na literatura de pesquisa que superam essas limitações. Essas variantes de problemas estendidos diferem na maneira como modelam o custo dos planos de consulta única e em termos de sua meta de otimização.
Otimização da consulta paramétrica
A otimização de consulta clássica associa cada plano de consulta a um valor de custo escalar. A otimização paramétrica da consulta assume que o custo do plano de consulta depende de parâmetros cujos valores são desconhecidos no tempo de otimização. Esses parâmetros podem, por exemplo, representar a seletividade dos predicados de consulta que não são totalmente especificados no tempo de otimização, mas serão fornecidos no momento da execução. A otimização da consulta paramétrica associa, portanto, cada plano de consulta a uma função de custo que mapeia de um espaço de parâmetros multidimensionais a um espaço de custo unidimensional.
O objetivo da otimização é geralmente gerar todos os planos de consulta que podem ser ideais para qualquer uma das possíveis combinações de valor de parâmetro. Isso produz um conjunto de planos de consulta relevantes. No momento da execução, o melhor plano é selecionado para fora desse conjunto quando os verdadeiros valores de parâmetros se tornarem conhecidos. A vantagem da otimização da consulta paramétrica é que a otimização (que é em geral uma operação muito cara) é evitada no tempo de execução.
Otimização de consulta multi-objetiva
Geralmente, existem outras métricas de custo, além do tempo de execução relevantes para comparar os planos de consulta. Em um cenário de computação em nuvem, por exemplo, deve -se comparar os planos de consulta não apenas em termos de quanto tempo eles levam para executar, mas também em termos de quanto dinheiro seus custos de execução. Ou no contexto de otimização aproximada da consulta, é possível executar planos de consulta em amostras selecionadas aleatoriamente dos dados de entrada para obter resultados aproximados com a sobrecarga de execução reduzida. Nesses casos, os planos de consulta alternativos devem ser comparados em termos de tempo de execução, mas também em termos de precisão ou confiabilidade dos dados que eles geram.
A otimização de consulta multi-objetiva modela o custo de um plano de consulta como um vetor de custo, onde cada componente vetorial representa o custo de acordo com uma métrica de custo diferente. A otimização clássica da consulta pode ser considerada como um caso especial de otimização de consultas com vários objetivos, onde a dimensão do espaço de custo (ou seja, o número de componentes do vetor de custo) é um.
Diferentes métricas de custo podem entrar em conflito (por exemplo, pode haver um plano com tempo mínimo de execução e um plano diferente com taxas mínimas de execução monetária em um cenário de computação em nuvem). Portanto, o objetivo da otimização não pode ser encontrar um plano de consulta que minimize todas as métricas de custo, mas deve ser encontrar um plano de consulta que realize o melhor compromisso entre diferentes métricas de custo. O que o melhor compromisso é depende das preferências do usuário (por exemplo, alguns usuários podem preferir um plano mais barato, enquanto outros preferem um plano mais rápido em um cenário de nuvem). O objetivo da otimização é, portanto, encontrar o melhor plano de consulta com base em algumas especificações das preferências do usuário fornecidas como entrada para o otimizador (por exemplo, os usuários podem definir pesos entre diferentes métricas de custo para expressar importância relativa ou definir limites de custo rígido em certas métricas) ou para gerar uma aproximação do conjunto de planos de consulta ideais (ou seja, planos para que nenhum outro plano tenha um custo melhor de acordo com todas as métricas), de modo que o usuário possa selecionar a troca de custos preferida fora desse plano definido.
Otimização de consulta paramétrica multi-objetiva
A otimização de consulta paramétrica multi-objetiva generaliza a otimização de consultas paramétricas e multi-objetivas. Os planos são comparados de acordo com as métricas de custo múltiplas e os custos de plano podem depender de parâmetros cujos valores são desconhecidos no tempo de otimização. O custo de um plano de consulta é, portanto, modelado como uma função de um espaço de parâmetros multidimensionais para um espaço de custo multidimensional. O objetivo da otimização é gerar o conjunto de planos de consulta que podem ser ideais para cada combinação possível de valores de parâmetros e preferências do usuário.
Várias ferramentas exibem planos de execução de consulta para mostrar quais operações têm o maior custo de processamento. Microsoft SMS, ApexSQLPlan, HANA e Tableau são alguns exemplos. Corrigir esses problemas encontrados nesses planos pode raspar dezenas de tempo de execução por cento e, em alguns casos, pode cortar pesquisas bidimensionais para as lineares.
Uma das listas de verificação de otimização primária e mais simples é usar operações que a maioria dos RDMs é projetada para executar com eficiência. Veja Sargable.