Get best of the week

Exibir resultados de pesquisa e problemas de desempenho

Um dos cenários típicos em todos os aplicativos familiares é procurar dados de acordo com certos critérios e exibi-los em um formato fácil de ler. Também pode haver oportunidades adicionais para classificação, agrupamento e paginação. A tarefa, em teoria, é trivial, mas ao resolvê-la, muitos desenvolvedores cometem vários erros, que sofrem com o desempenho. Vamos tentar considerar várias soluções para esse problema e formular recomendações para escolher a implementação mais eficaz.

imagem

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


A opção mais simples que vem à mente é paginar os resultados da pesquisa em sua forma mais clássica.


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

  • Obter linhas para a página atual.
  • Calcule o número total de linhas que correspondem aos critérios de pesquisa - isso é necessário para mostrar as páginas.

Considere a primeira consulta usando o servidor AdventureWorks para 2016 do banco de dados MS SQL de teste como exemplo . Para esse fim, usaremos a tabela Sales.SalesOrderHeader:

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

A consulta acima exibirá os primeiros 50 pedidos de uma lista classificada em ordem decrescente por data de adição, ou seja, os últimos 50 pedidos.

Ele é executado rapidamente em uma base de teste, mas vamos examinar o plano de execução e as 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 solicitação executando o comando SET STATISTICS IO ON no tempo de execução da consulta.

Como você pode ver no plano de execução, o mais intensivo em recursos é classificar todas as linhas na tabela de origem pela data em que foram adicionadas. E o problema é que, quanto mais linhas aparecerem na tabela, mais difícil será a classificação. Na prática, essas situações devem ser evitadas; portanto, adicione o índice na data da adição e verifique 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, ficou muito melhor. Mas todos os problemas foram resolvidos? Vamos alterar a solicitação de pesquisa de pedidos em que o 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 é um pouco pior que o anterior, mas o número real de leituras lógicas é quase o dobro do que com uma varredura de tabela completa. Existe uma saída - se fizermos o preço composto a partir do índice existente e adicionarmos o preço total das mercadorias como o segundo campo, obteremos 165 leituras lógicas:

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

Essa série de exemplos pode ser continuada por um longo tempo, mas os dois pensamentos principais que quero expressar aqui são:

  • Adicionar qualquer novo critério ou ordem de classificação à consulta de pesquisa pode afetar significativamente a velocidade de sua execução.
  • Porém, se precisarmos subtrair apenas uma parte dos dados, e não todos os resultados que atendem às condições de pesquisa, há muitas maneiras de otimizar essa consulta.

Agora vamos para a segunda consulta, mencionada no início - para a que conta o número de registros que atendem aos critérios de pesquisa. Tome o mesmo exemplo - localizando pedidos que custam mais de US $ 100:

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

Na presença do í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 de a consulta passar por todo o índice não é surpreendente, já que o campo SubTotal não está na primeira posição, portanto, a consulta não pode ser usada. O problema é resolvido adicionando outro índice ao campo SubTotal e, como resultado, já fornece apenas 48 leituras lógicas.

Você pode dar mais alguns exemplos de solicitações para contar a quantidade, mas a essência permanece a mesma: receber uma parte dos dados e contar a quantidade total são duas solicitações fundamentalmente diferentes e cada uma exige suas próprias medidas de otimização. No caso geral, não é possível encontrar uma combinação de índices que funcione igualmente bem para ambas as consultas.

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

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


Suponha 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:


De fato, apenas o fato de que não há como acessar números de página específicos mudou e agora esta tabela não precisa saber quantos deles podem ser exibidos. Mas surge a pergunta - como a tabela sabe se há dados para a próxima página (para exibir corretamente o link "Avançar")?

A resposta é muito simples: você pode subtrair do banco de dados um registro a mais do que precisa exibir, e a presença desse registro "adicional" mostrará se existe a próxima parte. Portanto, para obter uma página de dados, você precisará concluir apenas uma consulta, o que melhora significativamente o desempenho e facilita o suporte a essa funcionalidade. Na prática, tive um caso ao me recusar a contar o número total de registros acelerando a saída dos resultados em 4-5 vezes.

Para essa abordagem, existem várias opções para a interface do usuário: os comandos "voltar" e "encaminhar", como no exemplo acima, o botão "carregar mais", que simplesmente adiciona uma nova parte aos resultados exibidos, "rolagem infinita", que funciona com o princípio de "carregar mais" ", Mas o sinal para obter a próxima parte é o usuário percorrer todos os resultados exibidos até o final. Qualquer que seja a solução visual, o princípio da amostragem de dados permanece o mesmo.

As nuances da implementação da paginação


Em todos os exemplos das consultas acima, é usada a abordagem "offset + number", quando a própria consulta indica em que ordem as linhas de resultado e quantas linhas devem ser retornadas. Primeiro, considere a melhor forma de organizar a transferência de parâmetros nesse caso. Na prática, conheci várias maneiras:

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

À primeira vista, pode parecer que isso seja 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:

  • +1 , , pageIndex pageSize . , 50 . , , . «+1» , , 1 51, — 51 101 .. 51 pageIndex, 52 102 .. , — «» , .
  • A terceira opção não faz sentido, pois para executar consultas na maioria dos bancos de dados, você ainda precisará transferir a quantidade, não o índice do último registro. Deixe a subtração de startIndex de endIndex e uma operação aritmética elementar, mas é supérflua aqui.

Agora devemos descrever as deficiências da implementação da paginação através de "deslocamento + quantidade":

  • Obter cada página seguinte será mais caro e mais lento que a anterior, porque o banco de dados ainda precisará passar por todos os registros "desde o início", de acordo com os critérios de pesquisa e classificação, e depois parar no fragmento desejado.
  • Nem todos os DBMSs podem suportar essa abordagem.

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

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'. Em seguida, 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 é um campo não exclusivo e é provável que a condição mencionada acima ignore muitas das linhas necessárias. Para tornar essa solicitação inequívoca, 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

Essa opção funcionará corretamente, mas, no caso geral, será difícil otimizar, pois a condição contém o operador OR. Se o valor da chave primária aumentar com o crescimento de OrderDate, a condição poderá ser simplificada deixando apenas o 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 - na maioria dos DBMSs, esse OR não pode ser evitado. A exceção conhecida por mim é o PostgreSQL, onde a comparação de tuplas é totalmente suportada e a condição acima pode ser escrita como “WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074). Se você possui uma chave composta com esses dois campos, uma solicitação semelhante deve ser bastante fácil.

A segunda abordagem alternativa pode ser encontrada, por exemplo, na API de rolagem ElasticSearch ouBD do Cosmos - quando uma consulta, além dos dados, retorna um identificador especial com o qual você pode obter o próximo lote de dados. Se esse identificador tiver uma vida útil ilimitada (como no Comsos DB), essa é uma ótima maneira de implementar a paginação com transição sequencial entre as páginas (opção nº 2 mencionada acima). Suas possíveis desvantagens: nem todos os DBMSs são suportados; o próximo identificador de lote recebido 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


Nós complicamos ainda mais a tarefa. Suponha que exista um requisito para implementar a chamada pesquisa facetada, que é familiar a todos, de lojas online. Os exemplos acima, com base na tabela de pedidos, não são muito indicativos nesse caso, portanto, mudaremos para a tabela Produto do banco de dados AdventureWorks:


Qual é a ideia da pesquisa facetada? Nisso, para cada elemento do filtro, o número de registros correspondentes a este critério é mostrado levando em consideração os filtros selecionados em todas as outras categorias .

Por exemplo, se selecionarmos a categoria Bicicletas e a cor Preto neste exemplo, a tabela exibirá apenas bicicletas pretas, mas ao mesmo tempo:

  • Para cada critério do grupo "Categorias", o número de produtos dessa categoria em preto será mostrado.
  • Para cada critério do grupo Cores, o número de bicicletas dessa cor será mostrado.

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


Se além de marcar a categoria “Vestuário”, a tabela também mostrará as roupas pretas disponíveis. O número de produtos em preto na seção "Cor" também será recalculado de acordo com as novas condições, apenas na seção "Categorias" nada mudará ... Espero que esses exemplos sejam suficientes para entender o algoritmo de pesquisa facetado familiar.

Agora imagine como isso pode ser implementado em uma base relacional. Cada grupo de critérios, como Categoria e Cor, exigirá uma solicitação 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 essa decisão? Muito simples - não escala bem. Cada seção de filtro requer uma consulta separada para contar quantidades, e essas consultas não são as mais fáceis. Nas lojas online, em algumas seções, pode haver várias dezenas de seções do filtro, o que pode ser um problema sério de desempenho.

Geralmente, após essas declarações, elas me oferecem algumas soluções, a saber:

  • Combine todas as contagens de quantidade em uma consulta. Tecnicamente, isso é possível usando a palavra-chave UNION, apenas isso não ajudará muito no desempenho - de qualquer maneira, o banco de dados terá que executar cada um dos fragmentos do zero.
  • . , . , . , 10 «», 5 . «» , -. 9- , , . 50 , , 250. , . , 5-10 . , , - , , ( ).

Felizmente, essa tarefa há muito tempo tem soluções suficientemente eficazes que previsivelmente funcionam em grandes quantidades de dados. Para qualquer uma dessas opções, faz sentido dividir a recontagem de facetas e colocar a página de resultados em duas chamadas paralelas ao servidor e organizar a interface do usuário de tal maneira que carregar os dados nas facetas "não interfira" na exibição dos resultados da pesquisa.

  • «» . , , , , — «1425 , ?» , «». «». , , . -. , , .
  • search engine , Solr, ElasticSearch, Sphinx . «» . , , — . , search engine , : , , ; search engine . — . « ». «» , , . , - transactional outbox .


  1. — , . «» «» — , :
    • — .
    • , , , . - — .
  2. , — #2.
  3. faceted search, :
    • .
    • search engine Solr, ElasticSearch, Sphinx . , , .
  4. faceted search . , , .
  5. SQL , , , ( «» ). , — «». , .

More articles:


All Articles