Mysql查询中的性能滞后 [英] Performace Lag in Mysql query

问题描述

这个 mysql 查询需要 5 分 20 秒来执行

This mysql query takes 5 minutes 20 seconds to execute

SELECT PROVIDER, COUNT(DISTINCT(NAME)) FROM   Test  WHERE NAME NOT IN (SELECT NAME 
FROM Test   WHERE OPERATION = 'SIGN_IN' and Test.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) *    1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000) 
AND Test.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) * 1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 AND OPERATION='VALIDATE'  GROUP BY PROVIDER;

解释给出以下结果

explain SELECT  PROVIDER, COUNT(DISTINCT(NAME)) FROM   Test  WHERE NAME NOT IN  
(SELECT NAME  FROM Test   WHERE OPERATION = 'SIGN_IN' and Test.CREATED_TIME BETWEEN 
UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) * 1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000) AND         Test.CREATED_TIME    BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) * 1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 AND OPERATION='VALIDATE'  GROUP BY PROVIDER;
+----+--------------------+-----------------+----------------+----------------------------------------------------------------------+-------------------------+---------+------+--------+------------------------------------+
| id | select_type        | table           | type           | possible_keys                                                        | key                     | key_len | ref  | rows   | Extra                              |
+----+--------------------+-----------------+----------------+----------------------------------------------------------------------+-------------------------+---------+------+--------+------------------------------------+
|  1 | PRIMARY            | Test | ALL            | Test_CTndx,Test_CORndx                         | NULL                    | NULL    | NULL | 137523 | Using where; Using filesort        |
|  2 | DEPENDENT SUBQUERY | Test | index_subquery | Test_NAMEndx,Test_CTndx,Test_CORndx | Test_NAMEndx | 303     | func |    148 | Using where; Full scan on NULL key |
+----+--------------------+-----------------+----------------+----------------------------------------------------------------------+-------------------------+---------+------+--------+------------------------------------+

表中的行数为 50,000.

Number of rows in a table is 50,000.

我该如何优化?

推荐答案

重写查询以将 NOT IN 替换为等效的 NOT EXISTS 语句应该会更快.

Rewriting your query to replace the NOT IN with an equivalent NOT EXISTS statement should be faster.

例如，尝试这样的事情:

For example, try something like this:

SELECT t1.PROVIDER, COUNT(DISTINCT(t1.NAME)) 
FROM   Test t1 
WHERE t1.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) * 1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000 
AND t1.OPERATION='VALIDATE'  
and NOT EXISTS (
  select null
  from test t2
  where t2.OPERATION = 'SIGN_IN' 
  and t2.CREATED_TIME BETWEEN UNIX_TIMESTAMP(CURRENT_DATE()  - INTERVAL 1 DAY) *    1000  AND UNIX_TIMESTAMP(CURRENT_DATE()) * 1000
  and t2.name = t1.name
)
GROUP BY t1.PROVIDER;

这篇关于Mysql查询中的性能滞后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！