需要优化MySQL查询

Question

我有一个查询要花费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子句和其他比较中使用了键，但是没有在查询中使用它们中的任何一个，这是因为它们是派生查询吗？

- Manaf

---

请将您的数据结构发布到 http://sqlfiddle.com，以便我们可以进行优化查询。 - Muhammad Raheel

并解释您想要获得的结果。 - Muhammad Raheel

不要使用子查询，而要使用联接。 - Nin

Answer 1

它们是派生查询，但我认为没有理由让它们成为这样。

例如，考虑每行中的reportId始终相同。那么，总是引用驱动表的reportId是有优势的（MySQL应该自己足够聪明以执行此操作）。

例如，a和b表可以这样连接：

FROM
T_UserReport AS ur
JOIN T_ReportMonitor AS a ON (a.reportId = ur.reportId AND a.status = 'EndSP')
JOIN T_ReportMonitor AS b ON (b.reportId = ur.reportId AND b.status = 'BeginSP')

并且 T_ReportMonitor 需要在 status 和 reportId 上仅有一个索引：

CREATE INDEX ut_ndx ON T_ReportMonitor ( status, reportId, createdTS )

这样MySQL就可以立即选择a的EndSP条目，并为JOIN准备reportId列；完成后，它还可获得查询的createdTS。这个（更大的）数据表本身根本不需要访问。

其他表也是同样的概念。如果您：

- 在column1上进行JOIN， - 在column2 [AND column2a...] 值上有一个简单的WHERE过滤子句， - 查询体中仅需要column3， - 表格比迄今为止命名的三个列要大得多，

那么您将发现使用以下方式会更有优势：

JOIN table AS alias ON (alias.column1 = ... AND alias.column2 = 'filter value')

并拥有一个索引，例如：

CREATE INDEX table_ndx ON table ( column2, column1, column3 )

- LSerni

Answer 2

你的查询主要问题在于它使用了子查询。MySQL无法在子查询上使用索引，因为它基本上会为子查询创建一个新表，存储在内存中或磁盘上。尝试使用联结（JOIN）而不是子查询。

尝试使用以下语句：

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 上都有索引。

还有一件事会降低您的查询效率。您在 WHERE 子句中使用了函数：

date(ur.createdTS)

这意味着MySQL必须处理每一行数据，才能确定这个函数的结果。这可能会成为性能瓶颈。试图将该字段转换为日期类型（或创建一个新的日期字段），或者使用类似的方法。

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

- Nin