Archives du mot-clé 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.

clients

comm

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:
requete1

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 !

requete2

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 !
requete3

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:

requete4

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

requete5

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.

MongoDB : les bases pour bien débuter (3/3)

Revenons sur la collection MongoDB qui nous sert d’exemple depuis le début et effectuons quelques opérations de mise à jour élémentaires. Voici donc pour rappel à quoi ressemble notre collection :

sebastien.ferrandez@sebastien:~$ mongo
MongoDB shell version: 2.0.6
connecting to: test
> show dbs;
admin   0.203125GB
gens    0.203125GB
local   (empty)
> use gens;
switched to db gens
> db.gens.find();
{ "_id" : ObjectId("517a850cb1d6ce34f91af2d1"), "nom" : "ferrandez" }
{ "_id" : ObjectId("517a851eb1d6ce34f91af2d2"), "nom" : "ferrandez", "prenom" : "léo" }
{ "_id" : ObjectId("517a856bb1d6ce34f91af2d4"), "nom" : "ferrandez", "prenom" : "maïa", "age" : 6 }

mongo-db-logo

Opérations de mise à jour

Avec inc

Supposons qu’il nous faille mettre à jour l’âge d’une personne : aujourd’hui c’est l’anniversaire de Maïa, elle a 7 ans ! Voici plusieurs façons de faire : tout d’abord nous incrémentons la clé age de nos documents pour lesquels age est supérieur à 5.

db.gens.update({ age:{$gt: 5}}, {$inc: {age: 1}});

Avec set

Nous ciblons uniquement le prénom

db.gens.update({ prenom: "maïa"}, {$set: {age: 7}});

Nous faisons un mélange des deux précédentes requêtes :

db.gens.update({ age:{$gt: 5}}, {$set: {age: 7}});

Opérations de suppression

Suppression par ObjectId

> db.gens.find();
{ "_id" : ObjectId("517a850cb1d6ce34f91af2d1"), "nom" : "ferrandez" }
{ "_id" : ObjectId("517a851eb1d6ce34f91af2d2"), "nom" : "ferrandez", "prenom" : "léo" }
{ "_id" : ObjectId("517e33cf4937f8d068f9e9aa"), "nom" : "ferrandez", "prenom" : "maïa", "age" : 7 }
> db.gens.remove( {"_id": ObjectId("517e33cf4937f8d068f9e9aa")});
> db.gens.find();
{ "_id" : ObjectId("517a850cb1d6ce34f91af2d1"), "nom" : "ferrandez" }
{ "_id" : ObjectId("517a851eb1d6ce34f91af2d2"), "nom" : "ferrandez", "prenom" : "léo" }

Suppression par champ quelconque

Supprimons tous les gens qui s’appellent « ferrandez » :

> db.gens.find();
{ "_id" : ObjectId("517a850cb1d6ce34f91af2d1"), "nom" : "ferrandez" }
{ "_id" : ObjectId("517a851eb1d6ce34f91af2d2"), "nom" : "ferrandez", "prenom" : "léo" }
> db.gens.remove({nom: 'ferrandez'});
> db.gens.find();

Notez que cette fois-ci je n’ai pas mis de guillemets autour de la clé (nom) et j’ai mis des apostrophes autour du nom pour que vous voyez bien qu’il n’est pas obligatoire de mettre tout ça entre guillemets systématiquement !

Suppression de la première occurrence seulement

En mettant justOne à 1 (le premier paramètre), seul le premier document satisfaisant aux critères sera supprimé :

> db.gens.find();
{ "_id" : ObjectId("517e36c14937f8d068f9e9ab"), "nom" : "ferrandez", "prenom" : "maïa", "age" : 7 }
{ "_id" : ObjectId("517e36e54937f8d068f9e9ac"), "nom" : "ferrandez", "prenom" : "sébastien" }
> db.gens.remove({nom: 'ferrandez'}, 1);
> db.gens.find();
{ "_id" : ObjectId("517e36e54937f8d068f9e9ac"), "nom" : "ferrandez", "prenom" : "sébastien" }

Suppression de l’intégralité des documents d’une collection

> db.gens.remove();

Voilà ! Pour ceux d’entre vous qui ont déjà des connaissances en langage SQL, vous avez les bases pour débuter avec MongoDB. Nous allons bientôt rentrer en détail dans le fonctionnement de MongoDB et en particulier nous attarder sur l’aspect dénormalisation !

MongoDB : les bases pour bien débuter (1/3)

Le but de ce billet est d’effectuer une première incursion dans l’univers MongoDB en voyant les commandes de bases dans le shell mongo et en faisant un parallèle avec ce qui existe dans le monde du SQL. Nous allons commencer en manipulant les bases : bases de données, collections et documents. Le but n’est pas de se noyer d’emblée dans l’architecture interne de Mongo (le sharding, les replica sets, etc.) mais d’attaquer d’emblée des choses concrètes !

mongo_logo

L’emblème de MongoDB

Installation de MongoDB sur GNU Linux Debian
Nous allons faire les choses proprement au lieu d’insérer à la sauvage dans le fichier sources.list. Vous devez évidemment être sudoer pour effectuer ceci :

echo 'deb http://downloads-distro.mongodb.org/repo.fr.ian-sysvinit dist 10gen' > /tmp/mongodb.list
sudo cp /tmp/mongodb.list /etc/apt/sources.list.d/
rm /tmp/mongodb.list

sudo apt-get update
sudo apt-get install mongodb-10gen

A l’issue de l’installation, le serveur MongoDB doit être démarré automatiquement (le daemon mongod pour être plus précis).

Les principales commandes

Lancer le shell

Tout part de là ! En tapant « mongo » vous devriez y accéder car /bin doit être dans votre PATH. Si vous rencontrez des problèmes, lancez

/usr/bin/mongo

Quitter le shell

Rien de plus simple: faites donc Ctrl+C (ce bon vieux SIGINT !) ou tapez exit à l’invite, vous sortirez du shell.

Obtenir le numéro de version

Les données concernant votre installation apparaissent sous la forme d’un objet JSON, la commande a exécuter est en gras :

> db.runCommand({buildinfo: 1});
{
        "version" : "2.0.6",
        "gitVersion" : "nogitversion",
        "sysInfo" : "Linux z6 3.8-trunk-amd64 #1 SMP Debian 3.8.3-1~experimental.1 x86_64 BOOST_LIB_VERSION=1_49",
        "versionArray" : [
                2,
                0,
                6,
                0
        ],
        "bits" : 64,
        "debug" : false,
        "maxBsonObjectSize" : 16777216,
        "ok" : 1
}

Lister les bases de données

La commande permettant de faire ça est show dbs.

sebastien.ferrandez@sebastien:~$ mongo
MongoDB shell version: 2.0.6
connecting to: test
> show dbs;
local   (empty)

Comme je viens d’installer MongoDB, rien d’étonnant à ce qu’aucune base de données n’apparaisse ! Il est possible d’obtenir davantage d’informations en utilisant la base de données admin dont l’usage est réservé comme son nom l’indique à l’admin.

> use admin
switched to db admin
> db.runCommand({listDatabases: 1});
{
        "databases" : [
                {
                        "name" : "local",
                        "sizeOnDisk" : 1,
                        "empty" : true
                }
        ],
        "totalSize" : 0,
        "ok" : 1
}

Créer une collection

La collection en langage MongoDB est l’équivalent de la table en relationnel, on les crée soit en y insérant le tout premier document (l’équivalent du tuple en relationnel) soit en exécutant la commande suivante :

> db.createCollection('gens');
{ "ok" : 1 }

Insérer un document dans une collection

Notre collection s’appelle gens, insérons-y un premier document :

db.gens.insert( { nom: "ferrandez", prenom:"sebastien", age: 35 } );

Lister les documents contenus dans une collection

Pour faire l’équivalent d’un SELECT * FROM gens, j’utilise la commande find sans paramètres :

> db.gens.find();
{ "_id" : ObjectId("517941f3b12e1948c04f6d5e"), "nom" : "ferrandez", "prenom" : "sebastien", "age" : 35 }

Insérons un nouveau document et observons le changement dans le find :

> db.gens.insert( { nom: "ferrandez", prenom:"sandrine", age: 34, sexe:"F" } );
> db.gens.find();
{ "_id" : ObjectId("517941f3b12e1948c04f6d5e"), "nom" : "ferrandez", "prenom" : "sebastien", "age" : 35 }
{ "_id" : ObjectId("51794334b12e1948c04f6d5f"), "nom" : "ferrandez", "prenom" : "sandrine", "age" : 34, "sexe" : "F" }

Notez que, contrairement à une table, le nombre de champs n’est pas contraint par l’intention du schéma, en effet mon deuxième document a un champ de plus que le premier : sexe.
Dans les objets JSON que j’enregistre, un champ _id est déterminé par MongoDB. Il est évidemment tout à fait possible de forcer le sien :

> db.gens.insert( { _id: 10, nom: "ferrandez", prenom:"christophe", age: 40 } );
> db.gens.find();
{ "_id" : ObjectId("517941f3b12e1948c04f6d5e"), "nom" : "ferrandez", "prenom" : "sebastien", "age" : 35 }
{ "_id" : ObjectId("51794334b12e1948c04f6d5f"), "nom" : "ferrandez", "prenom" : "sandrine", "age" : 34, "sexe" : "F" }
{ "_id" : 10, "nom" : "ferrandez", "prenom" : "christophe", "age" : 40 }

Il est possible de manipuler des identifiants auto-incrémentés mais si vous insistez pour utiliser ce mécanisme offert par certains SGBDR comme MySQL c’est peut-être que vous avez une vision trop « relationnelle » de vos données ! Si toutefois vous persistiez à vouloir bénéficier de ce mécanisme, vous trouverez le lien à la fin de ce billet.

Supprimer une collection

Il vous faut utiliser drop, comme en SQL !

> db.gens.drop();
true

Supprimer une base de données

Deux manières de procéder, en faisant un use pour se connecter à la base de données à effacer :

> show dbs;
admin   0.203125GB
gens    0.203125GB
local   (empty)
> use gens;
switched to db gens
> db.runCommand({dropDatabase: 1});
{ "dropped" : "gens", "ok" : 1 }
> show dbs;
admin   0.203125GB
local   (empty)

ou bien en exécutant directement :

> db.gens.runCommand({dropDatabase: 1});
{ "dropped" : "gens", "ok" : 1 }

Pour aller plus loin…

Au sujet des identifiants auto-incrémentés : La documentation MongoDB (anglais)

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

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) :

snapshot2

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 28 = 256) et deux octets dans le cas contraire (2 octets = 16 bits, 216 = 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 ?).

MySQL : poser un index qui…pénalise le temps d’exécution !

Avant de poser un index sur un (ou plusieurs) champs d’une table, il faut évaluer de la manière la plus précise possible l’impact qu’aura cette pose sur le temps d’exécution des requêtes. Lorsque les développeurs découvrent les index (j’en connais qui sont en poste depuis des années et ne savent même pas ce que c’est…), ils ont tendance à en mettre de partout. Gare ! Un index posé à tort peut pénaliser les sélections, les mises à jour/suppressions/insertions. Regardons un exemple très simple; Henri a décidé que puisque les index sont là pour accélérer les recherches, il va en poser un sur sa table, dont voici le schéma (l’intention) :


create table if not exists test_index
(
   id mediumint unsigned not null primary key,
   nom char(4) not null,
   valid tinyint unsigned not null
);

Sa table comporte 100 000 tuples, qu’il insère avec une procédure stockée :

DROP PROCEDURE IF EXISTS insertion;

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

    WHILE (i<=100000) DO
        INSERT INTO test_index VALUES(i,'test', 1);
        SET i=i+1;
    END WHILE;
END
//
CALL insertion();

Il part du principe (plutôt intelligent, au demeurant) que la requête qu’il fait le plus souvent étant :


select * from test_index where valid = 1;

Il serait judicieux qu’il pose un index sur valid :


alter table test_index add index(valid);

Voilà la pose réalisée ! Une fois l’index en place, il a quand même le réflexe de valider que la pose de cet index accélère bien les recherches, comme il le souhaitait…

Il a bien ses 100 000 enregistrements…


mysql> select count(*) from test_index;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+

Avant la pose de l’index, un EXPLAIN sur sa requête favorite donnait :

snapshot2

On voit au passage qu’il réalisait un full table scan (type = ALL, possible_keys = NULL), c’est à dire qu’il parcourait l’ensemble des tuples de sa table (rows). Dorénavant, cela donne :

snapshot2

Henri constate avec satisfaction que la pose de son index lui fait lire moitié moins d’enregistrements (regardez la colonne rows). Pourtant, lorsqu’il lance sa requête, Henri est très déçu :


100000 rows in set (0.19 sec)

Alors que lorsqu’il détruit l’index, il obtient :


100000 rows in set (0.10 sec)

Pour résumer, Henri lit moitié moins de tuples…en deux fois plus de temps ! Ce n’est pas du tout conforme à l’idée qu’il se faisait des index et ses espoirs sont déçus ! En posant un index inutile sur un champ qui n’a qu’une valeur possible (1), Henri a posé un index qui pénalise le temps d’exécution de sa requête…Il aura tout de même compris que le seul vrai moyen de vérifier qu’on tire des bénéfices d’un index est le benchmarking des requêtes qui l’utilisent.