MySQL：在WHERE子句中与NOT IN相关的子查询非常缓慢

问题描述：

我正在使用开放式Id登录审核来自我的应用程序的用户详细信息。如果用户第一次登录时，我们认为这是一个OPEN ID作为注册。我正在使用此详细信息生成审核登录报告。示例表数据。MySQL：在WHERE子句中与NOT IN相关的子查询非常缓慢

+---------+----------+-----------+---------------+ 
| USER_ID | PROVIDER | OPERATION | TIMESTAMP  | 
+---------+----------+-----------+---------------+ 
|  120 | Google | SIGN_UP | 1347296347000 | 
|  120 | Google | SIGN_IN | 1347296347000 | 
|  121 | Yahoo | SIGN_IN | 1347296347000 | 
|  122 | Yahoo | SIGN_IN | 1347296347000 | 
|  120 | Google | SIGN_UP | 1347296347000 | 
|  120 | FaceBook | SIGN_IN | 1347296347000 | 
+---------+----------+-----------+---------------+

在这个表我要排除已经SIGN_UP版“SIGN_IN”版的用户数基于提供商。

展创建表

CREATE TABLE `signin_details` (
    `USER_ID` int(11) DEFAULT NULL, 
    `PROVIDER` char(40) DEFAULT NULL, 
    `OPERATION` char(40) DEFAULT NULL, 
    `TIMESTAMP` bigint(20) DEFAULT NULL 
) ENGINE=InnoDB

我使用这个查询。

select 
    count(distinct(USER_ID)) as signin_count, 
    PROVIDER from signin_details s1 
where 
    s1.USER_ID NOT IN 
    (
    select 
     USER_ID 
    from signin_details 
    where 
     signin_details.PROVIDER=s1.PROVIDER 
     and signin_details.OPERATION='SIGN_UP' 
     and signin_details.TIMESTAMP/1000 BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()-INTERVAL 1 DAY) * 1000 AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 
) 
    AND OPERATION='SIGN_IN' group by PROVIDER;

解释输出：

+----+--------------------+----------------+------+---------------+------+---------+------+------+-----------------------------+ 
| id | select_type  | table   | type | possible_keys | key | key_len | ref | rows | Extra      | 
+----+--------------------+----------------+------+---------------+------+---------+------+------+-----------------------------+ 
| 1 | PRIMARY   | s1    | ALL | NULL   | NULL | NULL | NULL | 6 | Using where; Using filesort | 
| 2 | DEPENDENT SUBQUERY | signin_details | ALL | NULL   | NULL | NULL | NULL | 6 | Using where     | 
+----+--------------------+----------------+------+---------------+------+---------+------+------+-----------------------------+

查询输出：

+--------------+----------+ 
| signin_count | PROVIDER | 
+--------------+----------+ 
|   1 | FaceBook | 
|   2 | Yahoo | 
+--------------+----------+

这需要40分钟以上，以执行用于200K行。

我的假设是它会检查依赖子查询输出总数的每一行。

我对此查询的假设。

A -> Dependant Outputs (B,C,D) . 
A check with B 
A check with C 
A check with D

如果从属查询输出较大，则需要很长时间才能执行。如何改进这个查询？

基本上只是问了同样的问题，3天前，我的答案是使用'NOT EXISTS'取代'NOT IN'。你尝试过吗？ http://stackoverflow.com/questions/12305066/performace-lag-in-mysql-query/12305168#12305168 –

你能显示'show create table ......'吗？ – jcho360

嗨沃克，我试过了，没有时间了。与IN完全相同的时间，不存在。 – RBK

答

如果你使用MySQL你必须知道，子查询执行速度很慢。

IN慢...

EXISTS往往快于IN

JOIN大多是最快的方法做这样的事情。

SELECT DISTINCT 
    s1.PROVIDER, 
    COUNT(DISTINCT s1.USER_ID) 

FROM 
    signin_details s1 
    LEFT JOIN 
    (
    SELECT DISTINCT 
     USER_ID, PROVIDER 
    FROM 
     signin_details 
    WHERE 
     signin_details.OPERATION='SIGN_UP' 
     AND 
     signin_details.TIMESTAMP 
      BETWEEN 
      UNIX_TIMESTAMP(CURRENT_DATE()-INTERVAL 1 DAY) * 1000 
      AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 
) AS t USING (USER_ID, PROVIDER) 

WHERE 
    t.USER_ID IS NULL 
    AND OPERATION='SIGN_IN' 
GROUP BY s1.PROVIDER

http://sqlfiddle.com/#!2/122ac/12

注：如果您想进一步了解sqlfiddle结果认为这里是在查询中UNIX_TIMESTAMP。

结果：

| PROVIDER | COUNT(DISTINCT S1.USER_ID) | 
----------------------------------------- 
| FaceBook |       1 | 
| Yahoo |       2 |

的MySQL和INTERSECT故事。你可以得到你不想数的所有USER_ID和PROVIDER的组合。然后LEFT JOIN他们到你的数据。现在，您要计数的所有行都没有来自LEFT JOIN的值。你通过t.USER_ID IS NULL得到它们。

输入：

| rn° | USER_ID | PROVIDER | OPERATION |  TIMESTAMP | 
------------------------------------------------------- 
| 1 |  120 | Google | SIGN_UP | 1347296347000 | - 
| 2 |  120 | Google | SIGN_IN | 1347296347000 | - (see rn° 1) 
| 3 |  121 | Yahoo | SIGN_IN | 1347296347000 | Y 
| 4 |  122 | Yahoo | SIGN_IN | 1347296347000 | Y 
| 5 |  120 | Google | SIGN_UP | 1347296347000 | - 
| 6 |  120 | FaceBook | SIGN_IN | 1347296347000 | F 
| 7 |  119 | FaceBook | SIGN_IN | 1347296347000 | - (see rn° 8) 
| 8 |  119 | FaceBook | SIGN_UP | 1347296347000 | -

MySQL：在WHERE子句中与NOT IN相关的子查询非常缓慢

相关推荐