mysql - 需要加快MySQL查询

Question

我有一个查询，用于获取各种输入以计算 Assets 排名。为了根据用户输入获取各种值来计算 Assets 排名，我对单个表使用了多个子查询。但这花费了太多时间。谁能帮我即兴创作这个查询？

 SELECT AssetId,
       AssetName,
       Isin,

  (SELECT DClose
   FROM eod_data
   WHERE Isin=a.Isin
     AND DDate >= now()-interval 12 MONTH
   ORDER BY DDate ASC LIMIT 1) AS rafval,

  (SELECT DClose
   FROM eod_data
   WHERE Isin=a.Isin
     AND DDate >= now()-interval 12 MONTH
   ORDER BY DDate DESC LIMIT 1) AS ralval,

  (SELECT DClose
   FROM eod_data
   WHERE Isin=a.Isin
     AND DDate >= now()-interval 3 MONTH
   ORDER BY DDate ASC LIMIT 1) AS rbfval,

  (SELECT DClose
   FROM eod_data
   WHERE Isin=a.Isin
     AND DDate >= now()-interval 3 MONTH
   ORDER BY DDate DESC LIMIT 1) AS rblval,

  (SELECT DClose
   FROM eod_data
   WHERE Isin=a.Isin
     AND DDate >= now()-interval 20 DAY
   ORDER BY DDate ASC LIMIT 1) AS rcfval,

  (SELECT DClose
   FROM eod_data
   WHERE Isin=a.Isin
     AND DDate >= now()-interval 20 DAY
   ORDER BY DDate DESC LIMIT 1) AS rclval,

  (SELECT STD(DClose)
   FROM eod_data
   WHERE Isin=a.Isin
     AND DDate >= now()-interval 20 DAY
   ORDER BY DDate DESC LIMIT 1) AS vstd
FROM assets a
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
  AND a.IsActive=1;

在上面的查询中，子查询中提到的所有区间都是变量。它们来自用户输入。 Assets 表包含 1000 项 Assets ，而 eod_data 将包含数百万条记录。

我还为 where 子句中涉及的所有字段创建了索引。

表格结构 Assets :AssetId、AssetName、Isin、IsActive |eod_data: Isin,DClose,DDate

上述查询花费了将近 11 分钟。

提前致谢。

请在此处找到示例数据库 http://sqlfiddle.com/#!9/d0a50/3

Answer 1

这是我的下一次尝试。我测试了一些不同的连接。这是最快的方法(快 1400 倍)。 STD() 列暂时未实现。你能检查一下其他输出是否正确吗

..和 STD() 的决赛(我希望)。

SELECT 
    a.AssetId
    , a.AssetName
    , a.Isin
    , CAST(COALESCE(ed2.DClose,0) AS DECIMAL(20,4)) AS rafval
    , CAST(COALESCE(ed3.DClose,0) AS DECIMAL(20,4)) AS ralval
    , CAST(COALESCE(ed4.DClose,0) AS DECIMAL(20,4)) AS rbfval
    , CAST(COALESCE(ed5.DClose,0) AS DECIMAL(20,4)) AS rblval
    , CAST(COALESCE(ed6.DClose,0) AS DECIMAL(20,4)) AS rcfval
    , CAST(COALESCE(ed7.DClose,0) AS DECIMAL(20,4)) AS rclval
    , COALESCE(ed.vstd,0) AS vstd
FROM (
    SELECT  
    ed.Isin
        , MIN(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_rafval
        , MAX(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_ralval
        , MIN(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rbfval
        , MAX(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rblval
        , MIN(IF( DDate >= now()-INTERVAL 40 DAY  , EodDataId, NULL)) AS id_rcfval
        , MAX(IF( DDate >= now()-INTERVAL 40 DAY  , EodDataId, NULL)) AS id_rclval
        , std(IF( DDate >= now()-INTERVAL 40 DAY  , NULL, DClose )) AS vstd
    FROM eod_data ed
    WHERE ed.DDate >= now()-INTERVAL 12 MONTH
    GROUP BY ed.Isin
    ORDER BY ed.EodDataId ASC
    ) ed
LEFT JOIN eod_data ed2 ON ed2.Isin = ed.Isin AND ed2.EodDataId = ed.id_rafval
LEFT JOIN eod_data ed3 ON ed3.Isin = ed.Isin AND ed3.EodDataId = ed.id_ralval
LEFT JOIN eod_data ed4 ON ed4.Isin = ed.Isin AND ed4.EodDataId = ed.id_rbfval
LEFT JOIN eod_data ed5 ON ed5.Isin = ed.Isin AND ed5.EodDataId = ed.id_rblval
LEFT JOIN eod_data ed6 ON ed6.Isin = ed.Isin AND ed6.EodDataId = ed.id_rcfval
LEFT JOIN eod_data ed7 ON ed7.Isin = ed.Isin AND ed7.EodDataId = ed.id_rclval
INNER JOIN assets a ON a.Isin = ed.Isin
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
  AND a.IsActive=1
ORDER BY a.AssetId;
SELECT 
    a.AssetId
    , a.AssetName
    , a.Isin
    , CAST(COALESCE(ed2.DClose,0) AS DECIMAL(20,4)) AS rafval
    , CAST(COALESCE(ed3.DClose,0) AS DECIMAL(20,4)) AS ralval
    , CAST(COALESCE(ed4.DClose,0) AS DECIMAL(20,4)) AS rbfval
    , CAST(COALESCE(ed5.DClose,0) AS DECIMAL(20,4)) AS rblval
    , CAST(COALESCE(ed6.DClose,0) AS DECIMAL(20,4)) AS rcfval
    , CAST(COALESCE(ed7.DClose,0) AS DECIMAL(20,4)) AS rclval
FROM (
    SELECT  
    ed.Isin
        , MIN(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_rafval
        , MAX(IF( DDate >= now()-INTERVAL 12 MONTH, EodDataId, NULL)) AS id_ralval
        , MIN(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rbfval
        , MAX(IF( DDate >= now()-INTERVAL  3 MONTH, EodDataId, NULL)) AS id_rblval
        , MIN(IF( DDate >= now()-INTERVAL 20 DAY  , EodDataId, NULL)) AS id_rcfval
        , MAX(IF( DDate >= now()-INTERVAL 20 DAY  , EodDataId, NULL)) AS id_rclval
    FROM eod_data ed
    WHERE ed.DDate >= now()-INTERVAL 12 MONTH
    GROUP BY ed.Isin
    ORDER BY ed.DDate ASC
    ) ed
LEFT JOIN eod_data ed2 ON ed2.Isin = ed.Isin AND ed2.EodDataId = ed.id_rafval
LEFT JOIN eod_data ed3 ON ed3.Isin = ed.Isin AND ed3.EodDataId = ed.id_ralval
LEFT JOIN eod_data ed4 ON ed4.Isin = ed.Isin AND ed4.EodDataId = ed.id_rbfval
LEFT JOIN eod_data ed5 ON ed5.Isin = ed.Isin AND ed5.EodDataId = ed.id_rblval
LEFT JOIN eod_data ed6 ON ed6.Isin = ed.Isin AND ed6.EodDataId = ed.id_rcfval
LEFT JOIN eod_data ed7 ON ed7.Isin = ed.Isin AND ed7.EodDataId = ed.id_rclval
INNER JOIN assets a ON a.Isin = ed.Isin
INNER JOIN assetclasses ac ON ac.AssetClassId=a.AssetClassId
INNER JOIN assetsubgroups asg ON asg.AssetSubGroupId=ac.AssetSubGroupId
WHERE asg.AssetGroupId=1
  AND a.IsActive=1
ORDER BY a.AssetId;

你能再测试一下吗？