:: 작업장

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 2절 인덱스 기본

1.인덱스 특징과 종류

- 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념.

- 테이블을 기반으로 선택적으로 생성할 수 있는 구조.

- 인덱스의 기본적인 목적은 검색 기능의 최적화이다.

가. 트리기반 인덱스

- DBMS에서 가장 일반적인 인덱스는 B-Tree 인덱스이다.

* B Tree 인덱스

- B Tree 인덱스는 브랜치블록과 리프블록으로 구성된다. 브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록 이라고 한다. 브랜치 블록은 분기를 목적으로 하는 블록이다.

- 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다.

- 리프 블록은 인덱스를 구성하는 칼럼의 데이터와 데이터를 가지고있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성되어 있다.

- 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.(데이터의 값이 동일하면 레코드 식별자의 순서로 저장됨)

- 리프 블록은 양방향 링크를 가지고 있다.  이것을 통해 오름 차순과 내림 차순 검색을 쉽게 겁색할 수 있다.

- B Tree 인덱스는 =로 검색하는 일치 검색과 BETWEEN, >, 등과 같은 범위 검색에 모두 적합한 구조이다.

인덱스에서 원하는 값을 찾는 과정.

1단계: 브랜치 블록의 가장 왼쪽 값이 찾고자 하는 값보다 작거나 같으면 왼쪽포인터로 이동

2단계: 찾고자 하는 값이 브랜치 블록의 값 사이에 존재하면 가운데 포인터로 이동

3단계: 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동

이과정을 리프 블록을 찾을 때까지 반복한다. 리프 블록에서 찾고자 하는 값이 존재하면 해당 값을 찾을 것이고, 없으면 해당 값은 존재하지 않아 검색에 실패하게된다.

인덱스를 생성할 때 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없다. 

인덱스 구성칼럼은 동일하지만 순서가 서로 다르면 생성가능하다 ( JOB + SAL, SAL + JOB 가능)

오라클에서 트리 기반 인덱스에는 B-Tree 인덱스 이외에도 비트맵 인덱스, 리버스키 인덱스, 함수기반 인덱스 등이 존재한다.

나. SQL Server의 클러스터형 인덱스

- SQL Server의 인덱스 종류는 저장 구조에 따라 클러스터형 인덱스와 비클러스터형 인덱스로 나뉜다.

클러스터형 인덱스는 두 가지 중요한 특징이있다.

1. 인덱스의 리프 페이지가 곧 데이터 페이지다.

- 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다

(인덱스 키 칼럼과 나머지 칼럼을 리프페이지에 같이 저장하기 때문에 테이블을 랜덤 엑세스할 필요가 없다.)

- 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.

2. 리프 페이지의 모든 로우는 인덱스 키 칼럼순으로 물리적으로 정렬되어 저장된다.

- 테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다. 그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

- 리프블록에 인덱스 키 칼럼 외에도 테이블의 나머지 칼럼이 모두 함께 있다.

2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

- 전체 테이블 스캔 방식으로 데이터를 검색하는 것은 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식으로 검색한다.

- Oracle의 경우 검색조건에 맞는 데이터를 찾기 위해 테이블의 고수위마크(HWM) 아래의 모든 데이터를 읽는다.

- 고수위 마크는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치를 의미한다.

- 전체 테이블 스캔 방식으로 데이터를 검색할 때 고수위 마크까지의 블록 내 모든 데이터를 읽어야 하기 때문에 모든 결과를 찾을 때까지 시간이 오래 걸릴 수 있다.

옵티마이저가 Full table scan을 선택하는 이유는 일반적으로 다음과 같다.

1) SQL문에 조건이 존재하지 않는 경우

- SQL문에 조건이 존재하지 않는다는 것은 테이블에 존재하는 모든데이터가 답이 된다는 의미이므로 테이블의 모든 블록을 읽으면서 무조건 결과로서 반환한다.

2) SQL문의 주어진 조건에 사용가능한 인덱스가 존재하지 않는 경우.

- 사용가능한 인덱스가 존재하지 않는다면 데이터를 엑세스할 수 있는 방법은 모든 데이터를 읽으면서 주어진 조건을 만족하는지를 검색하는 방법 뿐이다.

- 또한 사용가능한 인덱스는 존재하나 함수를 사용하여 인덱스 칼럼을 변경한 경우에도 인덱스를 사용할 수 없다.

3) 옵티마이저의 취사 선택

조건을 만족하는 데이터가 많을 경우 결과 데이터를 추출하기위해 테이블의 대부분 블록을 엑세스 한다고 옵티마이저가 판단하면 조건에 맞는 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.

4) 그 밖의 경우

- 병렬 처리 방식으로 처리할 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우 전체 테이블 스캔 방식으로 데이터를 읽을 수 있다.

나. 인덱스 스캔

- 인덱스 스캔은 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 엑세스 기법.

- 인덱스는 인덱스 구성 칼럼의 순서로 정렬되어 있다. (A+B 구성의 인덱스일 경우 A 칼럼으로 정렬되고 A칼럼 값이 동일할 경우 B 칼럼으로 정렬된다. B 칼럼까지 동일한경우 RID로 정렬된다.)

- 인덱스 순서와 동일한 정렬 순서를 사용자가 원하는 경우 정렬작업을 수행하지 않을 수 있다.

1) Index Unique Scan (인덱스 유일 스캔)

유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식. 유일 인덱스는 중복을 허락하지 않는 인덱스로 유일인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 된다.

인덱스 유일 스캔은 유일 인덱스 구성칼럼에 대해 모두 '='로 값이 주어진 경우에만 가능한 인덱스 스캔방식이다.

2) Index Range Scan

- 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식

- 유일 인덱스의 구성 컬럼 모두에대해  '='로 값이 주어지지 않는 경우와 비유일 인덱스를 이용하여 모든 엑세스 방식은 인덱스 범위 스캔 방식으로 데이터를 엑세스 하는것이다.

3) 인덱스 역순 범위스캔은 리프블록의 양방향 링크를 통해 내림차순으로 데이터를 읽는 방식. 이 방식을 이용하여 Max Value를 쉽게 찾을 수 있다.

다. Full Table scan과 Index Scan 방식의 비용.

- 인덱스 스캔 방식은 사용가능한 적절한 인덱스가 있을 경우에만 이용할 수 있는 스캔방식 이지만 전체 테이블 스캔 방식은 인덱스의 존재 유무와 상관없이 항상 이용가능한 스캔방식.

- 인덱스 스캔은 인덱스에 존재하는 레코드 식별자를 이용해서 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽는다. 그러므로 불필요하게 다른 블록을 더 읽을 필요가 없다.

 따라서 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

- 전체 테이블 스캔은 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽는다.

* 데이터를 찾을 때 여러 대부분의 데이터를 읽을 거라면 한번에 여러 블록씩 읽는 전체 테이블 스캔 방식이 유리할 수 있다.

[SQLD] 1과목 데이터 모델링의 이해 - 2장 5절:데이터베이스 구조와 성능

1. 슈퍼타입/ 서브타입 모델의 성능고려 방법.

가. 슈퍼/서브타입 데이터 모델의 개요.

- Extended ER모델이라고 부르는 슈파타입/서브타입 데이터 모델은 최근에 데이터 모델링을 할 때 자주 쓰이는 모델링 방법이다.

- 업무를 구성하는 데이터의 특징을 공통점과 차이점의 특징을 고려하여 효과적으로 표현할 수 있기 때문.

- 공통의 부분을 슈퍼타입으로 모델링하고 공통으로부터 상속받아 다른 엔터티와 차이가 있는 속성에 대해서는 별도의 서브엔터티로 구분하여 업무의 모습을 정확하게 표현하면서 물리적인 데이터 모델로 변환을 할 때 선택의 폭을 넓힐 수 있는 장점이 있다.

- 슈퍼/서브타입의 데이터 모델은 논리적 데이터 모델에서 이용되는 형태이며 분석/설계단계를 구분하자면 분석단계에서 많이 쓰이는 모델이다.

- 물리적인 데이터모델을 설계하는 단계에서는 슈퍼/서브타입 데이터 모델을 일정한 기준에 의해 변환해야한다.

- 물리적인 데이터모델이 성능을 고려한 데이터 모델이 되어야한다는 점을 고려하면 이렇게 막연하게 슈퍼/서브타입을 아무런 기준없이 변환하는것 자체가 성능이 저하될 수 있음을 기억해야한다.

나. 슈퍼/서브타입 데이터 모델의 변환

- 슈퍼 서브타입에 대한 변환을 잘못하여 성능이 저하되는 경우.

1) 트랜잭션은 항상 일괄로 처리하는데 테이블은 개별로 유지되어 UNION 연산에 의해 성능이 저하될 수 있다.

2) 트랜잭션은 항상 서브타입 개별로 처리하는데 테이블은 하나로 통합되어 있어 불필요하게 많은 양의 데이터가 집약되어 있어 성능이 저하되는 경우가 있다.

3) 트랜잭션은 항상 슈퍼+서브타입을 공통으로 처리하는데 개별로 유지되어 있거나 하나의 테이블로 집약되어 있어 성능이 저하되는 경우가 있다.

* 해당 테이블에 발생되는 성능이 중요한 트랜잭션이 빈번하게 처리되는 기준에 따라 테이블을 설계해야 성능저하 현상을 예방할 수 있다.

* 슈퍼/서브타입을 성능을 고려한 물리적인 데이터 모델로 변환하는 기준은 데이터 양과 해당 테이블에 발생되는 트랜잭션의 유형에 따라 결정된다.

* 데이터의 양은 소량일 경우 데이터 처리의 유연성을 고려하여 가급적 1:1관계를 유지하는것이 바랍직하다.

* 데이터의 양이 많을 경우 그리고 해당 업무적인 특징이 성능에 민감한 경우에는 트랜잭션이 해당 테이블에 어떻게 발생되는지에 따라 3가지 변환방법을 참조하여 변환해야한다.

다. 슈퍼/서브 타입 데이터 모델의 변환 기술.

- 논리적인 데이터 모델에서 설계한 슈퍼타입/서브타입 모델을 물리적인 데이터 모델로 전환할 때 주로 어떤 유형의 트랜잭션이 발생하는지 검증해야한다.

- 데이터양이 많이 존재하고 지속적으로 증가하는 양도 많다면 슈퍼타입/서브타입에 대해 물리적인 데이터모델로 변환하는 세 가지 유형에 대해 세심하게 적용해야한다.

1) 개별로 발생하는 트랜잭션에 대해서는 개별 테이블로 구성.

- 슈퍼타입과 서브타입 각각에 대해 독립적으로 트랜잭션이 발생이 되면 슈퍼타입에도 꼭 필요한 속성만 가지게 하고 서브타입에도 꼭 필요한 속성만 가지게 하기 위해서 모두 분리하여 1:1 관계를 갖도록 한다.

- 실전프로젝트에서는 데이터양이 대용량으로 존재하는 경우에 공통으로 이용하는 슈퍼타입의 속성의 수가 너무 많아져 디스크 I/O가 많아지는 것을 방지하기 위해 각각을 1:1 관계로 가져가는 경우도 있다.

2) 슈퍼타입+ 서브타입에 대해 발생되는 트랜잭션에 대해서는 슈퍼타입+서브타입 테이블로 구성

-  만약 대리인이 10만건 매수인 500만건 이해관계인 500만건의 데이터가 존재할 때, 슈퍼타입과 서브타입이 모두 하나의 테이블로 구성되어있을 경우 매수인 이해관계인에 대한 정보는 배제하고 10만건인 대리엔이데핸 데이터만 처리할 경우 다른테이블과 같이 1010만건 데이터가 저장되어있는 곳에서 처리하므로 불필요한 성능저하가 발생하게된다.

=>  이처럼 슈퍼타입과 서브타입을 묶어 트랜잭션이 발생하는 업무특징을 가지고 있을 때에는 슈퍼타입+서브타입을 각각 하나로 묶어 별도의 테이블로 구성하는것이 효율적이다.

3) 전체를 하나로 묶어 트랜잭션이 발생할 때는 하나의 테이블로 구성.

- 대리인이 10만건, 매수인이 500만건, 이해관계인 500만건의 데이터가 존재한다고 하더라도 데이터를 처리할 때 대리인, 매수인, 이해관계인을 항상 통합하여 처리한다고 하면 테이블을 개별로 분리하면  불필요한 조인을 유발하거나 불필요한 UNION ALL과 같은 SQL 구문이 작성되어 성능이 저하된다. 비록 슈퍼타입과 서브타입의 테이블을 하나로 묶었을 때 각각의 속성별로 제약사항을 정확하기 지정하지 못할 지라도 대용량이고 성능향상에 필요하다면 하나의 테이블로 묶어서 만들어준다.

라. 슈퍼/서브타입 데이터 모델의 변환타입 비교.

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

가. PK/FK 칼럼순서와 성능개요.

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

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

- 테이블에 발생되는 트랜잭션의 조회패턴에 따라 PK/FK 칼럼의 순서를 조정해야한다.

- PK는 해당 테이블의 데이터에 접근할 때 가장 빈번하게 사용되는 유일한 인덱스를 모두 자동생성한다.

- PK 순서를 결정하는 기준은 인덱스 정렬구조를 이해한 상태에서 인덱스를 효율적으로 이용할 수 있도록 PK순서를 지정해야한다.

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

* 앞쪽에 위치한 속성값이 가급적 '=' 아니면 최소한 범위 BEETWEEN, < > 가 들어와야 인덱스를 이용할 수 있다.

* 데이터 모델링 때 결정한 PK 순서와는 다르게 DDL 문장을 통해 PK 순서를 다르게 생성할 수 있다.

(하지만 보통 데이터 모델의 PK 순서에 따라 그대로 PK를 생성한다.)

* FK라고 하더라도 데이터를 조회할 때 조인의 경로를 제공하는 역할을 수행하므로 FK에 대해서는 반드시 인덱스를 생성하도록 하며 인덱스 칼럼의 순서도 조회의 조건을 고려하여 접근이 가장 효율적인 칼럼 순서대로 인덱스를 생성하도록 주의한다.

나. PK칼럼의 순서를 조정하지 않으면 성능이 저하되는 이유.

- 먼저 데이터 모델링에서 엔터티를 설계하면 그에 때라 DDL이 생성이 되고 생성된 DDL에 따라 인덱스가 생성된다.

- PK순서에 따라 DDL이 그대로 생성되고, 테이블의 주문번호가 가장먼저 정렬되고, 주문일자가 정렬되고 주문번호코드가 정렬이된다.

 1) 맨앞에 있는 인덱스 칼럼에 조회조건이 들어올 경우

SELECT 주문단가

FROM 주문목록

WHERE 주문번호 = '1002';

=> 인덱스에 정렬된 첫 번째 칼럼에 비교가 되었기 때문에 순차적으로 데이터를 찾아가게 된다.

2) 맨 앞에 있는 칼럼이 제외된 상태에서 데이터를 조회할 경우 데이터 비교버무이가 넓어지게되어 성능저하를 유발한다.

SELECT 주문단가

FROM 주문목록

WHERE 주문일자='2015.08.23';

=> 주문번호에 대한 비교값이 들어오지 않으므로 인덱스 전체를 다 읽은 후 원하는 데이터를 찾게된다. 이러한 이유로 인덱스를 읽고 데이터 블록에서 읽어 처리하는데 IO가 많이 발생하므로 옵티마이저는 차라리 테이블 전체를 읽는 방식으로 처리한다.

* PK의 순서를 인덱스 특징에 맞게 고려하지 않게 생성하게되면 테이블에 접근하는 트랜잭션의 특징에 효율적이지 않은 인덱스가 생성되어 있으므로 인덱스의 범위를 넓게 이용하거나 FULL SCAN을 유발하게 되어 성능이 저하된다고 할 수 있다.

다. PK순서를 잘못 지정하여 성능이 저하된 경우. - 간단한 오류

- 입시마스터라는 테이블의 PK는 수험번호+년도+학기로 구성되어 있고 전형과목실적 테이블은 입시마스터의 테이블에서 상속받은 수험번호+년도+학기에 전형과목코드로 PK가 구성되어있는 복합식별자 구조의 테이블이다.

- 입시마스터에는 200만 건의 데이터가 들어가있고 학사는 4학기로 구성되어 있고 데이터는 5년간 보관되어 한학기당 평균 2만 건의 데이터가 있을 때.

- 성능저하쿼리

=> 이 경우 입시마스터_I01인덱스가 수험번호+년도+학기 중 수험번호에 해당하는 값이 WHERE절에 들어오지 않으므로 FULL TABLE SCAN이 발생하게된다.

 * 입시마스터 테이블 조회시 년도와 학기가 빈번하게 들어오므로 PK순서를 변경함으로써 인덱스를 사용할 수 있도록 할 수 있다.

- PK 순서 변경 

라. PK순서를 잘못 지정하여 성능이 저하된 경우 - 복잡한 오류.

- 현금출급기의 PK는 거래일자+사무소코드+출급기번호+명세표번호로 되어있는데, 대부분 SQL 문장에서 조회를 할 때 사무소코드가 '='로 들어오고 거래일자에 대해서는 BETWEEN 조회를 하고있다.

이 때 SQL은 정상적으로 인덱스를 사용할 수 있지만 인덱스 효율이 떨어져 성능이 저하되는 경우에 해당한다.

=> 거래일자 + 사무소코드로 구성된 인덱스 이기 때문에 거래일자 범위때문에 조회범위가 넓어져서 성능저하가 발생한다.

 인덱스 구성을 사무소코드 + 거래일자로 구성하면 '='비교를 사용하므로써 범위가 좁혀질 수 있다.

* A+B 형식의 조회와 B+A 형식의 조회도 빈번하게 일어날 경우에는 좀 더 자주 이용되는 조회형태로 PK를 구성하고 순서를 바꾼 인덱스를 추가로 생성하는것이 좋다.

3. 물리적인 테이블에 FK제약이 걸려있지 않을 경우 인덱스 미생성으로 성능저하.

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

- 학사기준과 수강신청에 대한 데이터모델에서 물리적인 테이블에는 두 테이블 사이에 FK 참조무결성 관계가 걸려있지 않을 때 학사기준 데이터에는 5만건의 데이터가있고 수강신청에 데이터가 500만건이 있다고 가정하면.

=> FK 인덱스 미생성으로 FULL TABLE스캔이 발생하여 성능 저하가 생긴다.

- 학사기준번호가 SQL절에 비교자로 들어오지는 않지만 수강신청 테이블에서 상속받은 학사기준번호에 대해 인덱스를 생성하지 않으므로 인해 학사기준과 수강신청 테이블이 조인이 되면서 500만건의 수강신청 테이블이 FULL SCAN이 발생하며 성능이 저하되었다.

- 이때는 수강신청 테이블에 FK인덱스를 생성하여 성능을 개선할 수 있다.

CREATE INDEX 수강신청_FK01 ON 수강신청(학사기준번호)

* 물리적으로 학사기준과 수강신청이 연결되어 있지 않다고 하더라도 학사기준으로부터 상속받은 FK에 대해 FK인덱스를 생성함으로써 SQL문장이 조인이 발생할 때 성능저하를 예방할 수 있다.

* 물리적인 테이블에 FK제약을 걸었을 때는 반드시 FK 인덱스를 생성하도록 하고 FK 제약이 걸리지 않았을 경우에는 FK인덱스를 생성하는것을 기본 정책으로 하되 발생되는 트랜잭션에 의해 거의 활용되지 않았을 때에만 FK 인덱스를 지우는 방법으로 하는것이 적절한 방법이다.