SQL에서 대량 데이터 삽입의 성능 최적화, 무결성 및 잠금 측면에서 구현의 특징을 설명하십시오. 대량 데이터 작업 시 특히 주의해야 할 점은 무엇인가요?

답변

대량 데이터 삽입(bulk insert)은 대규모 테이블의 마이그레이션, 수입 또는 채우기와 같은 일반적인 작업입니다. 이러한 작업의 효율성은 다음과 같은 여러 요인에 따라 달라집니다:

1. 배치 삽입 사용(Batch Insert): 데이터를 합리적인 배치로 나누십시오 — 일반적으로 한 번에 수천 개의 행입니다. 이는 트랜잭션 로그에 대한 부담을 줄이고 잠금을 감소시킵니다.
2. 대량 삽입 동안 인덱스 및 제약 조건 비활성화: 부차적인 인덱스와 외래 키를 임시로 삭제하거나 비활성화하면 삽입 속도가 빨라질 수 있습니다. 작업이 완료된 후 인덱스를 재생성하십시오.
3. 트랜잭션 제어: 너무 큰 로그 파일이 쌓이지 않도록 고정된 수의 행으로 삽입 작업을 트랜잭션 내에서 수행하십시오.
4. 전문 도구 사용: 예를 들어, BULK INSERT 또는 COPY (PostgreSQL) — 이들은 일반 반복 삽입보다 더 빠르게 작동합니다.
5. 필요한 열만 로드: 불필요한 데이터를 제외하십시오 — 이는 트래픽과 처리 시간을 줄입니다.

예 (SQL Server):

BULK INSERT my_table
FROM 'C:\data\bulkdata.csv'
WITH (
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '
',
  BATCHSIZE = 5000,
  TABLOCK
);

TABLOCK은 대량 삽입 시 잠금 충돌을 줄입니다.

트릭 질문

질문: 대량 삽입 속도를 높이기 위해 언제든지 인덱스를 비활성화하고 재생성할 수 있나요, 만약 테이블이 트랜잭션에 참여하고 있다면?

답변: 아니요, 테이블이 활성 트랜잭션에 참여하고 있다면 인덱스 비활성화나 재생성은 잠금, 데이터 무결성 손상 또는 트랜잭션이 롤백될 경우 데이터 손실을 초래할 수 있습니다. 이 작업은 트랜잭션 외부에서 수행하거나 유지 관리 창을 미리 계획해야 합니다.

코드 예:

-- 잘못된 예:
BEGIN TRAN;
ALTER INDEX ALL ON my_table DISABLE;
-- ... 대량 삽입 ...
ALTER INDEX ALL ON my_table REBUILD;
COMMIT;

긴 트랜잭션 내에서 이러한 비활성화는 허용되지 않습니다!

주제에 대한 세부 사항 부족으로 인한 실제 오류 사례

---

사례 1: 한 프로젝트에서 여러 고유 인덱스가 있는 테이블에서 병렬 대량 삽입이 자주 교착 상태를 일으키고 성능 저하를 초래했습니다. 해결책은 가져오기 기간 동안 비주요 인덱스를 임시로 비활성화하고 배치 작업 크기를 줄이는 것이었습니다.

---

사례 2: 개발자들이 데이터 로드 기간 동안 외래 키 검사를 비활성화하는 것을 잊었고, 매 삽입 시 다른 대형 테이블에서 관계된 레코드의 존재를 확인했습니다. 이로 인해 로드 시간이 40분에서 9시간으로 증가했습니다. 제약 조건을 비활성화한 후 삽입은 12분이 걸렸습니다.

---

사례 3: 큰 파일을 단일 쿼리로 삽입하려고 (배치 처리 및 트랜잭션 없이) 시도한 결과 트랜잭션 로그가 가득 차고 데이터베이스 서버가 크래시되었습니다. 배치 처리로 전환한 후 문제는 사라졌습니다.

---