Quelle est l'importance de l'ordre des colonnes dans les index?

Regarder un indice comme ceci:

Cols
  1   2   3
-------------
|   | 1 |   |
| A |---|   |
|   | 2 |   |
|--- | ---|   |
|   |   |   |
|   | 1 | 9 |
| B |   |   |
|   |---|   |
|   | 2 |   |
|   |---|   |
|   | 3 |   |
|--- | ---|   |

Voir comment la restriction de sur les Une première, en tant que votre première colonne permet d'éviter plus de résultats que la restriction sur votre deuxième colonne de la première? C'est plus facile si vous imaginez ce que l'index doit être parcouru à travers, colonne 1, colonne 2, etc...vous voyez que tailler la plupart des résultats dans le poing pass rend la 2ème étape que beaucoup plus rapide.

Un autre cas, si vous interrogé sur la colonne 3, l'optimiseur ne serait même pas utiliser l'index, car il n'est pas utile tout en réduisant les jeux de résultats. Quand vous êtes dans une requête, réduisant le nombre de résultats à traiter avant la prochaine étape signifie de meilleures performances.

Depuis que l'indice est également stocké de cette façon, il n'y a pas de retour en arrière à travers l'index pour trouver la première colonne lorsque vous interrogez sur elle.

En bref: Non, ce n'est pas pour le spectacle, il y a de vrais avantages de performance.

Que Remus dit que cela dépend de votre charge de travail.

Je voudrais aborder un aspect trompeur de la accepté de répondre si.

Pour les requêtes qui sont de l'exécution d'une égalité de recherche sur toutes les colonnes de l'index il n'y a pas de différence significative.

Ci-dessous crée deux tables et remplit avec des données identiques. La seule différence est que l'on a les clés ordonnées de la plus à la moins sélective et l'autre l'inverse.

CREATE TABLE Table1(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);
CREATE TABLE Table2(MostSelective char(800), SecondMost TINYINT, Least  CHAR(1), Filler CHAR(4000) null);

CREATE NONCLUSTERED INDEX MyINDX on Table1(MostSelective,SecondMost,Least);
CREATE NONCLUSTERED INDEX MyINDX2 on Table2(Least,SecondMost,MostSelective);

INSERT INTO Table1 (MostSelective, SecondMost, Least)
output inserted.* into Table2
SELECT TOP 26 REPLICATE(CHAR(number + 65),800), number/5, '~'
FROM master..spt_values
WHERE type = 'P' AND number >= 0
ORDER BY number;

En train de faire une requête sur deux tables...

SELECT *
FROM   Table1
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~';

SELECT *
FROM   Table2
WHERE  MostSelective = REPLICATE('P', 800)
       AND SecondMost = 3
       AND Least = '~'; 

... Deux d'entre eux utilisent un indice d'amende et deux sont donné exactement le même coût.

L'ASCII art dans l'acceptation réponse n'est pas, en fait, la façon dont les index sont structurés. Les pages d'index pour Table1 sont représentés ci-dessous (cliquez sur l'image pour l'ouvrir en taille réelle).

Les pages d'index contient des lignes contenant l'ensemble de la clé (dans ce cas, il est en fait une colonne clé supplémentaire ajouté pour l'identificateur de ligne, l'indice n'a pas été déclarée comme un être unique, mais qui peut être ignoré de plus amples informations à ce sujet peuvent être trouvés ici).

Pour la requête ci-dessus SQL Server ne se soucient pas de la sélectivité des colonnes. Il fait un binaire de recherche de la page racine et découvre que le Clé (PPP...,3,~ ) est >=(JJJ...,1,~ ) et < (SSS...,3,~ ) donc, il faut lire la page 1:118. Il fait ensuite une recherche binaire des entrées de clé sur cette page et localise la page feuille pour faire le trajet à.

Modifier l'index dans l'ordre de sélectivité n'a pas d'incidence sur le nombre de comparaisons clés de la recherche binaire ou le nombre de pages qui doivent être explorés pour faire une recherche d'index. Au mieux, il pourrait légèrement la vitesse de la comparaison clé elle-même.

Parfois, la commande la plus sélective de l'indice d'abord du sens, pour d'autres requêtes dans votre charge de travail même si.

E. g si la charge de travail contient les requêtes des deux formes suivantes.

SELECT * ... WHERE  MostSelective = 'P'

SELECT * ...WHERE Least = '~'

L'index ci-dessus ne sont pas de couverture pour l'un ou l'autre. MostSelective est suffisamment sélectif pour faire un plan avec une recherche et des recherches de la peine, mais la requête contre Least ne l'est pas.

Toutefois ce scénario (non couvrant recherche d'index sous-ensemble de la colonne principale(s) d'un indice composite) est possible qu'une seule classe de requête qui peut être aidé par un index. Si vous n'avez jamais fait de recherche par MostSelective sur son propre ou une combinaison de MostSelective, SecondMost et toujours à la recherche d'une combinaison de tous les trois colonnes, alors cet avantage théorique est inutile de vous.

À l'inverse des requêtes telles que

SELECT MostSelective,
       SecondMost,
       Least
FROM   Table2
WHERE  Least = '~'
ORDER  BY SecondMost,
          MostSelective 

Serait facilitée par la présence de l'ordre inverse du couramment prescrits - comme il se couvre de la requête, peut soutenir une recherche et renvoie les lignes dans l'ordre souhaité pour démarrer.

C'est donc souvent répété morceau de conseils, mais en plus c'est une heuristique sur les avantages potentiels pour autres requêtes - et il n'est pas un substitut pour en fait en regardant votre de la charge de travail.

Toutes les réponses sont fausses.

Sélectivité des différentes colonnes dans un indice composite ne pas question lors de la prise de la commande.

Voici le simple processus de pensée: de manière Efficace, un indice est la concaténation des colonnes concernées.

Donner cette explication, la seule différence est de comparer deux chaînes de caractères qui diffèrent plus tôt lieu de plus tard dans la chaîne. Ce n'est qu'une infime partie du coût total. Il n'y a pas de "premier passage /seconde passe", comme mentionné dans une Réponse.

Donc, quel ordre doit-il être utilisé?

1. Commencer avec la colonne(s) testé avec =, dans tout commande.
2. Puis virer de bord sur une colonne de plage.

Par exemple, le très-faible sélectivité de la colonne doit venir en premier dans cette:

WHERE deleted = 0  AND  the_datetime > NOW() - INTERVAL 7 DAY
INDEX(deleted, the_datetime)

La permutation de l'ordre dans l'indice aurait-il totalement ignorer deleted.

(Il y a beaucoup plus de règles pour la commande de colonnes.)