MySQL递归CTE查询的性能缓慢

Question

我想计算一组超过30,000,000行的大量分钟数量的9分钟指数移动平均线（EMA），大约有4,500个不同的股票。 由于EMA的性质，我使用递归CTE的查询始终基于上一行（分钟）。

该代码有效，但这是问题所在。为了计算仅两张股票的EMA，总计构成了14,000行的分钟桌，查询占40分钟的时间。假设整个数据集的每行也一样快，则将MySQL-Server在60h和70h之间进行执行。

minute_data表的构建如下：

create table min_data
(
    t            datetime       not null,
    ticker       varchar(10)    not null,
    o            decimal(10, 4) not null,
    h            decimal(10, 4) not null,
    l            decimal(10, 4) not null,
    c            decimal(10, 4) not null,
    primary key (t, ticker)
);

我只使用以下列：

1. t = date＆amp;每次交易分钟
2. tricker的时间=股票符号（例如tesla - ＆gt; tsla）
3. c =每个交易的关闭价格

EMA计算：
ema =收盘价（当前分钟） * alpha + ema（前分钟） *（1-alpha）

SET GLOBAL cte_max_recursion_depth=1000000;

SET @alpha = 2 / (1 + 9);

CREATE TABLE min_data_EMA9 AS
WITH RECURSIVE t AS (
    SELECT t, ticker,
           row_number() over (partition by ticker order by t) as QuoteId,
           c
    FROM min_data
),

ema (t, ticker, QuoteId, c, EMA9) AS (
    SELECT *, avg(c) as EMA9
    FROM t
    WHERE QuoteId between 1 and 8
    GROUP BY ticker

    UNION ALL

    SELECT t2.t,
           t2.ticker,
           t2.QuoteId,
           t2.c,
           @alpha * t2.c + (1 - @alpha) * EMA9 as EMA9
    FROM ema
    JOIN t t2
        ON ema.QuoteId = t2.QuoteId - 1
        AND  ema.ticker = t2.ticker
)

SELECT t, ticker, QuoteId, EMA9
FROM ema;

在以以下方式限制第一个选择语句时 其中ticker ='tops' to一个单股用语句返回以下（executionTime = 18min 39s）

-> Table scan on ema  (cost=0.01..37822.72 rows=3025619) (actual time=0.002..4.562 rows=68471 loops=1)
    -> Materialize recursive CTE ema  (cost=1395569.12..1433391.84 rows=3025619) (actual time=1097987.024..1097996.206 rows=68471 loops=1)
        -> Table scan on <temporary>  (actual time=0.001..0.001 rows=1 loops=1)
            -> Aggregate using temporary table  (actual time=30081.632..30081.633 rows=1 loops=1)
                -> Filter: (t.QuoteId between 1 and 8)  (cost=1.01..306376.90 rows=302562) (actual time=30069.843..30081.576 rows=8 loops=1)
                    -> Table scan on t  (cost=2.50..2.50 rows=0) (actual time=0.001..3.723 rows=68471 loops=1)
                        -> Materialize CTE t if needed  (cost=2.50..2.50 rows=0) (actual time=30069.836..30078.154 rows=68471 loops=1)
                            -> Window aggregate: row_number() OVER (PARTITION BY min_data.ticker ORDER BY min_data.t )   (actual time=29997.654..30020.406 rows=68471 loops=1)
                                -> Sort: min_data.ticker, min_data.t  (cost=2861564.76 rows=27233289) (actual time=29997.639..30002.597 rows=68471 loops=1)
                                    -> Filter: (min_data.ticker = 'TOPS')  (cost=2861564.76 rows=27233289) (actual time=0.512..29953.891 rows=68471 loops=1)
                                        -> Table scan on min_data  (cost=2861564.76 rows=27233289) (actual time=0.510..27585.660 rows=30323912 loops=1)
        -> Repeat until convergence
            -> Nested loop inner join  (cost=1093007.22 rows=3025619) (actual time=0.010..533731.050 rows=34235 loops=2)
                -> Filter: (ema.ticker is not null)  (cost=34040.61 rows=302561) (actual time=0.004..50.239 rows=34236 loops=2)
                    -> Scan new records on ema  (cost=34040.61 rows=302561) (actual time=0.003..26.824 rows=34236 loops=2)
                -> Filter: (ema.QuoteId = (t2.QuoteId - 1))  (cost=0.25..2.50 rows=10) (actual time=7.784..15.587 rows=1 loops=68471)
                    -> Index lookup on t2 using <auto_key0> (ticker=ema.ticker)  (actual time=0.004..7.431 rows=68471 loops=68471)
                        -> Materialize CTE t if needed (query plan printed elsewhere)  (cost=0.00..0.00 rows=0) (never executed)

：新的递归CTE和一定的扩展也可以查询优化。因此，我将为您提供有关如何使此查询更快的建议！

Answer 1

由于您一次专注于一个股票，所以这可能会好多了：

PRIMARY KEY(ticker, t)

您是在第一个读取EMA并阅读到最后一次吗？并且不保存任何中间结果吗？您使用的因素是什么？根据因素的不同，您实际上不需要以超过最后一百个左右的值进行计算。

即使没有任何变化，您是否也存储每分钟的收盘价？

假设市场永远不会在一个漫长的周末（即，没有9/11打ic）上关闭，这应该足够（并且过度杀伤）：

WHERE ticker = ?
  AND t > NOW() - INTERVAL 4 DAYS
ORDER BY t

我强烈建议您选择 参赛作品并进行EMA在您的申请中。它的速度比< / code> / cte / cte /存储的函数 /等要快得多。

请注意，我更改主键将使和订购订购的行将是连续订购的。而且，与 的不同，只需要进行一次通行证。在您的应用程序中，您只需从选择中收到的数组进行一次通过即可。

今天，您只有2个股票；明天您将有3个。为每个股票做一个选择并计算其EMA。也就是说，循环通过应用程序内的2个以上的股票。

另一个想法...在每个小时结束时（包括一天的结束）存储EMA。然后，当您想重新计算它时，请从那里捡起；不会丢失精度，但要做的计算要少得多。

将每小时的EMA存放在单独的桌子中。 EMA算法的价值非常简单。现在，您正在查看类似

WHERE ticker = ?
  AND t > CONCAT(LEFT(NOW(), 13), ":00:00")
ORDER BY t

concat的内容计算当前小时的开始（作为字符串），然后使用该contecat以与t（a dateTime）进行比较。同样，我的PK和“一次做一个股票”建议适用。