Início / dba / Perguntas / 299708
Accepted
Doug Coats
Asked: 2021-09-17 07:42:58 +0800 CST

Ordem de Definição de Índice e Cláusula ORDER BY

  • 772

Então, eu estava lendo meu blog matinal e me deparei com este exercício divertido:

https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/

Aqui está a consulta em questão do artigo e o índice que ele propõe.

SELECT TOP (5000)
       p.LastActivityDate,
       p.PostTypeId,
       p.Score,
       p.ViewCount
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND   p.LastActivityDate >= '20110101'
ORDER BY p.Score DESC;

CREATE INDEX whatever 
    ON dbo.Posts(PostTypeId, Score DESC, LastActivityDate) 
        INCLUDE(ViewCount) WITH (DROP_EXISTING = ON);

Muito divertido construir e indexar e tentar ajustá-lo de acordo. No entanto, eu estava anteriormente sob o possível mal-entendido de que a ordem da chave do índice é importante e que certas cláusulas WHERE podem não usar determinados índices quando a ordem da chave do índice não corresponde à consulta. Ou seja, minha falta de experiência com o cenário específico listado, meu pensamento assumido seria que essa consulta não usaria esse índice porque Score está no meio da definição da chave de índice, mas não na cláusula where da consulta.

As colunas ORDER BY são avaliadas quando o otimizador decide qual índice usar e, desde que as colunas da cláusula WHERE e as colunas ORDER by estejam na definição do índice, ele será usado?

Acho que minha pergunta é mais sobre como o otimizador avalia índices em relação às cláusulas WHERE e ORDER BY.

sql-server index

1 respostas

  1. Best Answer

    A ordem das chaves definitivamente importa. Os dois índices sugeridos adotam uma abordagem diferente para resolvê-lo.

    Vamos pensar em como a consulta é executada quando o índice está em (PostTypeID, LastActivityDate), pensando em fazê-lo manualmente.

    Podemos encontrar facilmente todas as linhas que correspondem à cláusula WHERE - PostTypeID 1 e recentes o suficiente. Mas então precisamos classificá-las por Score para encontrar as 5.000 melhores. Se tivermos MUITAS linhas para classificar, isso pode ser caro.

    Como alternativa, com um índice em (PostTypeID, Score DESC), podemos filtrar apenas para o PostTypeID, mas podemos passar por esses dados na ordem correta. Sim, temos que rejeitar todas as linhas que não sejam recentes o suficiente com base em LastActivityDate, mas assim que encontrarmos as 5.000 linhas com as quais nos importamos, podemos parar. Nós não temos que fazer esse tipo caro. Mas estamos examinando mais linhas do que nos importamos. E, aliás, não acho que haja muito valor em ter LastActivityDate nas colunas-chave - ele se encaixaria tão bem nas colunas incluídas, pois não está desempenhando um papel no predicado Seek. O operador Seek está simplesmente retornando todas as linhas para esse valor PostTypeID na ordem de pontuação.

    O Query Optimizer sabe que os dados podem ser extraídos de um índice na ordem especificada no índice e isso pode beneficiá-lo de várias maneiras. Talvez ajude para um Merge Join, ou um Stream Aggregate, ou para uma cláusula ORDER BY.

    Se descobrirmos que PostTypeID é filtrado para um único valor, um índice está em Score para evitar uma classificação, enquanto o outro está em LastActivityDate para diminuir o intervalo de busca. O QO pesa o custo esperado de cada um e escolhe o 'mais barato'.

    • 7

relate perguntas