此查询在生产中运行了大约 15 个小时,我正在寻找改进的替代方案,
我认为这些改进可能会有所帮助,请在此处评论:
SELECT table1.*
FROM table1
WHERE UPPER(LEFT(table1.cloumn1, 1)) IN ('A', 'B')
AND table1.cloumn2 = 'N' /* add composite index for cloumn2,
column3 */
AND table1.cloumn3 != 'Y'
AND table1.id IN (
SELECT MAX(id)
FROM table1
GROUP BY column5,column6
) /* move this clause to 2nd after
where */
AND table1.column4 IN (
SELECT column1
FROM table2
WHERE column2 IN ('VALUE1', 'VALUE2')
AND (SUBSTRING(column3,6,1) = 'Y'
OR SUBSTRING(column3,25,1) = 'Y')
) /* move this clause to 1st after
where */
AND (table1.column5,table1.column6) NOT IN (
SELECT column1, column2
FROM table3
WHERE table3.column3 IN ('A', 'B')/* add index for this column*/
)
AND DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE,
INTERVAL - 28 DAY)) /* need index ON this col? */ ;
任何意见/建议不胜感激。
更新:仅更新过滤顺序,查询性能提升至~28秒,添加一些索引并替换一些子查询后将在此处更新
假设您可以添加有用的索引(这将有助于某些检查(,那么也许可以尽早尝试排除行。
我怀疑您在表1上为每个列5/列6组合有相当多的行。如果您可以尽早获取其中每个行中的最新行(即,使用您加入的子查询(,那么您可以在需要检查任何非索引 WHERE 子句之前从 table1 中排除大多数行。您还可以通过对 table3 上的子查询执行进一步的连接来排除其中一些。
未经测试,但是如果我对您的数据库结构的假设是正确的,那么这可能是一个改进:-
SELECT table1.*
FROM
(
SELECT MAX(table1.id) AS max_id
FROM table1
INNER JOIN
(
SELECT DISTINCT column1, column2
FROM table3
WHERE table3.column3 IN ('A', 'B')
AND DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE, INTERVAL - 28 DAY)
) sub0_0
ON table1.column5 = sub0_0.column1
AND table1.column6 = sub0_0.column2
WHERE (table1.cloumn1 LIKE 'A%' OR table1.cloumn1 LIKE 'B%')
AND table1.cloumn2 = 'N'
AND table1.cloumn3 != 'Y'
GROUP BY table1.column5,
table1.column6
) sub0
INNER JOIN table1
ON table1.id = sub0.max_id
INNER JOIN
(
SELECT DISTINCT column1
FROM table2
WHERE column2 IN ('VALUE1', 'VALUE2')
AND (SUBSTRING(column3,6,1) = 'Y'
OR SUBSTRING(column3,25,1) = 'Y')
) sub1
ON table1.column4 = sub1.column1
(查看SHOW CREATE TABLE
可能会有所帮助。
AND DATE_FORMAT(timstampColumn, '%Y/%m/%d') > DATE_ADD(CURRENT_DATE,
INTERVAL - 28 DAY))
不能使用索引;这可能是等效的:
AND timstampColumn > CURRENT_DATE - INTERVAL 28 DAY
请提供EXPLAIN
。
您使用的是哪个版本?
它可能有助于(取决于版本(将IN ( SELECT ... )
子句转换为"派生"表:
JOIN ( SELECT ... ) ON ...
WHERE (x,y) IN ...
没有得到很好的优化。 它们是什么类型的值?
使用 *_ci 排序规则,
UPPER(LEFT(table1.cloumn1, 1)) IN ('A', 'B')
可以做到:
LEFT(table1.cloumn1, 1) IN ('A', 'B')
这不会明显帮助性能。 最好不必将列分开进行测试。
这可能使用涉及cloumn1
的索引:
table1.cloumn1 >= 'A'
AND table1.cloumn1 < 'C'
事情的顺序和在一起很少重要。 INDEX
中的顺序可以产生很大的不同。