Problemas de saída e desempenho dos resultados da pesquisa

Um dos cenários típicos em todas as aplicações que conhecemos é a busca de dados de acordo com determinados critérios e sua exibição em um formato de fácil leitura. Também pode haver opções adicionais para classificação, agrupamento e paginação. A tarefa é, em teoria, trivial, mas ao resolvê-la muitos desenvolvedores cometem uma série de erros, que posteriormente prejudicam a produtividade. Vamos tentar considerar várias opções para resolver este problema e formular recomendações para escolher a implementação mais eficaz.

Opção de paginação nº 1

A opção mais simples que vem à mente é a exibição página por página dos resultados da pesquisa em sua forma mais clássica.

Digamos que seu aplicativo use um banco de dados relacional. Neste caso, para exibir informações neste formulário, você precisará executar duas consultas SQL:

• Obtenha linhas para a página atual.
• Calcule o número total de linhas correspondentes aos critérios de pesquisa - isso é necessário para exibir as páginas.

Vejamos a primeira consulta usando um banco de dados MS SQL de teste como exemplo AdventureWorks para servidor de 2016. Para isso utilizaremos a tabela Sales.SalesOrderHeader:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

A consulta acima retornará os 50 primeiros pedidos da lista, ordenados por data decrescente de adição, ou seja, os 50 pedidos mais recentes.

Ele é executado rapidamente na base de teste, mas vamos dar uma olhada no plano de execução e nas estatísticas de E/S:

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Você pode obter estatísticas de E/S para cada consulta executando o comando SET STATISTICS IO ON no tempo de execução da consulta.

Como você pode ver no plano de execução, a opção que consome mais recursos é classificar todas as linhas da tabela de origem por data de adição. E o problema é que quanto mais linhas aparecerem na tabela, mais “difícil” será a ordenação. Na prática, tais situações devem ser evitadas, então vamos adicionar um índice à data de adição e ver se o consumo de recursos mudou:

Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Obviamente melhorou muito. Mas todos os problemas estão resolvidos? Vamos alterar a consulta para procurar pedidos cujo custo total das mercadorias exceda US$ 100:

SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Temos uma situação engraçada: o plano de consulta não é muito pior que o anterior, mas o número real de leituras lógicas é quase duas vezes maior que o de uma varredura completa da tabela. Há uma saída - se fizermos um índice composto a partir de um índice já existente e adicionarmos o preço total dos bens como o segundo campo, obteremos novamente 165 leituras lógicas:

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

Esta série de exemplos pode continuar por muito tempo, mas os dois pensamentos principais que quero expressar aqui são:

• Adicionar qualquer novo critério ou ordem de classificação a uma consulta de pesquisa pode ter um impacto significativo na velocidade da consulta de pesquisa.
• Mas se precisarmos subtrair apenas parte dos dados, e não todos os resultados que correspondem aos termos de pesquisa, há muitas maneiras de otimizar tal consulta.

Agora vamos passar para a segunda consulta mencionada no início - aquela que conta o número de registros que satisfazem o critério de pesquisa. Vejamos o mesmo exemplo - pesquisando pedidos superiores a US$ 100:

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

Dado o índice composto indicado acima, obtemos:

Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

O fato da consulta percorrer todo o índice não surpreende, pois o campo SubTotal não está na primeira posição, portanto a consulta não pode utilizá-lo. O problema é resolvido adicionando outro índice no campo SubTotal e, como resultado, fornece apenas 48 leituras lógicas.

Você pode dar mais alguns exemplos de solicitações de contagem de quantidades, mas a essência permanece a mesma: receber um dado e contar o valor total são duas solicitações fundamentalmente diferentes, e cada um requer suas próprias medidas para otimização. Em geral, você não conseguirá encontrar uma combinação de índices que funcione igualmente bem para ambas as consultas.

Assim, um dos requisitos importantes que devem ser esclarecidos ao desenvolver tal solução de pesquisa é se é realmente importante para uma empresa ver o número total de objetos encontrados. Muitas vezes acontece que não. E a navegação por números de página específicos, na minha opinião, é uma solução com um escopo muito restrito, já que a maioria dos cenários de paginação se parece com “ir para a próxima página”.

Opção de paginação nº 2

Vamos supor que os usuários não se importem em saber o número total de objetos encontrados. Vamos tentar simplificar a página de pesquisa:

Na verdade, a única coisa que mudou é que não há como navegar para números de páginas específicos, e agora esta tabela não precisa saber quantos podem haver para exibi-la. Mas surge a pergunta - como a tabela sabe se há dados para a próxima página (para exibir corretamente o link “Próximo”)?

A resposta é muito simples: você pode ler no banco de dados um registro a mais do que o necessário para exibição, e a presença desse registro “adicional” mostrará se há uma próxima porção. Dessa forma, você só precisa executar uma solicitação para obter uma página de dados, o que melhora significativamente o desempenho e facilita o suporte a essa funcionalidade. Na minha prática, houve um caso em que a recusa em contar o número total de registros acelerou a entrega dos resultados em 4 a 5 vezes.

Existem diversas opções de interface de usuário para esta abordagem: comandos "voltar" e "avançar", como no exemplo acima, um botão "carregar mais", que simplesmente adiciona uma nova parte aos resultados exibidos, "rolagem infinita", que funciona no princípio de "carregar mais" ", mas o sinal para obter a próxima parte é o usuário rolar todos os resultados exibidos até o final. Qualquer que seja a solução visual, o princípio da amostragem de dados permanece o mesmo.

Nuances da implementação de paginação

Todos os exemplos de consulta fornecidos acima usam a abordagem “offset + count”, quando a própria consulta especifica em que ordem as linhas de resultados e quantas linhas precisam ser retornadas. Primeiro, vamos ver a melhor forma de organizar a passagem de parâmetros neste caso. Na prática, encontrei vários métodos:

• O número de série da página solicitada (pageIndex), tamanho da página (pageSize).
• O número de série do primeiro registro a ser retornado (startIndex), o número máximo de registros no resultado (count).
• O número de sequência do primeiro registro a ser retornado (startIndex), o número de sequência do último registro a ser retornado (endIndex).

À primeira vista pode parecer que isto é tão elementar que não há diferença. Mas não é assim - a opção mais conveniente e universal é a segunda (startIndex, count). Há várias razões para isso:

• Para a abordagem de revisão de entrada +1 fornecida acima, a primeira opção com pageIndex e pageSize é extremamente inconveniente. Por exemplo, queremos exibir 50 postagens por página. De acordo com o algoritmo acima, você precisa ler mais um registro do que o necessário. Se este “+1” não estiver implementado no servidor, verifica-se que para a primeira página devemos solicitar registros de 1 a 51, para a segunda - de 51 a 101, etc. Se você especificar um tamanho de página de 51 e aumentar o pageIndex, a segunda página retornará de 52 para 102, etc. Assim, na primeira opção, a única maneira de implementar corretamente um botão para ir para a próxima página é fazer com que o servidor revise a linha “extra”, o que será uma nuance muito implícita.
• A terceira opção não faz sentido algum, pois para executar consultas na maioria dos bancos de dados você ainda precisará passar a contagem em vez do índice do último registro. Subtrair startIndex de endIndex pode ser uma operação aritmética simples, mas é supérflua aqui.

Agora devemos descrever as desvantagens de implementar a paginação através de “offset + quantidade”:

• A recuperação de cada página subsequente será mais cara e lenta que a anterior, pois o banco de dados ainda precisará percorrer todos os registros “desde o início” de acordo com os critérios de busca e classificação, e então parar no fragmento desejado.
• Nem todos os SGBDs podem suportar esta abordagem.

Existem alternativas, mas também são imperfeitas. A primeira dessas abordagens é chamada de “paginação de conjunto de chaves” ou “método de busca” e é a seguinte: depois de receber uma parte, você pode lembrar os valores dos campos no último registro da página e usá-los para obter a próxima porção. Por exemplo, executamos a seguinte consulta:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

E no último registro obtivemos o valor da data do pedido '2014-06-29'. Então, para obter a próxima página, você pode tentar fazer o seguinte:

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

O problema é que OrderDate não é um campo exclusivo e a condição especificada acima provavelmente perderá muitas linhas obrigatórias. Para adicionar inequívoco a esta consulta, você precisa adicionar um campo exclusivo à condição (suponha que 75074 seja o último valor da chave primária da primeira parte):

SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
   OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Esta opção funcionará corretamente, mas em geral será difícil de otimizar, pois a condição contém um operador OR. Se o valor da chave primária aumentar à medida que OrderDate aumenta, a condição poderá ser simplificada deixando apenas um filtro por SalesOrderID. Mas se não houver uma correlação estrita entre os valores da chave primária e o campo pelo qual o resultado é classificado, esse OR não poderá ser evitado na maioria dos SGBDs. Uma exceção que conheço é o PostgreSQL, que suporta totalmente comparações de tuplas, e a condição acima pode ser escrita como "WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074)". Dada uma chave composta com esses dois campos, uma consulta como essa deve ser bastante fácil.

Uma segunda abordagem alternativa pode ser encontrada, por exemplo, em API de rolagem ElasticSearch ou Cosmos DB — quando uma solicitação, além dos dados, retorna um identificador especial com o qual você pode obter a próxima porção de dados. Se esse identificador tiver vida útil ilimitada (como no Comsos DB), então esta é uma ótima maneira de implementar paginação com transição sequencial entre páginas (opção 2 mencionada acima). Suas possíveis desvantagens: não é suportado em todos os SGBDs; o identificador do próximo pedaço resultante pode ter uma vida útil limitada, o que geralmente não é adequado para implementar a interação do usuário (como a API de rolagem ElasticSearch).

Filtragem complexa

Vamos complicar ainda mais a tarefa. Suponha que haja a necessidade de implementar a chamada pesquisa facetada, que é muito familiar a todos nas lojas online. Os exemplos acima baseados na tabela de pedidos não são muito ilustrativos neste caso, então vamos mudar para a tabela Produto do banco de dados AdventureWorks:

Qual é a ideia por trás da pesquisa facetada? O fato é que para cada elemento filtrante é mostrada a quantidade de registros que atendem a esse critério levando em consideração os filtros selecionados em todas as outras categorias.

Por exemplo, se selecionarmos a categoria Bicicletas e a cor Preta neste exemplo, a tabela mostrará apenas bicicletas pretas, mas:

• Para cada critério do grupo Categorias, a quantidade de produtos dessa categoria será mostrada em preto.
• Para cada critério do grupo “Cores” será mostrado o número de bicicletas desta cor.

Aqui está um exemplo da saída do resultado para tais condições:

Se você marcar também a categoria “Roupas”, a tabela também mostrará as roupas pretas que estão em estoque. A quantidade de produtos pretos na seção “Cor” também será recalculada de acordo com as novas condições, apenas na seção “Categorias” nada mudará... Espero que esses exemplos sejam suficientes para entender o algoritmo usual de pesquisa facetada.

Agora vamos imaginar como isso pode ser implementado de forma relacional. Cada grupo de critérios, como Categoria e Cor, exigirá uma consulta separada:

SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
  INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC

SELECT Color, COUNT(1) FROM Production.Product p
  INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC

O que há de errado com esta solução? É muito simples - não é bem dimensionado. Cada seção de filtro requer uma consulta separada para calcular quantidades, e essas consultas não são as mais fáceis. Nas lojas online, algumas categorias podem ter várias dezenas de seções de filtro, o que pode ser um sério problema de desempenho.

Normalmente após estas afirmações me são oferecidas algumas soluções, nomeadamente:

• Combine todas as contagens de quantidade em uma consulta. Tecnicamente, isso é possível usando a palavra-chave UNION, mas não ajudará muito no desempenho - o banco de dados ainda terá que executar cada um dos fragmentos do zero.
• Quantidades de cache. Isso me é sugerido quase sempre que descrevo um problema. A ressalva é que isso geralmente é impossível. Digamos que temos 10 “facetas”, cada uma com 5 valores. Esta é uma situação muito “modesta” face ao que se pode ver nas mesmas lojas online. A escolha de um elemento da faceta afeta as quantidades de outros 9, ou seja, para cada combinação de critérios as quantidades podem ser diferentes. Em nosso exemplo, há um total de 50 critérios que o usuário pode selecionar, portanto, haverá 250 combinações possíveis. Não há memória ou tempo suficiente para preencher tal array de dados. Aqui você pode objetar e dizer que nem todas as combinações são reais e o usuário raramente seleciona mais de 5 a 10 critérios. Sim, é possível fazer carregamento lento e armazenar em cache apenas uma quantidade do que já foi selecionado, mas quanto mais seleções houver, menos eficiente será esse cache e mais perceptíveis serão os problemas de tempo de resposta (especialmente se o o conjunto de dados muda regularmente).

Felizmente, esse problema há muito tem soluções bastante eficazes que funcionam de maneira previsível em grandes volumes de dados. Para qualquer uma dessas opções, faz sentido dividir o recálculo das facetas e o recebimento da página de resultados em duas chamadas paralelas ao servidor e organizar a interface do usuário de forma que o carregamento dos dados por facetas “não interfira” na exibição de Procurar Resultados.

• Chame um recálculo completo de “facetas” tão raramente quanto possível. Por exemplo, não recalcule tudo sempre que os critérios de pesquisa mudarem, mas em vez disso encontre o número total de resultados que correspondem às condições atuais e solicite ao usuário que os mostre - “1425 registros encontrados, mostrar?” O usuário pode continuar alterando os termos de pesquisa ou clicar no botão “mostrar”. Somente no segundo caso serão executadas todas as solicitações de obtenção de resultados e recálculo de quantidades em todas as “facetas”. Neste caso, como você pode facilmente perceber, você terá que lidar com uma solicitação para obter o número total de resultados e sua otimização. Este método pode ser encontrado em muitas pequenas lojas online. Obviamente, isto não é uma panaceia para este problema, mas em casos simples pode ser um bom compromisso.
• Use mecanismos de busca para encontrar resultados e contar facetas, como Solr, ElasticSearch, Sphinx e outros. Todos eles são projetados para construir “facetas” e fazem isso de forma bastante eficiente devido ao índice invertido. Como funcionam os mecanismos de pesquisa, por que nesses casos eles são mais eficazes do que os bancos de dados de uso geral, quais práticas e armadilhas existem - este é um tópico para um artigo separado. Gostaria aqui de chamar a atenção para o fato de que o mecanismo de busca não pode substituir o armazenamento principal de dados, ele é utilizado como um complemento: quaisquer alterações na base de dados principal que sejam relevantes para a pesquisa são sincronizadas no índice de pesquisa; O mecanismo de busca geralmente interage apenas com o mecanismo de busca e não acessa o banco de dados principal. Um dos pontos mais importantes aqui é como organizar esta sincronização de forma confiável. Tudo depende dos requisitos de “tempo de reação”. Se o tempo entre uma alteração no banco de dados principal e sua “manifestação” na pesquisa não for crítico, você poderá criar um serviço que pesquisa registros alterados recentemente a cada poucos minutos e os indexa. Se quiser o menor tempo de resposta possível, você pode implementar algo como caixa de saída transacional para enviar atualizações ao serviço de pesquisa.

Descobertas

1. A implementação da paginação no lado do servidor é uma complicação significativa e só faz sentido para conjuntos de dados de rápido crescimento ou simplesmente grandes. Não existe uma receita absolutamente exata de como avaliar “grande” ou “de rápido crescimento”, mas eu seguiria esta abordagem:
   • Se o recebimento de uma coleção completa de dados, levando em consideração o tempo do servidor e a transmissão da rede, atender normalmente aos requisitos de desempenho, não faz sentido implementar a paginação no lado do servidor.
   • Pode haver uma situação em que não sejam esperados problemas de desempenho num futuro próximo, uma vez que há poucos dados, mas a recolha de dados está em constante crescimento. Se algum conjunto de dados no futuro não atender mais ao ponto anterior, é melhor começar a paginar imediatamente.
2. Se não houver uma exigência estrita por parte da empresa para exibir o número total de resultados ou os números das páginas, e seu sistema não possuir um mecanismo de busca, é melhor não implementar esses pontos e considerar a opção nº 2.
3. Se houver um requisito claro para pesquisa facetada, você terá duas opções sem sacrificar o desempenho:
   • Não recalcule todas as quantidades sempre que os critérios de pesquisa forem alterados.
   • Utilize motores de busca como Solr, ElasticSearch, Sphinx e outros. Mas deve-se entender que ele não pode substituir o banco de dados principal, devendo ser utilizado como um complemento ao armazenamento principal para solucionar problemas de busca.
4. Além disso, no caso da pesquisa facetada, faz sentido dividir a recuperação da página de resultados da pesquisa e a contagem em duas solicitações paralelas. Contar quantidades pode demorar mais do que obter resultados, embora os resultados sejam mais importantes para o usuário.
5. Se você estiver usando um banco de dados SQL para pesquisa, qualquer alteração de código relacionada a esta parte deverá ser bem testada quanto ao desempenho na quantidade apropriada de dados (excedendo o volume no banco de dados ativo). Também é aconselhável utilizar o monitoramento do tempo de execução das consultas em todas as instâncias do banco de dados, e principalmente na “ao vivo”. Mesmo que tudo estivesse bem com os planos de consulta na fase de desenvolvimento, à medida que o volume de dados aumenta, a situação pode mudar visivelmente.

Fonte: habr.com