MySQL, index et contraintes d'intégrité par clés étrangères

Les Pages Perso Chez Free

Par Otomatic, le , dans Créer ses pages perso.
Tags : Erreur 500, MySQL, PHP, Sécurité, Tutoriaux

1. MySQL et les index

Les index sont un sujet vraiment paradoxal. Ils sont absents de la théorie relationnelle et de la norme SQL. Invisibles à l'utilisateur, ils sont pourtant omniprésents dans toute base de donnée relationnelle.

Notion d'index

Nota, j'ai pris des exemples à partir mes propres bases de données et tables :

  • aviatechno.avia_vilg_apprentis : Table des anciens apprentis de mon école professionnelle
  • aviatechno.test_octet : Table contenant 0 à 255 utilisée pour mes tests
  • aviatechno.test_chiffres : Table contenant 0 à 9 utilisée pour mes tests
  • aviatechno.test_perf : qui sera créée et utilisée pour les tests d'index
  • Windows 7 Pro 64 bits - Apache 2.4.7 - MySQL 5.6.16 - PHP 5.5.9
  • Les chiffres obtenus peuvent être très différents en fonction du processeur, de la quantité mémoire et de la vitesse de transfert des disques durs.

Les lignes d'une table sont stockées sans aucun ordre logique ; si l'on demande ainsi à MySQL de rechercher le ou les livres titrés « L'Illustration », il doit parcourir toutes les lignes de la table et tester chaque titre. Si les livres sont triés par ordre alphabétique de titre, MySQL peut sauter directement à la lettre L et s'arrêter de chercher quand il a atteint la lettre M.

Les index sont des sortes de répertoires qui permettent cette accélération de la recherche. Supposez par exemple que l'on pose trois index sur la table Livres, sur les colonnes Titre, Compendium et IDlivre. La table n'est pas ordonnée, mais chacun des index l'est, selon son ordre propre. À chaque fois qu'on ajoute ou qu'on supprime un livre, ou bien que l'on modifie son titre ou son Compendium, les index sont mis à jour. La création d'un index a donc pour effet d'augmenter la taille de la base de données, de ralentir légèrement toutes les modifications de données, mais d'accélérer énormément les recherches sur la colonne indexée.

Il est donc recommandé de poser des index sur les colonnes fréquemment utilisées en recherche, et seulement sur celles-ci.

Les types et création des index

Les colonnes les plus utiles à indexer sont évidemment les clés ; MySQL tend d'ailleurs à confondre les deux notions d'index et de clé. On peut le voir à travers les types d'index proposés par MySQL…

  • Key ou Index : index simple, autorisant Null et doublons.
  • Unique : index interdisant les doublons, et mettant ainsi en oeuvre une contrainte d'unicité.
  • Primary Key : MySQL définit automatiquement un index sur chaque clé primaire ; déclarer une colonne comme clé primaire a pour effet d'y interdire les Null et les doublons.

On sait comment définir, à la création de la table, une colonne comme clé primaire (PRIMARY KEY). Voici comment on peut ajouter les deux index manquant à la table Livres :

ALTER TABLE Livres
ADD Index (Titre), ADD Index (Compendium) ;

Avec de petites tables, il n'y a guère de problème de vitesse et de taux d'occupation du serveur MySQL. Donc, pour étudier les questions de performance, j'ai créé une table de test comprenant plus de sept millions de lignes à partir d'une copie de 202 lignes d'une de mes tables, de mes tables d'essais Octet (255) et Chiffres (10)

CREATE TABLE Test_Perf
SELECT A.a_nom, A.a_prenom
FROM avia_vilg_apprentis AS A
CROSS JOIN test_octet AS O CROSS JOIN test_chiffres ASC
ORDER BY RAND(); -- Tri aléatoire
Query OK, 7439360 rows affected (50.02 sec)

Cherchons maintenant combien il y a d'apprentis prénommés Michel :

SELECT COUNT(*) FROM Test_Perf WHERE a_prenom = 'Michel' ;

Réponse de MySQL :

+----------+
| COUNT(*) |
+----------+
|   317440 |
+----------+
1 row in set (3.14 sec)

Si on relance la même requête, on constate que nous obtenons cette fois-ci une réponse quasi immédiate (0,01 ou même 0,00 seconde). MySQL utilise en effet un cache de requêtes (query cache). Si une requête est relancée à l'identique (au caractère près) et si la table source n'a pas été modifiée depuis, il se souvient du résultat.

Afin d'éliminer cet artefact, nous utiliserons à l'avenir le mot-clé Sql_No_Cache pour demander à MySQL de ne pas utiliser le cache de requêtes.

Effets d'un index

Dans quelle mesure un index peut-il accélérer cette recherche ? Pour pouvoir comparer, nous allons dupliquer la colonne Prenom, et poser un index sur le duplicata :

ALTER TABLE Test_Perf ADD COLUMN PrenomIndexe VARCHAR(45) ;
Query OK, 7439360 rows affected (4.10 sec)
UPDATE Test_Perf SET PrenomIndexe = a_prenom ;
Query OK, 7439360 rows affected (1 min 40.25 sec)
mysql> CREATE INDEX i ON Test_Perf(PrenomIndexe) ;
Query OK, 7439360 rows affected (10 min 01.39 sec)

On peut déjà voir que la création de l'index prend plus de 10 min ; il faut quand-même voir qu'il y a plus de 7 millions de lignes.

Combien de temps prend la recherche des Michel dans la colonne indexée ? 

SELECT SQL_NO_CACHE
COUNT(*) FROM Test_Perf WHERE PrenomIndexe = 'Michel' ;
+----------+
| COUNT(*) |
+----------+
|   317440 |
+----------+
1 row in set (0.19 sec)

L'effet de l'index est spectaculaire !

Afin d'aller plus loin dans sa mesure, voici les résultats d'une série de tests (que vous êtes invité à reproduire).

Toutes les requêtes commencent par : Select Sql_No_Cache Count(*) From Test_Perf Where.

La colonne indexée est spectaculairement plus rapide, sauf dans un cas

Condition                Avec      Avec
                         a_prenom  PrenomIndexe
= 'Michel'               2,32      0,20
LIKE 'Michel%'           2,61      0,21
LIKE '%Michel%'          5,18      5,16
IS NULL                  0,00      0,00
IS NOT NULL              2.51      2.32
IN ('Michel','Jean')     2,79      0,41

Avec la colonne sans index, la durée de la requête est toujours à peu près la même : c'est le temps nécessaire à MySQL pour effectuer un balayage complet de la table (table scan).

Avec l'index, le temps est beaucoup plus court, sauf pour le test "contient (LIKE)". En effet, l'index se présente comme un répertoire des prénoms par ordre alphabétique, avec l'adresse des lignes correspondantes. Sur un tel répertoire, il est facile de trouver Michel, et tout aussi facile de trouver Jean. Par contre, si vous souhaitez trouver Jean-Michel, Jean-Marie, et tous les autres composés, vous êtes obligé de parcourir tout le répertoire. L'index trié ne vous apporte donc aucun avantage par rapport à la table, où les lignes sont en vrac.

Que donne l'index avec les opérateurs < et > ? 

Condition   Avec       Avec
            a_prenom   Prenomlndexe
< 'Alex'    7,89       0,32
< 'Jean'    7,64       4,00
< 'Yves'    7,52       8,19
> 'Alex'    7,56       7,87
> 'Jean'    7,53       4,17
> 'Yves'    7,66       0,15

Avec la condition inférieur, l'index est très efficace en début d'ordre alphabétique, et perd de son efficacité au fur et à mesure qu'on avance vers la fin. Avec supérieur, c'est le contraire. L'efficacité d'un index dépend de sa spécificité. En effet, peu de prénoms se trouvent avant Alex. L'index permet donc d'économiser l'essentiel du nombre de lignes.

À l'inverse, presque tous les prénoms sont avant Yves. MySQL doit donc parcourir soit la table en entier, soit l'index dans sa quasi-intégralité. Comme celui-ci est destiné à une recherche précise et non à une recherche intégrale, il est alors moins efficace que la table.

MySQL utilise-t-il vraiment l'index ?

À chaque requête, l'optimiseur de MySQL choisit ou non d'utiliser l'index. L'essentiel du travail d'optimisation consiste à s'assurer qu'il fait les bons choix.

Les chiffres présentés ici (et qu'on retrouve plus ou moins en répétant plusieurs fois les tests) montrent que la contribution globale de l'index est très positive, même si elle peut être légèrement pénalisante dans les cas marginaux.

Pour connaître la décision de l'optimiseur, mettons la commande EXPLAIN en tête de requête. Elle révèlera le plan d'exécution de la requête :

Affichage du plan d'exécution de deux requêtes 

EXPLAIN
SELECT COUNT(*) FROM Test_Perf WHERE a_prenom > 'Yves';
EXPLAIN
SELECT COUNT(*) FROM Test_Perf WHERE Prenomlndexe > 'Yves';

Quelques colonnes de l'Explain des deux requêtes 

Table        Type   Possible  Key   Key     Rows       Extra
                    _key            _len
Test_Perf    ALL    NULL      NULL  NULL    7469360    Using where
Test_Perf    range  i         i     138       10109    Using where
                                                       Using index

Lire un plan d'exécution ne s'apprend pas en quelques lignes. Soulignons juste trois points :

  1. Le type All indique que MySQL effectue un balayage de la table.
  2. La Possible_key est l'index que MySQL juge utilisable. key est l'index qu'il utilise effectivement.
  3. La colonne Extra indique clairement l'utilisation de l'index, ainsi que l'optimisation du Where.

Optimisation de MySQL (en français) : http://dev.mysql.com/doc/refman/5.0/fr/mysql-optimization.html

2. Intégrité référentielle et « clé étrangère »

Les contraintes d'intégrité sont destinées à empêcher les données fausses ou incohérentes de polluer la base.

Implicitement, plusieurs existent déjà :

  • Définir un type de colonne : pose implicitement une contrainte sur ses valeurs ; par exemple, une colonne définie comme TINYINT UNSIGNED ne peut contenir que des entiers positifs, compris entre 0 et 255.
  • L'interdiction des NULL : rendre une colonne obligatoire par la mention NOT NULL est un des mécanismes d'intégrité les plus courants.
  • La contrainte d'unicité : interdire les doublons par un index unique ou une clé primaire est également un mécanisme d'intégrité classique.
  • Les types ENUM et SET : il s'agit d'interdire les valeurs qui sont hors de la liste précisées avec le type.

Les tables de type InnoDB permettent d'ajouter un autre type de contrainte : les contraintes d'intégrité référentielle. Il s'agit d'obliger une clé « étrangère » à n'avoir que des valeurs qui existent dans la table référencée. Par exemple, le Genre d'un livre ne peut être PO que si un genre codé PO existe vraiment dans la table Genres.

Dans MySQL, ce type de contrainte ne fonctionne qu'à trois conditions :

  • Les deux tables mises en jeu doivent être gérées par le moteur InnoDB.
  • La clé étrangère et la colonne qu'elle référence doivent être indexées (au besoin, MySQL créera automatiquement un index sur la clé étrangère).
  • La clé étrangère et la colonne référencée doivent avoir le même type et les mêmes options (par exemple, un Int ne peut pas référencer un Smallint, et un Smallint ne peut pas référencer un Smallint Unsigned).

Procédons avec des exemples…

Pour mettre en place l'intégrité référentielle entre Livres et Genres, il faut donc commencer par ajouter un index sur la colonne Genre de la table Livres :

ALTER TABLE Livres ADD INDEX (CodeGenre) ;

On peut, ensuite, ajouter la contrainte d'intégrité référentielle :

Création d'une contrainte d'intégrité référentielle : Livres.CodeGenre doit référencer une valeur existant dans Genres.CodeGenre 

ALTER TABLE Livres
ADD FOREIGN KEY (CodeGenre) REFERENCES Genres (CodeGenre) ;

Que se passera-t-il si on souhaite modifier un code genre ? Par exemple, le code pour les sciences est SC, et on voudrait le changer en Sci. Avec la contrainte d'intégrité référentielle posée entre Livres et Genres, tous les livres codés SC se retrouveront hors intégrité. InnoDB va donc refuser toute modification.

Pour permettre ce genre de correction de code, il aurait fallu prévoir, lors de la création de la contrainte d'intégrité référentielle, la modification en cascade, avec l'option On Update Cascade.

De la même manière, InnoDB refusera qu'on supprime un genre référencé dans la table Livres. La suppression en cascade (On Delete Cascade) supprimerait tous les livres du genre concerné ! Une façon plus prudente de procéder serait de prévoir que les livres dont un genre est supprimé se retrouvent sans genre, c'est-à-dire avec un code genre Null : c'est l'option On Delete Set Null.

Pour ajouter les deux options On Update et On Delete à une contrainte d'intégrité référentielle déjà existante, il faut utiliser Show Create Table pour trouver le nom que MySQL a attribué à la contrainte, puis Alter Table… Drop Foreign Key pour la supprimer : Afficher l'instruction de création de la table Livres (SHOW CREATE TABLE Livres ;), permet de retrouver le nom attribué à la contrainte d'intégrité référentielle vers Genres (par exemple, livres_ibfk_1)

Suppression de la contrainte livres_ibfk_1 : 

ALTER TABLE Livres
DROP FOREIGN KEY livres ibfk 1 ;
On peut maintenant recréer la contrainte avec ses deux options : 
ALTER TABLE Livres
ADD FOREIGN KEY (CodeGenre) REFERENCES Genres (CodeGenre)
ON UPDATE CASCADE
ON DELETE SET NULL ;

Exemples pour la création de deux tables (Editeurs et Collections) avec une contrainte d'intégrité référentielle, puis la modification de la table Livres avec clé étrangère vers la table Collections : 

CREATE TABLE Editeurs
IDediteur SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
Editeur VARCHAR(30) NOT NULL UNIQUE
ENGINE = InnoDB ;

On peut maintenant créer Collections avec sa contrainte d'intégrité référentielle : 

CREATE TABLE Collections
IDcoll SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
Collection VARCHAR(60) NOT NULL,
IDediteur SMALLINT UNSIGNED NOT NULL,
INDEX (IDediteur),
FOREIGN KEY (IDediteur) REFERENCES Editeurs (IDediteur) ENGINE = InnoDB ;

Il ne reste plus qu'à ajouter à la table Livres la contrainte vers Collections : 

ALTER TABLE Livres
ADD INDEX(IDcoll),
ADD FOREIGN KEY (IDcoll) REFERENCES Collections (IDcoll) ;

Je répète que les contraintes d'intégrité par clés étrangères ne peuvent être appliquées QUE sur les tables de type InnoDB et en aucune manière sur les tables MyISAM, ces dernières actuellement les seules utilisables avec MySQL sur l'infrastructure de Free.