mysql - 如何减少mysql过程中处理数据的时间

Question

谢谢论坛...请帮助我...我有一个包含 TagName 的表，另一个表包含 taglog .im 将标签名称传递给名为 GetAvg 的过程，它将返回所有标签的平均值。它运行良好，但显示 100 个标签值大约需要 35 秒。如何减少时间。请帮助我成为数据库新手。

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAvg`(IN FromTime datetime, IN ToTime   datetime)
BEGIN
DECLARE  no_more_alarms INT DEFAULT 0;
DECLARE  TempTagName VARCHAR(45);
DECLARE  val FLOAT;
DECLARE  cur_tag CURSOR FOR
      select Tag_AVG from Report
      where(Tag_AVG IS NOT NULL );
      DECLARE  CONTINUE HANDLER FOR NOT FOUND
      SET  no_more_alarms = 1;


DROP TABLE IF EXISTS `tempAVG`;

CREATE  TABLE tempAVG (
val FLOAT
);


OPEN  cur_tag;

FETCH  cur_tag INTO TempTagName;
REPEAT

SELECT AVG(value) INTO val
FROM jas_taglog
WHERE ((TagId = (select TagId from jas_tags where jas_tags.Name = TempTagName)) AND jas_taglog.LogTime between FromTime and ToTime) ;

INSERT  INTO tempAvg(Val)
VALUES  (val);

FETCH cur_tag INTO TempTagName;
UNTIL  no_more_alarms = 1
END REPEAT;
CLOSE  cur_tag;
SELECT *  FROM tempAVG;

END

Answer 1

您正在手动实现可以在单个查询中编写的循环:

...
BEGIN
    INSERT  INTO tempAvg
    SELECT AVG(jas_taglog.value)
    FROM jas_taglog
    JOIN jas_tags USING (TagId)
    JOIN Report ON jas_tags.Name = Tag_AVG
    WHERE jas_taglog.LogTime BETWEEN FromTime AND ToTime
    GROUP BY Tag_AVG;
END