Discussion :

[olibara]

Bonjour

J'ai survolé le tuto de SQL Pro

http://sqlpro.developpez.com/cours/quoi-indexer/

Je pense qu'il reponds a une question que je me suis souvant posée

Si dans une table j'ai un champ Numero et en champ Date

Si je crée un index Numero, Date, est it utile d'avoir aussi un index Numero

Le tutorial dit assez clairement : NON ce n'est pas nécessaire
Ai-je bien compris ?
Il y aurait-il une exception a cette regle ?

Merci de votre savoir

[fsmrel]

Bonsoir olibara,


Si pour la table T vous avez créé un index X1 ayant pour clé le couple {Numero, Date}, un index supplémentaire X2 ayant pour clé {Numero} est parfaitement inutile. Cependant, si par exemple la clé primaire de la table T est {Numero}, les SGBD feignants vous forceront à créer X2, même si la table ne comporte qu’une ou deux lignes.

[olibara]

Merci fsmrel

Mais MS Qql cree automatiquement un index sur la Pk ...?
Non ?

[hmira]

Merci fsmrel

Mais MS Qql cree automatiquement un index sur la Pk ...?
Non ?

Oui, il y a toujours un index physique rattaché à la clé primaire. Il faut cependant distinguer si l’index est organisé en cluster ou non.

Une clé primaire est d’abord une contrainte. Pour gérer la contrainte de clé primaire, SQL Server créé automatiquement un index physique associé. De même qu’une contrainte d’unicité (CONSTRAINT … UNIQUE) est gérée physiquement par un index unique.
Lorsque la clé primaire (PRIMARY KEY) est créée avec la clause CLUSTERED, l’index associé à la clé primaire est un index cluster et pour une table donnée il ne peut y avoir au plus qu'un seul index organisé en cluster et auquel cas la table et l’index se confondent et ne font plus qu’un.

A+

[hmira]

Pour revenir à question initialement posée. Personnellement je serais entièrement d’accord avec et SQLPro et fsmrel a propos de l’affirmation selon laquelle :

Si l'index IXn est composée des colonnes C1, C2, ..Cn alors
Il est "généralement" inutile de créer un index IXp constitué des colonnes C1, C2,…Cp (où p < n et où l’ordre des colonnes et les clauses ASC ou DESC de chacune des colonnes Ci (i varie de 1 à p) de l'index IXp et de l'index IXn , sont exactement identiques)
FinSi

PS : je me suis permis de rajouter le mot "généralement" et tout est dans le mot "généralement" !

Si on ôte le mot "généralement", la démonstration formelle de l’assertion ci-dessus n’est chose aisée. En effet, pour cela, il faudra revenir à la structure interne des Indexes B-Tree et la façon (où l’algorithme interne) utilisé par l’optimiseur de requête pour déterminer le plan d’exécution le plus efficace pour extraire les données demandées, etc.. Et, personnellement, si on ôte le mot "généralement", j’aurais même tendance à conjecturer qu’elle serait fausse !

En effet, on peut facilement trouver des situations, certe très rares où l’utilisation de l’index IXp s’avère plus optimale. Ce serait le cas notamment si les colonnes Cp+1 … Cn sont très longues (exemple des CHAR(8000) ! oui cela peut surprendre, mais on peut imaginer le pire) et où la requête SELECT porte uniquement sur les colonnes Ci où i <= p (c.à.d cas où la lecture de l’index suffit, et il ne sera pas nécessaire d’aller lire d’autres données dans la table).
Exemple :

Code SQL :

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

1
2
3
4
5
SELECT C1, .. Cp
FROM Matable 
WHERE C1 = .. 
AND   C2 = .. 
AND   Cp = ..

Dans ce contexte, il n’est pas exclu que l'optimiseur privilégie l’index IXp au lieu de l'index IXn

Conclusion :
Il s’agit donc plus d’une règle de bonne pratique que d’un "théorème" démontrable et vérifiable quel que soit le contexte.

A+

[SQLpro]

Pour compléter ce qui vient d'être dit, il est possible de savoir quels sont les index inclus par la requête que j'ai posté ici :
http://blog.developpez.com/sqlpro/p9...s_index_anorma

A +

[fsmrel]

Bonjour,

Si l'index IXn est composée des colonnes C1, C2, ..Cn alors Il est "généralement" inutile de créer un index IXp constitué des colonnes C1, C2,…Cp (où p < n et où l’ordre des colonnes et les clauses ASC ou DESC de chacune des colonnes Ci (i varie de 1 à p) de l'index IXp et de l'index IXn , sont exactement identiques)
FinSi

Si on ôte le mot "généralement", la démonstration formelle de l’assertion ci-dessus n’est chose aisée

Pour ma part je ne souscris pas à l'emploi ici de l’adverbe « généralement », vous noterez que j’ai employé celui de « parfaitement », nuance...

Les index (au même titre que les techniques de hachage) sont du niveau quincaillerie, leur mission est de nous permettre d’accéder au plus vite aux données quand autrement les temps de réponse seraient insupportables. Incidemment, si les marchands de SGBD s’en servent par exemple pour garantir l’unicité des clés des tables, c’est qu’ils détournent les index de leur mission, mais ceci est une autre histoire.

En effet, pour cela, il faudra revenir à la structure interne des Indexes B-Tree et la façon (où l’algorithme interne) utilisé par l’optimiseur de requête pour déterminer le plan d’exécution le plus efficace pour extraire les données demandées, etc..

A supposer qu’il mette en œuvre des arbres pour les index, qu’il les équilibre ou pas, chaque marchand « plante » et entretient ses arbres dans son jardin (SGBD) comme il l’entend. Par ailleurs un SGBD est immergé dans le temps : par exemple entre la version 1 et la version 10 du SGBD, l’organisation interne d’un index a pu sensiblement évoluer du fait des progrès de la technologie ou des idées lumineuses des ingénieurs (qu'en sera-t-il avec la version 40 ? ) Comme disait François Ier : « Souvent femme varie, bien fol qui s’y fie »...

on peut facilement trouver des situations, certe très rares où l’utilisation de l’index IXp s’avère plus optimale. Ce serait le cas notamment si les colonnes Cp+1 … Cn sont très longues (exemple des CHAR(8000) !

Selon le SGBD et sa version, 8000 peut être une limite basse : si le SGBD compresse les 8000 caractères à 90% et à supposer que dans une version future il permette d’utiliser des pages d’un Mo ou bien plus pour les index, il resterait pas mal de place dans les pages consommées par ces index.

oui cela peut surprendre, mais on peut imaginer le pire) et où la requête SELECT porte uniquement sur les colonnes Ci où i <= p (c.à.d cas où la lecture de l’index suffit, et il ne sera pas nécessaire d’aller lire d’autres données dans la table).

En l’absence de l’index IXp, il ne sera pas non plus nécessaire d’accéder à la table puisque les données sont aussi contenues dans l’index IXn. Vous me rétorquerez qu’en lisant les pages de IXn, le SGBD encombrera plus la mémoire qu’en lisant les pages de IXp, mais on est alors encore ramené à des problèmes bassement matériels d’optimisation à une époque donnée, en relation avec le degré d'évolution de la quincaillerie, de la technologie, et qui n’ont rien à voir avec la démonstration de théorèmes par essence immatériels, immuables, intemporels.

Exemple :

Code SQL :

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

1
2
3
4
5
SELECT C1, .. Cp
FROM Matable 
WHERE C1 = .. 
AND   C2 = .. 
AND   Cp = ..

Dans ce contexte, il n’est pas exclu que l'optimiseur privilégie l’index IXp au lieu de l'index IXn

Si les deux index existent, un optimiseur normalement constitué privilégiera IXp, mais si cet index n’existe pas, IXn remplira parfaitement la mission.

Conclusion :
Il s’agit donc plus d’une règle de bonne pratique que d’un "théorème" démontrable et vérifiable quel que soit le contexte.

Il n’y a pas de théorème à démontrer puisqu’on est confronté au mariage de la carpe et du lapin, à savoir tricoter des êtres de nature mathématique (les relations, dont les tables SQL ne sont du reste que des approximations plus ou moins fidèles et peuvent malheureusement être des sacs (bags)) et des objets du rayon quincaillerie (les index), sujets à évoluer dans le temps et impliqués dans des « théorèmes » ne valant que pendant une certaine période... Ainsi, en suivant consciemment ou non la recommandation de Wittgenstein dans son avant-propos du Tractatus : « Sur ce dont on ne peut parler il faut garder silence », la norme SQL n'a pas pris parti, elle est restée muette comme une... carpe au sujet des index, je ne sache pas qu'on y trouve l'instruction CREATE INDEX (à moins d'un démenti de la part de SQLpro, plus au fait de l'évolution de la norme).

Quant aux bonnes pratiques, il est bon de ne pas oublier le précepte d’Ockham : « ne pas multiplier pas les entités au-delà du nécessaire », donc évitons de multiplier inutilement les index. Du point de vue concret et plus important, n’oublions pas que la multiplication des index est néfaste pour la performance des applications à l’occasion des opérations de mise à jour. Aussi faut-il être économe des ressources et ne mettre en œuvre des index que lorsque cela s’avère absolument nécessaire pour la performance des requêtes et du système, mais cela est du ressort du DBA.

[Rei Ichido]

Bonjour,



Pour ma part je ne souscris pas à l'emploi ici de l’adverbe « généralement », vous noterez que j’ai employé celui de « parfaitement », nuance...

Les index (au même titre que les techniques de hachage) sont du niveau quincaillerie, leur mission est de nous permettre d’accéder au plus vite aux données quand autrement les temps de réponse seraient insupportables. Incidemment, si les marchands de SGBD s’en servent par exemple pour garantir l’unicité des clés des tables, c’est qu’ils détournent les index de leur mission, mais ceci est une autre histoire.

Vous vous contredisez un peu dans la suite ... Car si tout ceci est une affaire de "plomberie" (un peu condescendant, mhh ?), il faut s'en tenir à la plomberie, justement. Donc, à ce qui se justifie d'un point de vue performance.
Et donc oui, si une personne a posé un index sur 10 colonnes dont 6 bien grosses, parce que ça optimise une requête bien lourde, je vais probablement poser un index "recouvert" qui ne prendrait que les 3 première colonnes et serait très, très nettement plus léger (et donc, performant).
J'accorde qu'il faut bien sûr peser le pour et le contre, en particulier la non profilération à tout va des index qui pèse sur les mises à jour, mais en tout état de cause, l'assertion munie de "parfaitement" me semble, dans le cadre des index et donc de la performance, tout à fait fausse.

[fsmrel]

D’accord Rei Ichido, en fait j’avais en tête la façon qu’à MySQL Workbench de définir d’office des index pour les clés étrangères, même quand c’est inutile, sujet sur lequel j’étais en train de plancher par ailleurs. Vous me direz que nous ne sommes pas dans le forum MySQL, mais je me sers de MySQL Workbench pour produire des scripts de création de tables, quel que soit le SGBD cible, y compris MS SQL Server !

Quoi qu’il en soit, prenons le cas des factures et des lignes de facture où l’on identifie les lignes relativement aux factures :

TABLE FACTURE

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE FACTURE
(
        FactureId         INT         NOT NULL
      , FactureNo         CHAR(10)    NOT NULL
      , FactureDate       DATE        NOT NULL
      ...
    , PRIMARY KEY (FactureId)
    , UNIQUE (FactureNo)
    ...
) ;

TABLE LIGNE_FACTURE

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE LIGNE_FACTURE
(
        FactureId         INT         NOT NULL
      , LigneFactureId    INT         NOT NULL
      , ProduitId         INT         NOT NULL
      , Quantite          INT         NOT NULL
      ...
    , PRIMARY KEY (FactureId, LigneFactureId)
    , FOREIGN KEY (FactureId) REFERENCES FACTURE
    ...
) ;

MySQL Workbench définit d’office un index pour chaque clé primaire et chaque clé alternative, soit, mais en plus il en définit un d’autorité pour la clé étrangère branchant LIGNE_FACTURE sur FACTURE, et cet index (que je vire sans état d’âme) est inutile, j’espère que vous en conviendrez...

Maintenant, si une clé primaire contient 10 colonnes et qu’un prototypage des performances montre qu’on a intérêt à mettre en œuvre un index ayant pour clé les 3 premières colonnes de cette clé primaire, alors bien sûr je reconnaîtrai que cet index est à mettre en oeuvre. Mon « parfaitement inutile » ne vaut en fait que lorsqu’on définit des index a priori, à la manière expéditive de MySQL Workbench.

[SQLpro]

...
Maintenant, si une clé primaire contient 10 colonnes et qu’un prototypage des performances montre qu’on a intérêt à mettre en œuvre un index ayant pour clé les 3 premières colonnes de cette clé primaire, alors bien sûr je reconnaîtrai que cet index est à mettre en oeuvre. Mon « parfaitement inutile » ne vaut en fait que lorsqu’on définit des index a priori, à la manière expéditive de MySQL Workbench.

D'où l'intérêt d'avoir des métriques d'utilisation des index, voir des diagnostic de pose d'index, comme le propose Oracle ou SQL Server....

A +