Inconvénient des performances de la clé composite composite dans MySQL

Nous avons une table avec une clé primaire composite composée de trois champs (et elle est en MySQL 5.1). Il y a près de 200 insertions et 200 sélections par seconde sur cette table, et la taille de la table est d’environ 1 million de lignes et elle augmente.

Ma question est la suivante: la “clé primaire composite” diminue-t-elle les performances des insertions et des sélections sur cette table?

Dois-je utiliser un simple champ d’ID à augmentation automatique au lieu d’une clé primaire composite? (Je pense que la réponse est très liée à la façon dont MySQL gère les index sur plusieurs colonnes)

INSERT performances INSERT et UPDATE varient peu: il en sera presque de même pour les clés (INT) et (INT, INT) .

SELECT performance SELECT de la PRIMARY KEY composite dépend de nombreux facteurs.

Si votre table est InnoDB , la table est implicitement groupée sur la valeur PRIMARY KEY .

Cela signifie que les recherches pour les deux valeurs seront plus rapides si les deux valeurs constituent la clé: aucune recherche de clé supplémentaire ne sera requirejse.

En supposant que votre requête ressemble à ceci:

 SELECT * FROM mytable WHERE col1 = @value1 AND col2 = @value2 

et la disposition de la table est la suivante:

 CREATE TABLE mytable ( col1 INT NOT NULL, col2 INT NOT NULL, data VARCHAR(200) NOT NULL, PRIMARY KEY pk_mytable (col1, col2) ) ENGINE=InnoDB 

, le moteur devra simplement rechercher la valeur clé exacte dans la table elle-même.

Si vous utilisez un champ d’auto-incrémentation en tant que faux identifiant:

 CREATE TABLE mytable ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL, data VARCHAR(200) NOT NULL, UNIQUE KEY ix_mytable_col1_col2 (col1, col2) ) ENGINE=InnoDB 

, le moteur devra d’abord rechercher les valeurs de (col1, col2) dans l’index ix_mytable_col1_col2 , extraire le pointeur de ligne de l’index (la valeur de l’ id ) et effectuer une autre recherche par id dans la table elle-même.

Pour les tables MyISAM , cependant, cela ne fait aucune différence, car les tables MyISAM sont organisées en tas et le pointeur de ligne est simplement un décalage de fichier.

Dans les deux cas, un même index sera créé (pour PRIMARY KEY ou pour UNIQUE KEY ) et sera utilisé de la même manière.

Si c’est InnoDB, la clé primaire composite sera incluse dans chaque entrée de chacun des index secondaires.

Cela signifie que

  • Vos index secondaires occuperont autant d’espace que ces colonnes + toutes les colonnes de la clé primaire
  • Vous pouvez utiliser un index secondaire comme index de couverture si toutes les colonnes requirejses sont contenues dans l’index secondaire + pk

Celles-ci sont bien sûr un inconvénient et un avantage respectivement.

Les clés primaires composites ne sont pas nécessairement mauvaises, parfois elles peuvent être vraiment utiles car InnoDB les regroupe – ce qui signifie que les parsings de plage (liées au disque) sur la PK peuvent être satisfaites en utilisant beaucoup moins d’opérations d’E / S que celles d’un index non clusterisé .

Bien sûr, si vous avez des clés étrangères dans d’autres tables, elles sont plus larges et elles doivent inclure la clé entière de votre table principale.

Mais je dirais que tout compte fait, non. Avoir une clé primaire composite ne cause pas de problème par lui-même. Avoir une “grande” clé primaire (par exemple, big varchars) peut cependant faire si cela l’emporte sur les avantages de la mise en cluster et de la possibilité d’utiliser des index de couverture.

  1. Avoir cette clé primaire composite ralentit SELECT , même si l’effet est pratiquement négligeable et ne vaut pas la peine de s’en préoccuper.
  2. Le fait d’avoir ces colonnes indexées ralentit votre INSERT , et vous faites certainement assez d’ INSERT pour vous en préoccuper. C’est beaucoup plus inquiétant s’il s’agit d’une table MyISAM, où INSERT verrouille la table, que s’il s’agit d’une table InnoDB. Si, en allant avec la clé primaire auto_increment, vous pouviez laisser ces colonnes non indexées, vous bénéficieriez de la modification. Si vous avez toujours besoin de garder ces trois colonnes indexées, (par exemple, si vous devez imposer l’unicité à la combinaison de ces trois colonnes), cela ne fera rien pour vous.