Maintien d'une grande table de valeurs uniques dans MySQL

Question

Il s'agit probablement d'une situation courante, mais je n'ai pas trouvé de réponse précise sur SO ou Google.

J'ai une grande table (>10 millions de lignes) de relations d'amitié sur une base de données MySQL qui est très importante et qui doit être maintenue de sorte qu'il n'y ait pas de lignes en double. La table stocke les identifiants de l'utilisateur. Le code SQL de la table est le suivant

CREATE TABLE possiblefriends(
 id INT NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY(id),
 user INT, 
 possiblefriend INT)

Le fonctionnement de la table est le suivant : chaque utilisateur a environ 1000 "amis possibles" qui sont découverts et doivent être stockés, mais les "amis possibles" en double doivent être évités.

Le problème est qu'en raison de la conception du programme, au cours d'une journée, je dois ajouter un million de lignes ou plus à la table, qui peuvent être des doublons ou non. La solution la plus simple semble être de vérifier chaque ligne pour voir s'il s'agit d'un doublon et, si ce n'est pas le cas, de l'insérer dans le tableau. Mais cette technique deviendra probablement très lente lorsque la taille de la table passera à 100 millions de lignes, 1 milliard de lignes ou plus (ce qui devrait bientôt arriver).

Quelle est la meilleure façon (c'est-à-dire la plus rapide) de maintenir cette table unique ?

Je n'ai pas besoin d'avoir toujours sous la main un tableau contenant uniquement des valeurs uniques. Je n'en ai besoin qu'une fois par jour pour les travaux par lots. Dans ce cas, dois-je créer une table séparée qui se contente d'insérer toutes les lignes possibles (contenant des lignes en double et tout), puis, à la fin de la journée, créer une seconde table qui calcule toutes les lignes uniques de la première table ?

Si ce n'est pas le cas, quel est le meilleur moyen pour cette table à long terme ?

(Si les indices sont la meilleure solution à long terme, veuillez me dire quels indices utiliser)

Answer 1

Ajouter un index unique sur (user, possiblefriend) alors utilisez l'un des deux :

• INSERT ... ON DUPLICATE KEY UPDATE ...
• INSERT IGNORE
• REPLACE

pour s'assurer que vous n'obtenez pas d'erreurs lorsque vous essayez d'insérer une ligne en double.

Vous pouvez également envisager d'abandonner la clé primaire auto-incrémentée et d'utiliser la clé primaire de l'entreprise. (user, possiblefriend) comme clé primaire. Cela réduira la taille de votre table et la clé primaire fera office d'index, ce qui vous évitera de devoir créer un index supplémentaire.

Voir aussi :

• "INSERT IGNORE" contre "INSERT ON DUPLICATE KEY UPDATE".

Answer 2

Un index unique vous permettra d'être sûr que le champ est effectivement unique, vous pouvez ajouter un index unique comme suit :

CREATE TABLE possiblefriends( 
 id INT NOT NULL AUTO_INCREMENT,  
 PRIMARY KEY(id), 
 user INT,  
 possiblefriend INT,
PRIMARY KEY (id),
UNIQUE INDEX DefUserID_UNIQUE (user ASC, possiblefriend ASC))

Cela accélérera aussi considérablement l'accès à votre table.

Votre autre problème avec l'insertion en masse est un peu plus délicat, vous pouvez utiliser la fonction intégrée ON DUPLICATE KEY UPDATE ci-dessous :

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;