[2] 인덱스 특성을 고려한 PK/FK 데이터베이스 성능향상
1. PK/FK 칼럼 순서와 성능 개요
* 테이블에 발생되는 트랜잭션의 조회 패턴에 따라 PK/FK 칼럼의 순서를 조정해야 한다.
- 용어 정리
인덱스 : 데이터를 조회할 때 가장 효과적으로 처리될 수 있도록 접근 경로를 제공하는 오브젝트
DDL : 데이터베이스를 정의하는 언어로 데이터를 생성하거나 삭제, 수정 등의 역할을 하는 것을 나타낸다.
ex. CREATE, DROP, ALTER
- 성능을 고려한 데이터베이스 설계가 이루어지도록 설계 단계 말에 PK/FK 칼럼의 순서를 조정할 필요가 있다.
- PK는 해당 테이블의 데이터를 접근할 때 가장 빈번하게 사용되는 유일한 인덱스(Unique Index)를 모두 자동 생성한다.
2. PK칼럼의 순서를 조정하지 않으면 성능이 저하 이유
- 데이터 모델링에서 엔터티를 설계하면 그에 따라 DDL이 생성이 되고 생성된 DDL에 따라 인덱스가 생성된다.
- SQL구문의 조건(트랜잭션의 조건)에 따라 인덱스를 처리하는 범위(인덱스 접근방식)가 달라진다.
- PK의 순서를 인덱스 특징에 맞게 고려하지 않고 곧바로 생성하면, 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되므로
인덱스의 범위를 넓게 이용하거나 Full Scan을 유발하여 성능이 저하된다.
3. PK순서를 잘못 지정하여 성능이 저하된 경우 - 간단한 오류
4. PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류
최적화된 인덱스 생성을 위해 PK순서 변경을 통한 인덱스 생성이 성능 개선에 있어서 더 바람직하다. PK 인덱스를 생성하고서 사용하지 않는다면, 불필요한 인덱스로 인해 오히려 성능이 저하된다.
[3] 물리적인 테이블에 FK제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하
물리적인 테이블에 FK를 사용하지 않아도 데이터 모델 관계에 의해 상속받은 FK속성들은 SQL WHERE 절에서 조인으로 이용되는 경우가 많이 있으므로 FK 인덱스를 생성해야 성능이 좋은 경우가 빈번하다.
=> 물리적인 테이블에 FK 제약을 걸었을 때는 반드시 FK 인덱스를 생성하고, FK 제약이 없을 경우에는 FK 인덱스를 생성하지만 발생되는 트랜잭션에 의해 활용되지 않은 FK 인덱스를 지우는 방법으로 하는 것이 적절하다.
제6절 분산 데이터베이스와 성능
[1] 분산 데이터베이스의 개요
- 여러 곳으로 분산되어 있는 데이터베이스를 하나의 가상 시스템으로 사용할 수 있도록 하는 데이터베이스이다.
- 논리적으로 동일한 시스템에 속하지만, 컴퓨터 네트워크를 통해 물리적으로 분산되어 있는 데이터들의 모임으로 사용자 통합하고 공유할 수 있다.
분산 데이터베이스는 빠른 네트워크 환경을 연결하여 데이터베이스를 여러 지역과 노드로 위치시켜 사용성과 성능을 극대화한 데이터베이스이다.
[2] 분산 데이터베이스의 투명성(Transparency)
1. 분할 투명성(단편화) : 하나의 논리적 Relation이 여러 단편으로 분할되어 각 단편의 사본이 여러 site에 저장
2. 위치 투명성 : 사용하려는 데이터의 저장 장소 명시 불필요. 위치정보가 System Catalog에 유지되어야 함
3. 지역사상 투명성 : 지역DBMS와 물리적 DB사이의 Mapping 보장. 각 지역시스템 이름과 무관한 이름 사용 가능
4. 중복 투명성 : DB 객체가 여러 site에 중복 되어 있는지 알 필요가 없는 성질
5. 장애 투명성 : 구성요소(DBMS, Computer)의 장애에 무관한 Transaction의 원자성 유지
6. 병행 투명성 : 다수 Transaction 동시 수행시 결과의 일관성 유지, Time Stamp, 분산 2단계 Locking을 이용 구현
[3] 분산 데이터베이스의 적용 방법 및 장단점
1. 분산 데이터베이스 적용방법
분산 환경의 데이터베이스를 우수한 성능으로 현장에서 가치 있게 사용하는 방법은
업무의 흐름을 보고 업무구성에 따른 구조설계(아키텍처) 특징에 따라 선택적으로 데이터베이스를 설계하는 것이다.
2. 분산 데이터베이스 장단점
[4] 분산 데이터베이스의 활용 방향성
분산 데이터베이스는 업무적인 기능이 다양해지고 데이터의 양이 기하급수적으로 증가하는 최근 데이터베이스 환경에서 적용하는 고급화된 기술이다. 업무적인 특징에 따라 분산 데이터베이스를 활용하는 기술이 필요하다.
[5] 데이터베이스 분산구성의 가치
데이터를 분산 환경으로 구성하였을 때 가장 핵심적인 가치는 바로 통합된 데이터베이스에서 제공할 수 없는 빠른 성능을 제공한다는 점이다.
원거리 또는 다른 서버에 접속하여 처리하면서 발생되는 네트워크 부하 및 트랜잭션 집중에 따른 성능 저하를 분산된 데이터베이스 환경을 구축해 해결하고 빠른 성능을 제공하는 것이 가능하다.
[6] 분산 데이터베이스의 적용 기법
1. 테이블 위치 분산
- 테이블의 구조가 변하지 않고 테이블이 다른 데이터베이스에 중복하여 생성되지도 않는다. 다만 설계된 테이블의 위치를 각각 다르게 위치시킨다.
- 테이블 위치 분산은 정보를 이용하는 형태가 각 위치 별로 차이가 있을 경우에 이용한다. 이때 테이블의 위치를 파악할 수 있는 도식화된 위치 별 데이터베이스 문서가 필요하다.
2. 테이블 분할(Fragmentation) 분산
테이블 분할 분산은 단순히 위치만 다른 곳에 두는 것이 아니라, 각각의 테이블을 쪼개어 분산하는 방법이다.
1) 수평 분할(Horizontal Fragmentation) : 지사(Node)에 따라 테이블을 특정 칼럼의 값을 기준으로 로우(Row)를 분리한다.
데이터가 지사 별로 존재하여서 (Primary Key가) 중복이 발생되지 않으며, 데이터의 무결성이 보장되는 형태이다.
데이터를 수정할 때는 타 지사에 있는 데이터를 원칙적으로 수정하지 않고 자신의 데이터에 대해서 수정하도록 한다.
각 지사에 존재하는 테이블에 대해서 통합처리를 해야 하는 경우는 조인(JOIN)이 발생하여 성능 저하가 예상되므로 사전에 검토 후 진행해야 한다.
2) 수직 분할(Vertical Fragmentation) : 지사(Node)에 따라 테이블 칼럼을 기준으로 칼럼(Column)을 분리한다.
칼럼을 기준으로 분할하였기 때문에 각각의 테이블에는 동일한 Primary Key 구조와 데이터를 가지고 있어야 한다.
이때 동일한 Primary Key를 이용해 하나의 데이터로 표현하면 되므로 데이터 중복은 발생되지 않는다.
각 지사에 존재하는 테이블에 대해 통합처리를 하는 경우는 조인(JOIN)이 발생하여 성능 저하가 예상되므로 이용하지 않는다.
3. 테이블 복제(Replication) 분산
테이블 복제 분산은 동일한 테이블을 다른 지역이나 서버에서 동시에 생성하여 관리하는 유형이다.
1) 부분복제(Segment Replication) : 마스터 데이터베이스에서 테이블의 일부의 내용만 다른 지역이나 서버에 위치시키는 것
- 통합된 테이블을 한군데(본사)에 가지고 있으면서 각 지사별로는 지사에 해당된 로우(Row)를 가지고 있는 형태이다.
지사에 존재하는 데이터는 반드시 본사에 존재하게 된다. 즉 본사의 데이터는 지사 데이터의 합이 되는 것이다. 각 지사에서 데이터 처리가 용이할 뿐만 아니라 전체 데이터에 대한 통합처리도 본사에 있는 통합 테이블을 이용하게 되므로 여러 테이블에 조인(JOIN)이 발생하지 않는 빠른 작업 수행이 가능해진다. 지사 간의 데이터 중복은 발생하지 않으나, 본사와 지사 간에는 데이터 중복이 발생한다.
다른 지역간의 데이터를 복제(Replication)하는데 많은 시간이 소요되고 데이터베이스와 서버에 부하(Load)가 발생하므로 보통 실시간 처리에 의해 복사하는 것보다는 야간에 배치 작업에 의해 수행되는 경우가 많이 있다.
또한 본사와 지사 양쪽 모두 데이터를 수정하여 전송하는 경우 데이터의 정합성을 일치시키는 것이 어렵기 때문에 가능하면 한쪽(지사)에서 데이터의 수정이 발생하여 본사로 복제를 하도록 한다.
2) 광역복제(Broadcast Replication) : 데이터베이스의 테이블의 내용을 각 지역이나 서버에 존재시키는 것
- 통합된 테이블을 한군데(본사)에 가지고 있으면서 각 지사에도 본사와 동일한 데이터를 모두 가지고 있는 형태이다.
지사에 존재하는 데이터는 반드시 본사에 존재하게 된다. 본사에 있는 데이터 양과 모든 지사에 있는 데이터 양은 동일하다. 본사와 지사 모두 동일한 정보를 가지고 있으므로, 둘 다 데이터 처리에 특별한 제약을 받지는 않는다.
부분복제의 경우는 지사에서 데이터에 대한 입력, 수정, 삭제가 발생하여 본사에서 이용하는 방식이 많은 반면 광역복제의 경우에는 본사에서 데이터가 입력, 수정, 삭제가 되어 지사에서 이용하는 형태가 차이점이다.
부분복제와 같은 이유로 마찬가지 야간에 복제 작업을 진행한다.
4. 테이블 요약(Summarization) 분산
1) 분석요약(Rollup Summarization) : 동일한 테이블 구조를 가지고, 분산되어 있는 동일한 데이터를 이용하여 통합된 데이터를 산출하는 방식
- 각 지사별로 존재하는 동일한 내용(구조)의 요약정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법이다.
보통 통계데이터를 산정할 경우 모든 지사의 데이터를 이용하여 처리하면, 성능이 지연되고 서버에 부하를 주기 때문에 업무에 장애가 발생한다.
이것은 통합 통계데이터에 대한 정보제공에 용이한 분산방법이다. 본사에 분석 요약된 테이블을 생성하고 데이터는 일반 업무가 종료되는 야간에 수행하여 생성한다.
2) 통합요약(Consolidation Summarization) : 분산되어 있는 다른 데이터를 이용하여 통합된 데이터를 산출하는 방식
- 각 지사별로 존재하는 다른 내용의 요약정보를 본사에 통합하여 다시 전체에 대해서 요약정보를 산출하는 분산방법이다.
본사에 통계데이터를 산정하는 유형은 분석요약과 비슷하나, 통합요약은 단지 지사에서 산출한 요약정보를 한군데 취합하여 보여주는 형태이다.
분석요약은 지사에 있는 데이터를 이용하여 본사에서 통합하여 요약 데이터를 산정하였지만, 통합요약에서는 지사에서 요약한 정보를 본사에서 취합하여 각 지사별로 데이터를 비교하기 위해 이용되는 것이다.
분석 요약과 마찬가지로 통합 통계데이터에 대한 정보제공에 용이한 분산방법이다. 본사에 통합 요약된 테이블을 생성하고 데이터는 역시 야간에 수행하여 생성한다.
[7] 분산 데이터베이스를 적용하면 효과적인 경우
- 성능이 중요한 사이트
- 공통코드, 기준정보, 마스터 데이터 등
- 실시간 동기화가 요구되지 않을 때 (※ 거의 실시간(Near Real Time)의 업무적인 특징을 가지고 있을 때도 분산 환경을 구성할 수 있다.)
- 특정 서버에 부하가 집중이 될 때
- 백업 사이트(Disaster Recovery Site)를 구성할 때
[데이터 전문가 포럼] SQL 개발자 스터디 교재(2020.08.22.)
#SQLD