Quelle est la différence entre mysql unique et non-unique index en termes de performances? Supposons que je souhaite créer un index sur une combinaison de 2 colonnes et que la combinaison soit unique, mais que je crée un index non unique. Cela aura-t-il un effet significatif sur les performances ou la mémoire utilisée par mysql? Même question, existe-t-il une différence entre la clé primaire et l'index unique?
Réponses
Trop de publicités?
UNIQUE et CLÉ PRIMAIRE sont contraintes, pas d'index. Bien que la plupart des bases de données de mise en œuvre de ces contraintes par l'utilisation d'un index. La charge supplémentaire de la contrainte en plus de l'index est négligeable, surtout quand on compte le coût de la traque et de la correction des doublons involontaires quand (pas si) ils se produisent.
Les index sont généralement plus efficace si vous avez une haute sélectivité. C'est le rapport du nombre de valeurs distinctes pour le nombre total de lignes.
Par exemple, dans une colonne de Numéro de Sécurité Sociale, vous pouvez avoir 1 million de lignes, avec 1 million de valeurs distinctes. Si la sélectivité est 1000000/1000000 = 1.0 (bien qu'il existe des archives rares exceptions, du SSN sont destinés à être unique).
Mais une autre colonne de la table, le "genre" ne peut avoir deux valeurs distinctes de plus de 1 million de lignes. 2/1000000 = très faible sélectivité.
Un index UNIQUE ou une contrainte de CLÉ PRIMAIRE est la garantie d'avoir une sélectivité de 1,0, alors il sera toujours aussi efficace qu'un index peut être.
Vous avez demandé à propos de la différence entre une clé primaire et une contrainte unique. Principalement, c'est que vous ne pouvez avoir qu'une contrainte de clé primaire par table (même si cette contrainte est la définition comprend plusieurs colonnes), alors que vous pouvez avoir plusieurs contraintes unique. Une colonne avec une contrainte unique peut autoriser les valeurs Null, tandis que les colonnes dans les contraintes de clé primaire ne doit pas autoriser les valeurs Null. Sinon, la clé primaire unique et sont très similaires dans leur mise en œuvre et de leur utilisation.
Vous avez demandé dans un commentaire sur l'opportunité d'utiliser MyISAM ou InnoDB. Dans MySQL, ils utilisent le terme" moteur de stockage. Il y a des tas de différences subtiles entre ces deux moteurs de stockage, mais les principaux sont:
- InnoDB supporte les transactions, de sorte que vous pouvez choisir de restaurer ou de valider les modifications. MyISAM est effectivement toujours en mode autocommit.
- InnoDB applique des contraintes de clés étrangères. MyISAM ne pas appliquer ou même stocker les contraintes de clé étrangère.
Si ces fonctionnalités sont les choses dont vous avez besoin dans votre application, vous devez alors utiliser InnoDB.
Pour répondre à votre commentaire, il n'est pas simple. InnoDB est effectivement plus rapide que MyISAM dans des cas assez nombreux, donc, cela dépend de ce que votre application mélange de sélectionne, mises à jour, des requêtes simultanées, les index, la configuration de la mémoire tampon, etc.
Voir http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/ pour un très approfondie de la comparaison des performances des moteurs de stockage. InnoDB victoires sur MyISAM assez souvent que c'est clairement pas possible de dire l'un est plus rapide que l'autre.
Comme avec la plupart des performances liées à des questions, la seule façon de répondre à votre demande est de tester les deux configurations à l'aide de votre application et d'un échantillon représentatif de données, et de mesurer les résultats.
MarkR
Points
37178
Sur un index non unique qui arrive juste à être unique et d'un index unique? Je ne suis pas sûr, mais je ne crois pas beaucoup. L'optimiseur doit examiner la cardinalité de l'index et de l'utiliser (il sera toujours le nombre de lignes, pour un index unique).
Autant qu'une clé primaire est concerné, probablement un assez grand nombre, mais ça dépend du moteur que vous utilisez.
Le moteur InnoDB (qui est utilisé par de nombreuses personnes) toujours clusters lignes sur la clé primaire. Cela signifie que le PK est essentiellement combiné avec la ligne réelle de données. Si vous faites beaucoup de recherches par PK (ou en effet, l'analyse de la plage, etc), c'est une Bonne Chose, car cela signifie que vous n'aurez pas besoin d'aller chercher autant de blocs du disque.
Un non-PK index unique ne sera jamais cluster en InnoDB.
D'autre part, certains autres moteurs (MyISAM en particulier) n'a pas de cluster sur le PK, de sorte que la clé primaire est juste comme un normal index unique.
