我有一个需要 33 秒的查询是否有更好的方法来重写它，我怎样才能将它转换为过程

select ut.templateId,
(
case 
when ut.reportTypeId=4 then 'Account'
when ut.reportTypeId=5 then 'Campaign' 
when ut.reportTypeId=6 then 'AdGroup' 
when ut.reportTypeId=7 then 'Ad'
when ut.reportTypeId=8 then 'Keyword' 
end
)as ReportType ,
ur.reportId,
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) < 5 then a.reportId else 0 end) as '<5secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 5 and 10 then a.reportId else 0 end) as '5-10secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 11 and 20 then a.reportId else 0 end) as '11-20secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 21 and 30 then a.reportId else 0 end) as '21-30secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 31 and 60 then a.reportId else 0 end) as '31-60secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 61 and 120 then a.reportId else 0 end) as '61-120secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 121 and 1800 then a.reportId else 0 end) as '2-30mins',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) > 1800 then a.reportId else 0 end) as '>30mins'
from

(select reportId,createdTS from T_ReportMonitor where status='EndSP')a,
(select reportId,createdTS from T_ReportMonitor where status='BeginSP')b,
(select templateId,reportTypeId,reportConsoleType from T_UserTemplate) ut,
(select reportId,templateId,createdTS,modifiedTS,isDeleted from T_UserReport) ur

where a.reportId=b.reportId 
and date(ur.createdTS) = 20120731
and ut.templateId=ur.templateId 
and reportConsoleType in ('Adser','APIAdser') 
and ur.isDeleted=false
and a.reportId=ur.reportId 
and ur.reportId!=313509 AND ur.reportId!=313510 AND ur.reportId!=313511 AND ur.reportId!=313512 AND ur.reportId!=313509 AND ur.reportId!=313510 AND ur.reportId!=313511 AND ur.reportId!=313512 AND ur.reportId!=313520;

查询的解释结果为

+----+-------------+-----------------+------+---------------+------+---------+------+--------+--------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra                          |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+--------------------------------+
|  1 | PRIMARY     | <derived2>      | ALL  | NULL          | NULL | NULL    | NULL |  20071 |                                |
|  1 | PRIMARY     | <derived3>      | ALL  | NULL          | NULL | NULL    | NULL |  20072 | Using where; Using join buffer |
|  1 | PRIMARY     | <derived5>      | ALL  | NULL          | NULL | NULL    | NULL | 148591 | Using where; Using join buffer |
|  1 | PRIMARY     | <derived4>      | ALL  | NULL          | NULL | NULL    | NULL | 154030 | Using where; Using join buffer |
|  5 | DERIVED     | T_UserReport    | ALL  | NULL          | NULL | NULL    | NULL | 124008 |                                |
|  4 | DERIVED     | T_UserTemplate  | ALL  | NULL          | NULL | NULL    | NULL | 151745 |                                |
|  3 | DERIVED     | T_ReportMonitor | ALL  | NULL          | NULL | NULL    | NULL |  60849 | Using where                    |
|  2 | DERIVED     | T_ReportMonitor | ALL  | NULL          | NULL | NULL    | NULL |  60849 | Using where                    |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+--------------------------------+

我在 where 子句和任何其他比较中使用了列上的键，但没有一个在查询中使用，是否是因为它们是派生查询的原因。

最佳答案

您的查询的主要问题是它使用了子查询。 MySQL 不能在子查询上使用索引，因为它基本上会在内存(或磁盘)中为您的子查询创建一个新表。尝试进行联接。

试试这个

select ut.templateId,
(
case 
when ut.reportTypeId=4 then 'Account'
when ut.reportTypeId=5 then 'Campaign' 
when ut.reportTypeId=6 then 'AdGroup' 
when ut.reportTypeId=7 then 'Ad'
when ut.reportTypeId=8 then 'Keyword' 
end
)as ReportType ,
ur.reportId,
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) < 5 then a.reportId else 0 end) as '<5secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 5 and 10 then a.reportId else 0 end) as '5-10secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 11 and 20 then a.reportId else 0 end) as '11-20secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 21 and 30 then a.reportId else 0 end) as '21-30secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 31 and 60 then a.reportId else 0 end) as '31-60secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 61 and 120 then a.reportId else 0 end) as '61-120secs',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) between 121 and 1800 then a.reportId else 0 end) as '2-30mins',
(case when timestampdiff(SECOND,b.createdTS,a.createdTS) > 1800 then a.reportId else 0 end) as '>30mins'
from
T_ReportMonitor as a JOIN T_ReportMonitor as b ON (a.reportId=b.reportId) JOIN  T_UserReport as ur ON (a.reportId=ur.reportId) JOIN T_UserTemplate as ut ON (ut.templateId=ur.templateId)
WHERE a.status='EndSP' AND b.status='BeginSP'
and date(ur.createdTS) = 20120731
and reportConsoleType in ('Adser','APIAdser') 
and ur.isDeleted=false
and ur.reportId NOT IN (313509,313510,313511,313512,313509,313510,313511,313512,313520);

确保 T_ReportMonitor.reportId、T_ReportMonitor.status 和 T_UserReport.reportId 上有一个键。

还有一件事会降低您的查询质量。您在何处使用函数:

date(ur.createdTS)

这意味着 MySQL 必须处理每一行以查看此函数的结果。这甚至可能被证明是最大的性能提升。尝试将该字段设为日期字段(或创建新的日期字段)或使用类似

WHERE ur.createdTS>='2012-07-31 00:00:00' AND ur.createdTS<='2012-07-31 23:95:59'

关于mysql - 需要优化 MySQL 查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/12813247/