MYSQL简单移动平均值计算

Question

以下的MySql更新状态似乎需要花费过多的时间来执行提供的记录集（约5000条记录）。以下更新语句平均需要12秒才能执行。我计划对5个不同的时期和大约500个不同的股票符号运行此计算。这意味着12秒* 5次计算* 500个符号= 30,000秒或8.33小时。 更新语句：

UPDATE tblStockDataMovingAverages_AAPL JOIN 
(SELECT t1.Sequence,
   (
    SELECT AVG(t2.Close)
        FROM tblStockDataMovingAverages_AAPL AS t2
        WHERE (t1.Sequence - t2.Sequence)BETWEEN 0 AND 7
    )AS "8SMA" 
FROM tblStockDataMovingAverages_AAPL AS t1 
ORDER BY t1.Sequence) AS ma_query 
ON tblStockDataMovingAverages_AAPL.Sequence = ma_query.Sequence  
SET tblStockDataMovingAverages_AAPL.8MA_Price = ma_query.8SMA

表格设计：

CREATE TABLE `tblStockDataMovingAverages_AAPL` (
  `Symbol` char(6) NOT NULL DEFAULT '',
  `TradeDate` date NOT NULL DEFAULT '0000-00-00',
  `Sequence` int(11) DEFAULT NULL,
  `Close` decimal(18,5) DEFAULT NULL,
  `200MA_Price` decimal(18,5) DEFAULT NULL,
  `100MA_Price` decimal(18,5) DEFAULT NULL,
  `50MA_Price` decimal(18,5) DEFAULT NULL,
  `20MA_Price` decimal(18,5) DEFAULT NULL,
  `8MA_Price` decimal(18,5) DEFAULT NULL,
  `50_200_Cross` int(5) DEFAULT NULL,
  PRIMARY KEY (`Symbol`,`Sequence`),
  KEY `idxSequnce` (`Sequence`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

任何对加速该过程的帮助将不胜感激。 Select Explain 的输出:

id  select_type         table   type    possible_keys   key                 key_len ref     rows    Extra
1   PRIMARY             t1      index   NULL            idxSymbol_Sequnce   11      NULL    5205    Using index; Using filesort
2   DEPENDENT SUBQUERY  t2      ALL     NULL            NULL                NULL    NULL    5271    Using where

- user1869801

---

请包含“desc”的输出 - 尤其是查询的内部部分（select） - ethrbunny

实际上，您有三个嵌套查询，只有一个带条件（无法用于优化）。因此，MySQL必须查找5000 ** 3行（125 * 10 ** 9）。 - Oroboros102

Answer 1

这应该会好一点：

update tblStockDataMovingAverages_AAPL
join (
  select t1.sequence as sequence, avg(t2.close) as av
  from tblStockDataMovingAverages_AAPL t1
  join tblStockDataMovingAverages_AAPL t2
  on t2.sequence BETWEEN t1.sequence-7 AND t1.sequence
  group by t1.sequence
) t1 on tblStockDataMovingAverages_AAPL.sequence = t1.sequence
set 8MA_Price = t1.av

关于我的BETWEEN语句：在ON条件中，field1 OPERATOR expression(field2)比expression(field1, field2) OPERATOR expression更容易优化。我认为这也适用于BETWEEN。

你的查询中的ORDER BY似乎是不必要的，删除它可能会大大加快查询速度。

如果任何股票符号出现在同一张表中，请将它们全部放入单个更新查询中（不同期间不起作用），这可能比每个查询都运行要快得多。

如已建议的那样，向Close添加索引可能会有所帮助。

- Bernhard Barker

Answer 2

您可以通过为Close字段添加索引来稍微优化它。AVG函数应该更有效。请分享数据集的转储以更近距离地查看。

- Константин Рекунов