[스터디] Real MySQL - 8장 인덱스 (1/2)

Glen·2024년 6월 14일

mysql

RealMySQL-스터디

목록 보기

5/6

디스크 읽기 방식

인덱스에 의존적인 용어는 아니지만, 자주 언급되는 랜덤 I/O, 순차 I/O와 같은 디스크 읽기 방식을 간단히 알아보고 인덱스를 살펴보자.

데이터 저장 매체는 컴퓨터에서 가장 느린 부분이고, 데이터베이스의 성능 튜닝은 결국 디스크 I/O를 줄이느냐가 관건일때가 상당히 많다.

HDD와 SSD

컴퓨터에서 CPU나 메모리 같은 주요 장치는 전자식 장치지만, 하드 디스크 드라이브는 기계식 장치이다.

따라서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다.

이러한 기계식 하드 디스크를 대체하기 위해 전자식 저장 매체인 SSD가 주로 DBMS에 사용된다.

HDD가 초당 60개의 트랜잭션을 처리할 동안 SSD는 초당 436개의 트랜잭션을 처리했다고 한다.

랜덤 I/O와 순차 I/O

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

하지만 이 둘의 차이는 시스템 콜을 얼마나 호출하냐의 차이인데, 예를 들어 3개의 페이지를 기록하는 것을 순차 I/O를 사용할 경우 시스템 콜을 한 번 요청하지만, 랜덤 I/O의 경우 3번 시스템 콜을 요청해야 한다.

즉, 순차 I/O가 랜덤 I/O에 비해 N배 빠르다고 볼 수 있다.

더 정확히는 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐 이다.

따라서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 크다.

데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.

SSD 또한 랜덤 I/O가 순차 I/O보다 느리다.

쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔 실행할 방법은 그다지 많지 않고, 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적이라고 할 수 있다.

이는 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.

인덱스란?

많은 사람들이 인덱스를 언급할 때 책의 맨 끝에 있는 찾아보기(색인)로 설명한다.

책의 마지막에 있는 찾아보기가 인덱스에 비유된다면 책의 내용은 데이터 파일에 해당한다고 볼 수 있다.

책의 찾아보기를 통해 알아낼 수 있는 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 비유될 것이다.

DBMS 또한 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸리기 때문에 칼럼(또는 칼럼들)의 값과 해당 레코드가 저장된 주소를 Key-Value로 인게스를 만들어 둔다.

또한 인덱스는 중요한 것이 정렬이 된 상태를 유지해야 한다.

때문에 인덱스가 많은 테이블은 INSERT, DELETE, UPDATE 와 같은 쿼리의 처리가 느려진다.

즉 인덱스는 INSERT, UPDATE, DELETE의 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다.

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

인덱스를 역할별로 분리한다면 프라이머리 키, 세컨더리 인덱스로 구분할 수 있다.

프라이머리 키

프라이머리 키는 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스를 의미한다.

프라이머리 키는 테이블에서 해당 레코드를 식별하는 기준값이 되기 때문에 식별자라고 부르고, NULL 값을 허용하지 않으며 중복 또한 허용하지 않는다.

세컨더리 인덱스

프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 인덱스로 분류한다.

유니크 인덱스는 프라이머리 키와 성격이 비슷하고 프라이머리 키를 대체해서 사용할 수 있다고 해서 대체 키라고도 하는데, 별도로 분류하기도 하고, 그냥 세컨더리 인덱스로 분류하기도 한다.

인덱스를 알고리즘 별로 분류한다면 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있고, 최근에는 Fractal-Tree(TokuDB) 인덱스나 로그 기반의 Merge-Tree 인덱스와 같은 알고리즘을 사용하는 DBMS도 개발되고 있다.

B-Tree

B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘으로, 상당히 오래전부터 도입됐으며 그만큼 성숙하다.

B-Tree 인덱스는 컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱한다.

그 외 위치 기반 검색을 지원하기 위한 R-Tree 알고리즘도 있지만, R-Tree 인덱스는 B-Tree 알고리즘의 응용으로 볼 수 있다.

Hash 인덱스

Hash 인덱스 알고리즘은 칼럼의 값으로 해시값을 계산하여 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.

하지만 값을 변형해서 인덱싱하므로 전방(Prefix) 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.

Hash 인덱스는 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

데이터의 중복 허용 여부로 분류하면 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있고, 인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지 1개 이상 존재 하는지를 의미하지만, 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제가 된다.

유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1개의 레코드만 찾으면 더 찾지 않아도 된다는 것과 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.

인덱스의 기능별로 분류하면 전문 검색용 인덱스와 공간 검색용 인덱스 등을 예로 들 수 있다.

물론 이 밖에 여러 인덱스도 존재하지만, MySQL을 사용할 때는 이 두가지만으로도 충분하다.

B-Tree 인덱스

B-Tree는 데이터베이스의 인덱스 알고리즘 가운데 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘이다.

또한 아직도 범용적으로 사용되는 인덱스 알고리즘이다.

B-Tree에는 여러 변형된 형태의 알고리즘이 잇는데, 일반적으로 B+Tree, B*Tree 가 사용된다.

참고로 B-Tree의 B는 Binary가 아닌 Balanced를 의미한다.

구조 및 특성

B-Tree는 트리 구조로 최상위에 하나의 루트 노드가 존재하고, 그 하위에 자식 노드가 붙어 있는 형태다.

트리 구조의 가장 하위에 있는 노드를 리프 노드라고 하고, 트리 노드도 아니고 리프 노드도 아닌 중간의 노드를 브랜치 노드라고 한다.

인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.

인덱스의 키 값은 항상 정렬된 형태를 따르지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다.

또한 데이터 파일의 레코드는 INSERT 순서로 저장되지 않고, 레코드가 삭제되어 빈 공간이 생겼을 때 해당 공간에 삽입될 수 있기에 항상 INSERT 순서대로 저장되는 것은 아니다.

하지만 이는 범용적인 DBMS의 이야기이고, InnoDB 스토리지 엔진을 사용한다면 데이터 파일의 레코드는 기본적으로 PK 기준 정렬되어 저장된다.

인덱스는 테이블의 키 컬럼만 가지고 있으므로 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다.

따라서 인덱스를 통해 레코드를 읽을 때는 데이터 파일에 바로 접근할 수 없고, 인덱스를 통해 얻은 프라이머리 키 값을 이용해 데이터 파일에 접근하여 레코드를 읽는다.

이를 반대로 생각하면 select 쿼리가 인덱스를 타고 찾으려는 컬럼이 모두 인덱스에 속하면 인덱스만 조회하면 된다. 이를 커버링 인덱스라고 한다.

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

인덱스 키 추가

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

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

리프 노드가 꽉 차서 저장할 수 없을 때는 리프 노드가 분리돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어진다.

따라서 B-Tree는 상대적으로 쓰기 작업에 비용이 많이 든다.

인덱스 추가로 인해 발생하는 오버헤드는 일반적으로 레코드 작업 비용이 1이라고 가정 할 때, 1.5 정도로 예측하는 것이다.

만약 인덱스가 3개인 경우 5.5(1.5 * 3 + 1) 정도로 예측한다.

중요한 것은 이 비용이 CPU와 메모리 상에서 처리 하는 비용이 아니라, 디스크로 부터 페이지를 읽고 쓰기를 해야하기 때문에 느린 작업이 될 수 있다.

하지만 InnoDB의 경우 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있도록 한다.

체인지 버퍼가 이 역할을 한다.

하지만 프라이머리 키 또는 유니크 인덱스의 경우 중복 체크가 필요하기에 즉시 B-Tree에 추가하거나 삭제해야 한다.

내림차순 인덱스 또한 체인지 버퍼를 활용할 수 없다.

인덱스 키 삭제

B-Tree의 키 값이 삭제되는 경우는 간단한데, 해당 키 값이 저장된 B-Tree 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다.

이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있다.

인덱스 키 삭제로 인한 마킹 작업 또는 디스크 쓰기가 필요하므로 이 작업 역시 디스크 I/O가 발생한다.

이 작업 또한 체인지 버퍼를 통해 최적화되어 처리 된다.

인덱스 키 변경

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

따라서 단순히 키 값을 삭제한 후, 다시 새로운 키를 추가하는 형태로 처리된다.

따라서 이 작업 또한 체인지 버퍼를 통해 최적화되어 처리 된다.

인덱스 키 검색

INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 오버헤드를 감당하며 인덱스를 구축하는 이유는 바로 빠른 검색을 위해서이다.

인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해서 브랜치 노드를 거쳐 최종으로 리프 노드까지 이동하며 비교 작업을 수행하는데 이를 트리 탐색이라고 한다.

인덱스 트리 탐색은 SELECT 뿐 아니라 UPDATE, DELETE를 처리하기 위해 항상 레코드를 먼저 검색해야 할 경우에도 사용된다.

B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분(glen%)만 일치하는 경우에만 사용할 수 있다.

부등호(<, >) 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색(%glen)하는 용도로는 인덱스를 사용할 수 없다.

또한 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 인덱스 사용이 불가하다.

또한 InnoDB 스토리지 엔진에서 인덱스는 레코드 잠금이나 넥스트 키 락이 검색을 수행한 인덱스를 잠금 후 테이블의 레코드를 잠구는 방식으로 구현되어 있기에 인덱스가 없다면 불필요하게 많은 테이블을 잠근다.

따라서 InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다.

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

B-Tree 인덱스는 인덱스를 구성하는 컬럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향 받는다.

인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 단위가 된다.

또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다.

따라서 인덱스도 페이지 단위로 관리된다.

루트, 브랜치, 리프 노드가 페이지 단위로 관리된다는 말이다.

그렇다면 페이지에 얼마 만큼의 인덱스가 저장될까?

페이지의 기본 크기는 16KB 이기 때문에 인덱스의 키가 16바이트라고 가정하고, 자식 노드 주소를 12바이트로 가정하면 (16 * 1024) / (16 + 12) = 585개의 인덱스를 저장할 수 있다.

만약 인덱스 키가 32바이트로 늘어났다고 가정하면 372개가 저장된다.

이는 SELECT 쿼리가 한 번에 500개를 읽어야 한다면 인덱스 키가 16바이트 일때는 페이지를 한 번 읽으면 되지만, 인덱스 키가 32바이트라면 페이지를 두 번 읽어야 한다.

즉 인덱스의 키 값이 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미한다.

또한 인덱스의 키 값이 커지면 버퍼 풀에 캐시해 둘 수 있는 크기가 줄어들고 결국 성능이 저하되는 결과를 가져온다.

B-Tree 깊이

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

하지만 B-Tree의 탐색 시 시간 복잡도는 O(logN)이고 데이터가 아무리 많아도 깊이가 5단계 이상 깊어지는 경우가 흔치 않다.

따라서 크게 신경은 쓰지 않아도 되지만, 최대한 인덱스 키의 크기는 작게 만드는 것이 좋다.

선택도(기수성)

주로 카디널리티라고 하며 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다.

전체 인덱스 키 값은 100개인데, 그중에서 유니크한 값의 수는 10개라면 카디널리티는 10이다.

인덱스 키 값 가운데 중복된 값이 많아지면 카디널리티는 낮아지고, 동시에 선택도 또한 떨어진다.

인덱스는 카디널리티가 높을수록 검색 대상이 줄어들기 때문에 카디널리티가 높은 대상을 인덱스로 잡아야 한다.

예를 들어 복합 인덱스를 설정할 때, 카디널리티가 높은 순서대로 복합 인덱스를 설정하는 것이 좋다.
하지만 복합 인덱스의 첫 요소가 인덱스로 자주 사용된다면 카디널리티를 무시하고 설정하는게 좋을 것 같다.

읽어야 하는 레코드의 건수

인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다.

예를 들어 100건의 데이터가 있을 때 50건의 데이터를 인덱스를 통해 읽을지, 100건을 모두 읽어 사용하지 않는 50건을 버릴 지 판단을 해야한다.

DBMS 별 차이가 있긴 하지만, 대략 인덱스를 통해 레코드 1건을 읽는 비용은 단순 읽는 것에 비해 4~5배 정도 비용이 드는 작업으로 예측한다.

즉, 인덱스를 통해 읽어야 할 작업이 전체 레코드의 20~25% 이상이라면 풀 테이블 스캔이 더 효율적이다.

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

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

인덱스 레인지 스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 중 가장 대표적인 접근 방식으로, 뒤에서 설명할 나머지 접근 방식보다 빠른 방법이다.

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

444 페이지에서 설명하는데, const, ref, range를 의미한다.

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

루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어간 뒤 시작 지점을 알 수 있다.

시작 지점을 찾으면 그때부터는 리프 노드의 레코드만 순서대로 읽으면 된다.

만약 리프 노드 페이지의 끝이라면 노드 간 링크를 이용해 다음 리프노드를 찾아서 다시 스캔한다.

그리고 스캔을 멈춰야 할 위치에 도달하면 지금까지 읽은 결과를 반환한다.

위 과정은 인덱스만 조회하는 과정이고, 읽은 결과로 실제 데이터 파일을 조회하는 경우 읽은 결과 만큼의 랜덤 I/O가 발생한다.

물론 버퍼 풀이 존재하기에 개수 만큼의 랜덤 I/O가 발생하지는 않을 것 이다.

따라서 인덱스를 통해 데이터를 읽는 작업은 비용이 많이 드는 작업으로 분류된다.

하지만 인덱스를 통해 실제 데이터를 조회하는 과정은 필요하지 않는 경우도 있는데, 조회하려는 컬럼이 인덱스에 모두 포함되어 있다면 굳이 데이터를 읽을 필요가 없다.

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

그 외 Handler 변수의 값은 4장 아키텍쳐에서 설명했으니 생략한다.

인덱스 풀 스캔

인덱스 레인지 스캔과 마찬가지로 인덱스를 사용하지만 인덱스 레인지 스캔과 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.

인덱스 풀 스캔은 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우 인덱스 풀 스캔이 사용된다.

하지만 인덱스 컬럼 뿐 아닌 데이터 레코드까지 읽어야 한다면 인덱스 풀 스캔이 사용되지 않는다.

인덱스 풀 스캔은 풀 테이블 스캔보다 적은 디스크 I/O를 소모하므로 효율적이지만, 인덱스 레인지 스캔보다 효율적인 방식은 아니다.

따라서 인덱스 풀 스캔을 사용하는 방법은 인덱스를 사용한다라고 볼 수 없다.

루스 인덱스 스캔

루스(Loose) 인덱스 스캔은 오라클의 인덱스 스킵 스캔이라고 하는 기능과 작동 방식은 비슷하지만 MySQL에서는 이를 루스 인덱스 스캔이라고 한다.

MySQL 5.7 이전까지는 루스 인덱스 스캔 기능이 많이 제한적이었지만, MySQL 8.0 버전부터는 다른 상용 DBMS에서 지원하는 인덱스 스킵 스캔과 같은 최적화를 조금씩 지원하기 시작했다.

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

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

예를 들어 select member_id, min(money) from purchase where member_id between 5 and 10 group by member_id 라는 쿼리가 있고, member_id와 money에 복합 인덱스가 있을 때 리프 노드 탐색 시 가장 첫 번째 레코드만 읽고 나머지 레코드는 읽을 필요가 없다.
(member_id, money 오름차순 정렬되어 있으므로)

따라서 그 뒤 레코드는 무시하고 다음 member_id가 올때까지 건너띄면 빠르게 처리를 할 수 있다.

인덱스 스킵 스캔

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

예를 들어, gender, birth_date 컬럼에 대해 복합 인덱스가 설정되어 있을때 다음과 같은 쿼리만 인덱스를 사용한다.

... where gender = 'M', ... where gender = 'M' and birth_date >= '2077-06-30'

하지만 다음 쿼리는 인덱스를 사용하지 못한다.

... where birth_date >= '2077-06-30'

해당 쿼리는 위에서 설명한 인덱스 풀 스캔처럼 동작하거나 풀 테이블 스캔이 사용될 것 이다.

하지만 MySQL 8.0 이후 도입된 인덱스 스킵 스캔을 사용하면 인덱스 레인지 스캔을 사용할 수 있다.

인덱스 스킵 스캔을 사용할 수 있는 조건은 WHERE 조건에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값이 적어야 하고, 커버링 인덱스여야 한다.

만약 선행 컬럼의 개수가 많다면 인덱스 스킵 스캔이 비효율적이기 때문에 인덱스 풀 스캔 또는 풀 테이블 스캔이 사용된다.

다중 컬럼 인덱스

인덱스는 하나의 컬럼으로 사용할 수 있는게 아니라, 다중 컬럼으로도 사용할 수 있다.

이러한 인덱스를 다중 컬럼 인덱스 또는 복합 컬럼 인덱스라고 한다.

다중 컬럼 인덱스는 첫 번째 컬럼이 우선 정렬되고, 두 번째 컬럼이 첫 번째 컬럼에 의존해 정렬돼 있다. (그 뒤에도 마찬가지)

즉 인덱스를 통해 조건을 검색할 때, 첫 번째 컬럼이 아닌 두 번째 컬럼을 통해 검색한다면 인덱스를 통한 검색을 사용할 수 없다.

따라서 복합 인덱스를 설정할 때는 순서를 신중하게 결정해야 한다.

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

인덱스를 생성할 때 설정한 정렬 규칙에 의해 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다.

하지만 어떤 인덱스가 오름차순으로 정렬됐다고 해서 그 인덱스를 오름차순으로만 읽을 수 있다는 뜻은 아니다.

인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다.

인덱스의 정렬

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

하지만 MySQL 5.7 까지는 칼럼 단위로 정렬 순서를 혼합할 수 없었지만, MySQL 8.0 버전부터는 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다.

인덱스 스캔 방향

오름차순으로 설정된 인덱스에 대해 조회하는 쿼리가 있다.

select ... order by name desc limit 1

이 쿼리를 실행하면 모든 인덱스를 끝까지 읽어 마지막 하나를 조회하는 것이 아니다.

옵티마이저는 오름차순으로 정렬된 인덱스를 최대값부터 거꾸로 읽으면 되는 것을 알고 있다.

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

따라서 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.

내림차순 인덱스

MySQL에서 정렬된 값을 조회할 때 인덱스가 실제 오름차순인지 내림차순인지 관계없이 읽는 순서만 변경하여 해결할 수 있다.

물론 복합 인덱스에서 정렬 조건이 혼합된 경우엔 내림차순 인덱스를 직접 사용해야 효과를 얻을 수 있다.

하지만 단일 인덱스의 경우 역순으로 정렬하는 요건만 있을 때, 오름차순 인덱스만 사용할 지, 내림차순 인덱스만 사용할 지 고민이 들 것이다.

정답을 바로 말하자면 역순으로 정렬되는 요건이 대부분이라면 내림차순 인덱스를 사용하는게 효율적이다.

이유는 첫 번째로 InnoDB 스토리지 엔진이 Forward index scan에 적합한 구조이기 때문이고, 두 번째로 페이지 내 인덱스 레코드는 단방향으로 연결된 구조이기 때문이라고 한다.

더 자세한 내용은 저자분이 작성하신 글을 참조하면 좋을 듯

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

쿼리의 WHERE 조건이나 GROUP BY, 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야 한다.

그래야만 쿼리의 조건을 최적화하거나, 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.

비교 조건의 종류와 효율성

다중 컬럼 인덱스에서 각 컬럼의 순서와 그 컬럼에 사용된 조건이 동등 비교 또는 범위 조건인지에 따라 각 인덱스 컬럼의 활용 형태가 달라지며, 그 효율 또한 달라진다.

다음과 같은 쿼리가 있다.

select * from dept_emp where dept_no = 'd002' and emp_no >= 10114

그리고 해당 쿼리를 다음과 같은 인덱스로 각 처리했을 때 결과는 어떨까?

dept_no, emp_no
emp_no, dept_no

1번은 dept_no = d002, emp_no = 10114인 레코드를 찾고, 그 이후 레코드가 dept_no=d002가 아닐때까지 쭉 읽기만 하면 된다.

하지만 2번의 경우 dept_no = d002, emp_no = 10114인 레코드를 찾고, 그 이후 레코드가 dept_no=d002 인지 비교하는 과정을 거치면서 계속 읽어야 한다.

공식적인 명칭은 아니지만 이러한 1번 조건을 작업 범위 결정 조건이라고 하고, 2번 조건을 필터링 조건(체크 조건)이라고 한다.

작업 범위 결정 조건은 많으면 많을수록 쿼리의 성능을 높이지만, 체크 조건은 많으면 많을 수록 쿼리의 성능을 떨어트린다.

인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다.

이는 다중 컬럼 인덱스 또한 마찬가지이다.

예를 들어 다음과 같은 쿼리는 인덱스를 사용할 수 없다.

index (name)
select ... where name like %glen
(값의 왼쪽 부분이 없기에 인덱스 사용 불가)

index (dept_no, emp_no)
select ... where emp_no >= 4885
(첫 인덱스 기준 정렬되어 있기에 인덱스 사용 불가)

가용성과 효율성 판단

B-Tree 인덱스 특성 상 다음 조건은 사용할 수 없다.

여기서 사용할 수 없다는 것은 범위 결정 조건으로 사용할 수 없다는 것을 의미하며, 경우에 따라 체크 조건으로 인덱스를 사용할 수는 있다.

NOT-EQUAL로 비교된 경우
- WHERE column <> 'N'
- WHERE column NOT IN (10, 11, 12)
- WHERE column IS NOT NULL
LIKE '%??'
- WHERE column like '%glen'
- WHERE column like '_glen'
- WHERE column like '%glen%'
스토어드 함수 또는 인덱스 컬럼이 비교된 경우
- WHERE SUBSTRING(column, 1, 1) = 'X'
- WHERE DAYOFMONTH(column) = 1
NOT-DETERMINSTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
- WHERE column = deterministic_function()
데이터 타입이 서로 다른 비교
- WHERE char_column = 10
문자열 데이터 타입의 콜레이션이 다른 경우
- WHERE utf8_bin_char_column = euckr_bin_char_column

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

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

WHERE column IS NULL

다중 컬럼으로 만들어진 인덱스는 어떤 조건에서 사용될 수 있고, 어떤 경우에는 절대 사용할 수 없다.

index (col_1, col_2, col_3 ... col_n)

작업 범위 결정 조건으로 사용할 수 있는 경우
- 각 컬럼에 대해 동등 비교 형태(= , IN)
- 두 번째 컬럼에 대해 다음 연산자 중 하나로 비교
  - 동등 비교(= , IN)
  - 크다 작다 형태 (> , <)
  - LIKE로 좌측 일치 패턴 (LIKE 'glen%')
작업 범위 결정 조건으로 사용할 수 없는 경우
- 첫 컬럼에 대한 조건이 없는 경우
- 첫 컬럼의 비교 조건이 위에서 나온 사용 불가 조건 중 하나인 경우