Deux index à colonne unique ou un index à deux colonnes dans MySQL ?

Question

Je suis confronté à la situation suivante et je ne sais pas quelle est la meilleure pratique.

Examinez le tableau suivant (qui va devenir volumineux) :

id PK | giver_id FK | recipient_id FK | date

J'utilise InnoDB et d'après ce que j'ai compris, il crée automatiquement des index pour les deux colonnes de clé étrangère. Cependant, je vais également faire beaucoup de requêtes où je dois faire correspondre une combinaison particulière de :

SELECT...WHERE giver_id = x AND recipient_id = t .

Chacune de ces combinaisons sera unique dans le tableau.

Y a-t-il un avantage à ajouter un index à deux colonnes sur ces colonnes, ou les deux index individuels seraient-ils en théorie suffisants / identiques ?

Answer 1

Si vous avez deux index à colonne unique, seul l'un d'entre eux sera utilisé dans votre exemple.

Si vous avez un index avec deux colonnes, la requête peut être plus rapide (vous devriez mesurer). Un index à deux colonnes peut également être utilisé comme un index à une seule colonne, mais uniquement pour la colonne listée en premier.

Il peut parfois être utile d'avoir un index sur (A,B) et un autre sur (B). Cela rend les requêtes utilisant l'une ou l'autre des colonnes, ou les deux, plus rapides, mais utilise bien sûr plus d'espace disque.

Lors du choix des index, vous devez également tenir compte de l'effet sur l'insertion, la suppression et la mise à jour. Plus d'index = mises à jour plus lentes.

Answer 2

Un index de couverture comme :

ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);

...signifierait que l'index pourrait être utilisé si une requête se référait à giver_id ou une combinaison de giver_id et recipient_id . Il faut garder à l'esprit que les critères d'indexation sont basés sur la gauche. recipient_id ne serait pas en mesure d'utiliser l'indice de couverture dans la déclaration que j'ai fournie.

Veuillez noter que certaines anciennes versions de MySQL ne peuvent utiliser qu'un seul index par SELECT, de sorte qu'un index couvrant serait le meilleur moyen d'optimiser vos requêtes.

Answer 3

Si l'un des index de clé étrangère est déjà très sélectif, le moteur de base de données doit l'utiliser pour la requête que vous avez spécifiée. La plupart des moteurs de base de données utilisent une sorte d'heuristique pour choisir l'index optimal dans cette situation. Si aucun des index n'est très sélectif en soi, il est probablement judicieux d'ajouter l'index construit sur les deux clés puisque vous dites que vous utiliserez souvent ce type de requête.

Il est également possible d'éliminer le champ PK dans cette table et de définir l'index de la clé primaire sur le champ giver_id et recipient_id champs. Vous avez dit que la combinaison est unique, donc cela pourrait fonctionner (sous réserve de nombreuses autres conditions auxquelles vous seul pouvez répondre). En règle générale, cependant, je pense que la complexité supplémentaire qui en découle ne vaut pas la peine d'être exploitée.

Answer 4

Il faut également tenir compte du fait que les caractéristiques de performance des deux approches dépendent de la taille et de la cardinalité de l'ensemble de données. Il se peut que l'index à deux colonnes ne devienne nettement plus performant qu'à partir d'un certain seuil de taille de l'ensemble de données, ou exactement l'inverse. Rien ne peut remplacer les mesures de performance pour votre scénario exact.