在MYSQL中索引电子邮件地址的最佳方法是什么

问题描述：

我有一个包含数百万电子邮件ID记录的注册表。电子邮件ID是独一无二的。为了进行身份验证，使用asp.net将它们编入索引并获取它们的最佳方法是什么？我的意思是我应该将电子邮件ID列定义为聚集的唯一索引而不是UNIQUE？在MYSQL中索引电子邮件地址的最佳方法是什么

您使用的是MySQL还是MSSQL？ MySQL没有聚集的唯一索引。你可以对电子邮件进行散列处理（比如用'sha1'），并将其保存为二进制文件，每次将脚印减少到20个字节，并且该列旁边有明文电子邮件地址值。使哈希独一无二，现在您拥有固定长度的唯一标识符。 – Mjh

对电子邮件地址进行散列处理，将它们转换为二进制文件，然后将它们存储在电子邮件列旁边的数据库中是什么意思。它会增加数据库的大小。它不会解决我的问题，反而会增加它。我想通过实施索引来缩短查询时间。 – Deep

电子邮件地址具有可变长度。如果您散列可变长度值，您的索引将会有所不同，并且当您尝试索引过大的值时，索引还有其他问题。为了减少这个问题，你不需要索引电子邮件的实际字符串值，但是它的哈希值是因为哈希值是固定长度的。它确实解决了你的问题，并且你没有任何可以来回转换的东西。 – Mjh

答

当你有一个可变长度的文本输入，如e-mail或地址，但你希望他们是唯一的，则标准方法是指数的哈希那个价值。

原因：散列是固定长度的，并且您避免了超出索引长度的文本数据的问题。

根据您的意见，你有看起来像这样的表（我故意省略密码和手机号码）：

create table users (
    user_id int not null unsigned auto_increment, 
    first_name varchar(255) not null, 
    surname varchar(255) default null, 
    email varchar(255) not null, 
    primary key(id) 
) engine = innodb;

我会改变该表，并添加包含电子邮件散列的字段。我会通过触发器保持这一点，以便您可以专注于获取有效数据，而不用担心创建哈希。为此，该字段将为binary(20)，因为它将包含原始散列并占用20个字节。既然我们想通过触发来维护它，那么我们需要使该字段可为空且唯一。注意：你可以把它binary(40)

表：

create table users (
    user_id int not null unsigned auto_increment, 
    email_hash binary(20) default null, -- this is the field in question 
    first_name varchar(255) not null, 
    surname varchar(255) default null, 
    email varchar(255) not null, 
    primary key(id), 
    unique(email_hash) -- this is the unique index over the hash 
) engine = innodb;

，我们现在需要的是一个触发器，它与电子邮件哈希交易。我将介绍如何在插入前创建维护此信息的触发器。类似的逻辑适用于更新表格：

DELIMITER $$ 

CREATE TRIGGER users_before_insert BEFORE INSERT ON `users` 

FOR EACH ROW BEGIN 
    SET NEW.email_hash = UNHEX(SHA1(new.email)); -- You can remove UNHEX if you want human-readable value. You'll need binary(40) to hold it then 
END; 

DELIMITER ;

从您的应用程序中，您只需提供名字，姓氏和电子邮件的值。 MySQL会处理重复项，它会告诉你状态为23000。我不知道如何使用asp.net，所以你必须以某种方式适应错误处理。

你可以在你的asp.net应用程序中处理哈希，但如果你感觉数据库这样做更舒服 - 我展示了如何通过触发器实现它。

如果您需要手机号码是唯一的或任何其他字段，则同样的规则适用于手机号码。当然，哈希数可能会产生比实际数更长的哈希值，在这种情况下，您可能直接将手机号码设为unique。

我希望这可以帮助你决定做什么。

答

如果你正在做一个独特的密钥查找，它确实没有足够的性能差异来担心索引是否被聚集。在向表中添加更多内容时，对其进行群集可能是有意义的（或不是）。主要的是你有一个唯一的约束，很可能这将是主键，所以你会得到这个和相应的索引。性能会很好 - 关注其他用途。例如如果您想对域进行分析，则可能需要分解电子邮件地址。这可能更重要。最喜欢的事情，这取决于....

感谢您的帮助。但我想澄清一件事。我有一个单独的ID列，我已经声明为主键，但是我正在考虑将emailID声明为主键和Auto inc。因为我已经宣布它是唯一的。如果我有数百万条记录，它肯定会减少我的数据库的大小。如果我错了，请纠正我。谢谢.. :) – Deep

@Deep - 什么是'emailID'？电子邮件地址的价值？你打算如何使自动递增？只需保留主键，将电子邮件的散列添加为“唯一”，这样您就不会重复，并且所有问题都消失了。您可以快速查询，您可以快速检索，您可以只插入唯一的电子邮件 - 不要过度使用它，因为您会发现哪里没有问题。数以百万计的记录都不算什么，所以不要过于担心数百万条记录。 – Mjh

您不需要此ID。即使你决定包含它，你也需要在电子邮件中添加一个唯一的非空限制，这实际上是PK。如果你需要在电子邮件上使用外键，那么可能会保留该ID，但这不在你在这里描述的范围内。不知道为什么Mjh对电子邮件进行哈希处理如此坚持 - 这不是必要的，会使所有事情复杂化，并为错误开放。我不会那样做。 – LoztInSpace

答

事情太多了评论...

如果你已经有INDEX(email)，然后简单地把它变成UNIQUE(email)。表格（数据+索引）的大小不会改变（比ALTER多一点）。

如果email太大而无法索引 - 比如因为它是TEXT - 那么无法在email上添加UNIQUE索引。在这种情况下，“散列”解决方案将起作用。是的，它会为磁盘使用增加兆字节，但这不太可能成为问题。

如果您目前有id AUTO_INCREMENT和PRIMARY KEY(id)，那么您是否真的在其他表中使用id？如果没有，那么我们可以讨论其他路径，例如使email或hashPRIMARY KEY。这可能甚至缩小磁盘的足迹。

不管你做什么，都使用InnoDB。

如果不存储电子邮件ID的散列，而只是将emai_id声明为主键而不为空，它会为我做这件事吗？但主键本身是一个独特的聚集索引，这对我来说可能是一个问题。 – Deep

使用'PRIMARY KEY（email）'，'INSERT''再次发送同一封电子邮件将失败（重复密钥）。另见'INSERT ... ON DUPLICATE KEY UPDATE ...'。 –

在MYSQL中索引电子邮件地址的最佳方法是什么

相关推荐