엘비

2차 저장 공간

업데이트:

태그:

카테고리:

  • 튜닝은 스키마튜닝 -> 인덱스튜닝 -> 쿼리튜닝 순으로 진행한다.
  • DBA는 이 3가지의 튜닝을 진행하며 성능을 관리한다.

스키마 튜닝

  • 디자인 상에서 정규화를 많이하면 할수록, redundancy는 감소하지만, join으로인해 workload가 증가한다.
  • 기본적으로 제 3 정규화를 한다.
    • workload가 적다면, 물리적 데이터베이스 크기를 줄이기위해 BCNF로 정규화한다.
    • workload가 많다면,
      1. 필드를 추가해 역정규화를 (denormalize)한다.
      2. 혹은 수평 분할을 고려한다.


예시

Contracts(C,S,J,D,P,Q,V)
F = {JP -> C, SD->P}, PK = C, CK = JP
  • 위의 예시는 부분종속과 이전종속이 존재하지 않으므로, 제3정규형이다.
  • 후보키 (JP, C)의 진부분집합이 다른 nonprime attribute를 결정하지 않으며,
  • nonprime attribute가 다른 nonprime attribute를 결정하지 않는다.
  • 하지만, 함수적 종속관계에서 SD는 후보키가 아니므로, BCNF는 아니다.


  • 위 스키마를 BCNF로 만들기 위해선 SDP를 따로 분해해 스키마를 만들어야한다.


BCNF로 정규화하기

  1. BCNF로 만들기
SDP, CSJDQV

위 두 relation모두 BCNF이다.

  • lossless decomposition을 만족하지만,
  • dependency preserving 하지는 않다.
    • 왜냐하면, JP->C의 함수적 종속이 사라졌기 때문이다.
  • 함수적 종속성 보존을 위해서는 별도의 ASSERTION을 정의해줘야한다.


  1. 스키마 1개 추가
  • 따라서, dependency preserving하기 위해서는 JPC라는 스키마가 1개 추가되어야한다.
    • {SDP}, {CSJDQV}, {CJP}
  • 스키마가 1개 추가됨에따라, JOIN 성능에 영향을 주기때문에, JP에 Index를 걸어주면된다.


  1. SPQ->V 종속성이 추가되었을때
    • CSJDPQV => SPQ->V + CSJDPQ
      • CSJDPQ => SD=>P + CSJDQ
    • 따라서, SDP, CSJDQ, SPQV 모두 BCNF가 된다.
    • 단, 이 경우에도 CJP도 더해줘야 dependency preserving이 가능하다.


역정규화를 해야할때

SDP, CSJDQV

  1. CP=>Q와 같은 질의가 중요하다면 위 스키마를 사용하는게 아닌 3차정규형 상태 그대로를 사용하는 것이 좋다.

  2. V와 B를 비교하는 질의가 중요하다면, CSJDPQVB로 B를 스키마에 추가하는 것이 좋다.

    • 물론, 이렇게 된다면 nonprime attribute D가 B를 결정하므로, 2차정규형으로 역정규화된 것이다.


BCNF 분할하기

  • 위 예시에서 아래 스키마는 각각 BCNF이다. 
    {SDP, CSJDQV}


  • 아래와 같은 쿼리가 중요하다고 가정하자.
    • S가 가진 C를 가져와라.
    • D가 참여하는 C를 가져와라.


  • 현재 모든 스키마가 BCNF이지만 위 쿼리는 JOIN이 필요하지 않다.
  • 하지만 성능을 더 올릴 수 있는데, 메모리 I/O를 줄이는 방식으로 가능하다.
    • CS, CD, CJQV로 분할하여 쿼리 속도를 높일 수 있다.


  • 하지만, CS->V를 가져오는 쿼리는 당연하게도 느려지게된다.


  • 위에서 봤듯, 스키마 튜닝은 성능 향상에 지대한 영향을 주지만, 그만큼 어렵다.
  • 튜닝은 SQL튜닝-> 인덱스튜닝->스키마튜닝 의 순서로 진행하며, 성능 향상의 정도는 이 순서의 반대이다.



Horizontal Decompositions

  • 수평분할은 하나의 relation을 여러개의 relation으로 잘라내는 것이다.
  • 스키마 분할은 수직분할이며, 속성을 칼럼단위로 projection으로 자른 것이다.
  • 수평분할은 selection으로 잘라낸 것이다.
    • 이러한 수평분할을 빅데이터에서는 sharding이라고한다.


예시

  • 쿼리에서 특정 속성값이 10000 이상인 record를 뽑아낸다고 가정하자.

  • 위 쿼리를 빠르게하는 방법은 2가지가 있다.

    1. val 필드에 index를 건다.
    2. 10000개를 기준으로 수평분할을 한다.
      • 수평분할해놓고, 모든 데이터가 필요하다면, VIEW를 정의한다.



쿼리, 뷰 튜닝

  • 특정 쿼리가 느리다면, 인덱스를 재생성한다.
  • 인덱스를 다시 만들어도 느리다면, DBMS OPTIMIZER의 약점을 확인해야한다

Query Optimizer

쿼리가 처리되는 절차

  1. SQL 쿼리가 입력된다.
  2. Quary parser에 전달된다.
    • 파서는 이를 AST(추상구문트리)로 변환한다.
  3. Query Rewrite에 전달된다.
    • 재작성기에서는 정규형식으로 쿼리를 변환한다.
  4. Query Optimizer에 전달된다.
    • 추상구문트리를 효율적인 계획으로 변환한다.
    • Plan generator와 Cost Estimator로 구성되어있다.
    • Cost Estimator는 시스템 카탈로그에서 다른 테이블에 대한 스키마를 가져와 쿼리 계획을 어떻게 짜는 것이 효율적인가에 대해 파악한다.
  5. Query Executor
    • 효율적으로 생성된 쿼리에 대해 실행한다.


구성요소

  1. Query parser
  • 권한 확인
    • 쿼리 파서에 전달되는 쿼리는 사용자의 권한을 체크하고, 쿼리를 수행할 권한이 있는 사용자인지 확인하는 절차를 가진다.
  • Parse tree 생성
    • AST(추상구문트리)를 생성한다.


  1. Query rewriter
  • 추상구문트리를 캐노니컬 폼으로 변환한다.
  • 뷰를 가져와서 다른 테이블에 조인하는 쿼리를 4개의 테이블 쿼리로 평면화해 일부 경우에 뷰를 참조한다.
  • 일부 쿼리를 join으로 변환할 수 있기에, subquery들을 더 적은 수의 쿼리 블록으로 변환한다.


  1. Query Optimizer
  • 비용을 기반으로한다.
    • 비용을 계산할때, 한 번에 1개의 쿼리블록만 계산한다.
    • 이때, 쿼리 블록이란 아래의 묶음이다.
      1. Select, Project, Join
      2. Group by / Aggregate FNC
      3. Order By
  • 시스템 카탈로그에서 쿼리블록마다 최소비용으로 찾는 통계를 사용한다.
  • 항상 올바르게 최적화하는 것은 아니다.


  • 쿼리블록들은 관계대수로 변환된다.
  • 아래와 같은 쿼리가 있다고 가정하자. 스크린샷 2022-12-07 15 29 31


  • 위 쿼리 블록은 아래와 같은 관계대수로 변환되며, 스크린샷 2022-12-07 15 29 40

  • 관계대수는 아래와 같은 트리형태로 다시 변환된다. 스크린샷 2022-12-07 15 29 46


약점

  1. nested subquery 지양하기
    • 반드시 써야하는 경우를 제외하곤, 1개의 쿼리블록 내에 모두 표현하는 것이 좋다.
    • 쿼리블록을 기준으로 최적화되기 때문이다.
  2. Distinct 줄이기
    • distinct는 기본적으로 쿼리 실행전에 sorting하기때문에 느려진다.
  3. Group By 줄이기
    • Group By 또한 정렬하기때문에 느려진다.
  4. Null값 포함한 selection 줄이기
  5. 연산 selection 줄이기
  6. OR 절 줄이기
  7. 임시테이블 지양하기



정리

  • DB 튜닝 == 개념적 스키마의 변경
    • 성능상에서 괄목할만한 변화를 가져올 수 있다.
      1. workload에 따라 BCNF아래의 3NF 혹은 더 아래의 정규화를 수행한다.
      2. 역정규화를 통해 분할된 테이블을 합친다.
      3. BCNF형 스키마를 수직-수평분할을 통해 더 분할 할 수 있다.
      4. 무결성 검증을 위해 항상 종속성은 보존되어야한다.
  • 인덱스 튜닝
    • 인덱스는 항상 삭제 - 생성 - 재생성으로 성능적으로 잘 튜닝되어야한다.
  • DBMS는 항상 left-deep plan만을 고려한다.



튜닝 의사결정

  • Workload 의사결정
  1. Query
    • 가장 중요한 쿼리가 무엇이고, 얼마나 자주 일어나는가?
      • 어떤 relation에 접근하는가
      • 어떤 속성값을 받아오는가
      • selection, join에 사용되는 속성이 무엇이고, selectivity가 어느정도인가?
    • 가장 중요한 업데이트가 무엇이고, 얼마나 자주 일어나는가?
      • selection, join에 사용되는 속성이 무엇이고, selectivity가 어느정도인가?
      • INSERT/DELETE/UPDATE 중 무엇이고 어떤 속성이 영향받는지?
    • 그리고 이 쿼리와 업데이트의 이상적인 성능은 어느정도인가?
  2. Index
    • 어떤 인덱스를 만들어야하지?
    • 어떤 relation에 index를 만들 지,
    • 어떤 필드가 search key가 되고, 여러 index를 만들어야하는지?
    • 각각의 인덱스를 어떤 종류로 만들어야하는지?
      1. Clustered
      2. Hash/Tree
      3. Dynamic/Static
  3. Schema
    • 개념적 스키마를 변경해야하는지?
    • 다른 정규화된 스키마를 사용할지?
    • 역정규화를 할지?
    • 수평분할/중복/뷰 를 통해 처리할지?

인덱스 튜닝

  • 위 2개의 튜닝은 스키마 튜닝과 SQL 튜닝이다.
  • 성능 향상의 정도는 스키마 튜닝이 RDBMS의 근본적인 원리를 조작하는 것이기때문에 가장 향상의 정도가 높고,
  • 그 다음이 인덱스 튜닝이 성능 향상에 많은 도움이 된다.

인덱스란

  • 사전적 정의는 색인, DB상에서 데이터를 빠르게 검색하기 위한 데이터베이스의 일부분을 의미한다.
  • SELECT절에서 WHERE절에 인덱스가 적용된 칼럼이 사용된다면, DBMS는 인덱스를 사용해 데이터를 검색하게된다.
  • 항상 정렬된 상태를 유지하며, 10%의 추가적인 용량이 필요하다.


DB가 데이터를 찾아오는 알고리즘

  1. Full Table Scan
    • 페이지의 모든 값을 순회한다.
  • Full Table Scan은 아래와 같은 경우에 사용한다.
    1. 현재 쿼리가 적용가능한 인덱스가 없는 경우
    2. 인덱스의 처리 범위가 너무 큰 경우
    3. 크기가 작은 테이블에 액세스하는 경우


  1. B+Tree
      1. 보통 우리가 아는 BST(Balanced Search Tree)에서 자식 노드가 2개 이상이고 트리의 높이가 항상 같은 자료구조이다.
      2. 데이터 베이스 인덱스의 기본적인 구조이다.
  • B+ Tree에서 루트노드와 브랜치노드는 자식노드의 정보를 가지고 있다.
  • 리프 노드는 클러스터링인가 논클러스터링인가에 따라 가지고있는 데이터가 다르다.
    • 클러스터링 인덱스의 경우, 리프노드에 실제 데이터 페이지가 담겨있으며,
    • 논클러스터링 인덱스는 리프노드에 데이터의 주소 페이지가 담겨있다.


클러스터링 인덱스

스크린샷 2022-12-10 00 48 35

CREATE TABLE test (
	id int PRIMARY KEY,
	name char(20),
	email char(20) unique
	);
  • 위의 쿼리에서 인덱스는 총 2개가 생성된다.
  • PRIMARY KEY인 id에는 클러스터링 인덱스
  • UNIQUE NULL 제약이 걸린 email은 논클러스터링 인덱스가 생성된다.


  • DBMS는 PRIMARY KEY 또는 UNIQUE+NOT NULL 제약이 걸린 칼럼을 클러스터링 인덱스로 생성하는데,
    • 우선순위는 물론 PRIMARY KEY에 있다.
  • 실제 데이터 자체가 정렬되며
  • 테이블당 1개가 존재한다.
  • 위에서 언급했듯, 리프 페이지 자체가 데이터를 담고있다.


논클러스터링 인덱스

ALTER TABLE test ADD CONSTRAINT unq_name UNIQUE(name);

--중복을 허용하지 않는 인덱스
CREATE UNIQUE INDEX unq_idx_name ON test(name);

--중복을 허용하는 인덱스
CREATE INDEX idx_name ON test(name);
  • UNIQUE + NULL 제약은 논클러스터링 인덱스를 생성한다.

  • 또한, CREATE INDEX 명령을 통해 생성하는 인덱스도 논클러스터링 인덱스이다.

  • 위에서 언급했듯, 논클러스터링 인덱스의 리프노드는 데이터를 직접 가지고있는 것이 아닌, 데이터가 담겨진 페이지의 주소를 가진다.
  • 따라서 별도의 인덱스 페이지가 필요하다.
  • 여기에 offset 값으로 원래 값이 페이지의 주소로부터 몇 byte뒤에 있는지를 표현한다.
  • 또한 테이블당 여러개 존재할 수 있다.


Hashing Index

  • 큰 크기의 데이터를 저장할때, 데이터를 가져오기위해 모든 인덱스를 찾는 것은 비효율적이다.
  • 따라서 해싱을 통해 데이터가 디스크의 어느위치에 정확하게 존재하는지 계산할 수 있다.
  • 해시함수를 통해 데이터가 저장된 주소를 알아낼 수 있다.
  • 해싱에는 2가지 방법이 있다.
  1. 정적 해싱
    • 정적 해싱은 해시함수를 통해 나오는 데이터 주소가 항상 동일하다는 것이다.
    • 정적 해싱을 통해 데이터 주소에 담기는 데이터의 양은 항상 동일하다.
  2. 동적 해싱
    • 정적 해싱과 다르게, 데이터 주소에 담기는 데이터의 양이 동적이라는 의미.
    • 빠르게 데이터가 커졌다 작아졌다하는 데이터베이스에 적합하다.



인덱스 튜닝

  • 인덱스를 만들때 고려해야하는 사항
    1. 가장 중요한 쿼리를 고려한다.
    2. 현재 인덱스들을 사용하는 최고의 계획을 고려한다.
    3. 추가적인 인덱스 생성을 통해 만들 수 있는 계획을 찾아본다.


  • 인덱스 만들기 전에는 update에 가해지는 workload에 대해 고려해야한다.
    • 인덱스를 만들면 search는 빠르지만, update가 느려지며, disk 공간도 더 많이 차지한다.


  • 인덱스 Selection
    • 인덱스를 선택할때에는 INDEX가 생성된 KEY에 대한 WHERE 절에서
      1. KEY가 EXACT MATCH(=)로 선택된다면 hash index를 사용한다.
      2. KEY가 RANGE MATCH(>, <)로 선택된다면 tree index를 사용한다.
    • equality, range 쿼리에서 clustering이 유용한 이이유는 duplicate때문이다.
    • 가장 많은 쿼리에 도움을 줄 수 있는 인덱스를 선택해야한다.
      • relation마다 1개의 clustred index를 가질 수 있기 때문.


  • WHERE clause에서의 MultipleCondition
    • 범위 선택 연산이 포함되면, range ordering에 맞추기위해 속성의 순서가 중요
    • 인덱스들은 중요한 쿼리들에 index-only strategy를 제공할 수 있다.
    • index-only starategy에서 clustering은 중요하지 않다.


  • Join 조건
    • inner join에서 hash index는 매우 효율적이다.
    • inner tuple을 받기위해서는 반드시 clustered 되어야한다.



쿼리최적화 예시

쿼리 최적화 과정

SELECT LNAME
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE PNAME = 'AQUARIUS' 
AND PNUMBER = PNO 
AND ESSN = SSN 
AND BDATE > DEC-31-1957;
  • 위의 예시를 보면 3개의 relation을 join하는 것을 알 수 있다.
  • join clause로는 PNUMBER = PNO, ESSN = SSN이 있다.
  • 나머지 2개의 where clause 는 relation의 특정 데이터를 selection한다.


  • 쿼리 OPTIMIZER에게 전달되는 AST는 다음과 같다. 스크린샷 2022-12-10 19 24 11

  • 이 canonical tree는 3개의 relation을 CARTESIAN PRODUCT하므로 매우 비효율적이다.
  • 위 트리는 where절에 맞게 아래와 같은 표준 트리로 변환된다.


스크린샷 2022-12-10 20 18 23

  • 하지만 이 트리도 실행되지 않는다.
  • 쿼리 옵티마이저가 효율적 실행이 가능한 트리로 변환한다.


스크린샷 2022-12-10 20 20 32

  • Pname='Aquarius'라는 조건으로 1개의 tuple만이 선택될 것이므로, 쿼리 옵티마이저는 이 쿼리를 트리의 가장 왼쪽으로 이동한다.
  • 기본적으로 쿼리 옵티마이저는 트리의 왼쪽부분부터 확인해나가기때문에, 이 쿼리를 왼쪽으로 옮기면 cartesian product 시 생기는 튜플의 개수가 적어져 실행속도가 빨라지게된다.
  • 이제 쿼리 옵티마이저는 Cartesian product를 join과 select 연산을 사용해 치환하게된다.


스크린샷 2022-12-10 20 27 55

  • 여기서 더 최적화를 할 수 있는데, join 조건에서 사용되는 attribute만을 project하도록 하는 것이다.


스크린샷 2022-12-10 20 29 18

  • 필요한 attribute만을 읽어도록해 입출력 연산을 최소화할 수 있다.



최적화 속 인덱스 사용

1번예시

SELECT ename
FROM Department, Employee
WHERE dname = 'Toy' 
AND Employee.dno = Department.dno;
  • 위의 쿼리는 쿼리 옵티마이저가 아래와같이 바꾼다. 스크린샷 2022-12-10 20 47 19


  • 만약에 Department.dno에 인덱스가 생성되어있다고 생각해보자.
    • 이 인덱스는 연산하면서 사용될까?
  • 정답은 NO이다.
    • Department에서 유일한 tuple이 dname조건에 의해 선택되기때문이다.
  • 다만, Employee.dno는 사용된다.


2번예시

SELECT E.ename, D.mgr
FROM Employee E, Department D
WHERE D.dname = 'sales' 
AND E.dno = D.dno
AND E.age = 25;
  • 위 쿼리 역시 아래와 같이 계획된다.

  • D.dname과 E.age에 걸린 인덱스를 통해 빠른 연산이 가능하다.
  • 만약 E.age에 인덱스가 걸려있다면, E.dno 인덱스를 만들 필요는 없다.

댓글남기기

참고