여러 관련 테이블에서 대량 데이터를 업데이트하는 것은 SQL로 산업 프로그래밍에서 전통적인 문제입니다. 비즈니스 애플리케이션의 발전으로 인해 대량의 데이터를 동시에 업데이트 할 필요성이 생겼고, 동시에 이들의 일관성을 보장해야 했습니다. 역사적으로 순환 시나리오가 사용되어 성능 저하와 긴 블로킹을 야기했습니다. 이후에는 고급 DML 연산자(예: MERGE), 트랜잭션 구조 및 스테이징 테이블을 사용하는 방법이 등장했습니다.
문제는 데이터 업데이트가 관계가 있는 여러 테이블(예: 주문 및 주문 세부정보)을 포함하므로 "고아 행"이 발생하고, 블로킹으로 인한 성능 저하 및 데이터베이스에 대한 예측 불가능한 부하가 발생할 수 있다는 것입니다.
해결책은 원자적 트랜잭션, JOIN 조건을 포함한 UPDATE/DELETE/MERGE 작업 및 데이터의 배치 처리를 활용하는 것입니다. 좋은 방법은 집계된 변경 사항을 임시 스테이징 테이블에 보관한 다음, 트랜잭션을 통해 배치로 적용하는 것입니다. SQL Server에서 MERGE를 사용한 예:
BEGIN TRANSACTION; -- MERGE를 사용한 기본 테이블과 관련 테이블의 대량 업데이트 예 MERGE INTO orders AS tgt USING temp_order_updates AS src ON tgt.id = src.id WHEN MATCHED THEN UPDATE SET tgt.status = src.status, tgt.updated_at = src.updated_at; MERGE INTO order_details AS tgt USING temp_detail_updates AS src ON tgt.order_id = src.order_id AND tgt.sku = src.sku WHEN MATCHED THEN UPDATE SET tgt.price = src.price, tgt.qty = src.qty; COMMIT;
주요 특징:
첫 번째 테이블을 업데이트 한 다음, 트랜잭션 없이 관련된 테이블을 개별적으로 업데이트 할 수 있습니까, 속도가 중요한 요구 사항이라면?
트랜잭션 외부에서 개별 UPDATE는 모든 단계에 오류가 발생할 경우 데이터가 심각하게 비일관해집니다. 예를 들어, 주문이 업데이트 되었지만 세부 정보가 업데이트되지 않았다면, 로직이 깨집니다. 현대의 데이터베이스에서 트랜잭션 사용은 배치 처리에서 거의 추가 오버헤드를 증가시키지 않습니다.
서브쿼리를 사용하여 큰 UPDATE를 한 경우 성능이 저하될까요? 블로킹을 초래할 수 있습니까?
네, 대형 테이블에 대한 단일 UPDATE는 블로킹을 증대시키고 테이블 잠금 및 다른 사용자들의 대기 시간을 초래합니다. WHERE ... AND rownum/id/limit를 사용하여 처리를 배치로 나누는 것이 더 나은 방법입니다.
배치 예:
UPDATE orders SET status = 'closed' WHERE status = 'pending' AND id BETWEEN 100000 AND 199999;
MERGE는 원자성과 관련 테이블 처리의 올바른 순서를 보장합니까?
아니요, MERGE는 단일 테이블 내에서 작동합니다. 관련 테이블을 업데이트하려면 별도의 MERGE 또는 UPDATE가 필요하며, 반드시 두 작업을 한 트랜잭션 내에 넣어야 합니다.
한 회사가 트랜잭션 외부에서 주문 100만 개의 상태를 "완료"로 업데이트 했습니다: 기본 orders, 그 후에 order_details. 서버는 부하가 걸리면 "중단"되었으며, 오류가 발생할 경우 세부정보는 "열림" 상태로 남아있었습니다. 장점:
단점:
스테이징 테이블과 트랜잭션 내의 그룹 처리를 도입했습니다. 먼저 모든 변경 사항을 계산하여 임시 테이블에 쌓은 다음, 두 기본 테이블을 배치로 업데이트했습니다. 오류가 발생할 경우 전체 롤백이 이루어집니다. 장점:
단점: