时间戳

首页 > 解决方案 > MySQL第一个/最后一个值和使用前一个值的计算

mysql - MySQL第一个/最后一个值和使用前一个值的计算

问题描述

我有每分钟都有数据行的巨大表格。某些列的计算差异为[last value of interval] - [value of last interval before]。但是有没有办法加快查询速度？已经有主索引和日期索引。不应创建任何其他索引。目前，查询大约 500.000 行运行大约 11 秒。

问题出在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

从今年开始的 SQL 转储数据： https ://www.tds-net.de/table_min.zip

标签： mysqlindexingsubquerycalculation

解决方案

  • 2 唯一键。也许你可以摆脱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 个值获取到单个查询中。但我不知道最好的办法把它折回去。

                SELECT  DB222_DBD10_integer
                FROM  table_min
                WHERE  date <= DATE_SUB(tm.date, INTERVAL 1 DAY)
                ORDER BY  date DESC
            LIMIT  1)

推荐阅读