Contrairement à une idée communément répandue, l’absence de toute clef ou index est particulièrement contre performant à tous les niveaux dans les tables d’une base de données….

En théorie une relation (terme consacré pour la table dans la monde relationnel) sans clef n’existe pas. En pratique, une table sans clef est une aberration. En effet les bases de données relationnelles ont été créées pour gérer des relations (qui sont des objets mathématique porteur de données) devant avoir OBLIGATOIREMENT une clef, et non de vulgaires fichiers « Ã  la Cobol ».

La nécessité d’une clef

En fait, les SGBDR sont spécialement conçus à cet effet.
Tous les systèmes de gestion de bases de données relationnelles, partent du principe que toute table possède un clef (dite primaire) afin d’en optimiser les accès. En l’absence de clef, la lecture de votre table sera toujours lente, car elle sera lue à la manière d’un fichier, donc séquentiellement, c’est à dire par balayage de toutes les lignes.
L’ajout d’un index dans une table sans clef, améliore les choses, mais créée de la redondance, donc plus de volume.

Derrière chaque clef primaire ou unique, le SGBDR créé un index.

Clef primaire et index CLUSTERED

Pour beaucoup de SGBDR (dont SQL Server), la création d’une clef primaire, entraîne la création d’un index de type CLUSTERED (voir http://blog.developpez.com/sqlpro/p5930/langage-sql-norme/index-cluster-qu-est-ce-que-c-est/), c’est à dire que le SGBDR organise les données de la table en triant les lignes dans le sens de la clef d’index (la clef de l’index étant la ou les colonnes qui participent à la définition de l’index).
Bien entendu il ne peut y avoir qu’un seul index CLUSTERED puisque l’index CLUSTERED est la table elle même.
La taille de l’index est donc la taille des données de la table plus quelques pages techniques de navigation (pages de recherches dans l’index).

En créant une clef primaire sur l’ensemble des colonnes qui vous intéresse le plus, vous aurez donc tous les avantages et le moins d’inconvénients.
Cela dit, si l’insertion des lignes est aléatoire, alors la table CLUSTERED risque d’être fragmentée.
C’est pourquoi on conseille de réserver l’index CLUSTERED pour des données monotones au sens mathématique du terme, c’est à dire une série de valeurs allant croissante ou décroissante.
Dans le cas contraire il est intéressant de prévoir un FILL FACTOR, c’est à dire de réserver de l’espace dans les pages de données afin que de nouvelles valeurs arrivant au hasard ne provoquant pas des « splits » de pages et donc de la fragmentation (pour les splits de page et la fragmentation, voir http://sqlpro.developpez.com/optimisation/MaintenanceIndex/MaintenanceIndexVLDB.pdf en particulier § 2.1).

Les index secondaires

Un index secondaire est un index autre que l’index de clef primaire. Il peut avoir été créé implicitement par le SGBDR si vous avez posé une contrainte UNIQUE ou explicitement par un ordre pseudo SQL « CREATE INDEX ».
Chaque nouvel index créé sur une table, et qui n’est pas CLUSTERED, nécessite de retrouver la ligne d’où l’information a été puisée. Un index non CLUSTERED impose une redondance de l’information. On y trouve les données de l’index, mais aussi un donnée permettant de repérer de quelle ligne l’information provient. Pour cela on enregistre avec les données de l’index l’information de situation de la ligne. Deux cas se présentent alors :
* soit la table possède un index CLUSTERED et c’est la valeur de la clef de cet index qui est ajouté à l’index secondaire.
* soit la table n’a pas d’index CLUSTERED et dans ce cas, le repérage de la ligne d’origine au sein des structures de stockage d’une base de données se fait à l’aide d’un identifiant composé de :
. . . * le n° du fichier de stockage (une base pouvant avoir jusqu’à 65536 fichiers)
. . . * le n° d’une page dans le fichier (un fichier pouvant avoir 2147483648 pages)
. . . * le n° de « slot » de ligne (emplacement de la ligne dans la page, au plus 8000).
En général l’absence d’un index CLUSTERED s’avère plus couteux à bien des niveaux, d’autant plus que si les lignes sont appelées à bouger physiquement (défragmentation des espaces morts) alors tous les index secondaires seront impactés, ce que la présence de l’index CLUSTERED évite !

Index et volume de données

Comprenez que la présence d’un index est la plupart du temps d’une absolu nécessité et que le volume des index, n’est pas un problème et que surtout plus une table comporte de nombreuses lignes, plus l’indexation (ou la non indexation) se fera sentir.
Oui, les index ajoutent du volume à la base. Pour ma part je considère que dans une bonne base, bien modélisée et bien indexée, le volume des index doit se situer entre 30 et 50% du volume global de la base de données.
Pour une base bien indexées, le volume global n’a pas d’importance puisque peu de données sont lues (les index économisent drastiquement les données à manipuler et les lectures se font en mémoire).
Théoriquement le ration lecture directe des tables par balayage (SCAN) et recherche dans un index (SEEK) devrait être de 80% à 20%, voire mieux…

Les index ont un coût

Outre le coût de stockage il est indéniable que chaque index posé entraîne un surcoût dans certaines opérations, notamment de mise à jour. Cependant, les gains en lecture sont si important (les gains sur de grosses bases sont des rapports minimum de 1 à 1000, voire bien au dela… lire à cet égard l’étude sur l’indexation http://sqlpro.developpez.com/optimisation/indexation/) que les pertes en mise à jour sont tout à fait négligeables.

Coût de la mise à jour

Pour l’insertion de nouvelles lignes, un index cluster possède un surcoût que l’on peut estimer en gros entre 25% et 33% de plus que l’insertion de la ligne sans clef.

Pour la modification des données d’une ligne (UPDATE), le surcoût est le même, mais comme il faut repérer la ligne dans l’espace de stockage, la présence d’un index évite le coût phénoménal d’une recherche en balayage de toutes les lignes (SCAN).

Pour la suppression d’une ligne (DELETE), le surcoût minime (de l’ordre de 5 à 15%), mais, de la même manière que pour l’update, la présence d’un index évite le coût dramatique d’une recherche en balayage de toutes les lignes (SCAN).

On peut donc dire les choses suivantes :
Chaque index augmente globalement d’un tiers le coût d’insertion d’une ligne, mais permet de retrouver beaucoup plus rapidement la ou les lignes à impacter que ce soit pour de la lecture, comme pour l’écriture des donées.

Conclusion

Autrement dit et contrairement à certaines idées hélas très répandues, les performances seront bonne à excellente, si toutes vos tables sont dotées des bons index.
La seule exception à cette règle est pour les petites tables, par exemple celles pour lesquelles le volume des données ne représente pas plus d’une page. Mais même dans ce dernier cas, la présence d’index ou non ne permettra pas de voir la différence, et surtout petite table peut devenir grosse !

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *