Início / dba / Perguntas / 140425
Accepted
Jonesome Reinstate Monica
Asked: 2016-06-05 23:32:12 +0800 CST

Qual índice será usado neste cenário?

  • 772

SQL Server 2014 Standard Edition

Preciso encontrar o número de voos de e para cidades específicas em determinados meses. Por exemplo

select count(*) 
from flights 
where flightTo_AirportCode = 'aaaa' 
and flightFrom_Airportcode = 'bbbb' 
and flightdate < '2016-04-01' 
and flightdate > '2016-02-28' ;

O esquema da tabela está abaixo.

Estou tentando estimar se o modelo de índiceA ou o modelo de índiceB (abaixo) é preferível (leva muitas horas para construir o índice e o espaço em disco permite que apenas um exista por vez, então estou tentando olhar antes de saltar).

Pela minha experiência, qualquer um dos índices serve. Estou certo?

  create index [modelA] on flights (flightTo_AirportCode, flightFrom_AirportCode, flightDate)

  create index [modelB] on flights (flightDate, flightTo_AirportCode, flightFrom_AirportCode)

(Ou, melhor, existe um índice binário ou mecanismo avançado que posso usar para abordar isso?)

CREATE TABLE [dbo].[flights](
    [flightId] [uniqueidentifier] NOT NULL,
    [accountId] [uniqueidentifier] NULL,
    [flightDate] [datetime] NULL,
    [flightTo_AirportCode] [nvarchar](30) NULL,
    [flightFrom_AirportCode] [nvarchar](30) NULL,
    -- ... 45 more fields
    CONSTRAINT [PK_flight] PRIMARY KEY CLUSTERED 
(
    [flightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
sql-server sql-server-2014

1 respostas

  1. Best Answer

    O índice A é melhor para esta consulta. Quando todas as condições no WHEREforem verificações de igualdade, exceto uma que esteja usando uma condição de intervalo ou INoperador em uma coluna, a última coluna deverá ser a última no índice, depois de todas as colunas que tiverem uma verificação de igualdade.

    Isso permite que o otimizador use uma busca de índice para a primeira linha que corresponda às condições e, em seguida, atravesse o índice até encontrar uma linha que não corresponda a ela. Todas as linhas intermediárias também correspondem.

    Portanto, o melhor índice para esta consulta seria (to, from, date)(seu modelo A) ou (from, to, date).

    O índice do modelo B tem a data primeiro, portanto não é o melhor, embora ainda seja um índice de cobertura para a consulta. Se isso fosse usado, o plano de consulta seria quase o mesmo. Um índice procura encontrar a primeira linha que corresponde à condição de intervalo ( date > '2016-02-28') e, em seguida, percorre o índice até encontrar uma linha que não corresponda ao date < '2016-04-01'. Mas todas as linhas intermediárias não correspondem necessariamente às outras 2 condições, portanto, elas teriam que ser verificadas em relação a essas condições e (possivelmente muitas delas) rejeitadas.

    Portanto, embora os planos sejam semelhantes, o plano do modelo A teria que passar apenas pela parte do índice que possui todas as linhas necessárias e apenas elas, enquanto o plano do modelo B passaria por uma parte (possivelmente muito) maior do índice.

    • Também seria melhor usar um formato 100% seguro para datas ( YYYYMMDD).

    • E se você quiser as datas em março, deve usar um cheque inclusivo-exclusivo:

      AND flightdate >= '20160301' AND flightdate < '20160401'

      Garantido para trabalhar com tipos de data e data e hora. Sua consulta atual incluirá também qualquer linha que tenha '2016-02-28'apenas um horário diferente de '00:00:00'(você pode garantir que não há nenhum?) que presumo que você não queira. O método inclusivo-exclusivo também funcionará em anos bissextos (lembrando que 2016 é um ano bissexto, então havia uma data de 29 de fevereiro também que sua consulta retornará).

    Leia também estas postagens de blog de Aaron Bertrand:

    • 18

relate perguntas