Início / dba / Perguntas / 154227
Accepted
AV94
Asked: 2016-11-04 21:42:28 +0800 CST

Paginação no SQL Server

  • 772

Eu tenho um banco de dados muito grande, aproximadamente 100 GB. Estou executando a consulta:

select * from <table_name>;

e quero mostrar apenas as linhas 100 a 200.

Quero entender como isso acontece internamente. O banco de dados busca todos os registros do disco na memória e envia de volta 100 a 400 linhas para o cliente de consulta? Ou existe algum mecanismo, de modo que apenas esses registros (100º -200º) sejam buscados no banco de dados - usando o mecanismo de indexação como B-trees etc.?

Descobri que isso está relacionado ao conceito de paginação, mas não consegui descobrir exatamente como isso acontece internamente no nível do banco de dados.

sql-server pagination

3 respostas

  1. Best Answer

    Na consulta que você postou:

    select * from <table_name>;

    Não existem linhas 100-200, porque você não especifica um ORDER BY. A ordem não é garantida, a menos que você inclua ORDER BY por vários motivos interessantes, mas esse não é realmente o ponto aqui.

    Então, para ilustrar seu ponto, vamos usar uma tabela - vou usar a tabela Users do despejo de dados Stack Overflow e executar esta consulta:

    SELECT * FROM dbo.Users ORDER BY DisplayName;

    Por padrão, não há índice no campo DisplayName, portanto, o SQL Server precisa examinar a tabela inteira e classificá-la por DisplayName. Eis o plano de execução :

    Verificação de índice clusterizado com uma classificação

    Não é bonito - é muito trabalho, com um custo estimado de subárvore de cerca de 30k. (Você pode vê-lo passando o mouse sobre o operador select em PasteThePlan.) Então, o que acontece se quisermos apenas as linhas 100-200? Podemos usar esta sintaxe no SQL Server 2012+:

    SELECT * FROM dbo.Users ORDER BY DisplayName OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY;

    O plano de execução também é muito feio:

    Varredura de índice agrupado com uma classificação e um topo

    O SQL Server ainda está verificando toda a tabela para criar a lista classificada apenas para fornecer suas linhas 100-200, e o custo ainda está em torno de 30k. Pior ainda, toda essa lista será reconstruída toda vez que sua consulta for executada (porque, afinal, alguém pode ter alterado seu DisplayName).

    Para agilizar, podemos criar um índice não clusterizado em DisplayName, que é uma cópia da nossa tabela, ordenada por aquele campo específico:

    CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);

    Com esse índice, o plano de execução de nossa consulta agora faz uma busca de índice:

    Busca de índice e pesquisa de chave

    A consulta termina instantaneamente e tem um custo estimado de subárvore de apenas 0,66 (em vez de 30k).

    Resumindo, se você organizar os dados de forma a suportar as consultas que executa com frequência, sim, o SQL Server pode usar atalhos para tornar suas consultas mais rápidas. Se, por outro lado, tudo o que você tem são heaps ou índices clusterizados, você está ferrado.

    • 37

  2. Assim como um acréscimo à resposta de Brent ao usar um índice não abrangente para evitar uma classificação, há um possível problema com os números de página posteriores que podem ser vistos na execução abaixo

    SELECT * 
FROM dbo.Users 
ORDER BY DisplayName 
OFFSET 100000 ROWS 
FETCH NEXT 100 ROWS ONLY;

    O plano de execução mostra que a pesquisa foi executada 100.100 vezes, embora apenas 100 linhas sejam filtradas pelo operador TOP.

    insira a descrição da imagem aqui

    Isso pode ser atenuado usando o padrão abaixo

    WITH T
     AS (SELECT Id,
                DisplayName
         FROM   dbo.Users
         ORDER  BY DisplayName
        OFFSET 100000 ROWS 
        FETCH NEXT 100 ROWS ONLY
        )
SELECT U.*
FROM   dbo.Users U
       JOIN T
         ON U.Id = T.Id
ORDER  BY T.DisplayName

    Isso filtra tudo, exceto as 100 linhas finais antes de fazer as pesquisas, que podem ter um impacto significativo na velocidade de grandes valores de deslocamento.

    insira a descrição da imagem aqui

    • 16

  3. Realmente depende de como você implementa a paginação em sua consulta, a natureza dos dados e a forma como seu sistema está configurado. É bastante seguro dizer que o SQL Server tentará retornar seus dados usando o que parece ser o menor esforço possível. Se você não tiver ordem de classificação explícita, filtragem, agrupamento ou qualquer janela, o SQL Server pode otimizar o plano de consulta de forma que ele possa retornar apenas as páginas do disco que continham os dados necessários para sua consulta - ou melhor ainda, diretamente do pool de buffers. Assim que você começa a alterar a consulta para incluir classificação, agrupamento, janelas e filtragem, ela começa a ficar complicada.

    Há um artigo muito bom sobre SQL Performance aqui que detalha alguns métodos de paginação e como eles afetam o plano de consulta. Eu recomendo lê-lo e, em seguida, experimentar alguns dos vários métodos que eles apontam e ver qual plano de consulta é escolhido em seu próprio sistema.

    • 3

relate perguntas