MariaDB: 수십억 개의 레코드를 갱신하는 최적의 방법

MariaDB: 수십억 개의 레코드를 갱신하는 최적의 방법

1개의 테이블에 존재하는 수십억 개의 레코드를 갱신할 수 있는 최적의 방법을 찾고 있습니다(아래 표 3의 예).각 엔트리는 밀리초 단위의 타임스탬프에 관련지어집니다.이 예에서 표 3은 최신이 아닙니다.표 1과 표 2는 각각의 데이터의 실제 엔트리에 대해 최신입니다.표 1과 표 2와 표 3을 연결하는 것은 없습니다.저는 데이터베이스 전문가가 아니기 때문에 필요하시면 말씀해주세요.

표 1에는 4개의 열이 있습니다.

Timestamp T_0 PRIMARY KEY (ex: '2014-07-04 16:17:16.800000')
X1_T1 VARCHAR
X2_T1 VARCHAR
X3_T1 VARCHAR

표 2에는 4개의 열이 있습니다.

Timestamp T_0 PRIMARY KEY (ex: '2014-07-04 16:17:16.800000')
X1_T2 VARCHAR
X2_T2 VARCHAR
X3_T3 VARCHAR

표 3에는 7개의 열이 있습니다.

Timestamp T_0 PRIMARY KEY (ex: '2014-07-04 16:17:16.800000')
X1_T1 VARCHAR
X2_T1 VARCHAR
X3_T1 VARCHAR
X1_T2 VARCHAR
X2_T2 VARCHAR
X3_T3 VARCHAR

타임스탬프를 루프하고 다음 명령을 사용하여 각 행을 업데이트하는 절차를 사용하여 표 3을 업데이트했습니다.

SET tmp_T_0=(SELECT '2014-01-05 17:00:00.000000'); // set to the start of the table's timestamp
label1: LOOP
  UPDATE TABLE3 SET
      X1_T1=(select X1_T1 FROM TABLE1 where T_0 = tmp_T_0),
      X2_T1=(select X2_T1 FROM TABLE1 where T_0 = tmp_T_0),
      X3_T1=(select X3_T1 FROM TABLE1 where T_0 = tmp_T_0),
      X1_T2=(select X1_T2 FROM TABLE2 where T_0 = tmp_T_0),
      X2_T2=(select X2_T2 FROM TABLE2 where T_0 = tmp_T_0),
      X3_T2=(select X3_T2 FROM TABLE2 where T_0 = tmp_T_0)
 WHERE T_0 = tmp_T_0;  

 SET tmp_T_0=(SELECT TIMESTAMP(tmp_T_0,'00:00:00.001')); //ADD one millisecond and continue

 SET LoopInt=(SELECT(LoopInt + 1));
 IF LoopInt < LoopEnd THEN
   ITERATE label1;
 END IF;
 LEAVE label1;
END LOOP label1;

상기의 방법에서는, 100,000엔트리에 약 53초 걸립니다.나머지 엔트리를 완료하는 데 약 100일이 소요되므로 이는 허용되지 않습니다.

표 3이 각각의 타임스탬프 엔트리에 대해 표 1 및/또는 표 2의 데이터를 반드시 가질 필요는 없습니다(즉, 표 3의 타임스탬프는 X1_T1 및 X3_T1의 데이터를 포함할 수 있으며, 다른 값은 X1_T2 X2 X2 및 X3이다).

어떤 제안이라도 도움이 될 거예요.감사해요.

이 쿼리를 사용하여 TABLE1에서 TABLE3로 1시간 분량의 정보를 끌어오는 것은 어떻습니까?

UPDATE TABLE3 AS t3
  JOIN TABLE1 AS t1 ON t3.T_0 = t1.T_0
   SET t3.X1_T1 = IFNULL(t1.X1_T1,t3.X1_T1),
       t3.X2_T1 = IFNULL(t1.X2_T1,t3.X2_T1),
       t3.X3_T1 = IFNULL(t1.X3_T1,t3.X3_T1)
 WHERE t3.T_0 >= '2014-01-05' + INTERVAL 0 HOUR
   AND t3.T_0 <  '2014-01-05' + INTERVAL 1 HOUR

무슨 일이야?일단은WHERE절은 쿼리 범위를 1시간으로 제한합니다.테스트도 할 수 있어서 편리하네요.또한 쿼리를 너무 오래 실행하지 않도록 이 작업을 시간별로 반복해야 합니다.InnoDB 또는 Aria를 스토리지 엔진으로 사용하는 경우 쿼리 범위를 제한하지 않으면 트랜잭션 롤백 공간도 낭비됩니다.

이와 같이 HOUR 간격을 변경하여 매번 이 쿼리를 여러 번 실행할 수 있습니다.

 WHERE t3.T_0 >= '2014-01-05' + INTERVAL 1 HOUR
   AND t3.T_0 <  '2014-01-05' + INTERVAL 2 HOUR

TABLE1을 TABLE3에 가입시키는 거야.TABLE3에는 가능한 모든 타임스탬프가 포함되어 있지만 TABLE1에는 포함되어 있지 않기 때문에 유효합니다.제가 이 쿼리를 작성한 방법으로는 TABLE1에 대응하는 행이 없는 TABLE3의 행에는 영향을 주지 않습니다.그게 네가 원하는 거라고 생각해.

마지막으로 IFNULL() 함수는 NULL이 아닌 TABLE1 데이터가 있는 경우에만 TABLE3 데이터를 변경하도록 준비합니다.

TABLE1 데이터가 희박한 경우(즉, 대부분 NULL인 테이블에 랜덤하게 흩어져 있는 유효한 값이 많이 있습니다)에는 이와 같은 세 가지 쿼리를 사용해야 합니다.따라서 새로운 데이터가 없는 한 TABLE3의 행을 실제로 변경하지 않습니다.행의 값을 변경하는 것은 상대적으로 비용이 많이 듭니다.

UPDATE TABLE3 AS t3
  JOIN TABLE1 AS t1 ON t3.T_0 = t1.T_0
   SET t3.X1_T1 = t1.X1_T1
 WHERE t3.T_0 >= '2014-01-05' + INTERVAL 0 HOUR
   AND t3.T_0 <  '2014-01-05' + INTERVAL 1 HOUR
   AND t1.X1_T1 IS NOT NULL

UPDATE TABLE3 AS t3
  JOIN TABLE1 AS t1 ON t3.T_0 = t1.T_0
   SET t3.X2_T1 = t1.X2_T1
 WHERE t3.T_0 >= '2014-01-05' + INTERVAL 0 HOUR
   AND t3.T_0 <  '2014-01-05' + INTERVAL 1 HOUR
   AND t1.X2_T1 IS NOT NULL

UPDATE TABLE3 AS t3
  JOIN TABLE1 AS t1 ON t3.T_0 = t1.T_0
   SET t3.X3_T1 = t1.X3_T1
 WHERE t3.T_0 >= '2014-01-05' + INTERVAL 0 HOUR
   AND t3.T_0 <  '2014-01-05' + INTERVAL 1 HOUR
   AND t1.X3_T1 IS NOT NULL

TABLE2 데이터에 대해 이 모든 것을 반복해야 합니다.

이 모든 것을 단일 쿼리에서 실행할 수 있습니다.그렇게 하지 마.이것은 한 번에 한 시간씩 할 수 있고 필요할 때 다시 시작할 수 있어야 하는 작업입니다.한 번에 한 시간씩 하는 게 좋겠지만, 3.6 메가로는 충분해.한 번에 6분(360킬로) 정도의 작은 청크를 수행할 수 있습니다.

내가 너라면 분명 너의 TABLE3를 이틀치 복사해서 이 모든 거래를 디버깅할 거야.

언급URL : https://stackoverflow.com/questions/26536647/mariadb-optimum-way-to-update-billions-of-records