mysql匹配“相同”电子邮件

l0oc07j2  于 2021-06-20  发布在  Mysql
关注(0)|答案(4)|浏览(451)

我有一张有两列的表 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)
hjqgdpho

hjqgdpho1#

REGEXP_REPLACE 需要,所以它可以在所有版本的mysql/mariadb中工作:

UPDATE tbl
    SET email = CONCAT(SUBSTRING_INDEX(email, '@', 1),
                       '@',
                       SUBSTRING_INDEX(
                           SUBSTRING_INDEX(email, '@', -1),
                           '.',
                           -2);

既然没有索引是有用的,你也不必为 WHERE 条款。

kqqjbcuj

kqqjbcuj2#

如果你搜索相关的数据,你应该寻找一些数据挖掘工具或ElasticSearch,例如,这是你需要的工作。
我有另一个可能的“仅数据库”解决方案,但我不知道它是否可行,或者它是否是最好的解决方案。如果我不得不这样做,我会尝试做一个“单词参考”的表格,用所有非字母数字字符分割所有电子邮件。
在您的示例中,此表将填充:john、smith12、some、subdomains、example和com。每个单词都有一个唯一的id。然后,另一个表,一个联合表,它将用自己的单词链接电子邮件。两个表都需要索引。
要搜索密切相关的电子邮件,您必须使用正则表达式拆分源电子邮件,并在每个子单词上循环,就像答案中的这个(带有connected by),然后对于每个单词,在word references表中找到它,然后在union表中找到匹配它的电子邮件。
在这个请求中,您可以选择对所有匹配的电子邮件求和,通过按电子邮件分组来计算找到的电子邮件匹配的字数,并只保留最匹配的电子邮件(当然不包括源电子邮件)。
对于这个“不确定的答案”我很抱歉,但是它太长了,无法发表评论。我要举个例子。
以下是一些数据的示例(在oracle中,但应与mysql一起使用):

---------------------------------------------
-- Table containing emails and people info
CREATE TABLE PEOPLE (
     ID NUMBER(11) PRIMARY KEY NOT NULL,
     EMAIL varchar2(100) DEFAULT NULL,
     USERNAME varchar2(500) DEFAULT NULL
);

-- Table containing word references
CREATE TABLE WORD_REF (
     ID number(11) NOT NULL PRIMARY KEY,
     WORD varchar2(20) DEFAULT NULL
);

-- Table containg id's of both previous tables
CREATE TABLE UNION_TABLE (
     EMAIL_ID number(11) NOT NULL,
     WORD_ID number(11) NOT NULL,
     CONSTRAINT EMAIL_FK FOREIGN KEY (EMAIL_ID) REFERENCES PEOPLE (ID),
     CONSTRAINT WORD_FK FOREIGN KEY (WORD_ID) REFERENCES WORD_REF (ID)
);

-- Here is my oracle sequence to simulate the auto increment
CREATE SEQUENCE MY_SEQ
  MINVALUE 1
  MAXVALUE 999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

---------------------------------------------
-- Some data in the people table
INSERT INTO PEOPLE (ID, EMAIL, USERNAME) VALUES (MY_SEQ.NEXTVAL, 'john.smith12@example.com', 'jsmith12');
INSERT INTO PEOPLE (ID, EMAIL, USERNAME) VALUES (MY_SEQ.NEXTVAL, 'john.smith12@some.subdomains.example.com', 'admin');
INSERT INTO PEOPLE (ID, EMAIL, USERNAME) VALUES (MY_SEQ.NEXTVAL, 'john.doe@another.domain.eu', 'jdo');
INSERT INTO PEOPLE (ID, EMAIL, USERNAME) VALUES (MY_SEQ.NEXTVAL, 'nathan.smith@example.domain.com', 'nsmith');
INSERT INTO PEOPLE (ID, EMAIL, USERNAME) VALUES (MY_SEQ.NEXTVAL, 'david.cayne@some.domain.st', 'davidcayne');
COMMIT;

-- Word reference data from the people data
INSERT INTO WORD_REF (ID, WORD) 
  (select MY_SEQ.NEXTVAL, WORD FROM
   (select distinct REGEXP_SUBSTR(EMAIL, '\w+',1,LEVEL) WORD
    from PEOPLE
    CONNECT BY REGEXP_SUBSTR(EMAIL, '\w+',1,LEVEL) IS NOT NULL
  ));
COMMIT;

-- Union table filling
INSERT INTO UNION_TABLE (EMAIL_ID, WORD_ID)
select words.ID EMAIL_ID, word_ref.ID WORD_ID
FROM 
(select distinct ID, REGEXP_SUBSTR(EMAIL, '\w+',1,LEVEL) WORD
 from PEOPLE
 CONNECT BY REGEXP_SUBSTR(EMAIL, '\w+',1,LEVEL) IS NOT NULL) words
left join WORD_REF on word_ref.word = words.WORD;
COMMIT;    

---------------------------------------------
-- Finaly, the request which orders the emails which match the source email 'john.smith12@example.com'
SELECT COUNT(1) email_match
      ,email
FROM   (SELECT word_ref.id
              ,words.word
              ,uni.email_id
              ,ppl.email
        FROM   (SELECT DISTINCT regexp_substr('john.smith12@example.com'
                                             ,'\w+'
                                             ,1
                                             ,LEVEL) word
                FROM   dual
                CONNECT BY regexp_substr('john.smith12@example.com'
                                        ,'\w+'
                                        ,1
                                        ,LEVEL) IS NOT NULL) words
        LEFT   JOIN word_ref
        ON     word_ref.word = words.word
        LEFT   JOIN union_table uni
        ON     uni.word_id = word_ref.id
        LEFT   JOIN people ppl
        ON     ppl.id = uni.email_id)
WHERE  email <> 'john.smith12@example.com'
GROUP  BY email_match DESC;

请求结果:

4    john.smith12@some.subdomains.example.com
    2    nathan.smith@example.domain.com
    1    john.doe@another.domain.eu
emeijp43

emeijp433#

你得到的名称(即“@”之前的部分)与

substring_index(email, '@', 1)

你得到了域名

substring_index(replace(email, '@', '.'), '.', -2))

(因为如果我们用一个点代替“@”,那么它总是第二个到最后一个点之后的部分)。
因此,你发现重复与

select *
from users
where exists
(
  select *
  from mytable other
  where other.id <> users.id
    and substring_index(other.email, '@', 1) = 
        substring_index(users.email, '@', 1)
    and substring_index(replace(other.email, '@', '.'), '.', -2) =
        substring_index(replace(users.email, '@', '.'), '.', -2)
);

如果速度太慢,则可能需要在两个合并的列上创建一个计算列并对其编制索引:

alter table users add main_email as 
  concat(substring_index(email, '@', 1), '@', substring_index(replace(email, '@', '.'), '.', -2));

create index idx on users(main_email);

select *
from users
where exists
(
  select *
  from mytable other
  where other.id <> users.id
    and other.main_email = users.main_email
);

当然,您也可以将这两个部分分开并为它们编制索引:

alter table users add email_name as substring_index(email, '@', 1);
alter table users add email_domain as substring_index(replace(email, '@', '.'), '.', -2);

create index idx on users(email_name, email_domain);

select *
from users
where exists
(
  select *
  from mytable other
  where other.id <> users.id
    and other.email_name = users.email_name
    and other.email_domain = users.email_dome
);

当然,如果你在email地址栏中同时允许大小写,你也会想申请 LOWER 在上面的表达中( lower(email) ).

egdjgwm8

egdjgwm84#

你告诉我们这张table有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) . 索引定义中列的顺序很重要。
我会删除所有其他索引,这样Optimizer就无法做出其他选择,除非有其他可能需要它们的查询。
很可能在表上定义一个主键也不会有什么坏处。
还有一件不重要的事情要考虑。你的数据真的有空值吗?如果不是,请将列定义为 NOT 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

相关问题