L'ordre des champs est-il important dans l'index multi-colonnes dans MySQL ?

Question

Je connais l'importance des index et comment l'ordre de jointure modifie les performances. J'ai beaucoup lu sur les index multi-colonnes mais je n'ai pas trouvé de réponse à ma question.

Je suis curieux de savoir si l'ordre dans lequel ils sont spécifiés est important si je fais un index multi-colonnes. Je suppose que non, et le moteur les traitera comme un groupe où l'ordre n'a pas d'importance. Mais je veux le vérifier.

Par exemple, depuis le site Web mysql (http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html)

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

Dans tous les cas, ce qui suit serait-il meilleur ou équivalent, et serait-il bénéfique ?

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (first_name,last_name)
);

Plus précisément :

INDEX name (last_name,first_name)

Comparaison

INDEX name (first_name,last_name)

Answer 1

Les deux index sont différents. Cela est vrai dans MySQL et d'autres bases de données. MySQL explique la différence dans la documentation .

Considérez deux index :

create index idx_lf on name(last_name, first_name);
create index idx_fl on name(first_name, last_name);

Les deux devraient s’appliquer également à :

where last_name = XXX and first_name = YYY

idx_lf est optimal pour :

where last_name = XXX
where last_name like 'X%'
where last_name = XXX and first_name like 'Y%'
where last_name = XXX order by first_name

idx_fl sera le mieux adapté aux situations suivantes :

where first_name = YYY
where first_name like 'Y%'
where first_name = YYY and last_name like 'X%'
where first_name = XXX order by last_name

Dans de nombreux cas comme celui-ci, il est possible d'utiliser deux index, mais l'un d'eux est optimal. Par exemple, pensez à utiliser la requête idx_lf :

where first_name = XXX order by last_name

MySQL peut utiliser idx_lf pour lire la table entière, puis filtrer après order by. Je ne pense pas que ce soit une option d'optimisation en pratique (pour MySQL), mais cela peut arriver dans d'autres bases de données.

Answer 2

Lorsque je parle d’index multi-colonnes, j’utilise une analogie avec un annuaire téléphonique. Un annuaire téléphonique est essentiellement un index avec le nom de famille en premier, puis le prénom. Ainsi, l'ordre de tri est déterminé par la « colonne » qui vient en premier. Les recherches sont divisées dans les catégories suivantes :

1. Si vous recherchez des personnes portant le nom de famille Smith, vous pouvez facilement les trouver car le livre est trié par nom de famille.

2. Si vous recherchez quelqu'un nommé John, l'annuaire téléphonique n'est pas utile car John est dispersé dans le livre. Vous devez parcourir tout le répertoire téléphonique pour les trouver.

3. Si vous recherchez des personnes avec un nom de famille spécifique Smith et un prénom spécifique John, ce livre vous aidera car vous trouverez les Smith triés ensemble et au sein des Smith, les Johns se trouvent également dans l'ordre trié.

Si vous aviez un annuaire téléphonique trié par prénom puis par nom, le tri de l'annuaire téléphonique vous aiderait dans les cas n°2 et n°3 ci-dessus, mais pas dans le cas n°1 .

Cela explique le cas de la recherche d'une valeur exacte, mais que se passe-t-il si vous recherchez par une plage de valeurs ? Supposons que vous souhaitiez rechercher toutes les personnes dont le prénom est John et dont le nom commence par « S » (Smith, Saunders, Staunton, Sherman, etc.). Les Johns sont triés par « J » dans chaque nom de famille, mais si vous souhaitez que tous les Johns dont tous les noms commencent par « S », les Johns ne sont pas regroupés. Ils sont à nouveau dispersés, vous devez donc rechercher tous les noms dont le nom de famille commence par « S ». Cependant, si l'annuaire téléphonique était organisé par prénom, puis par nom de famille, vous constateriez que tous les John seraient regroupés, puis au sein de John, tous les noms de famille « S » seraient regroupés.

L'ordre des colonnes dans un index multi-colonnes est donc certainement important. Un type de requête peut nécessiter un ordre spécifique des colonnes de l'index. Si vous avez plusieurs types de requêtes, vous aurez peut-être besoin de plusieurs index pour les aider, avec des colonnes dans un ordre différent.

Vous pouvez lire ma présentation Vraiment Comment concevoir un index pour en savoir plus, ou regarder la vidéo.