Processamento e Otimização de Consultas

Processamento e Otimização de
Consultas
Introdução a Banco de Dados – DCC 011
Processo de Execução de uma Consulta
Introdução a Banco de Dados – DCC 011
1
Otimização de Consultas SQL
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
Introdução a Banco de Dados – DCC 011
Otimização de Consultas SQL
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
Introdução a Banco de Dados – DCC 011
2
Tradução de Consultas SQL para
Expressões da AR
Consultas SQL são decompostas em blocos
Cada bloco é transformado em uma expressão
da álgebra relacional
Os blocos são otimizados internamente, levandose em consideração a ordem de execução entre
eles
Um bloco contém um único comando SELECTFROM-WHERE, incluindo cláusulas GROUP BY e
HAVING, se houver
Introdução a Banco de Dados – DCC 011
Exemplo de Tradução de uma
Consulta SQL
SELECT
FROM
WHERE
SELECT
FROM
WHERE
LNAME, FNAME
EMPLOYEE
SALARY > (
SELECT
FROM
WHERE
LNAME, FNAME
EMPLOYEE
SALARY > C
πLNAME, FNAME (σSALARY>C(EMPLOYEE))
MAX (SALARY)
EMPLOYEE
DNO = 5);
SELECT
FROM
WHERE
MAX (SALARY)
EMPLOYEE
DNO = 5
MAX SALARY (σDNO=5 (EMPLOYEE))
Introdução a Banco de Dados – DCC 011
3
Otimização Baseada 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)
Introdução a Banco de Dados – DCC 011
Exemplo Preliminar
Consulta Q2 (Cap. 5 e 8):
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:
πPNUMBER, DNUM, LNAME, ADDRESS, BDATE (((σ
σPLOCATION=‘STAFFORD’(PROJECT))
DNUM=DNUMBER (DEPARTMENT))
MGRSSN=SSN (EMPLOYEE))
Introdução a Banco de Dados – DCC 011
4
Árvore de Consulta
πPNUMBER, DNUM, LNAME, ADDRESS, BDATE (((σ
σPLOCATION=‘STAFFORD’(PROJECT))
(DEPARTMENT))
DNUM=DNUMBER
MGRSSN=SSN (EMPLOYEE))
Introdução a Banco de Dados – DCC 011
Árvore Canônica
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’;
Introdução a Banco de Dados – DCC 011
5
Otimização Heurística
Parte de uma árvore “canônica”, obviamente
ineficiente, mas fácil de ser construída
No exemplo anterior, considerando-se que
P tem 100 tuplas de 100 bytes
D tem 20 tuplas de 50 bytes
E tem 5000 tuplas de 150 bytes
os produtos cartesianos resultariam em 10 milhões
de tuplas de 300 bytes cada
Transformações a partir da árvore canônica usam
regras de equivalência entre expressões da álgebra
relacional para melhorar progressivamente o plano
de execução da consulta
Introdução a Banco de Dados – DCC 011
Exemplo
Consulta SQL:
SELECT LNAME
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE PNAME = ‘AQUARIUS’ AND PNUMBER=PNO AND
ESSN=SSN AND BDATE > ‘DEC-31-1957’;
Álgebra Relacional (Expressão Canônica):
πLNAME (σ
σPNAME=‘AQUARIUS’ AND PNUMBER=PNO AND ESSN=SSN AND BDATE>‘DEC-31-1957’
(EMPLOYEE × WORKS_ON × PROJECT))
Introdução a Banco de Dados – DCC 011
6
Exemplo: etapa 1
A árvore canônica
é construída
diretamente a
partir da consulta
SQL
Introdução a Banco de Dados – DCC 011
Exemplo: etapa 2
A condição de seleção é
desmembrada e as duas
operações de seleção
(sobre BDATE e PNAME)
são aplicadas antes dos
produtos cartesianos,
para reduzir o número
de tuplas resultantes
Introdução a Banco de Dados – DCC 011
7
Exemplo: etapa 3
As posições das relações
EMPLOYEE e PROJECT
são trocadas para que a
condição de seleção
mais restritiva
(PNAME=‘Aquarius’)
seja executada primeiro
Introdução a Banco de Dados – DCC 011
Exemplo: etapa 4
Produtos cartesianos
seguidos de seleção
são substituídos por
junções
Introdução a Banco de Dados – DCC 011
8
Exemplo: etapa 5
A cada passo, são
mantidas apenas os
atributos necessários
(operações de projeção
são deslocadas para
baixo)
Introdução a Banco de Dados – DCC 011
Regras de Transformação
R1. Cascata de seleções:
σ<cond1 E cond2 E ... E condn>(R) =
σ<cond1>(σ<cond2>(... σ<condn>(R))
R2. Comutatividade de seleções:
σ<cond1>(σ<cond2>(R)) = σ<cond2>(σ<cond1>(R))
R3. Cascata de projeções:
π<lista1>(π<lista2>(R)) = π<lista1>(R)
R4. Comutatividade da seleção e projeção
π<lista>(σ<cond>(R)) = σ<cond>(π<lista>(R))
Introdução a Banco de Dados – DCC 011
9
Regras de Transformação (cont.)
R5. Comutatividade da junção e do produto
cartesiano
R×S=S×R
R S=S R
R6. Comutatividade da seleção e junção ou
produto cartesiano (θ = { , ×})
σ<cond>(R θ S) = (σ<cond>(R)) θ S
R7. Comutatividade da projeção e junção ou
produto cartesiano (θ = { , ×})
π<lista>(R θ S) = (π<listaR>(R)) θ (π<listaS>(S))
R8. Comutatividade da união e da interseção
R∪S=S∪R
R∩S=S∩R
Introdução a Banco de Dados – DCC 011
Regras de Transformação (cont.)
R9. Associatividade da junção, produto cartesiano,
união e interseção (θ = { , × , ∪ , ∩})
(R θ S) θ T = R θ (S θ T)
R10. Comutatividade da seleção e das operações
de conjunto (união, interseção e diferença)
σ<cond>(R θ S) = σ<cond>(R) θ σ<cond>(S)
R11. Comutatividade da projeção e união
π<lista>(R ∪ S) = π<lista>(R) ∪ π<lista>(S)
R12. Conversão da sequência seleção/produto
cartesiano em junção
σc(R × S) = R c A
Introdução a Banco de Dados – DCC 011
10
Passos para Otimização de uma
Árvore Canônica do Tipo SPJ
1.
2.
3.
4.
5.
Usando a regra R1, desmembre a condição (conjuntiva)
da operação de seleção.
Usando as regras R2 e R6, reposicione as condições de
seleção e junção de forma que elas possam ser aplicadas
o mais cedo possível.
Usando as regras R5 e R9, reposicione as relações de
forma que condições de seleção mais restritivas possam
ser aplicadas mais cedo.
Usando a regra R12, converta as sequências de
operações de seleção e produto cartesiano em junções.
Usando as regras R3, R4 e R7, desmembre a lista de
atributos da operação de projeção de forma que
operações de projeção específicas possam ser
executadas mais cedo.
Introdução a Banco de Dados – DCC 011
Exercício
Seja o banco de dados de uma livraria representado pelo seguinte esquema relacional:
Editora(CodEditora,NomeEditora)
Livro(CodLivro,Titulo,Autor,Assunto,AnoPub,CodEditora)
Instituicao(CodInst,NomeInst,Sigla,Local)
Adotado-por(CodLivro,CodInst,AnoAdocao)
Dada a consulta SQL
SELECT NomeInst FROM Instituicao
WHERE CodInst IN
(SELECT CodInst FROM Adotado-por
WHERE AnoAdocao = ‘2007’ AND CodLivro IN
(SELECT CodLivro FROM Livro
WHERE Assunto = ‘Portugues’ AND CodEditora IN
(SELECT CodEditora FROM Editora
WHERE NomeEditora = ‘Editora Campus’)))
reescreva-a de forma não-aninhada, gere a sua árvore canônica e, usando as regras de
transformação, derive a árvore de consulta que corresponda à sequência de operações
da álgebra relacional mais eficiente para a sua execução.
Introdução a Banco de Dados – DCC 011
11