在 MySQL 中维护一个包含唯一值的大型表

Question

这可能是一种常见的情况，但我在 SO 或 Google 上找不到具体的答案。

我在 MySQL 数据库上有一个很大的朋友关系表（>1000 万行），该表非常重要，需要维护以确保没有重复的行。该表存储用户的uid。该表的SQL是：

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

该表的工作方式是每个用户大约有1000个左右的“可能的朋友”被发现并需要存储，但需要避免重复的“可能的朋友”。

问题是，由于程序的设计，在一天的时间内，我需要向表中添加 100 万行或更多行，这些行可能是重复的行条目，也可能不是重复的行条目。简单的答案似乎是检查每一行以查看它是否重复，如果不是，则将其插入表中。但当表大小增加到 1 亿行、10 亿行或更高时（我预计很快就会实现），这种技术可能会变得非常慢。

维护这个唯一表的最佳（即最快）方法是什么？

我不需要始终拥有一个仅包含唯一值的表。我每天只需要它一次来进行批处理作业。在这种情况下，我是否应该创建一个单独的表，仅插入所有可能的行（包含重复行和所有行），然后在一天结束时创建第二个表来计算第一个表中的所有唯一行？

如果不是，从长远来看，该表的最佳方法是什么？

（如果索引是最佳的长期解决方案，请告诉我使用哪些索引）

Answer 1

在 (user, possiblefriend) 上添加唯一索引，然后使用以下之一：

• 插入...重复密钥更新...
• 插入忽略
• REPLACE

以确保在尝试插入重复行时不会出现错误。

您可能还需要考虑是否可以删除自动递增主键并使用 (user, possiblefriend) 作为主键。这将减少表的大小，并且主键将充当索引，从而使您不必创建额外的索引。

另请参阅：

• “插入忽略”与“插入......重复密钥更新”

Answer 2

唯一索引可以让您确定该字段确实是唯一的，您可以像这样添加唯一索引：

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))

这也将显着提高您的表访问速度。

批量插入的另一个问题有点棘手，您可以使用下面内置的 ON DUPLICATE KEY UPDATE 功能：

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;