Qu'est-ce que SQL ? La lingua franca de l'analyse des données

Aujourd’hui, le langage de requête structuré est le moyen standard de manipuler et d’interroger les données dans les bases de données relationnelles, avec toutefois des extensions propriétaires parmi les produits. La facilité et l’omniprésence de SQL ont même conduit les créateurs de nombreux magasins de données « NoSQL » ou non relationnels, comme Hadoop, à adopter des sous-ensembles de SQL ou à imaginer leurs propres langages de requête de type SQL.

Mais SQL n’a pas toujours été le langage « universel » des bases de données relationnelles. Dès le début (vers 1980), SQL a eu certains coups durs contre lui. De nombreux chercheurs et développeurs de l’époque, dont moi, pensaient que les frais généraux de SQL l’empêcheraient d’être jamais pratique dans une base de données de production.

De toute évidence, nous avions tort. Mais beaucoup pensent encore que, malgré toute la facilité et l’accessibilité de SQL, le prix exigé en termes de performances d’exécution est souvent trop élevé.

Histoire de SQL

Avant qu’il n’y ait SQL, les bases de données avaient des interfaces de programmation serrées et axées sur la navigation, et étaient généralement conçues autour d’un schéma de réseau appelé modèle de données CODASYL. CODASYL (Committee on Data Systems Languages) était un consortium responsable du langage de programmation COBOL (à partir de 1959) et des extensions du langage de base de données (à partir de 10 ans plus tard).

Lorsque vous programmiez contre une base de données CODASYL, vous naviguiez vers les enregistrements par le biais d’ensembles, qui expriment des relations de un à plusieurs. Les anciennes bases de données hiérarchiques ne permettent à un enregistrement d’appartenir qu’à un seul ensemble. Les bases de données en réseau permettent à un enregistrement d’appartenir à plusieurs ensembles.

Supposons que vous vouliez répertorier les étudiants inscrits en CS 101. Tout d’abord, vous trouveriez "CS 101" dans l’ensemble Courses par le nom, définissez cela comme le propriétaire ou le parent de l’ensemble Enrollees, trouver le premier membre (ffm) de l’ensemble Enrollees, qui est un enregistrement Student, et le lister. Ensuite, vous entreriez dans une boucle : Trouver le membre suivant (fnm) et le lister. Lorsque fnm échouait, vous sortiez de la boucle.

Cela peut sembler être beaucoup de travail de bûcheron pour le programmeur de base de données, mais c’était très efficace au moment de l’exécution. Des experts comme Michael Stonebraker de l’Université de Californie à Berkeley et d’Ingres ont souligné que faire ce genre de requête dans une base de données CODASYL comme IDMS prenait environ la moitié du temps CPU et moins de la moitié de la mémoire que la même requête sur une base de données relationnelle utilisant SQL.

Pour comparaison, la requête SQL équivalente pour renvoyer tous les étudiants en CS 101 serait quelque chose comme

SELECT student.name FROM courses, enrollees, students WHERE course.name ="CS 101"

Cette syntaxe implique une jointure interne relationnelle (en fait deux d’entre elles), comme je l’expliquerai ci-dessous, et laisse de côté certains détails importants, comme les champs utilisés pour les jointures.

Bases de données relationnelles et SQL

Pourquoi renoncer à une amélioration d’un facteur deux de la vitesse d’exécution et de l’utilisation de la mémoire ? Il y avait deux grandes raisons : la facilité de développement et la portabilité. Je ne pensais pas que l’une ou l’autre comptait beaucoup en 1980 par rapport aux performances et aux besoins en mémoire, mais à mesure que le matériel informatique s’est amélioré et est devenu moins cher, les gens ont cessé de se soucier de la vitesse d’exécution et de la mémoire et se sont davantage préoccupés du coût du développement.

En d’autres termes, la loi de Moore a tué les bases de données CODASYL au profit des bases de données relationnelles. En l’occurrence, l’amélioration du temps de développement a été significative, mais la portabilité SQL s’est avérée être une chimère.

D’où viennent le modèle relationnel et SQL ? E.F. « Ted » Codd était un informaticien du laboratoire de recherche IBM de San Jose qui a élaboré la théorie du modèle relationnel dans les années 1960 et l’a publiée en 1970. IBM a tardé à mettre en œuvre une base de données relationnelle dans le but de protéger les revenus de sa base de données CODASYL IMS/DB. Lorsqu’IBM a finalement lancé son projet System R, l’équipe de développement (Don Chamberlin et Ray Boyce) n’était pas sous la direction de Codd, et ils ont ignoré l’article de 1971 de Codd sur le langage relationnel Alpha pour concevoir leur propre langage, SEQUEL (Structured English Query Language). En 1979, avant même qu’IBM n’ait lancé son produit, Larry Ellison a incorporé le langage dans sa base de données Oracle (en utilisant les publications SEQUEL d’IBM avant le lancement comme spécification). SEQUEL est rapidement devenu SQL pour éviter une violation de marque internationale.

Les « tom-toms battant pour SQL » (comme l’a dit Michael Stonebraker) venaient non seulement d’Oracle et d’IBM, mais aussi des clients. Il n’était pas facile d’embaucher ou de former des concepteurs de bases de données et des programmeurs CODASYL, alors SEQUEL (et SQL) semblait beaucoup plus attrayant. SQL était si attrayant à la fin des années 1980 que de nombreux fournisseurs de bases de données ont essentiellement agrafé un processeur de requête SQL sur leurs bases de données CODASYL, au grand dam de Codd, qui estimait que les bases de données relationnelles devaient être conçues à partir de zéro pour être relationnelles.

Une base de données relationnelle pure, telle que conçue par Codd, est construite sur des tuples regroupés en relations, en cohérence avec la logique des prédicats du premier ordre. Les bases de données relationnelles du monde réel ont des tables qui contiennent des champs, des contraintes et des déclencheurs, et les tables sont reliées par des clés étrangères. SQL est utilisé pour déclarer les données à renvoyer, et un processeur de requêtes SQL et un optimiseur de requêtes transforment la déclaration SQL en un plan de requête qui est exécuté par le moteur de base de données.

SQL comprend un sous-langage pour définir les schémas, le langage de définition des données (DDL), ainsi qu’un sous-langage pour modifier les données, le langage de manipulation des données (DML). Ces deux langages sont issus des premières spécifications CODASYL. Le troisième sous-langage de SQL déclare les requêtes, par le biais de l’instruction SELECT et des jointures relationnelles.

L’instruction SQL SELECT

L’instruction SELECT indique à l’optimiseur de requêtes les données à retourner, les tables à consulter, les relations à suivre et l’ordre à imposer aux données retournées. L’optimiseur de requêtes doit déterminer par lui-même quels index utiliser pour éviter les balayages de tables par force brute et obtenir de bonnes performances de requête, à moins que la base de données particulière ne prenne en charge les indices d’indexation.

Une partie de l’art de la conception de bases de données relationnelles repose sur l’utilisation judicieuse des index. Si vous omettez un index pour une requête fréquente, l’ensemble de la base de données peut ralentir sous de fortes charges de lecture. Si vous avez trop d’index, toute la base de données peut ralentir sous de fortes charges d’écriture et de mise à jour.

Un autre art important consiste à choisir une bonne clé primaire unique pour chaque table. Vous devez non seulement tenir compte de l’impact de la clé primaire sur les requêtes courantes, mais aussi de la manière dont elle jouera dans les jointures lorsqu’elle apparaîtra comme clé étrangère dans une autre table, et de la manière dont elle affectera la localité de référence des données.

Dans le cas avancé des tables de base de données qui sont réparties en différents volumes en fonction de la valeur de la clé primaire, appelé sharding horizontal, vous devez également tenir compte de la manière dont la clé primaire affectera le sharding. Conseil : vous voulez que la table soit répartie uniformément sur les volumes, ce qui suggère que vous ne voulez pas utiliser des timbres de date ou des entiers consécutifs comme clés primaires.

Les discussions sur l’instruction SELECT peuvent commencer simplement, mais peuvent rapidement devenir confuses. Considérez :

SELECT * FROM Customers;

Simple, non ? Il demande tous les champs et toutes les lignes de la table Customers. Supposons toutefois que le tableau Customers comporte cent millions de lignes et cent champs, et que l’un des champs est un grand champ de texte pour les commentaires. Combien de temps faudra-t-il pour descendre toutes ces données sur une connexion réseau de 10 mégabits par seconde si chaque ligne contient en moyenne 1 kilo-octet de données ?

Parfois, vous devriez réduire la quantité de données que vous envoyez sur le fil. Considérez:

SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers
WHERE state ="Ohio" AND city ="Cleveland"
ORDER BY lastSaleDate DESCENDING;

Maintenant, vous allez tirer beaucoup moins de données. Vous avez demandé à la base de données de ne vous donner que quatre champs, de ne prendre en compte que les entreprises de Cleveland, et de ne vous donner que les 100 entreprises ayant les ventes les plus récentes. Pour faire cela le plus efficacement possible au niveau du serveur de la base de données, cependant, la table Customers a besoin d’un index sur state+city pour la clause WHERE et un index sur lastSaleDate pour les clauses ORDER BY et TOP 100.

A propos, TOP 100 est valable pour SQL Server et SQL Azure, mais pas pour MySQL ou Oracle. Dans MySQL, vous utiliseriez LIMIT 100 après la clause WHERE. Dans Oracle, vous utiliseriez une borne sur ROWNUM dans le cadre de la clause WHERE, c’est-à-dire WHERE... AND ROWNUM <=100. Malheureusement, les normes ANSI/ISO SQL (et il y en a neuf à ce jour, s’étendant de 1986 à 2016) ne vont que jusqu’à un certain point, au-delà duquel chaque base de données introduit ses propres clauses et caractéristiques propriétaires.

Les jointures SQL

Jusqu’ici, j’ai décrit la syntaxe SELECT pour les tables simples. Avant que je puisse expliquer les clauses JOIN, vous devez comprendre les clés étrangères et les relations entre les tables. Je vais expliquer cela en utilisant des exemples dans DDL, en utilisant la syntaxe de SQL Server.

La version courte de ceci est assez simple. Chaque table que vous voulez utiliser dans les relations doit avoir une contrainte de clé primaire ; cela peut être un champ unique ou une combinaison de champs définis par une expression. Par exemple :

CREATE TABLE Persons (
PersonID int NOT NULL PRIMARY KEY,
PersonName char(80),
...

Chaque table qui doit se rapporter à Persons doit avoir un champ qui correspond à la Persons clé primaire, et pour préserver l’intégrité relationnelle, ce champ doit avoir une contrainte de clé étrangère. Par exemple :

CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
...
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

Il existe des versions plus longues de ces deux déclarations qui utilisent le mot-clé CONSTRAINT, qui vous permet de nommer la contrainte. C’est ce que génèrent la plupart des outils de conception de bases de données.

Les clés primaires sont toujours indexées et uniques (les valeurs des champs ne peuvent pas être dupliquées). Les autres champs peuvent éventuellement être indexés. Il est souvent utile de créer des index pour les champs de clés étrangères et pour les champs qui apparaissent dans les clauses WHERE et ORDER BY, mais pas toujours, en raison de la surcharge potentielle due aux écritures et aux mises à jour.

Comment écririez-vous une requête qui renvoie toutes les commandes passées par John Doe?

SELECT PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
WHERE PersonName ="John Doe";

En fait, il existe quatre types de JOININNEROUTERLEFT, et RIGHT. La INNER JOIN est la valeur par défaut (vous pouvez omettre le mot INNER), et c’est celle qui inclut uniquement les lignes qui contiennent des valeurs correspondantes dans les deux tables. Si vous voulez lister les personnes, qu’elles aient ou non des commandes, vous utiliserez un LEFT JOIN, par exemple :

SELECT PersonName, OrderID FROM Persons
LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY PersonName;

Lorsque vous commencez à faire des requêtes qui joignent plus de deux tables, qui utilisent des expressions ou qui contraignent les types de données, la syntaxe peut devenir un peu velue au début. Heureusement, il existe des outils de développement de bases de données qui peuvent générer des requêtes SQL correctes pour vous, souvent en glissant et en déposant des tables et des champs du diagramme de schéma dans un diagramme de requête.

Procédures stockées SQL

Parfois, la nature déclarative de l’instruction SELECT ne vous mène pas là où vous voulez aller. La plupart des bases de données disposent d’une fonction appelée procédures stockées ; malheureusement, il s’agit d’un domaine où presque toutes les bases de données utilisent des extensions propriétaires des normes SQL ANSI/ISO.

Dans SQL Server, le dialecte initial pour les procédures stockées (ou procs stockés) était Transact-SQL, alias T-SQL ; dans Oracle, c’était PL-SQL. Les deux bases de données ont ajouté des langages supplémentaires pour les procédures stockées, tels que C#, Java et R. Une procédure stockée T-SQL simple pourrait n’être qu’une version paramétrée d’une déclaration SELECT. Ses avantages sont la facilité d’utilisation et l’efficacité. Les procédures stockées sont optimisées lorsqu’elles sont enregistrées, et non à chaque exécution.

Une procédure stockée T-SQL plus compliquée pourrait utiliser plusieurs instructions SQL, des paramètres d’entrée et de sortie, des variables locales, des BEGIN...END blocs, des IF...THEN...ELSE conditions, des curseurs (traitement ligne par ligne d’un ensemble), des expressions, des tables temporaires et toute une série d’autres syntaxes procédurales. Il est évident que si le langage de la procédure stockée est C#, Java ou R, vous allez utiliser les fonctions et la syntaxe de ces langages procéduraux. En d’autres termes, malgré le fait que la motivation de SQL était d’utiliser des requêtes déclaratives standardisées, dans le monde réel, vous voyez beaucoup de programmation de serveur procédurale spécifique à la base de données.

Cela ne nous ramène pas tout à fait au mauvais vieux temps de la programmation de base de données CODASYL (bien que les curseurs s’en approchent), mais cela fait reculer les idées que les déclarations SQL devraient être standardisées et que les préoccupations de performance devraient être laissées à l’optimiseur de requête de base de données. Au final, un doublement des performances est souvent trop important pour être laissé sur la table.

Apprendre SQL

Les sites listés ci-dessous peuvent vous aider à apprendre SQL, ou à découvrir les bizarreries des différents dialectes SQL.

Codecademy. Apprenez SQL. Gratuit et interactif. Mise à niveau Pro disponible moyennant des frais.
Khan Academy. Intro à SQL : Interroger et gérer les données. Tutoriel vidéo gratuit.
SoloLearn. Fondamentaux de SQL. Orienté MySQL. Gratuit.
Problèmes et solutions SQL et Exercices SQL. Manuel et exercices interactifs. Gratuit.
SQLZoo. Un tutoriel SQL interactif développé et maintenu par l’Université Napier d’Édimbourg. Prend en charge Microsoft SQL Server, Oracle Database, MySQL, IBM DB2 et PostgreSQL. Gratuit.
Tutorialspoint. Apprenez SQL. Texte seulement, non interactif. Gratuit.
Udacity. Intro aux bases de données relationnelles. Utilise Python et nécessite une certaine connaissance de Python. Gratuit.
Udemy. Parmi les cours gratuits, citons Introduction aux bases de données et aux requêtes SQL, Base de données MySQL pour les débutants, Microsoft SQL pour les débutants, Hands-on SQL pour les débutants (SELECTFROM, et WHERE), et Sachin Quickly Learns (SQL).
Vertabelo Academy. Les bases de SQL, Opérer sur des données en SQL, Créer des tables en SQL, et huit autres cours interactifs de SQL. Certains cours ont des essais gratuits, après quoi il peut y avoir des frais. Il existe sept cours supplémentaires pour Microsoft SQL Server. Le site dispose également d’un outil graphique de conception de bases de données pour PostgreSQL, MySQL, Oracle Database, SQL Server, SQLite et IBM DB2.
W3Schools. Tutoriel SQL. Gratuit et disponible sans inscription.

Documentation sur les bases de données :