MySQL第一个/最后一个值和以前值的计算

Question

我有巨大的表，每分钟都有数据行。一些列是用的差值计算的，最后的值是之前的最后一个区间的值。但是有什么方法可以加快查询速度吗？已经有主索引和日期索引了。不应该创建任何额外的索引。目前，大约有500.000行的查询运行在11s左右。

这个问题接近DATE_SUB()函数的计算结果。

MySQL: 10.1.44-MariaDB-0ubuntu0.18.04.1

PHP: 7.2.24-0ubuntu0.18.04.3

*id* || *select_type*      || *table*   || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *filtered* || *Extra*
1    || PRIMARY            || a10       || ALL    || date            ||       ||           ||       || 513754 || 21.86      || Using where; Using temporary; Using filesort
4    || DEPENDENT SUBQUERY || table_min || index  || date            || date  || 5         ||       || 1      || 100.00     || Using where
3    || DEPENDENT SUBQUERY || table_min || index  || date            || date  || 5         ||       || 1      || 100.00     || Using where
2    || DEPENDENT SUBQUERY || table_min || index  || date            || date  || 5         ||       || 1      || 100.00     || Using where

查询：

EXPLAIN EXTENDED 
SELECT  SQL_CALC_FOUND_ROWS tm.date,
CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD10_float ORDER BY  tm.date DESC),
                ",", 1) AS double
    ) AS `10-44-2`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD14_float ORDER BY  tm.date DESC),
                ",", 1) AS double) AS `10-45-3`, MIN(tm.DB221_DBD218_float
                    ) AS `10-2-4`,
                MAX(tm.DB221_DBD218_float) AS `10-2-5`, MIN(tm.DB221_DBD222_float) AS `10-3-6`,
MAX(tm.DB221_DBD222_float) AS `10-3-7`, MIN(tm.DB221_DBD278_float) AS `10-4-8`,
MAX(tm.DB221_DBD278_float) AS `10-4-9`, (CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB222_DBD10_integer
                                    ORDER BY  tm.date DESC), ",", 1) AS double) - 
            (
                SELECT  DB222_DBD10_integer
                    FROM  table_min
                    WHERE  date <= DATE_SUB(tm.date, INTERVAL 1 DAY)
                    ORDER BY  date DESC
                LIMIT  1)
   ) AS `10-18-10`,
                    (CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB222_DBD46_integer
       ORDER BY  tm.date DESC),
                        ",", 1) AS double) - (
                SELECT  DB222_DBD46_integer
                    FROM  table_min
                    WHERE  date <= DATE_SUB(tm.date, INTERVAL 1 DAY)
                    ORDER BY  date DESC
                LIMIT  1)
                    ) AS `10-36-11`,
                    (CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB222_DBD50_integer
       ORDER BY  tm.date DESC),
                        ",", 1) AS double
                          ) - (
                SELECT  DB222_DBD50_integer
                    FROM  table_min
                    WHERE  date <= DATE_SUB(tm.date, INTERVAL 1 DAY)
                    ORDER BY  date DESC
                LIMIT  1)
                    ) AS `10-37-12`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD124_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-47-13`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD120_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-46-14`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD128_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-48-15`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD132_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-49-17`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD136_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-50-18`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD140_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-51-19`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD144_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-52-21`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD148_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-53-22`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD310_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-54-24`,
                CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD314_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double
                    ) AS `10-55-25`,
                    (CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD310_float
       ORDER BY  tm.date DESC),
                        ",",
                                                ",", 1) AS double),NULL)) AS `10-0-26`,
                        1) AS double)/NULLIF(CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB223_DBD314_float
       ORDER BY  tm.date DESC),
CAST(SUBSTRING_INDEX(GROUP_CONCAT(tm.DB221_DBD538_float
       ORDER BY  tm.date DESC),
                ",", 1) AS double) AS `10-31-27`,
                MIN(tm.DB221_DBD326_float) AS `10-9-28`,
                MAX(tm.DB221_DBD326_float) AS `10-9-29`,
                MIN(tm.DB221_DBD450_float) AS `10-29-30`,
                MAX(tm.DB221_DBD450_float) AS `10-29-31`,
                MIN(tm.DB221_DBD406_float) AS `10-27-32`,
                MAX(tm.DB221_DBD406_float) AS `10-27-33`,
                MIN(tm.DB221_DBD562_float) AS `10-41-34`,
                MAX(tm.DB221_DBD562_float) AS `10-41-35`
    FROM  table_min AS tm WHERE  tm.date>="2020-01-01 00:00"
      AND  tm.date<="2020-01-31 23:59:59"
    GROUP BY  YEAR(tm.date), MONTH(tm.date), DAY(tm.date)
    ORDER BY  tm.date
    LIMIT  0,20

今年开始的数据转储：min.zip

Answer 1

• 两个独特的键。也许你可以摆脱id，把date推广到PK？
• SQL_CALC_FOUND_ROWS强制遍历所有选定的行。你能把它扔掉吗？
• LIMIT 0,20 --这是“分页”的序曲吗？如果是这样的话，它对记得你停下来的地方吗的效率要高得多。
• GROUP BY YEAR(tm.date), MONTH(tm.date), DAY(tm.date) ORDER BY tm.date可能导致两种情况。(请参见EXPLAIN FORMAT=JSON SELECT ...进行验证。我认为，这将避免第二类，更简单，但可能不会更快：GROUP BY DATE(tm.date) ORDER BY DATE(tm.date)。请注意，他们现在说的是相同的东西，从而避免了额外的排序。
• SELECT tm.date ... GROUP BY ...是不适当的，也要改变它。
• 先获取数据，然后再进行数据透视。(这将涉及另一层嵌套的SELECTs。)
• 更好的方法是将语句中的3个值(例如，放在一个查询中)。但我不知道把它折回去的最好方法。 从DB222_DBD10_integer中选择table_min <= DATE_SUB(tm.date，间隔1天)，按日期排序