08. 인덱스-1

8.1 디스크 읽기 방식

데이터베이스의 성능 튜닝은 랜덤 I/O 나 순차 I/O 등과 같은 디스크 I/O를 어떻게 줄이느냐가 관건일 때가 상당히 많다.

하드 디스크 드라이브(HDD) 와 솔리드 스테이트 드라이브(SSD)

컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만 하드 디스크 드라이브는 기계식 장치다. 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다. 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD(Solid State Drive)가 많이 출시되고 있다. SSD도 기존 하드 디스크 드라이브와 같은 인터페이스(SATA나 SAS)를 지원하므로 내장 디스크나 DAS 또는 SAN에 그대로 사용할 수 있다.

 

SSD

• 기존의 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)을 제거하고 대신 플래시 메모리를 장착했다.
• 디스크 원판을 기계적으로 회전시킬 필요가 없어 빠르게 데이터를 읽고 쓸 수 있다.
• 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다.
• 컴퓨터의 메모리(D-Ram)보다는 느리지만 HDD보다는 훨씬 빠르다.
• 요즘은 DBMS용으로 사용할 서버에는 대부분 SSD를 채택한다.

주요 장치의 초당 처리 횟수(수치가 클수록 빠른 장치를 의미)

 

• 순차 I/O: 디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 I/O 방식
• 순차 I/O에서는 SSD가 HDD보다 조금 빠르거나 비슷한 성능을 보인다.
• 랜덤 I/O에서는 SSD가 HDD보다 훨씬 빠르다.

데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이라고 볼 수 있다. 

랜덤 I/O 와 순차 I/O

랜덤 I/O 는 하드 디스크 드라이브의 플래터(원판)을 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미하는데, 사실 순차 I/O 또한 이 작업 과정은 같다.

 

순차 I/O(왼쪽)와 랜덤 I/O(오른쪽) 비교

 

순차 I/O 는 3개의 페이지(3x16KB)를 디스크에 기록하기 위해 1번 시스템 콜을 요청했지만, 랜덤 I/O 는 3개의 페이지를 디스크에 기록하기 위해 3번 시스템 콜을 요청했다. 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O 는 디스크의 헤드를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직였다. 디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정된다. 결국 그림의 경우 순차 I/O 는 랜덤 I/O 보다 3배 정도 빠르다고 볼 수 있다.

 

디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다. 그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 크다. 데이터베이스 대부분의 작업은 이런 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다. 디스크 원판을 가지지 않는 SSD 드라이브에서도 랜덤 I/O 는 순차 I/O 보다 전체 스루풋(Throughput)이 떨어진다. 

 

참고: 순차 I/O 와 랜덤 I/O 를 단순하게 비교해서 설명한 것으로 랜덤 I/O 나 순차 I/O 모두 파일에 쓰기를 실행하면 반드시 동기화(fsync 또는 flush 작업)가 필요하다. 그런데 순차 I/O 인 경우에도 이러한 파일 동기화 작업이 빈번하게 발생한다면 랜덤 I/O 와 같이 비효율적인 형태로 처리될 때가 많다. 

 

사실 쿼리를 튜닝해서 랜덤 I/O 를 순차 I/O 로 바꿔서 실행할 방법은 그다지 많지 않다. 일반적인 쿼리 튜닝은 랜덤 I/O 자체를 줄이는 것이 목적이다. 이는 쿼리를 처리하는 데 꼭 필요한 데이터만을 읽도록 쿼리를 개선하는 것을 의미한다.

 

참고: 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O 를 사용하며, 풀 테이블 스캔은 순차 I/O 를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 이는 순차 I/O 가 랜덤 I/O 보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문인데, 이런 형태는  OLTP(On-Line Tansaction Processing) 성격의 웹 서비스보다는 데이터 웨어하우스나 통계작업에서 자주 사용된다.

8.2 인덱스란?

• 인덱스: 책의 맨 끝에 있는 찾아보기(또는 "색인")
• 데이터 파일: 책의 내용
• 데이터 파일에 저장된 레코드의 주소: 페이지 번호(in 찾아보기)

DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸린다. 그래서 컬럼(또는 컬럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-value pair)으로 삼아 인덱스를 만들어 두는 것이다. 그리고 책의 "찾아보기"처럼 DBMS 인덱스도 컬럼 값을 주어진 순서대로 미리 정렬해서 보관한다.

 

인덱스와 데이터 파일의 자료 구조

• 인덱스: SortedList, 저장된 컬럼 값을 항상 정렬된 상태로 유지한다.
• 데이터 파일: ArrayList, 별도의 정렬 없이 값을 저장되는 순서 그대로 유지한다. 

 

SortedList

• 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리다.
• 이미 정렬되어 있어서 원하는 값을 빠르게 찾을 수 있다.

DBMS 에서도 인덱스가 많은 테이블은 INSERT, UPDATE, DELETE 문장의 처리가 늦어진다. 하지만 이미 정렬된 "찾아보기"용 표(인덱스)를 가지고 있기 때문에 SELECT 문장은 매우 빠르게 처리할 수 있다.

테이블 인덱스 추가 -> 데이터 저장 속도를 희생하고, 읽기 속도를 증가시킴

SELECT 쿼리 문장의 WHERE 조건절에 사용되는 컬럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스 크기가 비대해져 오히려 역효과만 불러올 수 있다.

인덱스의 역할별 분류

프라이머리 키(Primary key)

• 레코드를 대표하는 컬럼
• 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 식별자라고도 부름
• 조건: NULL 값과 중복을 허용하지 않음

보조 키(Secondary key)

• 프라이머리 키를 제외한 나머지 모든 인덱스
• 유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해서 사용할 수도 있다고 해서 대체 키라고도 부름

인덱스의 데이터 저장 방식(알고리즘)별 분류

상당히 많은 분류가 가능하지만 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분

그 외에 Fractal-Tree 인덱스, 로그 기반의 Merge-Tree 인덱스 등이 있다.

 

B-Tree 알고리즘

• 가장 일반적으로 사용되는 인덱스 알고리즘
• 컬럼의 값을 변경하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘
• MySQL 서버에서는 위치 기반 검색을 지원하기 위한 R-Tree  인덱스 알고리즘도 있지만, 결국 B-Tree의 응용 알고리즘이라고 볼 수 있다.

Hash 인덱스 알고리즘

• 컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘
• 매우 빠른 검색을 지원
• 값을 변형해서 인덱싱하므로 전방(Prifix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.
• 주로 메모리 기반 데이터베이스에서 많이 사용

 

데이터 중복 허용 여부로 분류하면 유니크 인덱스(Unique)와 유니크하지 않은 인덱스(Non-Unique)로 구분할 수 있다. 인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지, 1개 이상 존재할 수 있는지를 의미하지만, 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제가 된다. 유니크 인덱스에 대해 동등 조건(Equal, =)으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 의미하기 때문이다. 이외에도 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.

 

인덱스의 기능별로 분류하면 전문 검색용 인덱스나 공간 검색용 인덱스 등이 있다. 

8.3 B-Tree 인덱스

Balanced Tree (Not Binary Tree)

B-Tree에는 여러 가지 변형된 형태의 알고리즘이 있는데, 일반적으로 DBMS에서는 주로 B+-Tree 또는 B*-Tree 가 사용된다. B-Tree 는 컬럼의 원래 값을 변형시키지 않고 (물론 값의 앞부분만 잘라서 관리하기는 하지만) 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다. 전문 검색과 같은 특수한 요건이 아닌 경우, 대부분 인덱스는 거의 B-Tree 를 사용할 정도로 일반적인 용도에 적합한 알고리즘이다.

구조 및 특성

B-Tree 기본 구조

• 트리 구조의 최상위에 하나의 "루트 노드(Root node)" + 하위 자식 노드
• 가장 하위에 있는 노드를 "리프 노드(Leaf node)"
• 루트 노드도 아니고 리프 노드도 아닌 중간의 노드를 "브랜치 노드(Branch node)"

B-Tree 의 특성

• 각 노드는 테이블의 키 컬럼 값을 key 로 가진다.
• 상위 노드(부모 노드)는 value 로 하위 노드(자식 노드)의 주솟값을 가진다.
• 리프 노드는 value 로 항상 실제 데이터 파일 상의 레코드의 주솟값을 가진다. 

인덱스와 실제 데이터가 저장된 데이터는 따로 관리된다.

 

B-Tree 인덱스의 구조

 

인덱스와 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장되어 있다. 데이터 파일의 레코드는 INSERT 순대로 저장되어 있다고 생각할 수 있지만 레코드를 전혀 삭제하거나 변경하지 않고 INSERT 만 수행한다면 맞을 수 있다. 하지만 레코드가 삭제되어 빈 공간이 생기면 그 다음의 INSERT 는 가능한 한 삭제된 공간을 재활용하도록 DBMS가 설계되기 때문에 항상 INSERT 된 순서로 저장되는 것은 아니다.

 

참고: 대부분 RDBMS 의 데이터 파일에서 레코드는 특정 기준으로 정렬되지 않고 임의의 순서로 저장된다. 하지만 InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서대로 정렬되어 저장된다. 이는 오라클의 IOT(Index Organized Table)나 MS-SQL의 클러스터 테이블과 같은 구조를 말한다. 다른 DBMS에서는 클러스터링 기능이 선택 사항이지만, InnoDB에서는 디폴트로 클러스터링 테이블이 생성된다. 클러스터링이란 비슷한 값을 최대한 모아서 저장하는 방식을 의미한다.

 

 

B-Tree의 리프 노드와 테이블 데이터 레코드(MyISAM)

 

레코드 주소는 MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 INSERT 된 순번이거나 데이터 파일 내의 위치(Offset)이다. 

MyISAM 스토리지 엔진에서 인덱스의 구조 -> 4.3.3절 '데이터 파일과 프라이머리 키(인덱스) 구조'의 ROWID 설명

 

B-Tree의 리프 노드와 테이블 데이터 레코드(InnoDB)

 

세컨더리 인덱스를 통한 조회

두 스토리지 엔진의 인덱스의 가장 큰 차이점은 세컨더리 인덱스를 통해 데이터 파일의 레코드를 찾아가는 방법에 있다.

 

InnoDB 스토리지 엔진을 사용하는 테이블에서는 프라이머리 키가 ROWID의 역할을 한다. 

MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가지는 반면, InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다.

 

InnoDB 테이블에서 인덱스를 통해 레코드를 읽을 때는 MyISAM 처럼 데이터 파일을 바로 찾아가지 못한다.

세컨더리 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스와 리프 페이지에 저장돼 있는 레코드를 읽는다.

즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 한 번 검색해야 한다.

이러한 인덱스 구조 차이에서 오는 각자의 장단점이 있다. -> 8.8절 '클러스터링 인덱스' 

B-Tree 인덱스 키 추가 및 삭제

테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생한다. 인덱스 키 추가나 삭제가 어떻게 처리되는지 알아두면 쿼리의 성능을 쉽게 예측할 수 있을 것이다.

인덱스 키 추가

1. 저장될 키 값을 이용해 B-Tree 상의 적절한 위치 검색
2. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장

리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리(split)돼야 하는데, 이는 상위 브랜치 노드까지 처리 범위가 넓어진다. 이러한 작업 탓에 B-Tree는 상대적으로 쓰기 작업(새로운 키를 추가하는 작업)에 비용이 많이 든다.

 

새로운 키 값이 B-Tree 에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있다.

• MyISAM나 MEMORY: INSERT 문장이 실행되면 즉시 새로운 키 값을 B-Tree 인덱스에 변경
• InnoDB: 필요하다면 인덱스 키 추가 작업을 지연 처리 가능 (체인지 버퍼). 하지만 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제

인덱스 키 삭제

삭제할 키 값이 저장된 B-Tree의 리프 노드를 찾아서 삭제 마크를 하면 작업이 완료된다.

삭제 마킹된 인덱스 키 공간은 그대로 방치하거나 재활용할 수 있다. 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 디스크 I/O 가 필요한 작업이다.

MySQL 5.5 이상 버전의 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어(체인지 버퍼) 지연 처리될 수도 있다. 처리가 지연된 인덱스 키 삭제도 사용자에게는 특별한 악영향 없이 MySQL 서버가 내부적으로 처리한다. 

인덱스 키 변경

인덱스 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키 값이 변경되는 경우, 단순히 인덱스상의 키 값만 변경하는 것은 불가능 하다.

1. 변경할 키 값 삭제
2. 새로운 키 값을 추가

키 변경 과정은 앞서 설명한 키 추가 및 삭제와 같은 절차대로 처리된다. 

인덱스 키 검색

INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 빠른 검색을 위해서다. 인덱스를 검색하는 작업은 B-Tree 의 루트 노드부터 시작하여 브랜치 노드 -> 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 "트리 탐색"이라고 한다.

인덱스 트리 탐색은 SELECT 뿐만 아니라 UPDATE 나 DELETE 를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 사용된다. B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분(Left-most part)만 일치하는 경우에 사용할 수 있다. 부등호(<,>) 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다. 변형된 값은 이미 B-Tree 인덱스에 존재하는 값이 아니다. 따라서 함수나 연산을 수행한 결과로 정렬하거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없다.

 

InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다. 따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 테이블의 모든 레코드를 잠글 수 있다. InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다.

B-Tree 인덱스 사용에 영향을 미치는 요소

• 인덱스를 구성하는 컬럼의 크기
• 레코드의 건수
• 유니크한 인덱스 키 값의 개수

등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

인덱스 키 값의 크기

페이지(Page) (= 블록(Block))

• InnoDB 스토리지 엔진에서 디스크에 데이터를 저장하는 가장 기본 단위
• 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위
• 버퍼 풀에서 데이터를 버퍼링하는 기본 단위

인덱스도 결국 페이지 단위로 관리되며, 루트와 브랜치, 리프 노드를 구분하는 기준이다.

 

일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적이다. 자식 노드의 최대 개수는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다. 

• innodb_page_size 시스템 변수 (5.7버전부터) 로 4KB ~ 64KB 사이의 값 선택 가능
• 기본 값: 16KB

인덱스 페이지의 구성

 

페이지의 크기는 한정적인데 인덱스를 구성하는 키 값의 크기가 커지면 자식 노드의 최대 개수가 줄어든다. 결국 인덱스 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.

또한 인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 것을 의미한다. 인덱스를 캐시해두는 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시 영역은 크기가 제한적이기 때문에 하나의 레코드를 위한 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어든다. 자연히 메모리의 효율이 떨어질 것이다.

B-Tree 깊이(Depth)

B-Tree 인덱스의 깊이는 상당히 중요하지만 직접 제어할 방법은 없다.

B-Tree 의 깊이는 MySQL 에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결된다. 결론적으로 인덱스 키 값의 크기가 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라고 해도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하게 된다.

따라서 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다. 아무리 대용량 데이터베이스라고 해도 깊이가 5단계 이상 깊어지는 경우는 흔치 않다.

선택도(기수성)

인덱스에서 선택도(Selectivity) 또는 기수성(Cardinality)은 거의 같은 의미로 사용되며, 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값은 100인데, 그 중에서 유니크한 값의 수가 10이라면 기수성은 10이다. 인덱스 키 값 가운데 중복된 값이 많아지면 기수성은 낮아지고 동시에 선택도 또한 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.

 

참고: 선택도가 좋지 않다고 하더라도 정렬이나 그루핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 많다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러가지 용도를 고려해 적절히 인덱스를 설계할 필요가 있다.

 

MySQL 에서는 인덱스의 통계 정보(유니크한 값의 개수)가 관리되기 때문에 인덱스로 생성되지 않은 컬럼의 기수성은 작업 범위에 아무런 영향이 없다. 

 

예) tb_test ( country, city ), INDEX( country ), 전체 레코드 건수 10,000건

케이스 A: country 컬럼의 유니크한 값의 개수가 10개

케이스 B: country 컬럼의 유니크한 값의 개수가 1,000개

 

위의 쿼리를 실행하면 A 케이스의 경우 평균 1000건, B 케이스의 경우에는 평균 10건이 조회될 수 있다는 것을 인덱스의 통계 정보(유니크한 값의 개수)로 예측할 수 있다. A 케이스와 B 케이스 모두 실제 모든 조건을 만족하는 레코드가 단 1건만 있었다면 A 케이스의 인덱스는 적합하지 않은 것이라고 볼 수 있다. A 케이스는 1건의 레코드를 위해 쓸모없는 999건의 레코드를 더 읽은 것이지만, B 케이스는 9건만 더 읽은 것이다. 물론 필요한 만큼의 레코드만 정확히 읽을 수 있다면 최상이겠지만 현실적으로 모든 조건을 만족하게 인덱스를 생성한다는 것은 불가능하므로 이 정도의 낭비는 무시할 수 있다.

 

예) 각 국가의 도시를 저장하는 tb_city, 전체 레코드 건수 10,000건, 국가와 도시가 중복되지 않고 저장

테이블 생성

SELECT 쿼리

country 컬럼의 유니크 값이 10개일 때

country 컬럼의 유니크 값이 10개이므로 tb_city 테이블에는 10개 국가(country)의 도시(city) 정보가 저장되어 있는 것이다. MySQL 서버는 인덱스된 컬럼(country)에 대해 전체 레코드의 건수나 유니크한 값의 개수 등에 대한 통계 정보를 가지고 있다. 이 케이스의 tb_city 테이블에서 country='KOREA' 라는 조건으로 인덱스를 검색하면 1000건(10000/10)이 일치하리라는 것을 예상할 수 있다. 그런데 인덱스를 통해 검색된 1000건 가운데 city='SEOUL' 인 레코드는 1건이므로 999건은 불필요하게 읽은 것으로 볼 수 있다.

 

country 컬럼의 유니크 값이 1000개일 때

country 컬럼의 유니크 값이 1000개이므로 tb_city 테이블에는 1000개 국가(country)의 도시(city) 정보가 저장되어 있는 것이다. 이 케이스에서는 한 국가당 대략 10개 정도의 도시 정보가 저장돼 있으리라는 것을 예측할 수 있다. 이 케이스의 tb_city 테이블에서 country='KOREA' 라는 조건으로 인덱스를 검색하면 10건(10000/1000)이 일치할 것이며, 그 10건 중에서 city='SEOUL' 인 레코드는 1건이므로 9건은 불필요하게 읽은 것이다.

 

두 케이스의 테이블에서 똑같은 쿼리를 실행하더라도 MySQL 서버가 수행한 작업 내용의 차이는 매우 크다는 것을 알 수 있다. 이처럼 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 든다. 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다. 즉, 인덱스를 통해 읽어야 할 레코드의 건수(물론 옵티마이저가 판단한 예상 건수)가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 사용하지 않고 테이블을 모두 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적이다.

B-Tree 인덱스를 통한 데이터 읽기

어떤 경우에 인덱스를 사용하게 유도할지, 또는 사용하지 못하게 할지 판단하려면 MySQL, 정확히는 스토리지 엔진이 어떻게 인덱스를 이용(경유)해서 실제 레코드를 읽어내는지 알아야 한다.

 

MySQL이 인덱스를 이용하는 대표적인 방법

• 인덱스 레인지 스캔
• 인덱스 풀 스캔
• 루스 인덱스 스캔 / 인덱스 스팁 스캔

인덱스 레인지 스캔 (Index Range Scan)

인덱스의 접근 방법 중 가장 대표적인 접근 방식, 뒤에서 설명할 두 가지 접근 방식보다는 빠른 방법이다. 

 

참고: 인덱스를 통해 레코드를 한 건만 읽는 경우와 한 건 이상을 읽는 경우를 각각 다른 이름으로 구분하지만, 이번 절에서는 "인덱스 레인지 스캔"으로 묶어서 표현했다.

 

mysql> SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

 

인덱스를 이용한 레인지 스캔

 

인덱스 레인지 스캔

검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 

검색하려는 값의 수나 검색 결과 레코드 건수와 관계없이 레인지 스캔이라고 표현한다.

1. 루트 노드 -> 브랜치 노드 -> 리프 노드 = 필요한 레코드의 시작 지점 
2. 시작 지점부터 리프 노드의 레코드만 순서대로 읽는다 -> 인덱스 자체의 정렬 특성
3. 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간 링크를 이용해 다음 리프 노드를 찾아 스캔한다.
4. 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 반환하고 쿼리를 끝낸다.

그림은 인덱스만을 읽는 경우로, 인덱스가 아닌 컬럼을 조회해야 할 경우에는 B-Tree 인덱스의 리프 노드를 스캔하면서 실제 데이터 파일의 레코드를 읽어 와야 한다.

 

인덱스 레인지 스캔을 통한 데이터 레코드 읽기

 

리프 노드에 저장된 레코드 주소로 데이터 파일의 레코드를 읽어오는데, 레코드 건 단위로 랜덤 I/O가 한 번씩 일어난다.

그림처럼 3건의 레코드가 검색 조건에 일치했다고 가정하면, 데이터 레코드를 읽기 위해 랜덤 I/O가 최대 3번 필요한 것이다.

그래서 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다. 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 된다.

 

1. 인덱스 탐색(Index seek): 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 
2. 인덱스 스캔(Index scan): 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 순서대로 읽는다. (정순/역순)
3. 2에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

쿼리가 필요로 하는 데이터에 따라 3번 과정은 필요하지 않을 수도 있는데, 이를 커버링 인덱스라고 한다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 되기 때문에 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다. 

인덱스 풀 스캔 (Index Full Scan)

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

 

쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우

• 예를 들어, 인덱스는 (A, B, C) 컬럼의 순서대로 만들어져 있지만 쿼리의 조건절은 B 컬럼이나 C 컬럼으로 검색하는 경우다.

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

• 인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.
• 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다. 

 

인덱스 풀 스캔

 

1. 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한다.
2. 인덱스의 리프 노드를 연결하는 LinkedList 를 따라서 처음부터 끝까지 스캔한다.

이 방식은 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 효율적이다. 인덱스에 포함된 컬럼만으로 쿼리를 처리할 수 있는 경우 테이블의 레코드를 읽을 필요가 없기 때문이다. 인덱스의 전체 크기는 테이블의 크기보다는 훨씬 작으므로 인덱스 풀 스캔은 테이블 전체를 읽는 것보다는 적은 디스크 I/O로 쿼리를 처리할 수 있다.

 

참고: 인덱스 풀 스캔은 인덱스를 이용하긴 하지만 효율적인 방식은 아니며, 일반적으로 인덱스를 생성하는 목적에서 벗어난 방식이다. 테이블 전체를 읽거나 인덱스 풀 스캔 방식으로 인덱스를 사용하는 경우는 "인덱스를 사용하지 못한다" 또는 "인덱스를 효율적으로 사용하지 못한다" 라고 표현했다.

루스 인덱스 스캔 (Loose Index Scan)

오라클 DBMS의 "인덱스 스킵 스캔" 과 작동 방식은 비슷하지만 MySQL에서는 "루스 인덱스 스캔" 이라고 한다.

인덱스 레인지 스캔과 인덱스 풀 스캔은 루스 인덱스 스캔과 상반된 의미에서 타이트(Tight) 인덱스 스캔으로 분류한다.

루스 인덱스 스캔이란 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다.

 

루스 인덱스 스캔

 

루스 인덱스 스캔

• 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 값은 무시(SKIP)하고 다음으로 넘어감
• GROUP BY, MAX() 또는 MIN() 함수에 대한 최적화

 

 

INDEX (dept_no, emp_no)

이 인덱스는 (dept_no, emp_no) 조합으로 정렬까지 돼있어서 그림처럼 dept_no 그룹별로(GROUP BY dept_no) 첫 번째 레코드의 emp_no 값만 읽으면 된다. 즉, 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다. 

루스 인덱스를 사용하기 위한 조건 -> 10장 '실행 계획'

인덱스 스킵 스캔 (Index Skip Scan)

데이터베이스 서버에서 인덱스의 핵심은 값이 정렬되어 있다는 것이며, 이로 인해 인덱스를 구성하는 컬럼의 순서가 매우 중요하다.

 

 

employees 테이블의 인덱스를 사용하려면 gender 컬럼에 대한 비교 조건이 필수다.

(쿼리의 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다)

 

 

첫 번째 쿼리 같은 경우 gender 컬럼에 대한 비교 조건이 없어 인덱스를 사용할 수 없었다. 주로 이런 경우에는 birth_date 컬럼부터 시작하는 인덱스를 새로 생성해야만 했다. -> 새로운 B-Tree 구조 생성 및 관리 필요

8.0 버전부터 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다. 8.0 이전 버전에서도 비슷한 최적화를 수행하는 '루스 인덱스 스캔'이라는 기능이 있었지만 GROUP BY 작업을 처리하기 위해 인덱스를 사용하는 경우에 한정되어 있었다. 인덱스 스킵 스캔은 WHERE 조건절의 검색에 사용할 수 있도록 용도가 넓어진 것이다.

 

예) 인덱스 스킵 스캔: OFF

 

실행 계획에서 type 컬럼이 "index" -> 인덱스 풀 스캔 (인덱스를 처음부터 끝까지 모두 읽음) 사용

• 인덱스 컬럼( gender, birth_date ) 만으로 처리할 수 있다.
• WHERE 조건절에 gender 컬럼에 대한 조건 없이 birth_date 컬럼의 비교 조건만 가진다.

위와 같은 이유로 ix_gender_birthdate 인덱스를 풀 스캔하여 처리한다. 즉, 인덱스를 효율적으로 사용할 수 없다.

만약 예제 쿼리가 employees 테이블의 모든 컬럼을 가져와야 했다면 테이블 풀 스캔을 실행했을 것이다.

 

예) 인덱스 스킵 스캔: ON

 

실행 계획에서 type 컬럼이 "range" -> 인덱스 레인지 스캔 (인덱스에서 필요한 부분만 읽음) 사용

Using where; Using index for skip scan -> ix_gender_birthdate 인덱스에 대해 인덱스 스킵 스캔을 활용해 데이터 조회

 

인덱스 스킵 스캔

 

MySQL 옵티마이저는 우선 gender 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gender 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.

1. gender 컬럼에 대해 가능한 값 2개('M', F')를 구한다.
2. 내부적으로 아래 2개의 쿼리를 실행하는 것과 비슷한 형태의 최적화를 실행한다.

mysql> SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date>='1965-02-01'; 
mysql> SELECT gender, birth_date FROM employees WHERE gender='F' AND birth_date>='1965-02-01';

-- // 기존 쿼리
mysql> SELECT gender, birth_date FROM employees WHERE birth_date>='1965-02-01';

 

참고: MySQL 서버는 인덱스를 루스 인덱스 스캔과 동일한 방식으로 읽으면서 인덱스에 존재하는 모든 값을 먼저 추출하고 그 결과를 이용해 인덱스 스킵 스캔을 실행한다.

 

인덱스 스킵 스캔의 단점

• WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 한다. (A, B, C)
• 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 한다. (커버링 인덱스)

첫 번째 조건은 쿼리 실행 계획의 비용과 관련된 부분인데, 만약 유니크한 값의 개수가 매우 많다면 옵티마이저는 인덱스에 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해진다. 그래서 쿼리 처리 성능이 오히려 더 느려질 수 있다.

그래서 인덱스 스킵 스캔은 인덱스의 선행 컬럼이 가진 유니크한 값의 개수가 소량일 때만 최적화를 적용할 수 있다.

 

두 번째 조건은 아래 쿼리를 통해 살펴보자.

 

 

WHERE 조건절은 동일하지만 SELECT 절에서 employees 테이블의 모든 컬럼을 조회하고 있다.

인덱스 컬럼 이외의 나머지 컬럼도 필요로 하기 때문에 인덱스 스킵 스캔을 사용하지 못하고 풀 테이블 스캔으로 실행 계획을 수립하였다. 그러나 이 제약 사항은 옵티마이저가 개선되면 충분히 해결될 수 있는 부분으로 보인다.

다중 컬럼(Multi-column) 인덱스

지금까지 살펴본 인덱스들은 모두 1개의 컬럼만 포함된 인덱스였다. 하지만 실제 서비스용 데이터베이스에서는 2개 이상의 컬럼을 포함하는 인덱스가 더 많이 사용된다.

두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스(또는 복함 컬럼 인덱스)라고 하며, 또한 2개 이상의 컬럼이 연결됐다고 해서 "Concatenated Index"라고도 한다.

 

다중 컬럼 인덱스 (dept_no, emp_no)

 

그림에서는 편의상 루트 노드를 생략했으나 실제로 데이터 레코드 건수가 작은 경우에는 브랜치 노드가 없는 경우도 있을 수 있다. 하지만 루트 노드와 리프 노드는 항상 존재한다.

그림은 다중 컬럼 인덱스일 때 각 인덱스를 구성하는 컬럼의 값이 어떻게 정렬되어 저장되는지 설명해준다. 중요한 것은 인덱스의 두 번째 걸럼은 첫 번째 컬럼에 의존해서 정렬돼 있다는 것이다. 즉, 두 번째 컬럼의 정렬은 첫 번째 걸럼이 똑같은 레코드에서만 의미가 있다. 위의 예제에서 emp_no 값의 정렬 순서가 빠르다고 해도 dept_no 컬럼의 정렬 순서가 늦다면 인덱스의 뒤쪽에 위치한다.

다중 컬럼 인덱스에서는 인덱스 내에서 각 컬럼의 위치(순서)가 상당히 중요하며, 그것을 신중히 결정해야 하는 이유다.

B-Tree 인덱스의 정렬 및 스캔 방향

인덱스의 정렬

일반적인 상용 DBMS에서는 인덱스를 생성하는 시점에 인덱스를 구성하는 각 컬럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다.

5.7 버전까지는 컬럼 단위로 정렬 순서를 혼합(ASC와 DESC 혼합)해서 인덱스를 생성할 수 없었다. 이런 문제점을 해결하기 위해 숫자 컬럼의 경우 -1을 곱한 값을 저장하는 우회 방법을 사용했었다.

8.0 버전부터는 다음과 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 되었다.

mysql> CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

인덱스 스캔 방향

인덱스 스캔 방향은 실제 인덱스가 오름차순인지 내림차순인지와는 관계없이 쿼리에 따라 옵티마이저가 설계한 실행 계획에 따라 결정된다.

 

first_name 컬럼에 대한 인덱스가 포함된 employees 테이블에 대해 다음 쿼리 실행

mysql> SELECT * FROM employees ORDER BY first_name DESC LIMIT 1;

 

MySQL은 위 쿼리를 실행하기 위해 인덱스를 처음부터 오름차순으로 읽어 first_name)이 가장 큰(오름차순으로 읽었을 때 가장 마지막 레코드) 값을 하나 가져오는 것일까?

그렇지 않다. 인덱스는 항상 오름차순으로만 정렬돼 있지만 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있고, 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 옵티마이저는 이미 알고 있다.

그래서 위 쿼리가 실행되면 인덱스를 역순으로 접근해 첫 번째 레코드만 읽는다.

 

인덱스의 오름차순(ASC)과 내림차순(DESC) 읽기

 

인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만, 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다. 

 

첫 번째 쿼리 실행 과정

1. first_name 컬럼에 정의된 인덱스를 이용해 "Anneke" 라는 레코드를 검색
2. 해당 인덱스를 정순으로 읽으며 4개의 레코드를 반환

두 번째 쿼리 실행 과정

1. first_name 컬럼에 정의된 인덱스를 역순으로 읽으며 처음 5개의 레코드를 반환

쿼리의 ORDER BY 처리나 MIN(), MAX() 함수 등의 최적화가 필요한 경우에도 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만든다.

 

내림차순 인덱스 (MySQL 8.0)

다음과 같이 2개 이상의 컬럼으로 구성된 복합 인덱스에서 각각의 컬럼이 내림차순과 오름차순이 혼합된 경우에는 8.0 버전의 내림차순 인덱스로만 해결될 수 있다.

mysql> CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

 

그렇다면 first_name 컬럼을 역순으로 정렬하는 요건만 있다면 다음 2개의 인덱스 중에서 어떤 것을 선택하는 것이 좋을까? 아니면 두 인덱스 모두 동일한 성능을 보일까?

mysql> CREATE INDEX ix_firstname_asc ON employees (first_name ASC);
mysql> CREATE INDEX ix_firstname_desc ON employees (first_name DESC);

 

인덱스 정순(포워드) 스캔과 인덱스 역순(백워드) 스캔

인덱스 정렬

• 오름차순 인덱스(Ascending index): 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스
• 내림차순 인덱스(Descending index): 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스

인덱스 스캔 방향

• 인덱스 정순 스캔(Forward index scan): 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔
• 인덱스 역순 스캔(Backward index scan): 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔

 

예) 내림차순 인덱스의 필요성 검증

 

t1 테이블을 풀 스캔하면서 정렬만 수행하는 쿼리를 다음과 같이 실행

-- // tid 컬럼의 값이 가장 큰 레코드 1건 반환
mysql> SELECT * FROM t1 ORDER BY tid ASC LIMIT 12619775,1;
1 row in set (4.15 sec)

-- // tid 컬럼의 값이 가장 작은 레코드 1건 반환
mysql> SELECT * FROM t1 ORDER BY tid DESC LIMIT 12619775,1;
1 row in set (5.35 sec)

 

두 쿼리는 테이블의 프라이머리 키를 정순 또는 역순으로 스캔하면서 마지막 레코드 1건만 반환한다.

그러나 LIMIT...OFFSET... 부분의 쿼리로 인해 실제로 MySQL 서버는 테이블의 모든 레코드를 스캔해야 한다.

역순 정렬 쿼리가 정순 정렬 쿼리보다 28.9% 더 시간이 걸리는 것을 확인할 수 있다. 인덱스를 "읽는 방향"에 따라 실행 시간의 차이가 발생하고 있다.

 

InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지(블록) 간의 양방향 연결고리(Double linked list)를 통해 전진(Forward)하느냐 후진(Backward)하느냐의 차이만 있지만, 실제 내부적으로는 InnoDB에서 인덱스 역순 스캔이 정순 스캔에 비해 느릴 수밖에 없는 다음 두 가지 이유가 있다.

• 페이지 잠금이 인덱스 정순 스캔(Forward index scan)에 적합한 구조
• 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

InnoDB 페이지 내에서 레코드 단방향 연결

 

참고: 그림에서는 페이지 내부의 레코드들이 정렬 순서대로 저장된 것처럼 표시됐지만 실제로 InnoDB 페이지는 힙(Heap)처럼 사용되기 때문에 물리적으로 저장이 순서대로 배치되지는 않는다. 그리고 각 데이터 페이지(InnoDB 스토리지 엔진에서 데이터 파일은 프라이머리 키 인덱스 자체)나 인덱스 페이지의 인덱스(데이터 레코드 또는 인덱스 키)는 키 값과 데이터를 가지는데, 인덱스(프라이머리 키 인덱스와 세컨더리 인덱스 모두)의 루트 노드 또는 브랜치 노드라면 자식 노드의 주소를 가진다. 프라이머리 키에서 리프 노드의 "데이터"는 실제 레코드의 컬럼 값들이며, 세컨더리 인덱스 페이지에서는 프라이머리 키 값을 가진다.

 

서비스 요건에 맞게 인덱스의 정렬 순서 선택하기

일반적으로 인덱스를 ORDER BY ... DESC  하는 쿼리가 소량의 레코드에 드물게 실행되는 경우라면 내림차순 인덱스를 굳이 고려할 필요는 없다.

 

mysql> SELECT * FROM tab WHERE userid=? ORDER BY score DESC LIMIT 10;

 

이 쿼리의 경우 다음 두 가지 인덱스 모두 적절한 선택이 될 수 있다.

• 오름차순 인덱스: INDEX ( userid ASC, score ASC )
• 내림차순 인덱스: INDEX ( userid DESC, score DESC )

하지만 이 쿼리가 많은 레코드를 조회하면서 빈번하게 실행된다면 오름차순 인덱스보다 내림차순 인덱스가 더 효율적이라고 볼 수 있다. -> 실행 범위와 빈도를 고려

 

또한 많은 쿼리가 인덱스의 앞쪽 또는 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는 데 도움이 될 것이다.

B-Tree 인덱스의 가용성과 효용성

쿼리의 WHERE 조건이나 GROUP BY, ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다. 그래야만 쿼리의 조건을 최적화하거나, 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.

비교 조건의 종류의 효율성

다중 컬럼 인덱스에서 각 컬럼의 순서와 그 컬럼에 사용된 조건이 동등 비교인지("=")인지, 아니면 크다(">") 와 작다("<") 같은 범위 조건인지에 따라 각 인덱스 컬럼의 활용 형태와 효율이 달라진다.

mysql> SELECT * FROM dept_emp WHERE dept_no='d002' AND emp_no>=10144;

이 쿼리를 위해 dept_emp 테이블에 컬럼의 순서만 다른 다음과 같은 인덱스를 생성했다고 가정하자.

• 케이스 A: INDEX (dept_no, emp_no)
• 케이스 B: INDEX (emp_no, dept_no)

케이스 A 인덱스를 이용한 쿼리 실행 과정

1. dept_no = 'd002' AND emp_no >= 10144 인 레코드를 찾는다.
2. dept_no != 'd002' 일 때까지 인덱스를 쭉 읽는다.

이 경우 읽은 레코드 모두가 사용자가 원하는 결과이다. 즉, 조건을 만족하는 레코드가 5건이라고 할 때, 5건의 레코드를 찾는 데 꼭 필요한 5번의 비교 작업만 수행한 것이므로 상당히 효율적으로 인덱스를 이용한 것이다.

 

케이스 B 인덱스를 이용한 쿼리 실행 과정

1. emp_no >= 10144 AND dept_no = 'd002' 인 레코드를 찾는다.
2. 모든 레코드에 대해 dept_no = 'd002' 조건을 만족하는지 비교를 수행한다.

이 경우 5건의 레코드를 찾기 위해 7번의 비교 과정을 거친다. 이처럼 인덱스를 통해 읽은 레코드가 나머지 조건에 맞는지 비교하면서 취사선택하는 작업을 '필터링'이라고 한다.

 

인덱스 컬럼 순서로 인한 쿼리 실행 내역의 차이

 

 

다중 컬럼 인덱스의 정렬 방식(인덱스의 N번째 키 값은 N-1번째 키 값에 대해서 다시 정렬됨) 때문이다. 케이스 A 인덱스의 2번째 컬럼인 emp_no는 비교 작업의 범위를 좁히는데 도움을 준다. 하지만 케이스 B 인덱스에서 2번째 컬럼인 dept_no는 비교 작업의 범위를 좁히는 데 아무런 도움을 주지 못하고, 쿼리의 조건에 맞는지 검사하는 용도로만 사용했다.

 

작업 범위 결정 조건

• 케이스 A 인덱스: dept_no = 'd002' 와 emp_no >= 10144
• 케이스 B 인덱스: emp_no >= 10144

필터링 조건(또는 체크 조건)

비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건

• 케이스 B 인덱스: dept_no = 'd002'

작업 범위 결정 조건이 많아질수록 쿼리의 성능을 높이지만 체크 조건은 많다고 해서 (최종적으로 가져오는 레코드는 작게 만들지 몰라도) 쿼리의 성능을 높이지는 못한다. 오히려 쿼리 성능을 더 느리게 만들 때가 많다. 

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서(Left-most) 오른쪽 값이 정렬된 것이다. 여기서 왼쪽이란 하나의 컬럼 뿐만 아니라 다중 컬럼 인덱스에 대해서도 함께 적용된다.

• 케이스 A: INDEX (first_name)
• 케이스 B: INDEX (dept_no, emp_no)

 

왼쪽 값(Left-most)을 기준으로 정렬

 

인덱스 키 값의 정렬만 표현하지만 사실은 인덱스 키 값의 이런 정렬 특성은 빠른 검색의 전제 조건이다. 즉, 하나의 컬럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다. 또한 다중 컬럼 인덱스에서도 왼쪽 컬럼의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없다.

 

케이스 A  인덱스가 지정된 employees 테이블에 대해 다음 쿼리 실행

mysql> SELECT * FROM employees WHERE first_name LIKE '%mer';

 

이 쿼리는 인덱스 레인지 스캔 방식으로 인덱스를 이용할 수는 없다. first_name 컬럼에 저장된 값의 왼쪽부터 한 글자씩 비교해가면서 일치하는 레코드를 찾아야 하는데, 조건절에 주어진 상숫값('%mer')에는 왼쪽 부분이 고정되지 않았기 때문이다. 

따라서 정렬 우선순위가 낮은 뒷부분 값만으로는 왼쪽 기준(Left-most) 정렬 기반의 인덱스인 B-Tree 에서는 인덱스의 효과를 얻을 수 없다.

 

케이스 B 인덱스가 지정된 dept_emp 테이블에 대해 다음 쿼리 실행

mysql> SELECT * FROM dept_emp WHERE emp_no >= 10144;

인덱스가 (dept_no, emp_no) 컬럼 순서대로 생성됐다면, 인덱스의 선행 컬럼인 dept_no 조건 없이 emp_no 값으로만 검색하면 인덱스를 효율적으로 사용할 수 없다.

케이스 B 인덱스는 다중 컬럼으로 구성된 인덱스이므로 dept_no 컬럼에 대해 먼저 정렬한 후, 다시 emp_no 컬럼 값으로 정렬되어 있기 때문이다.

 

참고: 인덱스 왼쪽 값 기준 규칙은 WHERE 조건절 뿐만 아니라 GROUP BY 절이나 ORDER BY 절에도 똑같이 적용된다.

가용성과 효율성 판단

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

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

• .. WHERE column <> 'N'
• .. WHERE column NOT IN (10, 11, 12)
• .. WHERE column IS NOT NULL

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

• .. WHERE column LIKE '%승환'
• .. WHERE column LIKE '_승환'
• .. WHERE column LIKE '%승%'

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

• .. WHERE SUBSTRING(column,1,1) = 'X'
• .. WHERE DAYOFMONTH(column) = 1

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

• .. WHERE column = deterministic_function()

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

• .. WHERE char_coloumn = 10
• 15장 '데이터 타입' 참조

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

• .. WHERE utf9_bin_char_column = enckr_bin_char_column
• 15.1.4절 '콜레이션(Collation)' 참조

다른 일반적인 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL에서는 NULL 값도 인덱스에 저장된다.

다음과 같은 WHERE 조건도 작업 범위 결정 조건으로 인덱스를 사용한다.

mysql> .. WHERE column IS NULL ..

 

다중 컬럼 인덱스의 사용 조건

다음과 같은 인덱스를 가정

INDEX ix_test ( column_1, column_2, column_3, .. , column_n )

 

작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우

• column_1 컬럼에 대한 조건이 없는 경우
• column_1 컬럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우

 

작업 범위 결정 조건으로 인덱스를 사용하는 경우 (i는 2보다 크고 n보다 작은 임의의 값을 의미)

• column_1 ~ column_(i-1) 컬럼까지 동등 비교 형태("=", "IN")로 비교
• column_i 컬럼에 대해 다음 연산자 중 하나로 비교
  • 동등 비교("=", "IN")
  • 크다 작다 형태("<", ">")
  • LIKE 로 좌측 일치 패턴(LIKE '승환%')

위 두 가지 조건을 모두 만족하는 쿼리는 column_1 부터 column_i 까지는 작업 범위 결정 조건으로 사용되고, column_(i+1) 부터 column_n 까지의 조건은 체크 조건으로 사용된다.

 

예) 인덱스를 사용하는 쿼리의 조건

 

작업 범위 결정 조건으로 인덱스를 사용하는 쿼리 패턴은 이 밖에도 많겠지만, 대표적인 것을 기억해두면 효율적인 쿼리를 쉽게 작성할 수 있다. 또한 여기서 설명한 내용은 모두 B-Tree 인덱스의 특징이므로 MySQL 뿐만 아니라 대부분의 RDBMS 에도 동일하게 적용된다.