Olá a todos! Sou um desenvolvedor backend escrevendo microsserviços em Java + Spring. Trabalho em uma das equipes internas de desenvolvimento de produtos da Tinkoff.
Em nossa equipe, surge frequentemente a questão de otimizar consultas em um SGBD. Você sempre quer ser um pouco mais rápido, mas nem sempre consegue sobreviver com índices cuidadosamente construídos – é preciso procurar algumas soluções alternativas. Durante uma dessas andanças pela web em busca de otimizações razoáveis ao trabalhar com bancos de dados, descobri , autor de Desempenho SQL explicado. Esse é aquele raro tipo de blog em que você pode ler todos os artigos seguidos.
Gostaria de traduzir um pequeno artigo de Marcus para você. Pode ser chamado, até certo ponto, de um manifesto que busca chamar a atenção para o antigo, mas ainda relevante problema do desempenho da operação de deslocamento de acordo com o padrão SQL.
Em alguns lugares complementarei o autor com explicações e comentários. Vou me referir a todos esses lugares como “aproximadamente”. para mais clareza
Pequena introdução
Acho que muitas pessoas sabem como é problemático e lento trabalhar com seleções de páginas via offset. Você sabia que ele pode ser facilmente substituído por um design mais eficiente?
Portanto, a palavra-chave offset informa ao banco de dados para pular os primeiros n registros da solicitação. Porém, o banco de dados ainda precisa ler esses primeiros n registros do disco, na ordem dada (nota: aplicar ordenação se for especificado), e só então será possível retornar registros de n+1 em diante. O mais interessante é que o problema não está na implementação específica no SGBD, mas na definição original de acordo com o padrão:
…as linhas são primeiro classificadas de acordo com a e depois limitadas eliminando o número de linhas especificadas na desde o início…
-SQL:2016, Parte 2, 4.15.3 Tabelas derivadas (nota: atualmente o padrão mais usado)
O ponto principal aqui é que o deslocamento usa um único parâmetro - o número de registros a serem ignorados e pronto. Seguindo esta definição, o SGBD só pode recuperar todos os registros e então descartar os desnecessários. Obviamente, esta definição de compensação obriga-nos a fazer um trabalho extra. E nem importa se é SQL ou NoSQL.
Só um pouco mais de dor
Os problemas com a compensação não param por aí e aqui está o porquê. Se, entre a leitura de duas páginas de dados do disco, outra operação inserir um novo registro, o que acontecerá neste caso?
Quando o deslocamento é usado para pular registros de páginas anteriores, na situação de adicionar um novo registro entre leituras de páginas diferentes, você provavelmente obterá duplicatas (nota: isso é possível quando lemos página por página usando a construção ordenar por, então no meio da nossa saída pode receber uma nova entrada).
A figura retrata claramente esta situação. A base lê os primeiros 10 registros, após os quais um novo registro é inserido, que compensa todos os registros lidos em 1. Em seguida, a base pega uma nova página dos próximos 10 registros e começa não a partir do 11, como deveria, mas a partir do 10º, duplicando este registro. Existem outras anomalias associadas ao uso desta expressão, mas esta é a mais comum.
Como já descobrimos, estes não são problemas de um SGBD específico ou de suas implementações. O problema está em definir a paginação de acordo com o padrão SQL. Dizemos ao SGBD qual página buscar ou quantos registros pular. O banco de dados simplesmente não consegue otimizar tal solicitação, pois há poucas informações para isso.
Vale esclarecer também que não se trata de um problema de uma palavra-chave específica, mas sim da semântica da consulta. Existem várias outras sintaxes que são idênticas em sua natureza problemática:
- A palavra-chave offset é mencionada anteriormente.
- Uma construção de duas palavras-chave limit [offset] (embora o limite em si não seja tão ruim).
- Filtragem por limites inferiores, com base na numeração de linhas (por exemplo, row_number(), rownum, etc.).
Todas essas expressões simplesmente informam quantas linhas pular, sem informações ou contexto adicionais.
Posteriormente neste artigo, a palavra-chave offset é usada como um resumo de todas essas opções.
Vida sem OFFSET
Agora vamos imaginar como seria o nosso mundo sem todos esses problemas. Acontece que a vida sem deslocamento não é tão difícil: com um select, você pode selecionar apenas aquelas linhas que ainda não vimos (nota: ou seja, aquelas que não estavam na página anterior), usando uma condição em where.
Neste caso, partimos do fato de que os selects são executados em um conjunto ordenado (o bom e velho order by). Como temos um conjunto ordenado, podemos usar um filtro bastante simples para obter apenas os dados que estão por trás do último registro da página anterior:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYEsse é todo o princípio desta abordagem. É claro que as coisas ficam mais divertidas ao classificar por muitas colunas, mas a ideia ainda é a mesma. É importante notar que este design é aplicável a muitos -decisões.
Essa abordagem é chamada de método de busca ou paginação de conjunto de chaves. Ele resolve o problema do resultado flutuante (nota: a situação de escrita entre leituras de páginas descrita anteriormente) e, claro, o que todos nós amamos, funciona mais rápido e estável do que o deslocamento clássico. A estabilidade reside no fato de que o tempo de processamento da solicitação não aumenta proporcionalmente ao número da tabela solicitada (nota: se você quiser saber mais sobre o trabalho das diferentes abordagens de paginação, você pode . Você também pode encontrar referências comparativas para diferentes métodos lá).
Um dos slides que a paginação por chaves, é claro, não é onipotente – ela tem suas limitações. O mais significativo é que ela não consegue ler páginas aleatórias (nota: de forma inconsistente). No entanto, na era da rolagem sem fim (nota: no front-end), isso não é um problema. De qualquer forma, especificar um número de página para clicar é uma má decisão no design da IU (nota: opinião do autor do artigo).
E as ferramentas?
A paginação em teclas muitas vezes não é adequada devido à falta de suporte instrumental para este método. A maioria das ferramentas de desenvolvimento, incluindo vários frameworks, não permitem escolher exatamente como a paginação será executada.
A situação é agravada pelo fato de o método descrito exigir suporte ponta a ponta nas tecnologias utilizadas - desde o SGBD até a execução de uma solicitação AJAX no navegador com rolagem infinita. Em vez de especificar apenas o número da página, agora você precisa especificar um conjunto de chaves para todas as páginas de uma vez.
No entanto, o número de estruturas que suportam paginação em chaves está crescendo gradualmente. Aqui está o que temos no momento:
- para Java;
- para Rubi;
- и para Django;
- para Python;
- — API de critérios para implementações de JPA;
- para Perl;
- , mapeador para Node.js .
(Nota: alguns links foram removidos devido ao fato de que no momento da tradução algumas bibliotecas não haviam sido atualizadas desde 2017-2018. Se você estiver interessado, pode consultar a fonte original.)
É neste momento que a sua ajuda é necessária. Se você desenvolve ou oferece suporte a uma estrutura que faz uso de paginação, então peço, peço e imploro que forneça suporte nativo para paginação em chaves. Se você tiver dúvidas ou precisar de ajuda, ficarei feliz em ajudar (, , ) (nota: pela minha experiência com Marcus, posso dizer que ele está muito entusiasmado em divulgar esse assunto).
Se você utiliza soluções prontas que acha dignas de ter suporte para paginação por chaves, crie uma requisição ou até mesmo ofereça uma solução pronta, se possível. Você também pode criar um link para este artigo.
Conclusão
A razão pela qual uma abordagem tão simples e útil como a paginação por chaves não é difundida não é porque seja difícil de implementar tecnicamente ou exija grande esforço. A principal razão é que muitos estão acostumados a ver e trabalhar com offset - essa abordagem é ditada pelo próprio padrão.
Como resultado, poucas pessoas pensam em mudar a abordagem da paginação e, por causa disso, o suporte instrumental de frameworks e bibliotecas está se desenvolvendo mal. Portanto, se a ideia e o objetivo da paginação livre de offset está perto de você, ajude a divulgá-la!
Fonte:
Autor: Markus Winand
Fonte: habr.com