Partie I
SQL de base
32
© Éditions Eyrolles
La plupart des index de MySQL (PRIMARY KEY, UNIQUE, INDEX, et FULLTEXT) sont stockés dans des arbres équilibrés (balanced trees : B-trees). D’autres types d’index existent,
citons ceux qui portent sur des colonnes SPATIAL (reverse key : R-trees), et ceux appliqués
aux tables MEMORY (tables de hachage : hash).
La particularité des index B-tree est qu’ils conservent en permanence une arborescence symétrique (balancée). Toutes les feuilles sont à la même profondeur. Le temps de recherche est
ainsi à peu près constant quel que soit l’enregistrement cherché. Le plus bas niveau de l’index
(leaf blocks) contient les valeurs des colonnes indexées et le rowid. Toutes les feuilles de
l’index sont chaînées entre elles. Pour les index non uniques (par exemple si on voulait définir
un index sur la colonne compa de la table Pilote) le rowid est inclus dans la valeur de la
colonne indexée. Ces index, premiers apparus, sont désormais très fiables et performants, ils
ne se dégradent pas lors de la montée en charge de la table.
Création d’un index (CREATE INDEX)
Pour pouvoir créer un index dans sa base, la table à indexer doit appartenir à la base. Si l’utilisateur a le privilège INDEX, il peut créer et supprimer des index dans sa base. Un index est
créé par l’instruction CREATE INDEX et supprimé par DROP INDEX.
La syntaxe de création d’un index est la suivante :
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX nomIndex
[USING BTREE | HASH]
ON nomTable (colonne1 [(taille1)] [ASC | DESC],...) ;
q
UNIQUE permet de créer un index qui n’accepte pas les doublons.
q
FULLTEXT permet de bénéficier de fonctions de recherche dans des textes (flot de caractères).
q
SPATIAL permet de profiter de fonctions pour les données géographiques.
q
ASC et DESC précisent l’ordre (croissant ou décroissant).
Créons deux index sur la table Pilote.
Tableau 1-10 Créations d’index
Instruction SQL
Commentaires
CREATE UNIQUE INDEX idx_Pilote_nom3
USING BTREE
ON Pilote (nom(3) DESC);
Index B-tree, ordre décroissant sur les trois
premiers caractères du nom des pilotes.
CREATE INDEX idx_Pilote_compa
USING BTREE
ON Pilote (compa);
Index B-tree, ordre croissant sur la colonne clé
étrangère compa.
SQL de base
32
© Éditions Eyrolles
La plupart des index de MySQL (PRIMARY KEY, UNIQUE, INDEX, et FULLTEXT) sont stockés dans des arbres équilibrés (balanced trees : B-trees). D’autres types d’index existent,
citons ceux qui portent sur des colonnes SPATIAL (reverse key : R-trees), et ceux appliqués
aux tables MEMORY (tables de hachage : hash).
La particularité des index B-tree est qu’ils conservent en permanence une arborescence symétrique (balancée). Toutes les feuilles sont à la même profondeur. Le temps de recherche est
ainsi à peu près constant quel que soit l’enregistrement cherché. Le plus bas niveau de l’index
(leaf blocks) contient les valeurs des colonnes indexées et le rowid. Toutes les feuilles de
l’index sont chaînées entre elles. Pour les index non uniques (par exemple si on voulait définir
un index sur la colonne compa de la table Pilote) le rowid est inclus dans la valeur de la
colonne indexée. Ces index, premiers apparus, sont désormais très fiables et performants, ils
ne se dégradent pas lors de la montée en charge de la table.
Création d’un index (CREATE INDEX)
Pour pouvoir créer un index dans sa base, la table à indexer doit appartenir à la base. Si l’utilisateur a le privilège INDEX, il peut créer et supprimer des index dans sa base. Un index est
créé par l’instruction CREATE INDEX et supprimé par DROP INDEX.
La syntaxe de création d’un index est la suivante :
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX nomIndex
[USING BTREE | HASH]
ON nomTable (colonne1 [(taille1)] [ASC | DESC],...) ;
q
UNIQUE permet de créer un index qui n’accepte pas les doublons.
q
FULLTEXT permet de bénéficier de fonctions de recherche dans des textes (flot de caractères).
q
SPATIAL permet de profiter de fonctions pour les données géographiques.
q
ASC et DESC précisent l’ordre (croissant ou décroissant).
Créons deux index sur la table Pilote.
Tableau 1-10 Créations d’index
Instruction SQL
Commentaires
CREATE UNIQUE INDEX idx_Pilote_nom3
USING BTREE
ON Pilote (nom(3) DESC);
Index B-tree, ordre décroissant sur les trois
premiers caractères du nom des pilotes.
CREATE INDEX idx_Pilote_compa
USING BTREE
ON Pilote (compa);
Index B-tree, ordre croissant sur la colonne clé
étrangère compa.