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

移除所有sum列，时间为0.004s。我想要0.01美元以下。此查询结果。

如何修复它？

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;