Physical Database Design and Tuning
물리적 데이터베이스 설계와 튜닝
* Workload
- Query와 Update가 수행되어지는 양(빈도)의 개념이다.
- 사용자들로부터의 Query가 얼마나 빠르게 처리되어야 하는지,
얼마나 많은 Transaction들이 수행되어야 하는지에 대한 요구조건들이 명시되어 있다.
* Index
- Relation의 레코드를 빠르게 검색하기 위한 도구이다.
- 일반적으로 B+ Tree, Hash Table 등으로 구현된다.
Physical Design and Tuning Decisions (물리적 설계와 튜닝에서의 결정사항)
- 물리적 DB의 설계와 DB Tuning시, 중요한 결정사항들은 아래와 같다.
1. 생성할 Index의 선택
- 어느 Relation의 index를 생성할 것이며, 어느 Field를 Index의 Search Key로 할 것인가
- 각 Index는 Clustered한가 Unclustered한가
2. Conceptual Schema Tuning
- Performance Criteria를 기반으로, Schema를 어떤 Normal Form으로 Decomposition할 것인가
- Denormalization*이 필요한가
- Vertical Partitioning이 필요한가
- View를 생성해야 하는가
* Denormalization
- Decomposition한 Table을 다시 Join하는 작업이다.
- 때로는 Query Performance를 위해 Denormalization이 필요한 경우도 있다.
3. Query와 Transaction Tuning
- 빈번히 수행되는 Query와 Transaction을 Rewrite하여 Performance를 제고할 여지가 있는가
Tuning the Conceptual Schema (=DB Tuning)
- Data Redundancy로 인해 발생되는 여러 Anomaly한 현상들을 방지하고자 Relation에 대한 Normalization을 수행한다.
- Query의 Workload를 파악하여 Normalization시, 3NF로 Decomposition할 지, BCNF로 Decomposition할 지를 정한다.
- Query Workload의 Performance를 높이기 위해,
BCNF 형태를 다시 Decomposition하거나 Denormalization하는 경우도 있을 수 있다.
- Query Workload의 Performance를 높이기 위해,
일반적인 Vertical Decomposition과 더불어 Horizontal Decomposition도 수행할 수 있다.
* Schema Evolution
- DB가 만들어져서 이미 사용중인 동안에, 위와 같은 개선이 일어난 경우를 의미하는 용어이다.
Setting for a Weaker Normal Form
- 아래와 같은 DB Schema가 있다 하자.
Contracts(Cid, Sid, Jid, Did, Pid, Qty, Val) (Candidate Key: Sid+Did+Pid)
Depts(Did, Budget, Report)
Suppliers(Sid, Address)
Parts(Pid, Cost)
Projects(Jid, Mgr)
- 위 DB중 Contracts Relation에는 아래와 같은 Functional Dependency가 있다 하자.
FD: \(C \to CSJDPQV\) (\(C\)가 Primary Key이기 때문에 생성된 FD이다.)
FD: \(JP \to C\)
FD: \(SD \to P\)
- \(JP\)와 \(SD\)가 Contracts Relation에서 Primary Key가 아니기 때문에, Contracts는 BCNF가 아니다.
- 그러나, 우변의 \(C\)는 Key의 일부(사실, Key 그 자체)이고, \(P\)는 Candidate Key이기 때문에
Contracts는 3NF이다.
\(CSJDPQV\)를 \(SDP\)와 \(CSJDQV\)로 Decomposition하자.
\(SDP\)와 \(CSJDQV\)는 BCNF이다.
- Lossless Join Decomposition은 성공적으로 수행되었지만,
Decomposition 이후에 FD: \(JP \to C\)는 보존되지 못했으므로, Dependency Preserving에는 실패했다.
- FD: \(JP \to C\)를 Preserving하기 위해, \(SDP\)와 \(CSJDQV\)에 더불어 \(CJP\)를 추가적으로 Decomposition하여 생성할 수 있다.
- 만약, Query Workload에서 매우 중요시되는 Query가 아래와 같다 하자.
Query: Contracts \(C\)에서 Parts \(P\)의 \(Q\)(개수)를 찾아라.
- 즉, 위 Query는 \(Q, P, C\)에 대한 Query이므로, \(SDP\)와 \(CSJDQV\)를 Join해야 하므로, Query Performance가 떨어지게 된다.
- 즉, 위 Query에 한정해서,
Decomposition이전의 Original Relation인 \(CSJDPQV\)가 오히려 더 나은 Query Performance를 보인다.
(join 연산이 필요 없기 때문이다.)
- 즉, 위 Query에 한정하여, 3NF가 BCNF보다 더 나은 Query Performance를 보인다.
Denormalization
- 의도적으로 Data Redundancy를 허용하여 Query Performance를 향상시키는 방법이다.
Example. Denormalization
- 아래와 같은 DB Schema가 있다 하자.
Contracts(Cid, Sid, Jid, Did, Pid, Qty, Val) (Candidate Key: Sid+Did+Pid)
Depts(Did, Budget, Report)
Suppliers(Sid, Address)
Parts(Pid, Cost)
Projects(Jid, Mgr)
위와 같은 DB에서, 아래와 같은 Query가 매우 빈번히 처리된다 하자.
Query: Contracts 금액이 계약을 진행한 부서의 예산(Budget)보다 적은 경우를 찾아라.
이 경우, Contracts Relation에 Budget Field(이하 B라 표현)가 필요하므로, Contracts에 B를 추가하게 된다.
즉, Contracts(CSJDPQVB)가 된다.
또한, Depts의 Key가 Did이므로, Contracts에는 FD: D \to B가 존재하게 된다.
따라서, B를 추가함으로 인해, Contracts는 더 이상 3NF가 아니게 된다.
하지만, 위와 같은 Query를 진행하기에는 B가 추가된 형태가 3NF 보다 나은 Query Performance를 보인다.
Example. Choice of Decomposition
- 아래와 같은 DB Schema가 있다 하자.
Contracts(Cid, Sid, Jid, Did, Pid, Qty, Val) (Candidate Key: Sid+Did+Pid)
Depts(Did, Budget, Report)
Suppliers(Sid, Address)
Parts(Pid, Cost)
Projects(Jid, Mgr)
- 위 DB중 Contracts Relation에는 아래와 같은 Functional Dependency가 있다 하자.
FD: \(C \to CSJDPQV\) (\(C\)가 Primary Key이기 때문에 생성된 FD이다.)
FD: \(JP \to C\)
FD: \(SD \to P\)
즉, Contracts는 3NF이다.
- 위 Contracts Relation의 Data Redundancy를 처리하는 방법은 아래의 경우들로 나뉜다.
1) Contracts를 수정하지 않고, 3NF로 유지하여 Data Redundancy를 감수한다.
2) Contracts를 BCNF로 Decomposition하여 Data Redundancy으로인한 Anomaly를 예방한다.
i) CSJDPQV를 SDP, CSJDQV로 분할
- Lossless Join에는 성공했지만, Dependency Preserving에는 실패한 분할이다.
(JP \to C가 Preserving되지 않았다.)
ii) CSJDPQV를 SDP, CSJDQV, CJP로 분할
- Lossless Join이 가능하고, Dependency Preserving에 성공한 분할이다.
- 그러나, CJP 테이블은 FD: JP \to C를 위한 테이블이며, 자주 사용되지 않을 것이라는 점에서 비효율적이다.
Example. Choice of Decomposition
- 위 DB Schema에서 Contracts에 아래와 같은 FD들이 존재한다 하자.
FD: \(C \to CSJDPQV\) (\(C\)가 Primary Key이기 때문에 생성된 FD이다.)
FD: \(JP \to C\)
FD: \(SD \to P\)
Additional FD: \(SPQ \to V\)
(Suppliers가 같은 Parts에 대해서는 항상 동일한 Value를 청구한다.)
- CSJDPQV를 BCNF로 Decomposition할 경우, 아래와 같이 분할할 수 있다.
CSJDPQV를 SPQV, CSJDPQ로 분할한다.
CSJDPQ를 다시 SDP와 CSJDQ로 분할한다.
JP \to C를 Preserving하기 위해 CJP로 추가적으로 분할한다.
- Query Workload 상황에 맞춰서, 위와 같이 전부 분할할 수도 있고, 일부만 분할하고 멈출 수 있다.
CSJDPQV를 SDP, CSJDQV로 분할했다 가정하자.
SDP, CSJDQV는 BCNF이다.
여기서, C와 S에 연관된 Query, 혹은 C와 D에 연관된 Query가 중요시된다 하자.
CSJDQV를 다시 CS, CJQV로 분할하여 C와 S에 연관된 Query에 최적화시킬 수 있고,
CSJDQV를 다시 CD, CJQV로 분할하여 C와 D에 연관된 Query에 최적화시킬 수도 있다.
(즉, 이미 BCNF보다 더 높은 수준의 Normalization을 진행하는 형태이다.)
단, 위처럼 세부적으로 분할해놓을 경우, 레코드의 전반적인 Field값을 요구하는 Query에 대해서는 좋은 Performance를 내기 힘들다.
(세부적으로 분할해놓은 테이블들을 다시 Join시켜야 하기 때문이다.)
※ 즉, Query Workload를 면밀히 분석하여 최적화하는 것이 중요하다.
Horizontal Decomposition (= Selection)
- 특정 Field의 값에 따라 적용되는 FD가 다른 경우, Horizontal Decomposition을 통해 Refinement를 수행할 수 있다.
Example. Vertical Decomposition (= Projection)
FD: SD \to P가 존재하는 CSJDPQV를 SDP와 CSJDQV로 분할하는 것은 Vertical Decomposition에 속한다.
Example. Horizontal Decomposition (= Selection)
CSJDPQV에서,
V > 10,000일 경우에는 Large_Contracts {CSJDPQV}로 분할하고,
V <= 10,000일 경우에는 Small_Contracts {CSJDPQV}로 분할한다.
Large_Contracts와 Small_Contracts에는 각각 다른 FD가 적용될 것이다.
Masking Conceptual Schema Change
- View를 통해 Conceptual Schema의 변화를 감출 View를 생성하여 일반 사용자에게 Abstraction을 제공할 수 있다.
Example. View
CREATE VIEW Contracts(cid, sid, jid, did, pid, qty, val)
AS SELECT *
FROM LargeContracts
UNION
SELECT *
FROM SmallContractsTuning Query and Tuning View
Tuning Query
1) 가능한 한, Nested Query를 Unnested Query로 변환하기
Example. Tuning Query (= Rewrite SQL Qurey)
SELECT DISTINCT *
FROM Sailors S
WHERE S.sname IN
(SELECT Y.sname
FROM YoungSailors Y)
-- Tuned Query below:
SELECT DISTINCT S.*
FROM Sailors S,
YoungSailors Y
WHERE S.sname = Y.sname- Nested Query는 Performance를 저해하므로, 가급적 Flat한 Query로 구현해야 한다.
- 그러나 아래 SQL 예시처럼, Nested Query가 꼭 Flat하게 변환되지 않을 수 있음에 유의해야 한다.
Example. Wrong Conversion
SELECT *
FROM Sailors S
WHERE S.sname IN
(SELECT DISTINCT Y.sname
FROM YoungSailors Y)
-- 위 Query와 아래 Query는 동치가 아니다
SELECT S.*
FROM Sailors S,
YoungSailors Y
WHERE S.sname = Y.sname
2) Minimize DISTINCT Option
- Duplicated Record를 제거하기 위해, Sorting 혹은 Hashing이 이루어지는데, 이들은 Overhead를 발생시킨다.
3) Minimize the use of GROUP BY, HAVING
- GROUP BY, HAVING 또한, DISTINCT 옵션처럼 Sorting을 수행하는 과정에서 Overhead가 발생된다.
Example. Minimize the use of \(\texttt{GROUP BY, HAVING}\)
SELECT MIN (E.age)
FROM Employee E
GROUP BY E.dno
HAVING E.dno=102
-- Minimize GROUP BY and HAVING like below:
SELECT MIN (E.age)
FROM Employee E
WHERE E.dno=102
4) Remove Temporary Table
- 오직 Query를 위해 존재하는 임시 테이블들은 가급적 생성되지 않게 하여 Performance 향상을 이루어낼 수 있다.
Example. Remove Temporary Table
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
-- Remove Temporary Table like below:
SELECT E.dno, AVG(E.sal)
FROM Emp E, Dept D
WHERE E.dno=D.dno
AND D.mgrname=‘Joe’
GROUP BY E.dnoReference: Database Management Systems 3E (Raghu Ramakrishnan, Johannes Gehrke 저, McGrawHill, 2003)
