Buscar

Índices e Otimização de banco de dados

Prévia do material em texto

UNIVERSIDADE FEEVALE 
 
 
 
 
 
 
 
 
 
 
 
 
 
Índices e Otimização – SQL Server 
 
 
 
 
 
 
 
 
Ânderson Dionízio Bervanger - 0118714 
 
 
 
 
 
Novo Hamburgo, Junho de 2017 
Os índices em um sistema de gestão de banco de dados são utilizados para que 
a busca de informações em uma tabela seja facilitada, realizando o menor 
número possível de operações de leituras, fazendo com que a busca seja rápida 
e eficiente. 
É possível exemplificar a utilização dos índices ao fazer uma comparação da 
tabela de banco de dados com uma lista telefônica. A lista telefônica tem um 
índice por ordem alfabética do nome das pessoas que estão lá. Em posse da 
inicial do nome é possível iniciar a pesquisa pela página correspondente a inicial 
do nome. 
O SQL Server utiliza o mesmo princípio da Lista telefônica gravando as 
informações dos índices na estrutura B-Tree. 
A B-Tree ou no português, Árvore B, é uma árvore projetada para trabalhar com 
dispositivos de armazenamento secundário como discos magnéticos. 
Visa otimizar as operações de I/O nos dispositivos. O tempo de acesso às 
informações em um disco é prejudicado pelo tempo de posicionamento do braço 
de leitura. Quando o braço está posicionado no local correto, a leitura pode ser 
feita de forma rápida, minimizando assim o número de acessos ao disco. 
A estrutura B-Tree possuí um nó-raiz contendo uma única página de dados, uma 
ou mais páginas de níveis intermediários e uma ou mais páginas de níveis folha. 
 
Figura 1: B-Tree 
 
A B-tree possuí o mesmo número de páginas à esquerda e a direita de cada 
nível. 
 
 
Figura 2: Exemplo de índice para um campo de código do tipo Inteiro. 
 
Para criar o nível raiz e o nível intermediário, se pega o primeiro valor de cada 
página do nível abaixo juntamente com o ponteiro da página de onde veio o valor 
de dados. Toda a instrução de inserção, exclusão e alteração modifica a 
estrutura dos índices. Caso uma página utilizada pelo índice estar cheia, ocorre 
o processo de Page Splitting, que divide a página para comportar uma estrutura 
com mais páginas. 
A busca pelo índice começa no nível raiz percorrendo as linhas até achar a 
cadeia de valores a qual o mesmo se encaixa e através do ponteiro pular para a 
página do nível intermediário referido. O processo é repetido no nível 
intermediário, até achar a cadeia de valores e então pular para a página de nível 
folha. No nível folha repete-se o processo até o valor desejado ser encontrado e 
nesse momento é localizado os dados necessários. 
No SQL Server é possível criar índices clusterizados (clustered), não 
clusterizados, XML e especiais. 
O índice clusterizado determina a ordem em que as linhas de uma tabela são 
armazenadas no disco. No INSERT de uma tabela com o índice clusterizado as 
linhas da tabela serão armazenadas em disco na ordem exata do mesmo índice. 
Exemplificando, suponha uma tabela chamada “EQUIPAMENTO” onde há uma 
coluna chave primária com o nome “ID_Equipamento” e que foi criado um índice 
clusterizado para essa coluna. Com o índice clusterizado, todas as linhas dentro 
da tabela EQUIPAMENTO estarão fisicamente ordenadas através dos valores 
que estão na coluna ID_Equipamento. Implicando em um ganho na performance 
das pesquisas, visto que as colunas da tabela estarão ordenadas na mesma 
ordem dos índices clusterizados por intermédio do modelo de armazenamento 
utilizado. 
Os índices não-clusterizados não fazem a ordenação dos dados da forma que é 
feito com os índices clusterizados. Os índices clusterizados ordenam fisicamente 
as linhas da tabela e os índices mantendo-os próximos. Os não-clusterizados 
ordenam somente o índice, não ordenando as linhas, que são salvas 
aleatoriamente no disco. 
Como vantagem, os índices clusterizados tem maior performance nas 
pesquisas, são mais rápidas que os índices não-clusterizados. Isso acontece, 
pois, as informações dos índices e sua coluna respectiva estarem ordenadas e 
próximas na memória física do banco. 
A desvantagem dos índices clusterizados é o custo para as novas escritas em 
disco. Exemplificando, se uma linha tiver o seu valor atualizado em uma coluna 
de índice clusterizado, o banco de dados terá que mover a linha inteira para que 
a tabela continue a ser ordenada na mesma ordem da coluna de índice 
clusterizado. 
Índices XML podem ser criados em colunas de tipos de dado XML. Os índices 
indexam todas as marcas, valores e caminhos através das instâncias XML na 
coluna e se beneficiam do desempenho das consultas. Existem dois tipos de 
índices XML: 
Índice XML Primário: 
As colunas XML são armazenadas como objetos binários no SGBD, assim, as 
buscas em coluna desse tipo podem se tornar lentas devido ao grande volume 
de informação. O índice XML primário é usado para acelerar a busca de 
informações. 
Para criar um índice XML, a tabela que contém a coluna XML deve ter um índice 
clusterizado na chave primária da tabela. 
Um índice XML primário é uma representação fragmentada e persistente dos 
BLOBs XML na coluna de tipo de dados XML. Para cada BLOB XML na coluna, 
o índice cria várias linhas de dados. O número de linhas no índice é 
aproximadamente igual ao número de nós no BLOB XML. Cada linha armazena 
as seguintes informações de nó: 
 Nome da marca, como um nome de atributo ou elemento; 
 Valor do nó; 
 Tipo de nó, como um nó de elemento, nó de atributo ou nó de texto; 
 Informações de ordem do documento representada por um identificador 
de nó interno; 
 Caminho de cada nó para a raiz da árvore XML. 
 Chave primária da tabela base. 
As informações de nó são utilizadas para avaliar e construir resultados XML para 
uma consulta especificada. Na otimização, as informações de nome da marca e 
de tipo de nó são codificadas como valores inteiros e a coluna Path usa a mesma 
codificação. 
Índice XML Secundário: 
Feita a criação de um índice primário para uma coluna XML, é possível criar mais 
três índices secundários para a mesma coluna. Os índices secundários são 
utilizados para ajudar determinados tipos de consulta XML. 
 Índice XML secundário PATH 
o Quando as consultas normalmente especificarem expressões de 
caminho em colunas XML, o índice PATH poderá acelerar a 
pesquisa. O índice primário é útil quando existem consultas 
especificando o método exist() na cláusula WHERE. Adicionar o 
índice secundário PATH poderá também melhorar o desempenho 
da pesquisa nessas consultas. 
 
 
 Índice XML secundário VALUE 
o Quando as consultas forem baseadas em valor e o caminho não 
for especificado completamente ou se incluir um caractere curinga, 
é possível obter resultados mais rápidos construindo um índice 
XML secundário construído sobre valores de nós no índice XML 
primário. 
 
 Índice XML secundário PROPERTY 
o As consultas que recuperam um ou mais valores de instancias XML 
individuais podem se beneficiar de um índice property. É utilizado 
quando se tenta recuperar propriedades de objetos usando o 
método value() do tipo XML e quando o valor da chave primária do 
objeto é conhecido. 
 
Otimizador SQL Server 
Com o estudo do desempenho e otimização de consultas do SQL Server, é 
buscado a minimização do tempo de resposta do servidor de banco de dados. 
No que se trata do aprimoramento de desempenho de consultas, é notável que 
mesmo alterando ajustes de configuração ou com a inclusão de um hardware 
mais potente, as mudanças com relação ao aplicativo frequentemente surtem 
maiores efeitos de desempenho junto às consultas. Um SGBD pode ser rápido 
e eficiente com aplicativos bem planejados e implementados. Ao mesmotempo, 
num sistema onde os aplicativos estão mal planejados ou implementados de 
forma deficiente, o SGBD terá performance abaixo do esperado. 
O conjunto de hardware e software apropriados, SGBD normalizado e a forma 
que as consultas SQL são definidas, são itens os quais colaboram para a 
melhora do desempenho de um banco de dados. 
No SQL Server, existe a presença de um otimizador. Ele tem o objetivo de 
determinar um modo eficiente de implementar a requisição feita através de uma 
consulta SQL ao SGBD. 
O otimizador do SQL Server é um otimizador que se baseia no CUSTO. Cada 
plano de execução tem um custo associado em termos de quantidade de 
recursos de computação usados. O otimizador tem como função analisar os 
possíveis planos e escolher o de menor custo estimado. 
O otimizador de consultas não escolhe apenas o plano de execução com menor 
custo de recurso, ele escolhe também o plano que retorna resultados ao usuário 
com custo razoável em recursos e que retorna os resultados rapidamente. 
Além disso, o otimizador depende de estatísticas de distribuição ao estimar os 
custos de recursos de métodos diferentes para extração de informações de uma 
tabela ou índice. Se a estatística estiver ausente ou desatualizada, o otimizador 
de consultas não terá informações vitais necessárias ao processo de otimização 
da consulta e, assim, suas estimativas poderão estar erradas. Em casos assim, 
o otimizador escolhe um plano aquém do ideal, superestimando ou 
subestimando custos de execução de diferentes planos. 
Para cada tabela envolvida na consulta SQL, o otimizador do SQL Server avalia 
os argumentos de pesquisa e avalia até que ponto o índice pode “excluir” linhas 
de uma seleção. 
Durante a consulta existem três fases que o otimizador realiza: 
 Análise de consulta 
o O otimizador examina a cláusula da consulta e determina se ela 
pode ou não ser útil para limitar os dados que devam ser 
percorridos, ou seja, se a cláusula é útil como argumento de 
pesquisa (SARG) ou como critério de união. Uma SARG pode fazer 
uso de índice para recuperação mais rápida. Ela limita a pesquisa 
pois especifica uma correspondência exata, intervalo de valores ou 
junção de um ou mais itens definidos pela cláusula AND. 
 Seleção do índice 
o É determinado se existe um índice para cada cláusula SARG, o 
otimizador avalia a utilidade do índice determinando a seletividade 
da cláusula e estimando o custo para encontrar as linhas. A 
seletividade é expressa através da formula: 
 Seletividade = registros qualificados/total de registros 
o Caso a relação entre o número de registros qualificados e o total 
de registro for baixa, isso implica que o índice é altamente útil, caso 
contrário, o índice tem pouca seletividade e não é útil. O índice será 
útil quando a relação é de 5% ou menos. 
 Seleção da união 
o Se a consulta envolve várias tabelas ou é uma auto-união, o 
otimizador de consultas avalia a seleção da união e define a 
estratégia de menor custo. 
 União de loop aninhado: São processadas um conjunto de 
loops que extraem a primeira linha da primeira tabela e 
usam essa linha para percorrer a tabela mais interna e assim 
por diante, até que o resultado que satisfaça seja utilizado 
para percorrer a última tabela. 
 União de mesclagem: É utilizada quando nas duas tabelas 
a serem unidas existirem índices setorizados na coluna de 
união. 
 União por HASH: Utilizada quando não existe nenhum 
índice útil na coluna de união em nenhuma das entradas. 
Permite determinar se um item de dados em particular 
corresponde a um valor já existente, dividindo os dados 
existentes em grupos baseados em alguma propriedade. Os 
dados com o mesmo valor são colocados num hash bucket. 
Para verificar se um novo valor é correspondente ao 
existente, se examina o bucket quanto ao valor correto. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Referências bibliográficas: 
 
DevMedia, SQL Server Query: análise do plano de execução. Disponível em: 
http://www.devmedia.com.br/sql-server-query-analise-do-plano-de-
execucao/30024 
Acesso em: 14 de junho de 2017. 
 
TechNet Magazine, SQL Server: Optimizing SQL Server Query Performance. 
Disponível em: 
< https://technet.microsoft.com/pt-br/library/2007.11.sqlquery.aspx> 
Acesso em: 14 de junho de 2017. 
 
TechNet, Processamento de Instruções SQL. Disponível em: 
https://technet.microsoft.com/pt-
br/library/ms190623(v=sql.105).aspx#Otimizando instruções SELECT 
Acesso em: 15 de junho de 2017. 
 
BateByte, Otimizando consultas no ambiente SQL Server. Disponível em: 
http://www.batebyte.pr.gov.br/modules/conteudo/conteudo.php?conteudo=1184 
Acesso em: 15 de junho de 2017. 
 
MSDN, Índices XML (SQL Server). Disponível em: 
< https://msdn.microsoft.com/pt-br/library/ms191497.aspx#%C3%8Dndice> 
Acesso em: 15 de junho de 2017. 
 
Domina tudo, Índices XML- SQL Server. Disponível em: 
< http://dominatudo.blogspot.com.br/2014/03/indices-xmlsql-server.html> 
Acesso em: 16 de junho de 2017. 
 
UNICAMP, Árvore B. Disponível em: 
< http://www.ft.unicamp.br/liag/siteEd/definicao/arvore-b.php> 
Acesso em: 14 de junho de 2017. 
 
DevMedia, SQL Server Cluster: Índices clusterizados e não-clusterizados. 
Disponível em: 
< http://www.devmedia.com.br/indices-clusterizados-e-nao-clusterizados-no-sql-
server/30288> 
Acesso em: 16 de junho de 2017. 
 
DevMedia, Índices no SQL Server. Disponível em: 
< http://www.devmedia.com.br/indices-no-sql-server/18353> 
Acesso em: 15 de junho de 2017. 
 
Linha de Código, SQL Services – Índices. Disponível em: 
< http://www.linhadecodigo.com.br/artigo/619/sql-server-indices.aspx> 
Acesso em: 15 de junho de 2017.

Outros materiais

Materiais relacionados

Perguntas relacionadas

Materiais recentes

Perguntas Recentes