Discussion :

[looping]

Bonjour,

Je me suis pas mal documenté sur les index.
J'aurais une question sur les colonnes d'une table qui sont clé étrangères.

A priori, de ce que j'ai compris, il faudrait indexer toutes les colonnes d'une table qui sont clé étrangères.
Mais est ce que c'est bien utile si la table "étrangère" contient peu de données ?

J'ai des petites table (civilité, pays, moyen de paiement, banques, etc..) qui contiennent moins de 100 enregistrements.
Est ce que l'index est pertinent dans ce cas ?


Cordialement

[tatayo]

Bonjour,
En fait ça dépend.
Si on prend par exemple la table des pays, je doute qu'il soit nécessaire d'indexer son identifiant, car le nombre de lignes sera assez faible (moins de 200).
Mais de l'autre côté, est-ce qu'il faut indexer cet identifiant dans la table des adresses, si celle-ci contient des centaines de milliers de lignes, voir plusieurs millions ?

Si la grande majorité des adresses sont dans un même pays, et qu'il n'y a pas/peu de recherche sur le code pays, je doute fort qu'un index soit pertinent.
A l'inverse, si les adresses sont réparties de façon plus ou moins équitable sur les différents pays, et qu'il y a beaucoup de recherche sur ces codes pays, un index peut être pertinent.

Tatayo.

[escartefigue]

bonjour,

Sur le plan des recherches, un index n'est utile que s'il est filtrant. Le but est de faciliter les recherches lors des lectures et des mises à jour pour ne parcourir qu'une petite partie des éléments de la table.
Selon les SGBD soit seul l'index cluster possède un pointeur vers les pages data, soit chaque index possède son pointeur vers les data.
Dans le premier cas, une recherche par l'index nécessite de passer par l'index correspondant aux critères, puis pas l'index cluster pour enfin atteindre les lignes de données, dans le deuxième cas, on passe directement de l'index aux données.

Parcourir ce chemin prend évidemment du temps, or, si l'index correspondant aux critères de recherche représente un gros pourcentage des données à atteindre, on perd plus de temps à faire des allers et retours entre index et données que de parcourir séquentiellement les données.

Un exemple que j'utilise souvent est celui d'une table des personnes contenant des hommes et des femmes. Si l'on crée un index sur le code sexe, chaque d'index représentera environ 50% de l'effectif de la table.
Passer par cet index n'a donc aucun intérêt puisque aller de l'index aux données pour 50% de l'effectif de la table prendra plus de temps que de tout lire.
En général, si une valeur représente plus de 10% de l'effectif, l'index n'est pas éligible pour les recherches.

Donc non, on ne crée surtout pas d'index systématique sur les FK, on ne le fait que si c'est filtrant et qu'il y a un besoin de recherche sur la ou les colonnes de cette FK.

Un index peut aussi être utilisé parce qu'il est couvrant : si toutes les colonnes d'une requête sont contenues dans un index, alors, même si cet index n'est pas filtrant, il peut être utile. La requête pourra parcourir séquentiellement cet index sans accéder aux données.

[looping]

Bonjour,

Merci pour toutes ces réponses.

J'ai compris maintenant que pour qu'un index soit utile, il faut:
- qu'il y ait des requêtes sur les colonnes concernées.
- que cet index soit assez sélectif , et qu'il permette de filtrer.

Je vais faire le ménage !
Par exemple, j'ai mis un index sur la colonne "devise" d'une table de vente (relatif a une table devise) .
98 % des ventes utilise l'Euro, donc pas besoin d'index

En général, si une valeur représente plus de 10% de l'effectif, l'index n'est pas éligible pour les recherches.

Est ce que pour évaluer ce 10% il y des routines existantes, ou des outils dans PGadmin ou Dbeaver ?
ou bien i faut des requêtes pour chaque table ?

Cordialement

[escartefigue]

Pour chaque requête, on peut faire un EXPLAIN pour vérifier son plan d'exécution et quels sont les index utilisés.
Sachant que le chemin dépend des valeurs, ce n'est souvent pas le même chemin d'accès en prod et en test ou en recette, les cardinalité et les valeurs étant différentes.
Il faut aussi chasser les index redondants.
Par exemple, soit ces index sur une même table :
I1 = Col1 asc, Col2 asc
I2 = Col1 asc, Col2 desc
I3 = Col1 asc

I3 est redondant avec I1 et I2, il peut être supprimé
I2 n'est pas redondant, mais il faut vérifier s'il est pertinent d'avoir deux index uniquement pour trier en ascendant et en descendant Col2, donc vérifier les utilisations de I1 et I2 et droper l'index inutile s'il y a lieu.

[looping]

OK merci pour les info

Tout une science ces index !!

J'ai du travail d'analyse à prévoir ....


Cordialement

[SQLpro]

...
J'ai compris maintenant que pour qu'un index soit utile, il faut:
- qu'il y ait des requêtes sur les colonnes concernées.
- que cet index soit assez sélectif , et qu'il permette de filtrer.

En fait plus complexe que cela...
Dans une commande SELECT, un index de type BTree sera toujours utilisé quelques soit les circonstances, si :

• la colonne figure dans l'index et que seule cette colonne de la table est utilisé dans la requête partout ailleurs.
• la colonne dans la clé d'index et toutes les autres colonnes utilisées dans la requête pour cette même table figure dans l'index (index multicolonne ou clause INCLUDE de l'index)
• la colonne figure en tête de la clé d'index, que cette colonne de tête est utilisé en comparaison ou jointure avec un prédicat "cherchable" (=, >, >=, <, <=, LIKE 'motif%'), à condition que la "surface" des données au final (nombre de ligne X longueur des lignes) estimé préalablement par l'optimiseur (appelé stupidement "planeur" dans PostGreSQL) soit moindre que tout autre solution (l’estimation étant faite à partir de statistiques de distribution des données)
• ...

Il existe de nombreux autres cas du fait que ce que vous écrivez comme requête n'a rien à voir avec ce qui sera exécuté en interne par le SGBDR...

Est ce que pour évaluer ce 10% il y des routines existantes, ou des outils dans PGadmin ou Dbeaver ?
ou bien i faut des requêtes pour chaque table ?

C'est l'optimiseur (stupidement appelé "planer") qui décide en fonction des statistiques qu'il détient sur la distribution des données de cette colonne.

Vous pouvez voir ces statistiques (SELECT * FROM pg_stats WHERE tablename = '???', mais c'est assez incompréhensible par rapport aux autres SGBDR comme SQL Server ou ces dernières sont en clair....



Distribution statistique des données de l'index X ('MS SQL Server) portant sur le nom de famille dans une table des personnes

[SQLpro]

Pour continuer cette discussion, voici ce que j'ai dit sur StackOverflow au sujet de l'indexation des FK :

"
Premièrement, il n'y a pas d'indexation automatique de FOREIGN KEY dans les SGBDR sauf MySQL, ce qui est un comportement stupide.

Deuxièmement, dans certains cas, l'indexation d'un FK crée un index redondant inclus, notamment lorsque :

• la table est une table associative
• la table provient d'un design hérité

Troisièmement, indexer systématiquement FK n'est pas une bonne pratique, sauf si toutes vos requêtes ne sont qu'une jointure sans qu'aucune autre colonne ne soit utilisée dans une autre partie de la requête pour la table enfants.

Quelques explications...

POINT 2

Premier exemple, table associative*:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TABLE Orders (ord_id int PRIMARY KEY, ...)
CREATE TABLE Product (prd_id int PRIMARY KEY, ...)
CREATE TABLE order_details (ord_id int NOT NULL REFERENCES Orders (ord_id), prd_id int NOT NULL REFERENCES Product (prd_id), PRIMARY KEY (ord_id, prd_id), ...)

Ajouter un index sur ord_id pour la table order_details est stupide, car la PRIMARY KEY a déjà un index (ord_id, prd_id) qui peut être utilisé à la place du simple FK (ord_id).

Deuxième exemple, table héritée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE TABLE vehicles (vhc_id int PRIMARY KEY, ...)
CREATE TABLE vehicle_cars (vhc_id int PRIMARY KEY REFERENCES  vehicles (vhc_id , ...)

Ajouter un index sur vhc_id est encore plus stupide, car la PRIMARY KEY possède déjà un index (vh_idc) qui est strictement le même.

POINT 3

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE TABLE customers (ctm_id int PRIMARY KEY, ...)
CREATE TABLE Orders (ord_id int PRIMARY KEY, ctm_id int NOT NULL REFERENCES customers (ctm_id), ...)

L'ajout d'un index sur la colonne ctm_id dans la table des commandes ne sera utilisé que dans deux cas :

• l'index lui-même est suffisant pour récupérer toutes les données pour la jointure
• il y a quelques lignes renvoyées par la table des commandes

Exemple du sous-point 2.1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT ctm_name, COUNT(*)
FROM   customers AS c
       JOIN Orders AS o 
          ON c.ctm_id = o.ctm_id
GROUP BY ctm_name;

Exemple du sous-point 2.2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT *
    FROM   customers AS c
           JOIN Orders AS o 
              ON c.ctm_id = o.ctm_id
WHERE ctm_id = 123;

Dans toute autre requête, il y a de grandes « chances » que l'index FK ne soit pas utilisé en raison du coût d'utilisation de l'index en mode « recherche », puis en jointure de l'index à la table pour récupérer toutes les autres colonnes qui ne sont pas dans l'index, coût qui sera beaucoup plus important que de balayer les lignes de la table.

DONC, INDEXER SYSTÉMATIQUEMENT UNE CLÉ ÉTRANGÈRE EST, LA PLUPART DU TEMPS, STUPIDE !

N'oubliez pas que les index sont très coûteux en termes de transactions lorsque les données sont modifiées (INSERT, UPDATE, DELETE, MERGE, TRUNCATE...). Alors ne créez pas d’index inutiles !
D'autant plus de PostGreSQL ne pratique pas le parallélisme d'exécution, donc la mise à jour des données s'effectue d'abord dans la table puis dans tous les index séquentiellement.... Pour MS SQL Serve par contre c'est en parallèle (en fonction du nombre de cœurs de la machine).

Pour être clair, le meilleur choix pour chaque index est d'avoir un index COUVRANT, ce qui signifie que le seul et unique index lui-même est suffisant pour l'ensemble de la requête, y compris WHERE, ON (à partir des JOINs), HAVING, GROUP BY, ORDER BY et SELECT. Ceci étant grandement facilité par l'introduction de la clause INCLUDE pour les index dans certains SGBDR (SQL Server depuis la version 2008 et PostGreSQL depuis la version 11 en 2018)

Bien entendu, le choix de l’indexation doit résulter de l’exploitation de la base de données et non d’un dogme !

Certains SGBDR (Microsoft SQL Server depuis la version 2008) proposent systématiquement un diagnostic complet des index à créer, et il est très rare que des index concernant uniquement la colonne de clé étrangère soient signalés par le système de diagnostic... A titre d'exemple, cette requête pour Microsoft SQL Server :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM sys.dm_db_missing_index_details*;

... donne la liste de tous les index nécessaires à l'amélioration des performances des requêtes réellement exécutées depuis le démarrage de l'instance SQL Server et quelques détails supplémentaires, comme le gain potentiel, si l'index est créé...
"

Référence :
https://softwareengineering.stackexc...-good-practice

A +