Il est révolu le temps où vous n'aviez pas à vous soucier de l'optimisation des performances de la base de données. Le temps ne s'arrête pas. Chaque nouvel entrepreneur technologique souhaite créer le prochain Facebook, tout en essayant de collecter toutes les données sur lesquelles il peut mettre la main. Les entreprises ont besoin de ces données pour mieux former des modèles qui les aident à gagner de l'argent. Dans de telles conditions, les programmeurs doivent créer des API qui leur permettent de travailler rapidement et de manière fiable avec d'énormes quantités d'informations.
Si vous concevez des backends d'applications ou de bases de données depuis un certain temps, vous avez probablement écrit du code pour exécuter des requêtes paginées. Par exemple, comme ceci :
SELECT * FROM table_name LIMIT 10 OFFSET 40
C'est comme ça?
Mais si c'est ainsi que vous avez procédé à votre pagination, je suis désolé de dire que vous ne l'avez pas fait de la manière la plus efficace.
Voulez-vous vous opposer à moi ? . , и Ils utilisent déjà les techniques dont je veux parler aujourd'hui.
Nommez au moins un développeur back-end qui n’a jamais utilisé OFFSET и LIMIT pour effectuer des requêtes paginées. Dans MVP (Minimum Viable Product) et dans les projets où de petites quantités de données sont utilisées, cette approche est tout à fait applicable. Cela « fonctionne », pour ainsi dire.
Mais si vous avez besoin de créer des systèmes fiables et efficaces à partir de zéro, vous devez vous assurer à l'avance de l'efficacité des requêtes sur les bases de données utilisées dans ces systèmes.
Aujourd'hui, nous allons parler des problèmes liés aux implémentations couramment utilisées (dommage) des moteurs de requêtes paginées, et de la manière d'atteindre des performances élevées lors de l'exécution de telles requêtes.
Quel est le problème avec OFFSET et LIMIT ?
Comme déjà mentionné, OFFSET и LIMIT Ils fonctionnent bien dans les projets qui n'ont pas besoin de travailler avec de grandes quantités de données.
Le problème survient lorsque la base de données atteint une taille telle qu'elle ne rentre plus dans la mémoire du serveur. Cependant, lorsque vous travaillez avec cette base de données, vous devez utiliser des requêtes paginées.
Pour que ce problème se manifeste, il doit y avoir une situation dans laquelle le SGBD recourt à une opération inefficace d'analyse complète des tables sur chaque requête paginée (alors que des opérations d'insertion et de suppression peuvent se produire, et nous n'avons pas besoin de données obsolètes !).
Qu'est-ce qu'une « analyse de table complète » (ou « analyse de table séquentielle », Sequential Scan) ? Il s'agit d'une opération au cours de laquelle le SGBD lit séquentiellement chaque ligne du tableau, c'est-à-dire les données qu'elle contient, et vérifie leur conformité à une condition donnée. Ce type d’analyse de table est connu pour être le plus lent. Le fait est que lors de son exécution, de nombreuses opérations d'entrée/sortie sont effectuées qui impliquent le sous-système de disque du serveur. La situation est aggravée par la latence associée au travail avec les données stockées sur les disques et par le fait que le transfert de données du disque vers la mémoire est une opération gourmande en ressources.
Par exemple, vous disposez d'enregistrements de 100000000 XNUMX XNUMX d'utilisateurs et vous exécutez une requête avec la construction OFFSET 50000000. Cela signifie que le SGBD devra charger tous ces enregistrements (et nous n'en avons même pas besoin !), les mettre en mémoire, et ensuite prendre, disons, 20 résultats rapportés dans LIMIT.
Disons que cela pourrait ressembler à ceci : « sélectionnez les lignes de 50000 50020 à 100000 50000 sur XNUMX XNUMX ». Autrement dit, le système devra d'abord charger XNUMX XNUMX lignes pour terminer la requête. Voyez-vous combien de travail inutile elle devra faire ?
Si vous ne me croyez pas, jetez un œil à l'exemple que j'ai créé en utilisant les fonctionnalités .
Exemple sur db-fiddle.com
Là, à gauche, dans le champ Schema SQL, il y a du code qui insère 100000 XNUMX lignes dans la base de données, et à droite, dans le champ Query SQL, deux requêtes sont affichées. Le premier, lent, ressemble à ceci :
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Et la seconde, qui est une solution efficace au même problème, est la suivante :
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Afin de répondre à ces demandes, il vous suffit de cliquer sur le bouton Run en haut de la page. Cela fait, nous comparons les informations sur le temps d'exécution de la requête. Il s'avère que l'exécution d'une requête inefficace prend au moins 30 fois plus de temps que l'exécution de la seconde (ce temps varie d'une exécution à l'autre ; par exemple, le système peut signaler que la première requête a mis 37 ms à se terminer, mais l'exécution de la seconde - 1 ms).
Et s'il y a plus de données, alors tout semblera encore pire (pour s'en convaincre, jetez un œil à mon avec 10 millions de lignes).
Ce dont nous venons de parler devrait vous donner un aperçu de la manière dont les requêtes de base de données sont réellement traitées.
Veuillez noter que plus la valeur est élevée OFFSET — plus la demande sera longue à traiter.
Que dois-je utiliser à la place de la combinaison OFFSET et LIMIT ?
Au lieu d'une combinaison OFFSET и LIMIT Il vaut la peine d'utiliser une structure construite selon le schéma suivant :
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Il s'agit d'une exécution de requête avec une pagination basée sur le curseur.
Au lieu de stocker les actuels localement OFFSET и LIMIT et les transmettre à chaque requête, vous devez stocker la dernière clé primaire reçue (généralement c'est ID) Et LIMIT, en conséquence, des requêtes similaires à celles ci-dessus seront obtenues.
Pourquoi? Le fait est qu'en spécifiant explicitement l'identifiant de la dernière ligne lue, vous indiquez à votre SGBD où il doit commencer à rechercher les données nécessaires. De plus, la recherche, grâce à l'utilisation de la clé, sera effectuée efficacement ; le système ne devra pas être distrait par des lignes en dehors de la plage spécifiée.
Jetons un coup d'œil à la comparaison suivante des performances de diverses requêtes. Voici une requête inefficace.
Demande lente
Et voici une version optimisée de cette requête.
Demande rapide
Les deux requêtes renvoient exactement la même quantité de données. Mais le premier prend 12,80 secondes et le second 0,01 seconde. Sentez-vous la différence ?
Les problèmes possibles
Pour que la méthode de requête proposée fonctionne efficacement, la table doit avoir une ou plusieurs colonnes contenant des index séquentiels uniques, tels qu'un identifiant entier. Dans certains cas spécifiques, cela peut déterminer le succès de l'utilisation de telles requêtes pour augmenter la vitesse de travail avec la base de données.
Naturellement, lors de la construction de requêtes, vous devez prendre en compte l'architecture spécifique des tables et choisir les mécanismes qui fonctionneront le mieux sur les tables existantes. Par exemple, si vous devez travailler sur des requêtes avec de grands volumes de données associées, cela pourrait vous intéresser. article.
Si nous sommes confrontés au problème de l'absence d'une clé primaire, par exemple si nous avons une table avec une relation plusieurs-à-plusieurs, alors l'approche traditionnelle consistant à utiliser OFFSET и LIMIT, est garanti pour nous. Mais son utilisation peut entraîner des requêtes potentiellement lentes. Dans de tels cas, je recommanderais d’utiliser une clé primaire à incrémentation automatique, même si elle n’est nécessaire que pour gérer les requêtes paginées.
Si ce sujet vous intéresse - , и - plusieurs matériaux utiles.
Les résultats de
La principale conclusion que l’on peut tirer est que, quelle que soit la taille des bases de données dont on parle, il est toujours nécessaire d’analyser la vitesse d’exécution des requêtes. De nos jours, l'évolutivité des solutions est extrêmement importante, et si tout est conçu correctement dès le début du travail sur un système donné, cela peut, à l'avenir, éviter au développeur de nombreux problèmes.
Comment analyser et optimiser les requêtes de base de données ?
Source: habr.com