• 40 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

Ce cours existe en livre papier.

Vous pouvez obtenir un certificat de réussite à l'issue de ce cours.

Vous pouvez être accompagné et mentoré par un professeur particulier par visioconférence sur ce cours.

J'ai tout compris !

Mis à jour le 24/04/2019

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

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

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, ce qui permet d'optimiser celles-ci.

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

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.

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.

Commande

Description

SHOW CHARACTER SET

Montre les sets de caractères (encodages) disponibles.

SHOW [FULL] COLUMNS FROM nom_table [FROM nom_bdd]

Liste les colonnes de la table précisée, ainsi que diverses informations (type, contraintes…). Il est possible de préciser également le nom de la base de données. En ajoutant le mot-clé FULL, les informations affichées pour chaque colonne sont plus nombreuses.

SHOW DATABASES

Montre les bases de données sur lesquelles on possède des privilèges (ou toutes si l'on possède le privilège global SHOW DATABASES).

SHOW GRANTS [FOR utilisateur]

Liste les privilèges de l'utilisateur courant, ou de l'utilisateur précisé par la clause FOR  optionnelle.

SHOW INDEX FROM nom_table [FROM nom_bdd]

Liste les index de la table désignée. Il est possible de préciser également le nom de la base de données.

SHOW PRIVILEGES

Liste les privilèges acceptés par le serveur MySQL (dépend de la version de MySQL).

SHOW PROCEDURE STATUS

Liste les procédures stockées.

SHOW [FULL] TABLES [FROM nom_bdd]

Liste les tables de la base de données courante, ou de la base de données désignée par la clause FROM. Si FULL  est utilisé, une colonne apparaîtra en plus, précisant s'il s'agit d'une vraie table ou d'une vue.

SHOW TRIGGERS [FROM nom_bdd]

Liste les triggers de la base de données courante, ou de la base de données précisée grâce à la clause FROM.

SHOW [GLOBAL | SESSION] VARIABLES

Liste les variables système de MySQL. Si GLOBAL  est précisé, les valeurs des variables seront celles utilisées lors d'une nouvelle connexion au serveur. Si SESSION  est utilisé (ou si l'on ne précise ni GLOBAL  ni SESSION), les valeurs seront celles de la session courante. Plus d'informations sur les variables système seront données dans le prochain chapitre.

SHOW WARNINGS

Liste les avertissements générés par la dernière requête effectuée.

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".

SHOW COLUMNS 
FROM Adoption
LIKE 'date%';

Field

Type

Null

Key

Default

Extra

date_reservation

date

NO

 

NULL

 

date_adoption

date

YES

 

NULL

 

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

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

Charset

Description

Default collation

Maxlen

cp1256

Windows Arabic

cp1256_general_ci

1

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.

SHOW CREATE TABLE Espece \G
*************************** 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.

SHOW CREATE TRIGGER before_insert_adoption \G
*************************** 1. row ***************************
               Trigger: before_insert_adoption
              sql_mode: 
SQL Original Statement: CREATE DEFINER=`student`@`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 que l'on trouve comme tables dans cette base de données.

SHOW TABLES FROM information_schema;

Tables_in_information_schema

CHARACTER_SETS

COLUMNS

COLUMN_PRIVILEGES

REFERENTIAL_CONSTRAINTS

ROUTINES

SESSION_VARIABLES

STATISTICS

TABLES

TABLE_CONSTRAINTS

TABLE_PRIVILEGES

TRIGGERS

USER_PRIVILEGES

VIEWS

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 ?

SHOW COLUMNS FROM VIEWS FROM information_schema;

Field

Type

Null

Key

Default

Extra

TABLE_CATALOG

varchar(512)

YES

 

NULL

 

TABLE_SCHEMA

varchar(64)

NO

 

 

 

TABLE_NAME

varchar(64)

NO

 

 

 

VIEW_DEFINITION

longtext

NO

 

NULL

 

CHECK_OPTION

varchar(8)

NO

 

 

 

IS_UPDATABLE

varchar(3)

NO

 

 

 

DEFINER

varchar(77)

NO

 

 

 

SECURITY_TYPE

varchar(7)

NO

 

 

 

CHARACTER_SET_CLIENT

varchar(32)

NO

 

 

 

COLLATION_CONNECTION

varchar(32)

NO

 

 

 

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.

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. 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: student@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.

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

CONSTRAINT_SCHEMA

CONSTRAINT_NAME

TABLE_NAME

CONSTRAINT_TYPE

elevage

PRIMARY

Animal

PRIMARY KEY

elevage

ind_uni_nom_espece_id

Animal

UNIQUE

elevage

fk_race_id

Animal

FOREIGN KEY

elevage

fk_espece_id

Animal

FOREIGN KEY

elevage

fk_mere_id

Animal

FOREIGN KEY

elevage

fk_pere_id

Animal

FOREIGN KEY

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().

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. 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: student@localhost
     SECURITY_TYPE: DEFINER

Déroulement d'une requête

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. 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 que l'on désire examiner.  EXPLAIN  peut être utilisée pour les requêtes  SELECT,  UPDATE,  DELETE,  INSERT  et  REPLACE  .

Exemple

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

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

1

SIMPLE

Animal

const

PRIMARY,fk_espece_id

PRIMARY

2

const

1

 

1

SIMPLE

Espece

const

PRIMARY

PRIMARY

2

const

1

 

1

SIMPLE

Race

const

PRIMARY

PRIMARY

2

const

1

 

  • id : cette colonne identifie la requête 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.

    Type

    Explication

    SIMPLE

    Un simple SELECT, sans sous-requête ou UNION.

    PRIMARY

    SELECT  extérieur, c'est-à-dire le premier, le principal SELECT  (en présence de sous-requêtes ou de UNION).

    UNION

    Seconde requête SELECT  d'un UNION  (et requêtes suivantes).

    DEPENDENT UNION

    Comme UNION, mais requête dépendant d'une requête SELECT  externe (à cause d'une sous-requête corrélée, par exemple).

    UNION RESULT

    Résultat d'un UNION.

    SUBQUERY

    Première requête SELECT  d'une sous-requête.

    DEPEDENT SUBQUERY

    Première requête SELECT  d'une sous-requête, dépendant d'une requête externe.

    DERIVED

    Table dérivée (résultat d'une sous-requête).

    UNCACHEABLE SUBQUERY

    Sous-requête dont le résultat ne peut être mis en cache et doit donc être réévalué pour chaque ligne de la requête SELECT.

    UNCACHEABLE UNION

    Deuxième (ou plus) requête SELECT  dans un UNION  faite dans une sous-requête qui ne peut être mise en cache.

  • table : le nom de la table sur laquelle l'étape est réalisée.

  • type : le type de jointure utilisé par l'étape. Pour un détail des valeurs possibles, je vous renvoie à la documentation officielle. Les voici cependant classées de la meilleure (du point de vue de la performance) à la moins bonne : 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.

Mais quel est l'intérêt de savoir cela ?

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'en ajouter un.

Exemple

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. 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 lignes 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.

ALTER TABLE Adoption ADD INDEX ind_date_reservation (date_reservation);

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

*************************** 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. Lorsque l'on hésite entre plusieurs écritures, utiliser EXPLAIN  peut permettre d'utiliser la requête la plus performante.

Exemple

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. 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 décortique les étapes d'une 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.

Exemple de certificat de réussite
Exemple de certificat de réussite