我有一张有两列的表 email
以及 id
. 我需要找到密切相关的电子邮件。例如:
john.smith12@example.com
和
john.smith12@some.subdomains.example.com
因为用户名( john.smith12
)最顶级的域名( example.com
)都是一样的。它们目前在我的表中有两个不同的行。我已经编写了下面的表达式来进行比较,但是执行起来需要几个小时(可能/可能是因为regex)。有没有更好的方法来写这个:
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
我猜索引没有被使用是因为regexp。
正则表达式如下:
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)))
以及 explain
这种方法至少是使用索引。
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)
4条答案
按热度按时间hjqgdpho1#
不
REGEXP_REPLACE
需要,所以它可以在所有版本的mysql/mariadb中工作:既然没有索引是有用的,你也不必为
WHERE
条款。kqqjbcuj2#
如果你搜索相关的数据,你应该寻找一些数据挖掘工具或ElasticSearch,例如,这是你需要的工作。
我有另一个可能的“仅数据库”解决方案,但我不知道它是否可行,或者它是否是最好的解决方案。如果我不得不这样做,我会尝试做一个“单词参考”的表格,用所有非字母数字字符分割所有电子邮件。
在您的示例中,此表将填充:john、smith12、some、subdomains、example和com。每个单词都有一个唯一的id。然后,另一个表,一个联合表,它将用自己的单词链接电子邮件。两个表都需要索引。
要搜索密切相关的电子邮件,您必须使用正则表达式拆分源电子邮件,并在每个子单词上循环,就像答案中的这个(带有connected by),然后对于每个单词,在word references表中找到它,然后在union表中找到匹配它的电子邮件。
在这个请求中,您可以选择对所有匹配的电子邮件求和,通过按电子邮件分组来计算找到的电子邮件匹配的字数,并只保留最匹配的电子邮件(当然不包括源电子邮件)。
对于这个“不确定的答案”我很抱歉,但是它太长了,无法发表评论。我要举个例子。
以下是一些数据的示例(在oracle中,但应与mysql一起使用):
请求结果:
emeijp433#
你得到的名称(即“@”之前的部分)与
你得到了域名
(因为如果我们用一个点代替“@”,那么它总是第二个到最后一个点之后的部分)。
因此,你发现重复与
如果速度太慢,则可能需要在两个合并的列上创建一个计算列并对其编制索引:
当然,您也可以将这两个部分分开并为它们编制索引:
当然,如果你在email地址栏中同时允许大小写,你也会想申请
LOWER
在上面的表达中(lower(email)
).egdjgwm84#
你告诉我们这张table有70万行。
这并不多,但你是连接到它本身,所以在最坏的情况下,引擎将不得不处理
700K * 700K = 490 000 000 000 = 490B
排。索引在这里绝对有用。
最佳索引取决于数据分布。
下面的查询返回什么?
如果结果接近700k,比如说100k,那就意味着有很多不同的用户名,你最好关注它们,而不是它们
domain
. 如果结果比索引低,比如说100username
不太可能有用。我希望有很多不同的用户名,所以,我会在上面创建一个索引
username
,因为查询使用简单的相等比较对该列进行联接,而此联接将大大受益于此索引。另一个可以考虑的选择是在
(username, domain)
或者甚至包括(username, domain, leadid, email)
. 索引定义中列的顺序很重要。我会删除所有其他索引,这样Optimizer就无法做出其他选择,除非有其他可能需要它们的查询。
很可能在表上定义一个主键也不会有什么坏处。
还有一件不重要的事情要考虑。你的数据真的有空值吗?如果不是,请将列定义为
NOT NULL
. 另外,在许多情况下,最好是空字符串,而不是空字符串,除非您有非常具体的要求,并且必须区分空字符串和空字符串''
.查询将稍微简单一些: