Блог

 CREATE TABLE `device_m1000` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sensor_id` MEDIUMINT(9) NOT NULL,
`ctime` DATETIME NOT NULL,
`now_data` FLOAT(12) NOT NULL,
`total_data` FLOAT(12) NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `sensor_id` (`sensor_id`) USING BTREE,
INDEX `ctime` (`ctime`) USING BTREE) COLLATE='utf8_unicode_ci' ENGINE=InnoDB AUTO_INCREMENT=296533;
 

В таблице выше содержится 300 000 данных.
Столбец total_data всегда используется в накопленном объеме. (Структура, которая всегда увеличивается)
Если я хочу узнать использование «сегодня», я беру минимальное значение и максимальное значение на основе сегодняшней даты и вычитаю его.

попробуйте sql

 SELECT ROUND((max.v - min.v), 2) total
FROM   (SELECT
               DATE(ctime) `date`,
               sum(total_data)  v
        FROM   device_m1000
        WHERE  ( sensor_id, ctime ) IN (SELECT sensor_id,
                                               MAX(ctime) AS dt
                                        FROM   device_m1000
                                        WHERE  ctime >= '2020-11-23 06:30' and ctime < '2020-11-24 06:30'
                                        GROUP  BY sensor_id
                                        ORDER  BY null)
        group by `date`) max
       INNER JOIN (SELECT
                          DATE(ctime) `date`,
                          sum(total_data)  v
                   FROM   device_m1000
                   WHERE  ( sensor_id, ctime ) IN (SELECT sensor_id,
                                                          MIN(ctime) AS dt
                                                   FROM   device_m1000
                                                   WHERE  ctime >= '2020-11-23 06:30' and ctime < '2020-11-24 06:30'
                                                   GROUP  BY sensor_id
                                                   ORDER  BY null)
                   group by `date`) min
               ON min.`date` = max.`date`;
 

Используйте следующий запрос для получения данных.
Однако это просто задерживает код более чем на 10 секунд при максимальном времени (ctime).
Как я могу это оптимизировать?

• «Конструкторы строк» работают плохо. ( WHERE (a,b) IN ... )
• FLOAT(12) не дает вам 12 значащих цифр, вы получаете только около 7.
• id Полезно? Или является (sensor_id, ctime) уникальным? Если он уникален, сделайте его уникальным PRIMARY KEY и удалите id .
• Если вы не можете сделать это PK, имейте INDEX(sensor_id, ctime) .

Вернемся к вашему вопросу. Откажитесь от попытки поместить промежуточный итог в каждую строку. Практически любой подход будет выполнять более миллиарда операций в секунду — из-за необходимости суммировать около 30 тыс. элементов за день для каждого датчика.

Вместо этого создайте отдельную «сводную таблицу» с датой, датчиком и итогом за день. Затем, когда вы INSERT перейдете к подробной таблице (например, к тому, что у вас есть), также выполните

 INSERT INTO daily_summary (date, sensor, tot)
    VALUES (?, ?, ?)
    ON DUPLICATE KEY UPDATE
        tot = VALUES(tot)   ?
 

И используйте DOUBLE для tot . И иметь PRIMARY KEY(sensor, date)

Это позволяет избежать вашего вычитания и обеспечивает большую эффективность.

Подробнее о сводных таблицах: http://mysql.rjweb.org/doc.php/summarytables

Если вам нужны почасовые промежуточные итоги, сделайте это почасовой таблицей, а не ежедневной. Вы можете эффективно получить ежедневную сумму, добавив 24 строки из почасовой сводной таблицы.

Я понимаю, что вы хотите, чтобы разница между последними и самыми ранними total_data на сегодняшний день для каждого датчика. Если это так, вы можете использовать оконные функции, подобные этой:

 select sensor_id, sum(case when rn_desc = 1 then total_data else - total_data end) as total_diff
from (
    select d.*, 
        row_number() over(partition by sensor_id order by ctime) rn_asc,
        row_number() over(partition by sensor_id order by ctime desc) rn_desc
    from device_m1000
    where ctime >= current_date and ctime < current_date   interval 1 day
) t
where 1 in (rn_asc, rn_desc)
group by sensor_id
 

На самом деле, если total_data всегда увеличивается, это проще (и работает во всех версиях MySQL):

 select sensor_id, max(total_data) - min(total_data) as total_diff
from device_m1000
where ctime >= current_date and ctime < current_date   interval 1 day
group by sensor_id
 

Тогда я бы рекомендовал следующий индекс: (ctime, sensor_id, total_data) .