MySQL查询优化总..案例

Question

有什么办法可以优化下面的查询吗？

SELECT 
DATE_FORMAT(a.duedate,'%d-%b-%y') AS dte, 
duedate, 
SUM(CASE WHEN (typeofnotice='ddat' AND STATUS='open') THEN 1 ELSE 0 END) AS 'DDatOpen', 
SUM(CASE WHEN (typeofnotice='ddat' AND STATUS='closed') THEN 1 ELSE 0 END) AS 'DDatClosed', 
SUM(CASE WHEN (b.action='tagunchanged' AND STATUS='closed') THEN 1 ELSE 0 END) AS 'DDatUnchanged',
SUM(CASE WHEN (typeofnotice='rss' AND validindicator IS NULL AND STATUS='open') THEN 1 ELSE 0 END) AS 'RSSValidation', 
SUM(CASE WHEN (typeofnotice='rss' AND validindicator=1 AND STATUS='open') THEN 1 ELSE 0 END) AS 'RSSValidOpen', 
SUM(CASE WHEN (typeofnotice='rss' AND validindicator=1 AND STATUS='closed') THEN 1 ELSE 0 END) AS 'RSSValidClosed', 
SUM(CASE WHEN (typeofnotice='rss' AND validindicator=0) THEN 1 ELSE 0 END) AS 'RSSInvalid', 
SUM(CASE WHEN (typeofnotice='copernic' AND validindicator IS NULL AND STATUS='open') THEN 1 ELSE 0 END) AS 'CopernicValidation', 
SUM(CASE WHEN (typeofnotice='copernic' AND isaward=1 AND validindicator=1 AND STATUS='open') THEN 1 ELSE 0 END) AS 'CopernicValidAwardOpen', 
SUM(CASE WHEN (typeofnotice='copernic' AND isaward=1 AND validindicator=1 AND STATUS='closed') THEN 1 ELSE 0 END) AS 'CopernicValidAwardClosed', 
SUM(CASE WHEN (typeofnotice='copernic' AND isaward=1 AND validindicator=0) THEN 1 ELSE 0 END) AS 'CopernicInvalidAward', 
SUM(CASE WHEN (typeofnotice='copernic' AND isaward=0 AND validindicator=1 AND STATUS='open') THEN 1 ELSE 0 END) AS 'CopernicOpportunityValidOpen', 
SUM(CASE WHEN (typeofnotice='copernic' AND isaward=0 AND validindicator=1 AND STATUS='closed') THEN 1 ELSE 0 END) AS 'CopernicOpportunityValidClosed', 
SUM(CASE WHEN (typeofnotice='copernic' AND isaward=0 AND validindicator=0) THEN 1 ELSE 0 END) AS 'CopernicOpportunityInvalid', 
SUM(CASE WHEN (typeofnotice='copernic' AND STATUS='limited') THEN 1 ELSE 0 END) AS 'CopernicLimitation', 
SUM(CASE WHEN ((validindicator IS NULL OR validindicator = 1) AND STATUS='open') THEN 1 ELSE 0 END) AS 'TotalNotices', 
SUM(CASE WHEN (validindicator=1 AND STATUS='closed') THEN 1 ELSE 0 END) AS 'TotalCompleted', 
SUM(CASE WHEN (validindicator=0 AND (typeofnotice='wget' OR typeofnotice='copernic' OR typeofnotice='rss')) THEN 1 ELSE 0 END) AS 'TotalInvalid'
FROM tblNotices AS a LEFT JOIN tblTransactions AS b 
ON a.id = b.noticeid WHERE b.noticeid IS NOT NULL
WHERE duedate >= '2011-04-04 00:00:00'  AND  a.duedate <= '2012-05-08 24:00:00' 
GROUP BY dte 
ORDER BY dueDate ASC;

tblTransactions 有 150 万行tblNotices 有 900k 行。

查询运行大约 1 分钟。正常吗？有什么办法可以优化这个查询吗？

我认为这里的DATE_FORMAT函数确实会降低性能。这里有什么提示吗？它运行约 58 秒。

Answer 1

除了 SUM( IF()) 实例之外，查询看起来非常简单。

您的查询对事务有一个 LEFT-JOIN，但是 b.noticeID 不为空。两者的组合产生正常的“JOIN”或“INNER JOIN”，含义是双方都需要的。

至于您的Where子句，我会确保您在到期日有一个简单的索引。您的表显示“MUL”(多键)索引，我将确保到期日期是该键的第一部分，或者至少有一个索引将到期日期作为第一个字段。

接下来，您的分组依据。由于您正在按截止日期的日期格式进行分组，因此我将仅根据截止日期(与表索引匹配)离开分组。无论如何，视觉格式化的字符串将一路跟随。由于您的 Order By 也是基于到期日期(而不是漂亮格式的字符串版本)，因此应该很好。

至于查询本身的日期范围...有多少条记录在提供的范围内。

修订

您可以利用查询中使用的元素的多部分索引，这样查询就不必实际转到页面数据来查看整个记录中的各个元素。由于数据将是索引键的一部分，因此查询可以直接使用它，而不需要转到页面。

尝试建立索引

索引...(截止日期、状态、IsAward、TypeOfNotice、ValidIndicator)

另外，为了澄清查询中的alias.fields。有时您会明确引用“a”。别名和其他时候，没有别名。对于在您之后处理查询的其他人，或其他试图提供帮助的人。它可以更轻松地使用适当的别名显式引用所有字段，以防止哪个表中的哪些列出现歧义。是的，您在此处提供了表结构，但 future 可能会让事情变得比必须不断回顾结构更容易。