10장 인덱스 사용

34. 인덱스와 B-tree

- RDB에서 사용하는 인덱스는 구조에 따라 다음 세 가지로 분류할 수 있다.

    ㆍB-tree 인덱스

    ㆍ비트맵 인덱스

    ㆍ해시 인덱스

 

1. 만능형 : B-tree

- B-tree 인덱스는 이름 그대로 데이터를 트리 구조로 저장하는 형태의 인덱스이다.

- 균형잡힌 뛰어난 범용성을 인정받아 가장 많이 사용된다.

- 데이터베이스에서 '인덱스'라고 말하면 대부분 B-tree 인덱스를 지칭하는 것이다.

- 실제로 특별한 수식을 붙이지 않은 채 CREATE INDEX 구문을 실행하면, 모든 DBMS에서 암묵적으로 B-tree 인덱스가 만들어진다.

 

- B-tree가 검색 알고리즘으로서는 뛰어나게 성능이 좋은 편은 아니다.

 

- B+tree의 검색 성능이 뛰어난 이유

    ㆍB+tree는 루트와 리프의 거리를 가능한 일정하게 유지할려 한다.

    ㆍ따라서 균형이 잘 잡혀 검색 성능이 안정적이다.

 

2. 기타 인덱스

- 비트맵 인덱스는 데이터를 비트 플래그로 변환해서 저장하는 형태의 인덱스로, 카디널리티가 낮은 필드에 대해 효과를 발휘한다.

    ㆍ하지만 갱신할 때 오버헤드가 너무 크기 때문에 빈번한 갱신이 일어나지 않는 BI/DWH 용도로 사용된다.

- 해시 인덱스는 키를 해시 분산해서 등가 검색을 고속으로 실행하고자 만들어진 인덱스이다.

    ㆍ하지만 등가 검색 외에는 효과가 거의 없고 범위 검색을 할수 없는 점 때문에 거의 사용되지 않는다.

 

---

35. 인덱스를 잘 활용할려면

 

1. 카디널리티와 선택률

- 인덱스는 테이블의 특정 필드집합에 대해 만든다.

- 이때 어떤 필드에 대해 인덱스를 작성할 것인지 기준이 되는 요소가 필드의 카디널리티와 선택률이다.

 

- 카디널리티란 값의 균형을 나타내는 개념이다.

- 카디널리티가 가장 높은 필드는 모든 레코드에 다른 값이 들어가 있는 유일 키 필드이다

- 반대로 모든 레코드에 같은 값이 들어가 있다면 카디널리티가 낮은 필드이다.

 

- 선택률은 특정 필드값을 지정했을 때 테이블 전체에서 몇 개의 레코드가 선택되는지를 나타내는 개념이다.

- 예를 들어 100개의 레코드를 가진 테이블에서 유일키로 'pkey = 1' 처럼 등호를 지정한다면 한 개의 레코드가 선택될 것이다.

    ㆍ따라서 선택률은 1%이다.

 

- 인덱스의 성능을 결정하는 요인으로 클러스터링 팩터라는 개념도 있다.

    ㆍ이는 저장소에 같은 값이 어느 정도 물리적으로 뭉쳐 존재하는지를 나타내는 지표로, 높을수록 분산되어 있고 낮을수록 뭉쳐있다는 뜻

    ㆍ인덱스로 접근할 때는 특정 값에만 접근하는 경우가 많으므로 보통 클러스터링 팩터가 낮을수록 접근할 데이터양이 적어져 좋다.

 

2. 인덱스를 사용하는 것이 좋은지 판단하려면

- 인덱스를 작성하는 필드 집합의 조건은 다음 두 가지 지표로 판단한다.

    ㆍ첫 번째는 카디널리티가 높은 것, 즉 값이 평균치에서 많이 흩어져있을수록 좋은 인덱스 후보이다.

    ㆍ두 번째는 선택률이 낮은 것, 즉 한 번의 선택으로 레코드가 조금만 선택되는 것이 좋은 후보라는 뜻이다.

- 선택률이 5% 미만이라면 해당 필드 집합은 인덳를 작성할 가치가 있고 10% 초과면 테이블 풀 스캔을 하는 편이 더 빠를 가능성이 커진다.

 

---

36. 인덱스로 성능 향상이 어려운 이유

- SQL의 검색 조건과 결합 조건을 바탕으로 데이터를 효율적으로 압축할 수 있는 조건을 찾아야 한다.

 

1. 압축 조건이 존재하지 않음

# 경우1 : 압축조건이 존재하지 않음
SELECT order_id, receive_date
    FROM Orders;

- 레코드를 압축하는 WHERE 구가 애시당초 없으므로 인덱스로 작성할만한 필드도 존재하지 않는다.

 

2. 레코드를 제대로 압축하지 못하는 경우

- 인덱스가 있더라도 선택률이 높은 수치로 나온다면 풀 스캔을 할 때보다 느려질 가능성이 크다.

- 인덱스가 제대로 작동하려면 어디까지나 '레코드를 크게 압축할 수 있는 검색 조건'이 있어야 한다.

 

입력 매개변수에 따라 선택률이 변동하는 경우

SELECT order_id
    FROM Orders
    WHERE receive_date BETWEEN :start_date AND :end_date;

SELECT COUNT(*)
    FROM Orders
    WHERE shop_id = :sid;

 

3. 인덱스를 사용하지 않는 검색 조건

- 압축할 검색 조건이 있으면서도 인덱스를 사용할 수 없는 타입

 

중간 일치, 후방 일치의 LIKE 연산자

SELECT order_id
    FROM Orders
    WHERE shop_name LIKE '%대공원%';

 

색인 필드로 연산하는 경우

SELECT *
    FROM SomeTable
    WHERE col_1 * 1.1 > 100;

- 하지만 검색 조건의 우변에 식을 사용할 때는 인덱스가 사용된다.

SELECT *
    FROM SomeTable
    WHERE col_1 > 100/1.1

 

IS NULL을 사용하는 경우

- NULL과 관련한 검색 조건에서 인덱스가 사용되지 않는 것은 일반적으로 색인 필드의 데이터에 NULL이 존재하지 않기 때문이다.

SELECT *
    FROM SomeTable
    WHERE col_1 IS NULL;

 

- 또 색인 필드에 함수를 사용하는 경우에도 인덱스가 사용되지 않는다.

SELECT *
    FROM SomeTable
    WHERE LENGTH(col_1) = 10;

- 색인 필드에 함수를 사용하면 인덱스가 적용되지 않는 이유는 '색인 필드에 연산을 하는 경우'와 같다.

- 인덱스 내부에 존재하는 값은 어디까지나 col_1이지 LENGTH(col_1)이 아니기 때문이다.

- 함수 색인하는 방법도 있지만, 쓸데없는 연산 비용이 발생하므로 기본적으로 사용하지 않는게 좋다

 

부정형을 사용하는 경우

- 부정형(<>, !=, NOT IN)은 인덱스를 사용할 수 없다.

SELECT *
    FROM SomeTable
    WHERE col_1 <> 100;

 

---

37. 인덱스를 사용할 수 없는 경우 대처법

- 일반적인 인덱스를 사용하지 못하거나, 인덱스를 사용하면 오히려 느려지는 SQL 구문의 성능은 어떻게 튜닝해야 할까?

    ㆍ1. 애플리케이션에서의 설정으로 처리하는 방법이다.

    ㆍ2. 인덱스 온리 스캔

 

1. 외부 설정으로 처리 - 깊고 어두운 강 건너기

UI 설계로 처리

- 가장 간단한 해결방법은 처음부터 이러한 쿼리가 실행되지 않게 애플리케이션에서 제한한다.

- 예를 들어 2가지 이상으로 검색을 해야 한다거나, 기간 검색을 최대 1개월까지라는 조건을 준다면 인덱스를 잘 사용할 가능성이 커진다.

 

2. 외부 설정을 사용한 대처 방법의 주의점

 

3. 데이터 마트로 대처

- 외부 설정에 영향을 받지 않는 방법 중 하나가 바로 데이터 마트이다.

- 간단하게 줄여서 마트 또는 개요 테이블이라고도 한다.

- 데이터 마트는 특정한 쿼리에서 필요한 데이터만을 저장하는, 상대적으로 작은 크기의 테이블을 의미한다.

- 원래 테이블의 부분 집합(또는 서브셋)이라고 보면 좋다.

- 데이터 마트라는 용어는 원래 대규모의 데이터를 다뤄야 하는(따라서 성능 요건이 중요한) BI/DWH 분야에서 사용되었다.

- 접근 대상 테이블의 크기를 작게 해서 I/O양을 줄이는 것이 데이터 마트의 목적이다.

 

4. 데이터 마트를 채택할 시 주의점

- 이러한 데이터 마트를 채택할 때 주의할 포인트는 4가지다.

 

데이터 신선도

- 데이터 동기 시점의 문제이다.

- 데이터 마트는 원래 테이블의 부분적인 복사본이다.

- 따라서 특정 시점마다 원본 테이블에서 데이터를 동기화해야 한다.

- 동기 사이클이 짧으면 짧을수록 데이터의 신선도는 높으며, 원본 테이블과 가까운 모습을 가진다.

    ㆍ대신 빈번한 갱신 처리가 실행되면 성능적으로 문제가 생길 수 있다.

- 보통은 야간배치로 갱신

 

데이터 마트의 크기

- 데이터 마트를 만드는 목적은 테이블의 크기를 작게 해 I/O 양을 줄이는 것이다.

- 따라서 원래 테이블에서 크기를 딱히 줄일 수 없다면, 데이터 마트를 만들어도 빨라지지 않는다.

- 예를 들어 SELECT * 처럼 모든 필드를 검색해야 하는 경우나, 검색 조건의 선택률이 높아 레코드를 제대로 압축하지 못하는 경우에는 데이터 마트를 만들어도 성능적인 개선이 불가능하다

- 다만 GROUP BY 절을 미리 사용해서 집계를 마치고 데이터 마트를 만들면, 필드 수와 레코드 수를 크게 줄일 수 있으며, GROUP BY에 필요한 정렬 또는 해시처리도 사전에 끝낼 수 있으므로 굉장히 효과적이다

 

데이터 마트 수

- 수가 늘어나면 그 만큼 저장소 용량을 압박하고, 백업 또는 스냅샷을 할 때의 시간이 오래 걸리는 문제도 발생한다

- 따라서 데이터 마트에 지나치게 의존하는 것은 좋지 않다.

 

배치 윈도우

- 데이터 마트를 만드는 데도 시간이 걸리므로 배치 윈도우를 압박한다.

- 따라서 이러한 처리를 여유있게 수행하기 위한 배치 윈도우와 Job Net도 고려해야 한다.

 

5. 인덱스 온리 스캔으로 대처

- 외부 설계에 영향을 주지 않고 튜닝하는 두 번째 방법이 인덱스 온리 스캔이다.

- 인덱스 온리 스캔은 SQL 구문이 접근하려는 대상의 I/O 감소를 목적으로 한다는 점에서는 데이터 마트와 같다

- 특히 데이터 마트에서 문제가 되는 데이터 동기 문제를 해결할 수 있다.

 

- 인덱스 온리 스캔은 이름처럼 인덱스를 사용한 고속화 방법이다.

- 하지만 기존 인덱스와는 사용 방법이 많이다르다.

 

CREATE INDEX CoveringIndex ON Orders (order_id, receive_date);

- 인덱스 온리 스캔은 SQL 구문에서 필요한 필드를 인덱스만으로 커버할 수 있는 경우에 테이블 접근을 생략하는 기술이다.

- 이러한 기술의 장점은 데이터 마트를 사용할 때와 마찬가지로 I/O 비용을 줄일 수 있다는 것이다.

 

- 인덱스 온리 스캔이라는 기술은, 특정한 상황에서 검색 성능을 극단적으로 높일 수 있는 강력한 기능이다.

 

6. 인덱스 온리 스캔의 주의사항

DBMS에 따라 사용할 수 없는 경우도 있다.

 

한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있다

- 인덱스의 크기는 무제한이 아니며, 포함할 수 있는 필드 수또는 크기에 제한이 있다.

 

갱신 오버헤드가 커진다.

- 인덱스란 테이블의 갱신 부하를 올리기 마련이다.

- 인덱스 온리 스캔을 위한 커버링 인덱스는 성질상 필연적으로 필드 수가 많아 크기가 큰 인덱스가 되기 쉽다.

- 따라서 테이블을 갱신할 때의 오버 헤드도 일반적인 인덱스에 비해 큰 경향이 있ㅎ다.

- 검색을 고속으로 만들 수 있는 대신, 갱신 성능이 떨어지는 트레이드 오프 발생

 

정기적인 인덱스 리빌드가 필요

- 인덱스에만 접근한다는 것은 다시 말해 검색 성능 자체가 인덱스의 크기에 의존한다는 것이다.

- 특히 인덱스의 일부만 읽어들이는 일반적인 레인지 스캔과 달리, Oracle의 INDEX FAST FULL SCAN 등은 인덱스로 풀 스캔을 수행한다.

- 따라서 검색 성능이 인덱스의 크기에 거의 비례하며, 일반적인 인덱스보다도 크기에 민감하게 성능이 반응한다.

- 이러한 이유 때문에, 커버링 인덱스의 정기적인 크기 모니터링과 리빌드를 운용에 포함시켜야 한다.

 

SQL 구문에 새로운 필드가 추가된다면 사용할 수 없다

- 애플리케이션 우지 보수 작업에 의해서 쿼리에 새로운 필드가 추가될 수 있다.

- 그렇게 되면 인덱스 온리 스캔을 사용할 수 없다.

- 커버링 인덱스는 SQL 구문에서 사용하는 필드를 모두 커버할 수 없게 된 시점에서, 더 이상 커버링 인덱스가 아니다.

- 이러한 점에서 인덱스 온리 스캔은, 일반적인 인덱스에 비해 애플리케이션 유지 보수에 약한 타입의 튜닝이라고 할 수 있다.

 

---

마치며..

- B-tree 인덱스는 편리하지만, 카티널리티와 선택률에 따라 성능이 결정

- 선택률을 제어하려면 UI 설계까지도 변경할 필요가 있음

- 선택률이 높은 경우에는 인덱스 온리 스캔을 활용

- 결국 인덱스를 사용한 성능 개선도 I/O 비용을 줄이기 위한 노력