Indexação desde o início ou quando surge um problema de desempenho?

Devo começar a indexar desde o início ou quando surgir um problema de desempenho?

A estratégia de indexação tende a evoluir à medida que surgem os padrões de uso. Dito isso, também existem estratégias e diretrizes de design que podem ser aplicadas antecipadamente.

• Escolha uma boa chave de agrupamento . Geralmente, você pode determinar o índice clusterizado apropriado em tempo de design, com base no padrão esperado de inserções em uma tabela. Se surgir um caso convincente para uma mudança no futuro, que assim seja.

• Crie suas restrições primárias e outras exclusivas . Estes serão aplicados por índices exclusivos.

• Crie suas chaves estrangeiras e índices não agrupados associados . As chaves estrangeiras são as colunas de junção referenciadas com mais frequência, portanto, indexe-as desde o início.

• Crie índices para quaisquer consultas obviamente altamente seletivas . Para padrões de consulta que você já sabe, serão altamente seletivos e provavelmente usarão pesquisas em vez de varreduras.

Além do acima, adote uma abordagem gradual e holística para implementar novos índices. Por holístico, quero dizer avaliar o benefício potencial e o impacto de todas as consultas e índices existentes ao avaliar uma adição.

Um problema não incomum nos círculos do SQL Server é o excesso de indexação, como resultado da orientação dos DMVs de índice ausente e das dicas do SSMS. Nenhuma dessas ferramentas avalia os índices existentes e sugere alegremente que você crie um novo índice de 6 colunas em vez de adicionar uma única coluna a um índice de 5 colunas existente.

-- If you have this
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
)

-- But your query would benefit from the addition of a column
CREATE NONCLUSTERED INDEX [IX_MyTable_MyIndex] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

-- SSMS will suggest you create this instead
CREATE NONCLUSTERED INDEX [IX_MyTable_AnotherIndexWithTheSameColumnsAsTheExistingIndexPlusCol6] ON [dbo].[MyTable] 
(
    [col1] ASC
    , [col2] ASC
    , [col3] ASC
    , [col4] ASC
    , [col5] ASC
    , [col6] ASC
)

Kimberly Tripp tem um excelente material sobre estratégia de indexação que, embora focado em SQL, é aplicável a outras plataformas. Para o pessoal do SQL Server, existem algumas ferramentas úteis para identificar duplicatas , como no exemplo acima.

Também podemos criar um índice temporário durante a execução de uma consulta. Quais são os prós e os contras de tais técnicas?

Isso geralmente se aplica apenas a consultas raramente executadas, geralmente ETL. Você precisa avaliar:

1. O tempo gasto para criar o índice reduz o tempo de execução da consulta.
2. A sobrecarga de manutenção de deixar o índice no lugar supera o tempo necessário para criar/eliminar quando necessário.

Há realmente riscos associados a ambas as abordagens:

Opção a) Indexar desde o início, mas não perceber que você criou vários índices que nunca são usados. Isso adiciona alguma sobrecarga (principalmente para consultas que modificam dados, mas também com otimização de instruções SELECT tentando identificar o melhor índice).

Você precisará se disciplinar para identificar os índices que não estão mais sendo usados ​​e tentar removê-los (o PostgreSQL pode fazer isso; infelizmente o MySQL, em comparação, é muito fraco nisso fora da caixa).

Opção b) Não adicione índices até que as pessoas comecem a reclamar, ou suas ferramentas de diagnóstico acionem que certas consultas são lentas e podem ser melhoradas.

O risco que você apresenta é que você não tem uma janela de tempo grande o suficiente entre o momento em que percebe que precisa do índice e o momento em que precisa adicioná-lo.

O PostgreSQL oferece suporte à criação de índices CONCURRENTLY, o que reduz parte do estresse desse requisito repentino de adição de índice, mas há algumas ressalvas observadas no manual.

A opção (b) tende a ser minha preferência, mas acho que um híbrido de ambas as opções é provavelmente a melhor solução. Tem a ver com o seu nível de confiança se você acha que um índice será realmente usado.

O que torna essa discussão particularmente complexa é que geralmente é fácil alterar os índices, mas é mais difícil alterar o esquema. Não quero promover a reação retardada de b como desculpa para ser imprudente.

Além da resposta de Mark

Você pode ter uma ideia ao ter dados de teste realistas nas quantidades esperadas. Já vi muitos, muitos (muitos) casos em que uma consulta é executada corretamente com 1.000 linhas, mas não com o milhão em produção.

Se puder, trabalhe em uma cópia da produção mais tarde,

Claro, eu vi o problema estranho apenas na produção por causa dos padrões de uso quando todo o resto é idêntico

Índices temporários? Fora dos padrões de carga ETL, se você precisar deles uma vez, precisará deles novamente. Não se esqueça: um índice create/drop é uma gravação e é registrado = mais carga

Só para acrescentar algumas coisas.

• Índices temporários são uma péssima ideia... a menos que o índice esteja em uma tabela temporária.
• Os índices ocupam muito mais espaço de dados (bem como outras despesas gerais) do que as pessoas imaginam. Portanto, crie-os de forma conservadora.

Esta é a minha abordagem.

1. Semelhante ao Mark, faça índices onde eles fazem sentido, mas não exagere.
2. Você não precisa esperar até que o desempenho fique lento para criar novos índices. Sempre que você escrever um novo SQL, execute um plano de consulta (de preferência em seu banco de dados prod). Você deve ser capaz de ver se um novo índice é necessário.

3. Não tenha medo de colocar > 0ou > ""em suas cláusulas where para colunas não utilizadas.

   1. Ou seja, digamos que você tenha um índice em A,B,C e D. No entanto, você só tem informações A,B,D. Não há nenhuma razão que você não pode fazer-

   select * from blah 
   where A="one" 
   and B="two" 
   and C>=""     --to match index
   and D="four"
   
   --This will use your existing index. No need to create a redundant one.

Vou tentar responder apenas a primeira pergunta. Se você puder estimar, mesmo aproximadamente desde o início, quantos registros terá em suas tabelas após um certo período de tempo, então eu diria que é melhor começar desde o início para projetar alguns índices. Tente usar algumas ferramentas de teste ou scripts de teste que irão automatizar o maior número possível de chamadas para as chamadas de aplicativos que você acha que serão usadas com mais frequência e você verá quais varreduras de tabela podem ser evitadas desde o início.

Será um trabalho de adivinhação no início, mas com o tempo, à medida que você tiver estatísticas de uso adequadas, terá uma imagem mais clara.