Quais colunas geralmente fazem bons índices?

Como acompanhamento de ” O que são índices e como posso usá-los para otimizar consultas no meu database? “, Onde estou tentando aprender sobre índices, quais colunas são boas candidatas a índices? Especificamente para um database MS SQL?

Depois de algum googling, tudo que eu li sugere que colunas que geralmente são crescentes e únicas fazem um bom índice (coisas como auto_increment do MySQL), eu entendo isso, mas estou usando o MS SQL e estou usando GUIDs para chaves primárias, então parece esses índices não beneficiariam as colunas GUID …

Os índices podem desempenhar um papel importante na otimização de consultas e pesquisar rapidamente os resultados nas tabelas. Portanto, é mais importante selecionar quais colunas serão indexadas. Existem dois lugares principais onde podemos considerar a indexação: colunas referenciadas na cláusula WHERE e colunas usadas nas cláusulas JOIN. Em suma, tais colunas devem ser indexadas contra as quais você é obrigado a pesquisar registros específicos. Suponha que tenhamos uma tabela denominada compradores onde a consulta SELECT usa índices como abaixo:

SELECT buyer_id /* no need to index */ FROM buyers WHERE first_name='Tariq' /* consider to use index */ AND last_name='Iqbal' /* consider to use index */ 

Como “buyer_id” é referenciado na porção SELECT, o MySQL não o usará para limitar as linhas escolhidas. Portanto, não há grande necessidade de indexá-lo. O abaixo é outro exemplo pouco diferente do acima:

 SELECT buyers.buyer_id, /* no need to index */ country.name /* no need to index */ FROM buyers LEFT JOIN country ON buyers.country_id=country.country_id /* consider to use index */ WHERE first_name='Tariq' /* consider to use index */ AND last_name='Iqbal' /* consider to use index */ 

De acordo com as consultas acima first_name, as colunas last_name podem ser indexadas como estão localizadas na cláusula WHERE. Além disso, um campo adicional, country_id from country table, pode ser considerado para indexação porque está em uma cláusula JOIN. Portanto, a indexação pode ser considerada em todos os campos na cláusula WHERE ou na cláusula JOIN.

A lista a seguir também oferece algumas dicas que você deve sempre ter em mente quando pretender criar índices em suas tabelas:

  • Apenas indexe as colunas que são necessárias nas cláusulas WHERE e ORDER BY. Colunas de indexação em abundância resultarão em algumas desvantagens.
  • Tente tirar proveito do recurso “index prefix” ou “multi-columns index” do MySQL. Se você criar um índice como INDEX (first_name, last_name), não crie INDEX (first_name). No entanto, “prefixo de índice” ou “índice de várias colunas” não é recomendado em todos os casos de pesquisa.
  • Use o atributo NOT NULL para as colunas nas quais você considera a indexação, para que os valores NULL nunca sejam armazenados.
  • Use a opção –log-long-format para registrar as consultas que não estão usando índices. Dessa forma, você pode examinar esse arquivo de log e ajustar suas consultas de acordo.
  • A instrução EXPLAIN ajuda você a revelar como o MySQL executará uma consulta. Mostra como e em que ordem as tabelas são unidas. Isso pode ser muito útil para determinar como gravar consultas otimizadas e se as colunas precisam ser indexadas.

Atualização (23 de fevereiro de 2015):

Qualquer índice (bom / ruim) aumenta o tempo de inserção e atualização.

Dependendo de seus índices (número de índices e tipo), o resultado é pesquisado. Se o seu tempo de busca vai aumentar por causa do índice, então isso é um índice ruim.

Provavelmente em qualquer livro, “Página de índice” pode ter a página inicial do capítulo, o número da página do tópico é iniciado, a página do sub-tópico também é iniciada. Algum esclarecimento na página de índice ajuda, mas um índice mais detalhado pode confundi-lo ou assustá-lo. Índices também estão tendo memory.

Seleção de índice deve ser sábia. Lembre-se de que nem todas as colunas precisariam de um índice.

Algumas pessoas responderam uma pergunta semelhante aqui: Como você sabe o que é um bom índice?

Basicamente, isso realmente depende de como você estará consultando seus dados. Você deseja um índice que identifique rapidamente um pequeno subconjunto de seu dataset que seja relevante para uma consulta. Se você nunca consultar por datestamp, você não precisa de um índice, mesmo que seja principalmente exclusivo. Se tudo que você faz é obter events que aconteceram em um determinado período, você definitivamente quer um. Na maioria dos casos, um índice de gênero é inútil – mas se tudo que você faz é obter statistics sobre todos os homens e, separadamente, sobre todas as mulheres, pode valer a pena criar um. Descubra quais serão seus padrões de consulta e o access a qual parâmetro limita mais o espaço de pesquisa, e esse é o melhor índice.

Considere também o tipo de índice que você faz – as trees B são boas para a maioria das coisas e permitem consultas de alcance, mas os índices de hash levam você direto ao ponto (mas não permitem intervalos). Outros tipos de índices têm outros prós e contras.

Boa sorte!

Tudo depende de quais consultas você espera perguntar sobre as tabelas. Se você perguntar por todas as linhas com um determinado valor para a coluna X, você terá que fazer uma varredura completa da tabela se um índice não puder ser usado.

Índices serão úteis se:

  • A coluna ou colunas têm um alto grau de exclusividade
  • Você freqüentemente precisa procurar por um determinado valor ou intervalo de valores para a coluna.

Eles não serão úteis se:

  • Você está selecionando uma porcentagem grande (> 10-20%) das linhas na tabela
  • O uso de espaço adicional é um problema
  • Você deseja maximizar o desempenho da inserção. Cada índice em uma tabela reduz o desempenho de inserção e atualização porque eles devem ser atualizados sempre que os dados forem alterados.

As colunas de chave primária costumam ser ótimas para indexação porque são exclusivas e costumam ser usadas para pesquisar linhas.

Em geral (eu não uso o mssql para não poder comentar especificamente), as chaves primárias fazem bons índices. Eles são únicos e devem ter um valor especificado. (Além disso, as chaves primárias fazem índices tão bons que normalmente têm um índice criado automaticamente.)

Um índice é efetivamente uma cópia da coluna que foi classificada para permitir a pesquisa binária (que é muito mais rápida que a pesquisa linear). Os sistemas de database podem usar vários truques para acelerar ainda mais a pesquisa, principalmente se os dados forem mais complexos do que um simples número.

Minha sugestão seria não usar nenhum índice inicialmente e fazer o perfil de suas consultas. Se uma consulta específica (como procurar pessoas por sobrenome, por exemplo) for executada com muita frequência, tente criar um índice sobre os atributos e perfil relevantes novamente. Se houver uma aceleração perceptível nas consultas e uma lentidão insignificante nas inserções e atualizações, mantenha o índice.

(Desculpas se eu estou repetindo coisas mencionadas em sua outra pergunta, eu não tinha visto isso anteriormente.)

Isso realmente depende das suas perguntas. Por exemplo, se você escrever quase que apenas em uma tabela, é melhor não ter nenhum índice, eles apenas retardam as gravações e nunca são usados. Qualquer coluna que você esteja usando para se juntar a outra tabela é um bom candidato para um índice.

Além disso, leia sobre o recurso de índices ausentes. Ele monitora as consultas reais que estão sendo usadas contra seu database e pode dizer quais índices teriam melhorado a performance.

Uma coluna GUID não é o melhor candidato para indexação. Os índices são mais adequados para colunas com um tipo de dados que podem receber uma ordem significativa, ou seja, classificados (integer, date, etc).

Não importa se os dados em uma coluna geralmente estão aumentando. Se você criar um índice na coluna, o índice criará sua própria estrutura de dados que simplesmente fará referência aos itens reais em sua tabela sem preocupação com o pedido armazenado (um índice não agrupado). Então, por exemplo, uma pesquisa binária pode ser executada sobre sua estrutura de dados de índice para fornecer recuperação rápida.

Também é possível criar um “índice clusterizado” que reordenará fisicamente seus dados. No entanto, você só pode ter um desses por tabela, enquanto você pode ter vários índices não agrupados.

Qualquer coluna que seja usada regularmente para extrair dados da tabela deve ser indexada.

Isso inclui: foreign keys –

 select * from tblOrder where status_id=:v_outstanding 

campos descritivos –

 select * from tblCust where Surname like "O'Brian%" 

As colunas não precisam ser exclusivas. Na verdade, você pode obter um desempenho realmente bom de um índice binário ao procurar exceções.

 select * from tblOrder where paidYN='N' 

Sua chave primária deve ser sempre um índice. (Eu ficaria surpreso se não fosse automaticamente indexado pelo MS SQL, na verdade.) Você também deve indexar as colunas SELECT ou ORDER por freqüentemente; sua finalidade é a pesquisa rápida de um único valor e a sorting mais rápida.

O único perigo real na indexação de muitas colunas é diminuir as alterações nas linhas em tabelas grandes, pois os índices também precisam ser atualizados. Se você realmente não tem certeza do que indexar, apenas calcule as consultas mais lentas, veja quais colunas estão sendo usadas com mais frequência e indexe-as. Então veja quanto mais rápido eles são.

Os tipos de dados numéricos ordenados em ordem crescente ou decrescente são bons índices por vários motivos. Primeiro, os números geralmente são mais rápidos para avaliar do que as strings (varchar, char, nvarchar, etc). Em segundo lugar, se seus valores não forem ordenados, linhas e / ou páginas podem precisar ser embaralhadas para atualizar seu índice. Isso é sobrecarga adicional.

Se você estiver usando o SQL Server 2005 e configurar o uso de uniqueidentifiers (guids), e NÃO precisar que eles sejam de natureza aleatória, verifique o tipo uniqueidentifier sequencial.

Por fim, se você está falando sobre índices clusterizados, está falando sobre o tipo de dados físicos. Se você tem uma string como seu índice clusterizado, isso pode ficar feio.

Deve ser ainda mais rápido se você estiver usando um GUID. Suponha que você tenha os registros

  1. 100
  2. 200
  3. 3000
  4. ….

Se você tem um índice (pesquisa binária, você pode encontrar a localização física do registro que está procurando no tempo O (lg n), em vez de pesquisar sequencialmente o (n) tempo. Isso é porque você não sabe quais registros você tem na sua mesa.

A regra geral era colunas que são muito usadas nas cláusulas WHERE, ORDER BY e GROUP BY, ou em qualquer outra que parecesse ser usada frequentemente em junções. Lembre-se de que estou me referindo a índices, NOT Primary Key

Não para dar uma resposta ‘baunilha-ish’, mas isso realmente depende de como você está acessando os dados

O melhor índice depende do conteúdo da tabela e do que você está tentando realizar.

Tomemos um exemplo Um database de membro com uma chave primária do Numnber de seguridade social dos membros. Escolhemos o SS porque o aplicativo principal se refere ao indivíduo dessa maneira, mas você também deseja criar uma function de pesquisa que utilizará o nome e sobrenome do membro. Eu sugeriria então criar um índice sobre esses dois campos.

Você deve primeiro descobrir quais dados será consultado e, em seguida, determinar quais dados você precisa indexar.