Otimizador de consultas
Download
Report
Transcript Otimizador de consultas
OTIMIZADOR DE
CONSULTAS
Lílian Simão Oliveira
“a otimização da consulta apresenta tanto um desafio
como uma oportunidade”, pois a otimização feita pelo
programador ou por um otimizador de banco de
dados torna-se um desafio devido ao grande número
de relacionamentos existentes em um banco e a
diversidade de estruturas de dados existentes no
SGBD.
A oportunidade acontece pelo simples fato de que
otimizar consultas é possível e viável, seja ela como for,
feita pelo programador humano, pelo DBA ou pelo
próprio otimizador do banco.
Otimização de Consulta
Uma consulta SQL quando é solicitada ao banco de
dados é tratada pelo SGBD, que ao encontrar o
melhor caminho, mostra o resultado das informações
descritas no comando.
Passos do SGBD
Otimizador de consultas
Em algumas linguagens de consulta, a estratégia de
execução é definida pela maneira como o usuário
(ou programador) expressa a consulta
Em SQL, que é uma linguagem declarativa, apenas
os resultados desejados são especificados
Portanto, a otimização de consultas é necessária
em SGBDs relacionais baseados em SQL
Otimizador de Consultas
Passos principais
Tradução
da consulta SQL para a álgebra relacional
Otimização do resultado
Estratégias de otimização
Otimização
baseada em heurísticas
Otimização baseada na estimativa de custo da
consulta
Otimização semântica
Consultas baseadas em Heurísticas
Consultas são representadas internamente na forma de uma árvore
ou grafo
Árvores de consulta são preferidas para a otimização pois
determinam a ordem de execução das operações
Grafos de consulta indicam apenas as operações e os respectivos
operandos envolvidos portanto, existe apenas um grafo correspondente
a cada consulta
Regras heurísticas são usadas para alterar a representação interna
(árvore ou grafo) de uma consulta de modo a otimizar a sua
execução
Por exemplo: operações de projeção e seleção são aplicadas antes
de uma junção
O plano de execução gerado determina a ordem em que as
operações serão executadas e os recursos a serem utilizados (por
ex., índices)
Exemplo
Consulta Q2 :
Para cada projeto localizado em ‘Stafford’, recupere o
número do projeto, o número do departamento responsável
e o último nome, o endereço e a data de nascimento do
gerente do departamento.
Consulta SQL:
SELECT P.PNUMBER, P.DNUM, E.LNAME, E.ADDRESS, E.BDATE
FROM PROJECT AS P, DEPARTMENT AS D, EMPLOYEE AS E
WHERE P.DNUM=D.DNUMBER AND D.MGRSSN=E.SSN
AND
P.PLOCATION=‘STAFFORD’;
Álgebra Relacional:
Árvore de consulta
Árvore Canônica
Exemplo 2
Exemplo2 – Etapa 1
Exemplo 2- Etapa 2
Exemplo 2- Etapa 3
Exemplo 2 – Etapa 4
Exemplo 2 – Etapa 5
Resumindo
1.
2.
3.
4.
5.
Executar operações de SELEÇÃO e PROJEÇÃO
primeiramente;
A JUNÇÃO só deve ser realizada depois da
SELEÇÃO e PROJEÇÃO;
Somente os atributos solicitados para o resultado da
consulta e os que realmente são necessários em
consultas subseqüentes é que devem ser projetados;
Evitar geração de múltiplas tabelas intermediárias;
Pesquisar as subexpressões comuns e processá-las
somente uma vez;
Otimização baseada em Custo
A otimização baseada em custos é gerada a partir
de funções estatísticas dos SGBDs. Estas funções
armazenam informações referentes ao número de
tuplas, aos índices, as chaves e a cardinalidade das
tabelas. As estatísticas da distribuição dos dados
nas colunas das tabelas e que são usadas para
estimar os custos, também consideram o uso de CPU
e I/O e o tamanho das tabelas utilizadas.
Otimização baseada em Custo
Porém, como este tipo de otimização é baseado em
estatísticas e pode gerar um resultado que talvez não
seja o melhor. Isto acontece devido ao SGBD utilizar
informações armazenadas que talvez estejam
desatualizadas. Assim, é necessário que as funções que
armazenam as informações das tuplas, índices, chaves
e cardinalidade sejam atualizadas periodicamente.
Não é viável realizar a atualização todas as vezes que
acontece alguma alteração no banco, pois isto implica
em alto custo, mas atualizando periodicamente, o SGBD
manterá valores próximos aos reais.