여러 축에서 시계열 데이터를 집계하고 있습니까?

여러 축에서 시계열 데이터를 집계하고 있습니까?

매일 수백만 개의 시계열 포인트가 들어오고 두 개의 중요한 축을 검색해야 합니다.데이터는 다음과 같습니다.

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

인덱스가 2개 있습니다.

X, Y, value
X, Y, TIMESTAMP

타임스탬프나 값을 필터링하면서 X와 Y의 조합을 빠르게 검색할 수 있도록 이 데이터를 저장하는 방법(또는 새로운 데이터베이스)에 대한 권장 사항을 찾고 있습니다.

질의에 구체화된 뷰를 사용하는 것에 대한 답변을 바탕으로 다음과 같은 경우에 개선할 수 있습니다.

시계열 데이터가 데이터베이스에 "실시간"으로 기록됩니다.

즉, 예를 들어 어제와 같이 과거에 "예"를 통과한 데이터를 쓰지 않습니다.

이 경우 구체화된 뷰의 데이터를 결합할 수 있습니다. 즉, 과거에 집계된 각 날짜의 데이터를 포함하는 테이블입니다.

즉, 특정 날짜 사이에 쿼리를 실행할 때 startTime = 2019-03-03 12:00:00 -> endTime = 2019-04-02 12:00:00:

• TIMESTAMP가 startTime 사이에 있는 시계열 테이블에서 집계된 데이터를 가져옵니다. startTime의 마지막(2019-03-03 12:00:00, 2019-03-04 00:00:00:00)
• (2019-03-04,2019-04-01) 기간 동안 구체화된 보기에서 집계된 데이터 가져오기
• TIMESTAMP가 startTime 사이에 있는 시계열 테이블에서 집계된 데이터를 가져옵니다(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;

매일 0시 직후에 새로운 행을 추가하는 작업이 있을 것입니다.

이행할 가 있는 IODKU 「 「 」 , 「 」 , 「 」 )를 시켜 할 수 .INSERT ... ON DUPLICATE KEY UPDATE...필요에 따라 업데이트 또는 삽입을 처리합니다.

몇 할 하십시오.dy 꼭 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아, 아.

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>constantsyslog.

AVG(value)그후 store , " " " 입니다.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,ts40개의 케이스를 취급하는 5개의 요약표일 것입니다.

요약 테이블에 대한 자세한 내용은http://http://mysql.rjweb.org/doc.php/summarytables 를 참조해 주세요.

쿼리 「 」 「 」 )GROUP BY X, Y ORDER BY COUNT(*) DESC는 현재 큰하고 있습니다.은 인덱스를 .인덱스를 작성해도ts제가 제안하는 요약표에서는 쿼리는 요약표의 테이블 캔이 됩니다.이 크기는 10배 작을 수 있으므로 테이블 스캔이 훨씬 빠릅니다.

의 COUNT(*)이는 결과 집합의 행 수에 따라 달라지는 경미한 부담입니다.

Raymond Nijland는 구체화된 뷰(다른 테이블의 쿼리로 작성된 테이블)를 사용하기 위한 권장사항을 게시했다.처음에는 현재 구체화된 뷰를 구축하기 위해 사용하고 있는 쿼리에서 계산을 실행하기 위해 (거의) 풀테이블 스캔이 필요했기 때문에 이 문제를 회피하려고 했습니다.

그러나 구체화된 뷰도 한 번에 한 개씩 구축할 수 있으므로 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> 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 플랫폼에서 여러 축을 가진 시계열 데이터에 대한 더 나은 솔루션을 찾고 있습니다.

언급URL : https://stackoverflow.com/questions/55460404/aggregating-timeseries-data-on-multiple-axises