Mysql 匹配 "Same"邮件

Question

我有一个包含 2 列 email 和 id 的表格。我需要找到密切相关的电子邮件。例如:

john.smith12@example.com

和

john.smith12@some.subdomains.example.com

这些应该被认为是相同的，因为用户名 (john.smith12) 和最顶级的域 (example.com) 是相同的。它们目前在我的表中是 2 个不同的行。 我已经编写了下面的表达式，它应该进行比较，但执行起来需要几个小时(可能/可能是因为正则表达式)。有没有更好的写法:

  select c1.email, c2.email 
  from table as c1
  join table as c2
   on (
             c1.leadid <> c2.leadid 
        and 
             c1.email regexp replace(replace(c2.email, '.', '[.]'), '@', '@[^@]*'))

此查询的解释返回为:

id, select_type, table, type, possible_keys, key, key_len, ref,  rows,   Extra
1,  SIMPLE,      c1,    ALL,   NULL,         NULL,  NULL,  NULL, 577532, NULL
1,  SIMPLE,      c2,    ALL,   NULL,         NULL,  NULL,  NULL, 577532, Using where; Using join buffer (Block Nested Loop)

创建表是:

CREATE TABLE `table` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Email` varchar(100) DEFAULT NULL,
 KEY `Table_Email` (`Email`),
 KEY `Email` (`Email`)
) ENGINE=InnoDB AUTO_INCREMENT=667020 DEFAULT CHARSET=latin1

我猜是因为正则表达式没有使用索引。

正则表达式输出为:

john[.]smith12@[^@]*example[.]com

应该匹配两个地址。

更新:

我已经将 on 修改为:

on (c1.email <> '' and c2.email <> '' and c1.leadid <> c2.leadid and substr(c1. email, 1, (locate('@', c1.email) -1)) = substr(c2. email, 1, (locate('@', c2.email) -1))
and    
substr(c1.email, locate('@', c1.email) + 1) like concat('%', substr(c2.email, locate('@', c2.email) + 1)))

并且使用这种方法的解释至少使用了索引。

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, c1, range, table_Email,Email, table_Email, 103, NULL, 288873, Using where; Using index
1, SIMPLE, c2, range, table_Email,Email, table_Email, 103, NULL, 288873, Using where; Using index; Using join buffer (Block Nested Loop)

到目前为止，这已经执行了 5 分钟，如果有很大的改进，将会更新。

更新 2:

我已经拆分了电子邮件，所以用户名是一列，域是一列。我以相反的顺序存储域，因此它的索引可以与尾随通配符一起使用。

CREATE TABLE `table` (
     `ID` int(11) NOT NULL AUTO_INCREMENT,
     `Email` varchar(100) DEFAULT NULL,
     `domain` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
     `username` varchar(500) CHARACTER SET utf8 DEFAULT NULL,
     KEY `Table_Email` (`Email`),
     KEY `Email` (`Email`),
     KEY `domain` (`domain`)
    ) ENGINE=InnoDB AUTO_INCREMENT=667020 DEFAULT CHARSET=latin1

填充新列的查询:

update table
set username = trim(SUBSTRING_INDEX(trim(email), '@', 1)), 
domain = reverse(trim(SUBSTRING_INDEX(SUBSTRING_INDEX(trim(email), '@', -1), '.', -3)));

新查询:

select c1.email, c2.email, c2.domain, c1.domain, c1.username, c2.username, c1.leadid, c2.leadid
from table as c1
join table as c2
on (c1.email is not null and c2.email is not null and c1.leadid <> c2.leadid
    and c1.username = c2.username and c1.domain like concat(c2.domain, '%'))

新解释结果:

1, SIMPLE, c1, ALL, table_Email,Email, NULL, NULL, NULL, 649173, Using where
1, SIMPLE, c2, ALL, table_Email,Email, NULL, NULL, NULL, 649173, Using where; Using join buffer (Block Nested Loop)

从那个解释看来 domain 索引没有被使用。我还尝试使用 USE 强制使用，但这也没有用，导致没有使用索引:

select c1.email, c2.email, c2.domain, c1.domain, c1.username, c2.username, c1.leadid, c2.leadid
from table as c1
USE INDEX (domain)
join table as c2
USE INDEX (domain)
on (c1.email is not null and c2.email is not null and c1.leadid <> c2.leadid
    and c1.username = c2.username and c1.domain like concat(c2.domain, '%'))

用use解释:

1, SIMPLE, c1, ALL, NULL, NULL, NULL, NULL, 649173, Using where
1, SIMPLE, c2, ALL, NULL, NULL, NULL, NULL, 649173, Using where; Using join buffer (Block Nested Loop)

Answer 1

您告诉我们该表有 70 万行。

这并不多，但您正在将其连接到自身，因此在最坏的情况下，引擎必须处理 700K * 700K = 490 000 000 000 = 490B 行。

索引绝对可以提供帮助。

最佳索引取决于数据分布。

以下查询返回什么？

SELECT COUNT(DISTINCT username) 
FROM table

如果结果接近 700K，比如 100K，则意味着有很多不同的用户名，您最好关注它们，而不是 domain。如果结果很低，比如 100，则索引 username 不太可能有用。

我希望有很多不同的用户名，所以，我会在 username 上创建一个索引，因为查询使用简单的相等比较在该列上连接，而这个连接将极大地受益于这个指标。

要考虑的另一种选择是(username, domain) 上的复合索引，甚至覆盖(username, domain, leadid, email) 上的索引。索引定义中列的顺序很重要。

我会删除所有其他索引，这样优化器就无法做出其他选择，除非有其他查询可能需要它们。

很可能在表上定义一个主键也不会有什么坏处。

---

还有一件不太重要的事情需要考虑。您的数据真的有 NULL 吗？如果不是，则将列定义为 NOT NULL。此外，在许多情况下，最好使用空字符串而不是 NULL，除非您有非常具体的要求并且必须区分 NULL 和 ''。

查询会稍微简单一些:

select 
    c1.email, c2.email, 
    c1.domain, c2.domain, 
    c1.username, c2.username, 
    c1.leadid, c2.leadid
from 
    table as c1
    join table as c2
        on  c1.username = c2.username 
        and c1.domain like concat(c2.domain, '%')
        and c1.leadid <> c2.leadid