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

En savoir plus sur les antipatterns, les plans d'exécution, la complexité temporelle, le réglage des requêtes et l'optimisation SQL

Le langage de requête structuré (SQL) est une compétence indispensable dans l'industrie informatique et, d'une manière générale, l'apprentissage de cette compétence est relativement simple. Cependant, la plupart des gens oublient que SQL ne consiste pas seulement à écrire des requêtes, c'est juste la première étape sur la route. Garantir les performances des requêtes ou faire correspondre le contexte dans lequel vous travaillez est une tout autre chose.

C'est pourquoi ce guide SQL vous fournira un petit aperçu de certaines des étapes que vous pouvez suivre pour évaluer votre requête:

• Tout d'abord, vous commencerez par un bref aperçu de l'importance de l'apprentissage SQL pour travailler dans le domaine de la science des données;
• Ensuite, vous apprendrez d'abord comment traiter et exécuter des requêtes SQL afin de comprendre l'importance de créer des requêtes de qualité. Plus précisément, vous verrez que la demande est analysée, réécrite, optimisée et finalement évaluée.
• Dans cet esprit, vous irez non seulement à quelques antipatterns de requêtes que les débutants font lors de l'écriture de requêtes, mais vous en apprendrez également plus sur les alternatives et les solutions à ces erreurs possibles; De plus, vous en apprendrez davantage sur l'approche de requête basée sur un ensemble.
• Vous verrez également que ces antipatterns découlent de problèmes de performances et qu'en plus de l'approche «manuelle» pour améliorer les requêtes SQL, vous pouvez analyser vos requêtes de manière plus structurée et approfondie, en utilisant d'autres outils qui vous aident à voir le plan de requête; Et
• Vous apprendrez brièvement la complexité du temps et la notation O, pour avoir une idée de la complexité du plan d'exécution à temps avant d'exécuter la demande;
• Vous apprendrez brièvement comment optimiser votre requête.

Pourquoi devriez-vous apprendre SQL à travailler avec des données?

SQL est loin d'être mort: c'est l'une des compétences les plus recherchées que vous trouverez dans les descriptions de travail de l'industrie du traitement et de l'analyse des données, que vous postuliez pour l'analyse de données, l'ingénieur de données, le spécialiste des données ou tout autre rôle. Cela est confirmé par 70% des répondants à l'enquête sur les salaires O 'Reilly Data Science pour 2016, qui indiquent qu'ils utilisent SQL dans leur contexte professionnel. De plus, dans cette enquête, SQL se démarque des langages de programmation R (57%) et Python (54%).

Vous obtenez l'image: SQL est une compétence nécessaire lorsque vous travaillez à trouver un emploi dans l'industrie informatique.

Pas mal pour un langage qui a été développé au début des années 1970, non?

Mais pourquoi est-il si souvent utilisé? Et pourquoi n'est-il pas mort malgré le fait qu'il existe depuis si longtemps?

Il existe plusieurs raisons: l'une des premières raisons pourrait être que les entreprises stockent principalement des données dans des systèmes de gestion de bases de données relationnelles (RDBMS) ou dans des systèmes de gestion de flux de données relationnelles (RDSMS), et SQL est requis pour accéder à ces données. SQL est une lingua franca de données: il permet d'interagir avec presque n'importe quelle base de données ou même de créer la vôtre localement!

Si cela ne suffit toujours pas, gardez à l'esprit qu'il existe de nombreuses implémentations SQL qui sont incompatibles entre les fournisseurs et ne sont pas nécessairement conformes aux normes. Par conséquent, la connaissance du SQL standard est une exigence pour vous de trouver votre chemin dans l'industrie (informatique).

De plus, il est sûr de dire que de nouvelles technologies ont également rejoint SQL, comme Hive, une interface de langage de requête de type SQL pour interroger et gérer de grands ensembles de données, ou Spark SQL, qui peut être utilisé pour exécuter des requêtes SQL. Encore une fois, le SQL que vous y trouverez sera différent du standard que vous pourriez apprendre, mais la courbe d'apprentissage sera beaucoup plus simple.

Si vous voulez faire une comparaison, considérez-la comme l'apprentissage de l'algèbre linéaire: après avoir mis tous ces efforts dans ce sujet, vous savez que vous pouvez également l'utiliser pour maîtriser l'apprentissage automatique!

En bref, c'est pourquoi vous devriez apprendre ce langage de requête:

• Il est assez facile à apprendre, même pour les débutants. La courbe d'apprentissage est assez simple et progressive, vous écrirez donc des requêtes dès que possible.
• Il suit le principe «apprendre une fois, utiliser partout», c'est donc un excellent investissement de votre temps!
• C'est un excellent ajout aux langages de programmation; Dans certains cas, l'écriture d'une requête est même préférable à l'écriture de code, car elle est plus efficace!
• ...

Qu'attendez-vous encore? :)

Traitement SQL et exécution de requêtes

Pour améliorer les performances de votre requête SQL, vous devez d'abord savoir ce qui se passe à l'intérieur lorsque vous cliquez sur un raccourci pour exécuter la requête.

Tout d'abord, la demande est analysée dans un arbre d'analyse; La demande est analysée pour vérifier sa conformité aux exigences syntaxiques et sémantiques. L'analyseur crée une représentation interne de la demande d'entrée. Cette sortie est ensuite transférée vers le mécanisme de réécriture.

Ensuite, l'optimiseur doit trouver l'exécution ou le plan de requête optimal pour la requête donnée. Le plan d'exécution détermine avec précision quel algorithme est utilisé pour chaque opération et comment les opérations sont coordonnées.

Pour trouver le plan d'exécution le plus optimal, l'optimiseur répertorie tous les plans d'implémentation possibles, détermine la qualité ou le coût de chaque plan, reçoit des informations sur l'état actuel de la base de données, puis sélectionne les meilleurs d'entre eux comme plan d'implémentation final. Les optimiseurs de requête pouvant être imparfaits, les utilisateurs et les administrateurs de base de données doivent parfois examiner et régler manuellement les plans créés par l'optimiseur pour améliorer les performances.

Maintenant, vous vous demandez probablement ce qui est considéré comme un «bon plan de requête».

Comme vous l'avez déjà lu, la qualité du coût d'un plan joue un rôle important. Plus précisément, des éléments tels que le nombre d'E / S disque requis pour évaluer le plan, le coût du processeur du plan et le temps de réponse total que le client de base de données peut observer, et le temps d'exécution total, sont importants. C'est là que le concept de complexité temporelle se pose. Vous en apprendrez plus à ce sujet plus tard.

Ensuite, le plan de requête sélectionné est exécuté, évalué par le mécanisme d'exécution du système et les résultats de la requête sont renvoyés.

Écriture de requêtes SQL

Il peut ne pas être devenu clair à partir de la section précédente que le principe de Garbage In, Garbage Out (GIGO) se manifeste naturellement dans le processus de traitement et d'exécution d'une requête: celui qui formule la requête possède également des clés pour les performances de vos requêtes SQL. Si l'optimiseur reçoit une requête mal formulée, il peut en faire autant ...

Cela signifie que vous pouvez faire certaines choses lors de la rédaction d'une demande. Comme vous l'avez déjà vu dans l'introduction, la responsabilité ici est double: il ne s'agit pas seulement d'écrire des requêtes qui répondent à une certaine norme, mais aussi de collecter des idées sur les endroits où les problèmes de performances peuvent être masqués dans votre requête.

Un point de départ idéal est de penser à des «endroits» dans vos requêtes où des problèmes peuvent survenir. Et, en général, il existe quatre mots clés dans lesquels les nouveaux arrivants peuvent s'attendre à des problèmes de performances:

• Condition WHERE ;
• Tous les mots clés INNER JOIN ou LEFT JOIN ; Et aussi
• HAVING état;

Bien sûr, cette approche est simple et naïve, mais, pour un débutant, ces points sont d'excellents pointeurs, et il est sûr de dire que lorsque vous commencez, des erreurs se produisent à ces endroits et, curieusement, où il est également difficile de les remarquer.

Cependant, vous devez également comprendre que la performance doit devenir significative. Cependant, le simple fait de dire que ces phrases et mots clés sont mauvais n'est pas ce dont vous avez besoin lorsque vous pensez aux performances SQL. Avoir une WHERE ou HAVING dans une requête ne signifie pas nécessairement que c'est une mauvaise requête ...

Consultez la section suivante pour en savoir plus sur les antipatterns et les approches alternatives pour créer votre requête. Ces trucs et astuces sont destinés à servir de guide. Comment et si vous avez vraiment besoin de réécrire votre demande dépend, entre autres choses, de la quantité de données, de la base de données et du nombre de fois que vous devez terminer la demande. Cela dépend complètement du but de votre demande et avoir une connaissance préalable de la base de données avec laquelle vous travaillerez est crucial!

1. Récupérez uniquement les données nécessaires

La conclusion «plus il y a de données, mieux c'est» - ne doit pas être suivie lors de l'écriture de SQL: vous risquez non seulement de vous perdre en obtenant plus de données que vous n'en avez réellement besoin, mais également les performances peuvent souffrir car votre requête reçoit trop de données.

C'est pourquoi, en règle générale, vous devez faire attention à l' SELECT , à la SELECT DISTINCT et à l'instruction LIKE .

SELECT

La première chose que vous pouvez déjà vérifier lorsque vous écrivez une requête est de savoir si l' SELECT aussi compacte que possible. L'objectif ici devrait être de supprimer les colonnes inutiles de SELECT . De cette façon, vous vous forcez à ne récupérer que les données correspondant à votre requête.

Si vous avez corrélé des sous-requêtes avec EXISTS , vous devez essayer d'utiliser une constante dans l' SELECT cette sous-requête au lieu de choisir la valeur de la colonne réelle. Ceci est particulièrement pratique lorsque vous ne vérifiez que l'existence.

N'oubliez pas qu'une sous-requête corrélée est une sous-requête qui utilise les valeurs d'une requête externe. Et notez que même si NULL peut fonctionner comme une «constante» dans ce contexte, c'est très déroutant!

Considérez l'exemple suivant pour comprendre ce que signifie l'utilisation d'une constante:

 SELECT driverslicensenr, name FROM Drivers WHERE EXISTS (SELECT '1' FROM Fines WHERE fines.driverslicensenr = drivers.driverslicensenr); 

Astuce: il est utile de savoir qu'avoir une sous-requête corrélée n'est pas toujours une bonne idée. Vous pouvez toujours envisager de vous en débarrasser, par exemple, en les réécrivant à l'aide de INNER JOIN :

 SELECT driverslicensenr, name FROM drivers INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr; 

Opération DISTINCT

L' SELECT DISTINCT utilisée pour renvoyer uniquement des valeurs différentes. DISTINCT est un point à éviter si possible. Comme dans d'autres exemples, le temps d'exécution augmente uniquement lorsque cette phrase est ajoutée à la demande. Par conséquent, il est toujours utile de déterminer si vous avez vraiment besoin de cette opération DISTINCT pour obtenir les résultats que vous souhaitez obtenir.

LIKE

Lors de l'utilisation de l'opérateur LIKE dans une requête, l'index n'est pas utilisé si le modèle commence par % ou _ . Cela empêchera la base de données d'utiliser l'index (s'il en existe un). Bien sûr, d'un autre point de vue, on peut également affirmer que ce type de demande laisse potentiellement la possibilité d'obtenir trop d'enregistrements qui ne répondent pas nécessairement à l'objet de la demande.

Encore une fois, connaître les données stockées dans la base de données peut vous aider à formuler un modèle qui filtrera correctement toutes les données pour trouver uniquement les lignes qui sont vraiment importantes pour votre requête.

2. Limitez vos résultats

Si vous ne pouvez pas éviter de filtrer votre SELECT , vous pouvez limiter vos résultats d'autres manières. C'est là qu'interviennent des approches telles que la LIMIT et les conversions de types de données.

ROWNUM TOP , LIMIT et ROWNUM

Vous pouvez ajouter des instructions LIMIT ou TOP aux requêtes pour spécifier le nombre maximal de lignes pour l'ensemble de résultats. Voici quelques exemples:

  SELECT TOP 3 * FROM Drivers; 

Notez que vous pouvez éventuellement spécifier PERCENT , par exemple, si vous modifiez la première ligne de requête avec SELECT TOP 50 PERCENT * .

 SELECT driverslicensenr, name FROM Drivers LIMIT 2; 

Vous pouvez également ajouter la ROWNUM équivalente à l'utilisation de LIMIT dans la requête:

 SELECT * FROM Drivers WHERE driverslicensenr = 123456 AND ROWNUM <= 3; 

Conversions de types de données

Les plus efficaces doivent toujours être utilisés, c'est-à-dire les plus petits types de données. Il y a toujours un risque lorsque vous fournissez un type de données énorme, tandis qu'un plus petit sera plus suffisant.

Cependant, lors de l'ajout d'une conversion de type de données à la requête, seul le temps d'exécution augmente.

Une alternative consiste à éviter autant que possible la conversion des types de données. Veuillez également noter qu'il n'est pas toujours possible de supprimer ou d'ignorer la conversion du type de données des requêtes, mais vous devez toujours vous efforcer de les inclure et vérifier l'effet de l'ajout avant d'exécuter la requête.

3. Ne compliquez pas les requêtes qu'elles ne devraient l'être

Les conversions de types de données vous amènent au point suivant: vous ne devez pas trop concevoir vos requêtes. Essayez de les rendre simples et efficaces. Cela peut sembler trop simple ou stupide même pour être un indice, principalement parce que les demandes peuvent être complexes.

Cependant, dans les exemples mentionnés dans les sections suivantes, vous verrez que vous pouvez facilement commencer à rendre les requêtes simples plus complexes qu'elles ne devraient l'être.

Opérateur OR

Lorsque vous utilisez l'opérateur OR dans votre requête, vous n'utilisez probablement pas d'index.

N'oubliez pas qu'un index est une structure de données qui améliore la vitesse de recherche des données dans une table de base de données, mais il est coûteux: des enregistrements supplémentaires seront nécessaires et un espace de stockage supplémentaire sera nécessaire pour maintenir la structure des données d'index. Les index sont utilisés pour rechercher ou rechercher rapidement des données sans avoir à rechercher chaque ligne de la base de données à chaque accès à la table de base de données. Les index peuvent être créés en utilisant une ou plusieurs colonnes dans une table de base de données.

Si vous n'utilisez pas d'index inclus dans la base de données, l'exécution de votre requête prendra inévitablement plus de temps. C'est pourquoi il est préférable de rechercher des alternatives à l'utilisation de l'opérateur OR dans votre requête;

Considérez la requête suivante:

 SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr = 123456 OR driverslicensenr = 678910 OR driverslicensenr = 345678; 

L'opérateur peut être remplacé par:

Condition avec IN ; ou

 SELECT driverslicensenr, name FROM Drivers WHERE driverslicensenr IN (123456, 678910, 345678); 

Deux SELECT avec UNION .

Astuce: ici, vous devez faire attention à ne pas utiliser l'opération UNION inutile, car vous consultez plusieurs fois la même table. En même temps, vous devez comprendre que lorsque vous utilisez UNION dans votre requête, le temps d'exécution augmente. Alternatives à l'opération UNION : reformulez la requête afin que toutes les conditions soient placées dans une seule SELECT , ou utilisez OUTER JOIN au lieu d' UNION .

Astuce: Gardez à l'esprit que même si OR - et les autres opérateurs qui seront mentionnés dans les sections suivantes - n'utilisent probablement pas d'index, la recherche d'index n'est pas toujours préférable!

NOT opérateur

Lorsque votre requête contient un opérateur NOT , il est probable que l'index n'est pas utilisé, comme avec l'opérateur OR . Cela ralentira inévitablement votre demande. Si vous ne savez pas ce que cela signifie ici, considérez la requête suivante:

 SELECT driverslicensenr, name FROM Drivers WHERE NOT (year > 1980); 

Cette requête s'exécutera certainement plus lentement que vous ne le pensez, principalement parce qu'elle est formulée beaucoup plus compliquée qu'elle ne peut l'être: dans des cas comme celui-ci, il est préférable de chercher une alternative. Pensez à remplacer NOT des opérateurs de comparaison tels que > , <> ou !> ; L'exemple ci-dessus peut en fait être réécrit et ressembler à ceci:

 SELECT driverslicensenr, name FROM Drivers WHERE year <= 1980; 

Ça a déjà l'air mieux, non?

AND opérateur

L'opérateur AND est un autre opérateur qui n'utilise pas d'index et qui peut ralentir une requête s'il est utilisé de manière trop complexe et inefficace, comme dans l'exemple suivant:

 SELECT driverslicensenr, name FROM Drivers WHERE year >= 1960 AND year <= 1980; 

Il est préférable de réécrire cette requête à l'aide de l'instruction BETWEEN :

 SELECT driverslicensenr, name FROM Drivers WHERE year BETWEEN 1960 AND 1980; 

ANY et ALL opérateurs

De plus, les opérateurs ANY et ALL sont ceux avec lesquels vous devez faire attention, car si vous les incluez dans vos requêtes, l'index ne sera pas utilisé. Des fonctions d'agrégation alternatives telles que MIN ou MAX sont utiles ici.

Conseil: dans les cas où vous utilisez les alternatives proposées, vous devez savoir que toutes les fonctions d'agrégation, telles que SUM , AVG , MIN , MAX sur plusieurs lignes, peuvent entraîner une longue requête. Dans de tels cas, vous pouvez essayer de minimiser le nombre de lignes à traiter ou à pré-calculer ces valeurs. Encore une fois, vous voyez qu'il est important de connaître votre environnement, le but de votre demande, ... Lorsque vous décidez quelle demande utiliser!

Isoler les colonnes dans des conditions

De plus, dans les cas où une colonne est utilisée dans un calcul ou dans une fonction scalaire, l'index n'est pas utilisé. Une solution possible serait de simplement sélectionner une colonne spécifique afin qu'elle ne fasse plus partie du calcul ou de la fonction. Prenons l'exemple suivant:

 SELECT driverslicensenr, name FROM Drivers WHERE year + 10 = 1980; 

Ça a l'air drôle, hein? Au lieu de cela, essayez de réviser le calcul et de réécrire la requête comme ceci:

 SELECT driverslicensenr, name FROM Drivers WHERE year = 1970; 

4. Manque de force brute

Cette dernière astuce signifie que vous ne devriez pas essayer de limiter trop la demande, car cela peut affecter ses performances. Cela est particulièrement vrai pour les jointures et pour la clause HAVING.

Ordre des tables dans les jointures

Lors de la jonction de deux tables, il peut être important de considérer l'ordre des tables dans la jointure. Si vous voyez qu'une table est considérablement plus grande que l'autre, vous devrez peut-être réécrire la requête afin que la plus grande table soit placée en dernier dans la jointure.

Conditions de connexion excessives

Si vous ajoutez trop de conditions aux connexions SQL, vous devez choisir un chemin spécifique. Cependant, il se peut que ce chemin ne soit pas toujours plus efficace.

HAVING condition

La HAVING été ajoutée à l'origine à SQL car le mot clé WHERE n'a pas pu être utilisé avec des fonctions d'agrégation. HAVING généralement utilisé avec la GROUP BY pour restreindre les groupes de lignes renvoyées à celles qui remplissent certaines conditions. Cependant, si cette condition est utilisée dans la requête, l'index n'est pas utilisé, ce qui, comme vous le savez déjà, peut conduire au fait que la requête ne fonctionne pas si bien.

Si vous recherchez une alternative, essayez d'utiliser la WHERE .

Tenez compte des requêtes suivantes:

 SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY state 

 SELECT state, COUNT(*) FROM Drivers GROUP BY state HAVING state IN ('GA', 'TX') ORDER BY state 

La première requête utilise la WHERE pour limiter le nombre de lignes qui doivent être résumées, tandis que la deuxième requête additionne toutes les lignes de la table, puis utilise HAVING pour supprimer les montants calculés. Dans de tels cas, l'option de WHERE est clairement meilleure car vous ne gaspillez pas de ressources.

On peut voir qu'il ne s'agit pas de limiter l'ensemble de résultats, mais de limiter le nombre intermédiaire d'enregistrements dans la requête.

Il convient de noter que la différence entre les deux conditions est que la WHERE introduit une condition pour les lignes individuelles, tandis que la HAVING introduit une condition pour les agrégations ou les résultats de sélection, où un résultat, tel que MIN , MAX , SUM , ... était créé à partir de plusieurs lignes.

Vous voyez, l'évaluation de la qualité, l'écriture et la réécriture des demandes ne sont pas une tâche facile, étant donné qu'elles doivent être aussi productives que possible; La prévention des contre-modèles et la considération d'options alternatives feront également partie de la responsabilité lors de l'écriture de requêtes devant être effectuées sur des bases de données dans un environnement professionnel.

Cette liste n'était qu'un petit aperçu de quelques antipatterns et astuces qui, je l'espère, aideront les débutants; Si vous voulez avoir une idée de ce que les développeurs plus âgés considèrent comme les anti-patterns les plus courants, consultez cette discussion .

Approches basées sur des ensembles ou procédurales pour écrire des requêtes

Les antipatterns susmentionnés impliquent qu'ils se résument en fait à une différence dans les approches basées sur des ensembles et procédurales pour construire vos requêtes.

L'approche procédurale des requêtes est une approche très similaire à la programmation: vous dites au système quoi faire et comment le faire.

Un exemple de ceci est les conditions excessives dans les connexions ou les cas où vous HAVING conditions HAVING , comme dans les exemples ci-dessus, dans lesquels vous interrogez une base de données en exécutant une fonction puis en appelant une autre fonction, ou vous utilisez une logique qui contient des conditions, des boucles, des fonctions définies par l'utilisateur ( UDF), curseurs, ... pour obtenir le résultat final. Avec cette approche, vous demanderez souvent un sous-ensemble de données, puis un autre sous-ensemble de données, etc.

Sans surprise, cette approche est souvent appelée une requête «étape par étape» ou «ligne par ligne».

Une autre approche est une approche basée sur un ensemble, où vous indiquez simplement quoi faire. Votre rôle consiste à spécifier les conditions ou exigences pour l'ensemble de résultats que vous souhaitez recevoir de la requête. Vous laissez la façon dont vos données sont récupérées aux mécanismes internes qui déterminent la mise en œuvre de la requête: vous laissez le moteur de base de données déterminer les meilleurs algorithmes ou logique de traitement pour exécuter votre requête.

Étant donné que SQL est basé sur un ensemble, il n'est pas surprenant que cette approche soit plus efficace que procédurale, et cela explique également pourquoi, dans certains cas, SQL peut s'exécuter plus rapidement que le code.

Le conseil est une approche basée sur un ensemble d'interrogations. C'est aussi celle que la plupart des grands employeurs de l'industrie des technologies de l'information vous demanderont de maîtriser! Il est souvent nécessaire de basculer entre ces deux types d'approches.

Veuillez noter que si vous avez besoin d'une demande procédurale, vous devriez envisager de la réécrire ou de la refactoriser.

La partie suivante couvrira l'optimisation du plan et des requêtes.