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 des données dans des bases de données relationnelles, mais avec 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, tels que Hadoop, à adopter des sous-ensembles de SQL ou à proposer 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. Depuis le début (vers 1980), SQL a eu certaines attaques contre lui. De nombreux chercheurs et développeurs à l'époque, dont moi, pensaient que la surcharge de SQL l'empêcherait d'être pratique dans une base de données de production.

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

Historique SQL

Avant qu'il y ait SQL, les bases de données avaient des interfaces de programmation de navigation étroites et étaient généralement conçues autour d'un schéma de réseau appelé modèle de données CODASYL. CODASYL (Comité sur les langages des systèmes de données) était un consortium qui était responsable du langage de programmation COBOL (à partir de 1959) et des extensions de langage de base de données (à partir de 10 ans plus tard).

Lorsque vous avez programmé sur une base de données CODASYL, vous naviguiez vers des enregistrements à travers des ensembles, qui expriment des relations un-à-plusieurs. Les anciennes bases de données hiérarchiques n'autorisent qu'un enregistrement à appartenir à un ensemble. Les bases de données réseau permettent à un enregistrement d'appartenir à plusieurs ensembles.

Supposons que vous vouliez lister les étudiants inscrits à CS 101. Vous devez d'abord rechercher "CS 101"dans l' Coursesensemble par nom, définir cela en tant que propriétaire ou parent de l' Enrolleesensemble, trouver le premier membre ( ffm) de l' Enrolleesensemble, qui est un Studentenregistrement, et lister il. Ensuite, vous entreriez dans une boucle: Trouvez le membre suivant ( fnm) et listez-le. En cas d' fnméchec, vous sortiriez de la boucle.

Cela peut sembler beaucoup de travail de scut 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 Ingres ont souligné que faire ce type de requête dans une base de données CODASYL telle que IDMS prenait environ la moitié du temps processeur et moins de la moitié de la mémoire pour la même requête sur une base de données relationnelle utilisant SQL. .

À titre de comparaison, la requête SQL équivalente pour renvoyer tous les étudiants dans CS 101 serait quelque chose comme 

SELECT student.name FROM cours, inscrits, étudiants WHERE course.name

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, tels que les champs utilisés pour les jointures.

Bases de données relationnelles et SQL

Pourquoi renonceriez-vous à un facteur de deux pour améliorer la vitesse d'exécution et 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'un ou l'autre importait beaucoup en 1980 par rapport aux performances et aux besoins en mémoire, mais à mesure que le matériel informatique s'améliorait et devenait moins cher, les gens ont cessé de se soucier de la vitesse d'exécution et de la mémoire et se sont davantage inquiété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 fait, 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? EF «Ted» Codd était un informaticien au laboratoire de recherche IBM 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 mis du temps à 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. Quand IBM a finalement lancé son projet System R, l'équipe de développement (Don Chamberlin et Ray Boyce) n'était pas sous Codd, et ils ont ignoré le document de langage relationnel Alpha 1971 de Codd pour concevoir leur propre langage, SEQUEL (Structured English Query Language). En 1979, avant même qu'IBM ne publie son produit, Larry Ellison a incorporé le langage dans sa base de données Oracle (en utilisant les publications SEQUEL de pré-lancement d'IBM 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 de clients. Il n'a pas été facile d'embaucher ou de former des concepteurs et programmeurs de bases de données CODASYL, alors SEQUEL (et SQL) avait l'air 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êtes SQL sur leurs bases de données CODASYL, au grand désarroi 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, conformément à 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 liées via 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 de données (DDL), ainsi qu'un sous-langage pour modifier les données, le langage de manipulation de données (DML). Ces deux éléments ont leurs racines dans les premières spécifications CODASYL. Le troisième sous-langage de SQL déclare les requêtes, via l' SELECTinstruction et les jointures relationnelles.

SQL  SELECTdéclaration

L' SELECTinstruction indique à l'optimiseur de requêtes les données à renvoyer, les tables dans lesquelles rechercher, les relations à suivre et l'ordre à imposer aux données renvoyées. L'optimiseur de requêtes doit déterminer par lui-même les index à utiliser pour éviter les analyses de table 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'index.

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 lourdes charges de lecture. Si vous avez trop d'index, toute la base de données peut ralentir sous de lourdes 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 également de la manière dont elle jouera dans les jointures lorsqu'elle apparaît en tant que 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 divisées en différents volumes en fonction de la valeur de la clé primaire, appelée partitionnement horizontal, vous devez également considérer comment la clé primaire affectera la partition. Conseil: vous souhaitez que la table soit répartie uniformément sur les volumes, ce qui suggère que vous ne souhaitez pas utiliser d'horodatages ou d'entiers consécutifs comme clés primaires.

Les discussions sur la SELECTdéclaration peuvent commencer simple, mais peuvent rapidement devenir déroutantes. Considérer:

SÉLECTIONNER * DES Clients;

Simple, non? Il demande tous les champs et toutes les lignes de la Customerstable. Supposons, cependant, que la Customerstable comporte une centaine de millions de lignes et une centaine de champs, et que l'un des champs est un grand champ de texte pour les commentaires. Combien de temps faudra-t-il pour extraire 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?

Vous devriez peut-être réduire le montant que vous envoyez sur le fil. Considérer:

SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Clients

O état ET ville

COMMANDER PAR lastSaleDate DESCENDING;

Vous allez maintenant extraire beaucoup moins de données. Vous avez demandé à la base de données de ne vous donner que quatre champs, de ne considérer que les entreprises de Cleveland et de ne vous donner que les 100 entreprises ayant les ventes les plus récentes. Cependant, pour le faire le plus efficacement possible sur le serveur de base de données, la Customerstable a besoin d'un index state+citypour la WHEREclause et d'un index lastSaleDatepour les clauses ORDER BYet TOP 100.

À propos, TOP 100est valide pour SQL Server et SQL Azure, mais pas pour MySQL ou Oracle. Dans MySQL, vous utiliseriez LIMIT 100après la WHEREclause. Dans Oracle, vous utiliseriez une borne dans ROWNUMle cadre de la WHEREclause, c'est-à-dire WHERE... AND ROWNUM <=100. Malheureusement, les normes SQL ANSI / ISO (et il y en a neuf à ce jour, s'étendant de 1986 à 2016) ne vont que si loin, au-delà duquel chaque base de données introduit ses propres clauses et fonctionnalités propriétaires.

Jointures SQL 

Jusqu'à présent, j'ai décrit la SELECTsyntaxe des tables simples. Avant de pouvoir expliquer les  JOINclauses, vous devez comprendre les clés étrangères et les relations entre les tables. Je vais expliquer cela en utilisant des exemples en DDL, en utilisant la syntaxe SQL Server.

La version courte de ceci est assez simple. Chaque table que vous souhaitez utiliser dans les relations doit avoir une contrainte de clé primaire; il peut s'agir d'un seul champ ou d'une combinaison de champs définis par une expression. Par exemple:

CRÉER TABLE Personnes (

    PersonID int NOT NULL PRIMARY KEY,

    Caractère PersonName (80),

    ...

Chaque table qui doit être associée Personsdoit avoir un champ qui correspond à la Personsclé primaire, et pour préserver l'intégrité relationnelle, ce champ doit avoir une contrainte de clé étrangère. Par exemple:

CRÉER DES Commandes TABLE (

    OrderID int NOT NULL PRIMARY KEY,

    ...

    PersonID int RÉFÉRENCES CLÉS ÉTRANGÈRES Personnes (PersonID)

);

Il existe des versions plus longues des deux instructions qui utilisent le CONSTRAINTmot - clé, 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 de champ ne peuvent pas être dupliquées). D'autres champs peuvent éventuellement être indexés. Il est souvent utile de créer des index pour les champs de clé étrangère et pour les champs qui apparaissent dans les clauses WHEREet ORDER BY, mais pas toujours, en raison de la surcharge potentielle des écritures et des 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 = Commandes.PersonID

WHERE NomPersonne;

En fait, il existe quatre types de JOIN: INNER, OUTER, LEFTet RIGHT. Le INNER JOINest la valeur par défaut (vous pouvez omettre le mot INNER), et c'est celui qui inclut uniquement les lignes contenant des valeurs correspondantes dans les deux tables. Si vous souhaitez répertorier des personnes, qu'elles aient ou non des commandes, vous utiliserez LEFT JOINpar exemple:

SELECT PersonName, OrderID FROM Persons

GAUCHE 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 des types de données, la syntaxe peut devenir un peu épineuse au début. Heureusement, il existe des outils de développement de base de données qui peuvent générer des requêtes SQL correctes pour vous, souvent en faisant glisser et en déposant des tables et des champs du diagramme de schéma dans un diagramme de requêtes.

Procédures stockées SQL

Parfois, la nature déclarative de l' SELECTinstruction ne vous mène pas là où vous voulez aller. La plupart des bases de données ont une fonction appelée procédures stockées; malheureusement, c'est un domaine où presque toutes les bases de données utilisent des extensions propriétaires aux normes SQL ANSI / ISO.

Dans SQL Server, le dialecte initial des procédures stockées (ou procs stockées) é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, telles que C #, Java et R. Une procédure stockée T-SQL simple peut être uniquement une version paramétrée d'une SELECTinstruction. Ses avantages sont la facilité d'utilisation et l'efficacité. Les procédures stockées sont optimisées lorsqu'elles sont enregistrées, pas à chaque fois qu'elles sont exécutées.

Une procédure stockée T-SQL plus compliquée peut utiliser plusieurs instructions SQL, des paramètres d'entrée et de sortie, des variables locales, des BEGIN...ENDblocs, des IF...THEN...ELSEconditions, des curseurs (traitement ligne par ligne d'un ensemble), des expressions, des tables temporaires et une multitude d'autres syntaxe procédurale. De toute évidence, si le langage des procédures stockées 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 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 bases de données CODASYL (bien que les curseurs se rapprochent), mais cela revient à l'idée que les instructions SQL devraient être normalisées et que les problèmes de performances devraient être laissés à l'optimiseur de requêtes de base de données. . En fin de compte, un doublement des performances est souvent trop à laisser sur la table.

Apprenez SQL

Les sites répertoriés ci-dessous peuvent vous aider à apprendre SQL ou à découvrir les bizarreries de divers dialectes SQL.