otimizacao part I

Texto

(1)Fábio Porto. Processamento de Consultas. Problema l . A partir do Modelo Relacional – . Banco de Dados 2013. – . l . DEXL Lab. Linguagens de consulta declarativas SQL linguagem proposta pela IBM como parte do projeto SystemR. Questão: – . Como processar de forma eficiente uma consulta SQL sobre um Banco de Dados Relacional?. Fábio Porto. Processamento de consultas. DEXL Lab. Fábio Porto. Etapas da avaliação de consulta. DEXL Lab. Consulta. Estudo de técnicas para analisar, otimizar e executar consultas de banco de dados l Presente em:. Analisador Lex/Sintático/Sem. l . – . SGBDs relacionais/ OO/XML l . linguagens declarativas: cabe ao SGBD escolher uma estratégia ótima. (representação intermediária). Etapas da avaliação. Decomposição Consulta. Regras de equivalência regras de transformação. (Plano inicial). Otimizador de Consulta. metadados. (plano físico de execução). Execução do Cód. Consulta. Resultado. 1.

(2) Fábio Porto. Análise Sintática e Semântica l . Decomposição l . Avalia se a consulta obedece às regras sintáticas da linguagem – . l . DEXL Lab. Fábio Porto. Árvore de consulta lógica l . Estrutura arborescente – – – . – . Avaliação semântica – . Transformação da consulta em uma representação lógica inicial – . não difere dos métodos aplicados a outras linguagens declarativas ou não;. l . Tabelas existem l Atributos em tabelas l Tipos de dados de atributos. l . l . Não tem ordem Nós são relações Arestas e anotações são operações sobre os nós. Mapeamento inicial segue heurísticas – – . l . Nós são operações algébricas Ordenados segundo sequencia de avaliação Nós folha são relações. Grafo de consulta l . Verifica a correção da expressão de consulta com relação ao esquema do banco de dados. DEXL Lab. Interpretação direta da semântica da consulta Regras de equivalência para transformação. Fábio Porto. Árvore Sintática. DEXL Lab. Select. Fluxo de Processamento. DEXL Lab. Analisador Lex/Sintático/Sem. Consulta. Select-list. Fábio Porto. From From-list Where condição. Árvore sintática válida. Representação processável. Geração de plano lógico. <Atributo>. <nome Rel>. <Atributo> op <valor>. 2.

(3) Fábio Porto. Geração do Plano lógico. DEXL Lab. Fábio Porto. Exemplo: Consulta 1. DEXL Lab. Seja o esquema: l . Exprime a consulta SQL através de uma árvore de operadores da álgebra relacional . – . l l . Alternativamente pode-se adotar o grafo de consulta. • Ator (nome,endereço,sexo, ano-nascimento) • Filme(título,ano,ator-principal) e as consultas: a) Select título from Ator a, Filme f where a.nome=f.ator-principal and a.ano-nascimento =1996. O plano lógico inicial não guarda restrições quanto ao desempenho. Gerado a partir de regras de mapeamento.. Fábio Porto. Exemplo:. DEXL Lab. Fábio Porto. Expressões SFW -> PL l . b) Select título from Filme f where f.ator-principal in ( select nome from ator a where a.ano-nascimento =1996). DEXL Lab. Regra básica: – . Se uma consulta é do tipo SFW (select-fromwhere), sem sub-consulta, então: l . gerar produtos cartesianos entre todas as relações presentes na lista de From.. l . Introduzir uma seleção σC acima dos produtos, onde C representa a Condição na cláusula. where.. l . Finalmente, incluir ΠL, onde L representa a lista de atributos na cláusula Select.. 3.

(4) Fábio Porto. Árvore sintática p/ consulta 1.a. DEXL Lab. Fábio Porto. Plano lógico inicial. DEXL Lab. Consulta. Π(título) Select-list. Select. From From-list Where condição. σ (nome=ator-principal AND ano-nascimento=1996) X. <Atributo>. <Nome-rel>. condição. título. and. condição. Ator. Filme. Ano-nasc = 1996. Ator Filme <nome> = . <ator-principal>. Fábio Porto. Expressões SFW -> PL l . DEXL Lab. Fábio Porto. Árvore sintática p/ consulta 1.b. Expressões com sub-consulta: – . Consulta. não correlacionada: exprimir a consulta interna segundo o procedimento de mapeamento básico. l Eliminar linhas duplicadas. l Realizar um produto cartesiano entre a relação externa e a expressão relacional interna. l Introduzir uma seleção com a condição de comparação com a sub-consulta. l Introduzir a projeção final de acordo com a lista de atributos na cláusula Select. l . Select Select-list. DEXL Lab. Select título from Filme f where f.ator-principal in ( select nome from ator a where a.ano-nascimento =1996). From From-list Where. condição. <ator-principal> in <consulta>. <Atributo>. <Nome-rel>. título Filme. Select Where Sel.-list From From-list. <Atributo> <Nome-rel> condição. nome. Ator. Ano-nasc = 1996. 4.

(5) Fábio Porto. Plano lógico inicial (1b) Π(título) σ (nome=ator-principal) X. DEXL Lab. Observe: • Folhas são relações • Arestas representam fluxo de dados • Avaliação segue sentido das folhas para raiz;. Fábio Porto. Sub-consulta correlacionada l . Consulta 2: – . c) select distinct m1.título,m1.ano from Filme m1 where m1.ano - 40 <= (. select avg(a.ano-nascimento) from filme m2, ator a where m2.ator-principal = a.nome and m1.título = m2.título and m1.ano = m2.ano). Π(nome). Filme. σ (ano-nascimento=1996) Ator. Filmes produzidos com autores de até 40 anos. Fábio Porto. Sub-consulta correlacionada l . Colunas da consulta interna são substituidas por valores da coluna correlacionada l . Seja tupla externa: (‘Missão Impossível’, 2010,’Tom Cruise’) – . Substituida na cláusula where. where m2.ator-principal = a.nome and ‘Missão Impossível’= m2.título and. Árvore de análise parcialmente transformada. DEXL Lab. Consulta interna é avaliada para cada tupla da consulta externa – . DEXL Lab. Fábio Porto. DEXL Lab. δ Π. σ de dois parâmetros. (m1.título,m1.ano). Condição Pode ser Aplicada sobre a Filme m1 junção. σ. m1.ano - 40. condição. <=. select distinct m1.título,m1.ano from Filme m1 where m1.ano - 40 <= ( select avg(ano-nascimento) from filme m2, ator a where m2.ator-principal = a.nome and m1.título = m2.título and m1.ano = m2.ano). avg(ano-nascimento). σ (m2.titulo=m1.titulo AND m1.ano=m2.ano). 2010= m2.ano l . Sub-consulta funciona como uma restrição às tuplas da consulta externa. m2.ator-principal=a.nome. Filme m2. Ator a. junção. 5.

(6) Decomposição/reformulação da consulta DEXL Lab. Fábio Porto. Gerando o Plano lógico. DEXL Lab. δ Π. Fábio Porto. – . (m1.título,m1.ano). – . Transformações baseadas em heurísticas; Simplificações: l eliminação. σ m1.ano - 40 <= n (m2.titulo=m1.titulo AND m1.ano=m2.ano). n. m2.ator-principal=a.nome. Ator a. Fábio Porto. Sacolas e Conjuntos l . DEXL Lab. Diferenças: – – – . SQL é baseada em Sacolas (i.e. admite duplicados) Propriedades comutativa/associativa influenciadas pelos dois modelos. Ex: em conjuntos, A ∩ (B ∪ C) ≡ (A ∩ B) ∪ (A ∩ C) l l . Expansão de visões. – Exprime a consulta através de uma estrutura intermediária (ex. grafo de consulta). – . Filme m1 m2.titulo,m2.ano,avg(ano-nascimento). Filme m2. de redundâncias equivalentes mais eficientes. l substituições. Regras de Transformação p/ Operações da Álg. Relacional l . 1. – – . Propagação de σ:. 2. Comutatividade da σ:. l . 3. Seleção com operadores binários: (empurrando!!!). em sacolas não !!! Exemplo: A=B=C={1}. – – . l . DEXL Lab. σ c1 ∧ σ c2 ∧ ... σ cn ∧ (R) ≡ σ c1 (σ c2 (σ c3 (...(σ cn (R))...)) σ c1 ∨ σ c2(R) ≡ σ c1 (R) ∪ σ c2 (R). l . – . Fábio Porto. σ c1 (σ c2 (R)) ≡ σ c2 (σ c1 (R)). σ c1 (B ∪ C) ≡ σ c1 (B) ∪ σ c1 (C) σ c1 (B - C) ≡ σ c1 (B) - σ c1 (C). Para os demais operadores basta empurrar sobre um dos operandos. 6.

(7) Fábio Porto. Propagação de seleções l . DEXL Lab. Permutando σ com ⊗ (ou X): se todos os atributos na condição de seleção envolverem apenas atributos de uma das relações da junção, as 2 operações podem ser permutadas σ c (R ⊗ S) ≡ ((σ c (R)) ⊗ S – . Se c for expressa como c1 e c2, onde c1 envolve atrib. de R e c2 atrib de S: σ c (R ⊗ S) ≡ (σ c1(R)) ⊗ (σ c2(S)). Fábio Porto. Projeções l l l . l l . l . 4. Propagação de Π: todos à exceção do último pode ser ignorado Π lista1(Π lista2 (... listan (R)))...)) ≡ Π lista1 (R) 5. Trocando σ com Π: se a condição de seleção c envolver apenas atributos a1, ...an na lista de projeção, as 2 operações podem ser permutadas Π a1 ... an .( σ c (R)) ≡ σ c (Π a1 ... an (R)) 6. Permutando Π com ⊗ (ou X): seja L={a1,...an,b1,...bm} onde a1,...an são atributos de R e b1,...bm atributos de S. Se a junção envolver apenas atributos de L: Π L (R ⊗c S) ≡ (Π a1,...an (R) ⊗ c Π b1,...bn (S)) – . – – . Se a condição de junção c contiver atributos adicionais (que ∉a L), estes devem ser acrescentados à lista de projeção e uma projeção final é necessária: seja an+1, ... an+k de R e bn+1, ...bn+k de S presentes na condição de junção mas não na lista L: Π L (R ⊗c S) ≡ Π L ((Π a1,...an ,, an+1, ... an+k (R)) ⊗ c ( Π b1,...bn, bn +1, ...bn+k (S))). Fábio Porto. Junções l l l l l . DEXL Lab. 7. Comutatividade de ⊗ (ou X) R ⊗c S ≡ S ⊗c R (mesmo significado) 8. Junção e X R ⊗c S ≡ σ c (R X S) 9.Comutatividade entre operações em conjuntos: ∩, ∪ ok, - não 10.Associatividade de ⊗, X, ∩, ∪ : (R θ S) θ T ≡ R θ (S θ T) 11. Outras Transformações (De Morgan): – – . C ≡ NOT (C1∧ C2) ≡ (NOT C1) ∨ (NOT C2) C ≡ NOT (C1∨ C2) ≡ (NOT C1) ∧ (NOT C2). DEXL Lab. Fábio Porto. Uso de Regras de transformação DEXL Lab l . Transformam plano lógico inicial em árvores mais eficientes (regra heurística); – . Conjunções de seleções transformadas em cascatas. – . Lista de projeções transformadas em cascatas. l l . Quanto mais cedo aplicadas menos dados processados Quanto mais cedo aplicadas menor as tuplas. – . Usando comutatividade de seleções sobre operações. – . Usando regra de associatividade de junção. l l . – – . Reduz o tamanho de relacões intermediárias Aplicar junções com tabelas menores a frente. Combinar cartesianos com seleções acima em junções Identificar sub-árvores que podem ser implementadas por um único algoritmo;. 7.

(8) Fábio Porto. Etapas de avaliação da consulta. DEXL Lab. Analisador Lex/Sintático/Sem. Fábio Porto. Otimização da Consulta l . Processo de escolha da estratégia adequada p/ processamento da consulta. Árvore sintática válida. Geração de plano lógico. – – – Transformações heurísticas. Enumeração dos planos equivalentes Algoritmo de varredura do espaço de planos possíveis Estratégia de escolha do melhor plano equivalente l l . l . Plano lógico Inicial. DEXL Lab. Modelo de custo heurístico. Geração do Plano Físico – . Substituição de operações lógicas por algoritmos. Otimizador. Fábio Porto. Regras heurísticas. Fábio Porto. Algoritmo de otimização heurística DEXL Lab. DEXL Lab. Reordena as operações na árvore de consulta. Por exemplo, aplica as operações de seleção e projeção antes de aplicar a junção ou outras operações envolvendo mais de uma relação.. l . Objetivo:. l . Passos: Usando regra propagacão de seleções, quebre as operações de Seleção com condições conjuntivas (∧) numa cascata de operações de Seleção (≠ ramos) Usando as regras (comutatividade) mova cada operação de seleção p/ baixo na árvore de consulta, tal como é permitido pelos atributos envolvidos na condição de seleção Usando a regra de associatividade, rearranje os nós de modo que as relações do nó folha com a condição de seleção mais restritiva seja executada primeiro na árvore de consulta (seleção + restritiva = menor n0 tuplas). – . 1. . 2. . 3. . Utilização das regras p/ transformar a árvore de consulta inicial numa árvore otimizada. 8.

(9) Fábio Porto. Algoritmo de otimização heurística. Um exemplo completo DEXL Lab. 4. . Combine o produto cartesiano com a operação subsequente de Seleção, cuja condição represente uma condição de junção numa operação de Junção. 5. . Usando as regras (propagação de projeção e comutatividade c/ outras operações), quebre e mova listas de atributos da projeção para baixo na árvore, tão longe quanto possível, criando novas operações de projeção quando necessário Identifique subárvores que representem grupos de operações que sejam executadas por um simples algoritmo. 6. . Fábio Porto. DEXL Lab. Emp (id,nome,data_nasc,sal,ndep) Trab (idemp,nrproj,nhoras) Proj (nrproj,nomep,coddept). l . Empregados que trabalham no projeto Teste e têm mais de 40 anos. Select nome From Emp, Trab, Proj Where nomep = ‘Teste’ and Proj.nrproj= Trab.nrproj and Emp.id= Trab.idemp and year(data_nasc) <= current_year-40. Fábio Porto. Árvore de análise. DEXL Lab. Fábio Porto. Propagando as seleções Π nome. Π nome (1). (2). σ. Proj.nrproj= Trab.nrproj. nomep = ‘Teste’ and Proj.nrproj= Trab.nrproj and Emp.id= Trab.idemp and. X. DEXL Lab. X. year(data_nasc) <= currentDate-40. σ Emp.id=Trab.idemp X. σ nomep =‘teste’. PROJ. TRAB EMP. Operações algébricas são binárias ou unárias. X σ data_nasc<=1968 EMP. PROJ TRAB. 9.

(10) Fábio Porto. Fábio Porto. Associatividade entre junções. Transformação de PC em junção. DEXL Lab. σ Emp.id=Trab.idemp. (3). DEXL Lab. Π nome. Π nome. σ Emp.id=Trab.idemp ⊗. (4). X σ Proj.nrproj= Trab.nrproj. ⊗. σ data_nasc<=1968. Proj.nrproj= Trab.nrproj. σ data_nasc<=1968. X Proj é a menor relação. σ nomep =‘teste’. σ nomep =‘teste’. EMP TRAB. EMP TRAB. PROJ. PROJ. Fábio Porto. Propagação de projeções. DEXL Lab. Π nome Trab.id=Emp.id. (5). Π Trab.id. ⊗. Proj.n0proj= Trab.n0proj. ⊗ σ data_nasc<1968. Π n0proj. σ nomep =‘teste’. EMP TRAB. PROJ. 10.

(11)