Início / dba / Perguntas / 48458
Accepted
BlueChippy
Asked: 2013-08-21 22:01:52 +0800 CST

A ordem das colunas em um índice PK é importante?

  • 772

Tenho algumas tabelas muito grandes com a mesma estrutura básica. Cada um tem uma coluna RowNumber (bigint)e . DataDate (date)Os dados são carregados usando SQLBulkImport todas as noites e nenhum dado "novo" é carregado - é um registro histórico (SQL Standard, não Enterprise, portanto, sem particionamento).

Como cada bit de dados precisa ser vinculado a outros sistemas e cada RowNumber/DataDatecombinação é única, essa é minha chave primária.

Percebo que pela forma como defini o PK no SSMS Table Designer, RowNumberestá listado primeiro e DataDatesegundo.

Também noto que minha fragmentação é sempre MUITO alta ~ 99%.

Agora, como cada um DataDateaparece apenas uma vez, eu esperaria que o indexador apenas adicionasse às páginas todos os dias, mas me pergunto se ele realmente está indexando com base no RowNumberprimeiro e, portanto, tendo que mudar todo o resto?

Rownumbernão é uma coluna de identidade, é um int gerado por um sistema externo (infelizmente). Ele redefine no início de cada DataDate.

Dados de Exemplo

RowNumber | DataDate | a | b | c..... 
   1      |2013-08-01| x | y | z 
   2      |2013-08-01| x | y | z 
...
   1      |2013-08-02| x | y | z 
   2      |2013-08-02| x | y | z 
...

Os dados estão sendo carregados em RowNumberordem, um DataDatepor carregamento.

O processo de importação é bcp - tentei carregar em uma tabela temporária e, em seguida, selecionar em ordem a partir daí ( ORDER BY RowNumber, DataDate), mas ainda sai alta fragmentação.

sql-server sql-server-2008-r2

2 respostas

  1. Best Answer

    A ordem das colunas em um índice PK é importante?

    Sim.

    Por padrão, a restrição de chave primária é aplicada no SQL Server por um índice clusterizado exclusivo. O índice clusterizado define a ordem lógica das linhas na tabela. Pode haver um número extra de páginas de índice adicionadas para representar os níveis superiores do índice b-tree, mas o nível mais baixo (folha) de um índice clusterizado é simplesmente a ordem lógica dos próprios dados.

    Para ser claro, as linhas em uma página não são necessariamente armazenadas fisicamente na ordem da chave de índice clusterizado. Há uma estrutura de indireção separada dentro da página que armazena um ponteiro para cada linha. Essa estrutura é classificada pelas chaves de índice clusterizadas. Além disso, cada página tem um ponteiro para a página anterior e a próxima no mesmo nível na ordem de chave de índice clusterizado.

    Com uma chave primária agrupada de (RowNumber, DataDate), as linhas são classificadas logicamente primeiro por RowNumbere depois por DataDate- então todas as linhas onde RowNumber = 1são logicamente agrupadas, depois as linhas onde RowNumber = 2e assim por diante.

    Quando você adiciona novos dados (com RowNumbersde 1 a n), as novas linhas pertencem logicamente às páginas existentes, portanto, o SQL Server provavelmente terá que fazer muito trabalho dividindo as páginas para liberar espaço. Toda essa atividade gera muito trabalho extra (incluindo o registro das alterações) sem nenhum ganho.

    As páginas divididas também começam com cerca de 50% vazias, portanto, a divisão excessiva também pode resultar em baixa densidade de páginas (menos linhas do que o ideal por página). Isso não é apenas uma má notícia para a leitura do disco (menor densidade = mais páginas para ler), as páginas de menor densidade também ocupam mais espaço na memória quando armazenadas em cache.

    Alterar o índice clusterizado para (DataDate, RowNumber) significa que novos dados (com, presumivelmente, maiores DataDatesdo que os armazenados atualmente) são anexados ao final lógico do índice clusterizado em páginas novas. Isso removerá as sobrecargas desnecessárias de páginas divididas e resultará em tempos de carregamento mais rápidos. Dados menos fragmentados também significam que a atividade de leitura antecipada (ler páginas do disco antes de serem necessárias para uma consulta em andamento) pode ser mais eficiente.

    Se nada mais, suas consultas têm muito mais probabilidade de pesquisar do DataDateque RowNumber. Um índice clusterizado em (DataDate, RowNumber) dá suporte a buscas de índice em DataDate(e depois em RowNumber). O arranjo existente suporta apenas buscas em RowNumber(e só então, talvez, em DataDate). Você pode descartar o índice não clusterizado existente DataDateassim que a chave primária for alterada. O índice clusterizado será mais largo do que o índice não clusterizado que ele substitui, portanto, você deve testar para garantir que o desempenho permaneça aceitável.

    Ao importar novos dados com bcp, você pode obter maior desempenho se os dados no arquivo de importação forem classificados pelas chaves de índice clusterizado (idealmente (DataDate, RowNumber)) e você especificar a bcpopção:

    -h "ORDER(DataDate,RowNumber), TABLOCK"

    Para obter o melhor desempenho de carregamento de dados, você pode tentar obter inserções minimamente registradas. Para mais informações, veja:

    • 54

  2. Sim, a ordem é crítica. Duvido muito que você consulte por RowNumber (por exemplo WHERE RowNumber=1). A esmagadora maioria das séries temporais são consultadas por data ( WHERE DataDate BEWEEN @start AND @end) e essas consultas exigiriam uma organização em cluster por DataDate.

    A fragmentação em geral é uma pista falsa. Reduzir a fragmentação não deve ser seu objetivo aqui, mas sim ter uma organização adequada para suas consultas. Obter fragmentação reduzida também é uma boa ideia, mas não é um objetivo por si só. Se você tiver um modelo de dados devidamente organizado que corresponda à sua carga de trabalho (suas consultas são cobertas adequadamente) e tiver medições que mostram que a fragmentação afeta o desempenho, podemos conversar sobre isso.

    • 15

relate perguntas