问优化两大MySQL表中的数据比较
EN

Stack Overflow用户

提问于 2020-01-09 09:41:27

回答 1查看 71关注 0票数 0

如何优化查询，该查询将查找所有记录，其中：

有activation_request.date_confirmed不为空的

和

在另一个表中没有相关的字符串值: activation_request.email = user.username不应该返回任何记录

我试过：

SELECT  email 
FROM activation_request l 
    LEFT JOIN user r ON r.username = l.email 
WHERE l.date_confirmed is not null 
AND r.username IS NULL

和

SELECT email 
FROM  activation_request 
WHERE  date_confirmed is not null 
AND NOT EXISTS (SELECT 1 
                FROM user  
                WHERE  user.username = activation_request.email
                )

但这两个表都有xxx.xxx.xxx记录，因此，不幸的是，在运行了一整晚的查询之后，我没有得到任何结果。

创建语句：

CREATE TABLE `activation_request` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `date_confirmed` datetime DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  (...)
  PRIMARY KEY (`id`),
  KEY `emailIdx` (`email`),
  KEY `reminderSentIdx` (`date_reminder_sent`),
  KEY `idx_resent_needed` (`date_reminder_sent`,`date_confirmed`),
) ENGINE=InnoDB AUTO_INCREMENT=103011867 DEFAULT CHARSET=utf8;




CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) NOT NULL,
  `username` varchar(255) NOT NULL,
  (...)
  PRIMARY KEY (`id`),
  UNIQUE KEY `Q52plW9W7TJWZcLj00K3FmuhwMSw4F7vmxJGyjxz5iiINVR9fXyacEoq4rHppb` (`username`),
) ENGINE=InnoDB AUTO_INCREMENT=431400048 DEFAULT CHARSET=latin1;

向左加入解释：

key:Q52plW9W7TJWZcLj00K3FmuhwMSw4F7vmxJGyjxz5iiINVR9fXyacEoq4rHppb，

[id:1，select_type:SIMPLE，select_type:SIMPLE:l，type:ALL，possible_keys:null，key:null，key_len:null，ref:null，：49148965，Extra: where，id:1，select_type:SIMPLE，table:r，type:index，possible_keys:null，select_type:SIMPLE key_len:257，ref:null，rows:266045508，exists: Using where；Not；Not；使用联接缓冲区(块嵌套循环)] [id:1，select_type:SIMPLE，select_type:SIMPLE:l，type:ALL，possible_keys:null，key:null，key_len:null，ref:null，ref:null，行:49148965，exists: where，id:1，select_type:SIMPLE，table:r，type:index，possible_keys:null，ref:null，行:266045508，Extern外: Using；Not；Using；使用联接缓冲区(块嵌套循环)]

在对暂存db添加索引(数据略有减少，但结构相同)之后，查询现在正在运行~24h，但仍然没有结果)：

$ show processlist;

| Id | User    | Host                                            | db       | Command | Time   | State        | Info 
| 64 | root    | localhost                                       | staging_db   | Query   | 110072 | Sending data | SELECT ar.email FROM  activation_request ar WHERE ar.date_confirmed is not null AND NOT EXISTS (SELE |

Mysql版本：

$ select version();
5.6.16-1~exp1

列表中的所有其他命令都是Sleep，因此没有其他查询正在运行，而且可能会干扰/锁定行。

mysql

sql

performance

query-performance

腾讯云域名特惠，新用户首年1元起

腾讯云域名专场特惠：个人新用户.cn仅8.8元/年，企业新用户.com仅1.06元/年...

回答 1

Stack Overflow用户

发布于 2020-01-09 15:02:12

对于此查询：

SELECT ar.email 
FROM  activation_request ar
WHERE ar.date_confirmed is not null AND
      NOT EXISTS (SELECT 1 
                  FROM user u
                  WHERE u.username = ar.email
                 )