Salut tout le monde! Je suis un développeur backend qui écrit des microservices en Java + Spring. Je travaille dans l'une des équipes internes de développement de produits chez Tinkoff.
Dans notre équipe, la question de l’optimisation des requêtes dans un SGBD se pose souvent. Vous voulez toujours être un peu plus rapide, mais vous ne pouvez pas toujours vous en sortir avec des index judicieusement construits : vous devez rechercher des solutions de contournement. Au cours d'une de ces pérégrinations sur le Web à la recherche d'optimisations raisonnables lorsque l'on travaille avec des bases de données, j'ai trouvé , auteur de SQL Performance Explained. C’est ce type de blog rare dans lequel vous pouvez lire tous les articles d’affilée.
J'aimerais traduire pour vous un court article de Marcus. On peut l'appeler dans une certaine mesure un manifeste qui cherche à attirer l'attention sur le problème ancien, mais toujours d'actualité, de l'exécution de l'opération de décalage selon la norme SQL.
À certains endroits, je compléterai l'auteur avec des explications et des commentaires. Je ferai référence à tous ces endroits par « environ ». pour plus de clarté
Une petite introduction
Je pense que beaucoup de gens savent à quel point le travail avec les sélections de pages via offset est problématique et lent. Saviez-vous qu’il peut être assez facilement remplacé par un design plus efficace ?
Ainsi, le mot-clé offset indique à la base de données d'ignorer les n premiers enregistrements de la requête. Cependant, la base de données doit toujours lire ces n premiers enregistrements du disque, dans l'ordre donné (remarque : appliquer le tri s'il est spécifié), et alors seulement il sera possible de renvoyer les enregistrements à partir de n+1. La chose la plus intéressante est que le problème ne réside pas dans l'implémentation spécifique dans le SGBD, mais dans la définition originale selon la norme :
…les lignes sont d'abord triées selon le puis limité en supprimant le nombre de lignes spécifié dans le Depuis le début…
-SQL:2016, Partie 2, 4.15.3 Tables dérivées (remarque : actuellement le standard le plus utilisé)
Le point clé ici est que le décalage prend un seul paramètre : le nombre d'enregistrements à ignorer, et c'est tout. Suite à cette définition, le SGBD peut uniquement récupérer tous les enregistrements, puis supprimer ceux qui sont inutiles. Évidemment, cette définition de la compensation nous oblige à faire un travail supplémentaire. Et peu importe qu’il s’agisse de SQL ou de NoSQL.
Juste un peu plus de douleur
Les problèmes de compensation ne s'arrêtent pas là, et voici pourquoi. Si, entre la lecture de deux pages de données du disque, une autre opération insère un nouvel enregistrement, que se passera-t-il dans ce cas ?
Lorsque le décalage est utilisé pour ignorer les enregistrements des pages précédentes, dans le cas de l'ajout d'un nouvel enregistrement entre les lectures de différentes pages, vous obtiendrez très probablement des doublons (remarque : cela est possible lorsque nous lisons page par page en utilisant la construction order by, puis au milieu de notre sortie, il peut y avoir une nouvelle entrée).
La figure illustre clairement cette situation. La base lit les 10 premiers enregistrements, après quoi un nouvel enregistrement est inséré, ce qui décale tous les enregistrements lus de 1. Ensuite, la base prend une nouvelle page à partir des 10 enregistrements suivants et ne commence pas à partir du 11, comme il se doit, mais à partir du 10e, dupliquant ce record. Il existe d'autres anomalies associées à l'utilisation de cette expression, mais celle-ci est la plus courante.
Comme nous l'avons déjà découvert, il ne s'agit pas de problèmes liés à un SGBD spécifique ou à leurs implémentations. Le problème est de définir la pagination selon le standard SQL. Nous indiquons au SGBD quelle page récupérer ou combien d'enregistrements ignorer. La base de données n'est tout simplement pas en mesure d'optimiser une telle demande, car il y a trop peu d'informations pour cela.
Il convient également de préciser qu'il ne s'agit pas d'un problème lié à un mot-clé spécifique, mais plutôt à la sémantique de la requête. Il existe plusieurs autres syntaxes identiques dans leur nature problématique :
- Le mot-clé offset est comme mentionné précédemment.
- Une construction de deux mots-clés limit [offset] (même si limit en soi n'est pas si mal).
- Filtrage par limites inférieures, basé sur la numérotation des lignes (par exemple, row_number(), rownum, etc.).
Toutes ces expressions vous indiquent simplement combien de lignes sauter, sans informations ni contexte supplémentaires.
Plus loin dans cet article, le mot-clé offset est utilisé comme résumé de toutes ces options.
La vie sans COMPENSATION
Imaginons maintenant à quoi ressemblerait notre monde sans tous ces problèmes. Il s'avère que la vie sans décalage n'est pas si difficile : avec une sélection, vous pouvez sélectionner uniquement les lignes que nous n'avons pas encore vues (remarque : c'est-à-dire celles qui n'étaient pas sur la page précédente), en utilisant une condition où.
Dans ce cas, on part du fait que les sélections sont exécutées sur un ensemble ordonné (bon vieux order by). Puisque nous avons un ensemble ordonné, nous pouvons utiliser un filtre assez simple pour obtenir uniquement les données qui se trouvent derrière le dernier enregistrement de la page précédente :
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYC'est tout le principe de cette approche. Bien sûr, les choses deviennent plus amusantes lors du tri sur plusieurs colonnes, mais l'idée est toujours la même. Il est important de noter que cette conception est applicable à de nombreux -les décisions.
Cette approche est appelée méthode de recherche ou pagination du jeu de clés. Il résout le problème du résultat flottant (remarque : la situation d'écriture entre les lectures de page décrite précédemment) et, bien sûr, ce que nous aimons tous, il fonctionne plus rapidement et de manière plus stable que le décalage classique. La stabilité réside dans le fait que le temps de traitement des requêtes n'augmente pas proportionnellement au numéro de table demandé (attention : si vous souhaitez en savoir plus sur le fonctionnement des différentes approches de pagination, vous pouvez . Vous pouvez également y trouver des benchmarks comparatifs pour différentes méthodes).
Une des diapositives Bien entendu, la pagination par clés n'est pas omnipotente - elle a ses limites. Le plus important est qu’elle n’a pas la capacité de lire des pages au hasard (note : de manière incohérente). Cependant, à l’ère du défilement sans fin (ndlr : sur le front-end), ce n’est pas un tel problème. Spécifier un numéro de page sur lequel cliquer est de toute façon une mauvaise décision dans la conception de l'interface utilisateur (note : avis de l'auteur de l'article).
Et les outils ?
La pagination sur les touches n'est souvent pas adaptée en raison du manque de support instrumental pour cette méthode. La plupart des outils de développement, y compris divers frameworks, ne vous permettent pas de choisir exactement comment la pagination sera effectuée.
La situation est aggravée par le fait que la méthode décrite nécessite une prise en charge de bout en bout des technologies utilisées - du SGBD à l'exécution d'une requête AJAX dans le navigateur avec défilement sans fin. Au lieu de spécifier uniquement le numéro de page, vous devez désormais spécifier un jeu de clés pour toutes les pages à la fois.
Cependant, le nombre de frameworks prenant en charge la pagination sur les clés augmente progressivement. Voici ce que nous avons pour le moment :
- pour Java ;
- pour Ruby ;
- и pour Django ;
- pour Python ;
- — API de critères pour les implémentations JPA ;
- pour Perl ;
- , mappeur pour Node.js .
(Remarque : certains liens ont été supprimés car au moment de la traduction, certaines bibliothèques n'avaient pas été mises à jour depuis 2017-2018. Si vous êtes intéressé, vous pouvez consulter la source originale.)
C'est à ce moment-là que votre aide est nécessaire. Si vous développez ou prenez en charge un framework qui utilise la pagination, alors je vous demande, je vous exhorte, je vous implore de fournir un support natif pour la pagination sur les clés. Si vous avez des questions ou avez besoin d'aide, je me ferai un plaisir de vous aider (, , ) (note : d'après mon expérience avec Marcus, je peux dire qu'il est vraiment enthousiaste à l'idée de diffuser ce sujet).
Si vous utilisez des solutions toutes faites qui, selon vous, méritent de prendre en charge la pagination par clés, créez une demande ou même proposez une solution toute faite, si possible. Vous pouvez également créer un lien vers cet article.
Conclusion
La raison pour laquelle une approche aussi simple et utile que la pagination par touches n'est pas répandue n'est pas qu'elle soit difficile à mettre en œuvre techniquement ou qu'elle nécessite de gros efforts. La raison principale est que beaucoup sont habitués à voir et à travailler avec l'offset - cette approche est dictée par la norme elle-même.
En conséquence, peu de gens pensent à changer l'approche de la pagination, et de ce fait, le support instrumental des frameworks et des bibliothèques se développe mal. Par conséquent, si l’idée et l’objectif d’une pagination sans offset sont proches de vous, aidez-nous à le diffuser !
Source:
Auteur: Markus Winand
Source: habr.com