<아는 만큼 보이는 데이터베이스 설계와 구축>, UPDATE.20200210

저자 : 이춘식

저서 : <데이터베이스 설계와 구축> 외

펴낸 곳: 한빛미디어(주)

초판 : 2008년 7월 5일

7쇄 : 2016년 2월 1일

리뷰

뜻이 있는 곳에 길이 있다는 말이 맞는 건지는 몰라도
전공서적임에도 매우 재미있게 읽었다.

어느 정도 가독을 쉽게 구성해 논 이유도 있겠지만
내가 해결해야 할, 또는 마주한 문제들을 어떻게 처리할 수 있을지에 대한
정답을 찾아보려고 읽다보니 더 잘 읽히지 않았나 싶다.
물론, 책의 두께도 한몫했다. 휴대성 갑!

다양한 사례를 바탕으로 데이터베이스에 대한 기본기를 
나름 자세하게 설명해준다.
특히 데이터베이스를 조금이라도 걸친 일을 하고 있다면
한번 쯤 가볍게 슥~읽어보기를 바란다.
인사이트를 얻을 수 있을 것이다.

 

Story 01. PK 컬럼 순서, 대충 하지 말자

데이터베이스 생성 : 분석 > 설계 > 구축 > 테스트 > 이행

보통 상용화된 데이터 모델링 툴 사용 -> DDL(Data Definition Language) 생성

 

-> PK 컬럼의 순서를 고려하지 않고 생성

 

그럴 시 문제점

1) 인덱스 구성에 의도하지 않은 순서의 Primary key Unique Index가 생성된다.

2) 그에 따라 조회 SQL 실행 시 성능 저하 현상이 나타날 수 있다.

3) 많은 인덱스가 생성되므로 입력/수정/삭제 시 불필요한 내부 작업이 증가해 성능에 악영향을 미친다

 

해결

테이블 생성 전에 SQL Where 절을 분석하여 엔티티타입의 PK 컬럼 순서를 조정하는 작업을 해야 한다.

 

성능 저하 이유

PK 구성과 인덱스 이용

데이터 모델의 PK 순서를 조정하지 않은 채 테이블을 생성하면 인덱스를 이용하지 못해 Full Scan 현상이 발생하는 경우 가 있다. 

 

예)

1) PK가 수험번호+년도+학기 -> 자동 DDL생성 시 인덱스는 PK순서대로 생성됨

2) where 조건으로 년도와 학기만 입력 시 FULL TABLE SCAN 발생 -> 조건에 수험번호가 없기 때문

3) 수정 : Where 절에 '='이나 'between', '<', '>' 등을 분석하여 PK 컬럼을 범위가 들어오는 조건부터 앞쪽으로 위치시킴

 

# 진짜? 

 

인덱스의 비효율적 이용

인덱스를 정상적으로 이용하였으나 넓은 범위 조회로 인해 SQL 실행 성능이 심각하게 저하된다.

 

예)

PK는 거래일자 + 사무소코드 + 출급기번호 + 명세표번호

SQL 문장 조회 시 거래일자 'BETWEEN'으로 조회, 사무소코드 '=' 조회

-> 거래일자에 해당하는 넓은 범위 조회 후 일치하는 사무소 코드 조회 : 성능 저하

-> 일치하는 사무소코드 조회 후 거래일자 조회 : 성능 향상

 

# 테스트) 해보자

 

PK 컬럼 순서를 효율적으로 만드려면

- 설계 단계를 마치기 전 데이터 모델링을 수행할 때 PK 컬럼 순서를 반드시 검토하여 조정해야 한다.

 

- PK 순서로 인해 SQL 문장 성능이 저하되는 경우

 1) 인덱스를 이용하지 못하고 FULL TABLE SCAN으로 성능 저하

   -> SQL 실행 계획 확인으로 튜닝

 2) 인덱스는 이용하나 범위가 넓어져 성능 저하

   -> 튜닝의 필요성을 깨닫지 못함

 

- 인덱스의 정렬(Sort)구조를 이해한 상태에서 트랜잭션의 특성에 따른 PK 구성을 하여 인덱스 범위를 최소화하는 방향으로 데이터 모델에 반영해야 한다. 

 

- 설계상의 오류를 올바른 방법으로 처리하지 않고 SQL 구문을 수정하거나 인덱스를 생성하는 방법으로 정정하려고 하는 경우가 있다.

 

- 여러 개의 속성이 하나의 인덱스로 구성되어 있을 때 앞쪽에 위치한 속성의 값이 비교자로 있어야 인덱스가 좋은 효율을 나타낼 수 있다. 

  ex) 앞쪽에 위치한 속성의 값이 가급적 '=', 'BETWEEN, '<>'가 들어와야 인덱스를 이용할 수 있는 것이다.

 

- 어떤 값이 들어오는지 예상을 해야 하기에, 데이터베이스에서 일어나는 트랜잭션의 성격을 이해하지 못하면 원할한 PK 순서를 지정할 수 없게 된다.

  -> 데이터 모델링에 참여한 사람이 정확한 프로세스의 특징을 이해하지 못한다면 PK순서를 정확하게 지정할 수 없다는 의미

 

Story 22. 인덱스 특성을 고려한 PK/FK 데이터베이스 성능 향상

- 인덱스 : 데이터 조회가 가장 효과적으로 처리될 수 있도록 접근 경로를 제공하는 오브젝트

 

- 데이터베이스 테이블에서는 일반적으로 균형 잡힌 트리구조의 B*Tree 구조를 많이 사용

 

- FK는 데이터를 조회할 때 조인의 경로를 제공하는 역할을 수행하므로 FK에 대해서는 반드시 인덱스를 생성하도록 한다. 

 

PK 컬럼 순서와 성능 저하의 관계

- SQL 구문의 조건에 따라 인덱스를 처리하는 범위가 달라진다.

 

- 인덱스를 읽고 테이블 블록에서 읽어 처리하는데 I/O가 많이 발생하게 되므로, 옵티마이저는 차라리 테이블에 가서 전체를 읽는 방식으로 처리

 

물리적인 테이블과  FK 인덱스

- 물리적인 테이블에 FK를 사용하지 않아도 데이터 모델 관계에 의해 상속받은 FK속성들은 SQL WHERE 절에서 조인으로 이용되는 경우가 많이 있다. 따라서 FK 인덱스를 생성해야 성능이 좋은 경우가 많다. 

 

- 개발 초기에는 데이터양이 적어 FK인덱스 없이도 성능 저하가 나타나지 않는다

 

- FK 인덱스 생성을 기본 정책으로 하되, 발생하는 트랜잭션에 의해 거의 활용되지 않았을 때에만 FK인덱스 삭제가 적절하다