SQL - Que colunas devo indexar? (Optimização de Indexes)

Uma das dificuldades com que os programadores de bases de dados se deparam é saber escolher quais as colunas de uma tabela que devo escolher para indexar de forma a conseguir optimizações de performance quando a query é executada.

Espreitei um site que tem alguns guidelines que podem ajudar, e que aqui enumero resumidamente.

- Antes de começar a indexar tabelas, perceber os tipos de queries que serão executadas sobre elas.

Como identificar as queries mais frequentes: correr um Profiler sobre a Base de Dados
Após identificar as queries principias, como decidir quais os melhores indexes a criar: correr cada query no Query Analizer, examinando o seu Execution Plan.

Quando os indexes necessários tiverem sido identificados, então começar a indexar!

- Cada tabela deve ter, pelo menos, um clustered index.

O clustered index deve ser construido sobre uma coluna cujos valores se autoincrementam e são unicos. Regra geral, as chaves primárias são ideais para isso.

- Deve considerar-se a hipotese de indexar todas as colunas que sejam acedidas através de WHERE, ORDER BY, GROUP BY, TOP, e DISTINCT.

Sem um index, estas colunas são potenciais alvos de table scans, que afectam a performance. No entanto, há que ter em atenção diferentes queries que utilizam as mesmas colunas. Um index para uma query pode não ser o melhor index para outra query. É necessário algum bom senso.

- Indexes que podem não ser optimizados.

Há diferentes formas de indexar colunas: clustered, non-clustered, indexes compostos por várias colunas, definição do FILLFACTOR (parâmetro que define a percentagem de ocupação de cada 'página' do index), definição do PAD_INDEX (parâmetro que define quanto espaço livre é deixado em cada 'página' do index). Não há uma resposta fácil para decidir qual o melhor método, pelo que o ideal é fazer várias tentativas, e perceber qual o index mais optimizado.

- Criar apenas os indexes necessários.

Criar apenas os indexes que serão de facto utilizados nas queries de que estas tabelas serão alvo.

- Tabelas estáticas.

As tabelas que praticamente não são mexidas (por exemplo, as tabelas de metadados) podem ser alvo de mais indexes, o que não quer dizer que se indexem todas as colunas! Devem ser criados apenas os indexes necessários, porque cada index que é adicionado aumenta o tempo de execução de operações como INSERTs, UPDATES e DELETES. Estas tabelas devem ser criadas com um FILLFACTOR de 100, para garantir que não há desperdício de espaço.

- Não adicionar o mesmo index duas vezes.

Acontece, por exemplo, quando criamos uma chave primária, que automaticamente cria um index para essa coluna. Ao criar um novo index que inclui a mesma coluna, estamos a duplicar o index, já que o SQL permite ter indexes 'repetidos', desde que o nome dos indexes seja diferente.

- Eliminar indexes que não são utilizados.

Porque:
. Atrasam as operações de alteração de dados
. Causam acessos desnecessários ao disco
. Desperdiçam espaço na base de dados
. Aumentam o tempo de backup e restore de base de dados

E ainda, uma sugerida por um colega de trabalho:

- Indexar colunas com maior numero de valores diferentes.

Dentre as colunas existentes na instrução de SELECT, verificar as que apresentam maior disparidade de valores, e indexá-las.

E pronto!

Estas e outras guidelines em: http://www.sql-server-performance.com/optimizing_indexes.asp

Divirtam-se ;)