Início / dba / Perguntas / 158291
Accepted
Andrew Williamson
Asked: 2016-12-16 10:53:15 +0800 CST

Por que meu índice não está sendo usado em um SELECT TOP?

  • 772

Aqui está o resumo: estou fazendo uma consulta de seleção. Todas as colunas nas cláusulas WHEREe ORDER BYestão em um único índice não clusterizado IX_MachineryId_DateRecorded, como parte da chave ou como INCLUDEcolunas. Estou selecionando todas as colunas, para que isso resulte em uma pesquisa de favoritos, mas estou apenas usando TOP (1), então certamente o servidor pode dizer que a pesquisa só precisa ser feita uma vez, no final.

Mais importante, quando forço a consulta a usar index IX_MachineryId_DateRecorded, ela é executada em menos de um segundo. Se eu deixar o servidor decidir qual índice usar, ele escolherá IX_MachineryIde levará até um minuto. Isso realmente sugere para mim que fiz o índice certo e o servidor está apenas tomando uma decisão ruim. Por quê?

CREATE TABLE [dbo].[MachineryReading] (
    [Id]                 INT              IDENTITY (1, 1) NOT NULL,
    [Location]           [sys].[geometry] NULL,
    [Latitude]           FLOAT (53)       NOT NULL,
    [Longitude]          FLOAT (53)       NOT NULL,
    [Altitude]           FLOAT (53)       NULL,
    [Odometer]           INT              NULL,
    [Speed]              FLOAT (53)       NULL,
    [BatteryLevel]       INT              NULL,
    [PinFlags]           BIGINT           NOT NULL,
    [DateRecorded]       DATETIME         NOT NULL,
    [DateReceived]       DATETIME         NOT NULL,
    [Satellites]         INT              NOT NULL,
    [HDOP]               FLOAT (53)       NOT NULL,
    [MachineryId]        INT              NOT NULL,
    [TrackerId]          INT              NOT NULL,
    [ReportType]         NVARCHAR (1)     NULL,
    [FixStatus]          INT              DEFAULT ((0)) NOT NULL,
    [AlarmStatus]        INT              DEFAULT ((0)) NOT NULL,
    [OperationalSeconds] INT              DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_dbo.MachineryReading] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.MachineryReading_dbo.Machinery_MachineryId] FOREIGN KEY ([MachineryId]) REFERENCES [dbo].[Machinery] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.MachineryReading_dbo.Tracker_TrackerId] FOREIGN KEY ([TrackerId]) REFERENCES [dbo].[Tracker] ([Id]) ON DELETE CASCADE
);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId]
    ON [dbo].[MachineryReading]([MachineryId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_TrackerId]
    ON [dbo].[MachineryReading]([TrackerId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_MachineryId_DateRecorded]
    ON [dbo].[MachineryReading]([MachineryId] ASC, [DateRecorded] ASC)
    INCLUDE([OperationalSeconds], [FixStatus]);

A tabela é particionada em intervalos de meses (embora eu ainda não entenda realmente o que está acontecendo lá).

ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-01-01T00:00:00.000') 

ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-02-01T00:00:00.000') 
...

CREATE UNIQUE CLUSTERED INDEX [PK_dbo.MachineryReadingPs] ON MachineryReading(DateRecorded, Id) ON PartitionSchemeMonthRange(DateRecorded)

A consulta que eu normalmente executaria:

SELECT TOP (1) [Id], [Location], [Latitude], [Longitude], [Altitude], [Odometer], [ReportType], [FixStatus], [AlarmStatus], [Speed], [BatteryLevel], [PinFlags], [DateRecorded], [DateReceived], [Satellites], [HDOP], [OperationalSeconds], [MachineryId], [TrackerId]
    FROM [dbo].[MachineryReading]
    --WITH(INDEX(IX_MachineryId_DateRecorded)) --This makes all the difference
    WHERE ([MachineryId] = @p__linq__0) AND ([DateRecorded] >= @p__linq__1) AND ([DateRecorded] < @p__linq__2) AND ([OperationalSeconds] > 0)
    ORDER BY [DateRecorded] ASC

Plano de consulta: https://www.brentozar.com/pastetheplan/?id=r1c-RpxNx

Plano de consulta com índice forçado: https://www.brentozar.com/pastetheplan/?id=SywwTagVe

Os planos incluídos são os planos de execução reais, mas no banco de dados de preparação (cerca de 1/100 do tamanho do ao vivo). Estou hesitante em mexer no banco de dados ativo porque comecei nesta empresa há apenas um mês.

Tenho a sensação de que é por causa do particionamento, e minha consulta normalmente abrange todas as partições (por exemplo, quando desejo obter o primeiro ou o último OperationalSecondsregistrado para uma máquina). No entanto, as consultas que tenho escrito à mão estão sendo executadas de 10 a 100 vezes mais rápido do que o EntityFramework gerou, então vou apenas criar um procedimento armazenado.

sql-server index

2 respostas

  1. Best Answer

    Se eu deixar o servidor decidir qual índice usar, ele escolherá IX_MachineryIde levará até um minuto.

    Esse índice não é particionado, portanto, o otimizador reconhece que pode ser usado para fornecer a ordem especificada na consulta sem classificação. Como um índice não clusterizado não exclusivo, ele também possui as chaves do índice clusterizado como subchaves, portanto, o índice pode ser usado para buscar MachineryIde o DateRecordedintervalo:

    busca de índice

    O índice não inclui OperationalSeconds, portanto, o plano deve procurar esse valor por linha no índice clusterizado (particionado) para testar OperationalSeconds > 0:

    Olho para cima

    O otimizador estima que uma linha precisará ser lida do índice não clusterizado e pesquisada para satisfazer o TOP (1). Este cálculo é baseado no objetivo da linha (encontrar uma linha rapidamente) e assume uma distribuição uniforme de valores.

    No plano real, podemos ver que a estimativa de 1 linha é imprecisa. Na verdade, 19.039 linhas precisam ser processadas para descobrir que nenhuma linha atende às condições da consulta. Este é o pior caso para uma otimização de objetivo de linha (1 linha estimada, todas as linhas realmente necessárias):

    Real/estimativa

    Você pode desativar os objetivos de linha com o sinalizador de rastreamento 4138 . Isso provavelmente resultaria no SQL Server escolhendo um plano diferente, possivelmente aquele que você forçou. Em qualquer caso, o índice IX_MachineryIdpode ser otimizado incluindo OperationalSeconds.

    É bastante incomum ter índices não agrupados não alinhados (índices particionados de maneira diferente da tabela base, incluindo nenhum).

    Isso realmente sugere para mim que fiz o índice certo e o servidor está apenas tomando uma decisão ruim. Por quê?

    Como de costume, o otimizador está selecionando o plano mais barato que considera.

    O custo estimado do IX_MachineryIdplano é de 0,01 unidades de custo, com base na suposição de meta de linha (incorreta) de que uma linha será testada e retornada.

    O custo estimado do IX_MachineryId_DateRecordedplano é muito maior, em 0,27 unidades, principalmente porque ele espera ler 5.515 linhas do índice, classificá-las e retornar a que tiver a classificação mais baixa (por DateRecorded):

    Ordenação N Superior

    Este índice é particionado e não pode retornar linhas em DateRecordedordem diretamente (veja mais adiante). Ele pode procurar MachineryIde o DateRecordedintervalo dentro de cada partição , mas é necessário classificar:

    Busca particionada

    Se esse índice não fosse particionado, uma classificação não seria necessária e seria muito semelhante ao outro índice (não particionado) com a coluna extra incluída. Um índice filtrado não particionado seria um pouco mais eficiente ainda.

    Você deve atualizar a consulta de origem para que os tipos de dados dos parâmetros @Frome correspondam à coluna ( ). No momento, o SQL Server está computando um intervalo dinâmico devido à incompatibilidade de tipo em tempo de execução (usando o operador Merge Interval e sua subárvore):@ToDateRecordeddatetime

    <ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@From],NULL,(22))">
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@To],NULL,(22))">

    Essa conversão impede que o otimizador raciocine corretamente sobre a relação entre os IDs de partição crescente (cobrindo um intervalo de DateRecordedvalores em ordem crescente) e os predicados de desigualdade em DateRecorded.

    O ID da partição é uma chave principal implícita para um índice particionado. Normalmente, o otimizador pode ver que ordenar por ID de partição (onde os IDs crescentes são mapeados para valores crescentes e disjuntos de DateRecorded) DateRecordedé o mesmo que ordenar DateRecordedapenas por (desde que MachineryIDseja constante). Essa cadeia de raciocínio é quebrada pela conversão de tipo.

    Demonstração

    Uma tabela particionada simples e um índice:

    CREATE PARTITION FUNCTION PF (datetime)
AS RANGE LEFT FOR VALUES ('20160101', '20160201', '20160301');

CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

CREATE TABLE dbo.T (c1 integer NOT NULL, c2 datetime NOT NULL) ON PS (c2);

CREATE INDEX i ON dbo.T (c1, c2) ON PS (c2);

INSERT dbo.T (c1, c2) 
VALUES (1, '20160101'), (1, '20160201'), (1, '20160301');

    Consulta com tipos correspondentes

    -- Types match (datetime)
DECLARE 
    @From datetime = '20010101',
    @To datetime = '20090101';

-- Seek with no sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 >= @From
AND T2.c2 < @To
ORDER BY 
    T2.c2;

    Não procure nenhum tipo

    Consulta com tipos incompatíveis

    -- Mismatched types (datetime2 vs datetime)
DECLARE 
    @From datetime2 = '20010101',
    @To datetime2 = '20090101';

-- Merge Interval and Sort
SELECT T2.c2 
FROM dbo.T AS T2 
WHERE T2.c1 = 1 
AND T2.c2 >= @From
AND T2.c2 < @To
ORDER BY 
    T2.c2;

    Intervalo de mesclagem e classificação

    • 22

  2. O índice parece muito bom para a consulta e não sei por que não foi escolhido pelo otimizador (estatísticas? o particionamento? limitação do Azure?, realmente não faço ideia.)

    Mas um índice filtrado seria ainda melhor para a consulta específica, se > 0for um valor fixo e não mudar de uma execução de consulta para outra:

    CREATE NONCLUSTERED INDEX IX_MachineryId_DateRecorded_filtered
    ON dbo.MachineryReading
        (MachineryId, DateRecorded) 
    WHERE (OperationalSeconds > 0) ;

    Existem duas diferenças entre o índice que você tem onde OperationalSecondsé a 3ª coluna e o índice filtrado:

    • Primeiro o índice filtrado é menor, tanto em largura (mais estreito) quanto em número de linhas.
      Isso torna o índice filtrado mais eficiente em geral, pois o SQL Server precisa de menos espaço para mantê-lo na memória.

    • Segundo e isso é mais sutil e importante para a consulta é que ela possui apenas linhas que correspondem ao filtro utilizado na consulta. Isso pode ser extremamente importante, dependendo dos valores desta 3ª coluna.
      Por exemplo, um conjunto específico de parâmetros para MachineryIde DateRecordedpode render 1.000 linhas. Se todas ou quase todas essas linhas corresponderem ao (OperationalSeconds > 0)filtro, ambos os índices se comportarão bem. Mas se as linhas correspondentes ao filtro forem muito poucas (ou apenas a última ou nenhuma), o primeiro índice terá que percorrer muito ou todas essas 1000 linhas até encontrar uma correspondência. O índice filtrado, por outro lado, precisa apenas de uma busca para encontrar uma linha correspondente (ou para retornar 0 linhas), porque apenas as linhas correspondentes ao filtro são armazenadas.

    • 5

relate perguntas