mariadb(mysql)慢速查询修复

Question

SELECT 
CAST(`a`.`selldate` AS DATE) AS `regdate`,
`a`.`market` AS `market`,
`a`.`user_seq` AS `user_seq`, 
COUNT(0) AS `complete`,
ROUND(SUM(`a`.`sell`) - SUM(`a`.`buy`) - SUM(`a`.`buy_fee`) - SUM(`a`.`sell_fee`),8) AS `profit`, 
ROUND(SUM(`a`.`buy`),8) AS `price`, 
ROUND(SUM(`a`.`sell_fee`),8) AS `sell_fee`, 
ROUND(SUM(`a`.`buy_fee`),8) AS `buy_fee`,
(
SELECT SUM(`yangpago`.`funding`.`price`) AS `funding_fee`
FROM `yangpago`.`funding`
WHERE 
DATE_FORMAT(FROM_UNIXTIME(`yangpago`.`funding`.`time`),'%Y-%m-%d') = CAST(`a`.`selldate` AS DATE) 
AND 
`yangpago`.`funding`.`user_seq` = `a`.`user_seq`            
GROUP BY `regdate`,`yangpago`.`funding`.`user_seq`
) AS `funding_fee`
FROM (
SELECT 
`v_order`.`cnt` AS `cnt`,
`v_order`.`market` AS `market`,
`v_order`.`user_seq` AS `user_seq`,
`v_order`.`selldate` AS `selldate`,
`v_order`.`sell_price` * `v_order`.`sell_amount` AS `sell`,
`v_order`.`buy_price` * `v_order`.`buy_amount` AS `buy`,
`v_order`.`sell_price` * `v_order`.`sell_amount` * `f`.`sell_fee` AS `sell_fee`,
`v_order`.`buy_price` * `v_order`.`buy_amount` * `f`.`buy_fee` AS `buy_fee`
FROM (`yangpago`.`v_order`
JOIN `yangpago`.`fee` `f` 
ON (
`f`.`market` = `v_order`.`market` 
AND 
`f`.`user_seq` = `v_order`.`user_seq` 
AND 
CAST(`v_order`.`selldate` AS DATE) BETWEEN `f`.`startdate` 
AND 
IFNULL(`f`.`enddate`, CURRENT_TIMESTAMP())
)
)
WHERE `v_order`.`state` = 'COMPLETE') `a`
GROUP BY CAST(`a`.`selldate` AS DATE),`a`.`market`,`a`.`user_seq`

这是我的余额视图查询。

这个查询速度非常慢。运行时间(1~2s(

我尝试资金子查询删除和检查时间0.2s~0.3s

所以，也删除联接查询。检查时间0.1s

所有和列删除，时间是0.004s。我想要0.01s以下。此查询结果。

如何修复？

Answer 1

实际上，您读取数据两次，一次创建虚拟表，然后再次聚合-虚拟表没有必要考虑重构代码

SELECT 
CAST(v_order.selldate AS DATE) AS regdate,
v_order.market  AS market,
v_order.user_seq AS user_seq, 
COUNT(0) AS complete,
sum(v_order.sell_price * v_order.sell_amount) -
SUM(v_order.buy_price * v_order.buy_amount) -
SUM(v_order.buy_price * v_order.buy_amount * f.buy_fee) -
SUM(v_order.sell_price * v_order.sell_amount * f.sell_fee) as profit,
SUM(v_order.buy_price * v_order.buy_amount) AS price, 
SUM(v_order.sell_price * v_order.sell_amount * f.sell_fee) AS sell_fee, 
SUM(v_order.buy_price * v_order.buy_amount * f.buy_fee) AS buy_fee,
(SELECT SUM(funding.price) AS funding_fee
FROM funding
WHERE DATE_FORMAT(FROM_UNIXTIME(funding.time),'%Y-%m-%d') = CAST(a.selldate AS DATE) 
AND  funding.user_seq = v_order.user_seq           
) AS funding_fee
FROM v_order
JOIN fee f ON  f.market = v_order.market 
AND f.user_seq = v_order.user_seq 
AND  CAST(v_order.selldate AS DATE) BETWEEN f.startdate 
AND  coalesce(f.enddate, CURRENT_TIMESTAMP())


WHERE v_order.state = 'COMPLETE'
GROUP BY regdate,market,user_seq;