mysql - 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 都是单独的。

如果我将其分成 2 个查询，第一个查询将在 3 毫秒内运行:

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

不确定如何以最有效的方式将两者结合在一起，或者即使它们一开始就存在一些基本问题...任何帮助表示赞赏。

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