Le SQL : Comprendre le langage à l’origine de 30 ans de domination des SGBDR !

Quelques temps après avoir défini le cadre théorique des bases de données relationnelles, la seconde partie des travaux de Edgar Franck CODD, mathématicien chercheur à IBM et fondateur du SQL, a consisté à mettre sur pied un mécanisme par lequel un logiciel de base de données pourrait traduire et exécuter une requête ensembliste sur une collection ensembliste de relations. Cela est chose faite 4 ans plus tard en 1974 lorsqu’IBM met sur pied le tout premier SGBD ensembliste Système R et le tout premier langage de requête ensembliste SEQUEL qui deviendra plus tard SQL.

System R a démontré que les SGBD adoptant le paradigme relationnel pouvaient fournir des performances adéquates. Le succès expérimental de System R a donné naissance à une pléthore de SGBD commerciaux qualifiés de SGBDR (Systèmes de Gestion de Bases de Données Relationnelles). Parmi ceux-ci, on compte MySQL, Oracle de l’éditeur Oracle, SQL Server de Microsoft, ou encore DB2 d’IBM.  La caractéristique particulière de tous ces SGBDR c’est l’utilisation commune du SQL (Structured Query Langage), le langage de manipulation et d’interrogation des bases de données relationnelles mis au point par IBM avec System R.

Le SQL remplace les applications à pointeurs complexes et fournit un moyen simple d’interroger la base de données. En plus de faire taire les critiques qui pesaient sur la théorie relationnelle de CODD, son succès expérimental, qui est intervenu au même moment que la démocratisation des micro-ordinateurs (les PC) et la mise au point des architectures clients/serveur, a permis à l’approche relationnelle d’éclipser les approches hiérarchiques/réseaux et de dominer le marché des bases de données, même jusqu’à l’heure du Big Data avec le NoSQL, 30 ans plus tard !

Dans cette chronique exhaustive dédiée à l’étude du SQL, nous allons vous aider à comprendre ce que c’est que le SQL, comment l’utiliser, et comment les requêtes SQL sont exécutées. Grâce à ces connaissances, vous comprendrez les raisons qui expliquent qu’il ait permis aux SGBDR de dominer le marché des bases de données pendant si longtemps.

Asseyez-vous, prenez un café, mettez la page en favori, car la lecture va être longue !

1 – Comprendre l’origine du langage SQL

Christopher J Date, l’un des principaux pioniers de l’enseignement du SQL dès son apparition, a fait un constat selon lequel le SQL (et non la théorie relationnelle) est au fondement des compétences de la majorité des professionnels des bases de données relationnelles. Il exprime ce constat en ces termes : « si votre connaissance des bases de données relationnelles dérive uniquement de votre connaissance du SQL, alors il y’a des chances qu’une partie de votre connaissance soit erronée ».

Le problème est qu’en commençant l’apprentissage des bases de données relationnelles par le SQL, on omet son intérêt principal, on ignore sa mécanique interne de fonctionnement et on n’est donc incapable de l’optimiser. Bref, on place les charrues avant les bœufs.

1.1 – Intérêt et usage du SQL

Pour comprendre véritablement le SQL, il faut comprendre la théorie relationnelle, l’un ne va pas sans l’autre. En effet, quelques temps après avoir défini le cadre théorique des bases de données relationnelles, la seconde partie des travaux de CODD a consisté à mettre sur pied un mécanisme par lequel un logiciel de base de données pourrait traduire et exécuter une requête ensembliste sur une collection ensembliste de relations. Cela est chose faite 4 ans plus tard en 1974 lorsqu’IBM met sur pied le tout premier SGBD ensembliste Système R et le tout premier langage de requête ensembliste SEQUEL qui deviendra plus tard SQL.

Le SQL remplace les applications à pointeurs complexes et fournit un moyen simple d’interroger la base de données. Le SQL permet d’exécuter des requêtes ensemblistes sur les tables d’une base de données relationnelle.  Ainsi, l’algèbre relationnelle ou plutôt la théorie des ensembles est le fondement de l’interrogation des bases de données avec le SQL. C’est pourquoi apprendre le SQL sans commencer par la théorie relationnelle c’est placer les charrues avant les bœufs.

Le SQL a réussi à traverser le temps parce qu’il est transparent aux SGBDR et aux utilisateurs. Aujourd’hui, il est plus que jamais le langage standard d’interrogation des bases de données et beaucoup de système de données concurrents aux SGBDR essayent de proposer des versions qui lui sont proches. C’est dire que malgré les multiples tentatives de le déclasser, il a su s’imposer depuis plus de 20 ans sur le marché ultra compétitif des bases de données comme le langage de choix pour l’exploitation des données. 

L’expérience montre et continue de montrer que l’adoption à grande échelle d’une technologie et son succès ne dépendent pas des développeurs, mais des utilisateurs métiers. Le SQL fournit la simplicité nécessaire aux métiers pour le développement de leur base de données. Il a démontré suffisamment de flexibilité pour satisfaire à la fois les besoins des administrateurs, des développeurs et des utilisateurs métiers : les administrateurs mettent en production la base de données et s’assurent de son bon fonctionnement quotidien, les développeurs embarquent le SQL via une API de programmation dans leurs applications, tandis que les utilisateurs métiers l’utilisent de façon « ad hoc » pour envoyer des requêtes distantes à la base de données. C’est d’ailleurs le SQL qui est à l’initiative de la création des approches de Self-Service (Self-Service Business Intelligence et Self-Service Business Analytics), permettant aux utilisateurs d’écrire eux-mêmes leurs requêtes sans être obligés de passer par le service IT de l’entreprise comme c’était le cas auparavant.

Pas étonnant donc que l’apprentissage de la conception et de l’exploitation des bases de données relationnelles élude jusque aujourd’hui encore la théorie relationnelle pour passer directement par le SQL. Cependant, bien qu’étant un langage déclaratif, le SQL n’est que le moyen par lequel exprimer des requêtes ensemblistes. Son utilisation appropriée exige donc une bonne compréhension de la théorie relationnelle.

1.2 – L’algèbre relationnelle : retour aux fondations des bases de données SQL

Nous sommes en 1970 dans un laboratoire d’IBM à San Diego aux Etats Unis. Edgar Franck CODD termine ses recherches, il s’apprête à publier les résultats de ses 10 années de travaux dans son article « A Relational Model of Data for Large Shared Data Banks ». Avec cet article, CODD est très loin de se douter qu’il changera à tout jamais l’histoire des bases de données.

En effet, plusieurs années auparavant, CODD avait vécu la difficulté d’utilisation des bases de données de l’époque, qu’on qualifiait de « bases de données hiérarchiques« , puisqu’en tant que chercheur d’IBM, il travaillait avec elles lors des différents projets importants comme le projet de conquête spatiale APOLLO. Etant donné que dans les bases de données hiérarchiques, les liens entre les enregistrements sont établis à l’aide d’un pointeur, il fallait prédéfinir les chemins d’accès aux données et donc prédéfinir les requêtes d’interrogation de données qui pouvaient être faites sur la base. Toute requête, même simple qui était différente des requêtes préétablies exigeaient de redévelopper les programmes existants ou de développer de nouveaux programmes complexes qui allaient parcourir la base entière pour localiser les enregistrements satisfaisant la requête.

Ces limites ont poussé CODD à travailler sur un tout nouveau paradigme de base de données qu’il a appelé « bases de données relationnelles ». L’objectif de ces bases de données relationnelles étant de dépasser les limites des bases de données existantes et de garantir :

1. l’indépendance entre les applications et les données de la base 
2. l’interrogation de la base de données sans développement de programmes à pointeurs complexes 

Étant mathématicien de formation, ses recherches n’ont pas porté au départ sur l’optimisation par un moyen informatique de la gestion des données. Il a plutôt adopté une approche inverse. Il a d’abord cherché à théoriser un modèle mathématique rigoureux qui servirait de fondation au stockage des données, avant de chercher ensuite un moyen informatique d’implémenter  ce modèle mathématique et d’interroger la base de données.

En s’appuyant sur ses connaissances en algèbre des ensembles (une branche de la théorie des ensembles), CODD finit par conceptualiser une base de données comme étant « une collection ensembliste de données provenant de domaines finis« .  Plus simplement, réduit en modèle mathématique, la structure d’un fichier dans une base de données est définie comme une relation (communément une table) entre les données provenant d’un nombre fini de domaines (communément des champs). Les enregistrements sont des tuples (des enregistrements ou lignes) et constituent des occurrences de la relation. Les liens entre les relations sont assurés sur la base d’un champ de même type, commun aux relations (la clé). Si les champs communs possèdent les mêmes valeurs, les relations sont logiquement liées. Il n’est dès lors plus nécessaire de gérer des pointeurs physiques pour assurer ces liens. De physiques qu’ils étaient dans les bases de données hiérarchiques ou réseau, les liens sont dorénavant logiques, basés sur les valeurs des champs, ce qui rend la navigation entre les enregistrements beaucoup plus souple et favorise l’atteinte de l’objectif que CODD cherchait tant à accomplir : l’indépendance entre les données et les applications. Voilà en un paragraphe, les fondations des bases de données relationnelles et du SQL. Le tableau ci-après récapitule les changements vocabulaire qui ont lieu à chaque fois qu’on quitte de la mathématique pure au concret.

Après avoir défini ce cadre théorique, la seconde partie des travaux de CODD a consisté à mettre sur pied un mécanisme par lequel un logiciel de base de données pourrait traduire et exécuter une requête ensembliste sur cette collection de relations. Cela est chose faite 4 ans plus tard en 1974 lorsqu’IBM met sur pied le tout premier SGBD ensembliste Système R et le tout premier langage de requête ensembliste SEQUEL qui deviendra plus tard SQL. Le SQL remplace les applications à pointeurs complexes et fournit un moyen simple d’interroger la base de données. Ainsi est atteint le second objectif de CODD.  Nous allons analyser en 2 principes l’essentiel de la théorie relationnelle.

Allons en profondeur dans les 2 principes d’algèbre relationnelle qui soutiennent les bases de donnée relationnelles et le SQL.

Principe #1 : une base de données est une collection de relations ensemblistes

Dans sa modélisation mathématique, CODD définit la base de données comme une collection de relations normalisées et identifiées chacune par un nom différent. Ainsi, comprendre la base de données relationnelle c’est d’une part comprendre le concept de relation et d’autre part comprendre le lien qu’elles peuvent entretenir. Regardons tout ceci du côté de la théorie des ensembles. Soit R, un ensemble constitué d’attributs A_i avec i ≥ 0 ; on définit R de la façon suivante :

R = {A₁, A₂, A₃, …, A_n}

Les éléments de A (A_i, les attributs) prennent leurs valeurs dans des ensembles comme ℕ (l’ensemble des entiers naturels, auquel cas ce sont des entiers), ⅅ (l’ensemble des décimaux) ou encore ℝ (l’ensemble des entiers relatifs). Les attributs peuvent également avoir pour valeurs des chaînes de caractère. Dans le jargon courant, on parle de type pour désigner les valeurs acceptables pour un attribut et en mathématique on parle de domaine de définition de l’attribut. Une relation est donc un ensemble de valeurs d’attributs et une base de données, un ensemble de relations. Mathématiquement, on dirait qu’une base de données relationnelle est un ensemble de sous-ensembles.

BD = {R (A₁, A₂, A₃); R (B₁, B₂, B₃, B₄); R (C₁, C₂)}

Concrètement, si on remplaçait tous les symboles mathématiques par de vrais attributs, on aurait une base de données définie par exemple de la façon suivante :

B = {Employes (emp_id, employe_name, employe_adresse, code_dep)
      Departements (code_dep, nom_departement, region_departement) 
     Commandes (cmd_id, date_commande, quantité_commandée, total)
    }

La base de données B est constituée de 3 tables ou trois relations : Employes, Departements, et Commandes. Chacune de ces tables possèdent un ensemble d’attributs qui prennent leurs valeurs dans des ensembles différents. Par exemple la table Departements possède l’attribut region_departement qui prend ses valeurs dans l’ensemble des chaînes de caractère, tandis que l’attribut quantité_commandée de la table Commandes prend ses valeurs dans l’ensemble des entiers naturels ℕ (une quantité est nécessairement un entier naturel positif).

Notez que mathématiquement, les relations peuvent avoir des attributs partagés. En d’autres termes, vous pouvez avoir des attributs qui se retrouvent dans plusieurs relations. Dans l’exemple, code_dep se retrouve à la fois dans les relations Employes  et Departements. Cet attribut partagé permet d’établir qu’il existe un lien de dépendance entre les deux relations. Cela ne pose aucun problème d’un point de vue purement mathématique. Au contraire, cela permet de supprimer le besoin de faire appel aux pointeurs et introduit le concept d’intégrité reférentielle, nécessaire pour assurer la cohérence de la base de données.

De ce premier principe, Il faut retenir que ce qui caractérise une base de données relationnelle, ce sont ses relations et l’ensemble des liens qui peuvent exister entre elles au travers d’attributs partagés. C’est pourquoi on dit qu’une base de données est une collection ensembliste ou collection d’ensembles.

Principe #2 : une relation est un ensemble de données provenant de domaines finis

A ce stade, vous savez mathématiquement ce que CODD entend par « base de données ». Maintenant, il vous faut bien comprendre la notion de relation. Nous avons certes commencé à en parler dans le premier principe, mais il faut que vous alliez plus loin dans votre connaissance de ce concept. Pour vous aider à y arriver, nous allons encore emprunter le chemin de la théorie des ensembles.  La relation est au cœur des bases de données relationnelles. C’est un ensemble au sens algébrique du terme, d’attributs dont les valeurs proviennent de domaines finis.  Le concept de relation ne renvoie donc pas à la même réalité que dans son sens littéraire. La relation n’est pas un lien associatif entre les données comme beaucoup sur le marché le pensent intuitivement, mais bien un ensemble. Ne faites donc pas d’amalgame ! Reprenons du point précédent notre définition mathématique R.  R est un ensemble constitué d’attributs A_i (avec i ≥ 0) défini de la façon suivante :

R = {A₁, A₂, A₃, …, A_n}

Chaque attribut A_i de R prend ses valeurs dans un domaine de définition fini tel que ℕ, ⅅ ou encore ℝ.

Ce domaine de définition caractérise le type de valeurs acceptables pour l’attribut. R telle que défini est une relation, c’est-à-dire un ensemble de valeurs d’attributs appartenant chacune à un domaine bien précis. Le nombre d’attributs d’une relation est appelée le degré de la relation. Par exemple, on désignera par R₃, une relation qui contient 3 attributs, R₂ une relation qui contient 2 attributs, etc. On définit A_ij les valeurs respectives j (avec j > 0) de chaque attribut A_i dans son ensemble de définition de valeurs (ℕ, ℝ, ⅅ, etc.). Chaque couple de valeur j des A_i dans R est un tuple.  Par exemple, soit la relation ternaire ou relation de degré 3 suivante :

R₃ = {(A₁₁, A₂₁, A₃₁); (A₁₂, A₂₂, A₃₂); (A₁₃, A₂₃, A₃₃); (A₁₄, A₂₄, A₃₄)} 

R3 est constitué de 4 tuples distincts. Mathématiquement, ces tuples sont des n-uplets qui peuvent conceptuellement être perçus comme la liste des occurrences de la relation sur l’ensemble de ses attributs. Ainsi, vous voyez que ce qui définit une relation ce ne sont pas ses valeurs, mais c’est d’abord et avant tout ses attributs. Les attributs eux-mêmes sont techniquement définis par leur domaine de définition, c’est-à-dire le type de valeur qu’ils acceptent.

Les relations possèdent chacune un attribut particulier qui identifie chaque tuple ou chaque occurrence de la relation de façon unique : la clé. La clé, encore appelée clé primaire, est un attribut pour lequel les valeurs sont uniques sur l’ensemble des valeurs de tous les tuples et autres attributs d’une relation.  La présence de cet attribut évite la redondance des tuples dans les relations et garantit ainsi l’unicité des données de la base de données tout entière. 

Illustrons ces concepts de relation, attributs, clé primaire et tuple par l’exemple de la relation précédente : Employes(emp_id, employe_name, emp_adress, code_dep). Supposons qu’emp_id est la clé primaire de la relation. Ci-après un exemple de la relation avec ses tuples par attribut.

EMPLOYES
{TUPLE { EMP_ID ‘E1’, EMPLOYE_NAME ‘Chokogoue’, EMP_ADRESS ‘Londres’, CODE_DEP ‘01A’},
TUPLE { EMP_ID ‘E2’ , EMPLOYE_NAME ‘Bastille’ , EMP_ADRESS ‘Paris’ , CODE_DEP ‘01B’},
TUPLE { EMP_ID ‘E3’ , EMPLOYE_NAME ‘Lamartin’ , EMP_ADRESS ‘Naples’, CODE_DEP ‘01A’},
TUPLE { EMP_ID ‘E4’ , EMPLOYE_NAME ‘Brachet’, EMP_ADRESS ‘Richmond’, CODE_DEP ‘01C’},
TUPLE { EMP_ID ‘E5’,  EMPLOYE_NAME ‘Calille’ , EMP_ADRESS ‘DC City’, CODE_DEP ‘01B’}
}

Physiquement, les relations se représentent de façon tabulaire. Le tableau suivant représente la relation EMPLOYES dans sa forme tabulaire. 

L’exemple de la relation EMPLOYES ci-dessus résume bien l’esprit ensembliste de base de données que CODD a mis sur pied et les deux principes fondamentaux qui résument ses travaux à savoir : une base de données est une collection de relations ensemblistes, et une relation est un ensemble de valeurs d’attributs provenant de domaines finis. Ne confondez donc pas une relation avec sa représentation tabulaire. Une relation est un ensemble et une base de données est un ensemble d’ensembles.

2 – Caractéristiques et fonctionnement du langage SQL

Maintenant que vous avez compris les fondements du SQL, il vous sera facile maintenant de comprendre son fonctionnement.

2.1 – Le SQL : un langage de programmation déclarative & non-procédurale

Le langage est un élément très important. Il permet à des individus, à des systèmes ou autre entités intelligentes de communiquer et partager de l’information. Plus la palette du langage est riche et variée et plus large sera le périmètre potentiel de communication à l’aide de ce langage. A ce propos, le philosophe Ludwig Wittgenstein disait tantôt : « les limites de mon langage signifient les limites de mon monde ».

Le but de n’importe quel langage est de communiquer. Celui d’un langage de programmation est de communiquer à un ordinateur les actions qu’il doit exécuter. Un langage de programmation poursuit deux sortes d’objectifs : l’efficience et l’expressivité. L’efficience fait référence à la vitesse avec laquelle les actions codées dans le programme sont transmises au microprocesseur de l’ordinateur. Plus précisément, un langage de programmation efficient est un langage qui dit littéralement à l’ordinateur les actions qu’il doit exécuter, en d’autres termes un langage machine.

L’expressivité quant à elle fait référence à la facilité avec laquelle un programme peut être écrit et compris. Un langage de programmation est expressif à la mesure de la simplicité avec laquelle il permet de spécifier des actions à exécuter.  Par ailleurs, dans un de ses billets de recherche, Facebook affirme de par sa propre expérience que le premier facteur qui affecte la performance d’un système informatique ce n’est pas le système lui-même mais c’est l’expérience de l’utilisateur avec ce système.

Plus le langage dans lequel les utilisateurs vont interagir avec le système est simple, expressif, facile à utiliser et plus le système est adopté. Malheureusement, même si la simplicité d’un langage facilite le système qui l’utilise, la réalité est que développer un langage qui soit à la fois efficient et expressif n’est pas chose aisée. Nous aurons même tendance à dire que l’efficience et l’expressivité sont deux objectifs presque mutuellement exclusifs. Plus on augmente l’efficience d’un langage, plus il tend à perdre en expressivité ; l’inverse tend à se vérifier également. Plus on augmente l’expressivité d’un langage, plus il tend à perdre en efficience. L’efficience accroît le niveau de contrôle que les utilisateurs ont sur la performance de leurs requêtes, tandis que l’expressivité le diminue.

Le développement d’un langage implique donc un arbitrage délicat entre ces deux objectifs. Cet arbitrage a donné naissance à 3 types de langages informatiques pour l’interaction entre les utilisateurs et un système informatique : les langages déclaratifs (ou programmation déclarative), les langages fonctionnels (programmation fonctionnelle) comme le langage Scala et les langages procéduraux (programmation procédurale). Le SQL est un langage de programmation déclarative et non-procédurale.

D’une manière générale, un langage déclaratif est un langage qui donne la possibilité d’écrire ses programmes sous forme d’instructions déclaratives. Ici l’utilisateur définit le « QUOI » de ce qu’il souhaite obtenir à l’aide d’un ensemble de mots clés ou clauses, et le moteur du langage se charge de traduire ces mots clés en plan d’exécution qu’il transmet au système. Ce type de langage met plus l’accent sur l’expressivité que sur l’efficience. Grâce à cela, il est le préféré des utilisateurs, spécialement des utilisateurs métiers, qui n’ont pas une formation d’Informatique à la base.

Le SQL est sans aucun doute le plus simple et le plus connu de ces types de langage.  En programmation distribuée, on note également le HiveQL, un langage dérivé du SQL, très appréciée pour la programmation dans un cluster Hadoop.  Le script suivant est une illustration d’une requête écrite avec un langage déclaratif. Les instructions CREATE APPLICATION, CREATE INPUT TABLE, FROM SCRIBE, SELECT, FROM sont des clauses, avec une signification humaine intuitive qui permettent aux utilisateurs d’exprimer aisément leurs problèmes sous forme de requêtes. Le SQL fonctionne sur le même principe.

CREATE APPLICATION top_events;
CREATE INPUT TABLE events_score(
    event_time,
    event,
    category,
    score
)
FROM SCRIBE("events_stream")
TIME event_time;

CREATE TABLE top_events_5min AS
SELECT
    category,
    event,
    topk(score) AS score
FROM events_score [5 minutes]

La programmation procédurale offre des instructions conditionnelles (IF….ELSE…), des structures itératives (WHEN….THEN, FOR EACH….) et des expressions logiques qui permettent d’exprimer des problèmes plus complexes. Le SQL malheureusement n’est pas un langage procédural, cela signifie que vous ne pouvez pas par exemple gérer des exécutions conditionnelles grâce à des expressions comme SI…ALORS SI… Même si des versions procédurales existent, le SQL est par définition un langage déclaratif et non-procédural.

 Gardez à l’esprit que la simplicité du langage déclaratif exige que les requêtes fassent l’objet d’une optimisation à l’aide d’un plan d’exécution et d’une traduction en langage machine par le système. Le SQL n’y fait pas exception. Toutes les requêtes SQL sont compilées et traduite en plan d’exécution par le SGBDR avant d’être exécutées.  Nous y reviendrons dans les points suivants.

2.2 – Fonctionnement du SQL et rédaction des requêtes SQL

Pour permettre aux utilisateurs d’exprimer des requêtes ensemblistes, le SQL s’appuie sur un ensemble de clauses correspondant chacune à une opération ou une contrainte algébrique relationnelle. Il intègre toutes les opérations prévues dans l’algèbre relationnelle (restriction, projection, produit cartésien, etc.) et permet de les effectuer.

Voyez le SQL comme une interface de communication qui sert de passerelle entre la théorie relationnelle et l’informatique. Juste après sa création en 1974, ses clauses ont fait l’objet d’une normalisation par l’ANSI. Peu de temps après, l’ISO (Organisation International de Standardisation ) s’est mis d’accord avec l’ANSI et tous les deux ont proposé une norme commune pour le SQL qui a été de suite adopté sur le marché. La première version mature de cette norme est connue sur le nom de SQL-92 (SQL – ISO 92 ou SQL-ANSI 92).

Avec l’évolution des besoins du marché, cette norme a été révisée et de nouvelles clauses y ont été rajoutées pour couvrir ses manquements. C’est ainsi que sont apparues les normes SQL-97, SQL-2003, SQL-2006 et plus récemment SQL-2011. En pratique, il n’y’a pas eu d’innovation majeure entre toutes ces nouvelles versions, sauf les fonctions de fenêtrage introduites à partir de la norme SQL-2003, qui permettent désormais aux SGBDR d’effectuer des opérations de fenêtrage sur les lignes d’une table. Par conséquent, il est attendu d’un SGBDR l’implémentation minimale d’au moins toutes les clauses prévues par la norme SQL-92.

Pour exprimer les opérations de l’algèbre relationnelle, le SQL se compose de deux catégories de clauses ou d’instructions : le langage de définition de données (DDL – Data Definition Langage) et le langage d’interrogation de données (DQL – Data Query Langage).

2.2.1 – Le langage de définition des données

C’est l’ensemble des clauses SQL qui permettent de définir la structure de la base de données, c’est-à-dire définir les tables, les associations entre elles, les différentes contraintes d’intégrité référentielle et sémantique qui pèsent sur elles. Ces clauses permettent également de modifier la structure de la base de données, de changer le type d’une colonne par exemple, ou encore ajouter/modifier les contraintes sur une table. Plus précisément, dans la norme SQL-ISO 92, le langage de définition de données est composé de 3 clauses de base : CREATE TABLE, ALTER TABLE, et CONSTRAINT. Bien évidemment, ce nombre peut varier en fonction du SGBDR. Le tableau suivant donne le rôle illustré de chacune de ces clauses.

2.2.2 – Le langage d’interrogation des données

La deuxième catégorie de clauses du SQL forme ce qu’on appelle communément le langage d’interrogation des données. Celui-ci est l’ensemble des clauses qui permettent d’interroger les données dans la base de données, autrement dit d’exprimer et d’adresser des calculs ensemblistes au SGBDR. Ce sont ces clauses qui permettent d’effectuer les opérations de l’algèbre relationnelle telles que les jointures, le produit cartésien, la restriction, ou la projection.

En plus de cela, elles permettent d’effectuer des opérations élémentaires de manipulation de données telles que l’insertion, la suppression de lignes dans une table ou la modification des lignes existantes dans une table. Elles offrent également un ensemble de fonctions mathématiques qui permettent de construire des prédicats, d’effectuer des calculs agrégés (moyenne, somme, total, etc.) et des calculs élémentaires comme la concaténation de texte, le modulo de division de valeurs de deux colonnes, ainsi de suite.  A partir de la norme SQL-2003, elles prennent également en compte les fonctions de fenêtrage. La principale clause ici est bien évidemment la fameuse clause SELECT. Le tableau suivant indique exemple à l’appui l’ensemble de ces clauses.

En réalité, ce ne sont pas ces clauses SQL qui en arrière-plan exécutent les calculs ensemblistes, mais le moteur du SGBDR.  Elles indiquent juste au moteur quels opérateurs algébriques le moteur doit exécuter. Le moteur transforme ces clauses en plan d’exécution optimisé qui par la suite est transformé en instructions machines. Nous y reviendrons dans le point suivant. Le tableau ci-après montre la correspondance entre les clauses et les opérateurs relationnels qu’ils permettent de déclencher.

Maintenant que vous avez compris comment fonctionne le SQL, il est temps de comprendre le processus par lequel les requêtes SQL sont exécutées dans un SGBDR, car c’est la connaissance de ce processus seul qui vous permettra d’écrire du code SQL performant.

3 – Exécution des requêtes SQL dans un SGBDR

Le SQL est un langage très expressif pour les utilisateurs métiers, il leur permet d’exprimer dans un style simple et non-mathématique, des requêtes ensemblistes. Il est important que vous sachiez comment le SGBDR exécute ces requêtes, puisqu’après tout, la base de données relationnelle reste un ensemble au sens mathématique du terme et non un fichier informatique. L’objectif de ce point est de vous expliquer comment les SGBDR transforment les requêtes SQL en opérations d’algèbre relationnelle et comment il les exécute. C’est la connaissance de ce processus seul qui vous permettra d’écrire un code SQL optimisé et performant.

3.1 – Processus d’exécution globale du code SQL dans un SGBD

En réalité, le SQL fonctionne comme une abstraction, il permet à l’utilisateur de décrire le QUOI sans décrire le COMMENT. L’exécution de toute requête SQL dans un SGBDR passe par 3 phases :

1. La vérification syntaxique : dans une première étape, la syntaxe du code SQL écrit et soumis par l’utilisateur est vérifiée par le système. Le SGBDR s’assure que celui-ci est conforme aux spécifications de la norme SQL qu’il supporte (ANSI SQL-92, 2003, etc.). Si l’utilisateur a mal saisi une instruction, s’est trompé sur l’écriture d’une clause, ou a écrit une instruction non-supportée par la norme SQL employée par le SGBDR, alors le système génère une erreur et le programme s’arrête ;
2. La création d’un plan optimal d’exécution : dans une seconde étape, lorsque la syntaxe de la requête est validée, la requête SQL est transformée en plan d’exécution, un arbre de décision hiérarchique similaire à un graphe acyclique direct qui indique au moteur du SGBDR l’ordre dans lequel les clauses de la requête doivent être exécutées. Ne soyez pas intimidés par l’expression « graphe acyclique direct ».  Un graphe est simplement la description de l’ordre dans lequel les opérations d’un programme doivent être traitées pour répondre au besoin pour lequel le programme a été exécuté. Dans le cas d’une requête SQL, c’est l’ordre dans lequel les clauses de la requête doivent être traitées. Par exemple, considérons la requête suivante :

SELECT sum (vente) FROM customers WHERE age_client BETWEEN 25 AND 30 GROUP BY genre; 

Le plan d’exécution de cette requête est le suivant :

La requête est transformée en un plan de 5 étapes pour l’obtention des résultats souhaités. Au départ, une opération permet de lire la table Customers, par la suite le système effectue un filtre sur la colonne de l’âge du client pour conserver uniquement les clients dont l’âge est situé entre 25 et 30 ans compris, un regroupement de ces clients par genre sur la table filtrée est réalisé, le calcul de la somme des ventes sur les clients ainsi groupés par genre et filtrés par âge est effectué et enfin les résultats sont enregistrés dans une table temporaire puis restitués au client. 

Le graphe est l’ensemble des 5 boîtes (rectangles pour illustrer les tables de données et cercles pour illustrer les traitements de données) et des flèches. Les boîtes s’appellent formellement les vertices, tandis que les flèches, les arcs. Un graphe est donc un ensemble de vertices qui s’enchaînent selon l’ordre précisé par un ensemble d’arcs. Le graphe de notre requête est acyclique, car aucune vertice n’est itératif et relié à un vertice précédent ; il est également direct, car les arcs indiquent le sens des opérations. Dans certains cas, les arcs peuvent ne pas indiquer le sens des opérations, le graphe sera alors qualifié de non-orienté. Le plan d’exécution d’une requête SQL produit par un SGBR est ungraphe acyclique Orienté ou acyclique direct (Directed Acyclique Graph – DAG), l’enchaînement des opérations entre les clauses est direct et sans détour, aucune opération n’est itérative (ou cyclique).   

Remarquez dans l’exemple que l’ordre ou chemin du plan d’exécution n’est pas unique. Le système pourrait tout aussi bien commencer par regrouper les données par genre, avant de calculer la somme et de filtrer par âge. Ainsi, le système a plusieurs choix dans le cheminement du plan d’exécution d’une requête SQL. Ce constat soulève une question : comment le système fait-il pour choisir le cheminement des opérations du plan d’exécution de la requête ? Au passage, notez que chaque cheminement dans l’exécution des requêtes ne fournit pas la même performance. Le fait de faire une somme avant d’effectuer un filtre sur les données ne s’exécute pas aussi rapidement que le fait de filtrer avant d’effectuer la somme. Autrement dit, les cheminements possibles du plan d’exécution ne se valent pas en termes de performance. Pour répondre à la question, le système calcul le chemin optimal pour l’exécution de chaque requête SQL soumise. En d’autres termes, le SGBDR va déterminer la combinaison d’enchaînement des clauses SQL qui est la plus rapide à exécuter. C’est pourquoi nous parlons de la création d’un plan optimal d’exécution. Heureusement, ce calcul ne se fait pas de façon aléatoire. Il se fait selon des règles précises qui ont déjà été définies par CODD. Cela ne laisse donc aucun hasard dans l’optimisation de la performance des requêtes SQL. 

1. La compilation et l’exécution de la requête : une fois que le plan d’exécution est produit, la requête est compilée en instructions machine et ce sont ces instructions qui sont exécutées par le processeur de la machine sur laquelle le SGBDR est installé. Ces instructions sont exécutées dans l’ordre du cheminement séquentiel spécifié par le plan de la requête.

 La figure suivante illustre le processus globale d’exécution d’une requête SQL ;

3.2 – Exécution du SQL dans un environnement distribué

Un petit point d’attention quant à l’exécution du SQL dans un environnement distribué. Lorsque la base de données est développée pour des besoins de Reporting à grande échelle et est installée sur des SGBDR parallèles capables de s’exécuter sur plusieurs machines dans un environnement distribué (tel que Teradata), le schéma d’exécution que nous avons vu précédemment ne s’applique pas.  Le processus général reste le même, mais le schéma d’exécution change.  Les requêtes SQL sont décomposées en processus parallèles, et exécutées de façon concurrente sur tous les nœuds contenant une copie de la base de données.  La vérification syntaxique et la construction du plan d’exécution sont faites au niveau de la machine centrale de l’environnement distribuée, tandis que la compilation et l’exécution du code sont faites par les machines de calcul. La figure suivante illustre cette exécution.

L’exécution des requêtes SQL en environnement distribué, les bases de données parallèles, sont un vaste sujet auquel nous avons consacré tout un ouvrage : Hadoop – Devenez opérationnel dans le monde du Big Data. Nous allons maintenant revenir plus en profondeur sur la façon dont le SGBDR détermine le plan optimal d’exécution du SQL.

3.3 – Ordre d’exécution des clauses SQL

Précédemment, nous vous avons montré que le système avait le choix quant au cheminement à adopter pour exécuter une requête et que tous les cheminements ne fournissent pas la même performance. Pour déterminer le meilleur chemin, ou le chemin le plus optimal d’exécution de la requête, le SGBDR s’appuie sur une recette simple. C’est cette recette que nous allons vous montrer dans ce point. 

Avant d’aller en profondeur, ne perdez pas de vue que les SGBDR sont des systèmes ensemblistes, c’est-à-dire des systèmes capables d’exécuter des requêtes d’algèbre relationnelle (donc des requêtes ensemblistes) sur les données qu’ils hébergent. Ceci permet de vous rappeler que les fondations des SGBDR ne sont pas technologiques, mais mathématiques. Ainsi, toute opération qui y est faite l’est uniquement parce que mathématiquement, elle est conforme aux principes de la théorie des ensembles. 

En s’appuyant sur ces principes, le SGBDR utilise la recette suivante pour construire le plan d’exécution optimal d’une requête : il détermine l’ordre d’exécution des opérateurs algébriques qui consomme le moins de temps possible. Souvenez-vous que d’un point de vue mathématique, une requête SQL est un ensemble d’opérateurs algébriques appliqués sur des tuples ou des colonnes d’une table qui produisent d’autres tuples ou une autre table. D’un point de vue des ensembles, cela revient à exécuter par ordre de priorité les opérateurs qui minimisent la quantité de données à manipuler. Illustrons cela par des exemples de complexité progressive. Considérons la première requête SQL suivante :

SELECT id_client, nom_client, montant FROM customers WHERE age_client BETWEEN 25 AND 30 order by id_client ; 

Cette requête est composée de 3 opérateurs : une projection (SELECT), une restriction (WHERE) et un tri (ORDER BY).  Son ordre d’exécution optimal est la suivante :

Comme vous le constatez, il est plus optimal de commencer l’exécution de la requête par le filtre. Celui-ci réduit la quantité de données qui sera traitée par le système. Ensuite, la projection est effectuée pour réduire le nombre de colonnes traitées (et donc le volume de données) et enfin le tri est effectué sur les données restantes. Si le tri avait été effectué en premier lieu, le calcul serait sous-optimal car le système effectuerait le tri sur toutes les données de la table d’entrée (mêmes celles qui seraient rejetés par la restriction), ce qui prendrait plus de temps et serait inutile puisque celles-ci seront filtrées par la suite. Le même raisonnement s’applique si la projection avait été effectuée en premier. Prenons un autre exemple. Supposons la requête suivante :

SELECT id_client, sum(montant) as ca FROM customers GROUP BY id_client WHERE age_client > 25 ; 

La figure ci-après indique le plan d’exécution optimal de la requête.

La restriction est effectuée au début pour filtrer (donc réduire) la quantité de données à traiter, ensuite la projection pour réduire le nombre de colonnes à manipuler et enfin la somme est effectuée sur le jeu de données obtenu. Tout comme dans la requête précédente, le système pourrait bien commencer par sommer les données par id_client, mais cela ne serait pas optimal, car l’ensemble des données serait concerné par l’opération, augmentant ainsi le temps d’exécution de la requête.  Prenons un dernier exemple, cette fois-ci le cas de l’exécution d’une jointure.  Soit la requête suivante :

SELECT Nom_department, nom_employe,  age_employe  
FROM department  INNER JOIN employes 
ON  id_dep = emp_id_dep 
WHERE employes.age_employe > 30 ;

Dans cette requête, on cherche à obtenir la liste des employés âgés de plus de 30 ans et le département dans lequel ils travaillent.  Cette requête met enjeu 3 opérateurs : la projection, la restriction et la jointure. Voici son plan d’exécution optimal.

La restriction est exécutée en premier sur la table employes, ce qui réduit d’entrée de jeu la quantité de données à utiliser pour l’équi-jointure. Ensuite, un produit cartésien des deux tables est fait (une jointure s’effectue en deux phases : un produit cartésien et un filtre sur ce produit). Par la suite la projection est réalisée pour enfin obtenir la table des résultats. Vous l’aurez compris, l’optimisation du plan de requête réalisé par le système passe par l’exécution en priorité des opérateurs qui minimise la quantité de données qui va être utilisée dans la suite du plan.

Ceci conclut notre chronique sur le SQL. Nous espérons que vous avez compris les atouts qui lui permettent jusque aujourd’hui de s’imposer dans le monde des bases de données. Si vous souhaitez aller plus loin dans votre apprentissage dans les bases de données, téléchargez notre livre numérique suivant, il vous aidera à comprendre l’écosystème des technologies principales utilisées pour valoriser les données dans le contexte de Big Data actuel.