Archives de catégorie : SQL

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.

MySQL : l’opérateur ensembliste UNION (2/2)

Revenons sur l’opérateur ensembliste UNION et voyons comment il traite les duplicatas :

create table employe (
    id tinyint unsigned NOT NULL PRIMARY KEY,
    nom varchar(20) NOT NULL,
    prenom varchar(30) NOT NULL
);

create table manager(
    id smallint unsigned NOT NULL PRIMARY KEY,
    nom varchar(30),
    prenom varchar(20)
);

INSERT INTO employe VALUES (1, 'Klein', 'Roger'),
                           (2, 'Bagnole', 'Marcel'),
                           (3, 'De Narvale', 'Nadine');

INSERT INTO manager VALUES (3, 'Avignon', 'Eric'),
                           (2, 'Avril', 'Mathilda'),
                           (1, 'Klein', 'Roger');

Faisons l’union de ces deux relations :


select * from employe union select * from manager;

Roger Klein n’apparaît qu’une seule fois : UNION supprime bien les doublons !

Pour avoir l’intégralité de l’union des deux extensions de ces relations, il suffit de faire :


select * from employe union ALL select * from manager;

Et voilà que nous avons tout à présent !

snapshot2

 

Utiliser un UNION ALL vous permettra de faire apparaître l’intégralité des extensions des schémas unis.

MySQL : l’opérateur ensembliste UNION (1/2)

En algèbre relationnelle, UNION est un opérateur dit « ensembliste ». Par définition, il travaille sur au moins deux ensembles (l’extension de deux relations), qu’il agrège tout en éliminant les éventuels doublons. Créons deux relations matérialisant des employés et des managers au sein d’une entreprise pour voir comment tout cela fonctionne :


create table employe (
    id tinyint unsigned NOT NULL PRIMARY KEY,
    nom varchar(20) NOT NULL,
    prenom varchar(30) NOT NULL
);

create table manager(
    id smallint unsigned NOT NULL PRIMARY KEY,
    nom varchar(30),
    prenom varchar(20)
);

INSERT INTO employe VALUES (1, 'Klein', 'Roger'),
                           (2, 'Bagnole', 'Marcel'),
                           (3, 'De Narvale', 'Nadine');

INSERT INTO manager VALUES (1, 'Avignon', 'Eric'),
                           (2, 'Avril', 'Mathilda'),
                           (3, 'Klein', 'Roger');

Ces deux relations ont le même schéma, c’est à dire le même nombre d’attributs; c’est une des conditions de l’UNION. Lorsque l’on fait l’union de nos deux relations, par la requête :


select * from employe union select * from manager;

On obtient les tuples suivants :

snapshot2

Vous notez que Roger Klein est présent deux fois, car il est à la fois employé et manager; ce n’est pas un doublon au sens strict du terme car il a deux valeurs d’identifiant différentes dans chaque table. Il apparaît donc logiquement deux fois, ce qui n’aurait pas été le cas s’il avait eu la valeur d’id 3 dans employe ou de 1 dans manager.

La condition pour réaliser une UNION, je l’ai déjà dit plus haut, est d’avoir le même schéma dans les deux tables, c’est à dire le même nombre d’attributs; prenons deux tables qui n’ont aucun rapport entre elles :


create table chevre (

    id tinyint unsigned NOT NULL PRIMARY KEY,

    nom varchar(20) NOT NULL,

    age tinyint unsigned NOT NULL default 0

);

create table chou (

    variete varchar(15) NOT NULL PRIMARY KEY,

    poids tinyint unsigned NOT NULL default 0,

    couleur varchar(10) NOT NULL

);

insert into chevre values (1, 'Biquette', 10),
                          (2, 'Lola', 13);

insert into chou values ('romanesco', 2, 'vert'),
                        ('chinois', 1, 'blanc');

Faisons l’UNION de ces deux relations de même schéma :


select * from chevre UNION select * from chou;

Je ne comprends pas, on me répète depuis tout petit qu’il ne faut pas ménager la chèvre et le chou et pourtant, stupeur…

snapshot2

C’est tout à fait possible d’avoir les deux ! Vous avez vu que mes champs sont mélangés, il ne sont pas de même type (le premier champ de chevre est un entier non signé alors que le premier champ de chou est une chaîne de caractères, le deuxième est une chaîne de caractères dans l’un et un entier dans l’autre, etc.). Au final, le résultat de notre union ne veut pas dire grand chose sémantiquement parlant, regardez le nom des colonnes : nom contient le nom de nos sympathiques caprins mais aussi le poids de nos choux, age contient l’âge de nos ruminants mais aussi la couleur de nos choux.

Maman avait donc raison : on ne doit pas ménager la chèvre et le chou !

Pour en finir avec le display length sur les types de données numériques de MySQL

Contrairement à la croyance populaire, écrire :


create table eleve (

id_eleve tinyint(2) unsigned NOT NULL PRIMARY KEY

) 

ne fera pas en sorte que vous vous retrouviez avec des nombres inférieurs à 99. Ce 2 entre parenthèses n’est utile que lorsque vous utilisez l’option ZEROFILL (littéralement « remplir avec des zéros »). La preuve, insérez donc 255, qui est la limite haute d’un tinyint non signé :

insert into eleve values (255);

Si vous faites :

select * from eleve;

Vous verrez bien 255 s’afficher ! Si par contre vous utilisez l’option ZEROFILL, comme suit :

create table eleve (
id_eleve tinyint(2) unsigned ZEROFILL NOT NULL PRIMARY KEY
)

Alors en insérant 1 :

insert into eleve values (1);

Vous verrez, après un nouveau select, que ce chiffre a été complété (sur la gauche, évidemment…) par autant de zéros qu’il faut pour satisfaire votre longueur d’affichage (display length), qui est de 2 !