Evite usar OFFSET e LIMIT em consultas paginadas

Já se foi o tempo em que você não precisava se preocupar em otimizar o desempenho do banco de dados. O tempo não pára. Todo novo empreendedor de tecnologia deseja criar o próximo Facebook, enquanto tenta coletar todos os dados que puder. As empresas precisam desses dados para treinar melhor modelos que as ajudem a ganhar dinheiro. Nessas condições, os programadores precisam criar APIs que lhes permitam trabalhar de forma rápida e confiável com grandes quantidades de informações.

Se você projeta back-ends de aplicativos ou bancos de dados há algum tempo, provavelmente já escreveu código para executar consultas paginadas. Por exemplo, assim:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Do jeito que é?

Mas se foi assim que você fez sua paginação, lamento dizer que você não fez da maneira mais eficiente.

Você quer se opor a mim? Você pode não gastar tempo. Slack, Shopify и mixmax Eles já estão utilizando as técnicas sobre as quais quero falar hoje.

Cite pelo menos um desenvolvedor backend que nunca usou OFFSET и LIMIT para realizar consultas paginadas. Em MVP (Produto Mínimo Viável) e em projetos onde são utilizadas pequenas quantidades de dados, esta abordagem é bastante aplicável. “Simplesmente funciona”, por assim dizer.

Mas se você precisa criar sistemas confiáveis ​​e eficientes do zero, você deve se preocupar antecipadamente com a eficiência da consulta aos bancos de dados usados ​​em tais sistemas.

Hoje falaremos sobre os problemas com implementações comumente usadas (que pena) de mecanismos de consulta paginados e como obter alto desempenho ao executar tais consultas.

O que há de errado com OFFSET e LIMIT?

Como já mencionado, OFFSET и LIMIT Eles apresentam bom desempenho em projetos que não precisam trabalhar com grandes quantidades de dados.

O problema surge quando o banco de dados cresce a tal ponto que não cabe mais na memória do servidor. No entanto, ao trabalhar com este banco de dados, você precisa usar consultas paginadas.

Para que este problema se manifeste, deve haver uma situação em que o SGBD recorra a uma operação ineficiente de Full Table Scan em cada consulta paginada (embora possam ocorrer operações de inserção e exclusão, e não precisamos de dados desatualizados!).

O que é uma “varredura completa de tabela” (ou “varredura sequencial de tabela”, varredura sequencial)? Esta é uma operação durante a qual o SGBD lê sequencialmente cada linha da tabela, ou seja, os dados nela contidos, e verifica o cumprimento de uma determinada condição. Esse tipo de varredura de tabela é conhecido por ser o mais lento. O fato é que quando ele é executado, são realizadas muitas operações de entrada/saída que envolvem o subsistema de disco do servidor. A situação é agravada pela latência associada ao trabalho com dados armazenados em discos e pelo fato de que a transferência de dados do disco para a memória é uma operação que consome muitos recursos.

Por exemplo, você tem registros de 100000000 milhões de usuários e executa uma consulta com a construção OFFSET 50000000. Isso significa que o SGBD terá que carregar todos esses registros (e nem precisamos deles!), colocá-los na memória e depois disso pegar, digamos, 20 resultados reportados em LIMIT.

Digamos que seja assim: "selecione as linhas de 50000 a 50020 de 100000". Ou seja, primeiro o sistema precisará carregar 50000 linhas para concluir a consulta. Você vê quanto trabalho desnecessário ela terá que fazer?

Se você não acredita em mim, dê uma olhada no exemplo que criei usando os recursos db-fiddle.com. 

Exemplo em db-fiddle.com

Ali, à esquerda, no campo Schema SQL, há um código que insere 100000 linhas no banco de dados, e à direita, no campo Query SQL, duas consultas são mostradas. O primeiro, lento, fica assim:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

E a segunda, que é uma solução eficaz para o mesmo problema, é assim:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Para atender a essas solicitações, basta clicar no botão Run no topo da página. Feito isso, comparamos as informações sobre o tempo de execução da consulta. Acontece que a execução de uma consulta ineficiente leva pelo menos 30 vezes mais tempo do que a execução da segunda (esse tempo varia de execução para execução; por exemplo, o sistema pode reportar que a primeira consulta demorou 37 ms para ser concluída, mas a execução da segundo - 1ms).

E se houver mais dados, tudo ficará ainda pior (para se convencer disso, dê uma olhada no meu exemplo com 10 milhões de linhas).

O que acabamos de discutir deve lhe dar algumas dicas sobre como as consultas ao banco de dados são realmente processadas.

Observe que quanto maior o valor OFFSET — mais tempo a solicitação levará para ser concluída.

O que devo usar em vez da combinação de OFFSET e LIMIT?

Em vez de uma combinação OFFSET и LIMIT Vale a pena utilizar uma estrutura construída conforme o seguinte esquema:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Esta é a execução de consulta com paginação baseada em cursor.

Em vez de armazenar os atuais localmente OFFSET и LIMIT e transmiti-los com cada solicitação, você precisa armazenar a última chave primária recebida (geralmente é ID) E LIMIT, como resultado, serão obtidas consultas semelhantes às acima.

Por que? A questão é que, ao especificar explicitamente o identificador da última linha lida, você informa ao seu SGBD onde ele precisa começar a procurar os dados necessários. Além disso, a pesquisa, graças ao uso da chave, será realizada de forma eficiente, o sistema não terá que se distrair com linhas fora da faixa especificada.

Vamos dar uma olhada na seguinte comparação de desempenho de várias consultas. Aqui está uma consulta ineficaz.

Solicitação lenta

E aqui está uma versão otimizada desta solicitação.

Solicitação rápida

Ambas as consultas retornam exatamente a mesma quantidade de dados. Mas o primeiro leva 12,80 segundos para ser concluído e o segundo leva 0,01 segundos. Você sente a diferença?

Possíveis problemas

Para que o método de consulta proposto funcione de forma eficaz, a tabela deve ter uma coluna (ou colunas) contendo índices sequenciais exclusivos, como um identificador inteiro. Em alguns casos específicos, isso pode determinar o sucesso do uso de tais consultas para aumentar a velocidade de trabalho com o banco de dados.

Naturalmente, ao construir consultas, é necessário levar em consideração a arquitetura específica das tabelas e escolher os mecanismos que funcionarão melhor nas tabelas existentes. Por exemplo, se você precisa trabalhar em consultas com grandes volumes de dados relacionados, pode achar interessante isso artigo.

Se nos depararmos com o problema de falta de uma chave primária, por exemplo, se tivermos uma tabela com um relacionamento muitos-para-muitos, então a abordagem tradicional de usar OFFSET и LIMIT, é garantido que nos servirá. Mas seu uso pode resultar em consultas potencialmente lentas. Nesses casos, eu recomendaria usar uma chave primária com incremento automático, mesmo que seja necessária apenas para lidar com consultas paginadas.

Se você estiver interessado neste tópico - aqui, aqui и aqui - vários materiais úteis.

Resultados de

A principal conclusão que podemos tirar é que, independentemente do tamanho das bases de dados de que estamos falando, é sempre necessário analisar a velocidade de execução das consultas. Hoje em dia a escalabilidade das soluções é extremamente importante, e se tudo for projetado corretamente desde o início do trabalho em um determinado sistema, isso, no futuro, pode salvar o desenvolvedor de muitos problemas.

Como você analisa e otimiza consultas ao banco de dados?

Fonte: habr.com