Introdução
Um dos caminhos mais importantes para obter alto desempenho numa base de dados (BD) do SQL Server são os Índices. Os índices são os objectos de BD que permitem acelerar o processo de consulta das linhas de uma tabela, de uma maneira semelhante à forma como o índice de um livro ajuda a encontrar, rapidamente, informações dentro desse livro.
Uma boa compreensão dos índices é essencial para os administradores de BD (DBA), mas também para os programadores, essencialmente pela seguinte razão: quando um pedido de dados chega ao SQL Server, este só tem duas opções possíveis para aceder às linhas solicitadas:
- Percorrer todas as linhas da(s) tabela(s) que contém os dados, examinando cada uma para ver se corresponde aos critérios de selecção;
- Ou usar um índice, se existir, para localizar rapidamente os dados solicitados.
A primeira opção está sempre disponível no SQL Server. A segunda opção só estará disponível se forem dadas instruções ao SQL Server, para criar um índice.
Como os índices têm um custo associado (ocupam espaço e devem ser mantidos sincronizados com as tabelas), não são obrigatórios no SQL Server. É possível ter uma BD sem nenhum índice. Provavelmente irá ter uma péssima performance e problemas de integridade de dados, mas o SQL Server permite criar uma BD com estas características.
Mas não é isto que nós queremos! O que queremos é uma base de dados que tenham uma boa performance, integridade de dados e, ao mesmo tempo, mantenha a sobrecarga associada aos índices, num mínimo. Neste artigo, vamos começar a tratar deste objectivo.
Conteúdo
O que é um Índice?
Índices mal concebidos e falta de índices são as fontes primárias de bloqueios nas aplicações de BD. Projectar índices eficientes é fundamental para alcançar um bom desempenho.
Os índices permitem ao SQL Server encontrar e/ou modificar dados num espaço de tempo menor, utilizando um mínimo de recursos para atingir um máximo de performance. Os índices, quando bem construídos, também permitem ao SQL Server atingir um nível de concorrência máxima, permitindo que as consultas efectuadas por um utilizador tenham pouco impacto nas consultas executadas por outros. Finalmente, os índices providenciam uma forma eficiente de fazer cumprir a integridade dos dados, garantindo a unicidade de valores chave, quando um índice único é criado.
Mas, afinal, o que é um índice?
Um índice corresponde a uma estrutura em disco, associada a uma tabela ou view, que agiliza a pesquisa de linhas nessa tabela ou view.
Um índice contém chaves, construídas a partir de uma ou mais colunas da tabela ou view, que permitem ao SQL Server encontrar a linha ou linhas associadas aos valores das chaves, de forma rápida e eficiente. Por exemplo, numa tabela com informação pessoal, podemos criar um índice para a coluna do Número Contribuinte (NIF). Se efectuarmos uma pesquisa baseada num NIF, o SQL Server primeiro vai pesquisar o valor da chave no índice e, em seguida, vai usar o índice para localizar directamente a respectiva linha de dados, na tabela. Sem o índice, teria que se percorrer todas as linhas da tabela até se encontrar o NIF pretendido, o que costuma ter um impacto significativo na degradação do desempenho.
Podem-se criar índices para a maioria das colunas de uma tabela ou view. As excepções são, principalmente as colunas configuradas com tipos de dados de grandes objectos (BLOB e CLOB), como image, text e varchar(max). Também se podem criar índices em colunas do tipo XML, mas esses índices são um pouco diferentes e estão fora do âmbito deste artigo.
Estrutura de um Índice
Um índice é composto por um conjunto de páginas (nós) que são organizadas numa estrutura de Árvore-B. Esta estrutura é de natureza hierárquica, com o nó da raiz na parte superior da hierarquia e os nós de folha na parte inferior.
Quando é feita uma consulta sobre uma coluna indexada, o SQL Server começa no nó de raiz e navega, para baixo, através dos nós intermédios, sendo cada camada mais granular do que a anterior. O mecanismo de consulta continua para baixo, através dos nós de índice, até atingir o nível da folha. Por exemplo, se pesquisarmos o valor 123, numa coluna indexada, o mecanismo de consulta começa no nível da raiz e determina qual a página de referência no primeiro nível intermédio. Neste exemplo, a primeira página aponta para os valores 1-100 e a segunda página, para os valores 101-200, de modo que o mecanismo de consulta irá para a segunda página. O mecanismo continua e determina que deve ir para a terceira página no nível intermédio seguinte. Finalmente, o mecanismo de consulta navega até ao nó de folha que corresponde ao valor 123 (percurso a vermelho na imagem). O nó de folha contém toda a linha de dados ou apenas um ponteiro para essa linha, dependendo se o índice é clustered ou nonclustered.
Índices Clustered e Nonclustered
No SQL Server, existem dois tipos principais de índices: o Clustered e Nonclustered.
Clustered IndexUm índice Clustered armazena as linhas de dados de uma tabela ou view já ordenadas, com base na chave do índice. Com um índice clustered, os dados são armazenados na própria estrutura do índice. Como fisicamente as linhas da tabela só podem ter uma ordem, apenas pode existir um índice clustered por tabela.
A única ocasião em que as linhas duma tabela são armazenadas de forma ordenada é quando a tabela tem um índice clustered. Neste caso temos uma tabela clustered (clustered table). Quando a tabela não é clustered, as linhas de dados são armazenadas numa estrutura desordenada chamada heap.
Nonclustered Index
Um índice noncluster é criado em separado da tabela. Fisicamente, as linhas dos dados são armazenadas na estrutura da tabela (que pode ser clustered ou heap) e as linhas do índice são armazenadas numa estrutura de árvore-B separada.
Cada linha do índice contém o valor da chave e um localizador de linha. Este localizador aponta para a linha de dados da tabela, correspondente ao valor da chave do índice nonclusterd. Isto significa que o mecanismo de consulta necessita executar um passo adicional, de modo a obter realmente os dados.
As linhas do índice são armazenados na ordem dos valores da chave, mas as linhas de dados podem não ter qualquer ordem em particular, a menos que seja uma tabela clustered. Mesmo assim, é normal que a ordem dos dados na tabela não seja a mesma da ordem das chaves do índice nonclustered.
A estrutura do localizador de linha depende se ele aponta para uma tabela em cluster ou em heap. Se referenciar uma tabela clustered, o localizador de linha aponta para o valor da chave do índice clustered. Se referenciar uma tabela em heap, o localizador aponta directamente para a linha de dados.
Tipos de Índices
Para além de serem clustered e nonclustered, os índices podem ter características e configurações adicionais:
Índice Composto
Índice Único (Unique)
Um índice que garante a singularidade de cada valor da chave indexada.
Se a chave do índice for simples (apenas uma coluna), então garante-se que o valor dessa coluna não se repete em nenhuma das linhas da tabela. Se a chave for composta, os valores de cada coluna individual podem-se repetir, mas cada combinação de valores das colunas da chave já não. Por exemplo, numa tabela que regista dados sobre um jogo de futebol, temos uma chave composta por duas colunas, uma com o tempo de jogo em minutos (0-90) e outra com o número de golos marcados (0-?). Podemos ter a seguinte sequência de dados da chave: (0,0) (5,0) (10,0) (11,1) (11,2), mas não podemos ter (0,0) (5,0) (10,0) (11,1) (11,2) (11,2).
Índice de Cobertura
Um tipo de índice que inclui todas as colunas que são necessárias para processar uma consulta particular. Por exemplo, a consulta pode devolver as colunas nome e apelido de uma tabela, com base no valor na coluna ContactId. Pode-se criar um índice de cobertura, que inclui todas as três colunas.
Limites dos Índices
Existem algumas limitações internas aos índices.
Tamanho da chave
O tamanho de uma chave de índice está limitado a um máximo de 900 bytes e 16 colunas. Este é definitivamente um limite e não um objetivo, pois quanto maior for a chave, mais páginas tem e mais profunda fica a árvore do índice. À medida que o número de páginas e a profundidade da árvore aumentam, menos eficiente se torna a utilização do índice. Índices maiores também usam mais espaço de armazenamento e podem resultar num uso menos eficiente de cache de dados do SQL Server.
Número de índices
Até ao SQL Server 2005 havia um limite de 250 índices por tabela, um cluster e 249 noncluster. A partir do SQL Server 2008, com a adição de índices filtrados, essa limitação foi aumentada para 1000, um cluster e 999 índices noncluster.
Qualquer destes limites é muito elevado e há poucos casos em que um sistema bem projectado se aproxime sequer deles. Existem duas razões para isso não acontecer:
- À medida que aumenta o número de índices também aumenta o tamanho total ocupado pela tabela (com todos os seus índices). Claro que os discos rígidos são baratos e abundam as soluções de armazenamento, mas o aumento do tamanho duma BD tem outros efeitos: as operações de manutenção (backups, restauros, verificações de consistência e reconstrução de índices) vão levar mais tempo, à medida que o tamanho da BD aumenta;
- Os índices têm que ser actualizados em sincronismo com as alterações dos dados. Quantos mais índices existirem numa tabela, mais os locais e maior o número de operações necessárias para actualizar. Se existirem 10 índices noncluster numa tabela, a inserção duma nova linha tem que ser feita em 11 locais (na tabela e em cada um desses índices).
Nas BDs que são principalmente de leitura (de suporte à decisão, ou datawarehouses) isto pode ser aceitável. Mas nas BDs em que são frequentes as operações de alteração de dados - inserir, alterar, eliminar (sistemas OLTP), a sobrecarga imposta pela existência de muitos índices pode não ser aceitável.
Como o SQL Server usa os Índices
Vamos imaginar que queremos efectuar uma consulta sobre uma tabela, com um dado critério (p.ex. todas as linhas em que uma dada coluna tenha o valor ‘X’). Se a tabela não tiver um índice baseado na coluna selecionada, a única maneira de encontrar todas as ocorrências correspondentes ao critério dado, é ler a tabela inteira. Agora, se a tabela tiver um índice, o SQL Server acelera a localização de valores dentro desse índice de duas maneiras:
- O índice é ordenado segundo as colunas da chave. Isto significa que uma vez encontrados todos os valores correspondentes ao critério, o resto da tabela pode ser ignorado. Isto é o mesmo que acontece numa lista telefónica: uma vez encontradas todas as entradas com um apelido em particular, o resto da lista pode ser ignorada, pois mais nenhuma correspondência é possível;
- A estrutura da árvore do índice permite uma abordagem do tipo dividir-e-conquistar, para a localização de linhas, onde grandes partes da tabela são rapidamente excluídas da pesquisa. Isto está ilustrado na figura da Estrutura de um Índice, anterior.
Quando temos um índice numa tabela, existem 4 operações básica que o SQL Server pode executar sobre esse índice:
Scans (Varreduras)
Uma varredura de índice (index scan) é uma leitura completa de todas as páginas de folha do índice. Quando a varredura é feita num índice clustered, na realidade é uma varredura de toda a tabela (table scan).
Quando a varredura do índice é feita pelo mecanismo de consultas, é sempre uma leitura completa de todas as páginas de folha do índice, independentemente de todas as linhas serem retornadas ou não. Nunca é uma leitura parcial.
Uma varredura não envolve apenas a leitura dos níveis de folha do índice, as páginas de nível superior também são lidas durante a operação.
Seeks (Pesquisas)
Uma pesquisa de índice (index seek) é uma operação em que o SQL Server usa a estrutura árvore-B para localizar um valor específico, ou o início de um conjunto de valores. Para uma pesquisa de índice ser possível, deve haver um predicado (filtro pesquisável) especificado na consulta e um índice correspondente (ou parcialmente correspondente) a esse predicado. Nos próximos artigos desta série iremos examinar esta questão em maior detalhe.
A operação de pesquisa é executada a partir da página de raiz. A partir das linhas desta página, o mecanismo de consultas vai localizar a página do seguinte nível inferior do índice, que contém a primeira linha que está a ser pesquisada. O mecanismo vai, então, ler essa página. Se a página estiver no nível de folha do índice, a pesquisa termina aí. Se não é o nível de folha, então o mecanismo de consulta identifica novamente a página do nível inferior seguinte, que contém o valor especificado. Este processo continua até que o nível de folha seja atingido.
Quando o mecanismo de consultas localiza a página de folha que contém o valor de chave especificado, ou o início do intervalo especificado de valores-chave, vai lendo ao longo das páginas de folha até que todas as linhas correspondentes ao predicado sejam devolvidas. A figura seguinte mostra um exemplo de como uma pesquisa seria feita, num índice, para devolver as linhas com o valor 4 na chave:
Se o índice contém todas as colunas que necessita para a consulta, nas suas páginas de folha, então temos um índice de cobertura para essa consulta. Se o índice não contém todas as colunas requeridas, então o SQL Server vai fazer uma localização (lookup) na tabela base para obter as outras colunas, a fim de processar a consulta.
Lookups (Localizações)
Os lookups ocorrem quando o SQL Server usa um índice para localizar as linhas pedidas por uma consulta, mas esse índice não contém todas as colunas necessárias para satisfazer a consulta, ou seja, o índice não faz a cobertura dessa consulta. Para obter as colunas restantes, o SQL Server faz um lookup, quer numa tabela clustered, quer numa heap.
Um lookup numa tabela clustered é sempre uma pesquisa (seek) de uma única linha do índice clustered. Então, se for necessário fazer lookup a 500 linhas, isso significa 500 pesquisas individuais ao índice clustered.
Updates (Atualizações)
Sempre que uma linha for alterada, essas alterações devem ser feitas não só na tabela base (clustered ou heap), mas também em qualquer índice que contenha as colunas que foram afetadas pela alteração. Isso aplica-se às operações de INSERT, UPDATE e DELETE.
Considerações para a criação de Índices
Por mais benéfica que a utilização de índices possa ser, estes devem ser projectados com cuidado (já vimos anteriormente alguns dos problemas relacionados com a criação excessiva de índices). Como resultado, a criação de índices deve levar em conta algumas considerações:
- O tamanho da chave dum índice clustered deve ser pequeno, porque ela vai fazer parte de todos os índices nonclustered.
O ideal é tentar implementar o índice clustered em colunas exclusivas (unique) e que não permitam valores nulos. É por isso que a Chave Primária é muitas vezes usada para o índice clustered da tabela, embora considerações sobre consultas também devam ser levadas em conta ao determinar quais colunas a usar no índice; - Os índices noncluster compostos são geralmente mais úteis que os índices simples, a não ser que todas as consultas sobre a tabela sejam filtradas com uma coluna de cada vez;
- Para índices compostos, deve levar em consideração a ordem das colunas na definição do índice. As colunas que são usadas em expressões de comparação na cláusula WHERE (como WHERE Nome = 'Luis') devem ser as primeiras da lista. As colunas subsequentes devem ser ordenadas com base na singularidade dos seus valores, com a mais exclusiva incluída primeiro;
- Os índices não devem ser maiores do que o necessário. Demasiadas colunas no índice, desperdiça espaço de armazenamento e aumenta a quantidade de locais em que os dados têm que ser alterados quando ocorre um INSERT, UPDATE ou DELETE;
- Se um índice é único, deve-se especificar que ele é único. O optimizador pode usar essa informação para gerar planos de execução mais eficientes.
A singularidade de valores duma coluna afecta o desempenho do índice. De um modo geral, quantos mais valores duplicados tiver uma coluna, pior o índice executa. Por outro lado, quanto mais exclusivo for cada valor, melhor é o desempenho. Sempre que possível, implementar índices únicos; - Nas tabelas que são modificadas frequentemente, use o mínimo de colunas possível no índice, e não crie muitos índices, uma vez que vai retardar as operações de actualização de dados;
- Se uma tabela contém uma grande quantidade de dados, mas existem poucas modificações, deve usar tantos índices quanto o necessário, para melhorar o desempenho das consultas. No entanto, deve usar criteriosamente os índices nas tabelas pequenas, porque o mecanismo de consulta pode demorar mais tempo a navegar pelo índice do que executar uma varredura (scan) da tabela.
Outras considerações para a criação de índices é a forma como a BD vai ser consultada. Conforme mencionado acima, deve levar em conta a frequência das modificações de dados. Além disso, deve considerar o seguinte:
- Tente inserir ou modificar tantas linhas quanto possível numa única instrução, em vez de usar múltiplas consultas;
- Crie índices nonclustered para as colunas usadas com frequência nos predicados das suas instruções e nas condições de JOIN;
- Considere criar índices para colunas usados em consultas de correspondência exata.
Nos próximos artigos desta série vamos analisar em mais detalhe algumas destas considerações e recomendações.
Resumo
Neste artigo, tentámos dar uma visão geral sobre os conceitos e funcionamento dos índices no SQL Server e fornecer algumas das orientações que devem ser consideradas na criação e implementação dos índices. Isto é apenas uma pequena introdução ao tema, não pretendendo ser, de forma alguma, um trabalho completo e exaustivo sobre indexação em SQL Server.
O desenho e a implementação de índices são componentes importantes de qualquer projecto de BD no SQL Server. Nós próximos artigos desta série vamos desenvolver alguns dos conceitos introduzidos e ver exemplos concretos da utilização e manipulação dos índices.
Entretanto, recomenda-se a consulta dos SQL Server Books Online, para mais informações sobre os conceitos aqui descritos e considerações adicionais.
Referências
- "Introduction to Indexes", de Gail Shaw para a SQL Server Central
- "Stairway to SQL Server Indexes: Level 1, Introduction to Indexes", de David Durant para a SQL Server Central
- "SQL Server Index Basics", de Robert Sheldon para a Simple Talk
- "Indexes", na Microsoft Developer Network (MSDN)
Sem comentários:
Enviar um comentário