L’indexation ? Pour quoi faire ? Comment faire ?

Dans la vie quotidienne, nous sommes entourés par les index : une adresse postale est une forme d’index, tout comme un numéro de téléphone ou un numéro de sécu. Pourtant, nombre de développeurs négligent l’indexation des données de sa base. Nous allons voir dans cet article pourquoi c’est important et comment optimiser cette partie. Cet article est basé sur ma propre expérience en la matière, ainsi que sur un certain nombre d’articles sur internet.


Pourquoi créer des Index ?

Lors de la création d’une table, la plupart des SGBD crée automatiquement un certain nombre d’index (sur les clés primaires et étrangères principalement).
Néanmoins, cela ne suffit pas. Prenons le cas d’une table client dont la structure serait la suivante :

Cette structure sera utilisée dans la suite de cet article et on réalisera tous les tests sur cette base de données, dans laquelle on aura pris soin d’insérer des données de tests (10 000 000 clients, chaque client ayant passé une seule commande).

Sans index, une simple recherche d’un client habitant Lyon par exemple (pour simplifier, on considérera que cela revient à chercher les clients dont le code postal commence par « 6900 ») prendra beaucoup de temps et de ressources matérielles :

  • Temps de CPU : 6488
  • Nombre de reads (donne une idée des accès disque) : 105871
  • Temps d’exécution : 15868

En ajoutant un index simple sur le champ « ClientCP », les performances seront considérablement augmentées :

  • Temps de CPU : 16 (soit un gain d’environ 99,75%)
  • Nombre de reads : 9142 (soit un gain d’environ 91,36%)
  • Temps d’exécution : 1598 (soit un gain d’environ 89,93%)

Comment créer des index efficaces ?

Pour commencer, il existe deux types d’index : les index cluster et les autres. Un index cluster est un peu particulier en cela qu’il « mixe » la table et l’index. C’est typiquement le cas de la clé primaire d’une table, à quelques exceptions près (guid par exemple). Évidemment, il n’est possible de créer qu’un seul index cluster par table.

Une autre notion est importante : la qualité de l’index :

  • Index 1 étoile : index qui rend le filtre WHERE « sargable »
  • Index 2 étoiles : index 1 étoile qui, en plus, couvre le SELECT
  • Index 3 étoiles : index 2 étoiles qui, en plus, couvre le tri (ORDER BY)
  • Index 4 étoiles : index 3 étoiles qui, en plus, couvre le regroupement (GROUP BY)

Par ailleurs, Indexer une (des) tables c’est bien, mais pas forcément suffisant. Une base de données contenant des vues gagnera grandement en performances si les vues sont également indexées. Cela n’est cependant pas possible sur tous les SGBD.

Quels index créer (ou ne pas créer) ?

1) Indexation systématique des clefs étrangères
L’idée est évidemment bonne (c’est d’ailleurs pour cette raison que la plupart des SGBD crée automatiquement ce type d’index). Pourtant, ce n’est pas toujours nécessaire.
Exemple :
Dans le cas du modèle ci-dessous, la clé primaire de la table intermédiaire « Composée » porte sur les champs « CMD_NUM » et « PRD_REFERENCE ».
Si l’on suit strictement l’idée de créer un index sur chaque clé étrangère, on en créera un sur le champ « CMD_NUM » de la table « Composée ». Or celui-ci est déjà indexé par la clé primaire de cette même table. Un index supplémentaire est donc inutile.

2) Indexation des colonnes les plus recherchées
Encore une idée qui semble séduisante, mais ne l’est en réalité pas systématiquement puisque certaines recherches pourront activer la recherche dans l’index (on dit alors que le prédicat est sargable – vient de « Search ARGument » – ).

Exemple :
Prenons la requête suivante :
SELECT *
FROM T_CLIENT
WHERE CLI_NOM LIKE '%ONT'
AND SUBSTRING (CLI_PRENOM, 2, 2) = 'au'
AND MONTH (CLI_DATE_NAISSANCE) = 1
AND YEAR (CLI_DATE_NAISSANCE) = 1930

  • Si l’on indexe la colonne « CLI_NOM », cet index sera inutile puisqu’on cherche là les 3 dernières lettres or les données sont indexées dans l’ordre des lettres. La solution sera alors de créer une colonne calculée qui inversera les lettres et d’indexer cette colonne
  • L’indexation de la colonne « CLI_PRENOM » sera inutile, sensiblement pour les mêmes raisons puisqu’on recherche une partie d’une chaine de caractères.
  • L’indexation de la colonne « CLI_DATE_NAISSANCE » sera utile à condition de modifier la clause WHERE de notre requête. L’index sera efficace à condition de ré-écrire cette clause comme suit : AND CLI_DATE_NAISSANCE BETWEEN '1930-01-01' AND '1930-01-31‘

Par ailleurs, créer un index sur un nombre trop important de champs serait contre-productif.

3) Index multi colonne et longueur des clefs d'index
Un index multi colonne ne sera efficace que pour des recherches dans le sens du vecteur constitué par les colonnes dans l'ordre positionnel. Il est donc inefficace de créer un index multi colonne pour des requêtes qui filtrent alternativement sur l'une ou l'autre colonne exclusivement. Néanmoins lorsque l'on est en mesure de tirer bénéfice d'un index multi colonne, il est intéressant de bien choisir l'ordre des colonnes dans le vecteur.

En effet, on obtiendra une efficacité plus grande en posant en premier les colonnes ayant la plus forte dispersion.

Par exemple s'il faut créer un index sur le sexe, le prénom et le nom, il y a fort à parier que la meilleure combinaison sera nom + prenom + sexe (dans cet ordre précis).

Quant à la longueur de la clef d'index, c'est-à-dire au nombre d'octets qui compose l'information vectorisée, mieux vaut qu'elle soit la plus petite possible.

Globalement, on peut considérer qu’il vaut mieux éviter de dépasser quelques dizaines d’octets.

Maintenir ses index
Créer des index, est une bonne chose, encore faut-il en assurer la maintenance. En effet, à chaque insertion ou suppression de données dans la table, l’index est modifié.

  • Après plusieurs insertions, l’index peut être fragmenté : lors d’une modification de l’index, un split de page de données peut avoir lieu : si la page d’index est pleine et que le moteur doit insérer une ligne dans cette page, il split la page en deux pages de même longueur avant de faire l’insertion.
  • Lors d’une suppression de données, la suppression n’est que logique, la place n’est pas réellement supprimée (que ce soit dans la table ou dans l’index). L’index contient alors une ligne fantôme.

Pour ces raisons, il est important de prévoir des plans de maintenance des index : défragmentations régulières, reconstructions complètes de temps en temps prendront certes un peu de temps, mais les performances de la base seront nettement améliorées.

En résumé, une indexation intelligente des tables dès la conception de la base de données vous évitera bien des galères par la suite, une fois l’application en production. A condition bien sûr de penser à mettre en place une maintenance régulière des index créés.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Captcha *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.