Baixe o app para aproveitar ainda mais
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.
Compartilhar