[Real MySQL 8.0]8장 - 인덱스와 B-Tree (1/2)

 

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분입니다. 이번 장에서는 MySQL 쿼리의 개발이나 튜닝을 설명하기 전에 인덱스의 종류와 특성을 알아보겠습니다.

 

📝8.1 디스크 읽기 방식

인데스에서만 사용하는 용어는 아니지만 이번 장에서는 랜덤 I/O와 순차 I/O 같은 디스크 일기 방식부터 알아봅시다.

 

컴퓨터의 CPU나 메모리처럼 전기적 특성을 지닌 장치는 매우 빠른 속도로 발전했지만 디스크 같은 기계식 장치의 성능은 상당히 제한적으로 발전했습니다. 데이터베이스나 쿼리 튜닝에 어느 정도 지식을 갖춘 사용자가 절감하고 있듯이 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건입니다.

 

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

컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치지만 하드 디스크의 경우는 기계식 장치입니다.

그래서 항상 하드디스크 부분에서 데이터베이스 서버가 병목됩니다. 이러한 기계식 하드 디스크 드라이브 대체를 위해 전자식 매체인 SSD가 많이 출시되고 있습니다.  

 

SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터를 제거하고 그 대신 플래시 메모리를 장착하고 있습니다.

HDD 대비 SSD의 장점

• 디스크 원판을 기계적으로 회전할 필요가 없어 데이터를 빨리 읽음
• 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않음

 

디스크 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O의 경우 SSD가 HDD보다 조금 빠르거나 거의 비슷한 성능을 보이지만 랜덤 I/O에서는 훨씬 빠른 성능을 보인다. 데이터베이스 서버에서는 순차 I/O 작업이 그다지 비중이 크지 않기에 DBMS용 스토리지에 최적이라고 볼 수 있다.

 

🧷8.1.2 랜덤 I/O와 순차 I/O

랜덤 I/O 라는 표현은 사실 HDD의 플래터를 돌려 읽어야할 데이터가 저장된 위치를 읽는 것을 의미한다. 처음 시작하는 헤더의 위치가 랜덤이기에 이러한 이름이 붙은 것이다. 사실 순차 I/O도 이 과정도 같다 그렇다면 차이가 무엇일까?

순차 I/O와 랜덤 I/O의 차이는 가져올 데이터의 규칙성이다. 

• 순차 I/O : 순차 I/O는 디스크 헤드를 1번 움직이며 서로 붙어있는 데이터들을 읽는다.
• 랜덤 I/O : 랜덤 I/O는 디스크 헤드를 여러 번 움직여 데이터들을 가져온다.

여기서 SSD는 디스크 원판이 없기에 랜덤 I/O와 순차 I/O의 차이가 거의 없을 것이라고 한것이다. 하지만 실제로는 순차 I/O가 더 빠르다.(데이터가 연결되어있기에 오버헤드가 랜덤 I/O보다 적다)

 

사실 쿼리 튜닝에서 랜덤 I/O를 순차 I/O로 바꿔 실행 하는 방법은 많이 없어 랜덤 I/O를 줄이는 방향으로 간 것이다.

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다.
그래서 큰 테이블의 레코드 대부분은 읽기 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 이는 순차 I/O가 랜덤 I/O보다 훨씬 빠리 많은 레코드를 읽을 수 있기 때문이다.
-> 즉, 범위가 매우 큰 데이터를 읽을 때는 인덱스를 쓰지 않고 풀 테이블 스캔이 더 빠르고, 조건 검색의 경우 인덱스를 사용합니다.

 

📝8.2 인덱스란?

인덱스를 언급할 때 사람들은 항상 책 맨 끝에 있는 색인으로 설명합니다. 

책의 마지막에 있는 색인이 인덱스라면 책의 내용은 데이터 파일에 해당합니다. 책의 색인을 통해 알아낼 수 있는 것은 페이지 번호이며 이것을 통해 데이터 파일에 접근하는 것이지요.

 

생각해봅시다. 목차, 색인 없이 오직 책에서 원하는 정보를 찾으려면 매우 오래걸립니다. 이 과정을 손쉽게 만들어주는 것이 흔히 말하는 목차인 것입니다. 그래서 인덱스는 칼럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼는 것입니다.

그리고 또다른 목차와 공통점은 바로 DBMS의 인덱스도 정렬이라는 것입니다. 목차는 내용이 많아지면 우리가 원하는 검색어를 찾는데도 시간이 걸릴 것입니다. 그래서 최대한 빠르게 찾을 수 있게 특정 규칙으로 정렬되어 있는 것입니다.

 

그렇다면 인덱스의 장단점은 무엇일까요?

인덱스의 장단점

• 장점
  • 이미 정렬돼 있어 원하는 값을 아주 빨리 찾아올 수 있습니다.
• 단점
  • 항상 정렬되어 있는 구조를 하기 때문에 저장하는 과정이 복잡하고 느립니다.
  • 인덱스 자체가 이미 하나의 자료 구조를 추가하는 행위입니다.  저장 공간을 따로 사용합니다.

 

위의 장단점으로 본다면 우리가 인덱스를 쓰는 이유를 알 수 있습니다.

그것은 바로 조회 성능을 높이기 위해이며 이것을 위해 저장 성능과 저장용량을 희생하는 것입니다.

 

📝8.3 B-Tree 인덱스

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

하지만 아직도 가장 범용적인 목적으로 사용되는 인덱스 알고리즘입니다. 많은 변형 알고리즘을 가지고 있습니다.

 

B-Tree는 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태를 유지합니다.

 

🧷8.3.1 구조 및 특성

B-Tree 인덱스를 제대로 사용하려면 B-Tree의 기본적인 구조를 알아야합니다. B-Tree는 트리 구조의 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙는 형태입니다. 트리 구조의 가장 하위에 있는 노드를 리프 노드라고 하며 트리 구조에서 루트 노드가 아니고 리프 노드도 아닌 것을 브랜치 노드라고 합니다.

• 루트 노드 : 최상위 노드
• 리프 노드 : 최하위 노드
• 브랜치 노드 : 중간 노드(루트도 리프도 아님)

 

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리하게 되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가집니다. 

 

그림과 같이 인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정려돼 있지 않고 임의의 순서로 저장돼 있습니다.

많은 사람들이 데이터 파일의 레코드를 INSERT된 순서대로 저장되는 것으로 생각하지만 그렇지 않고 순서는 보장되지 않습니다.

 

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

이를 위해 인덱스의 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가지며 

B-Tree의 특성으로 리프 노드들은 모두 깊이가 같아 항상 같은 조회 시간이 걸립니다.(평균 O(logn) 최악 O(n))

 

참고
InnoDB의 세컨더리 인덱스는 리프노드가 테이블의 물리 주소를 가지는 것이 아닌 프라이머리 키를 가지고 있습니다. 
그렇기에 세컨더리 인덱스에서 프라이머리 키를 찾고 클러스터링 인덱스에서 한번 더 조회하는 과정을 거칩니다. 

 

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

테이블의 레코드를 저장하거나 변경하는 경우 인덱스 키 추가나 삭제 작업이 발생합니다. B-Tree의 추가 삭제를 한번 봅시다.

 

📌8.3.2.1 인덱스 키 추가

새로운 키 값을 추가할 때 바로 인덱스에 저장될 수도 그렇지 않을 수도 있다.

B-Tree에 저장될 대는 저장될 키 값을 이용해 B-Tree의 적절한 위치를 검색해야한다. 리프 노드가 꽉차면 리프 노드를 분리 시켜야 되는데 이는 상위 브랜치 노드까지 처리 범위가 넓어진다. 이때문에 B-Tree가 쓰기 작업에 비용이 많이 드는 것이다.

 

테이블에 레코드를 추가하는 작업 비용이 1이라면 인덱스에 키를 추가하는 작업 비용은 1.5 정도로 예측할 수 있다.

중요한 것은 이 비용의 대부분이 메모리와 cpu 처리 시간이 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야하는 시간이다..

 

참고
InnoDB 스토리지 엔진은 인덱스 키 추가 작업을 지연 시켜 나중에 처리할 수 있지만 나중에 서술할 클러스터링, 유니크 인덱스의 경우 중복 체크가 필요하기에 즉시 추가 삭제한다.

 

📌8.3.2.2 인덱스 키 삭제

키 값을 삭제하는 경우는 상당히 간단하다. 그냥 그 노드를 B-Tree에서 찾아 삭제 마크만 하면 작업이 완료된다. 

물론 디크스 쓰기 작업이 필요하므로 이 작업 역시 그리 사정이 좋지 않은데..

삭제 마크는 그대로 방치하거나 재활용이 가능하다.

 

📌8.3.2.3 인덱스 키 변경

단순히 인덱스 상의 키 값만 변경하는 것은 불가능하며 먼저 키 값을 삭제시킨 후 새로운 키 값을 추가하는 형태로 진행된다.

 

📌8.3.2.4 인덱스 키 검색

위처럼 우리는 인덱스의 단점만을 설명했지만 인덱스를 사용하는 이유는 바로 빠른 조회에 있다.

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

 

인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE, DELETE 처리에서 항상 해당 레코드를 먼저 검색한다.

B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에도 사용할 수 있다. 부등호 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.

이것의 이유는 %ohn의 경우 검색 대상이 문자열 시작부분에 국한되지 않아 검색이 불가능하기 때문입니다..

• ex1) 이름이 “John”인 사람 검색 → 인덱스 O
• ex2) 이름이 “J” 이후 인 사람 검색 → 인덱스 O
• ex3) 이름이 “John”이 아닌 사람 검색 → 인덱스 X (사용하지만 비효율적)
• ex4) 이름이 “Jo”로 시작하지 않는 사람 검색 → 인덱스 X (경우에 따라 사용됨, 비효율적)
• ex5) 이름이 “hn”로 끝나는 사람 검색 → 인덱스 X
• ex6) 이름이 “Jo”로 시작하는 사람 검색 → 인덱스 O

또한 인덱스는 키 값이 변형이 가해진 후 비교를 통한 검색은 절대 빠른 검색 성능을 사용할 수 없다.

SELECT * FROM users WHERE UPPER(name) = 'JOHN';

위의 코드의 경우 인덱스는 변형 전으로 만들었기에 검색이 불가능하다.

SELECT * FROM orders WHERE total_amount - discount > 100;

이 연산도 인덱스로 미리 계산을 한 것이 아니기에 비효율적입니다. 

 

참고
InnoDB 스토리지 엔진의 인덱스는 갭락을 사용하여 검색을 수행한 인덱스를 잠근 후 테이블을 잠그는 방식을 사용한다.
따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. 심지어 모든 레코드를 잠글 수도 있다.  그렇기에 동시성 성능 문제 이슈와 데드락이 발생 가능하다.
그만큼 인덱스 설계가 많이 중요하다.

 

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

📌8.3.3.1 인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 기본 단위를 페이지 또는 블록이라 한다.

디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위이다. 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 하다. 인덱스도 결국은 페이지 단위로 관리된다.

 

B-Tree는 자식 노드의 개수가 가변적인데, MySQL에서는 인덱스의 페이지 크기와 키 값의 크기에 따라 자식 노드 수가 결정된다.

• 페이지의 크기는 innodb_page_size 시스템 변수를 이용해 4~64kb 선택할 수 있다. (기본 값은 16kb)

만약 페이지의 크기가 16KB이면 16*1024/(16+12) = 585개의 자식노드를 저장할 수 있다. 

그러면 인덱스 키 값이 커지면 어떤 현상이 발생할까요? 키 값의 크기가 32 바이트로 늘렸을때 372개의 자식 노드를 가질 수 있다. 

이때 SELECT로 500개의 레코드를 읽어야한다면 최소 2개의 디스크를 읽어야한다.

결국 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어난다.

 

📌8.3.3.2 B-Tree의 깊이

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

키 값의 평균 크기가 늘어나면 어떤 현상이 일어나는지 예시로 알아보자

키 값이 16바이트인 경우 최대 2억(585 * 585 * 585)개 정도의 키 값을 담을 수 있지만 키 값이 32 바이트로 늘어나면 5천만 개로 줄어든다. 

B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제이다. 결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 수가 적어진다.

 

📌8.3.3.3 카디널리티

카디널리티는 데이터의 중복도를 말하며, 높을 수록 중복된 값이 적다.

인덱스는 카디널리티가 높을수록 검색 대상이 줄어들기 때문에 더 빠르게 처리됩니다.

 

예제를 들어봅시다.

SELECT *
FROM tb_test
WHERE country = 'KOREA' AND city = 'SEOUL';

tb_test 테이블의 레코드 건수는 1만 건, country 컬럼에만 인덱스를 생성했다 했을 때 아래 두가지 케이스를 살펴보자

1. country 컬럼의 유니크한 값의 개수가 10개인 경우
2. country = ‘KOREA’ 이 조건에서 일치하는 값은 1000개 일 것이고, 그 중 city = ‘SEOUL’ 인 값이 1건이면 999건은 불필요하게 읽은 것이다.
3. country 컬럼의 유니크한 값의 개수가 1000개인 경우
4. country = ‘KOREA’ 이 조건에서 일치하는 값은 10개 일 것이고, 그 중 city = ‘SEOUL’ 인 값이 1건이면 9건은 불필요하게 읽은 것이다.

이처럼 유니크한 카디널리티의 값이 많아질수록 읽기 작업의 횟수가 줄어듭니다.

 

📌8.3.3.4 읽어야 하는 레코드의 건수

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

 

만약 테이블에 100만건의 레코드가 있고 이를 80만건 읽는다 가정해봅시다.

상황에 따른 성능 차이

• 인덱스를 사용한 경우 (랜덤 I/O):
  • 인덱스를 사용하면 필요한 데이터를 랜덤하게 찾기 위해 여러 위치에서 데이터를 읽습니다.
  • 각 데이터 접근이 랜덤 I/O 방식으로 처리되므로, 디스크에서 데이터를 찾는 데 시간이 더 걸릴 수 있습니다.
  • 특히, 조회할 데이터의 양이 많을 때는 랜덤 I/O가 비효율적일 수 있습니다.
• 테이블 풀 스캔 (순차 I/O):
  • 테이블 풀 스캔을 사용하면, 디스크에서 데이터를 순차적으로 읽게 되어 순차 I/O 방식으로 처리됩니다.
  • 순차 I/O는 디스크 헤드의 이동을 최소화하며, 데이터를 한 번에 연속적으로 읽기 때문에 대량의 데이터를 읽을 때 훨씬 더 빠르게 동작할 수 있습니다.

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

MySQL이 인덱스를 이용하는 대표적인 방법 3가지를 알아봅시다.

📌8.3.4.1 인덱스 레인지 스캔

인덱스 레인지 스캔은 접근 방법 중 가장 대표적인 접근 방법입니다. 3가지 방법 중 제일 빠릅니다.

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

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

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

위 그림처럼 화살표에서도 알 수 있듯이 루트 노드부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드를 거쳐야 시작지점을 찾을 수 있습니다. 일단 시작할 위치를 찾고 그때부터 레코드를 순서대로 읽으면 됩니다.

 

이제 스캔이 되었으면 프라이머리 키를 이용해 클러스터링 인덱스에서 재검색을 하고 데이터 파일을 읽어옵니다. 이때 레코드 한건한건 단위로 랜덤 I/O가 일어납니다. 이것 때문에 만약 읽어야하는 데이터가 인덱스의 20~25%를 넘으면 테이블을 읽는 것이 더 효율적입니다.

 

정리하면 다음과 같습니다.

1. 인덱스에서 조건이 만족하는 값이 저장된 위치를 찾는다. (인덱스 탐색)
2. 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 읽는다. (인덱스 스캔)
3. → 해당 인덱스를 구성하는 컬럼의 정순 or 역순으로 정렬된 상태로 레코드를 가져온다.
4. 읽어온 인덱스 키와 레코드 주소를 이용해 레코드를 읽는다.→ 인덱스를 통해 레코드를 읽는 작업은 그냥 읽는 것 보다 비용이 많이든다.
5. 근데, 만약 필요한 데이터가 전부 인덱스에 있다면 이 과정을 생략한다. (커버링 인덱스)
6. → 이때 레코드 한 건 마다 랜덤 I/O가 일어난다.

📌8.3.4.2 인덱스 풀 스캔

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

대표적으로 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식을 사용한다.

 

일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다.쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식을 사용한다. 물론 다른 데이터가 필요하면 절대 이 방식은 안쓴다.

 

또한 인덱스 풀 스캔은 몇가지 조건이 있는데

1. 유니크하지 않고 여러 값이 존재하는 경우:
   • 인덱스에 있는 칼럼이 유니크한 값만 가지고 있지 않거나, 중복된 값이 많이 있을 경우, 인덱스를 통한 검색이 모든 값을 읽는 방식으로 동작하게 됩니다. 이때 조건에 맞는 레코드가 많을 수 있기 때문에, 인덱스를 처음부터 끝까지 모두 읽는 풀 스캔이 발생합니다.
2. 조건절의 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우:
   • 다중 칼럼 인덱스가 있을 때, 쿼리의 조건이 인덱스의 첫 번째 칼럼이 아닌 다른 칼럼을 대상으로 할 경우, 인덱스의 순차적인 범위 조회가 어려워져 풀 스캔이 발생합니다.

📌8.3.4.3 루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 넘어가는 형태로 처리한다.

일반적으로 GROUP BY or 집합 함수 중 MIN(), MAX() 함수에 대해 최적화를 하는 경우에 사용한다.

 

루스 인덱스 스캔(Lazy Index Scan)의 동작 원리:

• 인덱스를 읽으면서 불필요한 값을 건너뛰는 방식으로, 실제로 필요한 값만 조회하고 나머지 값은 무시합니다.
• 일반적인 인덱스 스캔은 인덱스를 순차적으로 읽는 방식이지만, 루스 인덱스 스캔은 특정 조건에 맞는 값만 추출하므로 더 효율적입니다.
• GROUP BY나 **MIN(), MAX()**와 같은 연산을 최적화하기 위해 인덱스를 사용하여 불필요한 값을 건너뛰고, 필요한 최소값 또는 최대값만을 선택적으로 읽을 수 있습니다.

루스 인덱스 스캔을 사용하는 경우:

1. MIN() / MAX() 함수:
   • 예를 들어, MIN()이나 MAX()와 같은 집합 함수는 값들 중에서 최소값이나 최대값을 구하는 연산입니다.
   • 인덱스가 있다면 첫 번째 인덱스 값(예: 최소값)은 인덱스의 첫 번째 값을 읽어서 바로 찾을 수 있습니다. 이때 다른 값들은 건너뛰고 최소값/최대값만 추출할 수 있습니다.
   • 예시: SELECT MIN(price) FROM products;라는 쿼리에서 price에 대한 인덱스가 있다면, 루스 인덱스 스캔을 통해 인덱스에서 가장 작은 price 값만 읽고 나머지 값은 건너뛰는 방식으로 처리됩니다.
2. GROUP BY:
   • GROUP BY 쿼리에서 그룹핑된 각 항목에 대해 계산이 필요한데, 인덱스를 사용하면 일부 연산을 최적화할 수 있습니다. 예를 들어, 특정 컬럼으로 그룹화한 후 합계나 평균을 구하는 쿼리에서, 필요한 값을 인덱스만으로 빠르게 처리할 수 있습니다.

예시 1: MIN() 함수 최적화

SELECT MIN(price) FROM products;​

• products 테이블에 price에 대한 인덱스가 있으면, 루스 인덱스 스캔은 첫 번째로 나오는 최소 price 값만을 읽고 나머지 값은 건너뛰므로 빠르게 최소값을 찾을 수 있습니다.

예시 2: MAX() 함수 최적화

SELECT MAX(salary) FROM employees;

• employees 테이블에서 salary에 대한 인덱스를 사용할 경우, 루스 인덱스 스캔은 salary 컬럼의 최대값만을 추출하고, 나머지 값은 읽지 않으므로 연산이 효율적입니다.

📌8.3.4.4 인덱스 스킵 스캔

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

 

예를 들어보자

mysql> ALTER TABLE employees ADD INDEX inx_gender_birthdate (gender, birth_Date);

해당처럼 첫 칼럼을 성별 두번째 칼럼을 생일로 하는 인덱스를 만들었다고 해보자

 

이 인덱스를 사용하려면 WHERE 조건절에 gender 칼럼에 대한 비교 조건이 필수로 들어가야 사용할 수 있다.

//인덱스 사용 불가
SELECT * FROM employees WHERE birth_date>='1965-02-01';
//인덱스 사용 가능
SELECT * FROM employees WHERE gender = 'M' AND birth_date>='1965-02-01';

 

그래서 위의 두 쿼리 중에서 gender 칼럼과  birth_date 칼럼의 조건을 모두 가진 두 번째 쿼리만이 인덱스를 효율적으로 사용할 수 있다. 

 

MySQL 8.0 버전부터는 옵티마이저가 gender 칼럼을 건너뛰어서 birth_Date 칼럼만으로 인덱스를 검색해주는 기능인

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

 

인덱스 스킵 스캔 최적화 기능이 도입되면서 옵티마이저가 A 컬럼을 건너뛰어서 B, C 컬럼만으로도 인덱스 검색이 가능하게 되었다. GROUP BY의 인덱스 처리에만 사용할 수 있었던 루스 인덱스 스캔과는 다르게 인덱스 스킵 스캔은 WHERE 조건절의 검색에 사용 가능해져, 그 용도가 훨씬 넓어졌다.

 

물론 인덱스 스킵 스캔도 일반 인덱스 스캔보다 느리기에 빈도가 높다면 인덱스를 최적화 하는게 중요하다.

 

🧷8.3.5 다중 컬럼 인덱스

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

이러한 인덱스를 다중 칼럼 인덱스라고 한다.

위 그림은 다중 칼럼 인덱스일 때 각 인덱스를 구성하는 칼럼의 값이 어떻게 정렬되어 저장되는지 설명해준다.

이 그림에서 중요한 것은 인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬되는 것이다.

 

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

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

물론 정렬과 상관없이 어느 방향으로 읽을 지는 옵티마이저가 결정한다.

 

📌8.3.6.1.1 인덱스 스캔 방향

first_name 칼럼에 대한 인덱스가 포함된 employees 테이블로 예제를 살펴보자

예제로 인덱스가 오름차수능로 정렬되어있을 때 가장 큰 값을 읽는 것을 상정해보자

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

 

만약 이러한 상황이라면 인덱스를 처음부터 오름차순으로 끝까지 읽어 가장 큰 값을 가져오는 것일까>??

 

그렇지 않다.

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

 

그렇기에 위 쿼리에 대해 인덱스를 역순으로 접근해 첫 번째 레코드만 읽는다.

 

물론 위의 쿼리는 예제로 인덱스에서는 ORDER BY는 잘 쓰지 않는다.

 

 

 

📌8.3.6.1.2 내림차순 인덱스

MySQL 서버에서 다음 두 쿼리는 실제 내림차순인지 오름차순인지 관곙벗이 인덱스를 읽는 순서만 변경해서 해결할 수 있다는 것을 알았다.

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

 

그러나 두 쿼리는 성능차이가 난다 바로 오름차순 인덱스 조회가 더 빠르다는 것...

 

InnoDB 스토리지 엔진에서는 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 두 가지 이유가 있다.

1. 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
2. 페이지 내에 인덱스 레코드가 단방향으로만 연결된 구조

이 문제를 해결하려면 방법이 1개인데 바로 역방향 인덱스도 하나 만드는 것이다.. 물론 드물게 실행된다면 고려 안해도 된다.

 

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

쿼리의 WHERE 조건이나 GROUP BY, 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용하고 어떤 방식으로 사용할 수 잇는지 알아보자

 

📌8.3.7.1 비교 조건의 종류와 효율성

다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교인지 아니면 부등호(<>) 인지에 따라 각 인덱스 칼럼의 활용 형태가 달라지며 그 효율 또한 달라진다.

 

예시를 들어보자

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

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

케이스 A

• 해당 인덱스는 dept_no='d002' AND emp_no>=10144 인 레코드를 찾고 그 이후에는 dept_no가 d002가 아닐때까지 찾으면 된다.
• 이 경우에는 읽는 레코드가 모두 사용자가 원하는 결과임을 알 수 있다. 즉, 조건을 만족하는 레코드가 5개일 때 5번의 조회를 수행한 것이므로 상당히 효율적이다.

케이스 B

• 해당 인덱스는 emp_no>=10144 AND dept_no='d002'  인 레코드를 찾고 그 이후에는 모든 레코드에 대해 dept_no가 d002인지 비교한다.

@Table(
    indexes = {
        @Index(name = "idx_party_category_status", columnList = "partyType, isClosed")
    }
)
public class Party {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String title;

    private String content;

    private int partyLimit;

    private int peopleCount;

    private LocalDateTime startTime;

    private LocalDateTime endTime;

    private PartyType partyType;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "member_id")
    private Member member;

    private boolean isClosed;

 

해당 테이블은 나의 프로젝트 테이블인데 이것을 가지고도 생각해보자

 

지금 생성된 인덱스는 파티 타입 -> 파티 종료 여부 순으로 이루어져있다.

파티 타입은 3가지이고 종료 여부는 조건이기에 2가지이다.  여기서 올바른 순서가 무엇일까? 

 

정답!

 

파티라는 비즈니스 특성상 데이터는 쌓일 것이다. 그렇기에 종료 여부는 필연적으로 종료된 파티가 많아지고 비율 또한 그럴 것이다.

이럴때 만약 파티 타입으로 먼저 조회하게 된다. 파티 타입을 필터링하고 대용량의 종료 데이터까지 읽어야 될 것이다. 

페이지 조회 시에 필요하지 않는 종료된 데이터까지 탐색해야 된다는 의미이다. 그렇기에 종료 여부를 먼저 확인하는게 맞을 것이다. 종료 여부는 현재 실행중인 파티만 해당되고 그 안에서 파티타입으로 필터링한다. 이 방법이 더 비교 로직이 적을 것이다.

 

위의 예제처럼 두 조건에서 작업의 범위를 결정하는 조건을 작업 범위 결정 조건이라 한다.(정식용어는 아니다)

위의 예제의 경우는 파티 타입이 작업 범위를 크게 줄이지 못하고 단순히 거름종이 역할만 하는 조건을 필터링 조건이라고 한다.

이러하게 잘못 사용하는 경우 인덱스를 쓰지 않는 것이 더 빠를 때도 많다고 한다.

 

 

📌8.3.7.2 인덱스의 가용성

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있는 것이다. 키워드 값에 대한 정렬 방식, 다중 칼럼에 대한 정렬 방식을 말하는 것이 맞다.

 

그렇기에 인덱스의 왼쪽이 정해지지않으면 인덱스 레인지 스캔 방식이 불가능하다.(Like('%hj') 이러한 쿼리를 말하는 것)

예시로 보자면

 

첫번째로 왼쪽을 비워둔 상태로 조회를 해보자.

• 아래의 사진처럼 type이 ALL인 풀스캔을 했다는 것을 알 수 있다. 이유는 왼쪽이 정해지지 않은 것

 

두번째로 오른쪽을 비워둔 상태로 조회를 해보자.

• 정상적으로 인덱스 레인지 스캔을 한 것을 알 수 있다.

 

📌8.3.7.3 가용성과 효율성 판단

기본적으로 B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없다.

• NOT-EQUAL로 비교된 경우(<>, NOT IN, NOT BETWEEN, IS NOT NULL)
  • 이유 : 인덱스는 스캔 범위를 특정해야 하지만 부정의 경우 그것을 제외한 모든 것이기에 사용 안하는 편이 좋다.
• LIKE '%??' 뒷부분 일치의 문자 패턴 비교의 경우
  • 이유 : 인덱스는 기본적으로 왼쪽 정렬이기에 왼쪽이 특정되어야 사용이 가능하다.
• 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 경우
  • 이유 : 변형으로 인해 인덱스의 값을 비교 불가능하기 때문이다.
• 복합 인덱스에서 OR 조건 사용 
  • 이유 : 부정의 경우와 동일
• 데이터 타입이 서로 다른 비교
  • where char_column = 10
• 문자열 데이터 타입의 콜레이션이 다른 경우