Melhor maneira de indexar esta tabela muito grande

Question

Eu tenho a seguinte tabela

CREATE TABLE DiaryEntries
(
 [userId] [uniqueidentifier] NOT NULL,
 [setOn] [datetime] NOT NULL, -- always set to GETDATE().
 [entry] [nvarchar](255) NULL
)

Cada usuário irá inserir cerca de 3 entradas por dia. Haverá cerca de 1'000'000 usuários. Isso significa 3'000'000 novos registros nesta tabela todos os dias. Quando um registro tiver mais de um mês, ele será excluído.

A maioria das consultas tem a seguinte cláusula WHERE:

WHERE userId = @userId AND setOn > @setOn

A maioria das consultas não retorna mais do que 3 linhas, exceto uma que retorna todas as linhas inseridas neste mês (que são no máximo 90 linhas).

A data e o userId não podem ser alterados, uma vez que o registro é inserido.

Agora, minha pergunta é: como organizar melhor o índice para essa tabela? Eu estou preso com as duas alternativas:

1. Índice clusterizado em (userId, setOn) - isso me proporcionará pesquisas rápidas, mas estou preocupado com excessivas divisões de página, pois inseriremos muitos valores intermediários (mesmo userId, mas data diferente).
2. Índices não agrupados em (userId) e em (setOn) - isso também causará divisões de página no índice (userId) (mas é tão caro quanto na primeira opção?). A busca é retardada porque estamos usando o índice NC.
3. Índice agrupado em uma coluna adicional (id) e índice não agrupado em (userId, setOn) - isso eliminará as divisões de página da tabela de dados, mas ainda causará algumas no índice NC. Esta opção também não é ideal para pesquisa, já que procuramos usando o índice NC.

Quais são suas sugestões? Alguma outra opção?

PS - Obrigado pelo seu tempo.

Depois de dois dias ponderando, descobri uma solução diferente para esse problema.

CREATE TABLE MonthlyDiaries
(
 [userId] uniqueidentifier NOT NULL,
 [setOn] datetime NOT NULL, -- always set to GETDATE().

 [entry1_1] bigint NULL, -- FK to the 1st entry of the 1st day of the month.
 [entry1_2] bigint NULL, -- FK to the 2nd entry of the 1st day of the month.
 [entry1_3] bigint NULL,
 [entry2_1] bigint NULL,
 [entry2_2] bigint NULL,
 [entry2_3] bigint NULL,
 ...
 [entry31_1] bigint NULL,
 [entry31_2] bigint NULL,
 [entry31_3] bigint NULL,
 PRIMARY KEY (userId, setOn)
)
CREATE TABLE DiaryEntries
(
 [id] bigint IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 [entry] nvarchar(255) NOT NULL
)

Basicamente eu agrupei 31 dias em uma única linha. Isso significa que eu insiro um novo registro apenas uma vez por mês por usuário. Isso reduz as divisões de página de 3 vezes por dia por usuário para uma vez por mês por usuário. Obviamente, existem desvantagens, aqui estão algumas delas

• O tamanho da linha é enorme - no entanto, em 99,999% da vez que eu consultar apenas uma única linha de MonthlyDiaries.
• Estou potencialmente usando mais espaço do que eu preciso, porque alguns dias podem não tem entradas. Não é grande coisa.
• Para encontrar uma entrada para um dia específico vai exigir um índice extra procurar em Entradas diárias. Eu acredito que não vai ser um ótimo custo, já que eu sou recuperando não mais de 90 linhas e em 80% dos casos, recupero apenas 1 linha.

No geral, acho que é um bom compromisso: reduzir de 3 páginas divididas / dia / usuário para apenas 1 página dividida / mês / usuário, mas em troca pagar um preço pequeno, tornando minhas pesquisas um pouco mais lentas. O que você acha?

    

Answer 1

Suponho que você tenha boas razões para usar os guias como ids.

A fragmentação é principalmente um problema para digitalizações, menos para as pesquisas. A fragmentação tem um grande impacto nas leituras antecipadas e as buscas não usam nem precisam de leitura antecipada. Um índice não fragmentado com escolha de coluna fraca terá um desempenho sempre pior do que um índice de fragmentos de 99% com colunas boas e utilizáveis. Se você tivesse descrito as consultas de estilo de relatório do DW que examinam as tabelas, eu recomendaria concentrar-se em eliminar a fragmentação, mas, para a carga descrita, faz mais sentido se concentrar em buscas eficientes (abrangentes) e em varreduras (pequenas). / p>

Dado que o seu padrão de acesso é sempre orientado por @userId, esta deve ser a coluna mais à esquerda no índice clusterizado. Eu também adicionaria setOn como a segunda coluna no índice clusterizado porque ele adiciona algum valor marginal na maioria das consultas (digo marginal porque o @userId é tão seletivo, na pior das hipóteses é 90 registros de 90 mil., Que a filtragem extra adicionada por @setOn não é crítico). Eu não adicionei nenhum índice não clusterizado, a partir das consultas que você descreve não há necessidade de nenhum.

O único problema é a exclusão de registros antigos (a retenção de 30 dias). Eu recomendaria contra o uso de um índice NC secundário para satisfazer isso. Eu prefiro implantar um esquema de particionamento semanal com janela deslizante, consulte Como implementar um deslizamento automático Janela em uma tabela particionada no SQL Server 2005 . Com esta solução, os registros antigos são removidos por um switch de partição, que é apenas a maneira mais eficiente possível. Um esquema de particionamento diário satisfaria o requisito de retenção de 30 dias mais exatamente e talvez vale a pena tentar e testar. Hesito em recomendar 30 partições diretamente porque você descreve algumas consultas que têm potencial para procurar um determinado registro @userId em cada partição e 31 partições podem criar problemas de desempenho sob carga pesada. Teste e meça melhor.

    

Answer 2

Primeiro, adicione uma restrição padrão à sua tabela. Em segundo lugar, adicione um esquema de particionamento. Terceiro reescreva sua consulta mais comum.

O índice clusterizado deve ser definido como setOn, ID do usuário. Isso elimina a possibilidade de o índice se tornar fragmentado. Você deve usar o particionamento de tabelas para dividir a tabela para que cada mês seja armazenado em um arquivo separado. Isso reduzirá a manutenção. Você pode procurar online por um script de janela deslizante de partição que possa ser executado todo mês para criar uma nova tabela para o próximo mês, excluir o mês mais antigo e ajustar o esquema de particionamento. Você também pode mover os meses realmente antigos para uma tabela de arquivos se o armazenamento não lhe interessar.

Suas consultas onde cláusula deve estar no formulário:

WHERE setOn > @setOn AND userId = @userId

Ou quando você está retornando o mês inteiro:

WHERE setOn BETWEEN @setOnBegin AND @setOnEnd AND userId = @userId

Seu novo design de esquema, sem o particionamento, ficaria assim:

-- Stub table for foreign key
CREATE TABLE Users
(
 [userId] [uniqueidentifier] NOT NULL
  CONSTRAINT PK_Users PRIMARY KEY NONCLUSTERED
  CONSTRAINT DF_Users_userId DEFAULT NEWID(),
 [userName] VARCHAR(50) NOT NULL
)
GO

CREATE TABLE DiaryEntries
(
 [userId] [uniqueidentifier] NOT NULL
  CONSTRAINT FK_DiaryEntries_Users FOREIGN KEY REFERENCES Users,
 [setOn] [datetime] NOT NULL
  CONSTRAINT DF_DiaryEntries_setOn DEFAULT GETDATE(),
 [entry] [nvarchar](255) NULL,
 CONSTRAINT PK_DiaryEntries PRIMARY KEY CLUSTERED (setOn, userId)
)
GO

Depois de conseguir isso, você precisa adicionar o particionamento. Para isso, comece com Esta postagem no blog para alguns teoria. Em seguida, comece a ler este whitepaper da MSDN . O white paper foi escrito para 2005 e houve melhorias de partição em 2008 que não foram investigadas, portanto a solução pode ser mais simples em 2008.

    

Answer 3

Não estou aqui para criticar sua solução, nem estou em condições de fazê-lo, pois não sei o suficiente sobre o problema. Aqui está o meu feedback:

• Se a única coisa que você não está feliz sobre está usando muito espaço em disco por causa do tamanho da linha, confira colunas esparsas Dessa forma, todos os nulos não ocupam tanto espaço!
• Ter as chaves estrangeiras indo para diminua suas inserções consideravelmente, você já testou isso?

Answer 4

Você deseja que as novas linhas sejam inseridas no final do arquivo físico da tabela, como um arquivo de diário, porque há tantas linhas inseridas a cada dia.

Portanto, as linhas devem ser ordenadas em ordem cronológica

Portanto, setOn deve ser a primeira parte da chave primária. - ou, idealmente, adicione uma coluna "postId", que é apenas um inteiro que se autoincrementa

Se você não quiser uma coluna postId, a chave primária será (setOn, userId), caso contrário, ela pode simplesmente ser postId.

Assim, obtivemos tempos de inserção rápidos. Agora queremos tempos de recuperação rápidos ao selecionar por usuário.

Para isso, precisamos adicionar um índice secundário à tabela, que deve estar em useId. Como há apenas 90 registros por usuário, isso é suficiente para que os rdbms consigam recuperar rapidamente todas as linhas desse usuário (todas as 90 delas, desde as linhas de um mês por vez) e, em seguida, verificar essas 90 linhas na tabela. que será incrivelmente rápido.

O índice pode ser qualquer b-tree padrão, árvore vermelho-preto, índice, o que vier com o seu banco de dados.

A inserção será ligeiramente reduzida pela inserção no índice, mas não muito. Estruturas de árvores são muito boas em lidar com inserções aleatórias.

Como o índice UserId é baseado no conjunto de UserIds, que é um conjunto estável, a árvore deve ser razoavelmente estável e não precisar de muito rebalanceamento: apenas os nós folha no final serão alterados à medida que as entradas de diário forem adicionadas e limpas. , o que não mudará muito a forma da árvore.

    

Answer 5

Não sou fã da sua nova solução. Ele apenas apresentará novos problemas, sendo que o maior é que os UPDATES são (normalmente) mais lentos que os INSERTS e criam um risco maior de bloqueio quando as atualizações estão ocorrendo.

Se você estiver preocupado com divisões de páginas, tudo o que precisa fazer é ajustar o " FillFactor " para o índice clusterizado. O FillFactor define quanto de cada página é deixado em branco (por padrão) para permitir alterações ou inserções.

Definir um FillFactor razoável significa que as inserções não devem causar (tantas) divisões de página, e as suas remoções de registros antigos significam que mais espaço deve ser liberado nessas páginas, mantendo (um pouco) espaço livre consistente por página.

Infelizmente, o padrão SQL é geralmente 0 (o que significa o mesmo que 100), o que significa que todas as páginas estão completamente cheias, o que causa muitas divisões de página. Muitas pessoas recomendam um valor de 90 (10% de espaço livre em cada página de dados). Eu não posso te dizer qual seria o ideal para a sua tabela, mas se o seu ultra-paranóico sobre a página se dividir, tente 75 ou até menos, se você puder poupar o espaço extra em disco. Existem alguns contadores de perfmon que você pode monitorar para observar divisões de página ou executar consultas para informar o percentual de espaço livre em cada página de dados.

Em relação às especificidades dos índices em sua tabela (a versão original), eu recomendaria um índice clusterizado em ([userId], [setOn]), pelas razões que Remus mencionou.

Você também precisará de um índice não clusterizado em ([setOn]) para que sua consulta "excluir registros antigos" não precise executar uma varredura completa na tabela para localizar todos os registros antigos.

Também não sou fã de GUIDs para identificadores simples, na maioria das vezes, mas imagino que seja um pouco tarde para mudar.

Editar : alguns cálculos preliminares sobre um fator de preenchimento estimado para essa tabela.

Para cada usuário, três novas entradas por dia, mantidas por 30 dias, ou seja, um total de 90 entradas. Supondo que você faça uma limpeza diária de todos os registros com mais de 30 dias (em vez de apenas remover a cada 30 dias), você estará adicionando / excluindo menos de 5% dos registros diariamente.

Portanto, um fator de preenchimento de 90 (10% de espaço livre em cada página) deve ser mais que suficiente.

Se você está purgando apenas mensalmente , estará deixando quase 60 dias acumulados antes de excluir os 30 mais antigos, o que significa que você precisa de algo como 50% de fator de preenchimento.

Eu recomendo uma limpeza diária.

Editar 2 : Após uma análise mais aprofundada, um índice não agrupado em [setOn] pode não ser seletivo o suficiente para ser usado por sua consulta de eliminação (um único dia é 1/30 ou 3,3% do linhas, que está à direita de "útil"). Ele pode fazer uma varredura de índice em cluster, mesmo que o índice exista. Provavelmente vale a pena testar com e sem este índice adicional.

    

Answer 6

Sugiro:

1. Índice em cluster no ID do usuário
2. Índice de cobertura não agrupado em seton & entrada, ou apenas um índice não clusterizado no seton

Answer 7

Uma maneira de resolver isso é ter uma tabela para cada dia.

Com uma tabela de registros 3M, não é um problema ter um índice clusterizado em userid e seton. Seu tempo de inserção seria muito menor.

Você pode executar uma manutenção na tabela do dia no final do dia para que a tabela não seja fragmentada e o tempo de resposta seja aceitável.

Você também pode criar uma visão das tabelas para obter dados de um mês inteiro.