在以累积方式按天选择数据的日期应用联接中的性能问题

Question

我写了一个查询来获取报告数据，它在给定的时间范围内获取数据，但现在面临查询中的性能问题，我已经分析了这个问题，问题是当我们在日期应用联接时，它花费了太长的时间，我将我的查询放在下面，它需要10秒。如果您有其他可以提供更好结果的查询，请提供。

SELECT 
dtbl.selected_date AS grouping,
if(def.dfID IS NULL, 0, datediff(DATE(dtbl.selected_date), DATE(CONVERT_TZ(def.createdDate, "+00:00", "-05:00")))) AS defectAge,
if(def.dfID IS NULL, 0, 1) AS DefectCount
1
FROM
(SELECT selected_date
FROM
(SELECT adddate('2020-02-02', t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date
FROM
(SELECT 0 t0
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9) t0,
(SELECT 0 t1
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9) t1,
(SELECT 0 t2
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9) t2,
(SELECT 0 t3
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9) t3,
(SELECT 0 t4
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9) t4) v
WHERE DATE(selected_date) BETWEEN '2020-02-02' AND '2020-03-02') dtbl
LEFT JOIN
(SELECT d.dfID,
d.createdDate,
d.lastStatusChangeDate,
drs.defaultresolutionstatusID
FROM defect d
LEFT JOIN listDetail ld ON ld.listDetailID=d.dfType
LEFT JOIN listDetail ld1 ON ld1.listDetailID=d.priority
JOIN project p ON p.projectID = d.projectID
LEFT JOIN defaultresolutionstatus drs ON drs.listDetailID=d.dfStatus
AND drs.projectID = d.projectID
WHERE p.clientID = 51
AND d.projectID IN (4179)
AND d.extentityDataID is null
AND if('-1'='-1', true, d.affectedRelease in (-1))
AND if("-1"="-1", TRUE, ld.listValue IN (-1))
AND if("-1"="-1", TRUE, ld1.listValue IN (-1)) ) AS def ON DATE(dtbl.selected_date) >= def.createdDate ; 

Answer 1

• AND if("-1"="-1", TRUE, ld1.listValue IN (-1)) ) AS def——不要在没有操作的情况下扰乱查询；在应用程序代码中动态构造CCD_ 2子句
• ON DATE(dtbl.selected_date ) >= def.createdDate——如果数据类型是DATE，则不需要使用DATE()
• CONVERT_TZ...——如果时区设置正确，您可能不需要即时隐蔽。此外，请考虑TIMESTAMP而不是DATE
• MariaDB有一个漂亮的"序列表"，可以让你动态生成一系列数字(以及日期(，比你现在做的要快得多
• 什么是... AS DefectCount 1 FROM ...？也许是打字错误
• MySQL的哪个版本？如果它不是很新，那么这个构造执行非常:FROM ( SELECT ... ) JOIN ( SELECT ... )。即使使用较新的版本，根据有限的数据范围检查每个日期(一个长列表(也可能是主要的性能问题
• 请提供EXPLAIN SELECT ...，它可能会提供更多线索
• 你有什么索引