MySQL 堆栈过程性能修复，SELECT

Question

我需要运行 MYSQL 过程，该过程将从表 pointValues 中选择单个点的时间序列记录。当然，记录数量可能会很大 - 所以我只需要选择其中 200(限制)来绘制图表。我决定根据以下逻辑划分所有记录:

a) 记录/(limit/2) -> 每组中的行数
b) 从 a) 中定义的每个组中获取最小值和最大值。

1. 我在高性能查询方面没有太多经验，因此我需要一些帮助来提高此过程的性能。

   CREATE TABLE secChart 
   (
        id int(11) NOT NULL,
        dataPointId int(11) NOT NULL,
        dataType int(11),
        pointValue DOUBLE NOT NULL,
        ts bigint(20) NOT NULL 
   ) ENGINE=InnoDB;
   
   DROP PROCEDURE dataChart;
   DROP PROCEDURE IF EXISTS dataChart;
   
   DELIMITER //
   
   CREATE PROCEDURE dataChart(iter int, step int, pointId int, setStart int, 
   

   setStop int) 开始 截断表 secChart；

       SET @i = 0;
       SET @iter = iter;
       SET @pointId = pointId; myLoop: 
   
       WHILE (@i < @iter) 
       DO 
            IF @i = 0 THEN
               SET setStart = 0;
               SET setStop = step-1; 
            END IF; 
   
            IF @i > 0 THEN
               SET setStart = @i * step;
               SET setStop = setStart + (step-1);
               SET @start = setStart;
               SET @stop = setStop; 
            END IF; **
   
            INSERT INTO secChart
                (SELECT *
                 FROM pointvalues
                 WHERE dataPointId = @pointId
                   AND (pointValue = (SELECT MIN(pointValue)
                                      FROM 
                                          (SELECT *
                                           FROM flex2.pointvalues
                                           WHERE dataPointId = @pointId
                                           ORDER BY id ASC
                                           LIMIT setStart, setStop) AS b)
                        OR pointValue = (SELECT MAX(pointValue)
                                         FROM
                                             (SELECT *
                                              FROM flex2.pointvalues
                                              WHERE dataPointId = @pointId
                                              ORDER BY id ASC
                                              LIMIT setStart, setStop) AS b2))
                ORDER BY id
                LIMIT 0, 2);**
   
        SET @i = @i + 1; 
   
        IF @i > @iter 
        THEN 
            LEAVE myLoop; 
        END IF; 
   END WHILE; 
   END//
   DELIMITER ;
   
   CALL dataChart(100, 80, 1, 0, 0);
   

   对于近 15,000 条记录，需要 158 秒...

我测试的另一个选择:

INSERT INTO idx
VALUES(@start, @stop , @i, step);

INSERT INTO stt
    (SELECT * 
     FROM
         ((SELECT * 
           FROM
               (SELECT id, pointValue, ts
                FROM flex2.pointvalues AS pv
                WHERE pv.dataPointId = 1
                ORDER BY id
                LIMIT setStart, setStop) AS minval
           ORDER BY pointValue DESC
           LIMIT 0, 1)
          UNION
           (SELECT *  
            FROM
                (SELECT id, pointValue, ts
                 FROM flex2.pointvalues AS pv
                 WHERE pv.dataPointId = 1
                 ORDER BY id
                 LIMIT setStart, setStop) AS maxval
            ORDER BY pointValue ASC
            LIMIT 0, 1)) AS selectScore);

对于近 15,000 条记录，需要 58 秒 - 更快，但还不够快。

第三个想法是选择 n 行(例如 12 000 行中的 200 行)

SELECT COUNT(*) 
FROM flex2.pointvalues 
WHERE dataPointId = 1 
  AND id IN (SELECT id 
             FROM flex2.pointvalues 
             WHERE dataPointId = 1 
               AND id BETWEEN 
                           (SELECT MIN(id) FROM flex2.pointvalues 
                            WHERE dataPointId = 1) AND 
                           (SELECT MAX(id) FROM flex2.pointvalues 
                            WHERE dataPointId = 1)) 
              AND id % 10 = 0;

最好是修复想法 2 的性能。请帮忙!

Answer 1

首先是一些问题和评论。

• 您想从时间序列数据集中选取均匀间隔的点吗？
• 您正在绘制“烛台”(因此是最小值和最大值)吗？
• 不要使用任何类型的循环；会很慢。
• 目标是通过单个SELECT(无循环)来获取所有所需的项目。 SQL 已针对此进行了优化。
• 为什么表中有 5 列而不是简单的 2 列(代表 x 和 y，又名 ts 和值)？
• 您希望图表基于时间还是基于表中的索引？ 将会存在数据缺失的情况，因此您将会希望将图表基于时间。
• 避免OFFSET(即LIMIT m,n)，它必须扫描所有前面的行；因此很慢。

让我们退后一步。让我们首先考虑使用 AVG 而不是 MIN 和 MAX 的方法，而不是获取烛台。一旦掌握了这一点，也许就可以制作烛台了。

SELECT FLOOR(ts / 300000) AS '5-minute-intervals',  -- see below
       AVG(value)
    FROM tbl
    WHERE ts ...   -- limit the time span
    GROUP BY 1     -- shorthand, referring to the FLOOR(..)

300000 假定为毫秒 ts(Java 语言)。您根据时间跨度和“200...”讨论预先计算了该数字。

这就是全部内容。

现在，对于烛台:

SELECT FLOOR(ts / 300000) AS '5-minute-intervals',
       MIN(value),
       MAX(value)
    FROM tbl
    WHERE ts ...   -- limit the time span
    GROUP BY 1     -- shorthand, referring to the FLOOR(..)

然后你的图表程序需要获取最小值和最大值，并以某种方式将其变成一条垂直线。如果您确实想要某些百分位数而不是最小值和最大值，那就会变得非常困惑。

获取间隔...

请使用人类可读的时间。我真的不喜欢从数据导出但未能使用“圆形”数字的 x 轴和 y 轴。 (例如，当目标是大约 10 次抽动时，他们使用 143、286、...、1432，而不是 100、200、...、1500，但他们认为这意味着恰好 10抽搐。)

要“正确”地做到这一点，需要找到总体最大值和最小值，并进行一些涉及floor()和/或ceil()的算术运算。并投入一些启发式方法以获得“整数”。这可能是另一个讨论。它是纯粹的算法——可以用您的编程语言或 SQL 来同样好地实现。