如何使用重复子查询优化大型查询 [英] How to optimize huge query with repeated subqueries
本文介绍了如何使用重复子查询优化大型查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下包含重复子查询的巨大查询，对我来说它看起来效率很低.我该如何优化它?
I have the following huge query that contains repeated subqueries , It looks really inefficient to me. How can i optimize it ?
SELECT T2.date1, T2.date2, T2.period, T1.market, T1.ticker, 0 AS scenario
FROM
(SELECT DISTINCT
Q.market AS market,
Q.ticker AS ticker
FROM portfolio.scenario S RIGHT JOIN portfolio.quote Q
ON S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
Q.market = S.market AND
Q.ticker = S.ticker
WHERE Q.date = '2010-07-01' AND
S.date1 IS NULL) AS T1
JOIN
(SELECT DISTINCT S.date1, S.date2, S.period
FROM scenario S
WHERE S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01') AS T2
UNION
SELECT S.date1 AS date1,
S.date2 AS date2,
S.period AS period,
Q.market AS market,
Q.ticker AS ticker,
Q.close * EXP(S.ratio) AS scenario
FROM portfolio.scenario S , portfolio.quote Q
WHERE S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
Q.date = '2010-07-01' AND
Q.market = S.market AND
Q.ticker = S.ticker
UNION
SELECT T2.date1, T2.date2, T2.period, T1.market, T1.ticker, 0 AS scenario
FROM
(SELECT DISTINCT
Q.market AS market,
Q.ticker AS ticker
FROM portfolio.scenario S , portfolio.quote Q
WHERE Q.date = '2010-07-01' AND
Q.market = S.market AND
Q.ticker = S.ticker AND
S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' ) AS T1
JOIN
(SELECT DISTINCT S.date1, S.date2, S.period
FROM scenario S
WHERE S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01') AS T2
WHERE (T2.date1, T2.date2, T2.period, T1.market, T1.ticker)
NOT IN (SELECT S.date1 AS date1,
S.date2 AS date2,
S.period AS period,
Q.market AS market,
Q.ticker AS ticker
FROM portfolio.scenario S , portfolio.quote Q
WHERE Q.date = '2010-07-01' AND
Q.market = S.market AND
Q.ticker = S.ticker AND
S.series = (SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1) AND
S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' )
ORDER BY
date1,date2,period,market,ticker
在@Bruce 的评论和一些减少子查询的逻辑之后，我现在的查询是:
After @Bruce 's comment and some logic to reduce a subquery my query now is:
(SELECT S.date1,
S.date2,
S.period,
Q.market,
Q.ticker,
Q.close * EXP(S.ratio) AS scenario
FROM portfolio.scenario S , portfolio.quote Q
WHERE
S.date1 >= (@date1 := '2009-09-01') AND
S.date2 <= (@date2 := '2010-07-01') AND
Q.date = (@qdate := '2010-07-01') AND
S.series =
(@series :=
(SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1)) AND
Q.market = S.market AND
Q.ticker = S.ticker)
UNION
(SELECT T2.date1, T2.date2, T2.period, T1.market, T1.ticker, 0 AS scenario
FROM
(SELECT Q.market, Q.ticker
FROM quote Q
WHERE Q.date = @qdate) AS T1
JOIN
(SELECT DISTINCT S.date1, S.date2, S.period
FROM scenario S
WHERE S.series = @series AND
S.date1 >= @date1 AND
S.date2 <= @date2) AS T2
WHERE (T2.date1, T2.date2, T2.period, T1.market, T1.ticker)
NOT IN
(SELECT S.date1,
S.date2,
S.period,
Q.market,
Q.ticker
FROM portfolio.scenario S , portfolio.quote Q
WHERE Q.date = @qdate AND
Q.market = S.market AND
Q.ticker = S.ticker AND
S.series = @series AND
S.date1 >= @date1 AND
S.date2 <= @date2 ))
但是，如果我改变了
(@series :=
(SELECT S.series
FROM scenario S
WHERE S.date1 >= '2009-09-01' AND
S.date2 <= '2010-07-01' AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1))
成为
(@series :=
(SELECT S.series
FROM scenario S
WHERE S.date1 >= @date1 AND
S.date2 <= @date2 AND
S.period = 'QUARTER'
ORDER BY S.date2
LIMIT 1))
处理它需要太多时间(我在 10 分钟前执行了查询，但仍然没有得到结果)而查询通常在 5 秒内返回.
It takes too much time to process it (i have executed the query 10 mins ago and still did not get the result) while the query normally returns in 5 seconds.
另外，当我重置变量时，执行结果不正确(可能使用之前执行的变量值).我如何在不添加 SET 语句的情况下更改它(我希望它是单个查询)
Also when i reset the variables , execute the result is not correct (probably use the variable's value from the previous execution). How can i change that without adding SET statements (I would like it to be a single query)
推荐答案
使用 MySQL 变量:
Use MySQL variables:
SELECT
@x := ColumnName,
@y := ColumnName2 + @z,
@z := (SELECT * FROM SubTable WHERE x = @x),
(SELECT * FROM Table2 WHERE X = @z),
(SELECT * FROM Table3 WHERE X = @z)
FROM Table
WHERE
v = @v
- 您可以将子选择和列值分配给 SQL 变量
- 您可以在语句中的任何位置引用这些变量
- 变量包含前几行的值(如果已设置)
- 您可以通过这种方式重用子选择和其他值
这篇关于如何使用重复子查询优化大型查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
