création de site internet

flus RSS du blogsur Facebooksur Twitter
créateur de mot de passe

Générateur de mot de passe

Créez un mot de passe sécurisé

06
08

Optimisation MySQL - 2 - les index

dans MySQL

Un index en MySQL est un mécanisme qui permet d'accéder rapidement à des informations précises. Prenons l'exemple d'une bibliothèque et admettons que l'on cherche un ouvrage traitant de la Rome antique. Instinctivement, on ne va pas s'amuser à parcourir tous les rayons de la bibliothèque pour trouver notre bonheur, le premier réflexe va être de se concentrer sur le rayon histoire. C'est évident, car c'est le moyen le plus rapide de trouver ce que l'on cherche. Et bien les index sont basés sur cette logique. Ils segmentent virtuellement la base afin de limiter les données à traiter et ainsi accélérer les traitements des requêtes.

Les index sont basés sur 1 ou plusieurs colonnes (même si dans l'idéal moins l'index contient de colonne, mieux c'est). En fonction des colonnes indexées, MySQL va segmenter la table sur les différentes valeurs de la colonne. Exemple, sur une table classique contenant des coordonnées de personnes, admettons que l'on mette un index sur la civilité, la table sera virtuellement segmentée en 3 sous-ensembles : les enregistrements avec la civilité à "monsieur", ceux avec la civilité à "madame" et ceux à "mademoiselle". Ainsi quand on fera une requête avec un filtre sur la civilité, on ne traitera plus la totalité de la table, mais simplement le sous-ensemble concerné.

Je ne vais pas insister sur comment créer un index, le site de MySQL est là pour ça et pour les amateurs de PHPMyAdmin, c'est extrêmement simple de le faire par l'interface. Ce qui va nous intéresser, c'est comment optimiser l'utilisation des index, en choisissant le bon type d'index, en les plaçant sur les colonnes adéquates et enfin en réfléchissant aux types de données les plus adaptés.

1) les types d'index

NB : je ne traite pas là de la notion de clé primaire. J'imagine que vous, cher lecteur, êtes un minimum au fait de la gestion des bases de données et de MySQL et ainsi que cette notion vous est familière.

Il existe 3 types d'index en MySQL : INDEX, UNIQUE, FULLTEXT.

Les index de type INDEX sont les plus basiques dans le sens où ils segmentent la table sans autre contrainte. Pour autant ils permettent de traiter des sous-ensembles plus grands, ce qui peut être plus utiles sur certaines fonctionnalités.

Les index de type UNIQUE fonctionnent sur le même principe qu'une clé primaire, à la seule différence qu'ils peuvent traiter les valeurs NULL. Les index de ce type vont segmenter la table tout en s'assurant qu'il n'y ait aucun doublon dans la colonne concernée. Exemple, si on place un index de type UNIQUE sur une colonne email, cet index empêchera qu'un même email soit enregistré deux fois dans la table en renvoyant une erreur MySQL. Je reste peu favorable à ce type d'index, pour avoir constater des comportements étranges, notamment sur MySQL Cluster.

Les index de type FULL TEXT sont assez complexes à appréhender. Je ne vais d'ailleurs pas m'attarder dessus, préférant vous renvoyer vers la doc MySQL. Ce qu'il faut simplement savoir ce que les index de type FULL TEXT sont particulièrement adaptés pour les requêtes traitant des recherches sur des chaînes de caractères, permettant des manipulations avancées des textes et des critères de recherche. A noter que ce type d'index n'est pas supporté sur MySQL Cluster.

2) le choix des colonnes

Pour déterminer quelles colonnes indexer, il suffit de répondre aux questions suivantes :

  • Quelles sont les colonnes utilisées dans mes clauses WHERE ? (cas particuliers avec LIKE où seuls les filtres LIKE 'qquechose%', LIKE 'qque%chose%' exploitent les index, les filtres LIKE '%qquechose', LIKE '%qquechose%' et LIKE uneautrecolonne n'utilisent pas les index)
  • Quelles sont les colonnes utilisées dans des jointures avec d'autres tables ?
  • Quelles sont les colonnes utilisées dans mes clauses GROUP BY ou ORDER BY ?
  • Quelles sont les colonnes utilisées par les fonctions MIN() et MAX() ?

Bien sûr, ça ne sert à rien de mettre des index à toutes les colonnes, qui plus est, ça peut s'avérer néfaste étant donné que tous ces index sont stockés en mémoire et que l'espace mémoire est par définition limité. D'ailleurs, lorsque dans une requête, MySQL est amené à choisir entre plusieurs index, il va systématiquement prendre en considération l'index qui lui ressortira le sous-ensemble avec le moins d'enregistrements. Exemple, admettons que sur la table citée précédemment, en plus de l'index sur la civilité, on ait un index sur l'âge :

SELECT * FROM personnes WHERE civilite='monsieur' AND age='30';

Pour la requête ci-dessus, MySQL va utiliser l'index sur l'âge pour optimiser le traitement des résultats. Car le sous-ensemble des personnes agées de exactement 30 ans est plus petit que le sous-ensemble des personnes dont la civilité est à "monsieur".

Pour autant il est possible de forcer MySQL à utiliser un index précis, en utilisant USE INDEX :

SELECT * FROM personnes USE INDEX (nom_de_lindex_sur_civilite) WHERE civilite='monsieur' AND age='30';

La requête ci-dessus ne va plus laisser le choix à MySQL qui devra baser son optimisation sur l'index portant sur la colonne civilité. Alors sur les moteurs classiques (myisam, innodb, ...) ce n'est pas forcément pertinent car MySQL gère parfaitement les optimisations sur les index. En revanche sur des moteurs tel que MySQL Cluster où la gestion des index est beaucoup moins précise, il faut absolument utiliser ces mentions et le plus intelligement possible.

Enfin, quant à savoir si l'on place l'index sur une ou plusieurs colonne, voilà comment fonctionne MySQL :

SELECT * FROM matable WHERE col1='1' AND col2='2' AND col3='3';

Si l'index est multicolonne et placé sur col1,col2 et col3, MySQL va exploiter l'index et traiter le sous-ensemble concerné. En revanche si à la place, on a 3 index, un sur col1, un sur col2 et le dernier sur col3, MySQL va exploiter l'index dont le sous-ensemble contient le moins d'enregistrements.

SELECT * FROM matable WHERE col1='1';
SELECT * FROM matable WHERE col2='2';
SELECT * FROM matable WHERE col1='1' AND col2='2';
SELECT * FROM matable WHERE col2='2' AND col3='3';

Ici, si l'index est multicolonne, la première et la troisième requêtes l'exploiteront et seront optimisées (car col1 et col1,col2 préfixent l'index col1,col2,col3). Si chaque colonne disposent d'un index, les 2 premières requêtes l'exploiteront, les 2 dernières exploiteront l'index qui retourne le moins d'enregistrements (cf. principe précédent).

3) le choix des types de données

Les colonnes à indexer doivent bénéficier d'une attention particulière sur leur type de données. Il faut commencer par reprendre les principes évoqués dans l'article précédent et ainsi séléctionner le type et le format le plus juste pour la colonne. Pour les index, il faut privilégier dans la mesure du possible les colonnes de type numérique ou date, et éviter les colonnes de type texte.

Il y a 2 raisons à cela, la première est que MySQL est plus efficace et rapide sur les types numériques ou date, beaucoup moins sur les types texte. Ensuite, les index étant stockés en mémoire, les types texte prennent beaucoup plus d'espace mémoire, alourdissant ainsi le stockage relatif à MySQL.

Partager :