Index
Documentation Générale Qualité
Documentation Classes Metier
Documentation Liste Qualité Code Review
Documentation Liste Qualité Guideline Development
Documentation Bugs Defaults
Documentation Tests Unitaires
Documentation i18n
Documentation SQL Indexation
Documentation Timeframe

Les index de tables, résumé

Explication générale        2

Méthode à suivre pour les index        2

Index multicolonnes ou composites        2

Exemple facile        3

Pour le reste il faut arbitrer        4

Sources/liens/Aller plus loin        4

Explication générale

Sans index Mysql est obligé de parcourir toutes les lignes. Plus la table est grande, plus c’est long.

Les index des tables servent à optimiser les performances des requêtes SQL. Dans un SGBDR, chaque milliseconde gagnée peut se transformer en minute ou en heure en fonction du nombre d’utilisateurs et donc du nombre de fois ou une requête similaire est envoyée au serveur. Les index jouent tout leur rôle ici.

D’un autre côté, comme tout index coûte en temps de traitement, un index inutile est contre-performant. Trop d’index et les mauvais index tuent les performances.

Les index doivent donc être créés en fonction des requêtes qui les utilisent.

Parfois les index sont évidents (voir “exemple facile” plus bas), parfois il faut arbitrer ou optimiser la conception du programme pour mieux utiliser les index.

Il est inutile de se précipiter sur la création d'index sur chacune des colonnes figurant dans une clause WHERE. En effet, certaines expressions de recherche pourront activer la recherche dans l'index, d'autres pas.

Enfin, certains index sont créés par défaut lors de la pose des contraintes PRIMARY KEY et UNIQUE.

Méthode à suivre pour les index

• Créer la table sans les index et la reporter dans divers/Hightekers.sql
• Créer les requêtes nécessaires (et éventuellement ajouter les index s’ils vous paraissent évidents)
• Une fois les requêtes stabilisées, faites une analyse :
• Recherche utilisation de la table, les searchs et tout ce qui est “where “(updates, delete) avec une recherche sur le nom de la table dans le code source.
• Vous pouvez utiliser aussi la fonction Explain de Mysql qui permet d’optimiser les requêtes et vous donne des informations sur l’usage des index. Pour vous aider à obtenir les requêtes depuis le manager, vous pouvez afficher les requêtes issues du manager concerné à l’aide de méthodes spécifiques (cf le chapitre “Débogage” de DOCMANAGER).
• Créer les index en fonction de votre analyse sous la forme CREATE INDEX `WDIDX_modeles_clients_societe_IDclients_IDsocietes` ON `modeles_clients_societe` (`IDclients`,`IDsocietes`);

Index multicolonnes ou composites

Un index peut porter sur plusieurs colonnes.

Notez que si vous avez un index composite sur (c1,c2,c3), vous disposerez de fonctionnalités de recherche indexées sur l'une des combinaisons de colonnes suivantes :

(c1)

(c1,c2)

(c1,c2,c3)

L’ordre des colonnes de la clef (que l’on appelle vecteur) revêt une importance particulière. En effet un index composé des colonnes A, B, C est totalement différent d’un index créé sur les colonnes B, C et A.

Un index multicolonne 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 multicolonne 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 multicolonne, 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).

En fait, la dispersion des données est plus forte pour un nom qu'un prénom (il y a moins de prénoms différents que de noms de famille différents) et bien plus encore par rapport au sexe qui ne présente généralement que deux valeurs.

Exemple facile

-- création de la table modeles_clients_societe

CREATE TABLE `modeles_clients_societe`

(

    `IDmodeles_clients_societe` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

    `IDclients`                 INT NOT NULL,

    `IDmodeles_factures`        INT NULL,

    `IDsocietes`                INT NOT NULL

) ENGINE = MyISAM;

Les requêtes utilisant la table :

Dans cet exemple, seules 2 colonnes sont utilisées et elles le sont systématiquement. L’index doit être sur ces 2 colonnes, on aura donc : CREATE INDEX `WDIDX_modeles_clients_societe_IDclients_IDsocietes` ON `modeles_clients_societe` (`IDclients`,`IDsocietes`);

Pour le reste il faut arbitrer

Les cas sont multiples. Il convient d’analyser la situation et de suivre les règles ci-dessus et les règles d’indexation multicolonnes. Plus les cas paraissent complexes à la conception (exemples le CRA ou bourses missions), plus il faut se poser des questions sur les tables et l’indexation en amont du développement.

Si on arrive à des requêtes menant des à des indexations contradictoires, c’est peut-être le signe qu’il y a un problème de conception des tables.

Ajouter à la procédure qualité test & review, un lien vers cette doc

Sources/liens/Aller plus loin

https://sqlpro.developpez.com/cours/quoi-indexer/#LVII 

https://www.mysqltutorial.org/mysql-index/mysql-composite-index/

https://blog.developpez.com/sqlpro/p9816/langage-sql-norme/tout_sur_l_index