MySQL: faire un index composé de 3 champs, ou de faire 3 des indices distincts?

J'ai une table MySQL qui a, entre autres attributs, un horodatage, un type et un user_id.
Tous d'entre eux sont consultables et/ou sortable.
Est-il préférable de créer un index pour chacun d'eux, ou de créer un seul index composé avec tous les trois, ou les deux?

OriginalL'auteur Petruza | 2010-11-16

  1. 8

    Pablo réponse est bonne, mais peut-être que vous ne parviennent pas à réaliser que d'un index composé pourrait être justifiée.

    Vous pouvez avoir plusieurs index et avoir idx1(tstamp, user_id) n'exclut pas d'avoir indx2(tstamp, type) ou idx1reverse(user_id, tstamp) et ainsi de suite...

    Index composés sont plus utiles lorsqu'ils couvrent l'ensemble des conditions dans votre requête, de sorte que l'indice que vous proposez sera plus utile pour

    SELECT * FROM my_table WHERE tstamp = @ts1 AND user_id = @uid AND type = @type

    Si vous voulez améliorer les performances de ces requêtes, vous pouvez envisager l'ajout d'un indice composite.

    Le revers de l'index, c'est qu'il ralentit toutes les opérations de mise à jour. Cependant, la plupart des applications générales de faire beaucoup plus d'sélectionne ensuite les mises à jour (à la fois en termes de transactions, à savoir le nombre d'instructions et en particulier dans les conditions d'enregistrements affectés/(extrait) et en même temps sont beaucoup plus tolérants d'un ralentissement des mises à jour (les utilisateurs pour la plupart juger de la vitesse du système, non pas par le temps, il est nécessaire de mettre à jour un enregistrement, mais par le temps nécessaire pour récupérer des documents; une fois encore, YMMV et il y a des applications qui ne respectent pas ces règles).

    Le mieux serait si vous aviez une façon de tester les performances des bases de données en termes de charges de travail typiques (créer quelques exemples de scripts SQL; indépendant et reproductibles, ou de créer des tests unitaires au niveau de l'application) et puis vous pouvez objectivement de régler votre base de données.

    MODIFIER
    Aussi se rendre compte que les index peuvent être ajoutés et supprimée sans affecter le système en termes de fonctionnalité. Par conséquent, vous pouvez affiner votre index plus tard, lors de l'utilisation réelle du système et normalement vous devez recueillir et de profil de la lenteur des requêtes SQL à la recherche de conditions qui pourraient bénéficier de l'ajout d'index.

    OriginalL'auteur Unreason

  2. 9

    Si vous envisagez d'effectuer des recherches sur ces champs séparément, vous aurez probablement besoin d'indices distincts pour rendre vos requêtes s'exécutent plus rapidement.

    Si vous avez un indice comme ceci:

    mysql> create index my_idx on my_table(tstamp, user_id, type);

    Et vous question est:

    mysql> select * from my_table where type = 'A';

    Puis my_idx ne sera pas vraiment utile pour votre requête et MySQL va finir par faire un full table scan pour le résoudre.

    ...et sera toujours utile pour les requêtes telles que select * from my_table where tstamp = @ts1
    Oui, il pourrait aider à cette requête. Mais ne sera pas aussi utile comme un indice uniquement sur cette colonne.
    oui, en réalité, il sera aussi utile que l'index uniquement sur cette colonne. Il pourrait être plus lent en raison du fait que l'index est plus grand - toutefois, si l'indice sur une seule colonne serait utile (c'est à dire une haute sélectivité) puis de l'indice composite sera aussi utile - mysql peut regarder à travers un index de plus grande taille, mais btree les index sont organisés comme des arbres, et donc l'augmentation de la taille, combiné avec le fait que seule une partie de l'arbre doit être visité, combiné avec le fait que les opérations d'e/S se produire dans les tailles de bloc, se traduit par: index avec la même colonne sont également utiles pour les conditions sur cette colonne.
    grand commentaire.
    et en outre, vermifugés de la base de données permettra de garder les index dans la RAM, donc réduit la différence de performances encore plus loin (ici, en supposant que le composé de l'indice est justifiée sur son propre). En d'autres termes - d'habitude lors de l'introduction de composés indices, j'ai tendance à tomber colonne unique indices de la première colonne.

    OriginalL'auteur Pablo Santa Cruz