WampServer

Bonjour à tous,

J'utilise Wampserver depuis quelques temps et j'ai la question suivante :

Lorsque je crée une table, que j'en remplis les champs, je peux spécifier qu'une clé est primaire. Mais à quoi servent les spécifications "unique", "index" et "fulltext" ? Et par dessus tout, comment spécifie t-on qu'une clé est secondaire ?

Merci par avance

Bonjour,

Un clé secondaire n'existe pas. Il peut y avoir plusieurs « index » sur une même table.

Voilà un petit topo sur les index et MySQL (Uniquement pour le moteur MyISAM)

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 ma propre base de données)

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.

Pour étudier les questions de performance, j'ai créé une table de test comprenant près de 500000 lignes à partir d'une copie de 196 lignes d'une de mes tables, de mes tables Octet (255) et Chiffres (10)

CREATE TABLE TestPerf
SELECT Nom, Prenom
FROM Apprentis CROSS JOIN Octet CROSS JOIN Chiffres
ORDER BY RAND(); -- Tri aléatoire
Query OK, 499200 rows affected (26.11 sec)

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

SELECT COUNT(*) FROM TestPerf WHERE Prenom = 'Pierre' ;
Réponse de MySQL
COUNT(*)
I 28160
1 row in set (6.05 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 TestPerf ADD COLUMN Prenomlndexe VARCHAR(45) ;
UPDATE TestPerf SET Prenomlndexe = Prenom ;
CREATE INDEX i ON TestPerf(PrenomIndexe) ;

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

SELECT SQL_NO_CACHE
  COUNT(*) FROM TestPerf WHERE Prenomlndexe = 'Pierre' ;

1 row in set (0.28 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 TestPerf Where.
La colonne indexée est spectaculairement plus rapide, sauf dans un cas

Condition                Avec    Avec
                         Prenom  Prenomindexe
= 'Pierre'               7,61    0,41
LIKE 'Pierre%'           7,47    0,03
LIKE '%Pierre%'          7,50    8,33
IS NULL                  7,42    0,06
IS NOT NULL              7,66    0,53
IN ('Pierre','Marie')    7,72    0,05

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 Pierre, et tout aussi facile de trouver Pierre-Marie. Par contre, si vous souhaitez trouver Jean-Pierre, Marie-Pierre, 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
            Prenom   Prenomlndexe
< 'Alex'    7,89     0,38
< 'Jean'    7,64     4,00
< 'Yasmina' 7,52     8,39
> 'Alex'    7,56     7,98
> 'Jean'    7,53     4,20
> 'Yasmina' 7,66     0,16

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 Yasmina. 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 TestPerf2 WHERE Prenom > 'Yasmina';
EXPLAIN
SELECT COUNT(*) FROM TestPerf2 WHERE Prenomlndexe > 'Yasmina';

Quelques colonnes de l'Explain des deux requêtes

Table       Type   Possible  Key   Key      Rows     Extra
                   _key            _len
TestPerf    ALL                             476807   Using where
TestPerf    range   i        i     48       9548     Using where
                                                     Using index

Lire un plan d'exécution ne s'apprend pas en quelques lignes. Soulignons juste trois points :
Le type All indique que MySQL effectue un balayage de la table.
La Possible_key est l'index que MySQL juge utilisable. key est l'index qu'il utilise effectivement.
La colonne Extra indique clairement l'utilisation de l'index, ainsi que l'optimisation du Where.

Optimisation de MySQL (En français) :
[dev.mysql.com]

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

Bonsoir,

Merci pour cette aide très technique. Donc, si je comprends bien, pas de véritables clés "secondaires" ou "étrangères", seulement des index sur les clés qui ne sont pas primaires et qui sont appelées à reparaître dans d'autres tables, c'est ça ?

Merci beaucoup.

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 table MyISAM.

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons