Saviez-vous que la pagination par offset est très complexe mais facile à éviter ?
La clause offset demande à la base de données d'ignorer les N premiers résultats d'une requête. Cependant, la base de données doit toujours récupérer ces lignes du disque avant de pouvoir renvoyer les lignes suivantes.
Ce n'est pas un problème provenant de l'implémentation, c'est à la base du concept même d'offset :
… les lignes sont tout d'abord triées suivant la <clause order by>, puis sont filtrées en supprimant le nombre de lignes indiqué dans la <clause offset> à partir du début…
SQL:2016, Part 2, §4.15.3 Derived tables
Autrement dit, les gros décalages imposent un long travail à la base de données, qu'elle soit SQL ou NoSQL.
Mais les problèmes avec la clause offset ne s'arrêtent pas là : pensez à ce qu'il arrive si une ligne est insérée entre deux récupérations de pages ?
Lors de l'utilisation d'offset➌ pour ignorer les lignes lues précédemment❶, vous obtiendrez des duplicats au cas où des nouvelles lignes ont été insérées entre deux récupérations de pages➋. Il existe aussi d'autres anomalies, celle-ci étant la plus commune.
Ce n'est même pas un problème de bases de données, c'est la façon dont travaillent les environnements de développement : ils indiquent simplement le numéro de page à récupérer ou combien de lignes il faut ignorer. Avec cette seule information, aucune base de données ne peut exécuter cela correctement.
La vie sans OFFSET
Maintenant, imaginez un monde sans ces problèmes. Vivre sans offset est assez simple : il suffit d'utiliser une clause where qui extrait seulement les données que vous n'avez pas encore vu.
Pour cela, nous exploitons le fait que nous travaillons avec un ensemble de lignes ordonné. Parce que vous avez évidemment une clause order by, n'est-ce pas ? Une fois qu'un ordre est défini, nous pouvons utiliser un filtre pour sélectionner seulement ce qui suit la dernière ligne récupérée précédemment :
SELECT ...
FROM ...
WHERE ...
AND id < ?dernier_id_lu
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYC'est une recette basique. Cela devient plus intéressant lors d'un tri sur plusieurs colonnes mais le concept reste le même. Cette recette s'applique aussi aux nombreux systèmes NoSQL.
Cette approche, appelée méthode de recherche ou pagination par clé, résout le problème de la dérive des résultats illustré ci-dessus et est même plus rapide que la clause offset. Si vous voulez savoir ce qui arrive à l'intérieur de la base de données lors de l'utilisation d'offset ou de la pagination par clé, jetez un œil à ces slides (benchmarks, benchmarks !) :
Dans ces slides, vous pouvez aussi vous rendre compte que le SQL a une fonctionnalité très intéressante pour exprimer un filtre du type « tout ce qui se trouve avant dans le tri » : les comparaisons de lignes, qui sont rarement supportées. Néanmoins, il est facile de contourner cette absence. Vous devez écrire correctement la clause where. Cet article contient les détails nécessaires. Si vous voulez utiliser une pagination sans clause offset, vous devez tout d'abord lire cet article !
Mais les environnements de développement…
La raison principale pour laquelle les développeurs préfèrent la pagination avec offset est le manque de support de l'autre type de pagination par les environnements de développement. La plupart des environnements offrent la pagination basée sur offset, mais ne proposent pas de moyen simple pour la pagination par clé.
Notez que la pagination par clé affecte toute la pile technologique en commençant par JavaScript au niveau du navigateur qui utilise AJAX pour un déplacement infini : au lieu de passer un simple numéro de page au serveur, vous devez fournir une clé complète (souvent plusieurs colonnes) au serveur.
La liste des environnements de développement supportant la pagination par clé est très restreinte :
- JavaScript
- Python
- Java / JVM
- PHP
- .NET
- Ruby
- Perl
C'est pour cela que j'ai besoin de votre aide. Si vous maintenez un environnement de développement qui propose des fonctionnalités de pagination, je vous demande, je vous supplie de fournir aussi un support natif de la pagination par clé. Si vous avez des questions sur les détails du concept de ce type de pagination, je serais ravi de vous aider (forum, formulaire de contact, Twitter) !
Même si vous ne faites qu'utiliser un logiciel qui supporte la pagination par clé (par exemple un CRM ou un site d'e-commerce), faites le savoir aux mainteneurs de ce logiciel. Vous pouvez demander l'ajout d'une telle fonctionnalité en ajoutant un lien vers cette page ou, si c'est possible, en fournissant un patch. De nouveau, je serais ravi de vous aider en cela.
Passez le mot !
Le problème de la pagination par clé n'est pas technique. Le problème principal est que ce type de pagination n'est pas connu et n'est pas proposé par les environnements de développements. Si vous aimez l'idée d'une pagination sans offset, merci de passer le mot. Envoyez des tweets, partagez-le, envoyez le par mail, vous pouvez même re-poster ce billet de blog (qui est sous license CC-BY-NC-ND). Les traductions sont aussi les bienvenues, mais contactez moi avant pour que j'inclus un lien vers la traduction de cette page !
Oh, et si vous écrivez un billet de blog, vous pouvez aussi ajouter une banière à votre blog pour que vos lecteurs en soient bien conscients. J'ai préparé une galerie de banières NoOffset avec quelques formats standards de banières. Choisissez celle qui vous convient le mieux.
