Real MySQL 8.0 8장

김재연·2025년 7월 13일

Real MySQL mysql

디스크 읽기 방식

일단 인덱스에 대해서 알아보기 이전에 디스크의 읽기 방식의 차이에 대해서 알아보자.

그러기에 앞서 디스크의 종류는 어떤 것이 있을까?

HDD, SDD 의 차이

하드 디스크 드라이브 (HDD), 솔리드 스테이트 드라이브 (SSD) 가 있다.

하드 디스크는 정말 원판을 돌리거나, 헤드를 움직이며, 데이터의 물리적인 저장장소를 찾는 것을 의미한다.

하지만, SSD 는 원판을 제거하고, 플래시 메모리를 장착하고 있어 굉장히 빠르다.

또한 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다.

완전 개꿀이다.

HDD, SSD 와의 속도 차이를 나타내는 지표도 한번 봐보자.

랜덤 I/O와 순차 I/O

특히 하드디스크는 랜덤 I/O 인지 순차 I/O 인지가 정말 중요하다.

아무래도 물리적으로 헤드를 움직여야 하는 랜덤 I/O 는 당연히 느릴 수 밖에 없는 것이다.

반대로 SSD 는 이 부분에서 굉장한 강점을 드러낸다.

위 그림은 순차 I/O 와 랜덤 I/O 를 그림으로 나타낸 것이다.

그러면 SSD 는 순차 I/O 와 랜덤 I/O 의 속도차이가 거의 없을까?

그렇지 않다.

아직은 그 부분까지 개선하지는 못한 것 같다.

또한 쿼리 실행계획으로 랜덤 I/O 를 순차 I/O 로 바꾸기란 그다지 많지 않다고 한다.

그렇기 때문에, 쿼리의 실행계획을 효율적으로 구성해 I/O 자체를 줄이는 것이 인덱스의 목적이라고 할 수 있다.

인덱스

인덱스란, 데이터의 저장 성능을 희생하고, 그 대신 데이터의 읽기 속도를 높인 기법이다.

인덱스를 역할별로 구분해 본다면 프라이머리 키와 보조 키(세컨더리 인덱스) 로 구분할 수 있습니다.

크게 인덱스는 B-Tree, Hash-Index 로 구분할 수 있으며, 이외에도 많은 인덱싱 알고리즘들이 존재하지만, MySQL 에서는 이 두가지면 충분하다고 한다.

전문 검색이나 공간 검색용 인덱스는 뒤에서 좀 더 자세히 살펴본다고 한다.

B-Tree 인덱스

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

전문 검색과 같은 특수한 요건이 아닌 경우, 대부분 인덱스는 해당 인덱스를 활용할 정도로 일반적인 용도에 적합한 알고리즘입니다.

B-Tree 인덱스의 구조 및 특성

B-Tree 의 구조로는 최상위에 하나의 루트 노드, 중간에 브랜치 노드, 가장 끝단에 위치해 있는 리프 노드 로 이루어져있습니다.

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

위와 같은 그림으로 표현할 수 있겠죠!

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

그 과정을 스토리지 엔진에 따라 아래의 그림과 같이 표현할 수 있습니다.

MyISAM 은 솔직히 중요하지 않으니까, InnoDB 를 봅시다.

리프노드 페이지에서 또 루트 노드로 가는 것을 볼 수 있죠?

이는, 세컨더리 인덱스는 논리적인 주소를 프라이머리 키를 이용하여 가지고 있기 때문에 발생하는 일입니다.

실질적인 레코드의 물리적인 저장소 위치가 아니라, 프라이머리 키를 주소 값으로 가지고 있기 때문에, 세컨더리 인덱스로 탐색 후 프라이머리 키 인덱스로 탐색을 하게 되는 것이죠

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

인덱스에서의 추가 및 삭제는 굉장히 중요합니다.

이제부터 그를 알아보도록 합시다

인덱스에 데이터를 추가하는 경우, B-Tree 알고리즘으로 인해서 리프노드가 꽉찬 경우 노드가 분리되거나, Depth 가 깊어질 수도 있습니다.

그렇기 때문에, 굉장히 비용이 많이 드는 작업이죠

일반적인 레코드에 Insert 연산을 진행하는 것보다 Index 에다가 Insert 작업을 진행하는 것은 약 1.5 배의 비용을 요구한다고 보면 된다고 합니다.

그러니, 일반적인 Insert 를 하는 경우 1, 인덱스에 Insert 를 하는 경우 1.5 인 것이죠.

그렇기 때문에 인덱스가 3개가 되는 경우 5.5 의 비용이 든다고 예측할 수 있습니다.

이러한 높은 비용으로 인해서 4장에서 다뤘던 체인지 버퍼 를 활용하여 인덱스 변경사항들을 똑똑하게 필요한 경우 처리하게 됩니다.

아 물론 유니크 인덱스의 경우는 당장 유니크한지 체크가 필요하기 때문에, 이러한 똑똑한 방법이 불가능합니다.

인덱스 키 삭제

이는 B-Tree 의 리프노드를 찾아서 삭제 마크만 하면 됩니다.

굉장히 쉬운 작업이지만, 디스크 작업이 필요하기 때문에 체인지 버퍼를 활용합니다.

인덱스 키 변경

인덱스의 키 값을 변경할 수 없습니다.

키값을 통해서 정렬이 되어 있기 때문에 어떻게 보면 당연한 것이죠

그렇기 때문에 삭제 -> 삽입 순으로 이루어집니다.

이도 체인지 버퍼를 통해서 똑똑하게 이뤄지기 때문에, 성능 걱정은 할 필요가 없다고 생각합니다.

인덱스 키 검색

조건을 통해 트리 탐색을 진행하여 특정 인덱스를 찾아냅니다.

그러니 당연히 빠를 수 밖에 없겠죠.

또한, 이를 통해서 넥스트 키 락, 갭 락을 걸기도 합니다.

그렇기 때문에 인덱스가 굉장히 중요한 것이죠.

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

인덱스 페이지는 위와 같이 이루어져 있습니다.

여러개의 키가 위와 같이 저장되어 있죠.

아 물론 innodb_page_size 를 통해 인덱스 페이지를 다르게 할 수 있지만, 현재는 16KB 로 가정하고 넘어간다는 것을 유의해주세요

위와 같은 경우 하나의 인덱스 페이지에 몇 개의 키를 저장할 수 있을까요?

대략 585 개 저장할 수 있습니다.

이는, B-Tree 에 585 개의 자식노드만을 가질 수 있다는 것을 나타냅니다.

만일, 키 하나 하나의 용량이 커진다면 어떻게 될까요?

가질 수 있는 노드의 개수가 적어지지 않을까요?

그러면 여러개의 인덱스 페이지를 활용해야겠죠?

그렇다는 것은 여러번 디스크로부터 읽어와야 한다는 것입니다.

그렇기 때문에 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수간 늘어나게 되는 것입니다.

아 또한, 키값이 커짐에 따라 Depth 하나 하나에서 다룰 수 있는 데이터의 개수도 작아지니, Depth 가 깊어질 수 밖에 없습니다.

당연히 이도 디스크로부터의 읽기 연산을 더 해야 한다는 것이고 성능 저하로 이어질 것입니다.

선택도(기수성)

카디널리티라고 합니다.

중복된 키 값들이 많다면 카디널리티가 낮은 것이고, 적다면 카디널리티가 높은 것입니다.

책에서 나온 예제를 다뤄볼까요?

유니크한 값의 개수가 10개라면 만일 데이터가 10000개라고 했을 때, country 를 거쳤을 때 1000개의 데이터가 추출되게 될 것입니다.

반면에 케이스 B 의 경우에는 10개의 결과가 추출되겠죠.

만일 korea 이면서 seoul 인 레코드가 하나라면 1개를 탐색하기 위해서 1000 개의 데이터를 가져온 것과 동일한 것입니다.

케이스 B 의 경우는 효율적인 것이겠죠.

구체적인 예시로 아래와 같은 예시를 볼 수 있을 것 같아요.

위에서 설명한 바와 동일합니다.

이렇게 인덱스의 유니크함이 쿼리 성능에도 큰 영향을 끼칠 수 있음을 알 수 있었습니다.

읽어야 하는 레코드의 건수

일반적으로 DBMS 의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4 ~ 5 배 정도의 비용이 더 많이 드는 작업으로 인식한다고 합니다.

그렇기 때문에, 데이터의 탐색 범위가 20 ~ 25% 를 넘어가게 되면 인덱스를 활용하는 것이 아니라, 풀 스캔을 하게 되죠.

하지만, 예외 사항들도 존재합니다.

지금부터 한번 살펴보시죠.

인덱스 레인지 스캔

인덱스 레인지 스캔은 인덱스의 접근 방법 가운데 가장 대표적인 접근 방식이고, 뒤에서 설명할 나머지 두 가지 접근 방식보다 빠른 방법입니다.

딱봐도 알 수 있겠지만, 검색의 범위가 지정되었을 때 사용 가능한 방법입니다.

리프노드까지 가서, 데이터 스캔을 시작하고, 읽어야 하는 지점까지 읽은 다음, 쿼리 결과를 반환합니다.

실제로는 데이터에 접근해야 하는데 이도 한번 살펴보죠.

실제로 랜덤 디스크 I/O 를 발생시켜 레코드에 도달하는 것을 볼 수 있습니다.

이는 굉장히 리소스가 많이 드는 작업이죠

그렇기 때문에, 데이터 탐색 범위가 테이블의 20 ~ 25%를 넘으면 풀 스캔을 하는 것입니다.

또한 인덱스의 특징으로 인해서 정렬된 결과를 가져옵니다.

즉, 다음과 같은 3가지의 연산을 거칩니다.

하지만, 만일 실제 레코드에 접근하지 않아도 된다면, 성능은 어떻게 될까요?

개선이 되겠죠?

이를 활용한 것이 커버링 인덱스 입니다.

인덱스에 명시되어 있는 컬럼들만을 활용하여, 탐색을 하는 것이죠.

이렇게 되면 실질적으로 레코드에 접근하지 않아도 되어 비용이 많이 절감됩니다.

개꿀딱이죠.

인덱스 풀 스캔

이는 절대로 실제로 레코드를 읽어야 하는 상황에서 쓰이지 않는다고 합니다.

인덱스에서 바로 가져올 수 있는 값들로만 쿼리가 이루어져 있을 때, 사용되는 것이죠.

또한, 실제 테이블을 풀 스캔하는 것보다, 인덱스를 풀 스캔 하는 것이 사이즈가 더 작으니 빠를 때 사용됩니다.

인덱스 풀 스캔이 실행되는 경우는, (A, B, C) 이렇게 인덱스가 이루어져 있을 때, 선행된 인덱스가 where 절에 쓰이지 않고, 다른 절이 인덱스에 활용되었을 때 쓰입니다. 또한, 당연하게 인덱스에 존재하는 컬럼들로만 쿼리가 이루어져 있어야지 실행이 되겠죠?

루스 인덱스 스캔

이는 그냥 점프 점프 하면서 스캔 하는 것이라고 생각하면 될 것 같습니다.

인덱스 풀 스캔에서 점프가 추가되었다고 생각하면 될 것 같아요.

이는 아래 쿼리와 같은 Group By 연산에서 Min 과 같은 집계함수가 활용되었을 때 사용됩니다.

인덱스의 첫번째 컬럼으로 인해서 값들은 정렬이 되어 있을 것입니다.

또한, 각각 같은 dept_no 를 가진 컬럼들 사이에서는 emp_no 로 정렬이 되겠죠.

그렇기 때문에, min 혹은 max 를 검색하기 위해서는 첫번째 혹은 마지막 레코드만을 탐색하면 되는 것입니다.

인덱스 스킵 스캔

위와 같이 gender 를 명시하지 않아, 인덱스를 활용하지 못하는 인덱스가 있다고 가정해봅시다.

MySQL 8.0 버전 부터는 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 칼럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입되었습니다.

하지만, 잠깐 이를 비활성화 하고 다음 쿼리를 살펴볼까요?

여기서 type 이 index 라고 되어있죠.

인덱스를 풀 스캔 했다는 말입니다.

앞에서 보았듯이, 첫번째 인덱스가 명시되어 있지 않으니, 당연하게 풀 스캔을 때릴 수 밖에 없겠죠.

즉, 인덱스를 비효율적으로 활용한 것입니다.

이제 MySQL 8.0 버전부터 도입된 인덱스 스킵 스캔을 활성화하고, 동일 쿼리의 실행 계획을 다시 확인해볼까요?

눈여겨 볼 부분은 Using index for skip scan 입니다.

인덱스 스킵 스캔 최적화 방식을 활용했다는 것이고, 이는 gender 의 유니크한 값들을 조사해 gender 를 명시하여 다시 쿼리를 날려준 것입니다.

아래 이미지들과 같이 말입니다.

인덱스 스킵 스캔은 아래와 같은 단점들이 존재합니다.

유니크한 값의 개수가 굉장히 많다면, 쿼리가 너무 많이 발생하게 될 것입니다.

그렇기 때문에, 이러한 경우 해당 방법이 탐색을 효율적으로 만들어주지 않을 것이라는 것을 알 수 있습니다.

두번째 제약조건도 한번 살펴봅시다.

이 경우 Select 에 * 이 사용되었기 때문에 인덱스 스킵 스캔이 활용되지 못합니다.

이는 인덱스 스킵 스캔 자체가, 인덱스 풀 스캔을 활용하기 때문에 그런 것 같습니다.

다중 칼럼 인덱스

여태까지 본 것들은 모두 1개의 칼럼들을 포함한 인덱스였습니다.

이제부터는 2개 이상의 칼럼을 포함한 인덱스를 살펴보도록 할게요!

다중 컬럼 인덱스는 첫번째 인덱스부터 순서대로 정렬된다는 것을 볼 수 있습니다.

첫번째 인덱스가 같은 경우는 두번째 인덱스로 정렬이 되겠죠.

이는 추후 인덱스의 성능에 굉장히 큰 영향을 미칠 수 있으니 주의하여 설계해야 할 부분입니다.

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

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

하지만, 이를 위로 탐색하면 내림차순, 아래로 탐색하면 오름차순이니까 다양하게 활용할 수 있습니다.

인덱스의 정렬

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

하지만 예전에는 이가 불가능했죠, 그렇기 때문에, -1 를 곱해줘서 오름차순으로 정렬하더라도 오름차순, 내림차순으로 나눠서 정렬할 수가 있었습니다.

하지만, 지금은 혼합해서 인덱스를 생성할 수 있습니다.

아래와 같이 말이죠!

인덱스 스캔 방향

다음과 같은 쿼리가 실행되면 어떻게 될까요?

만일 인덱스의 정렬 순서가 ASC 라면 위 쿼리는 인덱스를 타지 않을까요?

그림에서 보면 알 수 있듯이 실제로는 그렇지 않습니다.

그냥 정렬된 순서 반대로 레코드를 타면 되는 것이니까요!

그렇기 때문에 위 코드도 간단합니다.

위의 쿼리는 Anneke 를 찾고, Anneke 를 포함해서 4개만 정순으로 가져오면 되는 것이고, 아래의 쿼리는 역순으로 5개를 가져오면 되는 것입니다.

옵티마이저가 이를 알아서 최적화해주고 실행계획을 구성해줄 것이에요.

그렇다면 내림차순 인덱스는 필요가 없는 것 아닐까요??

내림차순 인덱스

서버에서 두 쿼리는 실제로 내림차순인지 오름차순인지 관계없이 인덱스를 읽는 순서만 변경해서 해결할 수 있다는 것을 살펴보았어요.

물론 위와 같이 내림차순으로 따로 컬럼을 설정해서 해결해줄 수도 있겠죠!

근데, 이런 방식은 왜 존재하는 것일까요?

역순으로 정렬하는 조건이 필요가 없다면... 굳이 할 필요가 없는 것 아닐까요?

더 나아가 두 쿼리는 동일한 성능을 보일까요?

위와 같이 테이블을 설정해놓아봅시다.

일단, 용어를 이해하기 위해서 위와 같은 그림을 이용해보죠!

오름 차순 인덱스는 왼쪽에 있는 리프노드 값이 더 작은 것을 의미하고 내림차순은 그 반대를 의미합니다.

그리고 인덱스 정순 스캔은 왼쪽으로 오른쪽, 그리고 역순은 그 반대를 의미합니다.

조금 더 자세한 용어정리는 아래에서 확인해볼 수 있어요.

우리가 내림차순 인덱스의 필요성에 대해서 알아보기 위해 테스트를 해보기에 앞서 데이터 준비가 필요하겠죠?

위와 같이 테이블을 설계해놓고

1천 2백만개의 데이터를 넣고 쿼리를 날려봅시다.

위와 같은 쿼리는 Offset 을 걸어놔서 테이블의 모든 내용을 스캔해야합니다.

결과는 DESC 탐색이 5.35 초가 걸렸죠.

약 29% 가 느린 성능입니다.

이는 실제로 서비스에 들어가게 되면, 많은 요청이 몰렸을 때 병목현상으로 이어지게 되지 않을까요?

도대체 이런 현상이 왜 일어나는지 모르겠습니다.

그냥 역순으로 탐색하면 된다고 하지 않았나요?

하지만, 이유는 아래에서 찾아볼 수 있습니다.

즉, 단방향 연결리스트를 활용한 구조와, 페이지 잠금의 적합성 때문에, 역순으로 데이터를 탐색할 때 더 느렸던 것입니다.

물론 위에서도 말했듯, 위의 쿼리와 같은 단일 쿼리의 사이즈가 굉장히 작은 쿼리 같은 경우는 영향이 크지 않을 수 있습니다.

하지만, 이러한 쿼리가 많아진다면 어떻게 될까요?

인덱스의 정렬조건이 굉장히 큰 영향을 끼칠 수 있지 않을까요?

그렇기 때문에, 인덱스 정렬조건이 정말 중요한 것입니다.

이제부터는 인덱스의 가용성과 효율성이라는 굉장히 지루한 주제를 다뤄보도록 하겠습니다.

지루할 수 있지만, 실질적으로 쿼리 튜닝을 할 때, 해당 개념을 확실히 알고 있어야지 진행할 수 있으므로 굉장히 중요한 정보라고 할 수 있을 것 같습니다.

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

그리고 다중 칼럼 인덱스가 두 종류의 케이스로 위와 같이 나누어져 있다고 생각해볼게요.

dept_no 가 먼저 있는 경우는 먼저 d002 를 찾고 거기서 emp_no 를 이용하여 찾게 될 것입니다.

하지만, emp_no 가 먼저 걸린 경우는 emp_no 를 통해서 10114 다음으로까지 작업범위를 설정하고, dept_no 로 필터링을 하게 되겠죠.

위와 같은 두 케이스에서는 케이스 A 가 굉장히 인덱스를 효율적으로 사용했다고 말할 수 있을 것 같습니다.

케이스 A 는 dept_no, emp_no 모두 작업 범위 결정 조건이라고 할 수 있고, 케이스 B 와 같은 경우는 emp_no 만 작업 범위 결정 조건이고 dept_no 는 필터링 조건으로만 활용된 것이라고 할 수 있습니다.

즉, 많이 발생하는 쿼리, 그리고 다중 칼럼 인덱스의 순서가 인덱스를 효율적으로 사용할 수 있느냐에 지대한 영향을 미친다는 것입니다.

아무래도 쿼리의 기능을 많이 개선하기 위해서는 작업 범위 결정 조건이 쿼리에 많이 들어가 있는 것이 낫습니다.

아무래도 작업 범위를 줄여주니까 쿼리의 성능을 굉장히 높여주겠죠

또한 신기한 것이 있습니다.

문자열을 정렬한다고 했을 때, 왼쪽 값부터 가중치를 많이 주면서 정렬을 진행합니다.

그렇기 때문에 아래와 같은 현상이 발발하게 됩니다.

위와 같이 인덱스가 형성되어 있을 때, 위와 같은 쿼리가 발생하게 되면, 왼쪽 값들을 기준으로 정렬한 값들을 전혀 활용하지 못하게 됩니다.

당연합니다.

왼쪽 값은 어떤 값이어도 되고, 뒤에 값이 정해져 있기 때문에, 후자가 똑같이 mer 이라고 해도 앞의 글자에 따라 여기저기 흩어져 있을 것입니다.

그래서 이것과 똑같은 원리로

인덱스가 만일 dept_no, emp_no 로 설정되어 있다면

위 쿼리도 효율적으로 진행하지 못합니다.

당연합니다. emp_no 는 막 흩어져 있을테니까요!

그리고 인덱스의 왼쪽 값 기준 규칙은 Group By 절이나 Order By 절에도 똑같이 적용이 됩니다.

가용성과 효율성 판단

B-Tree 인덱스의 특성상 다음 조건에서는 사용할 수 없습니다.

여기서 말하는 사용할 수 없다는 의미는 작업 범위 결정 조건으로 사용할 수 없다는 것을 의미하고, 고로 효율적으로 사용할 수 없다는 것을 의미합니다.

null 로도 작업 범위 결정 조건을 결정할 수 있습니다.

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

위와 같이 인덱스가 설정되어 있다고 가정했을 때, 아래와 같이 판단할 수 있습니다.

고로 이 규칙에 따라, 아래와 같은 쿼리들에 대한 가용성과 효율성은 아래와 같이 판단할 수 있습니다.

위는 인덱스를 사용할 수 있는 경우와 사용할 수 있는 경우를 나타낸 것입니다.

모두 i 번째 조건을 제외하고는 동등 비교 형태를 띄고, 나머지만 대소 비교를 실행하는 것을 볼 수 있습니다.

<>(!=) 는 항상 작업 범위 결정 조건으로 활용되지 못하고 체크 조건으로 활용되는 것도 볼 수 있습니다.

그리고 우측 Like 연산도 작업 범위 결정을 할 수 있습니다.

R-Tree 인덱스

이제는 조금 특별한 인덱스를 살펴봅시다.

핸드폰이 대중화되면서, 위치 정보를 기반으로 서비스를 제공하는 사례가 많아졌습니다.

그렇기 때문에, MySQL 은 공간 확장이라는 개념을 개발하였습니다.

말 그대로 공간을 효율적으로 혹은 쉽게 처리할 수 있는 방법을 제공하는 것입니다.

구조 및 특성

각각의 MySQL 은 공간 정보의 저장 및 검색을 하기 위해 여러가지 기하학적 도형 정보를 관리할 수 있는 데이터 타입을 제공합니다.

제공하는 데이터 타입은 다음과 같습니다.(? 은 아래 3개의 유형을 모두 포함하는 와일드 카드 같은 것입니다.)

우리는 그리고 공간 데이터를 이해하기 위해서는 MBR 이라는 개념을 알고 있어야 합니다.

해당 도형을 감싸는 최소 크기의 사각형을 의미하게 됩니다.

이 사각형들의 포함 관계를 B-Tree 로 구현한 인덱스가 R-Tree 인덱스입니다.

어때요 쉽지 않나요?

우리 실제로 도형에 이를 대입해봅시다.

아래와 같은 공간 데이터가 있다고 가정해볼까요?

이들을 감싸는 MBR 들을 레벨에 따라 정의할 수 있습니다.

최상위 레벨, 차상위 레벨, 최하위 레벨을 아래와 같이 정의할 수 있죠.

뭔가 익숙하지 않나요?

그렇습니다. 최상위 레벨은 루트 노드, 차상위 레벨은 브랜치 노드, 최하위 레벨은 리프 노드인 것입니다.

이가 바로 R-Tree 인 것입니다.

아래는 이를 표현한 그림입니다.

R-Tree 인덱스의 용도

R-Tree 는 검색에 많이 사용된다.

실제로 우리 괜찮을 지도에서도 사용되었었다.

기능을 제대로 활용할 수 없어, 엄청난 효과는 보지 못했지만, 쥬니가 멋진 도전을 해주었다는 사실에는 변함이 없다.

인덱스를 활용하는 방법은 크게 두가지가 있을 것 같다.

특정 영역에 포함되어 있는지를 확인

혹은, 떨어진 거리들로 탐색하는 경우이다.

아직은 후자는 성능이 좋지 않아, 특정 영역에 포함되어 있는지를 확인한 후 떨어진 거리들로 탐색하는 경우가 인덱스를 효율적으로 사용할 수 있는 방법이다.

즉, 작업 범위 결정 조건으로 MBR 안에 Contains 해 있는지를 확인하는 것이다.

아 그리고 ST_Distance_sphere 를 활용하여 꼭짓점에 포함되어 있는 좌표를 검색 결과에서 지울 수도 있었다.

전문 검색 인덱스

지금까지 살펴본 인덱스 알고리즘은 일반적으로 크지 않은 데이터 또는 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이었지만, 지금부터 학습하는 인덱스는 다르다.

문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는, 전문 검색에 사용되는 인덱스가 따로 있다.

B-Tree 와 같이 일반적인 용도의 인덱스는 활용할 수 없기 때문이다. (키의 값이 너무큼)

인덱스 알고리즘

전문에서도 키워드가 있을 것이다.

이들을 가지고 인덱스를 구축하는 것이 굉장히 중요한데, 인덱싱하는 기법이 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

어근 분석 알고리즘

MySQL 서버의 전문 검색 인덱스는 위와 같이 두가지 중요한 과정을 거쳐서 색인 작업이 수행된다.

불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 말한다.

딱 봐도 신기하듯이 해당 데이터들을 커스터마이징 하는 방법도 다양하니, 이따가 한번 살펴보자.

MeCab 이라는 일본어를 위한 형태소 분석 프로그램이 있는데, 이게 한국어가 일본어와 많이 비슷하기 때문에 이를 통해서 한글 분석이 가능하다.

하지만, 제대로 동작하려면 단어 사전, 문장을 해체해서 각 단어의 품사를 식별할 수 있는 문장의 구조 인식이 필요하다.

n-gram 알고리즘

이는 형태소를 분석하는 어근 분석 알고리즘과 다르게 굉장히 간단하다.

n-gram 의 n 은 한 단어에서 각 단위를 n 글자로 나눈다는 의미이다.

이를 조금 더 살펴볼까?

위와 같이 2-gram 은 각 단어를(공백으로 구분된) 중복을 허용하여 2글자씩 나눈다.

즉, 2-gram 에서 10글자라고 하면 10 - 1 개의 토큰이 나오는 것이다.

그리고, 이를 기반으로 인덱스를 구성한다.

그리고 인덱스를 구성하기 전에 불용어를 걸러내는 과정이 있다.

불용어는 아래와 같은 테이블들에서 확인할 수 있다.

불용어 전체 목록은 위와 같다.

불용어 필터링 과정을 거쳐서 생성된 인덱스 엔트리는 위와 같다.

물론 전문 검색을 더 빠르게 하기 위해 2단계 인덱싱이 가능하다고 한다 (ex) 프론트엔드와 백엔드 인덱스) 또한 Merge-Tree 라는 구조로 구성할 수도 있다고 하는데, 우리는 이 정도로만 알면 될 것 같다.

불용어 관리

불용어는 사용자로 하여금 혼란을 줄 수도 있다.

전문 검색 인덱스를 사용한다면 이를 관리하는 방법을 아는 것도 굉장히 중요해보인다.

시스템 변수를 위와 같이 설정하여, 불용어를 아예 비활성화 할 수 있고 (서버를 시작할 때만 인지하기에, 서버를 재시작해야한다)

또한, 불용어를 사용자가 직접 정의한 파일의 경로를 설정하여 해당 경로의 파일에서 가져올 수도 있다.

또한, 위와 InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시할 수도 있다. (위 설정은 동적으로 가능하다.)

위는 사용자가 직접적으로 파일을 활용하여 불용어를 정의하는 방법이다.

또한 위와 같이 불용어의 목록을 테이블로 저장하여, InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 엔지에서만 사용할 수 있게 하는 방법도 있다.

전문 검색 인덱스 사용법

위와 같은 경우 인덱스가 활용이 되고

예시로는 위와 같은 경우가 있다, 첫번째 쿼리는 그냥 Like 연산으로 실행되고, 두번째 쿼리는 전문 검색 인덱스가 활용이 된다.

함수 기반 인덱스

이제부터는 변형된 칼럼을 인덱스로 활용하는 방법이다.

쓸 수 있는 방법은 아래와 같다.

내부구조는 일반적인 B-Tree 와 동일하고, 인덱싱할 값을 계산하는 과정의 차이(인덱스 만들 때?)만이 존재한다.

가상 칼럼을 이용한 인덱스

위와 같은 테이블이 있을 때

위와 같이 가상 칼럼을 만들어서 인덱스로 등록할 수 있다.

이제부터는 full_name 칼럼에 대한 검색도 새로 만들어진 ix_fullname 인덱스를 이용해 실행 계획이 생성되는 것을 확인할 수 있다.

아 그리고 가상 칼럼을 생성하는 것은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에, 테이블의 구조가 변경된다는 단점이 있다고 한다.

함수를 이용한 인덱스

그럼 테이블의 구조를 변경하지 않고 인덱스를 활용하는 방법이 있지 않을까 하는 찰나에 바로 있다고 나왔다.

아래와 같이 함수 인덱싱을 진행하고

인덱스에 명시한 함수를 그대로 써주면 인덱스를 타는 것을 확인할 수 있다.

만일 제대로 인덱스를 타지 않는다면

위 3개의 설정을 확인해보자.

멀티 밸류 인덱스

모든 레코드는 1개 칼럼에 1개의 매핑되는 값을 가진다.

그렇기 때문에, 인덱스를 생성하기 굉장히 애매하다.

심지어 MySQL 은 이를 지원하지 않았었기 때문에 인덱스도 존재하지 않았고, 그렇기에 MongoDB 와 계속해서 비교되어왔다. (MongoDB 는 이미 지원하고 있었음)

하지만, 이제는 MySQL 8.0 에 들어서면서 멀티 밸류 인덱스가 도입이 되었고, MongoDB 와 비슷한 성능을 내고 있다.

Json 칼럼이 위와 같이 있다고 가정해보자.

인덱스도 위와 같이 CAST 를 해주어 등록해주었다.

또한 해당 인덱스를 타기 위해서는 위와 같은 함수들을 사용해야 한다.

실제로 위와 같이 credit_scores 에 360 이 포함된 레코드를 찾으라고 했을 때, 해당 레코드를 기반으로 인덱스가 실행되는 것을 확인할 수 있다.

그리고 찾을 때 문법은 함수 인덱싱 기법과 동일하게 인덱스 할 때 입력한 값으로 그대로 실행하는 것을 볼 수 있다.

이렇게 되면, 인덱스를 통해서 쿼리가 실행이 된다.

아 그리고, 곧 CHAR/VARCHAR 타입의 배열 형태 CAST 와 멀티 밸류 인덱스가 지원될 것이라고 예상한다고 한다.

클러스터링 인덱스

클러스터링이란 여러 개를 하나로 묶는다는 의미로 주로 사용됩니다.

지금부터 설명할 클러스터링 인덱스도 이와 크게 다르지 않습니다.

설명에 앞서, 해당 인덱스는 InnoDB 에서만 지원된다는 점을 유의해주시길 바랍니다.

클러스터링 인덱스는 프라이머리 키에 대해서만 적용이 되는 내용입니다.

프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현합니다.

또한, 프라이머리 키 값으로 인해서 저장되는 위치도 바뀝니다.

그렇기 때문에 클러스터링 인덱스는 어떻게 보면 저장 방식을 의미하고, 그렇기 때문에 클러스터링 인덱스는 클러스터링 테이블이라고 불리기도 합니다.

클러스터링 테이블 구조를 그림으로 나타내면 위와 같습니다.

순서대로 저장되어 있는 것들을 볼 수 있죠.

리프 노드에는 레코드의 모든 칼럼이 같이 저장되어 있음을 알 수 있습니다.

굉장히 거대한 인덱스라고 할 수 있습니다.

근데, 만일 위의 레코드 중 하나라도 변경이 된다면 어떠한 일이 벌어질까요??

위와 같은 쿼리가 발생했을 때, 데이터 구조는 위와 같이 변경되게 됩니다.

인덱스의 삭제와 삽입이 동시에 이루어집니다.

그렇기 때문에 굉장히 느릴 수 밖에 없긴합니다.

만일 프라이머리 키가 없는 경우에는 어떻게 될 수 있을까요??

위와 같이 프라이머리키가 없는 경우, 다른 칼럼의 값을 프라이머리 키로 삼거나, 혹은 아무도 모르게 생성합니다.

아무도 모르게 생성하는 경우는, 사용자가 어떠한 경우라도 쿼리에 이를 언급하지 못하게 됩니다.

굳이 그럴 필요는 없겠죠 당연히?

프라이머리 키를 명시적으로 생성합시다.

세컨더리 인덱스에 미치는 영향

여기서부터는 굉장히 신기합니다.

도대체 왜 세컨더리 인덱스는 프라이머리키를 논리적 주소로 가지고 있었는지, 그것이 궁금했는데, 클러스터링 인덱스를 학습하고 나니 그 이유를 알 수 있었습니다.

만일, 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면? 테이블이 변경될 때마다, 모든 인덱스를 초기화해주어야 할 것입니다.

실제로는 클러스터링 인덱스만을 수정해도 되는 상황임에도 말입니다.

그렇기 때문에 논리적 주소로 프라이머리 키를 가지고, 테이블이 변경되어 물리적인 저장위치가 변경되더라도 클러스터링 인덱스만 수정하면 되게끔 된 것입니다.

그래서 세컨더리 인덱스로 조회시 아래와 같은 현상이 일어나게 됩니다.

조금 더 복잡하긴 하지만, 클러스터링 인덱스가 더 큰 장점을 제공하기 때문에, 성능 저하는 걱정하지 않아도 된다고 합니다.

클러스터링 인덱스의 장점과 단점

저는 솔직히, 장점의 커버링이 이해가 되지 않았습니다.

근데, 최고의 선생 chat gpt 에게 물어보니, 세컨더리 인덱스에서 찾고, 클러스터링 인덱스로 찾았을 때, 해당 리프노드에 레코드 값들이 전부 저장되어 있으니, 실제 레코드에 접근하지 않고 커버링 인덱스를 실행할 수 있다는 것이었습니다.

또한 세컨더리 인덱스는 프라이머리 키를 키값으로 가지고 있기 때문에, 프라이머리 키값이 커질 수록, 용량에 영향을 끼친다는 점도 알 수 있습니다.

여기서 보면 알 수 있듯이, 클러스터링 인덱스는 쓰기 연산을 포기하고, 읽기 연산의 성능을 확실하게 높이는 방법입니다.

보통 서비스가 쓰기연산에 비해 읽기연산이 비율이 높기 때문에, 이득이 되는 판단이라고 할 수 있습니다.

클러스터링 인덱스 사용 시 주의사항

프라이머리 키의 크기가 너무 커지게 되면, 아래와 같은 문제가 발생하게 됩니다.

테이블에 보통 4 ~ 5 개의 인덱스가 존재한다고 하면, 위와 같은 계산이 가능합니다.

DB 에 저장된 데이터가 많아지면 많아질 수록 테이블의 총 인덱스 크기는 기하급수적으로 늘어나게 됩니다.

프라이머리 키는 Auto-increment 보다는 업무적인 칼럼으로 생성 (가능한 경우에만)

클러스터링 인덱스는 엄청난 읽기 성능의 향상을 보여줍니다.

그러니, 서비스에서 빈번하게 사용이 되어야겠죠.

그러기 위해서는 업무적인 칼럼으로 생성해, 조회 쿼리에 많이 포함되게끔 해야 합니다.

Auto-Increment 칼럼을 인조 식별자로 사용하는 경우

하지만, 프라이머리 키 값이 너무 커지면 위에서 언급한 문제들로 인해서 성능 저하로 이어지거나, 디스크가 폭발하는 현상이 발발할 수도 있으니, 때에 따라서 인조 식별자를 사용하는 것도 좋을 것 같습니다.

유니크 인덱스와 일반 세컨더리 인덱스의 비교

유니크 인덱스가 무조건 성능상 엄청 좋다고 생각하면 그건 오산이다.

아무래도 유니크한 값을 가져오기 때문에, 성능상 큰 향상이 있을 거라고 예상하지만, 실제로 가져오는 레코드 개수만 같다면, 중복 처리를 하는 것은 CPU 가 해주기 때문에 성능상 차이는 미미짱합니다.

하지만, 이는 읽기에서 포함되는 이야기이다.

쓰기 연산으로 가면 조금 달라지게 된다.

유니크 인덱스의 특징상, 중복되는 값이 존재해서는 안된다. 그렇기 때문에, 쓰기 연산을 실행하게 되면 중복되는 값을 체크해야하는데, 이로 인해서, 작업을 지연시키지 못한다.

그러니, 인덱스의 변경사항을 저장해놓는 체인지 버퍼도 당연히 사용하지 못하게 된다.

또한, 유니크 인덱스는 읽기 연산을 할 때 읽기 잠금을 사용하고 (읽기 연산 시에 다른 트랜잭션에서 삽입 연산을 진행하게 되면 중복 체크에 차질이 생길 수 있기 때문에?), 쓰기 연산을 진행할 떄에는 쓰기 잠금을 사용하게 된다.

이러한 잠금으로 인해서 잠금 경합이 발생하게 되고, 데드락도 빈번하게 발생한다고 한다.

그리고 위와 같은 선택도 좋지 않은 선택이라고 할 수 있다.

유일성이 꼭 보장되어야 하는 컬럼이 있는 경우 유니크 인덱스를 생성하되, 꼭 필요하지 않다면, 유니크 인덱스 보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방법도 한번 고려해보는 것이 좋다.

외래키

외래키 관리는 최대한 효율적인 관리를 위해, 위와 같은 특징을 가지고 진행이 되게 된다.

이를 실제로 확인해보기 테스트를 진행해보자.

위와 같은 td_parent, td_child 와 같은 테이블이 있다고 가정해보자.

그리고 각각, parent1, parent2, child-100 이 삽입이 되어있다.

위 경우, 먼저 부모 테이블 트랜잭션에서 변경사항을 발생시켰다.

그 다음에, 자식 테이블 트랜잭션에서 변경사항을 발생시켰다.

이 때, 자식 테이블 트랜잭션은 부모 테이블 트랜잭션을 기다리고 있다는 것을 알 수 있다.

왜 그럴까??

먼저 부모 테이블 트랜잭션에서 id = 2 인 레코드에 대해서 쓰기 잠금을 획득했다.

그 다음에 자식 테이블 트랜잭션에서 외래키 칼럼 변경을 진행하게 될 때, 외래키 제약조건의 특성으로 인해서 부모 테이블을 확인하게 되고, 부모 테이블의 변경하려는 레코드에 잠금이 걸려있으니 기다리는 것이다.

이는 외래키 특성의 1번에 속하게 된다.

두 번째 예제이다.

이 경우는 먼저 child 가 변경이 된다.

그리고서 parent 가 변경이 되는데, 이 때, parent 트랜잭션이 대기하게 된다.

이러한 현상이 발생하는 이유는, child 가 변경이 되면서, 쓰기 잠금을 획득하고, parent 가 변경이 될 때, 테이블을 생성할 때, 명시했던 On Delete Cascade 제약조건으로 인해, parent 가 삭제된다면 속한 모든 child 들을 삭제해야 하기 때문에, child 의 변경 사항을 모두 기다리는 것이다.

그렇지 않으면, parent 가 삭제되면서 child 트랜잭션에서 변경사항이 적용되기 이전에 해당 레코드가 삭제되고, 이는 장애를 불러일으킬 수도 있어서 그런 것일 것 같다 허허허

김재연

끊임없이 '성장'하는 개발자 김재연입니다.

이전 포스트

Real MySQL 8.0 4장

다음 포스트