[MySQL] 인덱스의 기본, B-Tree 인덱스 사용법

DBMS에서 쿼리를 효율적으로 사용하기 위해

인덱스는 매우 중요하다.

 

그 중 가장 대표적인 B-Tree 인덱스에 대해 알아보자.

인덱스란

우선 인덱스가 무엇인지부터 살펴보자.

인덱스는 컬럼의 값과 해당 레코드가 저장된 주소를

키와 값의 쌍으로 만들어두는 것이다.

 

그리고 컬럼의 값을 주어진 순서로 미리 정렬하여 보관한다.

 

인덱스는 Sorted List의 구조를 가지고 있는데,

Sorted List 자료 구조는 데이터가 변경/추가될 때마다 

항상 값을 다시 정렬해야 하므로 저장하는 과정이 느리고 복잡하지만

이미 정렬 되어 있기 때문에 원하는 값을 매우 빠르게 찾을 수 있다는 특징을 갖는다.

 

즉, 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고

읽기 성능을 높이는 기능이다.

 

그렇기에 마냥 인덱스를 많이 추가하는 것은 바람직하지 않다.

 

B-Tree 인덱스란

B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘이다.

오래전에 도입되어 성숙된 상태이다.

Balanced Tree를 의미한다.

Binary Tree로 오해하는 이들이 많은데, 2개보다 많은 자식 노드를 갖기 때문에

Binary Tree는 아니다.

 

B-Tree 인덱스를 이용한 검색은

값의 100% 일치,

값의 앞부분(Left-most part)가 일치하는 경우,

부등호(>, <) 비교 조건에서 사용할 수 있다.

 

키 값의 뒷부분을 검색하는 용도로는 인덱스를 사용할 수 없다.

예를 들어, WHERE word LIKE '%하다'; 의 조건에서는 사용할 수 없는 것이다.

또, 인덱스의 키 값에 함수 등으로 변형이 가해진 후 값을 비교하는 경우에는

인덱스를 사용할 수 없다.

 

B-Tree 인덱스 성능에 영향을 미치는 요소들

B-Tree 인덱스는 대표적으로 세 가지 요소에 의해

성능이 영향을 받는다.

 

첫째는 인덱스 키 값의 크기이다.

인덱스도 디스크에 데이터를 저장하는 기본 단위인

페이지(page) 단위로 저장된다.

 

인덱스의 키 값의 크기가 크면 

필요한 페이지 수가 늘어나기 때문에

디스크로부터 읽어야 하는 횟수가 늘어나고

그만큼 읽기 속도가 느려지게 된다.

 

두 번째는 유니크한 값의 수이다.

Cardinality(기수성)라고도 한다.

 

인덱스는 유니크한 값이 많을 때,

즉, 중복된 값이 적을 때

검색 대상이 줄어들기 때문에 그만큼 성능이 좋아진다.

 

세 번째는 읽어야 하는 레코드의 건수이다.

일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이

테이블에서 직접 레코드 1건을 읽는 것보다 4-5배 정도

비용이 더 많이 드는 작업인 것으로 예측한다.

 

따라서 인덱스를 통해 읽어야 할 레코드의 건수가

전체 테이블 레코드의 20-25%를 넘으면

인덱스를 이용하지 않고

테이블 전체를 직접 읽어 필요한 레코드만 필터링 하는 방식으로

처리하는 것이 더 효율적이다.

 

B-Tree 인덱스로 데이터 읽는 방법

B-Tree 인덱스를 이용하여 데이터를 읽는 방법

네 가지를 알아보자.

 

첫 번째, 가장 대표적인 방식은 인덱스 레인지 스캔이다.

B-Tree 인덱스를 사용하는 빠른 방법이다.

검색해야 할 인덱스의 범위가 결정됐을 때 사용된다.

 

인덱스 레인지 스캔의 절차를 3단계로 나눌 수 있다.

 

1. 인덱스 탐색(Index Seek): 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 

2. 인덱스 스캔(Index Scan): 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다.

3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

 

경우에 따라 3번 과정은 필요하지 않을 수 있는데,

이를 커버링 인덱스라고 한다.

 

커버링 인덱스로 처리되는 쿼리는

디스크의 레코드를 읽지 않아도 되기 때문에

랜덤 읽기가 상당히 줄어들고 성능이 빨라진다.

 

예를 들어, 인덱스가 name 컬럼에 걸려있는데,

SELECT name 만 하는 경우이다.

 

두 번째 방식은 인덱스 풀스캔이다.

인덱스 레인지 스캔과 달리 인덱스의 처음부터 끝까지 모두 읽는 방식이다.

대표적으로 쿼리의 조건절에 사용된 컬럼이

인덱스의 첫 번째 컬럼이 아닌 경우 사용된다.

 

예를 들어, 인덱스가 (A, B, C) 순으로 만들어져 있는데,

조건절은 B 컬럼이나 C 컬럼으로 검색하는 경우다.

 

일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로

테이블 풀 스캔보다는 인덱스 풀 스캔이 더 효율적이다.

 

쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우

이 방식이 사용된다.

인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면

절대 이 방식으로 처리되지 않는다.

 

즉, 인덱스 풀 스캔은 인덱스 레인지 스캔보다 느리고

테이블 풀 스캔보다 빠르다.

 

세 번째는 루스 인덱스 스캔이다.

인덱스 레인지 스캔과 비슷하게 동작하지만

중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어간느 형태로 처리한다.

일반적으로 GROUP BY 또는 집합 함수 가운데

MAX(), MIN() 함수에 대해 최적화를 하는 경우에 사용된다.

 

네 번째는 인덱스 스킵 스캔이다.

인덱스를 구성하는 컬럼 순서는 매우 중요하다.

앞 순서의 컬럼에 대한 비교 조건이 없이 뒤 컬럼에 대해 검색할 경우

인덱스가 사용되지 않기 때문이다.

 

그런데 MySQL 8.0 버전부터는

인덱스 앞 순서의 컬럼을 건너뛰고

뒷 순서 컬럼만으로도 인덱스 검색이 가능하게 해주는

인덱스 스킵 스캔 최적화 기능이 도입됐다.

 

SET optimizer_switch='skpi_scan=on';

를 통해 인덱스 스킵 스캔을 활성화하고

SELECT 쿼리를 실행하면 인덱스 앞 순서의 컬럼을 건너뛰고 인덱스를 사용할 수 있다.

 

다만, 인덱스 스킵 스캔은 새로 도입된 기능이라 아직 단점이 있다.

WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 하며,

쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능한 커버링 인덱스여야 한다.

 

B-Tree 인덱스의 정렬

인덱스를 생성하는 시점에

인덱스를 구성하는 각 컬럼의 정렬을 오름차순 또는 내림차순으로 정렬할 수 있다.

 

MySQL 5.7 버전까지는 컬럼 단위로 정렬 순서를 혼합해서 인덱스를 생성할 수 없었으나,

8.0 버전부터 가능해졌다.

CREATE INDEX idx_name_height ON students (name ASC, height DESC);

 

인덱스의 정렬 방식과 관계 없이

인덱스가 오름 차순으로 정렬되어 있더라도

인덱스를 최댓값부터 거꾸로 읽으면 ORDER BY name DESC를 사용하는데 문제가 없다.

 

이렇게 인덱스를 정렬된 방향과 반대로 읽는 것을 인덱스 역순 스캔(Backward Index Scan)이라고 하고,

정렬된 방향대로 읽는 것을 인덱스 정순 스캔(Forward Index Scan)이라고 한다.

 

둘 다 가능하지만 역순 스캔으로 사용하는 쿼리가 

정순 스캔 쿼리보다 대략 28% 정도 시간이 더 걸린다는 테스트 결과가 있었으므로

되도록이면 자주 사용되는 정렬에 맞게

인덱스 정렬을 지정하는 것이 좋다.

 

B-Tree 인덱스 사용법

B-Tree 인덱스를 효율적으로 사용하는 방법을 알아보자.

 

다중 컬럼 인덱스에서 각 컬럼의 순서와 그 컬럼에 사용된 조건이

동등 비교(=)인지 범위 조건인지 (<, >)에 따라

각 인덱스 컬럼의 활용 형태와 효율이 달라진다.

 

비교 작업은 적게 일어날 수록 효율이 좋은데,

비교 작업의 범위를 줄여주는 조건을 작업 범위 결정 조건이라고 부르며,

그렇지 못하고 하나 하나 비교해야 하는 조건을 필터링 조건이라고 한다.

 

작업 범위를 결정하는 조건은 많을 수록 쿼리 처리 성능을 높이지만

필터링 조건은 그렇지 못한다.

 

작업 범위 결정 조건으로 B-Tree 인덱스를 사용할 수 없는 상황을 살펴보자.

 

- NOT_EQUAL로 비교된 경우(<>, NOT IN, NOT BETWEEN, IS NOT NULL)

- LIKE '%??'(앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우

- 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우

- NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우

- 데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우)

- 문자열 데이터 타입의 콜레이션이 다른 경우

 

일반적인 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만

MySQL에서는 NULL 값도 인덱스에 저장된다.

 

다중 컬럼으로 만들어진 인덱스를 작업 범위 결정 조건으로 사용할 수 없는 상황도 있다.

- 인덱스 앞 순서의 컬럼에 대한 조건이 없는 경우

- 인덱스 앞 순서의 컬럼의 비교 조건이 작업 범위 결정 조건으로 B-Tree 인덱스를 사용할 수 없는 위 조건들 중 하나인 경우