Guide SQL: comment mieux écrire des requêtes (partie 2)

Article suivant Guide SQL: comment mieux écrire des requêtes (partie 1)

De la demande aux plans d'exécution

Sachant que les antipatterns ne sont pas statiques et évoluent au fur et à mesure que vous grandissez en tant que développeur SQL, et le fait qu'il y a beaucoup de choses à considérer lorsque vous pensez à des alternatives signifie également qu'il est assez difficile d'éviter les antipatterns et les requêtes de réécriture tâche. Toute aide peut être utile, c'est pourquoi une approche plus structurée de l'optimisation des requêtes à l'aide de certains outils peut être la plus efficace.

Il convient également de noter que certains des anti-modèles mentionnés dans la dernière section sont enracinés dans des problèmes de performances, tels que les opérateurs AND , OR et NOT et leur absence lors de l'utilisation des index. Penser à la performance nécessite non seulement une approche plus structurée, mais aussi plus profonde.

Cependant, cette approche structurée et approfondie sera principalement basée sur le plan de requête, qui, comme vous vous en souvenez, est le résultat d'une requête d'abord analysée dans un "arbre d'analyse" ou "arbre d'analyse" et détermine exactement quel algorithme utilisé pour chaque opération et comment leur exécution est coordonnée.

Optimisation des requêtes

Comme vous le lisez dans l'introduction, vous devrez peut-être vérifier et configurer des plans qui sont compilés manuellement par l'optimiseur. Dans de tels cas, vous devrez analyser à nouveau votre demande en consultant le plan de demande.

Pour accéder à ce plan, vous devez utiliser les outils fournis par le système de gestion de base de données. Les outils suivants peuvent être à votre disposition:

• Certains packages contiennent des outils qui génèrent une représentation graphique du plan de requête. Prenons l'exemple suivant:
  
  
  
• D'autres outils fourniront une description textuelle du plan de requête. Un exemple est l'instruction EXPLAIN PLAN dans Oracle, mais le nom de l'instruction dépend du SGBD avec lequel vous travaillez. Ailleurs, vous pouvez trouver EXPLAIN (MySQL, PostgreSQL) ou EXPLAIN QUERY PLAN (SQLite).

Veuillez noter que lorsque vous travaillez avec PostgreSQL, vous pouvez faire une distinction entre EXPLAIN , où vous obtenez simplement une description qui indique comment le planificateur a l'intention d'exécuter la requête sans l'exécuter, tandis EXPLAIN ANALYZE exécute réellement la requête et vous renvoie l'analyse plans de demande attendus et réels. De manière générale, un véritable plan d'exécution est un plan dans lequel une demande est réellement exécutée, tandis qu'un plan d'exécution d'évaluation détermine ce qu'il fera sans répondre à la demande. Bien que cela soit logiquement équivalent, le plan d'exécution réel est beaucoup plus utile car il contient des informations et des statistiques supplémentaires sur ce qui s'est réellement passé lors de l'exécution de la demande.

Dans le reste de cette section, vous en apprendrez plus sur EXPLAIN et ANALYZE , ainsi que sur la façon de les utiliser pour obtenir plus d'informations sur le plan de requête et ses performances possibles. Pour ce faire, commencez par quelques exemples dans lesquels vous allez travailler avec deux tables: one_million et half_million .

Vous pouvez obtenir les informations actuelles de la table one_million utilisant EXPLAIN ; Assurez-vous de le placer directement au-dessus de la demande, et après l'avoir exécuté, il vous renverra le plan de requête:

 EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18584.82 rows=1025082 width=36) (1 row) 

Dans ce cas, vous voyez que le coût de la demande est 0.00..18584.82 et le nombre de lignes est 1025082 . La largeur du nombre de colonnes est de 36 .

De plus, vous pouvez mettre à jour les statistiques à l'aide d' ANALYZE .

 ANALYZE one_million; EXPLAIN SELECT * FROM one_million; QUERY PLAN ____________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (1 row) 

Outre EXPLAIN et ANALYZE , vous pouvez également obtenir le temps d'exécution réel avec EXPLAIN ANALYZE :

 EXPLAIN ANALYZE SELECT * FROM one_million; QUERY PLAN ___________________________________________________________ Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.015..1207.019 rows=1000000 loops=1) Total runtime: 2320.146 ms (2 rows) 

L'inconvénient d'utiliser EXPLAIN ANALYZE est que la requête est réellement exécutée, alors faites attention à cela!

Jusqu'à présent, tous les algorithmes que vous avez vus sont le Seq Scan séquentiel ( Seq Scan séquentiel) ou le scan complet de la table: il s'agit d'un scan effectué dans une base de données où chaque ligne de la table scannée est lue dans l'ordre série et les colonnes trouvées sont vérifiées pour respect de la condition ou non. En termes de performances, les analyses séquentielles ne sont certainement pas le meilleur plan d'exécution car vous effectuez toujours une analyse complète de la table. Cependant, ce n'est pas si mal quand la table ne tient pas en mémoire: les lectures séquentielles sont assez rapides même sur des disques lents.

Vous en apprendrez plus à ce sujet plus tard lorsque nous parlerons de l'analyse d'index.

Cependant, il existe d'autres algorithmes. Prenons, par exemple, ce plan de requête pour une connexion:

 EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN _________________________________________________________________ Hash Join (cost=15417.00..68831.00 rows=500000 width=42) (actual time=1241.471..5912.553 rows=500000 loops=1) Hash Cond: (one_million.counter = half_million.counter) -> Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.007..1254.027 rows=1000000 loops=1) -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=1241.251..1241.251 rows=500000 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 770kB -> Seq Scan on half_million (cost=0.00..7213.00 rows=500000 width=5) (actual time=0.008..601.128 rows=500000 loops=1) Total runtime: 6468.337 ms 

Vous voyez que l'optimiseur de requêtes a choisi Hash Join here! N'oubliez pas cette opération, car vous en aurez besoin pour évaluer la complexité temporelle de votre demande. Pour l'instant, notez qu'il n'y a pas d'index dans half_million.counter , que nous ajoutons dans l'exemple suivant:

 CREATE INDEX ON half_million(counter); EXPLAIN ANALYZE SELECT * FROM one_million JOIN half_million ON (one_million.counter=half_million.counter); QUERY PLAN ________________________________________________________________ Merge Join (cost=4.12..37650.65 rows=500000 width=42) (actual time=0.033..3272.940 rows=500000 loops=1) Merge Cond: (one_million.counter = half_million.counter) -> Index Scan using one_million_counter_idx on one_million (cost=0.00..32129.34 rows=1000000 width=37) (actual time=0.011..694.466 rows=500001 loops=1) -> Index Scan using half_million_counter_idx on half_million (cost=0.00..14120.29 rows=500000 width=5) (actual time=0.010..683.674 rows=500000 loops=1) Total runtime: 3833.310 ms (5 rows) 

Vous voyez qu'en créant l'index, l'optimiseur de requête a maintenant décidé d'utiliser la Merge join lors de l'analyse de l' Index Scan index.

Notez la différence entre les analyses d'index et les analyses de table complètes ou analyses séquentielles: la première, également appelée «analyses de table», analyse les données ou les pages d'index pour trouver les enregistrements correspondants, tandis que la seconde analyse chaque ligne de la table.

Vous voyez que le temps d'exécution global a diminué et que les performances devraient être meilleures, mais il existe deux analyses d'index, ce qui rend la mémoire plus importante ici, surtout si la table ne tient pas dedans. Dans de tels cas, vous devez d'abord effectuer une analyse complète de l'index, qui est effectuée à l'aide de lectures séquentielles rapides et n'est pas un problème, mais vous avez ensuite de nombreuses opérations de lecture aléatoire pour sélectionner des lignes par valeur d'index. Ce sont des opérations de lecture aléatoire qui sont généralement plus lentes de plusieurs ordres de grandeur que les opérations séquentielles. Dans ces cas, une analyse complète de la table se produit en effet plus rapidement qu'une analyse complète de l'index.

Astuce: Si vous souhaitez en savoir plus sur EXPLAIN ou examiner des exemples plus en détail, pensez à lire Understanding Explain de Guillaume Lelarge.

Complexité temporelle et Big O

Maintenant que vous avez brièvement examiné le plan de requête, vous pouvez commencer à approfondir et à réfléchir aux performances de manière plus formelle en utilisant la théorie de la complexité de calcul. Il s'agit d'un domaine de l'informatique théorique, qui, entre autres, se concentre sur la classification des problèmes de calcul en fonction de leur complexité; Ces problèmes de calcul peuvent être des algorithmes, mais aussi des requêtes.

Cependant, pour les requêtes, elles ne sont pas nécessairement classées en fonction de leur complexité, mais plutôt en fonction du temps nécessaire pour les compléter et obtenir des résultats. C'est ce qu'on appelle la complexité temporelle, et vous pouvez utiliser la grande notation O pour formuler ou mesurer ce type de complexité.

Avec la désignation big O, vous exprimez le temps d'exécution en termes de vitesse de croissance par rapport à l'entrée, car l'entrée devient arbitrairement grande. La grande notation O exclut les coefficients et les membres d'un ordre inférieur, vous pouvez donc vous concentrer sur la partie importante du temps d'exécution de votre requête: son taux de croissance. Lorsqu'ils sont exprimés de cette manière, en rejetant les coefficients et les termes d'un ordre inférieur, ils disent que la complexité temporelle est décrite asymptotiquement. Cela signifie que la taille d'entrée va à l'infini.

Dans un langage de base de données, la complexité détermine le temps nécessaire pour terminer une requête à mesure que la taille des tables de données et donc la base de données grandit.

Veuillez noter que la taille de votre base de données augmente non seulement à cause de l'augmentation de la quantité de données dans les tables, mais le fait qu'il existe des index joue également un rôle dans la taille.

Estimation de la complexité temporelle de votre plan de requête

Comme vous l'avez vu précédemment, le plan d'exécution, entre autres, détermine quel algorithme est utilisé pour chaque opération, ce qui vous permet d'exprimer logiquement chaque temps d'exécution de requête en fonction de la taille de la table incluse dans le plan de requête, appelée fonction de complexité. En d'autres termes, vous pouvez utiliser la notation O et le plan d'exécution pour évaluer la complexité et les performances des requêtes.

Dans les sections suivantes, vous obtiendrez un aperçu des quatre types de complexité temporelle et vous verrez quelques exemples de la façon dont la complexité temporelle des requêtes peut varier en fonction du contexte dans lequel elle est exécutée.

Astuce: les index font partie de cette histoire!

Cependant, il convient de noter qu'il existe différents types d'index, différents plans d'exécution et différentes implémentations pour différentes bases de données, de sorte que les difficultés temporaires répertoriées ci-dessous sont très générales et peuvent varier en fonction de paramètres spécifiques.

O (1): Temps constant

Ils disent qu'un algorithme fonctionne en temps constant s'il a besoin du même temps quelle que soit la taille des données d'entrée. Quand il s'agit d'une requête, elle sera exécutée en temps constant si le même temps est requis quelle que soit la taille de la table.

Ce type de requête n'est pas vraiment courant, mais en voici un exemple:

 SELECT TOP 1 t.* FROM t 

La complexité temporelle est constante, car une ligne arbitraire est sélectionnée dans le tableau. Par conséquent, la durée ne doit pas dépendre de la taille de la table.

Temps linéaire: O (n)

Ils disent que l'algorithme fonctionne en temps linéaire, si son temps d'exécution est directement proportionnel à la taille des données d'entrée, c'est-à-dire que le temps augmente linéairement avec la taille des données d'entrée. Pour les bases de données, cela signifie que le temps d'exécution sera directement proportionnel à la taille de la table: à mesure que le nombre de lignes de la table augmente, le temps d'exécution de la requête augmente.

Un exemple est une requête avec une WHERE pour une colonne non indexée: une analyse complète de la table ou une analyse Seq Scan sera nécessaire, ce qui entraînera une complexité temporelle O (n). Cela signifie que chaque ligne doit être lue afin de trouver la ligne avec l'identifiant (ID) souhaité. Vous n'avez aucune restriction, vous devez donc compter chaque ligne, même si la première ligne correspond à la condition.

Considérez également l'exemple de requête suivant, qui aura une complexité O (n) s'il n'y a pas d'index sur le champ i_id :

 SELECT i_id FROM item; 

• Ce qui précède signifie également que d'autres requêtes, telles que des requêtes pour calculer le nombre de lignes COUNT (*) FROM TABLE; aura une complexité temporelle O (n) , car une analyse complète de la table sera nécessaire car le nombre total de lignes n'a pas été enregistré pour la table. Sinon, la complexité temporelle serait similaire à O (1) .
  

Le runtime linéaire est étroitement lié au runtime des plans qui ont des jointures de table. Voici quelques exemples:

• La jointure de hachage a la complexité attendue de O (M + N). L'algorithme de jointure de hachage classique pour joindre en interne deux tables prépare d'abord la table de hachage de la plus petite table. Les entrées de la table de hachage se composent d'un attribut de connexion et de sa chaîne. La table de hachage est accessible en appliquant la fonction de hachage à l'attribut de connexion. Une fois la table de hachage créée, une grande table est analysée et les lignes correspondantes de la petite table sont trouvées en recherchant la table de hachage.
• Les jointures de fusion ont généralement une complexité O (M + N), mais cela dépendra fortement des index de colonne de jointure et, s'il n'y a pas d'index, du tri des lignes en fonction des clés utilisées dans la jointure:
  • Si les deux tables sont triées en fonction des clés utilisées dans la jointure, la requête aura une complexité temporelle de O (M + N).
  • Si les deux tables ont un index pour les colonnes jointes, alors l'index prend déjà en charge ces colonnes dans l'ordre et le tri n'est pas requis. La difficulté sera O (M + N).
  • Si aucune des tables n'a d'index sur les colonnes connectées, vous devez d'abord trier les deux tables, afin que la complexité ressemble à O (M log M + N log N).
  • Si une seule des tables a un index sur les colonnes connectées, seule la table qui n'a pas d'index doit être triée avant que l'étape de jointure se produise, de sorte que la complexité ressemble à O (M + N log N).
• Pour les jointures imbriquées, la complexité est généralement O (MN). Cette jointure est efficace lorsqu'une ou les deux tables sont extrêmement petites (par exemple, moins de 10 enregistrements), ce qui est une situation très courante lors de l'évaluation des requêtes, car certaines sous-requêtes sont écrites pour renvoyer une seule ligne.

N'oubliez pas: une jointure imbriquée est une jointure qui compare chaque enregistrement d'une table avec chaque enregistrement d'une autre.

Temps logarithmique: O (log (n))

On dit qu'un algorithme fonctionne en temps logarithmique si son temps d'exécution est proportionnel au logarithme de la taille d'entrée; Pour les requêtes, cela signifie qu'elles seront exécutées si le temps d'exécution est proportionnel au logarithme de la taille de la base de données.

Cette complexité temporelle logarithmique est valide pour les plans de requête dans lesquels une Index Scan ou un index cluster est analysé. Un index cluster est un index dont le niveau d'index final contient les lignes réelles de la table. Un index clusterisé est similaire à tout autre index: il est défini dans une ou plusieurs colonnes. Ils forment une clé d'index. La clé de clustering est les colonnes clés d'un index cluster. L'analyse d'un index cluster est essentiellement l'opération de lecture de votre SGBD pour une ou plusieurs lignes de haut en bas dans un index cluster.

Prenons l'exemple de requête suivant, où il existe un index pour i_id et qui entraîne généralement une complexité O (log (n)):

 SELECT i_stock FROM item WHERE i_id = N; 

Notez que sans index, la complexité temporelle serait O (n).

Temps quadratique: O (n ^ 2)

On pense que l'algorithme est exécuté en temps quadratique, si son temps d'exécution est proportionnel au carré de la taille d'entrée. Encore une fois, pour les bases de données, cela signifie que le temps d'exécution de la requête est proportionnel au carré de la taille de la base de données.

Un exemple possible d'une requête de complexité temporelle quadratique est le suivant:

 SELECT * FROM item, author WHERE item.i_a_id=author.a_id 

La complexité minimale peut être O (n log (n)), mais la complexité maximale peut être O (n ^ 2) sur la base des informations d'index des attributs de connexion.

Pour résumer, vous pouvez également consulter la feuille de triche suivante pour évaluer les performances des requêtes en fonction de leur complexité temporelle et de leur efficacité:

Réglage SQL

Compte tenu du plan d'exécution des requêtes et de la complexité temporelle, vous pouvez personnaliser davantage votre requête SQL. Vous pouvez commencer par vous concentrer sur les points suivants:

• Remplacez les analyses de table complètes inutiles par des analyses d'index;
• Assurez-vous que l'ordre de jointure optimal est appliqué.
• Assurez-vous que les index sont utilisés de manière optimale. Et
• La mise en cache des analyses de texte intégral des petites tables (cache les analyses de table complète des petites tables) est utilisée.

Utilisation ultérieure de SQL

Félicitations! Vous êtes arrivé à la fin de cet article, qui vient de vous donner un petit aperçu des performances des requêtes SQL. J'espère que vous avez plus d'informations sur les antipatterns, l'optimiseur de requêtes et les outils que vous pouvez utiliser pour analyser, évaluer et interpréter la complexité de votre plan de requête. Mais il vous reste encore tant à découvrir! Si vous voulez en savoir plus, lisez le livre «Database Management Systems» de R. Ramakrishnan et J. Gehrke.

Enfin, je ne veux pas vous refuser StackOverflow dans cette citation:

Mon antipattern préféré ne vérifie pas vos demandes.

Cependant, il est applicable lorsque:

• Votre requête fournit plusieurs tables.
• Vous pensez avoir la conception optimale pour la demande, mais n'essayez pas de vérifier vos hypothèses.
• Vous acceptez la première demande de travail, sans savoir à quel point elle est proche de l'optimum.