MYSQL递归cte查询性能缓慢

Question

我想计算一组超过30000000行的大型分钟股票数据的9分钟指数移动平均线(EMA)，其中大约有4500个不同的Ticker。由于EMA的性质，我的查询使用了递归cte，它总是基于前一行(分钟)。

代码有效，但问题出在这里。为了只计算minute_data表中总共14000行的两只股票的EMA，查询耗时19分钟40秒。假设整个数据集的每行速度一样快，那么MYSQL服务器需要60到70小时才能执行。

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=日期&每个交易分钟的时间
2. ticker=股票符号(例如特斯拉->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;

使用以下内容限制第一个select语句时：WHERE ticker = 'TOPS'到一只股票WITH语句的EXPLAIN ANALYZE函数返回以下内容(Executiontime=18min39s)：

-> 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事件)，这就足够了(而且是过度的)：

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

我强烈建议你在申请中SELECT条目并进行EMA。它将比MySQL中的OVER/CTE/存储函数等快得多。

请注意，我对PRIMARY KEY的更改将使WHERE和ORDER BY获取的行是连续的并且已经排序。而且，与OVER不同，只需要通过一次。在您的应用程序中，您可以简单地对从SELECT接收到的阵列进行一次遍历。

今天你有"只有2只股票"；；明天你会有3个。对每只股票进行一次选择并计算其EMA。也就是说，循环浏览应用程序中的2种以上股票。

另一个想法。。。在每小时结束时(包括一天结束时)存储EMA。然后，当你想重新计算它时，从那里开始；不会损失精度，但要做的计算要少得多。

将这些小时EMA存储在单独的表中。该值非常简单地对EMA算法进行素数运算。现在你看到的是类似的东西

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

该CONCAT计算当前小时的开始(作为字符串)，然后使用它与t(DATETIME)进行比较。再次，我的PK和"；一次做一个股票行情；建议适用。