Compartilhar via

  • Artigo

Diretrizes para criação de índice geral

Administradores de banco de dados experientes podem projetar um bom conjunto de índices, mas essa tarefa é muito complexa, demorada e propensa a erros até mesmo para bancos de dados e cargas de trabalho moderadamente complexos. Compreender as características de seu banco de dados, consultas e colunas de dados pode lhe ajudar a projetar índices melhores.

Considerações sobre banco de dados

Quando você projeta um índice, considere as seguintes diretrizes para banco de dados:

  • Números grandes de índices em uma tabela afetam o desempenho das instruções INSERT, UPDATE, DELETE e MERGE porque todos os índices precisam ser ajustados adequadamente à medida que os dados são alterados em uma tabela.

    • Evite tabelas fortemente atualizadas em cima desindexações e mantenha os índices estreitos, ou seja, com o mínimo de colunas possível.

    • Use muitos índices para aperfeiçoar o desempenho da consulta em tabelas com baixos requisitos de atualização, mas com grandes volumes de dados. Grandes números de índices podem ajudar o desempenho de consultas que não modificam dados, como instruções SELECT, porque o otimizador de consulta tem mais índices para escolher para determinar o método de acesso mais rápido.

  • Indexar tabelas pequenas pode não ser bom porque pode fazer o otimizador de consulta levar mais tempo para atravessar o índice procurando dados do que executar uma simples varredura de tabela. Portanto, os índices em tabelas pequenas talvez nunca sejam usados, mas ainda devem ser mantidos como dados nas alterações de tabela.

  • Índices em exibições pode prover ganhos de desempenho significantes quando a exibição contiver agregações, junções de tabela ou uma combinação de agregações e junções. A exibição não precisa estar explicitamente referenciada na consulta para o otimizador de consulta usá-la. Para obter mais informações, consulte Projetando exibições indexadas.

  • Use o Orientador de Otimização do Mecanismo de Banco de Dados para analisar seu banco de dados e fazer recomendações de índice. Para obter mais informações, consulte Compreendendo o Orientador de Otimização do Mecanismo de Banco de Dados.

Considerações sobre consultas

Quando você projeta um índice, considere as seguintes diretrizes para consultas:

  • Crie índices não-clusterizados em todas as colunas freqüentemente usadas em predicados e condições de junção em consultas.

    Observação importanteImportante

    Evite a adição desnecessária de colunas. Acrescentar muitas colunas de índice pode afetar adversamente o espaço em disco e o desempenho de manutenção de índice.

  • Cobrindo índices pode melhorar desempenho de consulta porque todos os dados precisaram satisfazer os requisitos da consulta existe dentro do próprio índice. Ou seja, apenas as páginas de índice, e não as páginas de dados da tabela ou do índice clusterizado, são necessárias para recuperar os dados solicitados, portanto reduzindo as operações de E/S gerais do disco. Por exemplo, uma consulta de colunas a e b em uma tabela que tem um índice composto criado em colunas a, b e c pode recuperar os dados especificados somente do índice.

  • Escreva consultas que insiram ou modifiquem o máximo de filas possível em uma única instrução, em vez de usar consultas múltiplas para atualizar essas mesmas filas. Ao usar apenas uma instrução, pode-se explorar uma manutenção otimizada do índice.

  • Avalie o tipo da consulta e como as colunas são usadas na consulta. Por exemplo, uma coluna usada em uma consulta de correspondência exata seria uma boa candidata para um índice clusterizado ou não-clusterizado. Para obter mais informações, consulte Tipos de consulta e índices.

Considerações sobre colunas

Quando você projeta um índice, considere as seguintes diretrizes para as colunas:

  • Mantenha o comprimento da chave de índice curto para os índices clusterizados. Além disso, os índices clusterizados se beneficiam de serem criados em colunas exclusivas ou não nulas. Para obter mais informações, consulte Diretrizes de design de índices clusterizados.

  • As colunas que forem do tipo de dados ntext, text, image, varchar(max), nvarchar(max), e varbinary(max) não podem ser especificadas como colunas de chave de índice. Entretanto, os tipos de dados, varchar(max), nvarchar(max), varbinary(max), e xml podem participar em um índice não-clusterizados como colunas de índice não-chave. Para obter mais informações, consulte Índice com colunas incluídas.

  • Um tipo de dados xml só pode ser uma coluna de chave em um índice XML. Para obter mais informações, consulte Índices em colunas de tipo de dados XML.

  • Examine a singularidade da coluna. Um índice exclusivo em vez de um índice não exclusivo na mesma combinação de colunas, provê informações adicional para o otimizador de consulta, o que torna o índice mais útil. Para obter mais informações, consulte Diretrizes de design de índice exclusivo.

  • Examine a distribuição de dados na coluna. Freqüentemente, uma consulta longa é causada ao se indexar uma coluna com poucos valores exclusivos, ou ao executar uma junção em tal coluna. Isto é um problema fundamental com dados e consulta, e geralmente não pode ser resolvido sem identificar esta situação. Por exemplo, uma lista telefônica física ordenada alfabeticamente pelo último nome não será rápida em localizar uma pessoa, se todas as pessoas na cidade tiverem nomes de Smith ou Jones. Para obter mais informações sobre distribuição de dados, consulte Usando estatísticas para melhorar o desempenho de consultas.

  • Considere o uso de índices filtrados em colunas com subconjuntos bem definidos, por exemplo, colunas esparsas, colunas com grande a maioria dos valores NULL, colunas com categorias de valores e colunas com intervalos diferentes de valores. Um índice filtrado bem projetado pode melhorar o desempenho da consulta e reduzir os custos de manutenção de índice e de armazenamento. Para obter mais informações, consulte Diretrizes de criação de índice filtrado.

  • Considere a ordem das colunas se o índice contiver colunas múltiplas. A coluna usada na cláusula WHERE em uma igual a (=), maior que (>), menor que (<), ou critério de consulta BETWEEN, ou se participar em uma junção, deve ser usada primeiro. Colunas adicionais devem ser ordenadas com base em seu nível de distinção, ou seja, do mais distinto ao menos distinto.

    Por exemplo, se o índice for definido como LastName, FirstName o índice será útil quando o critério de consulta for WHERE LastName = 'Smith' ou WHERE LastName = Smith AND FirstName LIKE 'J%'. Porém, o otimizador de consulta não usaria o índice para uma consulta que tivesse pesquisado apenas em FirstName (WHERE FirstName = 'Jane').

  • Considere indexar as colunas computadas. Para obter mais informações, consulte Criando índices em colunas computadas.

Características do índice

Depois de ter determinado que um índice é apropriado para uma consulta, você pode selecionar o tipo de índice que melhor se adéqüe a sua situação. Características de índice incluem o seguinte:

  • Clusterizado X não-clusterizado.

  • Exclusivo X não exclusivo

  • Única coluna X multicoluna

  • Ordem crescente ou decrescente em colunas no índice

  • Tabela completa versus filtrada para índices não-clusterizados

Você também pode personalizar as características de armazenamento inicial do índice para aperfeiçoar seu desempenho ou manutenção definindo uma opção como FILLFACTOR. Para obter mais informações, consulte Configurando opções de índice. Além disso, você pode determinar o local de armazenamento de índice usando grupos de arquivos ou esquemas de partição para aperfeiçoar o desempenho. Para obter mais informações, consulte Colocando índices em grupos de arquivos.