Início / dba / Perguntas / 209583
Accepted
Lopsided
Asked: 2018-06-14 09:31:09 +0800 CST

Se um banco de dados tiver apenas uma inserção, é ruim indexar todas as combinações de colunas possíveis?

  • 772

Estou trabalhando em um sistema de relatórios que exigirá grandes consultas de seleção, mas é baseado em um banco de dados que é preenchido apenas uma vez. O sistema de gerenciamento de banco de dados é o Microsoft SQL Server 2017. Provavelmente há uma maneira melhor de projetar um sistema como esse, mas vamos abordar isso teoricamente.

Teoricamente falando:

  1. Se tivermos um banco de dados muito grande (mais de 150 milhões de linhas em várias tabelas)
  2. E podemos assumir que o banco de dados será preenchido apenas uma vez.

A indexação de todas as combinações de colunas possíveis pode ter um impacto negativo no desempenho de uma consulta selecionada?

sql-server t-sql

4 respostas

  1. Best Answer

    Sim, isso influenciará o tempo de compilação do plano inicial, pois o otimizador terá muitos caminhos de acesso extras aos dados a serem considerados.

    Como você está no SQL Server 2017, carregando uma vez e executando relatórios, por que não usar apenas um índice de armazenamento de colunas em cluster?

    Essa parece ser a solução ideal para sua necessidade de indexar todas as combinações de colunas possíveis.

    Índices Columnstore - Visão geral

    • 37

  2. Se você tiver N colunas em uma tabela, todas as combinações de colunas possíveis são 2^N-1 (removendo o conjunto vazio). Para 10 colunas, isso significaria 1023 índices, para 20 colunas, acabamos com 1048575 índices. A maioria dos índices nunca será usada, mas terá que ser levada em consideração pelo otimizador. É possível que o otimizador escolha um índice abaixo do ideal em vez de um melhor. Eu não seguiria o caminho de gerar todos os tipos de índices, em vez de tentar descobrir quais índices seriam realmente benéficos.

    EDITAR número corrigido de índices possíveis

    Como Jeff aponta, é ainda pior do que 2^N (power-set) já que (3,2,1) é claramente diferente de (1,2,3). Para N colunas, podemos escolher a primeira posição em um índice que contém todas as colunas de N maneiras. Para a segunda posição em N-1 maneiras, etc. Nós, portanto, terminamos com N! diferentes índices de tamanho completo. Nenhum desses índices é incluído por outro índice neste conjunto. Além disso, não podemos adicionar outro índice mais curto para que não seja coberto por nenhum índice completo. O número de índices é, portanto, N!. O exemplo para 10 colunas, portanto, se torna 10! = 3628800 índices e para 20 (drumroll) 2432902008176640000 índices. Este é um número ridiculamente grande, se colocarmos um ponto para cada índice de um milímetro por peça, um feixe de luz levará 94 dias para passar todos os pontos. Todos e todos, não ;-)

    • 27

  3. Não.

    Não é prático indexar "tudo", mas você pode indexar "a maioria".

    Aqui está a coisa. Se uma tabela tiver Ncolunas, o número de índices possíveis será N!. Digamos que uma tabela tenha 10 colunas, então você não tem apenas 10índices possíveis, mas 10!. Ou seja... 3.628.800 ... em uma única mesa. Isso é muito espaço em disco, E/S em disco, cache e tempos de busca.

    Por quê? Alguns motivos:

    • Os índices Lightwwight geralmente são armazenados em cache, algo que os torna mais rápidos. Se você tiver 3 milhões deles, eles NÃO serão armazenados em cache.

    • O otimizador SQL pode levar muito tempo para decidir qual é melhor usar, especialmente ao usar junções.

    • O otimizador SQL pode desistir de usar o algoritmo abrangente e tentar um algoritmo heurístico. Isso pode ser "menos do que o ideal". O PostgreSQL, por exemplo, tem opções diferentes para "consultas de menos de 8 tabelas" e "consultas de mais de 8 tabelas".

    • Os índices devem ser mais leves que o heap. Se você está indexando tudo, então o índice se torna tão pesado quanto o heap... algo que anula o propósito do índice.

    • 7

  4. Não, provavelmente não terá impacto negativo nas SELECTconsultas, mas

    • Isso causará um alto uso do disco.
    • Vai aumentar muito os INSERTcustos.
    • A maioria dos seus índices nunca será usada.
    • Muitas WHEREexpressões de condição ainda não usam índices, principalmente as mais complexas.
    • A contagem dos índices necessários aumentará exponencialmente com a contagem das colunas. Ou seja, se você tiver, por exemplo, 8 colunas, precisará de 256 índices para todas as combinações possíveis.
    • 2

relate perguntas