SQL性能问题

Question

我有一个老的mysql查询，由于条目的数量突然增加，我的性能出现了问题。对于一个经验丰富的sql专家来说，主要的问题可能是显而易见的，但不幸的是，对我来说并非如此。原始查询至少运行30秒：

SET SQL_BIG_SELECTS = 1;
SET @start_date = '2018-12-31';
SET @end_date = '2019-08-14';
SET @client_id = 16;

SELECT
    L.instrument_id as iid, 
    I.instrument as instrument, 
    X.portfolio as port, 
    T.ExcludeValue as exclude_value, 
    sum(if(L.trade_type=1 and L.trade_date<=@start_date,L.nominal*PS.price*I.scale_factor,0)) as val_start, 
    sum(if(L.trade_type=1 and L.trade_date<=@end_date,L.nominal*PE.price*I.scale_factor,0)) as val_end, 
    sum(if(L.nominal>0 and L.trade_type=1 and L.trade_date>@start_date and L.trade_date<=@end_date,-L.nominal*L.price*I.scale_factor,0)) as val_buy, 
    sum(if(L.nominal<0 and L.trade_type=1 and L.trade_date>@start_date and L.trade_date<=@end_date,-L.nominal*L.price*I.scale_factor,0)) as val_sell, 
    sum(if(L.trade_type>1 and L.trade_date>@start_date and L.trade_date<=@end_date,L.nominal*L.price*I.scale_factor,0)) as val_other 
FROM ledger L, instruments I, portfolios X, prices PS, prices PE, instrument_types T
WHERE
    L.instrument_id NOT IN (95) and 
    I.instrument_type_id=T.id and 
    I.id=L.instrument_id and 
    X.id=L.portfolio_id and 
    PS.instrument_id=L.instrument_id and 
    PE.instrument_id=L.instrument_id and 
    L.is_current=1 and 
    L.trade_status_id=2 and 
    L.client_id=@client_id and 
    L.trade_date<=@end_date and 
    PS.trade_date=(select min(trade_date) from prices where instrument_id=L.instrument_id and is_current=1 and trade_date>=@start_date and trade_date<=@end_date) and 
    PE.trade_date=(select max(trade_date) from prices where instrument_id=L.instrument_id and is_current=1 and trade_date>=@start_date and trade_date<=@end_date)
GROUP BY L.instrument_id, L.portfolio_id

这个查询非常慢。我猜问题在于PS和PE的分选。它们是需要的，因为最小/最大trade_date是个别的每种仪器。

如果我将它分成两个查询，第一个在3ms内运行：

SELECT
    L.instrument_id as iid, 
    I.instrument as instrument, 
    X.portfolio as port, 
    T.ExcludeValue as exclude_value, 
    sum(if(L.trade_type=1 and L.trade_date<=@start_date,L.nominal*I.scale_factor,0)) as val_start, 
    sum(if(L.trade_type=1 and L.trade_date<=@end_date,L.nominal*I.scale_factor,0)) as val_end, 
    sum(if(L.nominal>0 and L.trade_type=1 and L.trade_date>@start_date and L.trade_date<=@end_date,-L.nominal*L.price*I.scale_factor,0)) as val_buy, 
    sum(if(L.nominal<0 and L.trade_type=1 and L.trade_date>@start_date and L.trade_date<=@end_date,-L.nominal*L.price*I.scale_factor,0)) as val_sell, 
    sum(if(L.trade_type>1 and L.trade_date>@start_date and L.trade_date<=@end_date,L.nominal*L.price*I.scale_factor,0)) as val_other 
FROM ledger L, instruments I, portfolios X, prices PS, prices PE, instrument_types T
WHERE
    L.instrument_id NOT IN (95) and 
    I.instrument_type_id=T.id and 
    I.id=L.instrument_id and 
    X.id=L.portfolio_id and 
    L.is_current=1 and 
    L.trade_status_id=2 and 
    L.client_id=@client_id and 
    L.trade_date<=@end_date 
GROUP BY L.instrument_id, L.portfolio_id

第二种是另一种价格查询，运行时间为0.3秒：

SELECT d.instrument_id, d.dt_min as dS, ps.price as pS, d.dt_max as dE, pe.price as pE 
from (select p1.instrument_id, min(p1.trade_date) as dt_min, max(p1.trade_date) as dt_max from prices p1 where p1.is_current=1 and p1.trade_date>=@start_date and p1.trade_date<=@end_date group by p1.instrument_id) d
left join prices ps on ps.trade_date=d.dt_min
left join prices pe on pe.trade_date=d.dt_max
where ps.instrument_id=d.instrument_id and pe.instrument_id=d.instrument_id

不知道如何将两者以最有效的方式组合在一起，或者即使存在一些基本问题来开始with...any帮助欣赏。

Answer 1

可以转换相关子查询的使用，如：

PS.trade_date=(select min(trade_date) from prices where instrument_id=L.instrument_id and is_current=1 and trade_date>=@start_date and trade_date<=@end_date) 

变成一个不相关的，像这样的：

(L.instrument_id, PS.trade_date) 
   IN (SELECT instrument_id, MIN(trade_date) 
       FROM prices 
       WHERE is_current=1 
           AND trade_date BETWEEN @start_date AND @end_date 
       GROUP BY instrument_id
   )

在您使用的关联版本中，对每个外部结果候选独立执行子查询；在不相关查询中，只执行一次子查询，并对照该集合检查外部查询的候选结果。

在外部查询返回几行的情况下，或者子查询数据相对庞大(如果不相关)，相关子查询可能会有帮助；但在我的经验中，在大多数情况下，不相关版本的性能通常要好得多。

使用显式联接语法，只使用一个子查询的方法更加明显：

...
INNER JOIN (
   SELECT instrument_id, MIN(trade_date) AS start_date, MAX(trade_date) AS end_date
   FROM prices 
   WHERE is_current=1 
       AND trade_date BETWEEN @start_date AND @end_date 
   GROUP BY instrument_id
) AS pb ON L.instrument_id = pb .instrument_id
INNER JOIN prices AS PS ON pb.instrument_id = PS.instrument_id AND pb.start_date = PS.trade_date
INNER JOIN prices AS PE ON pb.instrument_id = PE.instrument_id AND pb.end_date = PE.trade_date

Answer 2

在Uueerdo领导之后，查询变成：

SET SQL_BIG_SELECTS = 1;
SET @start_date = '2018-12-31';
SET @end_date = '2019-08-14';
SET @client_id = 16;

SELECT
    L.instrument_id as iid, 
    I.instrument as instrument, 
    X.portfolio as port, 
    T.ExcludeValue as exclude_value, 
    sum(if(L.trade_type=1 and L.trade_date<=@start_date,L.nominal*PS.price*I.scale_factor,0)) as val_start, 
    sum(if(L.trade_type=1 and L.trade_date<=@end_date,L.nominal*PE.price*I.scale_factor,0)) as val_end, 
    sum(if(L.nominal>0 and L.trade_type=1 and L.trade_date>@start_date and L.trade_date<=@end_date,-L.nominal*L.price*I.scale_factor,0)) as val_buy, 
    sum(if(L.nominal<0 and L.trade_type=1 and L.trade_date>@start_date and L.trade_date<=@end_date,-L.nominal*L.price*I.scale_factor,0)) as val_sell, 
    sum(if(L.trade_type>1 and L.trade_date>@start_date and L.trade_date<=@end_date,L.nominal*L.price*I.scale_factor,0)) as val_other 
FROM ledger L
LEFT JOIN instruments I ON L.instrument_id=I.id
LEFT JOIN portfolios X ON L.portfolio_id=X.id
LEFT JOIN instrument_types T on I.instrument_type_id=T.id

INNER JOIN (
   SELECT instrument_id, MIN(trade_date) AS start_date, MAX(trade_date) AS end_date
   FROM prices 
   WHERE is_current=1 
       AND trade_date>=@start_date AND trade_date<=@end_date 
   GROUP BY instrument_id
) AS pb ON L.instrument_id = pb.instrument_id
INNER JOIN prices AS PS ON pb.instrument_id = PS.instrument_id AND pb.start_date = PS.trade_date
INNER JOIN prices AS PE ON pb.instrument_id = PE.instrument_id AND pb.end_date = PE.trade_date

现在速度快很多倍..。

谢谢！