mysql平滑访问_MySQL数据平滑

更新2：

进一步改进了查询但仍然很慢。我意识到TIMESTAMPDIFF比UNIX_TIMESTAMP之间的直接比较要慢得多。因此，像这样更改UPDATE 1的代码，我们可以获得近20%的速度提升。

增加my.cnf中的innodb_buffer_pool_size选项有助于提高速度。

SELECT `date` , `mem_used` , `mem_total` , `mem_5_min_avg` ,

(`mem_5_min_avg` / `mem_total`) AS mem_usage_rate

FROM (

SELECT *, (

SELECT CEILING( AVG( mem_used ) )

FROM `data` AS t2

WHERE UNIX_TIMESTAMP(t2.date) - UNIX_TIMESTAMP(t1.date) <=300

AND t2.date >= t1.date

AND t1.mem_total = t2.mem_total

AND t1.host_id = t2.host_id

) AS mem_5_min_avg

FROM `data` AS t1

) AS t1

更新1：

我改进了查询以提供两倍的速度，但对于我的大表来说它仍然很慢。

SELECT `date` , `mem_used` , `mem_total` , `mem_5_min_avg` ,

(`mem_5_min_avg` / `mem_total`) AS mem_usage_rate

FROM (

SELECT *, (

SELECT CEILING( AVG( mem_used ) )

FROM `data` AS t2

WHERE TIMESTAMPDIFF(

MINUTE , t1.date, t2.date ) <=5

AND t2.date >= t1.date

AND t1.mem_total = t2.mem_total

) AS mem_5_min_avg

FROM `data` AS t1

) AS t1

初始发布

我在ubuntuforums中提出了同样的问题，TeoBigusGeekus给出了这个答案，它的工作原理与它必须正常工作一样，但对于我拥有更多100000行的大表来说，这个问题非常缓慢。如果我将查询限制为30行并且超过20秒，如果我将其限制为100，则需要7.5秒才能执行。我想这将需要永久的100000行。无论如何，对于有兴趣寻求解决方案的人来说，它是：

SELECT `date` , `mem_used` , `mem_total` , (

SELECT CEILING( AVG( mem_used ) )

FROM mytable AS t2

WHERE TIMESTAMPDIFF(

MINUTE , t1.date, t2.date ) <=5

AND t2.date >= t1.date

AND t1.mem_total = t2.mem_total

) AS mem_5_min_avg, (

SELECT CEILING( AVG( mem_used ) ) / mem_total

FROM mytable AS t3

WHERE TIMESTAMPDIFF(

MINUTE , t1.date, t3.date ) <=5

AND t3.date >= t1.date

AND t1.mem_total = t3.mem_total

) AS mem_rate_usage

FROM mytable AS t1