[SQLD : Ⅱ. 데이터 모델과 성능] 5-2. 데이터베이스 구조와 성능 - PK/FK

* PK/FK 컬럼 순서와 성능 개요

 - 일반적으로 균형잡힌 트리구조의 B+Tree 구조를 많이 사용

 - PK/FK 컬럼 순서 중요성을 인지 못하고 모델링되어 그 상태로 DDL 생성 시 데이터 처리 성능 문제 유발 가능

 - 인덱스의 특징은 여러 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성 값이 비교자로 있어야 좋은 효율

 - FK에 대해서는 반드시 인덱스를 생성하도록 하고, 인덱스 컬럼 순서도 조회 조건 고려해서 가장 효율적으로 순서 생성

 

* PK 컬럼 순서 미조정 시 성능 저하 이유

 - 데이터 모델링에서 엔티티 설계 시 그에 따라 DDL이 생성되고 생성된 DDL에 따라 인덱스가 생성됨

   인덱스의 정렬구조를 알아야 함

 - 데이터모델의 PK 순서에 따라 DDL이 생성되고 

    주문번호, 주문일자, 주문목록코드가 정렬됨

 - 이런 정렬 구조로 데이터를 접근하는 트랜잭션의 조건에 따라 다른 인덱스 접근방식을 보여줌

 - 인덱스의 정렬된 첫 컬럼과 비교시 순차적으로 데이터를 찾아감

    맨 앞 컬럼이 제외된 상태에서 데이터를 조회할 경우 데이터를 비교하는 범위가 넓어지며 성능 저하 유발

 - 주문번호에 대한 비교값이 들어오지 않으므로 인덱스 전체를 읽어야 함

    인덱스를 읽고 테이블 블록에서 읽어 처리하는데 IO가 많이 발생하여 옵티마이저는 테이블에 가서 전체를 읽음

 - PK의 순서를 인덱스 특징에 맞게 고려하지 않고 그대로 생성하면 효율적이지 않은 인덱스가 생성되어

   인덱스의 범위를 넓게 이용하거나 full scan을 유발하게 되어 성능이 저하됨

 

 1) 간단한 오류

  - 입시마스터 테이블의 PK는 수험번호+년도+학기로 구성

    전형과목실적 테이블은 입시마스터 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성

    200만건의 데이터가 있고 학사는 4학기로 구성되며 데이터는 5년간 보관한다고 가정

  - 입시마스터 테이블에 있는 인덱스 입시마스터_I01 이용을 위한 SQL문

SELECT COUNT(수험번호) 
FROM 입시마스터 
WHERE 년도 = '2008' AND 학기 = '1'

  - 입시마스터 _I01 인덱스가 수험번호+년도+학기 중 수험번호에 대한 값이 WHERE 절에 들어오지 않음

     FULL TABLE SCAN이 발생, 200만 건 데이터를 모두 읽어서 성능저하

  - 입시마스터 테이블 데이터를 조회할 때 년도와 학기에 대한 내용이 빈번하게 들어와서 PK 순서를 변경

  - 생성된 인덱스가 정상적으로 이용이 되어 평균 2만건의 데이터를 처리하여 성능 개선

 

 2) 복잡한 오류

  - 현금출급기실적의 PK는 거래일자+사무소코드+출급기번호+명세표번호

SELECT 건수, 금액 
FROM 현금출급기실적 
WHERE 거래일자 BETWEEN '20040701' AND '20040702'
      AND 사무소코드 = '000368'

  - 거래일자+사모소코드 순서로 인덱스를 구성한 경우와 사무소코드+거래일자 순서로 ㅇ ㅣㄴ덱스를 구성한 경우

    데이터를 처리하는 범위의 차이를 보여줌

  - 거래일자+사무소코드 : BETWEEN 비교를 한 거래일자 200440701이 인덱스의 앞에 위치하여 범위가 넓어짐

    사무소코드+거래일자 : = 비교를 한 사무소코드 000368이 인덱스 앞에 위치해서 범위가 좁아짐

  - 이미 만들어진 PK 인덱스가 전혀 사용되지 않으면 불필요한 인덱스로 성능이 더 저하됨

  - 최적화된 인덱스 생성을 위해 PK 순서변경을 통한 인덱스 생성이 바람직

  - PK속성이 A,B가 있을 때 A+B형태, B+A형태로도 빈번하게 조회되는 경우 더 자주이용되는 형태로 구성하고

    순서를 바꾼 인덱스를 추가로 생성하는 것이 필요

 

* 물리적인 테이블에 FK 제약이 걸리지 않아 인덱스 미생성으로 인한 성능 저하

 - 물리적인 테이블에 FK를 사용하지 않아도

   데이터 모델 관계에 의해 상속받은 FK 속성은 SQL WHERE절에서 조인으로 이용되는 경우가 많음

 - 학사기준과 수강신청에 대한 데이터 모델, 

   물리적인 테이블에는 두 테이블 사이에 FK 참조무결성 관계가 걸려있지 않는다고 가정

   학사기준에는 5만 건, 수강신청에는 500만건 데이터가 있다고 가정

 - 수강신청 테이블에 있는 학사기준번호가 SQL WHERE절에 비교자로 들어오지 않았지만

   수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 않아서

   학사기준과 수강신청 테이블이 조인되면서 FULL TABLE SCAN 발생으로 성능 저하

 - 수강신청 테이블에 FK 인덱스를 생성하여 성능 개선 가능

 - 물리적으로 학사기준과 수강신청이 연결되어 있지 않아도 학사기준으로부터 상속받은 FK에 대해 FK 인덱스 생성

 - FK인덱스를 적절하게 설꼐하여 구축하지 않으면 개발초기에 데이터량이 적어서 성능저하가 잘 나타나지 않음

   시스템 오픈 후 데이터량이 누력될수록 SQL 성능이 나빠져 데이터베이스서버에 심각한 장애현상 초래

 - 물리적인 테이블에 FK 제약을 걸었을 때 반드시 FK 인덱스를 생성하도록 하고

   FK 제약이 걸리지 않은 경우 FK인덱스를 생성하는 것을 기본정책으로 함

 - 발생되는 트랜잭션에 의해 거의 활용되지 않을 때만 FK 인덱스를 지우는 방법이 적절