Início / dba / Perguntas / 194015
Accepted
Zapnologica
Asked: 2017-12-27 23:42:20 +0800 CST

Índice duplicado questionável sugerido pela otimização

  • 772

Bom dia, tenho a seguinte tabela de banco de dados sql server: insira a descrição da imagem aqui

Observe a chave primária composta. Isso foi feito por 3 motivos:

  • Impedir entradas duplicadas
  • Melhore o desempenho da consulta, pois todas as consultas terão todas as 3 chaves.
  • Precisávamos e indexamos, e eu não queria introduzir um ID aleatório.

Observe também que esta tabela foi projetada com seu tamanho em mente. Esta tabela armazenará milhões e milhões de linhas de dados.

OK agora para a minha pergunta real. Estou usando o azure sql server para hospedar este banco de dados. e habilitei o ajuste automático. E, estranhamente, vejo que ele foi e criou um novo índice. (Veja abaixo)

insira a descrição da imagem aqui

Agora, na minha mente, isso parece ser um índice duplicado, pois as mesmas colunas estão sendo indexadas.

Então agora eu tenho dois índices na minha tabela:

Original (Meu PK):

ALTER TABLE [dbo].[SensorDataRaw] ADD  CONSTRAINT [PK_SensorDataRaw] PRIMARY KEY CLUSTERED 
(
    [DateTime] ASC,
    [SensorId] ASC,
    [Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Recém-adicionado (criado automaticamente pelo ajuste do Azure):

CREATE NONCLUSTERED INDEX [nci_wi_SensorDataRaw_DC9789077DA75B4440AC8BFE3E2AA198] ON [dbo].[SensorDataRaw]
(
    [Key] ASC,
    [SensorId] ASC,
    [DateTime] ASC
)
INCLUDE (   [Value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Observações:

  • A ordem das colunas foi invertida no novo índice.
  • O novo índice NÃO é único
  • o novo índice inclui a coluna de valor.

Observe que meu conhecimento sobre índices não é avançado, por isso estou perguntando isso.

Então minha pergunta é:

  1. Alguém pode explicar por que o índice recém-adicionado é melhor do que o meu criado inicialmente.
  2. Como posso remover os dois índices e apenas criar um que cubra os dois casos. Sendo este um banco de dados tão grande, não posso arcar com o espaço que esses dois índices ocuparão.
  3. O talvez seja uma alternativa de design melhor?

Informação adicional:

Estou assumindo que o tipo de consulta se torna importante aqui, então listei alguns exemplos.

Todas as consultas incluem DateTime, SensorIde Key.

Consultas simples:

Select SensorId Where average value for key w is greater than x where time between (y,z)

Dados gráficos:

SELECT AVG([Value]) AS 'AvgValue',    
    DATEADD( MINUTE, 
    (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
[DateTime]) / @IntervalInMinutes) * @IntervalInMinutes,
        '1990-01-01T00:00:00'
    )      AS 'TimeGroup'
 FROM [dbo].[SensorDataRaw]
 where 
    [dbo].[SensorDataRaw].[SensorId] = @SensorId
    and [dbo].[SensorDataRaw].[Key] = @KeyValue
    and [dbo].[SensorDataRaw].[DateTime] Between @DateFrom and @DateTo
    and [dbo].[SensorDataRaw].[Value] IS NOT NULL  
  GROUP BY (DATEDIFF(MINUTE, '1990-01-01T00:00:00', [dbo].[SensorDataRaw].
    [DateTime]) / @IntervalInMinutes)
sql-server index

3 respostas

  1. Best Answer

    O índice sugerido pelo sistema é muito mais adequado para a consulta que você mostrou. Você deve ter como objetivo ter colunas com predicados de igualdade como colunas iniciais.

    Considere uma lista telefônica ordenada por lastname, firstname. Se o seu requisito é encontrar todas as pessoas com sobrenomes entre "Brown" e "Yates" e um primeiro nome de "John", então você precisa ler a maior parte da lista telefônica. Se a lista telefônica foi ordenada por firstname, lastnamevocê pode encontrar facilmente a seção "John" e o primeiro "Brown" na seção, então tudo o que você precisa fazer é ler todos os nomes até que lastnameesteja depois de "Yates" ou um novo nome seja encontrado.

    Pode não ser o índice ideal. Potencialmente, você deve apenas alterar as colunas de chave no índice clusterizado para essa ordem, em vez de criar uma nova. Você precisa avaliar isso com base no conhecimento de sua carga de trabalho.

    • 6

  2. Alguém pode explicar por que o índice recém-adicionado é melhor que o meu criado inicialmente

    Seu índice é clusteredum, isso significa que inclui todos os dados como o nível folha.

    O índice oferecido pelo servidor é mais restrito em relação ao cluster, pois contém apenas key fields+ included fieldno nível folha.

    Sua consulta precisa apenas de key fields+ valuecampo para que o índice não clusterizado oferecido a você pelo servidor seja suficiente e seja escolhido pelo servidor como o índice de cobertura mais estreito possível.

    Como posso remover os dois índices e apenas criar um que cubra os dois casos.

    Você pode remover o índice não clusterizado, seu cluster é claro cobrindo um, e se ele tiver apenas 2 campos adicionais: datetime2(2) + decimal(19,4) em relação ao não clusterizado, não haverá grande sobrecarga ao usá-lo em vez de não agrupado.

    • 1

  3. O índice sugerido é mais restrito, o que o tornará mais eficiente quando a consulta tratar apenas de colunas cobertas por esse índice.

    A ordem das colunas também é diferente, o que pode torná-la mais eficiente para essa consulta específica (não posso dizer com certeza sem ver um plano de execução). A ordem das colunas FAZ diferença.

    O que é melhor, o índice não clusterizado pode ser melhor para essa consulta específica, mas não cobre tudo. Além disso, o índice clusterizado não pode ser removido e não vejo nenhum problema em manter ambos.

    Tente experimentar os dois, force o índice clusterizado e verifique o plano de execução e as estatísticas de E/S, depois force o não clusterizado e compare.

    • 1

relate perguntas