Armazenamento de dados, indexação, processamento de consultas e Projeto físico

Prévia do material em texto

1
Armazenamento de dados, 
indexação, processamento de 
consultas e Projeto físico
Viviane Cristina Dias
2
Processamento de Consultas
3
Introdução
Os SGBDs utilizam técnicas para processar,
otimizar e executar consultas de alto nível;
Linguagem de Consulta SQL:
Análise léxica;
Análise sintática;
A consulta também deve ser validada por meio de
verificação de que todos os atributos e nomes
relacionados são válidos;
Então uma representação interna da consulta é criada
4
Introdução
Linguagem de Consulta SQL:
Geralmente como uma estrutura de dados de árvore -
árvore de consulta;
Também é possível representar a consulta usando uma
estrutura de dados gráfica – grafo de consulta;
O SGBD, então deve planejar uma estratégia de
execução para a recuperação do resultado da
consulta a partir do arquivo no banco de dados;
5
Introdução
Em geral uma consulta possui muitas estratégias
de execução;
E o processo de escolha de uma estratégia
adequada para o processamento da consulta é
chamado Otimizado de Consulta;
O módulo Otimizador de Consulta tem a função
de produzir um plano de execução;
E o Gerador de Código gera o código que
executa aquele plano de execução.
6
Introdução
O Processador em tempo de execução do
banco de dados tem a função de executar o
código da consulta (modo interpretado ou
compilado) a fim de obter o resultado;
Se resultar em um erro em tempo de execução,
uma mensagem de erro é gerada pelo
processador em tempo de execução;
7
Passos Típicos 
durante a 
execução de uma 
consulta
Consulta em Linguagem de Alto Nível
Análise Léxica, Análise Sintática e Validação
Otimizador de Consulta
Forma Intermediária de Consulta
Plano de Execução
Gerador de Código de Consulta
Código para executar a consulta
Resultado da Consulta
Processador em Tempo de Execução do BD
Banco de Dados II 8
Etapas no 
processamento 
de Consultas
Consulta
Analisador e
Tradutor
Expressão 
Algébrica
Relacional
Otimizador
Herísticas
Estatísticas
Plano de
Consulta
SGBD
Resultado
da
Consulta
9
Introdução
O termo Otimização é, na verdade, uma
denominação imprópria, porque em alguns casos,
o plano de execução escolhido não é a melhor
estratégia;
É apenas uma estratégia razoavelmente eficiente
para a execução da consulta;
Encontrar a estratégia ótima geralmente é uma
tarefa que consome muito tempo;
Exceto para consultas mais simples, e pode
exigir informações de como os arquivos estão
implementados;
10
Introdução
As linguagens de consulta em alto nível: SQL,
OQL é mais declarativo,
pois especifica quais são os resultados
pretendidos na consulta,
Em vez de identificar os detalhes de como os
resultados devem ser obtidos;
Portanto, a otimização de consultas é
necessária para consultas que são especificadas
em uma linguagem de consulta de alto nível;
11
Otimizador de Consultas
o otimizador desempenha o papel central. Há
dois tipos de otimizações:
Otimização baseada em heurísticas. Estas
regras estão incorporadas no Oracle.
Em geral essas produzem bons resultados;
Otimização baseada em estatísticas.
Nesta opção o otimizador utiliza algumas fórmulas para
calcular o custo (tempo de processamento + tempo de
acesso aos dados de disco) de várias opções possíveis,
E escolher a opção que apresentar o menor custo
estimado;
12
Introdução
Os dados estatísticos, como número de linhas
em cada tabela e número de diferentes valores de
cada atributo são mantidos no dicionário de
dados do SGBD.
Alguns SGBDs utilizam um dos métodos, outros
permitem que o DBA escolha o método de
otimização que apresentar melhores resultados;
13
Processamento de Consultas 
SQL-Server
14
Query Analyser
Além das Grades e mensagens
A janela de consulta do SQL-Server fornece
três modos de exibição adicionais para analisar
o desempenho de consultas específicas:
O Painel do Plano de execução (Execution
Plan):
mostra a execução gráfica das tarefas que
serão executadas pelo SQL-Server durante a
consulta;
15
Query Analyser
16
Query Analyser
Ações Representadas pelo ícone;
Estatísticas importantes, tais como:
 o custo de I/O
Custo de CPU;
Número de registros envolvidos na operação;
Custo total da operação;
Banco de Dados II 17
Query Analyser
18
Query Analyser
Plano de execução da direita para a esquerda;
As setas indicam o número de registros que
passam pelo operação anterior e o tamanho
estimado de cada registro;
Banco de Dados II 19
Query Analyser
20
Melhorando o Desempenho 
do Banco de Dados
Ajuste da Instrução SQL
 É o processo de Otimização da criação de 
instruções SQL;
Para obter resultados da maneira mais eficiente 
e eficaz possível;
O ajuste as SQL tem início com a disposição 
básica dos elementos e uma consulta;
A formatação simples desempenha um 
importante papel na otimização de uma 
instrução;
22
Ajuste da Instrução SQL
O ajuste de instruções SQL consiste 
principalmente na tentativa de ajuste das 
cláusulas FROM e WHERE;
Cláusula From 
e Where
Servidor de BD Decisão: Como
Avaliar uma 
consultaAjuste Fino
23
Ajuste do BD X Ajuste da SQL
Ajuste do BD:
Processo de ajuste do BD, no que diz respeito a
memória alocada, uso do disco, a CPU e E/S;
Envolve o gerenciamento e manipulação da própria
estrutura de BD, como a criação e o layout da tabelas
de índices;
Mudanças no projeto do BD;
24
Ajuste do BD X Ajuste da SQL
Ajuste da SQL
Processo de ajuste das instruções SQL que
acessam o BD;
Essas instruções SQL incluem consultas de BD e
operações transacionais: como inserções,
atualizações e exclusões;
Formular instruções que acessem com mais eficácia
o BD em seu estado atual, tirando aproveito dos
recursos de índices do sistema e do BD;
25
Organização Correta das Tabelas na 
Cláusula From
A disposição ou organização das tabelas na
cláusula FROM podem fazer diferença,
dependendo de como o Otimizador lê a
instrução SQL;
Pode ser mais vantajoso listar as tabelas
menores primeiro e as maiores depois.
26
Organização Correta das Tabelas na 
Cláusula From
FROM Tabela1, Da Tabela menor
Tabela2 para a tabela
Tabela 3 maior
WHERE Tabela1.Coluna = Tabela3.Coluna Condição junção
And Tabela2.Coluna = Tabela3.Coluna Condição junção
[And Condição 1] Condição de Filtro{Menos restritiva}
[And Condição 2] Condição de Filtro{Mais restritiva}
27
Organização Correta das Tabelas na 
Cláusula From
Como as junções normalmente retornam um alto
percentual de linhas de dados das tabelas, as
condições de junção devem ser avaliadas após
condições mais restritivas;
28
Condições Restritivas
A condição mais restritiva é normalmente o
fator condutor na obtenção de um desempenho
ideal para uma consulta SQL.
Qual a condição mais restritiva?
Reduzir o overhead da consulta
O posicionamento da condição mais restritiva
na consulta requer conhecimento de como o
Otimizador opera.
29
Condições Restritivas
O Otimizador em alguns casos, parecem ler a
cláusula Where para cima.
Portanto, posicione a condição mais restritiva
em último lugar na cláusula Where, que é a
primeira condição lida pelo Otimizador;
30
Condições Restritivas
Tabela: EMPREGADO
Contagem de Linhas: 10.000
Condição: WHERE Last_Name = ‘Smith’
Retorna: 2.000 Linhas
Condição: WHERE City = ‘Belo Horizonte’
Retorna: 30.000 Linhas
Qual a Condição mais restritiva?
31
Condições Restritivas
Consulta 1:
Select Cout(*)
From Empregado
Where Last_Name = ‘Smith’
And City = ‘Belo Horizonte’
Consulta 2:
Select Cout(*)
From Empregado
Where City = ‘Belo Horizonte’
And Last_Name = ‘Smith’
Suponha 
Consulta 1: 
20 segundos
Suponha 
Consulta 2: 
10 segundos
O que podemos Conclui?
32
Varreduras completas na Tabela
Evitar varreduras completas em tabelas em
tabelas grandes;
Tabelas pequenas tudo bem;
33
Outras Considerações de Desempenho
Uso do operador Like e de curingas
Tente eliminar aspossibilidades
Uso do Operador OR somente quando
inevitável
Qual operador devo utilizar no lugar do OR?
Uso da cláusula HAVING somente quando
inevitável;
Execução de grandes operações de ordenação
somente quando inevitável;
34
Outras Considerações de Desempenho
Uso de Stored Procedures
Desativando índices durante o carregamento
em lote
Recurso disponível a partir do SQL-Server 2005;
Ao desabilitar um Noncluestered Index este não
poderá ser utilizado pelos usuários, porém o acesso
aos dados da tabela continuam liberado;
Ao desabilitar um Clustered Index os usuários não
terão acesso nem ao índice nem ao dados das
tabelas;
35
Outras Considerações de Desempenho
Desativando índices durante o carregamento
em lote
As definições dos índices são mantidas, porém suas
estatísticas são perdidas;
Durante um upgrade das versões anteriores do SQL-
Server para o SQL-Server 2005, automaticamente o
Setup desabilita os índices existentes
Ou o administrador poderá desabilitar os índices
existentes com comando Alter Index;
Sintaxe: Alter Index On 
 Disable
36
Outras Considerações de Desempenho
Desativando índices durante o carregamento
em lote
Desabilitar o índice:
Alter Index On 
Disable
Habilitar o índice:
Alter Index ON 
Rebuild
Ou Create Index On 
() With Drop_Existing