Início / dba / Perguntas / 345297
Accepted
jajanken
Asked: 2025-02-15 02:38:23 +0800 CST

A ordem e a natureza das colunas na lista de seleção da consulta UNION classificada afetam o desempenho

  • 772

Estou observando um impacto significativo no desempenho de listas de seleção em UNIONconsultas classificadas.

O formato geral das UNIONconsultas com as quais estou trabalhando é:

SELECT * FROM (
  SELECT <select_list> FROM <table>
  UNION ALL
  ...
) q
ORDER BY <column>

A seleção externa é usada porque melhora significativamente o desempenho na presença de ORDER BY, mas isso está fora do escopo desta questão. UNION ALLé sempre usado, em vez de UNION.

Vou me referir ao desempenho como "rápido" (instantâneo) ou "lento" (5 segundos ou mais).

O desempenho foi medido executando consultas no console do DBeaver, que por padrão recupera apenas a primeira página, cujo tamanho é 200.

Alterar a lista de seleção afeta o desempenho das seguintes maneiras:

  1. Quando <select_list> = <column>(ou seja, somente a coluna de classificação é selecionada), as consultas são rápidas. O plano de execução mostra varreduras de índice para a coluna de classificação e junções de mesclagem para concatenação.
  2. Quando a lista de seleção inclui outras colunas além da coluna de classificação, as consultas podem ser rápidas ou lentas. Foi observado que quando as seguintes condições são todas verdadeiras, as consultas são rápidas:
    • A lista de seleção inclui a coluna de índice agrupado.
    • A lista de seleção começa com a coluna de índice agrupado ou com a coluna de classificação imediatamente seguida pela coluna de índice agrupado.

As observações foram feitas usando o Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64).

Não consegui encontrar nada sobre os efeitos das listas de seleção no desempenho UNIONna documentação do SQL Server.

Uma descrição simplificada do ambiente em que os experimentos foram conduzidos e as próprias consultas são fornecidas abaixo.

CREATE TABLE AUDIT1 (
    ID bigint NOT NULL,
    AUDITDATE datetime2 NULL,
    [USER] bigint NULL,
  -- Implies clustered index.
    CONSTRAINT PK_AUDIT1 PRIMARY KEY (ID)
);
CREATE INDEX I_AUDIT1_AUDITDATE ON AUDIT1 (AUDITDATE);

CREATE TABLE AUDIT2 (
    ID bigint NOT NULL,
    AUDITDATE datetime2 NULL,
    [USER] bigint NULL,
  -- Implies clustered index.
    CONSTRAINT PK_AUDIT2 PRIMARY KEY (ID)
);
CREATE INDEX I_AUDIT2_AUDITDATE ON AUDIT2 (AUDITDATE);
  • A tabela AUDIT1contém 10 milhões de registros.
  • A tabela AUDIT2contém 1 milhão de registros.
  • Os valores de AUDITDATEin AUDIT2são maiores que aqueles em AUDIT1.
  • Os valores de AUDITDATEsão alinhados com IDem uma sequência crescente, ou seja, IDestá sempre aumentando, e assim é AUDITDATE.

Consulta 1 : a lista de seleção contém apenas a coluna de classificação ( rápida ).

SELECT * FROM (
  SELECT AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

Plano de execução:

|--Merge Join(Concatenation)
   |--Index Scan(AUDIT2.I_AUDIT2_AUDITDATE), ORDERED BACKWARD
   |--Index Scan(AUDIT1.I_AUDIT1_AUDITDATE), ORDERED BACKWARD

Consulta 2 : seleciona lista de tamanho > 1, contém a coluna de classificação, não contém a coluna de índice agrupado ( lento ).

SELECT * FROM (
  SELECT [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

Observe que colocar a coluna de classificação primeiro não teve efeito.

Consulta 3 : selecione uma lista de tamanho > 1, contenha a coluna de classificação, contenha a coluna de índice agrupado, a primeira coluna não é a coluna de classificação nem a coluna de índice agrupado ( lento ).

SELECT * FROM (
  SELECT [USER], ID, AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT [USER], ID, AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

Consulta 4 : selecione uma lista de tamanho > 1, contenha a coluna de classificação, contenha a coluna de índice agrupado, a primeira coluna é a coluna de classificação ou a coluna de índice agrupado ( rápido ).

SELECT * FROM (
  SELECT ID, [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT ID, [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE

Esta consulta mostra que se a primeira coluna for ID, a consulta é rápida.

Os planos de execução para as consultas 2, 3, 4 são os mesmos:

|--Parallelism(Gather Streams, ORDER BY:([Union1007] ASC))
   |--Sort(ORDER BY:([Union1007] ASC))
      |--Concatenation
         |--Parallelism(Distribute Streams, RoundRobin Partitioning)
         |--Clustered Index Scan(OBJECT:(AUDIT2.PK_AUDIT2))
         |--Clustered Index Scan(OBJECT:(AUDIT1.PK_AUDIT1))
sql-server

1 respostas

  1. Best Answer

    O ponto importante é se o SQL Server considera que uma classificação cara é necessária ou não.

    Abordei isso no meu artigo Evitando classificações com concatenação de junção de mesclagem .

    Pontos principais:

    • A concatenação de junção de mesclagem é um modo de execução especial do operador regular de junção de mesclagem, que preserva apenas a ordem das chaves de junção.
    • Não há 'join keys' em uma operação de concatenação. Em vez disso, todas as colunas projetadas da concatenação participam da 'ordem preservada'.
    • Como ponto de partida, as entradas de concatenação de mesclagem são classificadas na lista de projeção de saída.
    • A ORDER BYcláusula pode substituir o ponto inicial para evitar classificação dupla.
    • Quaisquer garantias de exclusividade que existam podem ser usadas pelo otimizador para truncar a lista necessária de colunas classificadas (exceto em trocas em planos paralelos de modo de linha). A IDcoluna desempenha esse papel no seu exemplo. Nenhuma classificação adicional é necessária após uma chave exclusiva.
    • O raciocínio do otimizador com ordens de classificação não é perfeito nem abrangente. Esforços razoáveis ​​são feitos.

    Muitas vezes, você pode ver a ordem de classificação de entrada que o otimizador está buscando adicionando OPTION (MERGE UNION)à consulta de teste e examinando o plano de execução para ver o que as classificações nas entradas de concatenação de junção de mesclagem estão fazendo.

    Por exemplo, isso revela que a consulta 2 quer uma entrada ordenada por (AUDITDATE ASC, [USER] ASC), o que os índices não podem fornecer.

    Um índice sobre (AUDITDATE ASC, [USER] ASC)ou (AUDITDATE DESC, [USER] DESC)poderia fornecer essa ordem.

    A consulta 3 quer (AUDITDATE ASC, [USER] ASC, ID ASC).

    A consulta 4 é rápida porque a coluna exclusiva garantida IDé listada primeiro. Essa exclusividade significa que não é mais necessária nenhuma ordenação depois de AUDITDATE, ID. O requisito para classificar em AUDITDATEvem da ORDER BYcláusula. IDé necessário para a mesclagem. Nenhuma outra ordenação é necessária para a mesclagem porque IDé exclusivo.

    A AUDITDATE, IDordem pode ser fornecida pelo índice não clusterizado (em virtude de não ser exclusivo, então o ID é parte da chave).

    SELECT * FROM (
  SELECT ID, [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT ID, [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE
OPTION (MERGE UNION);

    plano

    Você pode ver que o índice não clusterizado fornece ordem em AUDITDATE, ID:

    SELECT A.AUDITDATE, A.ID 
FROM dbo.AUDIT1 AS A 
ORDER BY A.AUDITDATE, A.ID;

    plano

    Agora vejo que merge join é usado em todas as consultas. Somente para consultas rápidas, no entanto, a classificação é evitada.

    Sim, essa é a questão principal. Evitar a classificação, onde isso é possível, geralmente é benéfico para o desempenho.

    Sua declaração "a cláusula ORDER BY pode substituir o ponto de partida para evitar classificação dupla." não parece se aplicar às consultas 2 e 3, que são lentas apesar do ORDER BY. Parece depender da ordem das colunas na lista de seleção.

    Sim, como eu disse, a lista de projeção é o ponto de partida. A ORDER BYcláusula pode substituir isso se for um ORDER BYarranjo adequado para os requisitos da mesclagem. Isso não significa que sempre será . Eu abordo isso extensivamente com exemplos no artigo.

    Você poderia elaborar o comentário no seu snippet SQL perto de ORDER BY? Por que deveria haver compatibilidade entre "merge concat output order" e sorting columns?

    A ordem de apresentação precisa ser compatível com uma ordenação que a mesclagem pode usar para evitar uma classificação.

    Além disso, se você pegar a consulta 3 e substituir *na seleção externa por ID, [USER], AUDITDATE- ela continuará lenta.

    Isso está fora da mesclagem. O seguinte usa essa ordem e pode evitar uma classificação:

    SELECT [USER], ID, AUDITDATE FROM (
  SELECT ID, [USER], AUDITDATE FROM AUDIT2
  UNION ALL 
  SELECT ID, [USER], AUDITDATE FROM AUDIT1
) q
ORDER BY AUDITDATE
OPTION (MERGE UNION);

    plano

    Observe que a pesquisa de chave necessária ainda pode tornar a consulta 'lenta', mas isso é um problema separado. Você pode, é claro, obter um plano diferente com os dados que tem.

    Por outro lado, pegue a consulta 4 e substitua *por [USER], ID, AUDITDATE- ela continua rápida.

    Sim, porque o ID com sua garantia de exclusividade ainda é o primeiro na fusão.

    Por fim, adicionar OPTION(MERGE UNION)à consulta 4 a torna lenta, com o plano sendo semelhante ao da consulta 3, mas com paralelismo adicional.

    O ponto é: Um plano sem classificação é possível . O otimizador considera muitas alternativas e escolhe a que custa menos. Isso pode muito bem ainda ser "lento" devido a, por exemplo, pesquisas como já mencionado.

    Um exemplo um pouco mais avançado mostrando a tensão entre a ordem de apresentação solicitada, colunas projetadas, preservação da ordem de mesclagem, raciocínio do otimizador, estimativa de custo e ordem de índice:

    SELECT
    Q.AUDITDATE, 
    Q.ID, 
    Q.[USER] 
FROM 
(
    SELECT 
        A2.AUDITDATE, 
        A2.ID, 
        A2.[USER] 
    FROM AUDIT2 AS A2

    UNION ALL

    SELECT 
        A1.AUDITDATE, 
        A1.ID, 
        A1.[USER] 
    FROM AUDIT1 A1
) AS Q
WHERE
    -- Give the optimizer a reason to read the index backwards
    Q.AUDITDATE <= CONVERT(datetime2(7), '9999-12-31T23:59:59.9999999', 126)
ORDER BY
    -- Compatible with merge concat output order
    Q.AUDITDATE DESC,
    Q.ID DESC
OPTION 
(
    MERGE UNION
);

    Plano de execução sem triagem

    Como nota lateral, USERé uma escolha ruim de nome de coluna. É um erro de sintaxe, a menos que esteja entre aspas, porque é uma função niladic do sistema .

    • 4

relate perguntas