160
Chapitre 6. Utilisation des index
D’un autre côté, tout ajout d’une ligne entraîne inévitablement une réorganisation physique de la table. L’ordre des lignes doit se maintenir, et cela a un coût.
L’ajout d’une ligne dont la clé clustered doit s’insérer à l’intérieur de l’index, peut provoquer une séparation (split) de page, et donc entraîner des écritures coûteuses du
moteur de stockage. Pour éviter ce coût de maintenance, il est fortement recommandé de créer un index clustered sur une colonne qui augmente de façon séquentielle (on dit aussi monotone), comme un auto-incrémental (IDENTITY) ou une
colonne d’horodatage. Pour la même raison, les mises à jour de la valeur de la clé
sont déconseillées. Pour ces raisons, on crée souvent l’index clustered sur la clé primaire de la table – d’ailleurs la clé primaire est par défaut créée clustered. Dans un
modèle de données bien conçu, à quelques rares exceptions près, la clé primaire est
immuable (on ne change pas un identifiant référencé dans des tables filles…).
L’index clustered est aussi préférable sur une colonne, ou un ensemble de colonnes,
unique. Nous avons vu qu’en interne, SQL Server doit unifier les clés de l’index clustered si elles ne le sont pas déjà, simplement parce que pour ordonner les lignes, il ne
peut tolérer de doublon. Créer un index clustered sur une clé non unique augmente
la taille de l’index. C’est un défaut avec lequel on va parfois composer, car les avantages de l’index clustered sont réels. Ceci dit, il ne faut pas sous-estimer l’importance
de conserver un index clustered aussi petit que possible. Comme sa clé est copiée
dans la clé de tous les autres index de la table, il influence la taille de tous les index,
et donc leur efficacité et leur utilité. Prenons un peu d’avance sur la section traitant
des statistiques, et faisons une expérience :
DROP INDEX cix$dbo_indexdemo$id
ON dbo.indexdemo;
CREATE CLUSTERED INDEX cix$dbo_indexdemo$texte
ON dbo.indexdemo (texte ASC);
GO
DBCC SHOW_STATISTICS ('dbo.indexdemo', 'nix$dbo_indexdemo$petittexte');
Nous supprimons l’index clustered sur notre table de test, et nous le recréons, mais
cette fois-ci sur la colonne texte, qui est de type CHAR(100). Voyons comment l’index
nix$dbo_indexdemo$petittexte, sur la colonne petittexte (CHAR(1)), a réagi. La
commande DBCC SHOW_STATISTICS nous permet d’obtenir des détails sur la structure
de l’index, qui sont ceux qu’utilise l’optimiseur SQL pour juger de sa pertinence pour
répondre à une requête. Voyons un extrait du résultat (nous n’avons gardé que les
lignes et colonnes utiles pour notre propos) :
Name
Rows
Density
Average key length
----------------------------------------------------------------------nix$dbo_indexdemo$petittexte 2007
0,1304348
100,0992
All density Average Length Columns
---------------------------------------------0,03703704
0,09915297
petittexte
0,003496503 100,0992
petittexte, texte
Chapitre 6. Utilisation des index
D’un autre côté, tout ajout d’une ligne entraîne inévitablement une réorganisation physique de la table. L’ordre des lignes doit se maintenir, et cela a un coût.
L’ajout d’une ligne dont la clé clustered doit s’insérer à l’intérieur de l’index, peut provoquer une séparation (split) de page, et donc entraîner des écritures coûteuses du
moteur de stockage. Pour éviter ce coût de maintenance, il est fortement recommandé de créer un index clustered sur une colonne qui augmente de façon séquentielle (on dit aussi monotone), comme un auto-incrémental (IDENTITY) ou une
colonne d’horodatage. Pour la même raison, les mises à jour de la valeur de la clé
sont déconseillées. Pour ces raisons, on crée souvent l’index clustered sur la clé primaire de la table – d’ailleurs la clé primaire est par défaut créée clustered. Dans un
modèle de données bien conçu, à quelques rares exceptions près, la clé primaire est
immuable (on ne change pas un identifiant référencé dans des tables filles…).
L’index clustered est aussi préférable sur une colonne, ou un ensemble de colonnes,
unique. Nous avons vu qu’en interne, SQL Server doit unifier les clés de l’index clustered si elles ne le sont pas déjà, simplement parce que pour ordonner les lignes, il ne
peut tolérer de doublon. Créer un index clustered sur une clé non unique augmente
la taille de l’index. C’est un défaut avec lequel on va parfois composer, car les avantages de l’index clustered sont réels. Ceci dit, il ne faut pas sous-estimer l’importance
de conserver un index clustered aussi petit que possible. Comme sa clé est copiée
dans la clé de tous les autres index de la table, il influence la taille de tous les index,
et donc leur efficacité et leur utilité. Prenons un peu d’avance sur la section traitant
des statistiques, et faisons une expérience :
DROP INDEX cix$dbo_indexdemo$id
ON dbo.indexdemo;
CREATE CLUSTERED INDEX cix$dbo_indexdemo$texte
ON dbo.indexdemo (texte ASC);
GO
DBCC SHOW_STATISTICS ('dbo.indexdemo', 'nix$dbo_indexdemo$petittexte');
Nous supprimons l’index clustered sur notre table de test, et nous le recréons, mais
cette fois-ci sur la colonne texte, qui est de type CHAR(100). Voyons comment l’index
nix$dbo_indexdemo$petittexte, sur la colonne petittexte (CHAR(1)), a réagi. La
commande DBCC SHOW_STATISTICS nous permet d’obtenir des détails sur la structure
de l’index, qui sont ceux qu’utilise l’optimiseur SQL pour juger de sa pertinence pour
répondre à une requête. Voyons un extrait du résultat (nous n’avons gardé que les
lignes et colonnes utiles pour notre propos) :
Name
Rows
Density
Average key length
----------------------------------------------------------------------nix$dbo_indexdemo$petittexte 2007
0,1304348
100,0992
All density Average Length Columns
---------------------------------------------0,03703704
0,09915297
petittexte
0,003496503 100,0992
petittexte, texte