친절한 SQL 튜닝 6]\
DML 성능에 영향을 미치는 요소는 다음과 같다.
- 인덱스
- 무결성 제약
- 조건절
- 서브쿼리
- Redo 로깅
- Undo 로깅
- Lock
- 커밋
인덱스와 DML 성능
테이블에 레코드를 INSERT 하면, 인덱스에도 입력해야 한다. 테이블은 Freelist 를 통해 입력할 블록을 할당 받지만, 인덱스는 수직적 탐색을 통해 입력할 블록을 찾는다. 입력하는 과정이 복잡하므로 DML 성능에 미치는 영향이 크다.
freelist 는 테이블마다 데이터 입력이 가능한 (여유 공간이 있는)블록 목록이다.
DELETE 할 때도 인덱스 레코드를 모두 찾아서 삭제해 줘야 한다.
UPDATE 할 때는 변경된 컬럼을 참조하는 인덱스만 찾아서 변경해 주면 된다. 한 건 변경할 때마다, 기존 저장 위치에서 삭제 -> 새로운 저장 위치에 저장 하기 때문에 두 개 오퍼레이션이 발생한다.
인덱스 개수가 DML 성능에 미치는 영향이 매우 큰 만큼, 인덱스 설계에 심혈을 기울여야 한다.
무결성 제약과 DML 성능
데이터베이스에 논리적으로 의미 있는 자료만 저장되게 하는 데이터 무결성 규칙으로는 아래 네 가지가 있다.
- 개체 무결성
- 참조 무결성
- 도메인 무결성
- 사용자 정의 무결성
이런 규칙들은 DBMS 에서 PK, FK, Check, Not Null 같은 Constraint 를 설정하면 더 완벽하게 데이터 무결성을 지켜낼 수 있다.
PK, FK 제약은 Check, Not Null 제약보다 성능에 더 큰 영향을 미친다. Check, Not Null 은 정의한 제약 조건을 준수하는지만 확인하면 되는데 PK, FK 제약은 실제 데이터를 조회해 봐야 하기 때문이다.
조건절과 DML 성능, 서브쿼리
select 문과 똑같다. 인덱스 튜닝, 서브쿼리 튜닝으로 성능을 개선할 수 있다.
Redo 로깅과 DML 성능
대부분의 RDBMS 는 테이블에 가해지는 모든 변경사항을 Redo 로그에 기록한다. Redo 로그는 트랜잭션 데이터가 어떤 이유에서건 유실됐을 때, 트랜잭션을 재현함으로써 유실 이전 상태로 복구하는 데 사용된다.
DML 을 수행할 때마다 Redo 로그를 생성해야 하므로 Redo 로깅은 DML 성능에 영향을 미친다.
변경사항을 Redo 로그로 저장하는 것과, 실제 테이블 데이터 파일에 저장하는 것과의 큰 차이점은 저장 방식에 있다. 데이터 파일에 반영하는 것은 랜덤 엑세스 방식으로 이루어지므로 느리다. 로그 파일에 저장하는 것은 append 방식이라 상대적으로 빠르다.
Undo 로깅과 DML 성능
Redo 는 트랜잭션을 재현함으로써 과거를 현재 상태로 되돌리는 데 사용하고 , Undo 는 트랜잭션을 롤백함으로써 현재를 과거 상태로 되돌리는 데 사용한다.
따라서 Redo 에는 트랜잭션을 재현하는데 필요한 정보를 로깅하고, Undo 에는 변경된 블록을 이전 상태로 되돌리는 데 필요한 정보를 로깅한다.
DML 을 수행할 때마다 Undo를 생성해야 하므로 Undo 로깅은 DML 성능에 영향을 미친다.
Lock 과 DML 성능
Lock 은 DML 성능에 매우 크고 직접적인 영향을 미친다. Lock 을 필요 이상으로 자주, 길게 사용하거나 레벨을 높일 수록 DML 성능은 느려진다. 그렇다고 Lock 을 너무 적게, 짧게 사용하거나 필요한 레벨 이하로 낮추면 데이터 품질이 나빠지므로 매우 세심한 동시성 제어가 필요하다.
Lock 레벨로는 로우 레벨, 블록 레벨, 익스텐트 레벨, 테이블 레벨 등이 있다. 처음에는 로우 레벨을 사용하다가 Lock 설정 대상이 많아지면 점점 레벨을 올리기도 한다.
커밋과 DML 성능
커밋은 DML 과 별개로 실행하지만, DML 을 끝내려면 커밋까지 완료해야한다. 특히 DML 이 Lock 에 의해 블로킹된 경우, 커밋은 DML 성능과 직결된다. DML 을 완료할 수 있게 Lock 을 푸는 열쇠가 커밋이기 때문이다.