Le guide ultime – Comment rédiger de meilleures requêtes SQL ?

Requêtes basées sur les méthodes de collecte et de programme

Le modèle inverse implique implicitement qu'il existe une différence entre les approches basées sur les collections et procédurales pour la création de requêtes.

• L'approche procédurale de l'interrogation est une approche très similaire à la programmation : vous indiquez au système ce qui doit être fait et comment le faire. Par exemple, comme dans l'exemple de l'article précédent, interrogez la base de données en exécutant une fonction puis en appelant une autre fonction, ou utilisez une approche logique impliquant des boucles, des conditions et des fonctions définies par l'utilisateur (UDF) pour obtenir le résultat final de la requête. Vous constaterez que de cette manière, vous demandez toujours un sous-ensemble de données dans chaque couche. Cette approche est également souvent appelée interrogation étape par étape ou ligne par ligne.
• L'autre est une approche basée sur la collecte, où il suffit de spécifier les opérations à effectuer. Ce que vous devez faire avec cette méthode est de spécifier les conditions et exigences pour les résultats que vous souhaitez obtenir via la requête. Lors de la récupération de données, vous n'avez pas besoin de prêter attention aux mécanismes internes qui implémentent la requête : le moteur de base de données détermine le meilleur algorithme et la meilleure logique pour exécuter la requête.

Étant donné que SQL est basé sur des ensembles, cette approche est plus efficace que l'approche procédurale, ce qui explique pourquoi dans certains cas, SQL peut fonctionner plus rapidement que le code.

La méthode de requête basée sur des ensembles est également une compétence que le secteur de l'analyse de l'exploration de données vous demande de maîtriser ! Parce que vous devez être habile pour basculer entre ces deux méthodes. Si vous constatez que vos requêtes contiennent des requêtes procédurales, vous devez vous demander si cette partie doit être réécrite.

De la requête au plan d'exécution

Le mode inverse n'est pas statique. Au fur et à mesure que vous progressez pour devenir développeur SQL, éviter les modèles inverses de requêtes et réécrire les requêtes peut être une tâche ardue. Vous avez donc souvent besoin d’utiliser des outils pour optimiser vos requêtes de manière plus structurée.

Réfléchir à la performance nécessite non seulement une approche plus structurée, mais aussi une approche plus approfondie.

Cependant, cette approche structurée et approfondie repose avant tout sur des plans de requêtes. Le plan de requête est d'abord analysé dans un « arbre d'analyse » et définit exactement quel algorithme est utilisé pour chaque opération et comment les opérations sont coordonnées.

Optimisation des requêtes

Lors de l'optimisation d'une requête, vous devrez probablement inspecter manuellement le plan généré par l'optimiseur. Dans ce cas, vous devrez réanalyser votre requête en examinant le plan de requête.

Pour maîtriser un tel plan de requête, vous devez utiliser certains outils fournis par le système de gestion de base de données. Voici quelques outils que vous pouvez utiliser :

• Certains progiciels proposent des outils capables de générer des représentations graphiques de plans de requête.
• D'autres outils peuvent vous fournir des descriptions textuelles des plans de requête.

Notez que si vous utilisez PostgreSQL, vous pouvez faire la différence entre différents EXPLAIN, vous obtenez simplement une description de la façon dont le planificateur exécute la requête sans exécuter le plan. Dans le même temps, EXPLAIN ANALYZE exécutera la requête et vous renverra un rapport d'analyse qui évalue le plan de requête et le plan de requête réel. D'une manière générale, le plan d'exécution réel exécutera réellement le plan, tandis que le plan d'exécution évalué peut résoudre ce problème sans exécuter la requête. Logiquement, le plan d'exécution réel est plus utile car il contient des détails et des statistiques supplémentaires sur ce qui s'est réellement passé lors de l'exécution de la requête.

Ensuite, vous en apprendrez plus sur XPLAIN et ANALYZE, et comment utiliser ces deux commandes pour mieux comprendre vos plans de requête et les performances des requêtes. Pour ce faire, vous devez commencer à faire quelques exemples en utilisant deux tables : one_million et half_million.

Vous pouvez récupérer les informations actuelles de la table one_million à l'aide d'EXPLAIN : assurez-vous de les mettre en premier lieu lors de l'exécution de la requête, et une fois l'exécution terminée, elles seront renvoyées au plan de requête :

EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18584.82 rows=1025082 width=36)
(1 row)

Dans l'exemple ci-dessus, nous voyons que le coût de la requête est de 0,00..18584,82, le nombre de lignes est de 1025082 et la largeur de colonne est de 36.

En même temps, vous pouvez également utiliser ANALYZE pour mettre à jour les informations statistiques.

ANALYZE one_million;
EXPLAIN
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-emphasis">___</span>_
Seq Scan on one_million
(cost=0.00..18334.00 rows=1000000 width=37)
(1 row)

En plus d'EXPLAIN et ANALYZE, vous pouvez également récupérer le temps d'exécution réel à l'aide d'EXPLAIN ANALYZE :

EXPLAIN ANALYZE
SELECT *
FROM one_million;
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
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 vous devez réellement exécuter la requête, ce qui mérite d'être noté !

Tous les algorithmes que nous avons vus jusqu'à présent sont des analyses séquentielles ou des analyses de table complète : il s'agit d'une méthode d'analyse sur une base de données, où chaque ligne de la table analysée est lue dans un ordre séquentiel (série), chaque colonne sera vérifiée pour voir si cela répond aux critères. En termes de performances, une analyse séquentielle n'est pas le meilleur plan d'exécution car la table entière doit être analysée. Mais si vous utilisez un disque lent, les lectures séquentielles seront également rapides.

Il existe quelques exemples d'autres algorithmes :

EXPLAIN ANALYZE
SELECT *
FROM one<span class="hljs-emphasis">_million JOIN half_</span>million
ON (one<span class="hljs-emphasis">_million.counter=half_</span>million.counter);
QUERY PLAN
<span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span><span class="hljs-strong">_____</span>_
Hash Join (cost=15417.00..68831.00 rows=500000 width=42)
(actual time=1241.471..5912.553 rows=500000 loops=1)
Hash Cond: (one<span class="hljs-emphasis">_million.counter = half_</span>million.counter)
<span class="hljs-code">    -> Seq Scan on one_million</span>
<span class="hljs-code">    (cost=0.00..18334.00 rows=1000000 width=37)</span>
<span class="hljs-code">    (actual time=0.007..1254.027 rows=1000000 loops=1)</span>
<span class="hljs-code">    -> Hash (cost=7213.00..7213.00 rows=500000 width=5)</span>
<span class="hljs-code">    (actual time=1241.251..1241.251 rows=500000 loops=1)</span>
<span class="hljs-code">    Buckets: 4096 Batches: 16 Memory Usage: 770kB</span>
<span class="hljs-code">    -> Seq Scan on half_million</span>
<span class="hljs-code">    (cost=0.00..7213.00 rows=500000 width=5)</span>
(actual time=0.008..601.128 rows=500000 loops=1)
Total runtime: 6468.337 ms

Nous pouvons voir que l'optimiseur de requêtes a sélectionné Hash Join. N'oubliez pas cette opération car nous devons l'utiliser pour évaluer la complexité temporelle de la requête. Nous avons remarqué qu'il n'y a pas d'index half_million.counter dans l'exemple ci-dessus, nous pouvons ajouter l'index dans l'exemple ci-dessous :

<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> <span class="hljs-keyword">ON</span> half_million(counter);
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">ANALYZE</span>
<span class="hljs-keyword">SELECT</span> *
<span class="hljs-keyword">FROM</span> one_million <span class="hljs-keyword">JOIN</span> half_million
<span class="hljs-keyword">ON</span> (one_million.counter=half_million.counter);
QUERY PLAN
______________________________________________________________
<span class="hljs-keyword">Merge</span> <span class="hljs-keyword">Join</span> (<span class="hljs-keyword">cost</span>=<span class="hljs-number">4.12</span>.<span class="hljs-number">.37650</span><span class="hljs-number">.65</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">42</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.033</span>.<span class="hljs-number">.3272</span><span class="hljs-number">.940</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
<span class="hljs-keyword">Merge</span> Cond: (one_million.counter = half_million.counter)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> one_million_counter_idx <span class="hljs-keyword">on</span> one_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.32129</span><span class="hljs-number">.34</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">1000000</span> width=<span class="hljs-number">37</span>)
    (actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.011</span>.<span class="hljs-number">.694</span><span class="hljs-number">.466</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500001</span> loops=<span class="hljs-number">1</span>)
    -> <span class="hljs-keyword">Index</span> <span class="hljs-keyword">Scan</span> <span class="hljs-keyword">using</span> half_million_counter_idx <span class="hljs-keyword">on</span> half_million
    (<span class="hljs-keyword">cost</span>=<span class="hljs-number">0.00</span>.<span class="hljs-number">.14120</span><span class="hljs-number">.29</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> width=<span class="hljs-number">5</span>)
(actual <span class="hljs-keyword">time</span>=<span class="hljs-number">0.010</span>.<span class="hljs-number">.683</span><span class="hljs-number">.674</span> <span class="hljs-keyword">rows</span>=<span class="hljs-number">500000</span> loops=<span class="hljs-number">1</span>)
Total runtime: <span class="hljs-number">3833.310</span> ms
(<span class="hljs-number">5</span> <span class="hljs-keyword">rows</span>)

En créant l'index, l'optimiseur de requêtes a décidé comment trouver la jointure de fusion lorsque l'index est analysé.

Veuillez noter la différence entre une analyse d'index et une analyse de table complète (analyse séquentielle) : cette dernière (également appelée "analyse de table") trouve des résultats appropriés en analysant toutes les données ou en indexant toutes les pages, tandis que la première analyse uniquement chaque ligne dans la table.

La deuxième partie du tutoriel est présentée ici. Le dernier article de la série « Comment rédiger de meilleures requêtes SQL » suivra, alors restez à l'écoute.

Veuillez indiquer la source de la réimpression : Grape City Control

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!