Bancos de Dados. 15. Processamento e O3mização de Consultas

Bancos de Dados

Introdução

•  Existe nos SGBDR um módulo conhecido como “o3mizador de consultas” •  A expressão “o3mização de consultas”, embora seja frequentemente u3lizada, é na verdade imprópria, pois em alguns casos a estratégia encontrada não é garan3damente a melhor possível (ó3ma) •  Encontrar a estratégia ó3ma consumiria muito tempo, e exigiria o uso de informação dinâmica sobre o conteúdo e organização em disco das tabelas •  Em vez de “o3mização de consultas”, deveríamos dizer “planejamento de execução de consultas”

Introdução

•  Em algumas linguagens de consulta, a estratégia está definida na maneira em que o programador implementa a consulta •  Em SQL, que é uma linguagem declara3va, apenas os resultados desejados são especificados •  Portanto, a o3mização de consultas é necessária em SGBD relacionais, baseados em SQL

Introdução

•  Passos:

–  Tradução de consultas SQL em álgebra relacional –  O3mização usando o resultado da tradução –  Estratégias de o3mização •  Heurís3cas •  Baseadas na es3ma3va de custos •  O3mização semân3ca (recente)

Ferramentas

•  Algoritmos para ordenação em memória secundária •  Algoritmos para SELECT •  Algoritmos para JOIN •  Algoritmos para PROJECT e operações de conjunto •  Alterna3vas para implementação das operações de agregação e outros 3pos de JOIN (OUTER, LEFT, RIGHT, etc.)

Ordenação Externa

•  A ordenação é um dos principais recursos para o3mização de consultas –  Usada sempre que aparece ORDER BY –  Usada para implementar algoritmos SORT-MERGE para processar junções –  Usada na eliminação de tuplas duplicadas (cláusula DISTINCT, ou em operações de conjuntos) •  A ordenação pode ser evitada se houver um índice •  Estratégia usual: SORT-MERGE –  Dividir a tabela em pedaços que caibam na memória –  Ordenar cada pedaço e salvar em disco –  Mesclar os pedaços (à la MergeSort) –  Vide E&N para a análise de complexidade

Ordenação Externa

•  O tamanho dos buffers de memória disponíveis para o SGBD é importante –  Tuning: definir um balanceamento para divisão da memória entre •  Buffers do SGBD e caches de I/O •  Espaço disponível para o S.O. (paginação, memória virtual) •  No tuning de I/O e do SGBD, o fator crí3co é a memória principal –  Se o SGBD usar memória em excesso, tornará o S.O. lento –  Se usar muito pouca memória, terá seu funcionamento prejudicado –  Servidores de BD 3picamente são configurados com muita memória, mas ter muitos dados em buffers de memória aguardando a gravação defini3va aumenta a dificuldade das operações de recuperação de falhas

Algoritmos para SELECT

•  Casos gpicos de SELECT

–  OP1: _{σCPF=‘55590333395’}(EMPREGADO)

•  Igualdade, atributo único, possível índice

–  OP2: _σNUMDEP>5(DEPARTAMENTO)

•  Não igualdade, atributo único, possível índice

–  OP3: _σNUD=5(EMPREGADO)

•  Igualdade em atributo não-chave

–  OP4: _{σ(NUD=5 AND SALARIO>3000 AND SEXO=F)}(EMPREGADO)

•  Múl3plas condições simultâneas (conjun3vas)

–  OP5: _{σCPFE=‘55590333395’ AND PNO=10}(TRABALHA_EM)

SELECT

•  Algoritmos para implementação de SELECT S1. Busca linear (força bruta) S2. Busca binária S3a. Usar um índice primário S3b. Usar uma chave de hash S4. Usar índice primário para recuperar múl3plos resultados S5. Usar índice em cluster S6. Usar um índice secundário (ex. B+-tree) S7a. Usar índice em bitmap S7b. Usar um índice funcional S8. Métodos de busca para seleção conjun3va (índice composto, interseção de índices, índices mul3dimensionais…) S9. Métodos de busca para seleção disjun3va

Algoritmos para SELECT

•  S1. Busca linear (força bruta): recuperar todos os registros no arquivo, testando se os valores atendem ou não à condição de seleção •  S2. Busca binária: executada sobre o atributo que determina a ordenação do arquivo (ex.: OP1, se CPF for a chave de ordenação do arquivo) •  S3: Uso de índice primário (ou chave de hashing): executada sobre o atributo que determina a indexação do arquivo. Recupera no máximo um registro (ex.: OP1, se CPF for a chave de indexação)

Algoritmos para SELECT

•  S4. Uso de índice primário para recuperar múl3plos registros: executado se a comparação não for de igualdade mas for sobre um atributo chave de um índice primário (ex.: NUMDEP em OP2) •  S5. Uso de índice em cluster para recuperar múl3plos registros: executado se a comparação não for de igualdade e for sobre atributo não-chave que possui índice em cluster (ex.: NUD=5 em OP3) •  S6. Uso de índice secundário (B-tree, B+-tree) sobre comparação de igualdade: usado para recuperar um único registro se o atributo de indexação for chave (valores únicos) ou para recuperar múl3plos registros se o atributo de indexação não for chave

Algoritmos para SELECT

•  S7. Seleção conjun3va usando índice individual: se um atributo envolvido em uma única condição simples em uma cláusula de junção conjun3va possuir um caminho de acesso que permita usar S2 a S6, selecionar com base nesse atributo e checar o atendimento às demais condições em cada registro recuperado •  S8. Seleção conjun3va usando índice composto: se dois ou mais atributos envolvidos em comparações de igualdade na condição de seleção conjun3va, e um índice composto exis3r para esses atributos, selecionar com base nesses atributos e checar o atendimento às demais condições em cada registro recuperado (ex.: OP5, com índice sobre CPFE e PNO)

Algoritmos para SELECT

•  S9. Seleção conjun3va pela interseção de apontadores de registros: se índices secundários ou outros caminhos de acesso es3verem disponíveis para mais de um dos atributos envolvidos na condição de seleção conjun3va, cada índice pode ser usado para recuperar um conjunto de apontadores para registros que sa3sfazem parcialmente à condição. A interseção entre esses conjuntos indica os registros que devem ser recuperados

Clodoveu Davis 16

Métodos de acesso

Ferramentas

•  Es3ma3va da sele3vidade de uma condição –  Cálculo aproximado do custo de uma operação de seleção •  Heurís3ca: baseado no 3po do atributo e no operador de comparação •  Baseado em custos: cálculo apoiado por metadados man3dos pelo SGBD –  Usado na priorização das condições, quando há várias –  Condições mais sele)vas são executadas primeiro

Clodoveu Davis 18

N_distinct: se -1, provavelmente valores únicos; se < 1, indica % de registros únicos; se > 1, indica

Ferramentas

•  Algoritmos para implementação de JOIN –  J1. Loops aninhados –  J2. Loops aninhados baseados em índice –  J3. Sort-merge join –  J4. Par33on-hash join •  Es3ma3vas do fator de seleção na junção –  Fração dos registros de uma tabela que serão combinados com registros da outra tabela

Algoritmos para JOIN

•  JOIN é uma das operações mais pesadas no processamento de consultas •  Serão analisadas aqui apenas EQUIJOINs ou NATURAL JOINs, que são a maioria dos casos (R _A=B S) •  Casos gpicos

–  OP6: EMPREGADO _DNO=NUMDEP DEPARTAMENTO –  OP7: DEPARTAMENTO _CPFGER=CPF EMPREGADO

Algoritmos para JOIN

•  J1. Junção usando loop aninhado (força bruta): para cada registro r de R, recuperar todo registro s de S e testar se os dois registros atendem a condição de junção •  J2. Junção usando loop simples: requer estrutura de acesso para recuperar os registros correspondentes na outra tabela, portanto um dos atributos da condição de junção precisa ser a chave de um índice •  J3. Junção sort-merge: se os registros de R e de S es3verem fisicamente ordenados pelos atributos A e B, realizar a junção pela varredura simultânea dos dois arquivos (este é o método mais eficiente possível)

Algoritmos para JOIN

•  J4. Hash-join: os registros de R e de S são espalhados no mesmo arquivo de hash, usando a mesma função de hash e tendo os dois atributos, A e B, como chaves –  Primeira passada: hashing dos registros do arquivo que contém menos registros (R) –  Segunda passada: varredura simples dos registros do outro arquivo (S), testando o atendimento à condição de junção em todos os registros do bucket correspondente à chave de S

Algoritmos para JOIN

•  Todos os algoritmos devem ser implementados para lidar com blocos de disco inteiros, em vez de registros individuais •  Há muita influência do tamanho do buffer na memória para executar as operações

Algoritmos para PROJECT e

operações de conjunto

•  O PROJECT é de implementação simples se a lista de atributos con3ver uma chave •  Se não con3ver, é necessário resolver a eliminação de duplicatas •  A eliminação é feita após uma ordenação, mas também pode ser resolvida usando hashing (como em J7) •  Operações de conjunto são caras, em par3cular o produto cartesiano •  A implementação é feita usando variações da técnica sort-merge, ou usando hashing

Algoritmos para Operações de

Agregação e Outer Joins

•  Os operadores de agregação podem ser implementados usando uma varredura na tabela ou o percurso de um índice, se disponível •  Se exis3r índice, o resultado de MIN e MAX pode ser ob3do diretamente •  O resultado de COUNT pode ser ob3do no índice, sem a necessidade de acessar os registros •  SUM e AVG podem ser muito simplificados se forem sobre um valor indexado

Algoritmos para Operações de

Agregação e Outer Joins

•  Quando existe cláusula GROUP BY, a tabela deve ser par3cionada em subconjuntos de tuplas •  Isso é feito usando hashing ou ordenando •  Se exis3r um índice em cluster, esse par3cionamento pode já estar pronto para uso •  Outer joins são resolvidos usando variações dos algoritmos de join, como J1 ou J2; no caso, o loop externo tem que ser sobre a tabela ligada ao lado do outer join

Tradução de Consultas SQL para AR

•  Consultas SQL são decompostas em blocos •  Cada bloco é transformado em uma expressão da AR •  Os blocos são o3mizados internamente e através de decisões sobre a ordem de execução dos blocos •  Um bloco contém um único comando SELECT-FROM-WHERE, incluindo cláusulas GROUP BY e HAVING, se houver

Tradução de Consultas SQL para AR

•  Exemplo

SELECT nome FROM empregado

WHERE salario > (SELECT MAX(salario) FROM empregado WHERE numdep = 5) SELECT MAX(salario) FROM empregado WHERE numdep = 5 SELECT nome FROM empregado

WHERE salario > c /* c = result.consulta anterior

> F

_MAX(salario)(

σ

_numdep=5 (empregado))

Tradução de Consultas SQL para AR

•  No exemplo, a subconsulta interna precisa ser executada apenas uma vez –  Subconsulta não correlacionada –  A ordem ideal é fácil de determinar •  Subconsultas correlacionadas (em que uma variável de tupla do bloco externo aparece no bloco interno) são bem mais complicadas

Heurís3cas para o3mização de consultas

•  Sequência básica de eventos: –  O parser da consulta gera uma representação interna inicial –  A o3mização é realizada de acordo com regras heurís3cas –  Um plano de execução é construído, de modo a gerar grupos de operações com base nos arquivos e índices envolvidos –  O plano é repassado ao processador de run3me para escalonamento •  Em vez das regras heurís3cas, cálculos baseados em um modelo de custos podem ser usados

Árvores e grafos de consulta

•  Árvore de consulta: corresponde a uma expressão em AR •  Relações de entrada são representadas em folhas •  Operações são representadas em nós internos •  A execução consiste em executar as operações assim que os operandos correspondentes es3verem disponíveis, e subs3tuir o nó interno com o resultado (uma relação) •  O processo termina quando o nó raiz é executado •  Exemplo: SELECT P.PNUMBER, P.DNUM, E.LNAME, E.ADDRESS, E.BDATE FROM PROJECT P, DEPARTMENT D, EMPLOYEE E WHERE P.DNUM = D.DNUMBER AND D.SSNMGR = E.SSN AND P.LOCATION=‘STAFFORD’

Árvores e grafos de consulta

•  Grafos de consulta: relações são indicadas em nós, valores constantes são indicados em nós com linhas duplas, e condições de seleção e junção são indicadas nos arcos •  Grafos de consulta não indicam a ordem de execução, ao contrário das árvores •  Existe apenas um grafo correspondente a cada consulta •  Árvores de consulta são preferidas para a o3mização, pois o processamento precisa ter sua ordem definida

O3mização heurís3ca de árvores

de consulta

•  Parte de uma árvore “canônica”, obviamente ineficiente mas fácil de compor •  No exemplo anterior, considerando: –  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 par3r da árvore canônica usam regras de equivalência entre expressões da AR para melhorar progressivamente esse resultado

Exemplo

SELECT P.Pnumber, D.Dnum, E.Lname, E.Address, E.Bdate FROM PROJECT P, DEPARTMENT D, EMPLOYEE E

WHERE P.Dnum = D.Dnumber AND D.Mgr_SSN = E.SSN

AND P.Plocation = ‘Stafford’

> Para cada projeto situado em ‘Stafford’, recuperar o número do projeto, o número do departamento que o controla, e o sobrenome, endereço e data de nascimento de seu gerente.

π

Pnumber,Dnum,Lname,Address,Bdate(((

σ

Ploca3on=‘Stafford’ (PROJECT)

_Dnum=Dnumber (DEPARTMENT)) _{Mgr_ssn=Essn} (EMPLOYEE))

Exemplo, etapa 2

Duas seleções

(PNAME e BDATE) são aplicadas antes dos produtos cartesianos, para reduzir o número de tuplas resultantes

Exemplo, etapa 3

As posições de EMPLOYEE e

PROJECT são trocadas para que a condição de seleção mais restritiva (PNAME=...) seja

Exemplo, etapa 4

Produtos cartesianos seguidos de seleção são substituídos por JOINs

Exemplo, etapa 5

Manter a cada passo apenas os atributos necessários – deslocar as operações

O3mização heurís3ca de árvores

de consulta

•  As transformações do exemplo são possíveis apenas se forem comprovadamente equivalentes, de modo a não alterar o resultado final •  Ou seja, a cada passo as árvores de consulta são equivalentes •  Existem regras gerais de transformação que garantem a equivalência

O3mização heurís3ca de árvores

de consulta

•  Regras gerais de transformação

–  R1. Cascata de seleções:

σ

<cond1 E cond2 E ... E condn>(R) =

σ

<cond1>(

σ

<cond2>(...

σ

<condn>(R))

–  R2. Comuta3vidade de seleções:

_σ

_<cond1>(

_σ

_<cond2>(R)) =

_σ

_<cond2>(

_σ

_<cond1>(R)) –  R3. Cascata de projeções:

π<lista 1>(π<lista 2) (R)) = π<lista 1>(R)

–  R4. Comuta3vidade de SELECT e PROJECT π<lista>(

σ

<cond>(R)) =

σ

<cond> (π<lista> (R))

O3mização heurís3ca de árvores

de consulta

•  Regras gerais de transformação (cont.) –  R5. Comuta3vidade de JOIN e produto cartesiano R X S = S X R, e (R JOINc S = S JOINc R) –  R6. Comuta3vidade entre SELECT e JOIN (ou X)

_σ

_<cond1>(R JOINc S) =

_σ

_<cond1>(R) JOINc S

–  R7. Comuta3vidade entre PROJECT e JOIN (ou X) π<lista>(R JOINc S) = π<listaR>(R) JOINc π<listaS>(S)

•  Se algum atributo usado em c não es3ver nas listas, acrescentar em ambas –  R8. Comuta3vidade das operações de união e interseção –  R9. Associa3vidade de JOIN, X, união e interseção

(

R

θ

S

)

θ

T

=

R

θ

(

S

θ

T

)

O3mização heurís3ca de árvores

de consulta

•  Regras gerais de transformação (cont.)

–  R10. Comuta3vidade entre SELECT e operações de conjuntos

_σ

_<cond>(R

_θ

S) =

_σ

_<cond>(R)

_θ

_σ

_<cond>(S)

–  R11. Comuta3vidade entre PROJECT e união

_π

_<lista>(R U S) =

_π

_<lista>(R) U

_π

_<lista>(S)

–  R12. Definição de junção

_σ

_<cond>(R X S) = R JOIN_cond A

O3mização heurís3ca de árvores

de consulta

•  Passos de um algoritmo de o3mização heurís3ca 1.  Usando R1, quebrar operações SELECT com condições conjun3vas em uma cascata de SELECTs 2.  Usando R2, R4, R6, R10, mover cada SELECT para baixo na árvore tanto quanto possível (dependendo dos atributos envolvidos na condição de seleção) 3.  Usando R5, R9 quanto à comuta3vidade e associa3vidade, rearranjar os nós folha de acordo com o seguinte: 3.1 Posicionar os nós folha com condições mais restri3vas para execução mais cedo (produzem menos tuplas, têm o menor tamanho absoluto, ou têm menor sele3vidade) 3.2 Garan3r que a ordenação dos nós folha não provoque a execução de um produto cartesiano (p.ex., as condições mais restri3vas não têm um JOIN em comum, e produzem mais de uma tupla cada)

O3mização heurís3ca de árvores

de consulta

•  Algoritmo (cont.) 4.  Usando R12, combinar produtos cartesianos com SELECTs subseqüentes para formar JOINs 5.  Usando R3, R4, R7 e R11, quebrar em partes e mover para baixo tanto quanto possível as operações PROJECT, mantendo apenas os atributos necessários para processamento dos passos subseqüentes 6.  Iden3ficar sub-árvores que representem grupos de operações que possam ser executadas por um único algoritmo de recuperação, possivelmente sem a necessidade de armazenamento de resultados intermediários (execução em pipeline versus execução materializada)

Sele3vidade e es3ma3vas de

custos para o3mização

•  As regras heurís3cas não são suficientes em todos os casos •  Existem situações de o3mização que podem ser melhor resolvidas se exis3r informação sobre os volumes de dados envolvidos •  Para que isso funcione corretamente, as es3ma3vas de custos têm que ser realistas e justas •  O custo do processamento das alterna3vas também não pode ser excessivo •  Es3ma3vas de custos são mais empregadas em consultas compiladas antecipadamente, enquanto para consultas intera3vas a escolha de uma alterna3va pode afetar o tempo de resposta

Sele3vidade e es3ma3vas de

custos para o3mização

•  A o3mização de consultas baseada em custos usa técnicas de o3mização propriamente ditas para encontrar a solução ó3ma em um espaço de soluções, minimizando o valor de uma função obje)vo (função de custo) •  As funções obje3vo são aproximadas, e portanto nem quando a solução ó3ma do problema de custos é encontrado podemos garan3r que se trata da solução ó3ma da estratégia para a consulta

Sele3vidade e es3ma3vas de

custos para o3mização

•  Componentes de custo –  Custo do acesso à memória secundária –  Custo de armazenamento (temporário) –  Custo computacional (busca, ordenação, cálculos, etc.) –  Custo de uso de memória principal (buffers necessários) –  Custo de comunicação (envio/recebimento da consulta e resultados) •  Para grandes BD, a ênfase é na minimização do custo de acesso à memória secundária •  Para BD de pequeno porte (cabe quase tudo na memória), a ênfase pode ser no custo computacional •  Em BD distribuídos, o custo de comunicação pode ser predominante •  Em muitos casos, considera-se apenas o custo do acesso ao disco

Sele3vidade e es3ma3vas de

custos para o3mização

•  Uso de informações do catálogo –  Número de registros (tuplas) (r) –  Tamanho médio do registro (R) –  Número de blocos de disco (b) –  Fator de bloco (número de registros por bloco) (ƒ) –  Número de níveis dos índices mul3nível (x) –  Número de valores dis3ntos de um atributo (d) –  Sele3vidade (sl): fração dos registros que sa3sfaz a uma condição de igualdade sobre o atributo (ex.: em 1000 registros, a sele3vidade de um atributo CPF é 0,001; a de um atributo sexo é 0,50) •  Atributo chave: sl = 1/r •  Atributo não chave: sl = d/r –  Cardinalidade da seleção (s): número médio de registros que sa3sfaz a uma condição de igualdade sobre o atributo (s = sl * r) •  Essas informações são aproximadas, dificilmente estarão totalmente atualizadas

Sele3vidade e es3ma3vas de

custos para o3mização

•  Exemplos de uso de funções de custo para SELECT

–  Supondo r(EMPREGADO) = r_E = 10000, b_E = 2000, bf_E = 5 –  Caminhos de acesso

•  Índice em cluster sobre o atributo SALARIO, com x_SAL = 3 níveis e cardinalidade s_SAL = 20

•  Índice secundário sobre o atributo chave CPF, com x_CPF=4 e s_CPF = 1 •  Índice secundário sobre o atributo NUD, com x_NUD=2 e b_NUD = 4

blocos no primeiro nível, sendo d_NUD=125, e portanto s_NUD=10000/125 = 80

•  Índice secundário sobre o atributo SEXO, com x_SEXO=1, sendo d_SEXO = 2, e portanto s_SEXO = 5000

Sele3vidade e es3ma3vas de

custos para o3mização

•  Exemplos (custos em número de acessos a disco) –  OP1:

_σ

_{CPF=‘55590333395’}(EMPREGADO) •  Opções: S1 (força bruta) ou S6 (B-Tree, com igualdade) –  Custo de S1 = 2000/2 = 1000 (busca seqüencial por atrib. chave) –  Custo de S6 = x_CPF + 1 = 4 + 1 = 5

–  OP2:

_σ

_NUMDEP>5(DEPARTAMENTO)

•  Opções: S1 (força bruta) ou S6 (B-Tree, com “>”)

–  Custo de S1 = 2000 (busca seqüencial por atrib. não chave)

–  Custo de S6 = x_NUD + (b_NUD / 2) + (r / 2) = 2 + 4/2 + 10000/2 = 5004 (1 bloco por nível + metade dos blocos em cada nível + metade dos registros via índice)

Sele3vidade e es3ma3vas de

custos para o3mização

•  Exemplos (custos em número de acessos a disco)

–  OP3:

_σ

_NUD=5(EMPREGADO)

•  Opções: S1 ou S6

–  Custo de S1= 2000 (busca seqüencial por atrib. não chave) –  Custo de S6 = x_NUD + s_NUD = 2 + 80 = 82

–  OP4:

_σ

_{(NUD=5 AND SALARIO>3000 AND SEXO=F)}(EMPREGADO)

•  Opções: S1 ou S6

–  Custo de S1 = 2000

–  Custo de S6 sobre NUD = 82 (cf. acima)

–  Custo de S6 sobre SALARIO = x_SAL + b_E / 2 = 3 + 2000/2 = 1003 –  Custo de S6 sobre SEXO = x_SEXO + s_SEXO = 1 + 5000 = 5001

•  Executar a seleção sobre NUD, executando a parte final da condição sobre cada registro recuperado

Sele3vidade e es3ma3vas de

custos para o3mização

•  Para que se tenha es3ma3vas minimamente precisas para operações de junção, é necessário es3mar a quan3dade de tuplas que resulta –  É em geral definido como a fração de tuplas resultantes da junção sobre a quan3dade de tuplas produzidas por um produto cartesiano –  Sele)vidade da junção (join selec3vity, js) –  js = | (R JOINc S) | / | (R X S) | = | (R JOINc S) | / |R| * |S| –  Caso especial: condição de junção é R.A = S.B •  Se A é chave de R, então js <= 1 / |R| •  Se B é chave de S, então js <= 1 / |S| •  Loops aninhados (J1) ou loops únicos (J2) devem ser escolhidos com base na tabela com menos registros •  Vide seções 15.8.4 e 15.8.6 para es3ma3vas completas de custos em exemplos

Referências

•  Capítulo 18 e 19 de Elmasri & Navathe 7ª Edição

•  Vide: seção sobre estratégias de o3mização no ORACLE e documentação do ORACLE