Início / dba / Perguntas / 19203
Accepted
m__
Asked: 2012-06-14 09:13:44 +0800 CST

Ordem de armazenamento vs ordem de resultado

  • 772

Esta é uma pergunta derivada da ordem de classificação especificada na chave primária, mas a classificação é executada em SELECT .

@Catcall diz isso sobre o assunto da ordem de armazenamento (índice clusterizado) e a ordem de saída

Muitas pessoas acreditam que um índice clusterizado garante uma ordem de classificação na saída. Mas não é isso que ele faz; garante uma ordem de armazenamento em disco. Veja, por exemplo, esta postagem no blog .

Li a postagem do blog de Hugo Kornelis e entendo que um índice não garante que o sql server leia os registros em uma ordem específica. No entanto, tenho dificuldade em aceitar que não posso assumir isso para o meu cenário.

CREATE TABLE [dbo].[SensorValues](
  [DeviceId] [int] NOT NULL,
  [SensorId] [int] NOT NULL,
  [SensorValue] [int] NOT NULL,
  [Date] [int] NOT NULL,
CONSTRAINT [PK_SensorValues] PRIMARY KEY CLUSTERED 
(
  [DeviceId] ASC,
  [SensorId] ASC,
  [Date] DESC
) WITH (
    FILLFACTOR=75,
    DATA_COMPRESSION = PAGE,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON)
  ON [MyPartitioningScheme]([Date])

Minha consulta original era esta:

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC

Mas sugiro que também poderia usar este (leia abaixo minha explicação):

SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010

Como você pode ver, as linhas da minha tabela são pequenas (16 bytes) e tenho apenas um índice, um clusterizado. No meu cenário, a tabela consiste em 100.000.000 registros neste momento (e isso provavelmente aumentará dez vezes).

Quando o servidor de banco de dados consulta esta tabela, ele tem duas maneiras de encontrar minhas linhas: busca a chave primária e, assim, lê e retorna meus valores em desc. ordem de Data, ou tem que fazer uma varredura completa da tabela. Minha conclusão é que uma varredura completa da tabela em todos esses registros será muito lenta e, portanto, o servidor de banco de dados sempre buscará a tabela por meio de sua chave primária e, assim, retornará os valores classificados porDate DESC

sql-server execution-plan

1 respostas

  1. Best Answer

    Deixe-me tentar explicar por que você não deve fazer isso, por que você nunca deve presumir que um produto SQL retornará um conjunto de resultados em uma ordem específica, a menos que você especifique, quaisquer que sejam os índices - agrupados ou não agrupados, árvores B ou R-Trees ou kd-trees ou fractal-trees ou quaisquer outros índices exóticos que um DBMS esteja usando.

    Sua consulta original diz ao DBMS para pesquisar a SensorValuestabela, encontrar linhas que correspondam às 3 condições, ordene essas linhas por ordem Datedecrescente, mantenha apenas a primeira linha delas e - finalmente - selecione e retorne apenas a SensorValuecoluna.

    SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date DESC ;

    Estas são ordens muito específicas que você deu ao DBMS e o resultado provavelmente será o mesmo toda vez que você executar a consulta (há uma chance de que não, se você tiver mais de uma linha que corresponda às condições e tenha o mesmo max Date, mas diferente SensorValue, mas vamos assumir para o resto da conversa que não existem tais linhas em sua tabela).

    O DBMS precisa fazer isso, para executar essa consulta, da maneira exata que descrevi acima? Não, claro que não e você sabe disso. Pode não ler a tabela, mas ler a partir de um índice. Ou pode usar dois índices se achar melhor (mais rápido). Ou três. Ou pode usar um resultado em cache (não SQL Server, mas outros resultados de consulta de cache do DBMS). Ou pode usar execução paralela uma vez e não na próxima vez que for executado. Ou ... (adicione qualquer outro recurso que afete a execução e os planos de execução).

    O que é garantido, porém, é que ele retornará exatamente o mesmo resultado, toda vez que você executá-lo - desde que nenhuma linha seja inserida, excluída ou atualizada.

    Agora vamos ver o que sua sugestão diz:

    SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010 ;

    Esta consulta diz ao DBMS para pesquisar a SensorValuestabela, encontrar linhas que correspondam às 3 condições, ordene essas linhas por ordem Datedecrescente, não se preocupe com a ordem, mantenha apenas uma linha e - finalmente - selecione e retorne apenas a SensorValuecoluna.

    Portanto, basicamente diz o mesmo que o primeiro, exceto que diz que você deseja apenas um resultado que corresponda às condições e não se importa com qual .

    Agora, podemos assumir que dará sempre o mesmo resultado por causa do índice clusterizado?
    - Se usar esse índice clusterizado todas as vezes, sim.

    Mas será que vai usar?
    - Não.

    Por que não?
    - Porque pode. O otimizador de consulta é livre para escolher um caminho de execução toda vez que executa uma instrução. Seja qual for o caminho que achar adequado naquele momento para essa declaração.

    Mas usar o índice clusterizado não é a melhor/rápida maneira de obter resultados?
    - Não, nem sempre. Pode ser a primeira vez que você executa a consulta. Na segunda vez, ele pode usar um resultado em cache (se o DBMS tiver esse recurso, não o SQL Server * ). A milésima vez o resultado pode ter sido removido do cache e outro resultado pode existir lá. Digamos que você tenha executado esta consulta antes:

    SELECT TOP 1 SensorValue
  FROM SensorValues
  WHERE SensorId = 53
    AND DeviceId = 3819
    AND Date < 1339225010
  ORDER BY Date ASC ;         --- Notice the `ASC` here

    e o resultado em cache (da consulta acima) é outro, diferente, que ainda corresponde às suas condições, mas não é o primeiro em seu pedido (desejado). E você disse ao DBMS para não se importar com o pedido.

    OK, então apenas o cache pode afetar isso?
    - Não, muitas outras coisas também.

    • outros índices foram considerados, na época pelo SGBD como melhores para esta consulta.
    • um desenvolvedor alterou ou removeu completamente esse índice clusterizado que você tinha.
    • você ou algum outro desenvolvedor adicionou outro índice que o otimizador decidiu que é mais eficiente de usar do que o CI.
    • você atualizou para uma nova versão e o novo otimizador tem um pequeno bug ou uma mudança na forma como ele classifica e escolhe os planos de execução.
    • estatísticas foram atualizadas.
    • a execução paralela foi escolhida.

    *: O SQL Server não armazena em cache os resultados da consulta, mas a Enterprise Edition possui um recurso de verificação avançada que é semelhante, pois você pode obter resultados diferentes devido a consultas simultâneas. Não tenho certeza exatamente quando isso entra em ação. (obrigado @Martin Smith pela dica.)

    Espero que você esteja convencido de que nunca deve confiar que uma consulta SQL retornará resultados em uma ordem específica, a menos que você especifique isso. E nunca use TOP (n)sem ORDER BY, a menos que você queira apenas n linhas no resultado e não se importe com quais são retornadas.

    • 16

relate perguntas