我必须在mysql的数据集上计算移动平均值（不同时段）。我尝试了两种方法来计算平均值，但这两种方法都需要相当长的时间。共享下面的代码。
method:-1
select t1.*,
(select avg(t2.last_price)
from temp_data t2
where t2.rownum>t1.rownum-50 and t2.rownum<=t1.rownum and t1.script_code=t2.script_code) as 'ma_small_price'
from temp_data t1;method:-2
select t1.*, avg(t2.last_price) 'ma_small_price'
from temp_data t1
join temp_data t2
where t2.rownum>t1.rownum-50 and t2.rownum<=t1.rownum and t1.script_code=t2.script_code
group by t1.id,t1.date, t1.time;这是表结构：
CREATE TABLE `temp_data` (
`id` int(11) NOT NULL DEFAULT '0',
`rownum` int(11) DEFAULT NULL,
`script_code` float DEFAULT NULL,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
`last_price` float DEFAULT NULL,
`last_qty` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;rownum是具有连续行号的列。id是主键，但不是连续的，所以我必须添加一个单独的列
示例的链接data:https://www.dropbox.com/s/z8iacqvlkjdx6ax/temp_data_sample.xlsx?dl=0
接下来，我必须并行计算同一数据上的多个移动平均值，但是周期（在上面的代码中指定为50）是不同的。
我的数据集非常庞大，而且还在不断增长（>100万行），运行这些查询所需的时间非常长，每个查询大约需要20分钟。寻找关于如何改进这些查询以减少运行时间的输入。谢谢！！
2条答案
按热度按时间kulphzqa1#
可以。首先，由于只有1百万排，这应该不需要20分钟。大概20秒。如果rownum列是唯一的，则应将其作为唯一键进行索引。它还应该是一个无符号的int，这样做会大大缩短查询时间，因为现在似乎您正在对每个连接进行一次完整的未排序表扫描。
其次，除非对于比较大量历史数据的数据库来说有一些不明显的原因，否则应该使用isam表，而不是innodb。
第三，脚本代码必须被索引，否则你将做一个完整的表扫描。
更多：*方法2中的join语句是将每一行连接到每一行，然后执行where。您应该在rownum>t1.rownum-50和rownum<=t1.rownum上留下join，而不是执行常规join然后运行where。这将大大加快查询速度，即使没有索引rownum。*如果需要更多的数据，还应该考虑基于rownum对表进行分区。分区可以很好地加速这些类型的读取，其中您正在访问的大多数数据是连续的，并且将落在一个或两个分区中。在您的例子中，您还可以按日期进行分区，这对于其他操作可能很方便。*查看explain select并查看连接上使用了哪些键。考虑使用use index提示来使用rownum而不是连接的主键。
从本质上看，您的两个查询似乎都不正确。一旦完成了上述优化，我的猜测是，如果没有方法2中的where，方法1（子查询）仍然比正确的join-on快。
此时，应该使用explain select查看每个查询中正在执行的操作。它将向您显示有多少行正在被读取和连接，以及使用了哪些索引，帮助您缩小未索引连接的问题范围。
2nbm6dog2#
很好的问题挑战是通过迭代将每一行分组并跳转，因此我们需要定义一个开始周期和一个结束周期，并在这些周期之间连接同一个表
因为table太大，我加了order by和limit
我还将向rownum列添加索引，以使联接和组运行得更快
希望有帮助