Блог

У меня есть большая таблица, содержащая данные датчиков временных рядов. Большой — это что-то от нескольких тысяч до 10 миллионов записей, разделенных между различными отслеживаемыми каналами. Для определенного типа датчика мне нужно вычислить временной интервал между текущим и предыдущим показаниями, т. е. Найти наибольшую временную метку, предшествующую текущей.

На ум приходят очевидные подходы, каждый из которых измеряется на Core i5 для канала из 40 тыс. записей:

Коррелированный подзапрос

 SELECT collect.*, prev.timestamp AS prev_timestamp
FROM data AS collect 
LEFT JOIN data AS prev ON prev.channel_id = collect.channel_id AND prev.timestamp = ( 
    SELECT MAX(timestamp) 
    FROM data 
    WHERE data.channel_id = collect.channel_id AND data.timestamp < collect.timestamp
) 
WHERE collect.channel_id=14 AND collect.timestamp >= 0 
ORDER BY collect.timestamp
 

Время (exec, выборка) 11 сек, 21 сек

План

  ---- -------------------- --------- ------ ------------------------------ --------- --------- ------------------------- ------- -------------------------- 
| id |    select_type     |  table  | type |        possible_keys         |   key   | key_len |           ref           | rows  |          Extra           |
 ---- -------------------- --------- ------ ------------------------------ --------- --------- ------------------------- ------- -------------------------- 
|  1 | PRIMARY            | collect | ref  | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |       5 | const                   | 45820 | Using where              |
|  1 | PRIMARY            | prev    | ref  | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |      13 | const,func              |     1 | Using index              |
|  2 | DEPENDENT SUBQUERY | data    | ref  | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |       5 | nils.collect.channel_id |  2495 | Using where; Using index |
 ---- -------------------- --------- ------ ------------------------------ --------- --------- ------------------------- ------- -------------------------- 
 

Антисоединение

 SELECT d1.*, d2.timestamp AS prev_timestamp
FROM data d1
LEFT JOIN data d2 ON
    d2.channel_id=14 AND
    d2.timestamp < d1.timestamp 
LEFT JOIN data d3 ON
    d3.channel_id=14 AND
    d3.timestamp < d1.timestamp AND
    d3.timestamp > d2.timestamp
WHERE 
    d3.timestamp IS NULL AND
    d1.channel_id=14
ORDER BY timestamp
 

Время 12 секунд, 21 секунда

План

  ---- ------------- ------- ------ ------------------------------ --------- --------- ------- ------- -------------------------------------- 
| id | select_type | table | type |        possible_keys         |   key   | key_len |  ref  | rows  |                Extra                 |
 ---- ------------- ------- ------ ------------------------------ --------- --------- ------- ------- -------------------------------------- 
|  1 | SIMPLE      | d1    | ref  | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |       5 | const | 45820 | Using where                          |
|  1 | SIMPLE      | d2    | ref  | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |       5 | const | 47194 | Using index                          |
|  1 | SIMPLE      | d3    | ref  | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |       5 | const | 47194 | Using where; Using index; Not exists |
 ---- ------------- ------- ------ ------------------------------ --------- --------- ------- ------- -------------------------------------- 
 

И я придумал еще один паттерн, который я называю Наивным подсчетом

 SELECT current.*, prev.timestamp AS prev_timestamp FROM
(
    SELECT data.*, @r1 := @r1 1 AS rownum from data
    CROSS JOIN (SELECT @r1 := 0) AS vars 
    WHERE channel_id=14
    ORDER BY timestamp
) AS current
LEFT JOIN
(
    SELECT data.*, @r2 := @r2 1 AS rownum from data
    CROSS JOIN (SELECT @r2 := 0) AS vars 
    WHERE channel_id=14
    ORDER BY timestamp
) AS prev
ON current.rownum = prev.rownum 1
 

Время 1.1 сек (этот на самом деле самый быстрый!)

План

  ---- ------------- ------------ -------- ------------------------------ --------- --------- ----- ------- ---------------- 
| id | select_type |   table    |  type  |        possible_keys         |   key   | key_len | ref | rows  |     Extra      |
 ---- ------------- ------------ -------- ------------------------------ --------- --------- ----- ------- ---------------- 
|  1 | PRIMARY     | <derived2> | ALL    |                              |         |         |     | 24475 |                |
|  1 | PRIMARY     | <derived4> | ALL    |                              |         |         |     | 24475 |                |
|  4 | DERIVED     | <derived5> | system |                              |         |         |     |     1 |                |
|  4 | DERIVED     | data       | ref    | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |       5 |     | 45820 | Using where    |
|  5 | DERIVED     |            |        |                              |         |         |     |       | No tables used |
|  2 | DERIVED     | <derived3> | system |                              |         |         |     |     1 |                |
|  2 | DERIVED     | data       | ref    | ts_uniq,IDX_ADF3F36372F5A1AA | ts_uniq |       5 |     | 45820 | Using where    |
|  3 | DERIVED     |            |        |                              |         |         |     |       | No tables used |
 ---- ------------- ------------ -------- ------------------------------ --------- --------- ----- ------- ---------------- 
 

Поскольку запрос, вероятно, выполняется на небольших платформах, таких как RasPi, производительность имеет решающее значение — пара секунд является максимально приемлемой.

Мой вопрос: является ли последний подход хорошим для наибольшего n-на-группу или есть лучшие? Ожидается ли, что коррелированный подзапрос выполняется так же медленно, как и раньше?

Последний подход с переменными является разумным. Вы также можете попробовать:

 SELECT collect.*,
       (select max(timestamp)
        from data
        where data.channel_id = collect.channel_id AND data.timestamp < collect.timestamp
       ) AS prev_timestamp
FROM data AS collect 
WHERE collect.channel_id = 14 AND collect.timestamp >= 0 
ORDER BY collect.timestamp;
 

Кроме того, создайте индексы на: collect(channel_id, timestamp).

Редактировать:

Следующее может быть самым быстрым:

   select d.*,
         if(@channel_id = channel_id, @prev_timestamp, NULL) as prev_timestamp,
         @channel_id := channel_id, @prev_timestamp = timestamp
  from data d cross join
       (select @channel_id := 0, @prev_timestamp := 0) vars
  where collect.channel_id = 14 AND collect.timestamp >= 0 
  order by channel_id, timestamp;