Archives de catégorie : SQL

Le prédicat SQL EXISTS

Le mot clé SQL EXISTS est ce que l’on appelle un prédicat, il évalue une (sous-)requête et dit si elle contient (true) ou non (false) des tuples. Pour travailler avec, nous allons utiliser deux tables au schéma simplissime, clients et commandes, dont voici les extensions, c’est à dire l’ensemble des tuples qu’elles contiennent.

Pour trouver les noms et prénoms des clients qui ont une commande:

SELECT nom, prenom FROM clients WHERE EXISTS (select * from commandes)

Voilà le résultat:

Heu…attendez voir, je fais quoi parmi ces résultats, je n’ai pas passé commande ! Que dit en réalité notre requête ? « Donne-moi le nom et prénom des clients tant qu’il EXISTE des commandes ». Il existe 5 commandes, j’ai donc 5 clients. Et oui, attention car à ce stade, nous n’avons absolument pas demandé de corrélation entre les résultats. Faisons-le et notez au passage que nous utilisons TRUE en lieu et place du joker « * » dans la sous-requête:

SELECT nom, prenom FROM clients c WHERE EXISTS (SELECT TRUE FROM commandes co WHERE c.id = co.client_id)

Voilà qui est nettement mieux, vous en conviendrez !

Alors, vous allez me dire, « Quel est l’intérêt d’utiliser EXISTS, nous sommes en train de faire ni plus ni moins qu’une jointure interne » et vous aurez raison. D’ailleurs, certaines personnes qui ont grand besoin d’une mise à jour en SQL s’en servent comme « jointure du pauvre ». La grosse différence, c’est que vous ne pouvez pas projeter des attributs potentiellement contenus dans la sous-requête, ce qui peut être fait avec un JOIN, interne ou externe. Ici nous ne pouvons projeter que nom et prenom, qui sont des attributs de la table clients. Pensez aussi à l’utilisation des index (utiliser EXPLAIN !)

To EXIST or not to EXIST?

Evidemment, EXISTS a aussi son contraire – comme NULL – c’est NOT EXISTS.
Reprenons la dernière requête (notez que TRUE est devenu 1)

SELECT nom, prenom FROM clients c WHERE NOT EXISTS (SELECT 1 FROM commandes co WHERE c.id = co.client_id)

Et hop ! Me voilà apparaissant dans les résultats !

Le prédicat EXISTS sur un UPDATE

Si je souhaite mettre le montant des commandes pour lesquelles il n’existe pas de client à 1, je ferai:

UPDATE commandes AS co SET montant = 1 WHERE NOT EXISTS (SELECT * FROM clients c WHERE c.id = co.client_id)

C’est un peu moins délicat à écrire qu’un UPDATE avec une jointure.

Le prédicat EXISTS sur un DELETE

Ce n’est guère plus difficile, notez seulement que DELETE n’accepte pas nativement l’alias (AS), il faut donc mettre commandes en toutes lettres.

DELETE FROM commandes WHERE NOT EXISTS (SELECT * FROM clients c WHERE c.id = commandes.client_id)

Autre exemple

Altérons notre relation commandes pour en faire celle qui suit:

Et rajoutons une relation produits elle aussi tout à fait basique:

Maintenant supposons que nous souhaitions trouver le nom des produits de toute commande contenant une écharpe et qui ne sont pas une écharpe justement:

SELECT p.nom FROM commandes AS co INNER JOIN produits p ON (p.id = co.produit_id) WHERE produit_id != 2 AND EXISTS (select * from commandes AS co2 WHERE co.client_id = co2.client_id AND co2.produit_id = 2)

Nous cherchons les commandes qui contiennent le produit 2 dans la sous-requête et nous projetons ceux dont l’id n’est pas 2.

Les formes normales

Laisser un commentaire

Relation, es-tu bien normale ?

Le but de la normalisation d’un schéma de base de données relationnelle ? Principalement l’élimination de la redondance et l’assurance d’avoir des données cohérentes (pas la même adresse orthographiée de N façons différentes, par exemple). Je dis « de la redondance inutile » car même si cela semble un pléonasme, il existe des cas où elle peut avoir une utilité et notamment quand la rapidité des accès aux données est placée au centre des préoccupations du concepteur.

Normalisez avant qu’il ne faille tout jeter !

Les formes normales, des règles à suivre pour rester en forme !

Les formes normales sont des règles à suivre pour parvenir à éliminer cette redondance inutile. Nous n’irons pas en détail dans les aspects formels de ces formes normales, parce qu’ils ne sont pas indispensables à la compréhension de celles-ci et parce qu’ils rebutent inutilement le débutant. Sim-pli-ci-té !

1NF

Que nous dit cette première forme normale (1st Normal Form) ? Elle nous dit que tout attribut d’une relation (toute colonne d’une table, si vous préférez) ne doit posséder qu’une valeur pour chacun de ses enregistrements (ou tuples).

Plus concrètement, voici une table enseignant qui viole allégrement 1NF :

La colonne prenom contient deux valeurs, cette colonne n’est donc pas mono-valuée comme nous l’impose 1NF mais multi-valuée, on dit que prenom ne contient pas de valeur atomique, que c’est un groupe de valeurs.

Comment se mettre en conformité avec 1NF ?

Dans notre cas ici, il ne faut pas faire l’économie d’une colonne dédiée au deuxième prénom (il pourrait même y en avoir un troisième, voire N car « le nombre de prénoms qui peuvent être attribués par les parents à un même enfant n’est pas fixé par la loi » nous dit le site Service Public). Créons donc cette nouvelle colonne et notons que la création de celle-ci nous permettra de faire des recherches de meilleure qualité sur les prénoms, par exemple en utilisant un index !

Plutôt simple, n’est-ce pas ? Que dire de cette relation :

Encore une fois, notre colonne adresse est multi-valuée ! Même si elle ne contient qu’une seule adresse, cette adresse elle-même contient plusieurs informations, de nature différente: un nom de rue, un numéro dans cette rue, un code postal, un nom de ville…Pourquoi ne pas créer une table adresse avec un champ numero, type_voie (rue, avenue, boulevard etc.), code_postal, ville etc. ? Cela peut servir si l’on veut normaliser nos adresses grâce à des outils tierce-partie et les rendre « propres ». Parfois il arrive que l’on laisse volontairement l’adresse dans cet état pour minimiser les jointures et donc accéder le plus rapidement possible à l’information…A vous d’étudier la pertinence de chacune de ces façons de faire : gardez simplement à l’esprit que l’absence de normalisation peut coûter cher plus tard…quand les données auront enflé !

2NF

Tout d’abord, pour être en 2NF, il faut être en 1NF : on ne peut pas être sans avoir été, lorsque l’on normalise ! Pour évoquer 2NF, il nous faut parler des dépendances fonctionnelles ! Qu’est-ce qu’une DF ? Rien de bien compliqué, regardons de plus près cette relation enseigne :

Nous avons ici une clé primaire composée du couple (identifiant_enseignant, identifiant_cours). Notre colonne ville nous dit dans quelle ville a lieu le cours.
Nous voyons que la colonne ville dépend de identifiant_cours : le cours 1 est toujours donné à Avignon et le 2, toujours à Aix.

On dit ici que identifiant_cours détermine ville ou bien, dans le sens inverse, que ville dépend fonctionnellement de identifiant_cours.

Si pour des valeurs identiques d’une colonne C on a les mêmes valeurs dans la colonne C2, alors C détermine C2 et nous le noterons C → C2.

Ici, pour la valeur 1 dans la colonne identifiant_cours nous avons toujours la valeur Avignon en face et pour la valeur 2 dans la colonne identifiant_cours nous avons toujours la valeur Aix. Ce n’est pas le cas de identifiant_enseignant, qui n’a pas les mêmes valeurs de ville en face (regardez l’enseignant 1, il enseigne dans la ville d’Aix et dans la ville d’Avignon).

Dans notre relation enseigne nous distinguons:

des attributs (des colonnes, c’est pareil) qui forment une clé primaire
un attribut non clé (ville)

Ici nous avons identifié une dépendance fonctionnelle entre une partie de la clé (la colonne identifiant_cours) et une colonne non clé (ville). Voilà pourquoi cette relation viole 2NF.

Pour qu’une relation soit conforme à 2NF, les attributs non clé de cette relation doivent dépendre fonctionnellement de toute la clé et non d’une partie seulement

Ici nous avions : identifiant_cours → ville. La colonne identifiant_cours est une partie (la moitié pour être parfaitement exact) de la clé. CQFD.

Comment se mettre en conformité avec 2NF ?

Nous allons isoler la DF identifiant_cours → ville qui nous pose problème dans sa propre table, que nous pourrions appeler cours par exemple :

Vous noterez que nous avons mis fin à la redondance inutile jusqu’alors présente dans la table (la ville potentiellement répétée N fois); cela nous aide aussi à assurer comme je vous l’ai dit en introduction la cohérence des données (que se serait-il passé avant si nous avions écrit Avignon « Avinion », « Avignom » et « Avignon » ? Nous aurions eu trois villes différentes !). Dorénavant, Avignon figure une fois et une seule…et le nom est écrit comme il faut.

Que va devenir notre ancienne relation ? Eh bien oui, nous l’avons quelque peu rabotée !

Nous avons sorti ville mais il nous faut bien garder le lien entre le cours et la ville, voilà pourquoi la source de la DF identifiant_cours → ville, c’est à dire identifiant_cours, RESTE dans la table d’origine. Grâce à identifiant_cours, qui existe maintenant dans les deux tables, nous pouvons effectuer des jointures et ainsi garantir qu’on a maintenu l’information initiale qui était « tel cours se donne dans telle ville ».

En pratique ici, identifiant_cours sera clé primaire dans la nouvelle table cours et une contrainte d’intégrité référentielle partira depuis identifiant_cours situé dans enseigne vers identifiant_cours situé dans cours.

3NF

Tout comme il nous faut être en 1NF pour être en 2NF, il nous faut être en 2NF pour être en 3NF : nous procédons de manière incrémentale !

Pour être en 3NF, il faut que toute colonne non clé ne dépende que de la clé de la relation

Revenons un instant à nos enseignants, dont la structure a quelque peu évolué :

Nous avons souhaité pour chaque enseignant dire à quel département il était rattaché et où ce dernier se situait. Dans le cas présent, localisation dépend de departement (noté departement → localisation). Notre attribut departement ne fait pas partie de la clé, pas plus que localisation, dont il dépend. Voilà pourquoi cette relation n’est pas conforme à 3NF !

Les problèmes posés par cet exemple sont les mêmes que pour la 2NF, à savoir :

la localisation du département est répétée dans chaque enregistrement (regardez les deux premiers) et elle peut aussi être orthographiée différemment suite à une erreur de saisie, compromettant ainsi la cohérence des données et donc leur qualité
si la localisation du département change, il faut mettre à jour tous les enseignants qui y travaillent
Si le département n’a plus d’enseignant, nous perdons l’information de sa localisation

Comment se mettre en conformité avec 3NF ?

Comme pour 2NF, il va nous falloir isoler la DF anormale departement → localisation dans sa propre table, comme ceci :

Là encore vous noterez que nous avons mis fin à la redondance qui jusque là existait dans notre table. Notre relation enseignant va « maigrir » elle aussi mais on doit également y faire exister un lien vers notre nouvelle relation, ce lien ce sera la colonne identifiant_departement :

Conclusion

Nous entretenons désormais des relations tout à fait normales !

En réalité, il existe d’autres formes normales après 3NF, mais quand on a normalisé en 3NF, on a déjà éliminé la grande partie de ces vilaines redondances que nous voulons éviter.

Pour retenir facilement 2NF et 3NF, les gens qui travaillent sur les bases de données connaissent par cœur cette phrase de feu William Kent :

« The key, the whole key, and nothing but the key »

qui reprend de façon assez humoristique le serment que font les témoins devant un tribunal : « Je jure de dire la vérité, toute la vérité, rien que la vérité » (des petits malins on rajouté « So help me Codd » – E.F Codd étant considéré comme le père des bases de données relationnelles – pour remplacer le « so help me God » (Dieu me vienne en aide) qui termine la phrase aux Etats-Unis).

En effet :

En 2NF, tout attribut (ou colonne) non clé doit dépendre de la clé toute entière (the whole key)
En 3NF, tout attribut (ou colonne) non clé ne doit dépendre QUE de la clé (nothing but the key)

Vous voilà maintenant armés pour partir à l’assaut de vos tables et les normaliser ! Bon courage !

MySQL – les clés étrangères et l’intégrité référentielle

Laisser un commentaire

L’heure est venue de nous pencher sur la notion de clé étrangère dans une table. Les clés étrangères servent à établir des contraintes dites d’ « intégrité référentielle ».

Qu’est-ce qu’une clé étrangère ?

C’est un champ (ou une colonne, ou un attribut, ou une propriété) d’une table. Jusque là, voilà qui est plutôt rassurant !

Qu’est-ce l’intégrité référentielle ?

Dans cette expression, nous avons la notion centrale de « référence ». Il est en effet question d’un champ qui fait référence à un autre. Qu’est-ce que cela signifie, dans les faits ? Et bien simplement que, si A est un champ qui référence B, alors A aura pour valeurs admissibles un sous-ensemble des valeurs de B.

Plus concrètement, si votre champ B, référencé par A, a pour valeurs entières 1,2 et 3, alors A ne pourra avoir lui aussi que ces valeurs et pas 4 ou 0 !

Les clés étrangères dans MySQL

En SQL, quelque soit le SGBDR cible, la notion de clé étrangère s’applique à l’aide du mot clé FOREIGN KEY. Comme vous vous apprêtez à modifier la structure de la table pour lui imposer cette contrainte, vous devrez utiliser ALTER TABLE.

Nous allons voir la syntaxe en détail dans l’exemple à venir.

Un exemple, tout de suite !

Soit une table qui recense les joueurs d’un sport quelconque (qui se joue à deux):

create table joueur (
id smallint unsigned not null primary key,
nom varchar(20),
prenom varchar(20));

La table joueur possède un champ qui est son identifiant primaire : c’est id ! Ce champ là possède un index parce que je l’ai explicitement déclaré comme clé primaire de ma table (primary key). Voilà pour ma table joueur, qui est somme toute très simple.

Nous allons maintenant créer une table qui va servir à opposer un joueur à un autre :

create table matches (
id smallint unsigned not null primary key,
id_joueur1 smallint unsigned not null,
id_joueur2 smallint unsigned not null,
duree tinyint unsigned not null);

Cette table là possède elle aussi un identifiant (une clé primaire): en plus de cet identifiant, elle possède deux champs qui vont correspondre à des identifiants de joueurs, donc à des valeurs du champ id présentes dans notre table joueur ! Dans l’état rudimentaire de cette table il est tout à fait possible de rentrer des joueurs, mais comment forcer l’utilisateur de nos données à ne rentrer que des identifiants de joueurs qui existent ? C’est là que la notion de clé étrangère entre en jeu !

Nous allons faire en sorte que les valeurs admissibles pour les champs id_joueur1 et id_joueur2 de la table matches soient uniquement celles présentes dans le champ id de la table joueur, en clair : interdit de faire s’affronter des joueurs qui n’existent pas !

Il va nous falloir modifier la structure de la table matches, il y a donc de l’ALTER dans l’air ! Notez qu’il va falloir créer un index sur le champ référençant (celui qui va servir à référencer l’autre, qui est le référencé) : cet index est INDISPENSABLE car il évite les full table scans et accélère donc la vérification des contraintes d’intégrité référentielle !

ALTER TABLE matches ADD INDEX (id_joueur1); 
ALTER TABLE matches ADD FOREIGN KEY (id_joueur1)
REFERENCES joueur (id);

La voilà enfin notre référence ! Nous disons en substance « crée moi un lien entre le champ id de joueur et le champ id_joueur1 de matches » ! Dorénavant, toute tentative d’insérer un identifiant de joueur qui n’existe pas se soldera par un échec ! Faisons le de suite :

mysql> insert into matches (id_joueur1) values (1);
ERROR 1452 (23000): Cannot add or update a child row:
a foreign key constraint fails (`test`.`matches`, 
CONSTRAINT `matches_ibfk_1` FOREIGN KEY (`id_joueur1`) 
REFERENCES `joueur` (`id`))

Il n’y a pas de joueur dans la table qui leur est réservée, donc toute tentative de rentrer un numéro de joueur dans id_joueur1 sera sanctionnée par ce message d’erreur !

Pour que la même contrainte pèse sur le champ id_joueur2, il faut effectuer exactement la même opération que précédemment, à avoir : la pose d’un index sur le champ ET la pose de la clé étrangère :

ALTER TABLE matches ADD INDEX (id_joueur2); 
ALTER TABLE matches ADD FOREIGN KEY (id_joueur)
REFERENCES joueur (id);

Voilà que maintenant pèse sur notre table matches deux contraintes d’intégrité référentielle qui font qu’il n’est possible de faire s’affronter que des joueurs répertoriés dans notre table joueur !

La fin d’une idée reçue

Je me suis aperçu que beaucoup de gens étaient encore persuadés qu’une contrainte de clé étrangère ne pouvait s’appliquer qu’entre un champ référençant et un champ référéncé obligatoirement clé primaire de sa table. C’est absolument faux et ce même si en pratique on s’en sert le plus souvent comme ça ! Pour qu’un champ puisse en référencer un autre, il suffit que tous les deux soient indexés et rien de plus. Comme la clé primaire est par définition un index, c’est elle qui rend cette référence possible mais autrement, vous pouvez tout à fait dans T1(A,B) faire que B référence D de T2(C,D) qui n’est pas le moins du monde la clé primaire de T2 ! (les C.P sont en gras)

A retenir

Pour poser une clé étrangère sur A qui référence B, il faut que A et B soient rigoureusement du même type ! Attention quand vous utilisez des types numériques, il faut que les signes (SIGNED/UNSIGNED) soient identiques !
Seul le moteur InnoDB autorise l’utilisation de ce mécanisme !
On remplit d’abord les tables référencées avant de remplir les référençantes !
On vide les tables référençantes avant de vider les référencées !
Si vous souhaitez contourner momentanément la vérification des contraintes d’intégrité, pour charger des données en masse par exemple, utilisez SET FOREIGN_KEY_CHECKS = 0;

MySQL – update et delete avec des jointures

Laisser un commentaire

Le but de ce billet est de réaliser des opérations de mise à jour ou de suppression à l’aide d’une jointure, externe ou interne.
Voici le schéma qui nous sert de base de travail :

une table client
une table produit
une table commande, avec deux contraintes de clé étrangère partant vers chacune des tables

Le SQL du schéma

create table client(
id tinyint unsigned primary key auto_increment,
nom varchar(30) not null,
prenom varchar(20) not null);

insert into client (nom, prenom) values 
('Ferrandez', 'Sébastien'), ('Jambon', 'Paul');

create table produit(
id tinyint unsigned primary key auto_increment,
designation varchar(30) not null,
prix float (6,2) unsigned not null);

insert into produit (designation, prix) values 
('Mousse à raser', 7.99), ('Stylo BIC', 4.99);

create table commande (
id tinyint unsigned primary key auto_increment, 
id_client tinyint unsigned , 
id_produit tinyint unsigned, 
quantite tinyint unsigned default 1,
date timestamp);

alter table commande add constraint foreign key (id_client) 
references client(id);

alter table commande add constraint foreign key (id_produit)
references produit(id);

insert into commande(id_client,id_produit, quantite )
values (1,1,2), (1,2,1), (2,2,1), (2,1,2);

Les jointures, externes et internes

Le principe de la jointure est trivial, on crée comme une sorte de « pont » entre au moins 2 tables selon une condition qu’on va très simplement appeler « condition de jointure ». Une condition de jointure n’est pas forcément une clause d’égalité !

Prenons l’exemple de deux tables sans rapport quelconque :

create table eleve (nom varchar(20), age tinyint unsigned);
insert into eleve values ('Ferrandez', 10), ('Lucas', 30);
create table produit (nom varchar(20), prix float(6,2) unsigned);
insert into produit values ('iPhone5', 499), ('Jambon', 10);

Qu’est-ce qui nous empêche de faire une jointure entre un élève et un produit basé sur l’égalité de valeur de l’âge de l’élève et du prix du produit ?

SELECT * 
FROM  eleve 
INNER JOIN produit ON ( eleve.age = produit.prix )

Nous obtenons

+-----------+------+--------+-------+
| nom       | age  | nom    | prix  |
+-----------+------+--------+-------+
| Ferrandez |   10 | Jambon | 10.00 |
+-----------+------+--------+-------+
1 row in set (0.00 sec)

Une jointure ne se fait pas uniquement sur la base d’une égalité de valeur, on peut utiliser d’autres opérateurs arithmétiques :

SELECT * 
FROM  eleve
INNER JOIN produit ON ( eleve.age <= produit.prix )

Celle-ci remontera les tuples suivants :

+-----------+------+---------+--------+
| nom       | age  | nom     | prix   |
+-----------+------+---------+--------+
| Ferrandez |   10 | iPhone5 | 499.00 |
| Lucas     |   30 | iPhone5 | 499.00 |
| Ferrandez |   10 | Jambon  |  10.00 |
+-----------+------+---------+--------+
3 rows in set (0.00 sec)

Et pire, qu’est-ce qui nous empêche de faire une jointure sur des champs qui n’ont pas le même type de données (horreur !) :

SELECT * 
FROM  eleve
INNER JOIN produit ON ( eleve.age <= produit.nom )

Si on a le droit de faire ça, c’est que l’optimiseur MySQL effectue des conversions de type pour effectuer des comparaisons (différence, égalité etc.). Mais recentrons-nous sur le sujet du billet ! Une requête de jointure externe, contrairement aux internes, retourne à la fois les correspondances (c’est à dire, les enregistrements satisfaisant la clause de jointure) et les non-correspondances (celles qui ne la satisfont pas). Voyons ce que donne :

SELECT * 
FROM  eleve 
LEFT JOIN produit ON ( eleve.age = produit.prix )

+-----------+------+--------+-------+
| nom       | age  | nom    | prix  |
+-----------+------+--------+-------+
| Ferrandez |   10 | Jambon | 10.00 |
| Lucas     |   30 | NULL   |  NULL |
+-----------+------+--------+-------+
2 rows in set (0.00 sec)

Les non-correspondances sont signalées par la présence du marqueur NULL. Si vous choisissez de les remonter, rien de plus facile :

SELECT eleve.*
FROM  eleve 
LEFT JOIN produit ON ( eleve.age = produit.prix ) 
WHERE produit.prix IS NULL

Notez bien que le même test, mais sur produit.nom IS NULL aurait également fonctionné !
Bref, nous savons maintenant ce qu’est une jointure interne (que ce qui correspond) et une jointure externe (ce qui correspond + ce qui ne correspond pas).

La mise à jour

Nous allons mettre à jour les commandes du client Sébastien Ferrandez en disant qu’elles ont eu lieu le 24 Décembre 2013 à 23:59:59 :

UPDATE commande INNER JOIN client
ON (commande.id_client = client.id)
SET commande.date = '2013-12-24 23:59:59'
WHERE client. nom = 'ferrandez'
AND client.prenom = 'sebastien'

Notez que nous sommes en collation latin1_general_ci et que nous ne tenons donc compte ni des accents ni des majuscules…
Si d’aventure vous souhaitez modifier d’autres champs (y compris dans la table client, pas que commande !), séparez les dans SET avec des virgules !

Vous pouvez très bien utiliser la jointure externe pour mettre a jour des enregistrements ne se trouvant pas dans la table commande (nous aurions par exemple dans la table client un champ booléen A_DEJA_COMMANDE que nous mettrions à 0 si aucune commande n’était trouvée pour celui-ci).

La suppression

Insérons un nouveau tuple dans client :

insert into client (nom, prenom) values ('Diego', 'Miguel');

Et effectuons sa suppression immédiate en disant « J’efface les clients qui n’ont pas passé de commande »

DELETE client.* FROM client
LEFT OUTER JOIN commande 
ON ( client.id = commande.id_client )
WHERE commande.id_client IS NULL

N’oubliez pas que vous n’êtes absolument pas limités à UNE jointure et que vous pouvez très bien alterner jointures externes et internes dans la même requête ! Entraînez-vous sur des petits schémas tout simples du même genre que celui que nous avons vu dans ce billet et tout ça n’aura bientôt plus de secret pour vous !

MySQL MAX : la/les ligne(s) contenant la plus grande valeur d’un champ

3 réponses

Si vous lisez régulièrement les billets de ce blog (ce que j’espère secrètement), vous savez que j’essaie dans la mesure du possible de parler de choses qui servent au quotidien à la fois pour les développeurs Web utilisant PHP mais aussi pour les personnes qui travaillent avec des bases de données (relationnelles ou pas). C’est ainsi qu’aujourd’hui nous allons nous focaliser sur un type de requête relativement souvent utilisé et qui a pour but de faire « remonter » la ligne (ou les lignes) contenant la plus grande valeur d’un champ ! C’est parti ! Amusons-nous avec nos joyeux élèves !

Voilà un élève joyeux (j’aimerais qu’ils soient tous ainsi) !

Notre jeu de données

Soit la table eleves :

mysql> select * from eleves;
+----+-----------+------------+------+
| id | nom       | prenom     | note |
+----+-----------+------------+------+
|  1 | Ferrandez | Sébastien  |   10 |
|  2 | Ferrandez | Christophe |   12 |
|  3 | Gérard    | Olivier    |    9 |
|  4 | Gérard    | olivier    |   15 |
|  5 | Ferrandez | Christophe |   17 |
|  6 | Ferrandez | Sébastien  |    8 |
|  7 | Gérard    | Jean       |    4 |
|  8 | Django    | Marina     |   17 |
+----+-----------+------------+------+

Le but

Je souhaite connaitre la ou les personne(s) qui ont obtenu la note la plus haute.

Les façons de faire

A l’instinct…

Je sais qu’il est question de MAX quelque part, je serais tenté de faire :

SELECT max(note), nom, prenom FROM eleves;

Mais là, deux problèmes se posent…

1 : je n’ai qu’un enregistrement alors que deux élèves ont la meilleure note
2 : en face de cette meilleure note, je n’ai pas le bon couple nom/prénom

La jointure externe

Ici, nous allons nous baser sur la présence d’un marqueur NULL pour remonter les enregistrements qui nous intéressent. Nous disons en substance « Je veux le nom et le prénom des élèves pour lesquels il n’existe pas de note qui soit plus grande ».

SELECT s1.note, s1.nom, s1.prenom
FROM eleves s1
LEFT JOIN eleves s2 ON (s1.note < s2.note)
WHERE s2.id IS NULL

Cette solution là ramène bien les bons résultats :

mysql> SELECT s1.note, s1.nom, s1.prenom
    -> FROM eleves s1
    -> LEFT JOIN eleves s2 ON (s1.note < s2.note)
    -> WHERE s2.id IS NULL;
+------+-----------+------------+
| note | nom       | prenom     |
+------+-----------+------------+
|   17 | Ferrandez | Christophe |
|   17 | Django    | Marina     |
+------+-----------+------------+
2 rows in set (0.00 sec)

Le temps d’exécution est de l’ordre de la milliseconde (inférieur à une dizaine de millisecondes), nous n’avons pas indexé quelque champ que ce soit donc tout semble correct. Mais votre jeu de données grossissant, vous allez vite vous rendre compte que cette solution n’est pas viable ! Sur une table de 100000 enregistrements, une telle requête (avec le champ note indexé !) prend plus de 17 minutes à s’exécuter…Nous serions bons pour le peloton d’exécution avec un tel temps d’exécution !

La sous-requête

Il nous reste la solution de la sous-requête, qui va s’avérer dans notre cas la plus performante :

SELECT  note, nom, prenom
FROM   eleves
WHERE  note=(SELECT MAX(note)
              FROM eleves)

Sur notre set de 100000 enregistrements, avec le champ note indexé, elle met 0.04 secondes à s’exécuter, nous sommes loin des 17 minutes de la jointure externe, qui semblait bien fonctionner sur un petit nombre d’enregistrements. Un EXPLAIN nous montre que

+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra                        |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | eleves | ref  | note          | note | 1       | const |    2 | Using where                  |
|  2 | SUBQUERY    | NULL   | NULL | NULL          | NULL | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
2 rows in set (0.00 sec)

« Select tables optimized away » signifie que notre sous-requête ne contient qu’une fonction agrégative (sans groupement) et que l’optimiseur MySQL a déterminé qu’il ne renverra qu’une seule et unique valeur.

En conclusion, nous voyons que la sous-requête est la plus adaptée à notre cas ici. Pourquoi ne pas tenter un petit GROUP_CONCAT ordonné, également ? La requête reste performante et les résultats sont présentés de manière « agréable »…

mysql> SELECT note, GROUP_CONCAT( nom,  ' ', prenom
    -> ORDER BY nom ) AS  "Eleves"
    -> FROM eleves
    -> WHERE note = ( 
    -> SELECT MAX( note ) 
    -> FROM eleves );
+------+------------------------------------+
| note | Eleves                             |
+------+------------------------------------+
|   17 | Django Marina,Ferrandez Christophe |
+------+------------------------------------+

N’écoutez pas les oiseaux de malheur qui vitupèrent constamment les sous-requêtes, souvent ils n’en n’ont jamais fait une seule de leur vie et propagent des racontars qu’ils lisent ça et là sur des forums généralistes où prolifèrent leurs congénères aux avis biaisés ou erronés…Dans certains cas, ces sous-requêtes se révéleront bien plus efficaces que des jointures internes ou externes hasardeuses…Votre seule jauge quand vous écrivez une requête c’est EXPLAIN et pas le collègue ignare qui propage des légendes urbaines informatiques !

MySQL et InnoDB : activer les fichiers au niveau table avec innodb_file_per_table

5 réponses

Fichiers de base d’InnoDB

Par défaut, votre configuration pour les tables utilisant le moteur InnoDB avec MySQL est la suivante :

un fichier ibdata1 constamment alimenté est créé
deux fichiers de log (ib_logfile0 et ib_logfile1) sont crées

Vous pouvez les voir en faisant :

sebastien.ferrandez@sebastien$ ls -l /var/lib/mysql
total 28736
-rw-rw---- 1 mysql mysql 18874368 mai   21 10:17 ibdata1
-rw-rw---- 1 mysql mysql  5242880 mai   21 10:17 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 mai   20 15:58 ib_logfile1

Evidemment, il faudra au besoin modifier le chemin si vous avez customisé votre configuration MySQL. Le problème de ce fichier ibdata1 en mode append est qu’il a tendance à grossir très vite et à atteindre des tailles problématiques (de nature à remettre en cause le bon fonctionnement de MySQL) : sur ma machine locale, le mien faisait 3.4G ! Il m’a fallu dumper mes bases de données à des fins de sauvegarde, faire en sorte de ne plus avoir ce fichier monolithique mais plusieurs (par base de données et par table), redémarrer MySQL et rejouer mon script SQL pour remettre en place les données dont j’avais besoin rapidement.

Pour effectuer ce découpage propre par table, nous allons nous servir de la directive de configuration innodb_file_per_table. Comme je le disais plus haut, celle-ci n’est pas activée par défaut :

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+

Pour l’activer, il vous suffit d’en faire mention dans votre fichier my.cnf (situé par défaut sur mon installation dans /etc/mysql/my.cnf), dans la section réservée à mysqld (par exemple du côté du commentaire « Fine-tuning ») et évidemment il vous faudra redémarrer le démon mysqld pour prendre en compte ce changement.

Voici l’option de configuration à rajouter :

[mysqld]
#
# * Fine Tuning
#
innodb_file_per_table   = 1

Vous pouvez également écrire tout simplement « innodb_file_per_table », ceci fonctionnera. Redémarrez ensuite le service MySQL :

sudo service mysql restart

Connectez-vous en ligne de commande à votre MySQL et retapez la commande donnée ci-dessus, vous devriez avoir du nouveau :

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

Bien ! Notre modification est donc bien prise en compte ! Si je crée une nouvelle base de données et une nouvelle table :

mysql> create database lolcats; use lolcats;
Query OK, 1 row affected (0.00 sec)

Database changed
mysql> create table cat(id tinyint unsigned primary key, name varchar(20), age tinyint unsigned);
Query OK, 0 rows affected (0.04 sec)

Je retrouve bien tout ça dans mon répertoire MySQL :

sebastien.ferrandez@sebastien$ sudo ls -l /var/lib/mysql/lolcats/
total 116
-rw-rw---- 1 mysql mysql  8614 mai   21 10:44 cat.frm
-rw-rw---- 1 mysql mysql 98304 mai   21 10:44 cat.ibd
-rw-rw---- 1 mysql mysql    65 mai   21 10:43 db.opt

C’est bien le signe que le découpage « un fichier par table » est désormais en place !

Bénéficier du découpage avec une base de données déjà implantée

Si vous avez déjà une base de données et que vous vous apercevez qu’ibdata1 a grossi et qu’il est temps de passer à un fichier par table, procédez comme suit :

Créez un répertoire s’apprêtant à recueillir votre sauvegarde :

sebastien.ferrandez@sebastien:$ mkdir -p $HOME/mysql/backups/avant_filepertable

Faites une sauvegarde de l’intégralité de vos données :

sebastien.ferrandez@sebastien:$ mysqldump -u root -p --all-databases > $HOME/mysql/backups/avant_filepertable/all_databases.sql

Arrêtez le démon MySQL :

sebastien.ferrandez@sebastien:~$ sudo service mysql stop
[ ok ] Stopping MySQL database server: mysqld.

Naturellement, rajoutez votre option dans my.cnf :

[mysqld]
#
# * Fine tuning
#
innodb_file_per_table

Redémarrez MySQL :

sebastien.ferrandez@sebastien:~$ sudo service mysql start
[ ok ] Starting MySQL database server: mysqld ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

A l’issue de ce redémarrage, pensez à valider la bonne prise en compte de cette option à l’aide du SHOW VARIABLES montré un peu plus haut dans ce billet.
Supprimez vos fichiers (à vous de voir si vous souhaitez conserver les logs) :

sebastien.ferrandez@sebastien:$ sudo rm -fr /var/lib/mysql/*

Installez de nouvelles tables système. Attention, les messages que j’ai mis en gras nécessitent votre attention !

sebastien.ferrandez@sebastien:$ sudo /usr/bin/mysql_install_db
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h sebastien password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

Je vous conseille de passer par :

sudo /usr/bin/mysql_secure_installation

Voici les options que j’ai personnellement choisi :

Change the root password? [Y/n] n
 ... skipping.

Remove anonymous users? [Y/n] n
 ... skipping.
Disallow root login remotely? [Y/n]
 ... Success!

Remove test database and access to it? [Y/n]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reload privilege tables now? [Y/n]

Ré-injectez vos données sauvegardées dans votre base qui maintenant sait découper par table :

sebastien.ferrandez@sebastien:~$ mysql -u root -p < $HOME/mysql/backups/avant_filepertable/all_databases.sql

Et validez que les tables que vous avez recrée se trouvent bien dans les répertoires correspondant à vos bases de données :

sebastien.ferrandez@sebastien:~$ sudo ls -l /var/lib/mysql/lolcats
total 116
-rw-rw---- 1 mysql mysql    65 mai   22 09:31 db.opt
-rw-rw---- 1 mysql mysql  8556 mai   22 10:02 cat.frm
-rw-rw---- 1 mysql mysql 98304 mai   22 10:02 cat.ibd

La table cat que j’ai crée se retrouve bien dans le répertoire correspondant à la base de données lolcats dont elle fait partie, l’opération est un succès !

Kit de premiers secours

Problèmes pouvant survenir au shutdown du démon MySQL

Si jamais vous n’arrivez pas à stopper MySQL, il s’agit sans doute d’un problème lié à l’utilisateur ‘debian-sys-maint’ du à la suppression des tables.
Voici comment le résoudre :

Récupérez d’abord le mot de passe courant de cet utilisateur (c’est une installation locale, je vous donne le mot de passe sans crainte) :

sebastien.ferrandez@sebastien:$ sudo grep password /etc/mysql/debian.cnf
password = JZe6ZMa9bMK4aqfm

Ensuite mettez-lui les bons privilèges dans votre ligne de commande MySQL :

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'JZe6ZMa9bMK4aqfm' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Changer le mot de passe root

Pour mettre p@$sW0rd! comme mot de passe à l’utilisateur root

mysqladmin -u root password p@$sW0rd!

Pour aller plus loin…

http://dev.mysql.com/doc/refman/5.0/fr/innodb-configuration.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html (anglais)

Doctrine : les migrations avec le bundle DoctrineMigrationsBundle (3/3)

Laisser un commentaire

Voici la dernière partie de mon billet concernant les migrations Doctrine et le bundle associé.

Les relations des entités Doctrine

Nos élèves ont une relation avec leur filière. Nous en avons parlé au tout début lorsque nous définissions les cardinalités :

un élève a une seule filière
une filière a 0 ou plusieurs élèves

Il va nous falloir définir ceci au niveau de nos entités Doctrine. Nous allons donc « tirer un fil » entre nos entités Eleve et Filiere. Doctrine a des moyens de mettre ça en place et nous allons voir comment ceci se matérialise avec les annotations, que nous avons choisi de privilégier lorsque nous avons généré nos entités.

Entre Eleve et Filiere, il y a une relation de un à plusieurs (one to many en anglais). Nous la voulons bi-directionnelle dans le sens où nous souhaitons pouvoir à partir d’un élève récupérer sa filière et, à partir d’une filière, récupérer ses élèves. Dans l’entité Eleve, nous sommes du côté plusieurs de la relation (car plusieurs élèves correspondent à une filière). Voici comment nous allons nous servir des annotations Doctrine :

   /**
     * @ORM\ManyToOne(targetEntity="Filiere", inversedBy="eleves")
     * @ORM\JoinColumn(name="filiere_id", referencedColumnName="id", nullable=false)
     **/
    private $filiere;

Nous définissons une variable d’instance privée filiere qui va aider Doctrine à stocker la filière d’un élève (il n’y en a qu’une possible, souvenez-vous). La cible de la relation que nous sommes en train de décrire dans l’entité Eleve est l’entité Filiere, c’est ce que nous dit targetEntity. Le inversedBy qui suit signifie que de l’autre côté de la relation (dans l’entité Filiere) nous trouverons l’attribut chargé de gérer cette relation sous le nom d’eleves (notez bien le pluriel car de l’autre côté de la relation nous aurons plusieurs élèves pour notre filière). JoinColumn décrit la manière physique dont cette relation va exister. En lisant, vous devinez que dans notre table nous allons avoir un champ rajouté suite à l’écriture de cette relation qui va identifier une filière pour un élève, que ce champ s’appellera filiere_id et qu’il référencera une colonne appelée id…Vous voyez déjà venir gros comme une maison la contrainte d’intégrité référentielle. Le nullable=false signifie qu’il faut obligatoirement une filière à un élève ; là aussi vous devez imaginer que cette clause entraînera la présence d’un NOT NULL dans le schéma relationnel.

Du côté de l’entité Filiere, nous avons l’annotation Doctrine suivante pour la variable d’instance eleves dont nous avons déjà parlé :

     /**
     * @ORM\OneToMany(targetEntity="Eleve", mappedBy="filiere")
     **/
    private $eleves;

    public function __construct()
    {
        $this->eleves = new ArrayCollection;
    }

Nous prenons la relation dans l’autre sens – « un vers plusieurs » – parce qu’une filière a plusieurs élèves. Voilà pourquoi cette fois c’est one to many en lieu et place du many to one qu’on trouvait dans Eleve. La cible, c’est bien Eleve et l’attribut qui va servir c’est bien filiere (private $filiere). Dans Eleve nous avions inversedBy, dans Filiere nous avons mappedBy. Doctrine détermine le côté « possesseur » de la relation par inversedBy et l’autre côté (« inverseur ») par mappedBy.

Comme nous avons potentiellement plusieurs élèves pour une filière, nous allons avoir à gérer plusieurs objets. Pour ce faire, Doctrine nous propose son type ArrayCollection, que vous allez pouvoir utiliser en faisant :

use Doctrine\Common\Collections\ArrayCollection;

Notre variable d’instance privée eleves sera de ce type et nous l’initialiserons dans le constructeur de notre classe.

Nous avons construit notre première relation au niveau objet ! L’heure est venue de mettre notre schéma de bases de données à jour avec tout cela ! Allons-y !

app/console doctrine:migrations:diff

Ouvrons le fichier ainsi généré pour vérifier que notre relation est bien en place :

$this->addSql("ALTER TABLE eleve ADD filiere_id INT NOT NULL");
$this->addSql("ALTER TABLE eleve ADD CONSTRAINT FK_ECA105F7180AA129 FOREIGN KEY (filiere_id) REFERENCES filiere (id)");
$this->addSql("CREATE INDEX IDX_ECA105F7180AA129 ON eleve (filiere_id)");

Ce que nous envisagions est effectivement arrivé, nous avons bel et bien une contrainte d’intégrité référentielle posée entre la table eleve et la table filiere !

Lançons maintenant la migration :

app/console doctrine:migrations:migrate

Notre table eleve a bien un champ en plus : filiere_id !

sebastien.ferrandez@sebastien:~/migrations$ mysql -u root -p -e 'use exercice; desc eleve'
Enter password: 
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| nom        | varchar(40) | NO   |     | NULL    |                |
| prenom     | varchar(40) | NO   |     | NULL    |                |
| age        | int(11)     | NO   |     | NULL    |                |
| filiere_id | int(11)     | NO   | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

Occupons-nous ensuite des cours: un cours a des relations avec salle, enseignant et filière, en fait cours est au centre de tout…Quelles sont-elles exactement ? Reprenons ce que nous avions établi :

une filière est composée d’un ou plusieurs cours
un cours n’a qu’un seul et unique enseignant mais un enseignant a un ou plusieurs cours
un cours se tient dans une seule salle à un moment donné et une salle peut abriter plusieurs cours. On peut très bien créer un cours sans salle car il arrive qu’on ne puisse décider de suite dans quelle salle il se tiendra. Elle sera rajoutée plus tard.

Les relations nous apparaissent évidentes. Un lien de type « un à plusieurs » existe entre cours et enseignant (« un à plusieurs » dans le sens enseignant → cours et « plusieurs à un » dans le sens inverse). Il en va de même pour les salles ou les filières. La seule différence réside en le fait qu’un cours a forcément un enseignant ou une filière alors qu’il n’a pas forcément de salle (on peut le créer sans salle pour lui en affecter une quand on a consulté le planning des salles et qu’on en a trouvé une de disponible). Nous allons donc nous retrouver à ajouter des annotations de type ManyToOne dans notre entité Cours.

    /**
     * @ORM\ManyToOne(targetEntity="Filiere", inversedBy="cours")
     * @ORM\JoinColumn(name="filiere_id", referencedColumnName="id", nullable=false)
     **/
    private $filiere;

    /**
     * @ORM\ManyToOne(targetEntity="Salle", inversedBy="cours")
     * @ORM\JoinColumn(name="salle_id", referencedColumnName="id")
     **/
    private $salle;

    /**
     * @ORM\ManyToOne(targetEntity="Enseignant", inversedBy="cours")
     * @ORM\JoinColumn(name="enseignant_id", referencedColumnName="id", nullable=false)
     **/
    private $enseignant;

Vous noterez que salle est le seul endroit où nullable est à sa valeur par défaut, c’est à dire true et qu’il sera donc possible d’enregistrer un cours sans salle. Dans la mesure où nous avons pour un cours soit 0 (pour une salle) soit un (enseignant, filière) inutile ici d’utiliser les ArrayCollection comme c’était le cas pour matérialiser la relation filière/élève.
Dans chacune de ces entités cibles nous auront une variable d’instance nommée cours, c’est aussi ce que nous disent ces annotations.

Dans l’entité Salle :

    /**
     * @ORM\OneToMany(targetEntity="Cours", mappedBy="salle")
     **/
    private $cours;

    public function __construct()
    {
        $this->cours = new ArrayCollection;
    }

Ici par contre une salle est susceptible d’accueillir plusieurs cours (NFA057 le jeudi soir, NFA053 le mardi), la notion de collection a ici du sens. Idem pour les enseignants :

    /**
     * @ORM\OneToMany(targetEntity="Cours", mappedBy="enseignant")
     **/
    private $cours;

    public function __construct()
    {
        $this->cours = new ArrayCollection;
    }

Ou encore Filiere :

    /**
     * @ORM\OneToMany(targetEntity="Cours", mappedBy="filiere")
     **/
    private $cours;

Vous avez remarqué que le champ mappedBy de l’annotation pointe vers la variable d’instance correspondante dans l’entité Cours. Dans le cas de Filiere, l’entité avait déjà une instanciation de la classe ArrayCollection dans son constructeur, nous devons en rajouter une autre pour les cours, comme fait dans les autres entités, plus haut, ce qui donnera :

public function __construct()
    {
        $this->cours = new ArrayCollection;
        $this->eleves = new ArrayCollection;
    }

Une fois que nous avons mis nos relations dans nos 4 entités, il nous reste à exécuter une migration !

app/console doctrine:migrations:diff
app/console doctrine:migrations:migrate

feront apparaître les contraintes d’intégrité référentielle entre la table cours et enseignant, salle et filiere.

Maintenant que nous avons établi notre architecture objet avec nos entités, regardons avec MySQL Workbench à quoi ressemble notre schéma et surtout s’il satisfait à toutes les contraintes que nous avons énoncées au début du présent document :

En naviguant à travers les relations, nous sommes sûrs que nous avons effectué du bon travail car elles correspondent en tous points à ce que nous souhaitions.

Au sujet des types de données utilisés par Doctrine, nous voyons qu’ils ne correspondent pas forcément à ce que nous recherchons en terme de plage de valeurs (des INTEGER pour gérer des âges – pas signés qui plus est -, des DATETIME alors que des TIMESTAMP, deux fois moins gourmands en espace disque, suffiraient). Il y a beaucoup à redire de ce côté là sur ce schéma mais sachez qu’il est toujours possible de forcer Doctrine à utiliser des types choisis par nos soins avant de passer une migration ! Il suffit d’en faire état dans les annotations de nos entités. Attention cependant, changer le type des données choisies par Doctrine peut faire que les changements d’état de votre schéma (de la colonne modifiée, pour être plus exact) ne sont pas pris en compte comme ils devraient l’être par Doctrine, qui ne retrouve plus ses types « natifs », puisque vous les avez modifiés.

Doctrine : les migrations avec le bundle DoctrineMigrationsBundle (1/3)

Laisser un commentaire

Nous allons voir comment se servir du bundle de migrations fourni par Doctrine avec Symfony et Composer. Placez vous dans le répertoire qui servira de base à cet exercice. Pour ma part j’ai choisi la localisation suivante :

mkdir $HOME/migrations && cd $HOME/migrations

Nous allons tout d’abord installer Composer dans ce répertoire. Bien évidemment, il vous faudra disposer de curl. Si vous ne l’avez pas installé, faites-le de suite :

sudo apt-get install curl

Ensuite installez Composer :

curl -sS https://getcomposer.org/installer | php
#!/usr/bin/env php
All settings correct for using Composer
Downloading...

Composer successfully installed to: /home/sebastien.ferrandez/migrations/composer.phar
Use it: php composer.phar

Maintenant que nous avons Composer installé, nous allons faire une installation de Symfony via cet outil :

php composer.phar create-project symfony/framework-standard-edition Symfony/ 2.2.1

Des bundles sont déployés sur votre machine (citons Doctrine, Swiftmailer, Monolog, Assetic) et vous les trouverez en faisant :

ls -l Symfony/vendor/

Vous vous en doutez, tous ne nous seront pas utiles. Pour nettoyer votre install et garder ce que vous voulez, n’hésitez pas à mettre à jour votre fichier composer.json.
Prenons quelques secondes pour regarder ce composer.json généré par notre create-project :

cat Symfony/composer.json

Attardons nous sur la partie « require » de notre objet JSON :

    "require": {
        "php": ">=5.3.3",
        "symfony/symfony": "2.2.*",
        "doctrine/orm": "~2.2,>=2.2.3",
        "doctrine/doctrine-bundle": "1.2.*",
        "twig/extensions": "1.0.*",
        "symfony/assetic-bundle": "2.1.*",
        "symfony/swiftmailer-bundle": "2.2.*",
        "symfony/monolog-bundle": "2.2.*",
        "sensio/distribution-bundle": "2.2.*",
        "sensio/framework-extra-bundle": "2.2.*",
        "sensio/generator-bundle": "2.2.*",
        "jms/security-extra-bundle": "1.4.*",
        "jms/di-extra-bundle": "1.3.*"
    }

Vous y retrouvez tous les bundles dont vous avez vu défiler le nom à l’installation, c’est plutôt rassurant !

L’heure est maintenant venue d’installer le bundle de migrations Doctrine. Vous êtes toujours à la racine de votre projet et vous exécutez :

php composer.phar require doctrine/doctrine-migrations-bundle dev-master -d Symfony
composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
  - Installing doctrine/migrations (v1.0-ALPHA1)
    Downloading: 100%         

  - Installing doctrine/doctrine-migrations-bundle (dev-master 99c0192)
    Cloning 99c0192804134a8c1d0588777bd98bdbc2dbb554

Vous refaites un :

cat Symfony/composer.json

Pour vérifier que votre fichier composer.json a été mis à jour. Normalement, la partie require devrait maintenant ressembler à ça :

    "require": {
        "php": ">=5.3.3",
        "symfony/symfony": "2.2.*",
        "doctrine/orm": "~2.2,>=2.2.3",
        "doctrine/doctrine-bundle": "1.2.*",
        "twig/extensions": "1.0.*",
        "symfony/assetic-bundle": "2.1.*",
        "symfony/swiftmailer-bundle": "2.2.*",
        "symfony/monolog-bundle": "2.2.*",
        "sensio/distribution-bundle": "2.2.*",
        "sensio/framework-extra-bundle": "2.2.*",
        "sensio/generator-bundle": "2.2.*",
        "jms/security-extra-bundle": "1.4.*",
        "jms/di-extra-bundle": "1.3.*",
        "doctrine/doctrine-migrations-bundle": "dev-master"
    },

Si c’est le cas, alors vous avez installé le bundle avec succès ! Il nous faut cependant faire quelques « réglages »; commençons par ajouter notre bundle fraîchement installé dans le fichier AppKernel.php

vi Symfony/app/AppKernel.php

Nous y rajoutons la ligne suivante (vous pouvez laisser la dernière virgule car il s’agit d’une dangling comma) :

new Doctrine\Bundle\MigrationsBundle\DoctrineMigrationsBundle(),

Ensuite il nous faut évidemment renseigner nos paramètres d’accès à la base de données afin que Doctrine puisse s’y connecter et donc faire :

vi Symfony/app/config/parameters.yml

Vous renseignerez dans ce fichier les paramètres de votre base de données (le driver utilisé – dans le doute laissez PDO -, l’IP de la machine cible, votre nom d’utilisateur et enfin le mot de passe).Dans la partie database, mettez le nom de votre base de données: assurez-vous bien entendu qu’elle existe ! La mienne s’appelle « exercice », je la crée sans plus attendre en ligne de commande (mon serveur MySQL est sur ma machine locale, je ne précise donc pas -h localhost) :

mysql -u root -p -e 'create database exercice'

Pour m’assurer que tout est en place avant de m’amuser avec les migrations, je lance une dernière commande :

sebastien.ferrandez@sebastien:~/migrations$ Symfony/app/console doctrine:migrations:status

 == Configuration

    >> Name:                        Application Migrations
    >> Database Driver:             pdo_mysql
    >> Database Name:               exercice
    >> Configuration Source:        manually configured
    >> Version Table Name:          migration_versions
    >> Migrations Namespace:        Application\Migrations
    >> Migrations Directory:        /home/sebastien.ferrandez/migrations/Symfony/app/DoctrineMigrations
    >> Current Version:             0
    >> Latest Version:              0
    >> Executed Migrations:         0
    >> Executed Unavailable Migrations: 0
    >> Available Migrations:            0
    >> New Migrations:                  0

Tout semble fonctionner ! Comme je suis d’un naturel curieux et que je ne crois que ce que je vois, je regarde si des choses se sont passées dans ma base de données :

sebastien.ferrandez@sebastien:~/migrations$ mysql -u root -p -e 'use exercice; show tables'
Enter password: 
+--------------------+
| Tables_in_exercice |
+--------------------+
| migration_versions |
+--------------------+

Et la réponse est OUI ! Une nouvelle table a été créée pour gérer les versions de mes migrations ! Tout est maintenant réuni pour s’adonner sans retenue aux joies de la migration avec Doctrine !
Rendez-vous donc bientôt dans la partie 2 pour y voir du concret !!!

MySQL : différence entre = et LIKE dans un SELECT

1 réponse

LIKE…a bo$$ !

On entend souvent dire qu’utiliser l’opérateur arithmétique = ou faire un LIKE sur une chaîne de caractères dans MySQL revient au même. Oui et non…Oui car au final les tuples qui « remontent » de nos tables sont les mêmes mais non car en coulisses l’impact sur le temps d’exécution n’est pas forcément le même.
Voyons sans plus tarder un exemple avec une table composée de 2 champs; un champ char(6) qui occupe donc 6 octets puisque les chaînes générées le sont avec des caractères ASCII et un champ varchar(6) qui va occuper 7 octets – un préfixe d’un octet + nos 6 octets de caractères – dans laquelle nous insérons des chaînes de caractères aléatoires et en nombre assez conséquent (100 000 entrées). Ne vous inquiétez pas si cela prend quelques secondes à s’exécuter…

CREATE TABLE test_like (
    cha char(6) NOT NULL,
    var varchar(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- le début de notre proc' stock'

DROP PROCEDURE IF EXISTS insertion;

DELIMITER //
CREATE PROCEDURE insertion()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE (i<=100000) DO
        INSERT INTO test_like VALUES
        (
        SUBSTRING(MD5(RAND()) FROM 1 FOR 6),
        SUBSTRING(MD5(RAND()) FROM 1 FOR 6));
        SET i=i+1;
    END WHILE;
END
//
CALL insertion();

C’est vrai que de prime abord faire

 select * from test_like
where cha like 'ee43b9'

 select * from test_like
where cha = 'ee43b9'

sur notre champ de type CHAR revient au même, tout comme :

select * from test_like
where var like 'fb7bb8'

produit le même résultat que

select * from test_like
where var = 'fb7bb8'

J’ai lu sur certains forums anglophones que le comportement de = ou like dépendait du type de données de la colonne (CHAR/VARCHAR), c’est peut-être vrai sur certains RDBMS, mais pas avec MySQL, vous en avez la preuve concrète. Le changement est surtout visible lorsque l’on pose un index sur la colonne concernée :

ALTER TABLE test_like ADD INDEX (cha)

Ainsi, voici ce que nous donne un EXPLAIN. Tout d’abord sur le LIKE :

explain select * from test_like where cha like 'ee43b9'

+—-+————-+———–+——-+—————+——+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——-+—————+——+———+——+——+————-+
| 1 | SIMPLE | test_like | range | cha | cha | 18 | NULL | 1 | Using where |
+—-+————-+———–+——-+—————+——+———+——+——+————-+
1 row in set (0.01 sec)

Et ensuite sur le = :

explain select * from test_like where cha = 'ee43b9'

+—-+————-+———–+——+—————+——+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——+—————+——+———+——-+——+————-+
| 1 | SIMPLE | test_like | ref | cha | cha | 18 | const | 1 | Using where |
+—-+————-+———–+——+—————+——+———+——-+——+————-+
1 row in set (0.01 sec)

Nous obtenons la même chose sur le champ de type VARCHAR après avoir posé un index dessus. Une recherche avec un LIKE sur un champ indexé donne un type = range alors qu’une recherche sur un champ indexé donne avec une égalité stricte un type = ref. Voyons ce que dit la documentation officielle à ce sujet :

ref peut être utilisé pour les colonnes indexées, qui sont comparées avec l’opérateur =.

C’est bien le cas ! Pour le range :

range peut être utilisé lorsqu’une colonne indexée est comparée avec une constante comme =, <>, >, >=, <, <=,IS NULL, <=>, BETWEEN ou IN.

Les exemples de la documentation officielle montrent bien que range peut-être AUSSI utilisé avec un = mais plutôt sur des types de données numériques…

Les tests réalisés sur un serveur MySQL situé sur le réseau local

Pour le champ de type CHAR

CHAR(6) indexé, LIKE sur une valeur existante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE cha LIKE 'edd34e'

1 total, Traitement en 0.0103 sec.

CHAR(6) indexé, LIKE sur une valeur inexistante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE cha LIKE 'fb7bb8'

MySQL n'a retourné aucune ligne. ( Traitement en 0.0007 sec. )

CHAR(6) indexé, = sur une valeur existante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE cha =  'edd34e'

Traitement en 0.0008 sec.

CHAR(6) indexé, = sur une valeur inexistante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE cha =  'udd34e'

MySQL n'a retourné aucune ligne. ( Traitement en 0.0006 sec. )

Pour le champ de type VARCHAR

VARCHAR(6) indexé, LIKE sur une valeur existante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE var LIKE  '64eaee'

1 total, Traitement en 0.0455 sec.

VARCHAR(6) indexé, LIKE sur une valeur inexistante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE var LIKE  '88eaee'

MySQL n'a retourné aucune ligne. ( Traitement en 0.0659 sec. )

VARCHAR(6) indexé, = sur une valeur existante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE var =  '6753ec'

1 total, Traitement en 0.0520 sec.

VARCHAR(6) indexé, = sur une valeur inexistante, bypass du cache de requêtes MySQL :

SELECT SQL_NO_CACHE *
     FROM test_like
     WHERE var =  '8888ec'

MySQL n'a retourné aucune ligne. ( Traitement en 0.0513 sec. )

D’après ce petit benchmark très rapide et sans grande prétention :

sur un VARCHAR, like est un peu plus rapide que =
sur un CHAR intégralement rempli, = est beaucoup plus rapide que like

MySQL : comparaison rapide des types de données CHAR et VARCHAR

Laisser un commentaire

Quand on choisit d’utiliser des champs de type chaîne de caractères dans une table MySQL (mais pas que…), on en vient rapidement à se poser la question suivante :

CHAR ou VARCHAR ?

Voici le tableau comparatif que nous donne la documentation MySQL. Il suppose que nous utilisions un jeu de caractères codés sur 1 octet comme latin1 par exemple (de son vrai nom ISO 8859-1) :

CHAR va de 0 à 255 caractères. La longueur d’un champ de type CHAR est fixée à la valeur déclarée lors de la définition du champ : si vous créez un champ de type CHAR(30) et que vous souhaitez y insérer une chaîne de 31 caractères, cette valeur sera stockée sous une forme tronquée. Si la valeur insérée est inférieure à 30, le « reste » (les caractères manquants pour arriver à 30) sera comblé avec des espaces. Lorsque la valeur sera récupérée, les espaces seront enlevés automatiquement, vous n’y verrez que du feu !

Les VARCHAR sont eux utilisés pour des chaînes de longueur variable et donc appropriés pour des données dont on ne peut prédire la longueur de façon certaine. VARCHAR peut stocker jusqu’à 65535 caractères (bien plus que les 255 qu’une grande partie des gens ont en tête). Leur taille étant variable, elle doit être stockée quelque part…Ainsi, pour tout type VARCHAR, MySQL réserve un préfixe d’un octet si la taille des données est inférieure ou égale à 255 (un octet = 8 bits et 2⁸ = 256) et deux octets dans le cas contraire (2 octets = 16 bits, 2¹⁶ = 65536).

Que lit-on sur ce tableau ? Lorsque l’on stocke une chaîne de caractères vide dans un champ en CHAR(4), quatre caractères « espace » sont réservés et donc 4 octets alloués ; avec un VARCHAR(4), on n’alloue que l’octet nécessaire au préfixe des chaînes de moins de 255 caractères. Si l’on stocke deux caractères, en CHAR(4), deux espaces sont alloués « pour rien » alors qu’en VARCHAR(4) on a toujours l’octet nécessité par le préfixe et les deux octets de chaque caractère. Jusqu’ici, VARCHAR est moins gourmand en espace disque. La tendance s’inverse lorsque l’on remplit CHAR avec le nombre exact de caractères attendus : on économise un octet par rapport à un VARCHAR ! Lorsque la chaîne dépasse la longueur maximale prévue, elle est tronquée dans les deux cas, mais c’est toujours CHAR qui est plus économique !

Conclusion, quand on sait qu’une chaîne de caractères aura une longueur définie, mieux vaut privilégier CHAR (si cette longueur est évidemment inférieure à 255, mais ce sera dans 99,99% des cas, n’est-ce pas ?).