데이터베이스 튜닝

ER다이어 그램을 설계하고 스키마를 정제하고 뷰를 정의한 뒤에, 디비 튜닝 까지 완료 해주어야 한다.

SQL질의를 던졌는데 버벅거리면서 제대로된 성능이 나오지 않을때 자동차를 튜닝하는것처럼 디비를 튜닝 해주어야 한다.

DB tuning 

1. index 적절하게 사용 -> 질의가 들어 왔을때 디스크에 존재하는 데이터를 가장 빠르게 찾아주는것 (B+TREE,Hash)

2.SQL Rewriting -> 중첩쿼리(Nested QUERY) 제거 -> 중첩 쿼리 대신 조인 사용, Grouping 최소화

3.정규화 / 역정규화 -> 정규화를 많이 할수록 데이터 중복성은 제거되지만 퍼포먼스는 떨어짐.

우리는 디비 튜닝할때 정규화/ 역정규화에 대해서 자세히 살펴볼것이다.

회사에 중요하고 자주사용하는 질의가 무엇인지 분석하는게 제일 중요하다.

역정규화를 통한 workload에 대한 디비 성능 개선

workload란 중요하고 자주사용되는 질의를 의미한다.

workload에 대한 퍼포먼스를 높여야 한다.

workload를 분석해서 뽑아내고 이것을 통해 테이블의 역정규화/정규화를 다시 한번 살펴봐야한다.

상황에 따라 퍼포먼스를 위해서 보이스 코드 정규형 까지 갈수 있지만 제2정규형 까지만 유지하는 경우도 있다.

정규화 라는것은 ABC 를 AB와 BC 테이블 2개로 수직 분할 한것이다. 테이블을 수평 분할 한 경우 같은 속성을 갖는 테이블이 2개 만들어지고 튜플을 반반씩 나눠갖게 된다. 우리가 말하는 정규화는 수직 분할을 의미한다.

스키마를 튜닝할때 보통은 수직분할을 의미하지만 어떤 상황에 따라서 수평분할이 질의 성능을 늘릴수도 있다.

위와 같은 스키마가 있다고 해보자.

Contratct,Supplier,Project,Department,Part,Quantaty,Value일때

Contracts라는 테이블에 JP->C SD->P C->CSJDPQV라는 함수 종속성이 있다고 해보자.

이 테이블은 제3정규형이 아니다. 왜냐면 SD->P에서 결정자인 SD가 키가 아니고 P가 키의 일부분도 아니기 때문이다.

제3정규형은 결정자가 키 이거나 결정당한것이 키의 일부분이어야 한다.

이 테이블을 제3정규형이상으로 만들어 주기 위해서 테이블을 수직분할 해야한다.

분할할때 loseless join과 dependancy preserving을 하면서 분할해야한다.

CSJDPQV

SDP    CSJDQV

이런식으로 테이블을 수평분할 할 수 있을것이다. 하지만 이렇게 테이블을 쪼갤 경우 JP->C라는 의존성이 사라졌다. 함수 의존성을 보존하기 위해서 JPC테이블을 새로 만들 경우 데이터 중복성을 없애기 위해 테이블을 쪼갠 의미가 사라지고 오히려 더 중복성이 많아지게 된다.

그렇기 때문에 위처럼 테이블을 2개로 분해하는 경우 무손실 조인은 가능하지만 함수 의존성 보존이 되지 않는다. 그렇기 때문에 잘못 쪼갠것이다.

따라서 minimal cover로 쪼개면 loseless join과 의존성 보존이 보장된다.

만약에 C라는 계약에서 구매한 부품 P의 수량Q를 찾는 질의가 굉장히 중요하며 자주사용되는 쿼리 = workload라고 가정하자.

이 질의에서 사용되는 속성은 QPC이다. 따라서 2개로 쪼개진 테이블에 위의 질의를 적용하려면 두개의 테이블을 다시 조인해야한다.

조인은 cost가 비싸다. 그렇기 때문에 차라리 테이블을 쪼개지 말고 원래 형태로 역정규화한다(제2정규형으로). 그게 퍼포먼스를 위해서 더 좋다.

위의 CSJDPQV는 어차피 보이스코드 정규형으로 만들수가 없다. 근데 만약에 보이스코드 정규형으로 만들수 있다고 생각했을때 위의 말이 성립한다.

계약금이 부서의 예산보다 큰지 적은지를 질의하는 sql문이 workload라고 해보자.

이 workload에서 필요한 속성은 D(부서),Budget(예산),Q(금액),C(계약) 근데 원래 테이블에 Budget속성이 없으므로 새로 추가해줘야한다.

B를 추가함으로써 D->B라는 함수 종속성이 새로 나타나게 된다. (부서가 예산을 결정한다)

CSJDPQV테이블이 있는데 이 테이블이 보이스코드 정규형이라고 가정해보자.(물론 위의 함수 종속성을 적용시키면 제2정규형이다)

근데 B라는 속성을 추가함으로써 보이스코드 정규형이 깨지게 된다. 왜냐하면 키가 아닌 속성이 또다른 키가 아닌 속성을 결정하기 때문이다.

D->B라는 함수 종속성이 추가로 생기기 때문이다. D도 키가 아니고 B도 키의 일부가 아니기 때문에 제3정규형이 아니다.

부분적 함수 종속성만 없는 제2정규형이 된것이다. B라는 속성이 추가됨으로써 역정규화 된것이다. D->B때문에 데이터 중복성은 좀더 생기겠지만 DBQC를 이용한 질의가 workload이므로 이 상태로 내비두는게 퍼포먼스에 더 좋은 영향을 미친다.

근데 이런 데이터 중복성 때문에 안좋은 이상현상이 심각하게 발생할 경우 다시 테이블을 정규화 할수도 있다.

이런것을 잘 결정하는게 데이터베이스 관리자의 역할이다.

정규화를 통한 workload에 대한 디비 성능 개선

만약 CSJDPQV 테이블에 SPQ->V라는 함수 종속성이 있다고 생각하자.

(어떤 주어진 부품에 대한 공급자가 항상 같은 가격을 책정한다)

CSJDPQV

SPQV    CSJDPQ

이렇게 테이블을 수직분할 할 수 있다.

위의 2개의 테이블은 각각 보이스코드 정규형이 되었다.

근데 이때 쿼리 workload를 봤는데

첫번째 workload가 공급자 s에 관한 계약을 찾는 질의

두번째 workload가 부서 D와 관련된 계약을 찾는 질의라고 할때,

CS,CD와 관련된 질의를 해야 한다.

따라서 

CSJDPQV

SPQV    CSJDPQ

CS    CD    CJQV

이런식으로 테이블을 한번더 수직분할 한 경우 보이스 코드 정규형을 한번더 수직분할 했으므로 4NF이다.

정규화를 더 함으로써 workload에 대한 퍼포먼스를 더 높였다.

앞의 예에서는 workload를 위해서 조인을 했어야 했기 때문에 다시 역정규화를 했지만 여기서는 정규화를 함으로써 더 성능이 좋을것으로 예상이 된다.