Get best of the week

Afficher les résultats de recherche et les problèmes de performances

L'un des scénarios typiques dans toutes les applications courantes consiste à rechercher des données selon certains critères et à les afficher sous une forme facile à lire. Il peut également y avoir des possibilités supplémentaires de tri, de regroupement, de pagination. La tâche, en théorie, est insignifiante, mais lors de sa résolution, de nombreux développeurs font un certain nombre d'erreurs, qui souffrent alors des performances. Essayons d'envisager différentes solutions à ce problème et formulons des recommandations pour choisir la mise en œuvre la plus efficace.

image

Option de pagination n ° 1


L'option la plus simple qui vous vient à l'esprit est de paginer les résultats de la recherche sous sa forme la plus classique.


Supposons qu'une application utilise une base de données relationnelle. Dans ce cas, pour afficher les informations sous cette forme, vous devrez exécuter deux requêtes SQL:

  • Obtenez des lignes pour la page actuelle.
  • Calculez le nombre total de lignes qui correspondent aux critères de recherche - cela est nécessaire pour afficher les pages.

Considérez la première requête en utilisant le serveur de base de données MS SQL test AdventureWorks pour 2016 comme exemple . Pour cela, nous utiliserons la table Sales.SalesOrderHeader:

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

La requête ci-dessus affichera les 50 premières commandes d'une liste triée par ordre décroissant par date d'ajout, en d'autres termes, les 50 dernières commandes.

Il s'exécute rapidement sur une base de test, mais regardons le plan d'exécution et les statistiques d'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.

Vous pouvez obtenir des statistiques d'E / S pour chaque demande en exécutant la commande SET STATISTICS IO ON dans l'exécution de la requête.

Comme vous pouvez le voir dans le plan d'exécution, le plus gourmand en ressources consiste à trier toutes les lignes de la table source en fonction de la date à laquelle elles ont été ajoutées. Et le problème est que plus il y a de lignes dans le tableau, plus le tri sera «difficile». Dans la pratique, de telles situations doivent être évitées, alors ajoutez l'index à la date d'ajout et voyez si la consommation des ressources a changé:


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.

De toute évidence, cela s'est beaucoup amélioré. Mais tous les problèmes ont-ils été résolus? Modifions la demande de recherche pour les commandes dont le coût total des marchandises dépasse 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.

Nous avons une situation amusante: le plan de requête est légèrement pire que le précédent, mais le nombre réel de lectures logiques est presque deux fois plus qu'avec une analyse complète de la table. Il existe un moyen de sortir - si nous établissons le prix composite à partir de l'indice existant et ajoutons le prix total des marchandises comme deuxième champ, nous obtenons alors 165 lectures logiques:

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

Cette série d'exemples peut être poursuivie pendant longtemps, mais les deux principales pensées que je veux exprimer ici sont:

  • L'ajout de tout nouveau critère ou ordre de tri à la requête de recherche peut affecter considérablement la vitesse de son exécution.
  • Mais si nous devons soustraire seulement une partie des données, et pas tous les résultats qui correspondent aux conditions de recherche, il existe de nombreuses façons d'optimiser une telle requête.

Passons maintenant à la deuxième requête, mentionnée au tout début - à celle qui compte le nombre d'enregistrements qui répondent aux critères de recherche. Prenons le même exemple - trouver des commandes qui coûtent plus de 100 $:

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

En présence de l'indice composite indiqué ci-dessus, on obtient:


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.

Le fait que la requête passe à travers l'index entier n'est pas surprenant, car le champ SubTotal n'est pas en première position, donc la requête ne peut pas l'utiliser. Le problème est résolu en ajoutant un autre index au champ SubTotal et, par conséquent, ne donne déjà que 48 lectures logiques.

Vous pouvez donner quelques autres exemples de demandes de comptage de la quantité, mais l'essentiel reste le même: la réception d'une partie des données et le comptage de la quantité totale sont deux demandes fondamentalement différentes , et chacune nécessite ses propres mesures d'optimisation. Dans le cas général, vous ne pouvez pas trouver une combinaison d'index qui fonctionne aussi bien pour les deux requêtes.

Par conséquent, l'une des exigences importantes qui doit être clarifiée lors du développement d'une telle solution de recherche est de savoir s'il est vraiment important pour l'entreprise de voir le nombre total d'objets trouvés. Il arrive souvent que non. Et la navigation vers des numéros de page spécifiques, à mon avis, est une solution avec une portée très étroite, car la plupart des scénarios de pagination ressemblent à "aller à la page suivante".

Option de pagination n ° 2


Supposons que les utilisateurs ne souhaitent pas connaître le nombre total d'objets trouvés. Essayons de simplifier la page de recherche:


En fait, seul le fait qu'il n'y ait aucun moyen d'accéder à des numéros de page spécifiques a changé, et maintenant ce tableau n'a plus besoin de savoir combien d'entre eux peuvent être affichés. Mais la question se pose - comment le tableau sait-il s'il existe des données pour la page suivante (afin d'afficher correctement le lien «Suivant»)?

La réponse est très simple: vous pouvez soustraire de la base de données un enregistrement de plus que ce dont vous avez besoin pour afficher, et la présence de cet enregistrement "supplémentaire" montrera s'il y a la partie suivante. Ainsi, pour obtenir une page de données, vous devrez effectuer une seule requête, ce qui améliore considérablement les performances et facilite la prise en charge de cette fonctionnalité. Dans ma pratique, il y a eu un cas où le refus de compter le nombre total d'enregistrements a accéléré la sortie des résultats de 4 à 5 fois.

Pour cette approche, il existe plusieurs options pour l'interface utilisateur: les commandes «retour» et «avant», comme dans l'exemple ci-dessus, le bouton «charger plus», qui ajoute simplement une nouvelle portion aux résultats affichés, «défilement infini», qui fonctionne sur le principe de «charger plus» ", Mais le signal pour obtenir la portion suivante est que l'utilisateur fasse défiler tous les résultats affichés jusqu'à la fin. Quelle que soit la solution visuelle, le principe de l'échantillonnage des données reste le même.

Les nuances de la mise en œuvre de la pagination


Dans tous les exemples de requêtes ci-dessus, l'approche «offset + numéro» est utilisée, lorsque la requête elle-même indique dans quel ordre les lignes de résultat et combien de lignes doivent être retournées. Tout d'abord, réfléchissez à la meilleure façon d'organiser le transfert des paramètres dans ce cas. En pratique, j'ai rencontré plusieurs façons:

  • Numéro de série de la page demandée (pageIndex), taille de la page (pageSize).
  • Le numéro de série du premier enregistrement à renvoyer (startIndex), le nombre maximal d'enregistrements en conséquence (count).
  • Le numéro de série du premier enregistrement à renvoyer (startIndex), le numéro de série du dernier enregistrement à renvoyer (endIndex).

À première vue, il peut sembler que c'est si élémentaire qu'il n'y a pas de différence. Mais ce n'est pas le cas - l'option la plus pratique et universelle est la seconde (startIndex, count). Il y a plusieurs raisons à cela:

  • +1 , , pageIndex pageSize . , 50 . , , . «+1» , , 1 51, — 51 101 .. 51 pageIndex, 52 102 .. , — «» , .
  • La troisième option n'a aucun sens, car pour exécuter des requêtes dans la plupart des bases de données, vous devrez toujours transférer la quantité, pas l'index du dernier enregistrement. Soit la soustraction de startIndex de endIndex et une opération arithmétique élémentaire, mais c'est superflu ici.

Décrivons maintenant les défauts de l'implémentation de la pagination via "offset + quantité":

  • Obtenir chaque page suivante sera plus coûteux et plus lent que la précédente, car la base de données devra toujours parcourir tous les enregistrements «depuis le début» en fonction des critères de recherche et de tri, puis s'arrêter au fragment souhaité.
  • Tous les SGBD ne peuvent pas prendre en charge cette approche.

Il existe des alternatives, mais elles sont également imparfaites. La première de ces approches est appelée «pagination par jeu de clés» ou «méthode de recherche» et consiste en ce qui suit: après avoir reçu une partie, vous pouvez vous souvenir des valeurs des champs du dernier enregistrement de la page, puis les utiliser pour obtenir la partie suivante. Par exemple, nous avons effectué la demande suivante:

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

Et dans le dernier enregistrement, nous avons obtenu la valeur de la date de commande '2014-06-29'. Ensuite, pour obtenir la page suivante, vous pouvez essayer de faire ceci:

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

Le problème est que OrderDate est un champ non unique et la condition mentionnée ci-dessus est susceptible d'ignorer un grand nombre des lignes requises. Pour rendre cette demande sans ambiguïté, vous devez ajouter un champ unique à la condition (supposons que 75074 est la dernière valeur de la clé primaire de la première partie):

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

Cette option fonctionnera correctement, mais dans le cas général, elle sera difficile à optimiser, car la condition contient l'opérateur OU. Si la valeur de la clé primaire augmente avec la croissance de OrderDate, la condition peut être simplifiée en ne laissant que le filtre par SalesOrderID. Mais s'il n'y a pas de corrélation stricte entre les valeurs de la clé primaire et le champ selon lequel le résultat est trié - dans la plupart des SGBD, ce OU ne peut pas être évité. L'exception que je connais est PostgreSQL, où la comparaison de tuple est entièrement prise en charge, et la condition ci-dessus peut être écrite comme «WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074). Si vous avez une clé composite avec ces deux champs, une demande similaire devrait être assez simple.

La deuxième approche alternative peut être trouvée, par exemple, dans l' API ElasticSearch scroll ouCosmos DB - lorsqu'une requête en plus des données renvoie un identifiant spécial avec lequel vous pouvez obtenir le prochain lot de données. Si cet identifiant a une durée de vie illimitée (comme dans Comsos DB), alors c'est un excellent moyen d'implémenter la pagination avec une transition séquentielle entre les pages (option n ° 2 mentionnée ci-dessus). Ses inconvénients possibles: tous les SGBD ne sont pas pris en charge; l'identifiant reçu pour la partie suivante peut avoir une durée de vie limitée, ce qui n'est généralement pas approprié pour implémenter une interaction utilisateur (comme l'API ElasticSearch scroll).

Filtrage complexe


Nous compliquons encore la tâche. Supposons qu'il soit nécessaire d'implémenter la recherche dite à facettes, familière à tout le monde dans les magasins en ligne. Les exemples ci-dessus basés sur la table de commande ne sont pas très indicatifs dans ce cas, nous allons donc passer à la table Product de la base de données AdventureWorks:


Quelle est l'idée de la recherche à facettes? En ce que, pour chaque élément de filtre, le nombre d'enregistrements correspondant à ce critère est affiché en tenant compte des filtres sélectionnés dans toutes les autres catégories .

Par exemple, si nous sélectionnons la catégorie Vélos et la couleur Noir dans cet exemple, le tableau n'affichera que les vélos noirs, mais en même temps:

  • Pour chaque critère du groupe «Catégories», le nombre de produits de cette catégorie en noir sera affiché.
  • Pour chaque critère du groupe Couleurs, le nombre de vélos de cette couleur sera affiché.

Voici un exemple de sortie du résultat pour de telles conditions:


Si en plus de marquer la catégorie «Vêtements», le tableau indiquera également les vêtements noirs disponibles. Le nombre de produits noirs dans la section "Couleur" sera également recalculé en fonction des nouvelles conditions, seulement dans la section "Catégories" rien ne changera ... J'espère que ces exemples suffisent pour comprendre l'algorithme de recherche à facettes familier.

Imaginez maintenant comment cela peut être mis en œuvre sur une base relationnelle. Chaque groupe de critères, tels que la catégorie et la couleur, nécessitera une demande distincte:

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


Quel est le problème avec cette décision? Très simple - il n'évolue pas bien. Chaque section de filtre nécessite une requête distincte pour compter les quantités, et ces requêtes ne sont pas les plus simples. Dans les magasins en ligne, dans certaines sections, il peut y avoir plusieurs dizaines de sections du filtre, ce qui peut être un grave problème de performances.

Habituellement, après ces déclarations, ils m'offrent quelques solutions, à savoir:

  • Combinez tous les comptages de quantité en une seule requête. Techniquement, cela est possible en utilisant le mot-clé UNION, mais cela n'aidera pas beaucoup dans les performances - de toute façon, la base de données devra exécuter chaque fragment à partir de zéro.
  • . , . , . , 10 «», 5 . «» , -. 9- , , . 50 , , 250. , . , 5-10 . , , - , , ( ).

Heureusement, une telle tâche a depuis longtemps des solutions suffisamment efficaces qui fonctionnent de manière prévisible sur de grandes quantités de données. Pour l'une de ces options, il est logique de diviser le recomptage de facette et d'obtenir la page de résultats en deux appels parallèles au serveur et d'organiser l'interface utilisateur de telle sorte que le chargement des données sur les facettes "n'interfère pas" avec l'affichage des résultats de la recherche.

  • «» . , , , , — «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