대용량 Update 튜닝하기

1) 대용량 update 튜닝을 시도하다.

회사에서 의도치않게 ISMS 프로젝트에서 혼자 회원 탈퇴&휴면 처리 프로세스 설계와 구현을 맡게 되었는데
이 과정에서 우리 개발 서버의 대용량 주문 테이블, 정산 테이블에 있는 회원들의 실제 개인 정보를 모두
마스킹 처리하여 업데이트 처리 해달라는 요구사항을 받게 되었다.
우선 큼지막한 테이블들의 데이터 현황을 추려보자면 다음과 같았다.

데이터 현황

<고객 테이블>

• 약 37,281건

<주문 마스터 테이블>

• 약 5,814,416건

<주문 정산 테이블>

• 약 5,159,729건

부장님이 이 모든 데이터를 UPDATE 하면 부하도 심할뿐더러 시간도 굉장히 오래걸릴거라고
더 효과적인 방법을 스스로 찾아서 해보라고 말씀해주셔서 곰곰히 생각하다가 요근래 열심히 공부중이던 SQLP 공부 내용에서 UPDATE 튜닝 단원의 내용이 생각나 실제로 적용해 보았다.

2)Truncate &Insert 방식을 사용하다.

• 대량의 데이터를 일반 Update문으로 갱신하거나 일반 Delete문으로 삭제하면 상당히 오랜 시간이 소요될 것이라 예상했다.
  • 왜냐하면 일반 Update문 , 일반 Delete문은 다음과 같이 내부적으로 많은 작업이 일어나기 때문이다.

    1. 테이블 데이터를 갱신하는 본연의 작업
    2. 인덱스 데이터까지 갱신
    3. 버퍼 캐시에 없는 블록을 디스크에서 읽어 버퍼 캐시에 적재한 후에 갱신
    4. 내부적으로 Redo와 Undo 정보 생성
    5. 블록에 빈 공간이 없으면 새 블록 할당(→ Row Migration 발생)

• 따라서 대량의 데이터를 갱신할 때는 Update문을 이용하기보다 아래와 같이 처리하는 것이 더 빠를 것이라 판단했다.

  1. 대상테이블의 데이터로 temp 테이블 생성
  2. 대상테이블의 제약조건 및 인덱스 삭제
  3. 대상테이블 truncate
  4. temp 테이블에 있는 원본 데이터를 update 할 값으로 수정하여 대상테이블에 insert
  5. 대상테이블에 제약조건 및 인덱스 생성

2) Truncate &Insert 방식으로 Update 튜닝 구현하기

1. 대상테이블의 데이터로 temp 테이블 생성

CREATE TABLE ORDER_MST_BACKUP AS
SELECT * FROM ORDER_MST;

2. 대상테이블의 제약조건 및 인덱스 삭제

ALTER TABLE ORDER_MST DROP CONSTRAINT ORDER_MST_PK;
DROP INDEX ORDER_MST_X1;
ALTER TABLE ORDER_MST DISABLE CONSTRAINT SYS_C001111;

3. 대상테이블 truncate ( 이 때가 제일 쫄렸다..로그가 전혀 안 남으니까😳)

TRUNCATE TABLE ORDER_MST;

4. temp 테이블에 있는 원본 데이터를 update 할 값으로 수정하여 대상테이블에 insert

• 컬럼은 테스트 컬럼명으로 대체했습니다.
• 아래 쿼리의 '상세주소' 는 고객의 집주소 정보를 마스킹한 부분입니다.
• 아래 쿼리의 '이메일마스킹@test.com' 부분은 고객의 이메일 정보를 마스킹한 부분입니다.
• 아래 쿼리의 '0100000000' 부분은 고객의 휴대전화 번호 정보를 마스킹한 부분입니다.

INSERT INTO ORDER_MST
SELECT TEST_COL1, TEST_COL2, TEST_COL3,'상세주소' TEST_COL54, TEST_COL5,
'이메일마스킹@test.com' TEST_COL6,'0100000000' TEST_COL7, TEST_COL8
FROM ORDER_MST_BACKUP;

5. 대상테이블에 제약조건 및 인덱스 생성

( 이 때도 엄청 쫄렸다..오래걸려서😳)

ALTER TABLE ORDER_MST ADD CONSTRAINT ORDER_MST_PK(COL1);
CREATE INDEX ORDER_MST_X1 ON ORDER_MST (TEST_COL1,TEST_COL2,TEST_COL3,TEST_COL4);
ALTER TABLE ORDER_MST ENABLE CONSTRAINT SYS_C001111;

이렇게해서 총 대량 업데이트하니 한 15분도 채 안 걸렸다!
역시 가장 효율적인 방법을 찾아 공부하고 구현하는 게 바로 백엔드 개발의 묘미이지 않을까 싶다.