Блог

У меня есть много миллионов точек временных рядов, поступающих каждый день, по двум важным осям, по которым мне нужно выполнить поиск. Мои данные выглядят следующим образом:

 X, Y, value, TIMESTAMP
  

Первоначально они хранились в MariaDB, но размер таблицы растет слишком быстро. Выполнение простых агрегированных запросов (например SUM() ) занимает слишком много времени даже на сервере среднего размера с индексами.

Вот несколько примеров запросов:

 SELECT COUNT(*) FROM tbl 
WHERE X = 23 AND Y = 46 AND TIMESTAMP > NOW() - INTERVAL 30 DAY

SELECT X, Y, COUNT(*) FROM tbl
WHERE TIMESTAMP > NOW() - INTERVAL 30 DAY
GROUP BY X, Y
ORDER BY COUNT(*) DESC
  

У меня есть два индекса:

 X, Y, value
X, Y, TIMESTAMP
  

Я ищу рекомендации о способах (или новых базах данных) хранения этих данных, обеспечивающих быстрый поиск любой комбинации X и Y при фильтрации по временной метке или значению.

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

Данные временных рядов записываются в базу данных «в реальном времени»

это означает, что вы не записываете данные, которые проходили «окно» в прошлом, например. давайте предположим, что вчера.

В этом случае вы могли бы объединить данные из материализованного представления, таблицы, содержащей агрегированные данные за каждый день в прошлом.

Идея заключается в том, что когда запрос выполняется между определенными датами, например, startTime = 2019-03-03 12:00:00 -> EndTime = 2019-04-02 12:00:00:

• Получите агрегированные данные из таблицы временных рядов, где ВРЕМЕННАЯ МЕТКА находится между временем начала — до конца дня времени начала (2019-03-03 12:00:00,2019-03-04 00:00:00)
• Получение агрегированных данных из материализованного представления за несколько дней между (2019-03-04, 2019-04-01)
• Получите агрегированные данные из таблицы временных рядов, где ВРЕМЕННАЯ МЕТКА находится между временем начала — до конца дня времени начала (2019-04-02 00:00:00,2019-04-02 12:00:00)
• Наконец, объедините приведенные выше значения, используя union all.

Предположим, что таблицы data и AggData :

 CREATE TABLE `data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `X` varchar(32) NOT NULL,
 `Y` varchar(32) NOT NULL,
 `value` float(10,2) NOT NULL,
 `TIMESTAMP` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
);

CREATE TABLE `AggData` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `X` varchar(32) NOT NULL,
 `Y` varchar(32) NOT NULL,
 `DAY` date NOT NULL,
 `sum1` float NOT NULL,
 PRIMARY KEY (`id`)
)
  

Вы можете объединить данные, используя эту процедуру:

 CREATE DEFINER=`root`@`localhost` PROCEDURE `getDataForPeriods`(IN `startTime` INT(32), IN `endTime` INT(32), OUT `AggSum1` FLOAT)
    NO SQL
BEGIN
SELECT SUM(allData.summed1) INTO AggSum1
FROM (SELECT SUM(d1.value) AS summed1,d1.X AS X,d1.Y AS Y FROM `data` d1
WHERE UNIX_TIMESTAMP(d1.`TIMESTAMP`) > startTime
AND UNIX_TIMESTAMP(d1.`TIMESTAMP`) <  UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(startTime   24*60*60)))
GROUP BY d1.X,d1.Y
      UNION ALL
SELECT SUM(s1.`sum1`) AS summed1,s1.X AS X,s1.Y AS Y FROM AggData s1
WHERE UNIX_TIMESTAMP(s1.DAY) > startTime 
AND UNIX_TIMESTAMP(s1.DAY)   24*60*60 < endTime
GROUP BY s1.X,s1.Y
     UNION ALL
     SELECT SUM(d2.value) AS summed1,d2.X AS X,d2.Y AS Y FROM `data` d2
WHERE UNIX_TIMESTAMP(d2.`TIMESTAMP`) > UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(endTime)))
AND UNIX_TIMESTAMP(d2.`TIMESTAMP`) < endTime
GROUP BY d2.X,d2.Y) allData
GROUP BY allData.X,allData.Y;
END
  

Рассматривая условие WHERE TIMESTAMP > NOW() - INTERVAL 30 DAY , это было бы улучшением для условий, подобных этому, поскольку:

• Для материализованной таблицы не требуется частых обновлений
• узким местом, по-видимому, является то, что запрос возвращает большой результирующий набор в течение 30 дней, а затем агрегирует его, таким образом, вы возвращаете большую часть данных из материализованной таблицы и агрегируете гораздо меньше строк

Обратите внимание, что при получении данных, близких к текущему времени NOW() , вы могли бы изменить третий запрос, чтобы включить больше дней, не только сегодня, на случай, если вы все еще получаете данные временных рядов, например, за вчерашний день.

MySQL и MariaDB не обладают необходимой спецификой, но сводные таблицы — это правильный путь. Но сначала…

 mysql> SELECT NOW() - INTERVAL 30 DAY;
 ------------------------- 
| NOW() - INTERVAL 30 DAY |
 ------------------------- 
| 2019-03-10 11:48:24     |
 ------------------------- 
  

Вы действительно хотите охватить 30-дневный период, начинающийся с какой-то секунды? Обычно людям требуется всего 30 полных дней:

 WHERE ts >= CURDATE() - INTERVAL 30 DAY
  AND ts  < CURDATE();

mysql> SELECT CURDATE() - INTERVAL 30 DAY, CURDATE();
 ----------------------------- ------------ 
| CURDATE() - INTERVAL 30 DAY | CURDATE()  |
 ----------------------------- ------------ 
| 2019-03-10                  | 2019-04-09 |
 ----------------------------- ------------ 
1 row in set (0.00 sec)
  

или даже учитывать месяцы переменной длины:

 WHERE ts >= CURDATE() - INTERVAL 1 MONTH
  AND ts  < CURDATE();

mysql> SELECT CURDATE() - INTERVAL 1 MONTH, CURDATE();
 ------------------------------ ------------ 
| CURDATE() - INTERVAL 1 MONTH | CURDATE()  |
 ------------------------------ ------------ 
| 2019-03-09                   | 2019-04-09 |
 ------------------------------ ------------ 
  

Если вы готовы рассматривать только целые дни, создание и ведение сводной таблицы (а-ля материализованный вид) легко и очень эффективно:

 CREATE TABLE SummaryXY (
    x ...,
    y ...,
    dy DATE,
    ct INT UNSIGNED,
    PRIMARY KEY(x,y,dy)
) ENGINE=InnoDB;
  

У вас была бы задача добавлять новые строки сразу после полуночи каждую высоту.

Если, с другой стороны, вам нужно перейти к текущей секунде, обновление может осуществляться через IODKU ( INSERT ... ON DUPLICATE KEY UPDATE... ), который позаботится об обновлении или вставке по мере необходимости.

Если вам нужно вернуться к часам вместо дней, измените dy . Но если вам действительно нужно вернуться к произвольной секунде, то выполните задачу в 2 шага:

 SELECT
    ( SELECT COUNT(*) FROM RawData WHERE ... (the partial day 30 days ago) )  
    ( SELECT SUM(ct) FROM SummaryXY WHERE ... (the 30 full days) );
  

(И обрабатывайте часть текущего дня либо с помощью IODKU, либо аналогичного SELECT COUNT(*) FROM RawDATA .)

Был ли ваш простой пример таким сложным, как вам нужно? То, что я описываю, будет хорошо работать для X=constant AND y=constant AND ts... , но не для X>constant etc.

Если вам нужно AVG(value) , то сохраните COUNT(*) (как указано выше) и SUM(VALUE) . Тогда это дает вам среднее:

 SUM(value_sum) / SUM(ct)
  

Если вам также нужно WHERE x=1 AND w=2 AND ts... , то создайте вторую сводную таблицу на основе x,w,ts .

Если вам также нужно WHERE x=1 AND y=1 AND z=3 AND ts... , то создайте сводную таблицу на основе x,y,z,ts , но используйте ее для x,y,ts . Возможно, типичным является 5 сводных таблиц, обрабатывающих 40 случаев.

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

Ваш второй запрос ( GROUP BY X, Y ORDER BY COUNT(*) DESC ) в настоящее время выполняет сканирование таблицы большой необработанной таблицы, даже если вы индексируете ts . С предложенной мной сводной таблицей запрос будет представлять собой таблицу can сводной таблицы. Поскольку это может быть в 10 раз меньше, сканирование таблицы будет значительно быстрее.

Дополнительная сортировка COUNT(*) является незначительной нагрузкой; это зависит от количества строк в результирующем наборе.

Раймонд Найланд опубликовал рекомендацию использовать материализованное представление (таблицу, созданную на основе запросов к другим таблицам). Сначала я отклонил это, потому что запрос, который я в настоящее время использовал для построения материализованного представления, требовал (почти) полного сканирования таблицы для выполнения вычисления, чего я и пытался избежать.

Однако материализованное представление также можно создавать по одному элементу за раз, что оказывается отличным решением этой проблемы как для баз данных NoSQL, так и для SQL (предоставляются индексы).

RDBMS

Если поступила вставка для оси X и Y , извлеките только записи с осями X и Y и повторно запустите вычисление для них. В моем случае это отлично работает, потому что частота ежедневных вставок на пару осей очень низкая (хотя все вставки пары осей высоки).

Когда:

 INSERT X, Y, value, TIMESTAMP
  

Затем запустите:

 INSERT INTO reports (X, Y, cnt, updated_at, ...) 
SELECT X, Y, COUNT(*), NOW(), ...(other columns)... FROM tbl 
WHERE X = ? AND Y = ? AND TIMESTAMP BETWEEEN ? AND ?)
  

Это расплывчатый пример, но, предполагая правильно структурированные индексы и разделы / первичные ключи, вы можете поддерживать постоянно обновляемую своего рода таблицу материализованных отчетов.

Если есть оси, которые обновляются не очень часто, вы можете запустить вторую фоновую задачу для идентификации и удаления / обновления строк WHERE updated_at < NOW() - INTERVAL 1 DAY .

Redis

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

 redis> SET X#Y#2020-01-01 1
"OK"
redis> INCR X#Y#2020-01-01
(integer) 2
  

Это сложнее для данных по нескольким осям.

DynamoDB, MongoDB и т.д…

• В AWS DynamoDB есть «потоки», которые предоставляют способ уведомления функции AWS Lambda об изменении.

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

В обоих случаях вы можете выполнить фоновое сопоставление / уменьшение данных и обновить вычисление на основе отсканированных данных.

Часто это намного дороже операции, чем то, что выполняется с меньшими наборами данных, которые помещаются в память (Redis) или RDMBS (выше).

Примечание: Я все еще ищу лучшие решения для обработки данных временных рядов по нескольким осям на платформах NoSQL, поскольку мою текущую рекомендацию легче сказать, чем сделать.