인덱스는 보통 B-tree 구조로 저장되는데, 여기서 성능을 더 높인 B+tree를 대부분 주로 사용한다.

B+tree가 좀 더 성능이 좋은 이유는 무엇일까?

• 루트와 리프의 거리를 가능한 일정하게 유지
• 트리의 깊이도 3-4정도의 수준으로 일정
• 데이터가 정렬 상태를 유지 → 검색 비용을 크게 감소
• 집약 함수 등에서 요구되는 정렬을 하지 않은 채 통과 가능

기타 인덱스

비트맵 인덱스

데이터를 비트 플래그로 변환해서 저장하는 형태의 인덱스이다.

카디널리티가 낮은 필드에 대해 효과를 발휘하지만, 갱신할 때 오버헤드가 너무 커 갱신이 자주 일어나지 않는 BI/DWH 용도로 사용한다.

해시 인덱스

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

하지만 등가 검색 외에는 효과가 거의 없고, 범위 검색을 하지 못해 거의 사용되지 않는다.

인덱스를 잘 활용해보자

카디널리티와 선택률

카디널리티란?

값의 균형을 나타내는 개념, 카디널리티가 높은 필드는 모든 레코드에 다른 값이 들어가 있는 유일 키 필드이고, 반대로 여러 레코드에 같은 값이 들어가 있다면 카디널리티가 낮은 필드이다.

선택률이란?

특정 필드값을 지정했을 때, 검색되는 레코드의 개수

만약 100개의 레코드 중, id=1인 레코드를 검색하면 레코드가 1개이므로, 1/100 = 0.01이 선택률이 된다.

인덱스를 사용하는 게 좋을까?

카디널리티가 높고, 선택률이 낮다면 인덱스를 사용하는 것을 권장한다.

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

DBMS마다 다르지만, 대체로 5~10프로 이하의 선택률을 가진다면 인덱스를 사용할 가치가 있다. 만약 선택률이 10프로 이상이라면 풀 스캔을 사용하는 게 높은 성능을 나타낼 수 있다.

인덱스로 성능 향상이 어려운 경우

일단 적절한 인덱스를 사용하려면, 아래와 같은 조건을 고려해봐야 한다.

• 레코드를 크게 압축할 수 있는가?
  • SQL의 검색 조건과 결합 조건
  • SQL 구문과 검색 키 필드의 카디널리티

압축 조건 존재 X

당연히 인덱스를 사용하긴 어렵다.

SELECT order_id, receive_date
FROM Orders;

레코드를 제대로 압축하지 못한 경우

process_flg의 분포가 아래와 같다고 가정하자.

• 1 주문단계: 200만 건
• 2 주문완료: 500만 건
• 3 재고확인중: 500만 건
• 4 배송준비중 500만 건
• 5 배송완료: 8,300만 건

SELECT order_id, receive_date
FROM Orders
WHERE process_flg = '5';

위 SQL구문의 선택률은 83%로 굉장히 높은 수치이다. 차라리 풀 스캔이 더 효율적이다.

이처럼 필드가 상태를 나타내는 경우에는 특정 범위에 값이 몰려있는 경우가 많아, 되도록이면 인덱스를 사용하지 말자.

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

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

위는 날짜의 범위가 검색 조건인데, 이때 범위가 하루가 될 수 있고, 1년이 될 수 있어 선택률이 들쑥날쑥할 것이다.

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

이것도 큰 점포일수록, 데이터가 많으니 입력하는 값마다 선택률이 달라진다.

인덱스를 사용할 수 없는 경우

LIKE 연산자

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

일단 압축이 되는 지 확인해보면, 선택률은 5%의 역치보다 낮은 수치라 압축 자체는 좋지만 인덱스 사용 시, 효율적이진 못하다.

LIKE 연산자의 경우 전방일치에만 인덱스가 적용된다. 위 구문처럼 중간 일치에서는 사용할 수 없다.

index 필드로 연산

인덱스 필드로 연산하는 경우엔 인덱스를 사용할 수 없다.

SELECT *
FROM SomeTable
WHERE col_1 * 1.1 > 100;

아래처럼 검색 조건의 우변에 식을 사용한다면 인덱스가 사용된다.

WHERE col_1 > 100/1.1

IS NULL

보통 인덱스 필드에 NULL이 존재하지 않기 때문에 인덱스가 사용되지 않는다.

SELECT *
FROM SomeTable
WHERE col_1 IS NULL;

또한 인덱스 필드에 함수를 사용하면, 인덱스를 사용할 수 없다.

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

함수 사용이나 인덱스에 연산할 때나 인덱스가 적용되어 있는 필드는 index이지, LENGTH(index)나, index * 0.1가 아니기 때문에 인덱스가 적용되지 않는 건 당연한 일이다.

부정형

부정형(<>, ≠, NOT IN) 또한 인덱스를 사용할 수 없다.

SELECT *
FROM SomeTable
WHERE col_1 <> 100;

인덱스를 사용하지 못할 때 대처

외부 설정으로 대체

이러한 쿼리가 실행되지 않도록, 애플리케이션을 제한적으로 두자. 사용자가 데이터를 필터링 할 수 없도록 기능을 따로 제공하지 않는 등과 같은 제한을 두면 된다.

주의할 점

당연히 사용자는 하고 싶은 것도 많고, 원하는 대로 입력하고 싶은 욕구가 있기 때문에 트레이드 오프를 통해 타협점을 찾아야 한다. 또 데이터베이스 개술자와 애플리케이션 개발자는 분업하기 때문에 서로 많은 소통과 정보 공유가 있어야 한다.

데이터 마트로 대처

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

아래처럼 데이터마트를 만들어 놓는다고 가정하자.

CREATE TABLE OrderMart
(order_id     CHAR(4) NOT NULL,
receive_date DATE NOT NULL);

그렇다면 이와 같이 압축 조건이 존재하지 않는 경우에도 성능을 보장할 수 있다.

SELECT order_id, receive_date
FROM OrderMart;

데이터 마트 주의사항

데이터 신선도

데이터 마트는 테이블의 부분 복사본이다. 따라서 특정한 시점마다 원본 테이블과 동기화해야 하는데, 동기화 사이클이 짧을수록 데이터는 신선하지만, 빈번한 갱신이 생겨 성능 문제가 발생할 수도 있다.

따라서 보통 야간에 배치를 실행한다.

데이터 마트 크기

데이트 마트의 목적은 테이블의 크기를 작게 해, I/O 양을 감소시키는 것이라 원본 테이블과 비교했을 때 크기 차이가 많이 나지 않으면 사용하는 의미가 없다.

하지만, GROUP BY 절을 미리 사용해 집계를 마치고 데이트 마트를 만들면 GROUP BY에 필요한 정렬 또는 해시 처리도 사전에 끝낼 수 있어 빠르다.

데이터 마트 수

데이트 마트는 기능 요건에 의해 만들어지는 엔티티가 아니기 때문에 제대로 관리하기 어렵다. 그 수가 늘어나면 저장소 용량을 압박하고, 스냅샷 생성 시 걸리는 시간도 길어진다. 따라서 데이터 마트에 지나치게 의존하는 것은 좋지 않다.

배치 윈도우

데이터 마트를 만드는 데도 시간이 걸리므로 배치 윈도우를 압박한다. 만들어진 데이터 마트는 통계 정보도 다시 수집할 때도 있어, 이러한 처리를 여유있게 실행하기 위한 Job Nest이나 배치 윈도우를 고려해야 한다.

인덱스 온리 스캔으로 대처

위에서 봤다시피 아래의 SQL 구문은 WHERE 구문이 없어, 풀 스캔이 발생했다.

SELECT order_id, receive_date
FROM OrderMart;

아래처럼 2개의 필드를 커버하는 인덱스가 존재하면, 테이블이 아닌 인덱스를 스캔 대상으로 하는 검색(index only scan)을 사용할 수 있다. 따라서, 테이블에 접근하지 않고, 인덱스만 스캔하면 검색이 완료된다.

CREATE INDEX CoveringIndex ON Orders (order_id, receive_date);

column 지향 저장소와 row 지향 저장소

대부분의 RDB는 row 지향 저장소이다. 쉽게 말하면 레코드 단위로 데이터를 저장하는 형태

하지만 이는 비효율적으로 보일 수 있다.

SELECT col_1 
FROM SomeTable;

위 구문에서 검색할 column은 단 하나인데, row 지향 저장소에서는 I/O가 레코드 단위이기 때문에, 불필요한 컬럼까지 읽어야 한다.

입출력적으로 굉장히 큰 낭비가 발생한다.

따라서 column 지향 저장소는 데이터 저장 단위를 column(=필드)로 바꾸어서 불필요한 필드를 읽지 않도록 만든 방법이다.

하지만 반대로 아래와 같은 구문에서 성능이 떨어진다.

SELECT *
FROM SomeTable
WHERE col_1 = 'A';

물론 WHERE 구문을 이용한 조건이 있지만, 검색하는 과정에서 모든 필드에 접근해야 하기 때문에 row 지향 저장소보다 성능이 떨어진다.

index only scan의 주의사항

• DB에 따라 사용하지 못할 수 있다
• 한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있다.
• 갱신 오버 헤드가 커진다 (커버링 인덱스는 보통 필드 수가 많다)
  • 테이블을 갱신할 때, 오버 헤드도 일반적인 인덱스에 비해 큰 경향이 있다.
  • 검색 속도는 빠르지만, 갱신 속도는 현저히 낮아진다.
• 정기적인 인덱스 리빌드 필요
  • 검색 성능 자체가 인덱스 크기에 의존
  • 정기적인 크기 모니터링과 리빌드를 운용에 포함시켜야 한다
• SQL 구문에 새로운 필드가 추가되면 사용할 수 없다
  • 유지 보수에 약하다는 단점