Informations sur la base de données et les requêtes

• Gestion des utilisateurs
• Sommaire
• Configuration et options

Dans ce chapitre, vous verrez comment aller chercher des informations sur les différents objets de vos bases de données (les tables, les procédures, etc.) de deux manières différentes :

• grâce à la commande SHOW ;
• en allant chercher ces informations directement dans la base de données information_schema.

Ensuite, vous découvrirez la commande EXPLAIN, qui donne des indications sur le déroulement des requêtes SELECT, ce qui permet d'optimiser celles-ci.

• Commandes de description
• La base de données information_schema
• Déroulement d'une requête de sélection

Commandes de description

Les commandes SHOW et DESCRIBE ont été utilisées ponctuellement tout au long du cours pour afficher diverses informations sur les tables et les colonnes. Nous allons ici voir plus en détail comment utiliser ces commandes, et ce qu'elles peuvent nous apprendre.

Description d'objets

1

SHOW objets;

Cette commande permet d'afficher une liste des objets, ainsi que certaines caractéristiques de ces objets.

Exemple : liste des tables et des vues.

1

SHOW TABLES;

Pour pouvoir utiliser SHOW TABLES, il faut avoir sélectionné une base de données.

Objets listables avec SHOW

Les tables et les vues ne sont pas les seuls objets que l'on peut lister avec la commande SHOW. Pour une liste exhaustive, je vous renvoie à la documentation officielle, mais voici quelques-uns de ces objets.

Clauses additionnelles

Certaines commandes SHOW objets acceptent des clauses supplémentaires : LIKE et WHERE.

• La clause LIKE permet de restreindre la liste aux objets dont le nom correspond au motif donné.
• WHERE permet d'ajouter diverses conditions.

Exemple 1 : sélection des colonnes d'Adoption dont le nom commence par "date".

1
2
3

SHOW COLUMNS 
FROM Adoption
LIKE 'date%';

Exemple 2 : sélection des encodages contenant "arab" dans leur description.

1
2

SHOW CHARACTER SET
WHERE Description LIKE '%arab%';

DESCRIBE

La commande DESCRIBE nom_table, qui affiche les colonnes d'une table ainsi que certaines de leurs caractéristiques, est en fait un raccourci pour SHOW COLUMNS FROM nom_table.

Requête de création d'un objet

La commande SHOW peut également montrer la requête ayant servi à créer un objet.

SHOW CREATE type_objet nom_objet;

Exemple 1 : requête de création de la table Espece.

1

SHOW CREATE TABLE Espece \G

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

*************************** 1. row ***************************
       Table: Espece
Create Table: CREATE TABLE `Espece` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `nom_courant` varchar(40) NOT NULL,
  `nom_latin` varchar(40) NOT NULL,
  `description` text,
  `prix` decimal(7,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom_latin` (`nom_latin`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

Exemple 2 : requête de création du trigger before_insert_adoption.

1

SHOW CREATE TRIGGER before_insert_adoption \G

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

*************************** 1. row ***************************
               Trigger: before_insert_adoption
              sql_mode: 
SQL Original Statement: CREATE DEFINER=`sdz`@`localhost` TRIGGER before_insert_adoption BEFORE INSERT
ON Adoption FOR EACH ROW
BEGIN
    IF NEW.paye != TRUE                                     
    AND NEW.paye != FALSE     
      THEN
        INSERT INTO Erreur (erreur) VALUES ('Erreur : paye doit valoir TRUE (1) ou FALSE (0).');

    ELSEIF NEW.date_adoption < NEW.date_reservation THEN    
        INSERT INTO Erreur (erreur) VALUES ('Erreur : date_adoption doit être >= à date_reservation.');
    END IF;
END
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8_general_ci

On peut ainsi afficher la syntaxe de création d'une table, d'une base de données, d'une procédure, d'un trigger ou d'une vue.

La base de données information_schema

Comme son nom l'indique, la base de données information_schema contient des informations sur les schémas. En MySQL, un schéma est une base de données. Ce sont des synonymes. La base information_schema contient donc des informations sur les bases de données.

Voyons ce qu'on trouve comme tables dans cette base de données.

1

SHOW TABLES FROM information_schema;

Cette base contient donc des informations sur les tables, les colonnes, les contraintes, les vues, etc., des bases de données stockées sur le serveur MySQL. En fait, c'est de cette base de données que sont extraites les informations affichées grâce à la commande SHOW.

Par conséquent, si les informations données par SHOW ne suffisent pas, il est possible d'interroger directement cette base de données.

Prenons par exemple la table VIEWS de cette base de données. Quelles informations contient-elle ?

1

SHOW COLUMNS FROM VIEWS FROM information_schema;

La colonne TABLE_NAME contient le nom de la vue. Interrogeons donc cette table, afin d'avoir des informations sur la vue V_Animal_details.

1
2
3
4
5

USE information_schema; -- On sélectionne la base de données

SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, IS_UPDATABLE, DEFINER, SECURITY_TYPE
FROM VIEWS
WHERE TABLE_NAME = 'V_Animal_details' \G

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

*************************** 1. row ***************************
   TABLE_SCHEMA: elevage
     TABLE_NAME: V_Animal_details
VIEW_DEFINITION: select `elevage`.`animal`.`id` AS `id`,`elevage`.`animal`.`sexe` AS `sexe`,`elevage`.`animal`.`date_naissance` AS `date_naissance`,
                        `elevage`.`animal`.`nom` AS `nom`,`elevage`.`animal`.`commentaires` AS `commentaires`,
                        `elevage`.`animal`.`espece_id` AS `espece_id`,`elevage`.`animal`.`race_id` AS `race_id`,
                        `elevage`.`animal`.`mere_id` AS `mere_id`,`elevage`.`animal`.`pere_id` AS `pere_id`,
                        `elevage`.`animal`.`disponible` AS `disponible`,`elevage`.`espece`.`nom_courant` AS `espece_nom`,
                        `elevage`.`race`.`nom` AS `race_nom` 
                 from ((`elevage`.`animal` 
                 join `elevage`.`espece` on((`elevage`.`animal`.`espece_id` = `elevage`.`espece`.`id`))) 
                 left join `elevage`.`race` on((`elevage`.`animal`.`race_id` = `elevage`.`race`.`id`)))
   IS_UPDATABLE: YES
        DEFINER: sdz@localhost
  SECURITY_TYPE: DEFINER

Voyons encore deux exemples d'exploitation des données d'information_schema.

Exemple 1 : données sur les contraintes de la table Animal.

1
2
3

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE
FROM TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'elevage' AND TABLE_NAME = 'Animal';

Les contraintes NOT NULL ne sont pas reprises dans cette table (on peut les trouver dans la table COLUMNS).

Exemple 2 : données sur la procédure maj_vm_revenus().

1
2
3

SELECT ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_TYPE, ROUTINE_DEFINITION, DEFINER, SECURITY_TYPE 
FROM ROUTINES 
WHERE ROUTINE_NAME = 'maj_vm_revenus' \G

Les routines comprennent les procédures stockées et les fonctions stockées (qui ne sont pas couvertes par ce cours).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

*************************** 1. row ***************************
      ROUTINE_NAME: maj_vm_revenus
    ROUTINE_SCHEMA: elevage
      ROUTINE_TYPE: PROCEDURE
ROUTINE_DEFINITION: BEGIN
    TRUNCATE VM_Revenus_annee_espece;

    INSERT INTO VM_Revenus_annee_espece
    SELECT YEAR(date_reservation) AS annee, Espece.id AS espece_id, SUM(Adoption.prix) AS somme, COUNT(Adoption.animal_id) AS nb
    FROM Adoption
    INNER JOIN Animal ON Animal.id = Adoption.animal_id
    INNER JOIN Espece ON Animal.espece_id = Espece.id
    GROUP BY annee, Espece.id;
END
           DEFINER: sdz@localhost
     SECURITY_TYPE: DEFINER

Déroulement d'une requête de sélection

On a vu comment obtenir des informations sur les objets de nos bases de données, voyons maintenant comment obtenir des informations sur les requêtes que l'on exécute sur nos bases de données.

Plus particulièrement, nous allons nous attarder sur la commande EXPLAIN, qui permet de décortiquer l'exécution d'une requête SELECT. Grâce à cette commande, il est possible de savoir quelles tables et quels index sont utilisés, et dans quel ordre.

L'utilisation de cette commande est extrêmement simple : il suffit d'ajouter EXPLAIN devant la requête SELECT que l'on désire examiner.

Exemple

1
2
3
4
5

EXPLAIN SELECT Animal.nom, Espece.nom_courant AS espece, Race.nom AS race
FROM Animal
INNER JOIN Espece ON Animal.espece_id = Espece.id
LEFT JOIN Race ON Animal.race_id = Race.id
WHERE Animal.id = 37;

• id : cette colonne identifie la requête SELECT concernée par l'étape. Ici, il n'y en a qu'une, mais dans le cas d'une requête avec des sous-requêtes, ou avec un UNION, il peut y avoir plusieurs requêtes SELECT différentes.
• select_type : le type de la requête SELECT concernée par l'étape.

• table : le nom de la table sur laquelle l'étape est réalisée.
• type : le type de jointure utilisée par l'étape. Pour un détail des valeurs possibles, je vous renvoie à la documentation officielle. Les voici cependant classées du meilleur (du point de vue de la performance) au moins bon : system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL.
• possible_keys : les index que MySQL a envisagé d'utiliser pour l'étape.
• key : l'index effectivement utilisé
• key_len : la taille de l'index utilisé (peut être inférieure à la longueur de l'index en cas d'utilisation d'un index par la gauche).
• ref : indique à quoi l'index sera comparé : une constante (const) ou une colonne.
• rows : le nombre estimé de lignes que MySQL devra parcourir pour terminer l'étape (plus ce nombre est petit, mieux c'est).
• Extra : donne des informations supplémentaires sur l'étape.

Dans le cas de notre requête SELECT, on a donc 3 étapes :

• La clé primaire d'Animal est utilisée et permet de trouver tout de suite la ligne correspondant à notre recherche (id = 37). On ne doit donc parcourir qu'une seule ligne.
• En utilisant la valeur d'Animal.espece_id trouvée à l'étape 1, on trouve la ligne correspondante dans la table Espece, à nouveau en utilisant la clé primaire.
• Même chose pour la table Race.

L'intérêt est de pouvoir optimiser ses requêtes. Soit en ajoutant un ou plusieurs index, soit en trouvant la manière optimale d'écrire la requête.

Savoir sur quelle colonne ajouter un index

Lorsqu'une requête est un peu lente, il est souvent possible de l'accélérer en ajoutant un ou plusieurs index à des endroits stratégiques. En utilisant EXPLAIN, on peut découvrir facilement quelles étapes de la requête n'utilisent pas d'index, et donc sur quelles colonnes il peut être intéressant d'ajouter un index.

Exemple

1
2
3
4

EXPLAIN SELECT Animal.nom, Adoption.prix, Adoption.date_reservation
FROM Animal 
INNER JOIN Adoption ON Adoption.animal_id = Animal.id
WHERE date_reservation >= '2012-05-01' \G

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Adoption
         type: ALL
possible_keys: ind_uni_animal_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 24
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Animal
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: elevage7.Adoption.animal_id
         rows: 1
        Extra:

Pour la première étape, key, key_len et ref sont NULL. Aucun index n'est donc utilisé. type vaut ALL, ce qui est la valeur la moins intéressante. Enfin, rows vaut 24, ce qui est le nombre de ligne dans la table Adoption. Toutes les lignes doivent être parcourues pour trouver les lignes correspondant à la clause WHERE. La deuxième étape par contre utilise bien un index (pour faire la jointure avec Animal).

En ajoutant un index sur Adoption.date_reservation, on peut améliorer les performances de cette requête.

1

ALTER TABLE Adoption ADD INDEX ind_date_reservation (date_reservation);

La même commande EXPLAIN donnera désormais le résultat suivant :

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Adoption
         type: range
possible_keys: ind_uni_animal_id,ind_date_reservation
          key: ind_date_reservation
      key_len: 3
          ref: NULL
         rows: 4
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: Animal
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: elevage7.Adoption.animal_id
         rows: 1
        Extra:

La première étape utilise l'index, et ne doit donc plus parcourir toutes les lignes, mais seulement une partie (dont le nombre est estimé à 4).

Comparer le plan d'exécution de plusieurs requêtes

Lorsque l'on fait une condition sur une colonne (dans une clause WHERE ou pour une condition de jointure), ce n'est pas parce qu'il existe un index sur celle-ci qu'il sera utilisé par la requête. En particulier, si la colonne est utilisée dans une expression, l'index ne sera pas utilisé, car la valeur de l'expression devra être calculée pour chaque ligne. Selon la manière d'écrire une condition donc, l'utilisation des index sera possible, ou non. Lorsqu'on hésite entre plusieurs écritures, utiliser EXPLAIN peut permettre d'utiliser la requête la plus performante.

Exemple

1
2
3
4
5
6
7

EXPLAIN SELECT * 
FROM VM_Revenus_annee_espece
WHERE somme/2 > 1000 \G

EXPLAIN SELECT * 
FROM VM_Revenus_annee_espece
WHERE somme > 1000*2 \G

Ces deux requêtes SELECT produisent un résultat équivalent, mais la première empêche l'utilisation de l'index sur somme, contrairement à la deuxième.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: VM_Revenus_annee_espece
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16
        Extra: Using where


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: VM_Revenus_annee_espece
         type: range
possible_keys: somme
          key: somme
      key_len: 14
          ref: NULL
         rows: 2
        Extra: Using where

En résumé

• Les commandes SHOW permettent d'afficher une liste des structures choisies, ainsi qu'un certain nombre d'informations sur celles-ci.
• Il est possible de restreindre les résultats de certaines commandes SHOW avec les clauses LIKE et WHERE.
• Avec SHOW CREATE, on peut afficher la requête de création d'un objet.
• La base de données information_schema contient toutes les informations sur les objets des bases de données du serveur.
• La commande EXPLAIN, qui s'utilise devant un SELECT, décortique les étapes de la requête, ce qui permet d'optimiser celle-ci. Soit en ajoutant un index, soit en changeant la manière dont les conditions sont écrites.

• Gestion des utilisateurs
• Sommaire
• Configuration et options