20장 물리적 데이터베이스 설계와 튜닝

　개념 스키마와 외부 스키마, 즉 릴레이션, 뷰, 그에 따르는 무결성 제약조건들을 설계한 후, 물리적 스키마(physical schema)를 설계하는 물리적 테이터베이스 설계(physical database design) 과정을 통해 성능측면에서 목표를 다루어야 한다. 사용자의 요구사항들이 바뀜에 따라서, 좋은 성능을 보장하기 위해서는 일반적으로 데이터베이스 설계의 모든 측면들을 튜닝(tuning) 또는 조정할 필요가 있다.

물리적 데이터베이스 설계 개요

데이터베이스 작업부하

　좋은 물리적 설계를 위한 핵심은 예상 작업부하를 정확하게 기술하는 것이다. 데이터베이스가 지원해야만 하는 전형적인 작업부하(workload)를 이해하는 것이 중요하다. 작업부하는 질의와 갱신들로 이루어진다. 작업부하 기술(workload description)은 다음 사항들을 포함한다.

• 질의 리스트(모든 질의/갱신연산의 비율로써 빈도)
• 갱신연산 리스트와 빈도
• 각 유형의 질의와 갱신에 대한 성능 목표

물리적 설계와 튜닝 결정사항

　물리적 데이터베이스 설계와 데이터베이스 튜닝 시 중요한 결정사항들은 다음과 같다.

• 생성할 인덱스의 선택
  • 어느 릴레이션에 인덱스를 만들 것이며 어느 필드 또는 어떤 필드들의 조합을 인덱스의 탐색키로 할 것이가?
  • 각 인덱스가 클러스터 또는 클러스터되지 않은 인덱스로 할 것인가?
  • Hash/ tree?
• 개념 스카마의 튜닝
  • 정규화된 스키마의 여러 대안들
    • 한 스키마를 원하는 정규형(BCNF 또는 3NF)으로 여러 가지 방법으로 분해할 수 있다.
    • 성능 기준을 기반으로 해서 선택을 해야 한다.
  • 반정규화(denormalication)
    • 분해된 여러 릴레이션들의 애트리뷰트들을 참고하는 질의의 성능을 향상시키기 위해, 개념 스키마 설계 과정에서 정규화를 통해 수행된 스키마 분해를 재고할 수 있다.
  • 수직분할(vertical partitioning)
    • 어떤 환경에서는 몇 개의 애트리뷰트만을 사용하는 질의릐 성능을 향상시키기 위해 릴레이션을 추가로 더 분해할 수 있다.
  • 뷰
    • 개념 스키마의 변경사항을 사용자에게 감추기 위하여 몇몇 뷰를 추가할 수도 있다.

인덱스 선택을 위한 가이드라인

　인덱스를 선택하는 하나의 방법은 가장 중요한 질의를 차례로 고려하면서, 각 질의에 대해 현재의 인덱스 리스트의 인덱스들이 주여졌을 때 최적화기가 어떤 계획을 생성할 지를 결정하는 것이다. 그런 후에, 인덱스들을 더 추가하면 상당히 더 나은 실행 계획이 가능할 지를 고려한다. 일반적으로 범위 검색에서는 B+트리가 좋으며, 완전일치 검색에는 해시 인덱스가 좋다. 클러스터링은 범위 질의에 도움이 되며, 키 값이 같은 여러 데이터 엔트리들이 있는 경우에는 완전일치 질의에도 도움이 된다.
　완전일치 셀렉션 조건에 대해서는 선택된 애트리뷰트에 대해서는 해시 인덱스를 고려하지만 범위 셀렉션 조건에 대래서는 선택된 애트리뷰트에 대해 B+ 트리 인덱스를 고려한다.
　인덱스가 인덱스 중첩루프 조인을 지원할 때 해시 인덱스가 더 좋다. 인덱스된 릴레이션이 내부 릴레이션이며, 탐색키가 조인 칼럼을 포함한다.

인덱스 선택의 기본적인 예제

Example1

SELECT E.ename, D.mgr
FROM Emp E, Dept D
WHERE D.dname=‘Toy’ AND E.dno=D.dno

　Dept의 dname 애트리뷰트에 대해 해시 인덱스를 만드는 것은 자명해 보인다. 그렇지만, 동등 조인 E.dno = D.dno를 고려해보면, Dept의 dno 애트리뷰트나 Emp의 dno 애트리뷰트에 해시 인덱스를 만드는 것이 필요할지를 알아보자. 직관적으로 D.dname=’Toy’를 만족하는 투플들은 얼마되지 않을 것이기 때문에 Dept 투플들은 dname에 대한 인덱스를 이용해서 검색하는 것이 좋을 것 같고, 이렇게 해서 찾아낸 Dept 투플 각각에 대해서 부합하는 Emp투플들을 찾는 일은 Emp 릴레이션의 dno 애트리뷰트에 대한 인덱스(해시 인덱스)를 이용하면 될 것이다. 따라서 Emp 릴레이션의 dno 필드에 대해 인덱스를 하나 만들어야 한다.
　WHERE 절을 WHERE D.dname = ‘Toy’ AND E.dno=D.dno AND E.age=25로 수정한다고 가정하면, dname에 대한 셀렉션을 만족하는 Dept 투플들을 검색하고, 부합하는 Emp 투플들을 dno필드에 대한 인덱스를 이용해서 검색하는 것이다. Emp의 age 필드에 인덱스가 있는 경우, Emp의 dno 필드에 대한 인덱스가 실제로는 필요가 없다. dname에 대한 셀렉션을 만족하는 Dept 투플을 구하고, age에 대한 인덱스를 이용해서 age 셀렉션을 만족하는 Emp 투플들을 구해서, 이 두 투플 집합들을 조인한다.

Example2

SELECT E.ename, D.mgr
FROM Emp E, Dept D
WHERE E.sal BETWEEN 10000 AND 20000
AND E.hobby=‘Stamps’ AND E.dno=D.dno

　Emp를 외부 릴레이션으로 Dept를 내부 릴레이션으로 하는 것이 최선의 계획임이 분명하고, Dept의 dno 애트리뷰트에 해시 인덱스를 만들어야만 한다. Emp에 어떤 인덱스를 만들어야 하는지 생각해보자. sal 애트리뷰트에 대해 B+ 트리 인덱스를 만들면, 이 질의에 있는 범위 셀렉션에 도움이 될 것이다. hobby 애트리뷰트에 대한 해시 인덱스는 동등 셀렉션에 도움이 될 것이다. 이 인덱스들 중 하나가 사용 가능한 경우라면, 그 인덱스를 이용해서 Emp 투플들을 검색하고, dno에 대한 인덱스를 이용해 이에 부합하는 Dept 투플들을 검색한 후, 나머지 셀렉션과 프로젝션들을 즉시로 적용한다. 두 인덱스 모두 사용가능한 경우, 최적화기는 주어진 질의에 대하여 선택도가 더 높은 접근경로를 선택할 것이다.
　As both examples indicate, our choice of indexes is guided by the plan(s) that we expect an optimizer to consider for a query. Have to understand optimizers!

클러스터링과 인덱싱

　다음 예를 보자.

SELECT E.ename, D.mgr
FROM Emp E, Dept D
WHERE D.dname=‘Toy’ AND E.dno=D.dno

　

데이터베이스 튜닝 개요

개념 스키마 튜닝

• BCNF 설계 대신 3NF 설계에서 머무르도록 결정할 수 있다.
• 3NF이나 BCNF으로 분해하는 두 가지 방법이 있다면, 작업부하를 고려해 선택한다.
• 때로는 BCNF 릴레이션도 추가적으로 더 분해하도록 결정하는 경우도 생긴다.
• 어떤 경우에는 반정규화(denormalization)를 할 수도 있다. 즉, 원래의 큰 릴레이션의 분해를 통해 얻어진 여러 개의 릴레이션들을, 중복성의 문제에도 불구하고, 원래의 릴레이션으로 다시 대체하도록 할 수도 있다.
• 릴레이션의 수평부할(horizontal partitioning)도 고려해야한다. 이는 동일한 스키마를 갖는 두 개의 릴레이션을 만들어낸다.

　개념 스키마를 재설계할 때, 기존 데이터베이스 스키마를 튜닝할 때에는 원래 스키마를 더 자연스럽게 느끼는 사용자로 하여금 스키마 변경 사실을 알지 못하게 뷰를 생성해야할 지를 고려할만 하다.

개념 스키마 튜닝시 선택사항들

Contracts (Cid, Sid, Jid, Did, Pid, Qty, Val)
Depts (Did, Budget, Report)
Suppliers (Sid, Address)
Parts (Pid, Cost)
Projects (Jid, Mgr)

　CSJDPQV, JP C, SC P, C primary key
　SDP CSJDQV is BCNF
　FD D B, no longer 3NF
　thus, SDP CSJDQV or SDP CSJDQV CJP, cost of enforcing JP C

질의와 뷰의 튜닝에 있어서의 선택사항

　SELECT 절의 DISTINCT는 중복 제거를 필요로 하는데, 이 연산은 비용이 비싸다. 따라서 가능하면 DISTINCT를 생략해야 한다. 예를들어, 단일 릴레이션에 대한 질의의 경우, 다음 조건중 하나를 만족하면 DISTINCT를 생략할 수 있다.

• 중복이 발생해도 무방하다
• SELECT 절에 언급된 애트리뷰트들이 해당 릴레이션의 후보 키를 포함하고 있다.

　때때로 GROUP BY와 HAVING은 다음과 같이 최소화 될 수 있다.

　위 두 질의는 같다.
　복잡한 질의들은 종종 임시 릴레이션을 이용해서 단계별로 작성된다. 그런 질의들을 더 빨리 수행하기 위해 임시 릴레이션을 사용하지 않는 질의로 재작성할 수 있다. 다음 예를 보자.

SELECT * INTO Temp
FROM Emp E, Dept D
WHERE E.dno=D.dno 
AND D.mgrname=‘Joe’

SELECT T.dno, AVG(T.sal)
FROM Temp T
GROUP BY T.dno

　위 질의는 다음처럼 재작성 될 수 있다.

SELECT E.dno, AVG(E.sal)
FROM Emp E, Dept D
WHERE E.dno=D.dno 
AND D.mgrname=‘Joe’
GROUP BY E.dno

　재작성된 질의는 중간 릴레이션 Temp를 실체화하지 않기 때문에 더 빠를 수 있다. 사실, 최적화기는 <dno, sal>의 복합 B+ 트리 인덱스가 있을 때 Employees 투플들을 검색하지 않는 매우 효율적인 인덱스만 사용하는 계획을 찾을 수도 있다.