들어가며

해당 포스트는 Real MySQL 8.0 (1권) 을 보며 공부한 내용을 기록하기 위해 작성한 포스트이다.

---

디스크를 읽는 방식

데이터를 저장하고 읽는 부분이 성능에 가장 치명적이고 그래서 스토리지 엔진이 아무리 발달했다고 하더라고 쿼리 튜닝은 성능 향상의 핵심적인 부분이다. 그래서 인덱스에 대해서 알아보기 전에 디스크를 읽는 방식을 짚고 넘어가고자 한다.

SSD 와 HDD

SSD 는 기존 HDD 에서 데이터 저장용 플래터(원판)을 제거하고 그 대신 플래시 메모리르 장착했다. 원판을 회전시킬 필요 없어졌기 때문에 보다 빠르게 데이터를 읽어낼 수 있다.

순차 I/O 와 랜덤 I/O

순차 I/O 는 디스크의 헤더를 움직이지 않고 많은 양의 데이터를 읽어내는 방식을 말한다. 이 방식도 SSD가 HDD보다 더 빠르긴 하지만 조금 빠르거나 비슷한 수준이다. SSD 와 HDD 가 많은 차이를 보이는 부분은 랜덤 I/O 이다. 실제로 DB 서버에서 순차 I/O는 비중이 크지 않고 거의 대부분의 비중을 랜덤 I/O가 을 차지하고 있다.

읽기 방식에 따른 성능 차이

3 개의 페이지를 디스크에 기록해야 하는 상황이라고 가정을 하고 순차 I/O와 랜덤 I/O의 차이점에 대해 알아보자.

• 순차 I/O : 3 개의 페이지를 디스크에 기록하기 위해 1 번의 시스템 콜을 요청한다.
• 랜덤 I/O : 3 개의 페이지를 디스크에 기록하기 위해 3 번의 시스템 콜을 요청해야 한다.

시스템 콜의 횟수는 디스크를 이동시키는 횟수와 동일하다. 즉, 순차 I/O는 디스크의 헤더를 1번만 이동시켜도 됐지만, 랜덤 I/O는 디스크의 헤더를 3번이나 이동시켜야 했고, 순차 I/O보다 3배 정도 느리다고 볼 수 있다.

결과적으로, 디스크의 성능 차이는 디스크 헤더의 위치 이동없이 얼마나 많은 양의 데이터를 한 번에 기록하느냐로 결정된다고 볼 수 있다.

( InnoDB는 이러한 이유로 버퍼풀을 사용해 쓰기 버퍼링을 지원한다 )

하지만 랜덤 I/O 를 순차 I/O로 튜닝할 수 있는 방법은 많지 않다. 그래서 쿼리를 튜닝한다의 의미는 보통 랜덤 I/O 시 정말 필요한 데이터들만 읽을 수 있도록 튜닝하는 것을 의미한다고 한다.

인덱스 레인지 스캔 : 데이터를 읽기 위해 랜덤 I/O를 사용
풀 테이블 스캔 : 데이터를 읽기 위해 순차 I/O를 사용
-> 인덱스 스캔으로 이득을 얻을 수 있는 데이터 양의 임계치가 존재하게 된다. (순차 I/O가 더 빠르기 때문) 보통 읽어야 하는 레코드의 양이 전체 테이블 레코드의 20~25% 를 넘어간다면 풀 테이블 스캔이 더 효율적이다.

---

인덱스?

인덱스는 항상 정렬된 상태를 유지한다. 즉, SortedList 자료 구조와 비슷하고 SortedList의 장단점을 통해서 인덱스의 장단점을 알아보도록 하자.

SortedList는 항상 정렬된 상태를 유지하기 때문에 원하는 데이터를 아주 빠르게 읽을 수 있다. 하지만, 데이터를 저장(INSERT, UPDATE, DELETE)할 때마다 정렬을 위한 작업이 수반된다. 결론적으로 DBMS는 SELECT의 성능을 위해 저장 성능(INSERT,UPDATE,DELETE)을 희생시켰다고 볼 수 있다.

B-TREE INDEX

• DBMS의 인덱싱 알고리즘 중 가장 일반적으로 사용되고 있는 알고리즘

• 트리의 최상위에는 루트 노드가 존재하고, 그 하위에는 브랜치 노드, 그 하위에는 리프 노드가 존재한다.

• 인덱스는 테이블의 키 컬럼만 가지고 있기 때문에 데이터의 나머지 컬럼들의 데이터가 필요하면 실제 데이터 파일로 이동해서 데이터를 가져와야 한다. 이를 위해 리프 노드에는 키 컬럼과 해당 키의 실제 위치 값을 [KEY, VALUE] 형태로 가지고 있다.

인덱스의 KEY 값들은 정렬된 상태를 유지하고 있기 때문에 실제 데이터들도 INSERT 되는 순서에 따라 물리적으로 순차적으로 저장되어 있을 것이라고 생각할 수 있다. 하지만, 데이터 파일의 레코드는 INSERT만 일어나는 것이 아니라 다른 작업들도 수행되기 때문에 순차적으로 저장되지 않고 임의의 순서대로 저장된다.

InnoDB 에서의 레코드 클러스터링

다른 RDBMS 에서는 위와 같이 레코드들이 임의의 순서대로 저장되지만, InnoDB에서는 레코드들이 클러스터링 되어서 디스크에 저장된다. 즉, 기본적으로 프라이머리 키 순서로 정렬되어 저장된다.

InnoDB의 인덱스

InnoDB 스토리지와 MyISAM 스토리지 엔진 간에는 세컨더리 인덱스가 데이터 파일의 레코드를 찾아가는 방법에 큰 차이점이 존재한다.

• InnoDB : 세컨더리 인덱스는 데이터 파일의 레코드를 찾기 위해 프라이머리 키 인덱스를 거쳐야 한다
• MyISAM : 세컨더리 인덱스가 데이터 파일 속 레코드의 물리적인 주소를 가지고 있어서 바로 접근할 수 있다.

InnoDB는 PK 를 일종의 논리적인 주소로 사용한다. 그렇기 때문에 기본적으로 PK 값을 KEY 로 사용하는 클러스터링 인덱스 테이블을 만들어서 제공한다.

여기서 성능적인 측면에서 의문점이 생길 수 있다. 왜냐면 바로 접근하는게 아니라 클러스터링 인덱스 테이블을 한번 거쳐야만 데이터에 접근할 수 있기 때문이다. 이 부분은 아래의 클러스터링 테이블 에서 자세하게 언급하도록 하겠다.

인덱스 KEY 추가/삭제/변경/검색

인덱스 KEY 추가

인덱스는 위에서 언급한 대로 B-TREE를 주로 사용한다. KEY 가 새로 추가될 때는 (1. 저장될 위치 탐색 작업), (2. 리프 노드가 꽉 차 있는 경우 리프 노드 분리 작업) 등에 의해서 KEY 쓰기 작업에 많은 비용이 들어간다.

MyISAM 이나 MEMORY 스토리지 엔진의 경우 인덱스 KEY 의 INSERT 가 발생하면 즉시 새로운 키 값을 B-TREE에 적용하지만, InnoDB 같은 경우는 인덱스 KEY 추가 작업을 지연시켜 나중에 처리하는 방법을 사용하며 지능적으로 처리한다.( 이 과정에서 체인지 버퍼를 사용한다 ) 다만, PK, 유니크 인덱스의 경우에는 중복 체크가 반드시 필요하기 때문에 즉시 추가하거나 삭제한다.

인덱스 KEY 삭제

KEY 값을 삭제하는 작업은 상대적으로 간단하다. 그냥 리프노드에서 해당 KEY 값을 찾아서 마킹만 해주면 되기 떄문이다. 마킹된 인덱스 공간은 후에 재활용되거나 방치하게 된다. MySQL 5.5 이상부터는 이 작업 또한 체인지 버퍼를 사용해서 지연처리 된다.

인덱스 KEY 변경

인덱스 KEY가 변경되는 과정은 단순히 값만 변경시키는 것이 아니라 삭제 -> 추가 과정을 거쳐야 하기 때문에 복잡하다고 할 수 있다. 이 작업또한 체인지 버퍼를 사용해 지연 처리될 수 있다.

인덱스 KEY 검색

인덱스를 활용해 검색을 하는 과정은 트리 탐색으로 이루어지기 떄문에 굉장히 빠르다. 다만 KEY의 뒷부분을 기준으로 검색을 하거나 (ex. like %hello), KEY 값에 변형을 준 후 검색을 하려고 하는 경우 (ex. 함수나 연산 수행 결과로 정렬 하거나 검색) 에는 B-TREE의 빠른 검색 기능을 사용할 수 없다.

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

1. 인덱스 레인지 스캔

• 인덱스 접근 방법 가운데 가장 일반적
• 검색해야 할 인덱스의 범위가 결정됐을 때 사용

인덱스를 사용해서 검색을 할 떄는 브랜치 노드를 거쳐 리프 노드에 도달해서야 최종적인 검색의 시작 위치를 알 수 있다. 검색의 시작 위치를 알아내고 난다면 그냥 순서대로 읽기만 하면 된다.

리프 노드에는 레코드들의 데이터 파일 위치가 저장되어 있고 이 레코드들을 읽을 때 랜덤 I/O 가 발생한다. 커버링 인덱스 의 경우에는 레코드를 읽는 랜덤 I/O 과정이 발생하지 않는다.

2. 인덱스 풀 스캔

• 인덱스를 사용하지만 인덱스 레인지 스캔과 다른 점은 인덱스의 처음부터 끝까지 읽는다는 점이다
• 대표적으로 쿼리 조건절의 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우에 사용된다.
  • ex) 인덱스는 (A,B,C) 순서지만, 조건절은 B나 C로 검색하는 경우

인덱스 풀 스캔을 사용하는 경우는 인덱스의 컬럼으로만 쿼리문을 만족시킬 수 있는 경우이다. 즉, 레코드를 읽을 필요없는 경우에만 사용된다. 추가적인 컬럼들이 필요하다면 풀 테이블 스캔이 더 빠르기 때문이다.

3. 루스 인덱스 스캔

• 말 그대로 인덱스를 듬성듬성하게 읽는 것을 의미한다
• 기본적으로 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요하지 않은 인덱스 키 값을 만나면 무시하고 다음으로 넘어가는 형태로 처리한다
• 일반적으로 MIN(), MAX() 같은 함수를 최적화하는 경우에 사용된다

4. 인덱스 스킵 스캔

• 비교 조건에 없는 인덱스 키 값을 건너 뛰고 나머지 인덱스 키들로 검색을 하게 해주는 기능이다
• WHERE 조건절의 검색을 위해 용도를 넓힌 결과다

예시를 통해 알아보자. [이름, 생년월일] 으로 만들어진 인덱스가 존재하고 SELECT 이름 FROM 학생 WHERE 생년월일 >= 1996년 라는 쿼리문을 처리해야 한다고 가정해보자.

인덱스 스킵 스캔이 없는 경우에는 [이름, 생년월일] 중 [이름] 컬럼에 대한 조건이 없기 떄문에 인덱스를 효율적으로 이용할 수 없다. 즉 꼭 필요한 부분에만 접근할 수 없게 된다.

인덱스 스킵 스캔을 적용한다면, [이름] 컬럼에 대한 유니크한 값을 모두 조회해서 주어진 쿼리에 [이름] 컬럼에 대한 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리하게 된다.

즉, WHERE 생년월일 >= 1996년 라는 쿼리가 WHERE 이름 = '유재석' AND 생년월일 >= 1996년, WHERE 이름 = '강호동' AND 생년월일 >= 1996년, WHERE 이름 = '조세호' AND 생년월일 >= 1996년 등의 쿼리로 처리되게 되는 것이다.

쿼리를 수행하기 전에 조건절에 없는 인덱스 컬럼들의 유니크 값들을 추출하는데, 루스 인덱스 스캔과 동일한 방식으로 값들을 검색하면서 모든 값을 추출하고 그 결과를 이용해서 인덱스 스킵 스캔을 실행한다.

인덱스 스킵 스캔 단점

인덱스 스킵 스캔은 MySQL 8.0 부터 도입되었기 때문에 아직 다음과 같은 단점들이 존재한다.
1. 조건절에 없는 인덱스 선행 컬럼의 유니크 개수가 적어야 함
2. 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함 (커버링 인덱스)

5. 다중 컬럼 인덱스

• 두 개 이상의 컬럼으로 이루어진 인덱스를 말한다
• 복합 칼럼 인덱스, Concatenated Index 라고도 한다
• 인덱스 컬럼의 순서대로 정렬된다
  • 1번 컬럼 정렬 -> 1번 컬럼에 의존해 2번 컬럼 정렬 -> 2번 컬럼에 의존해 3번 컬럼 정렬 -> ...

B-TREE 인덱스 정렬 및 스캔 방향

인덱스는 항상 오름차순 혹은 내림차순으로 정렬된 상태를 유지한다. 하지만 오름차순으로 정렬됐다고 항상 오름차순으로만 읽을 수 있는 것은 아니다.

인덱스 스캔 방향

인덱스는 필요에 따라 내림차순의 인덱스를 역순(오름차순)으로 읽을 수 있고, 만들 때 부터 오름차순으로 만들어버릴 수 있다. 하지만 역순 스캔(리프 노드를 오른쪽 -> 왼쪽으로 읽는 방식)은 정순 스캔보다 느리다. 그 이유는 다음의 두 가지 이유에 의해서다.

• 페이지 잠금이 인덱스 정순 스캔에 적합한 구조이다
• 페이지 내에서 인덱스 레코드가 단방향으로 연결된 구조다
  • InnoDB 페이지 내부에서 레코드들이 단방향으로 링크를 가지고 있다

인덱스의 가용성과 효율성 판단

인덱스를 작업 범위 결정 조건으로 사용할 수 없는 경우는 다음과 같다. 다음의 경우들을 사용할 경우 인덱스를 사용해 탐색의 범위를 효율적으로 줄일 수 없다고 생각할 수 있다.

1. NOT-EQUAL로 비교된 경우
2. LIKE '%??' 형태로 문자열 패턴이 비교된 경우 (뒷부분 일치 조건)
3. 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교하는 경우
   -> WHERE DAYOFMONTH(column) = 1
   -> WHERE SUBSTRING(column, 1, 1) = 'X'
4. NOT-DETEMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
   -> WHERE column = deterministic_funtion()
5. 데이터 타입이 서로 다른 비교 (인덱스 컬럼 타입을 변환해야 비교 가능한 경우)
   -> WHERE char_type_column = 10
6. 문자열 데이터 타입의 콜레이션이 다른 경우
   -> WHERE utf8_column = euckr_column

---

멀티 밸류 인덱스

전문 검색 인덱스를 제외하고 모든 레코드는 1건이 1개의 인덱스 키 값을 가진다. 하지만 멀티 밸류 인덱스는 하나의 데이터 레코드에 여러 개의 키 값을 가질 수 있는 형태의 인덱스다.

클러스터링 인덱스

클러스터링이란 여러 개를 하나로 묶는다는 의미로, MySQL 서버에서 클러스터링은 테이블의 비슷한 레코드를 묶어서 저장하는 형태로 구현된다. 이는 주로, 비슷한 값들을 동시에 조회하는 경우가 많다는 점에서 착안한 것이다.

• 클러스터링 인덱스는 테이블의 PK에 대해서만 적용되는 내용이다
• 여기서 중요한 점은 PK에 의해서 레코드 저장 위치가 결정된다는 점이다. 또한 PK 값이 변경되면 레코드의 물리적 저장 위치가 변경되어야 한다는 것을 의미하기도 한다. 즉, PK 값으로 클러스터링 된 테이블은 PK 자체에 의존성이 상당히 크기 때문에 신중히 결정해야 한다.
• 클러스터링 인덱스는 PK 에 의해 레코드 저장 위치가 결정되기 때문에 알고리즘보다 테이블의 레코드 저장 방식이라고 볼 수 있다. 또한 클러스터링의 기준이 되는 PK는 클러스터링 키라고도 표현한다.

일반적으로 B-TREE 인덱스도 인덱스 키 값을 기준으로 정렬되어 저장된다. 하지만 이를 클러스터링 인덱스라고 부르지 않는다. 테이블의 레코드가 키 값으로 정렬되어 저장된 경우만 클러스터링 인덱스 혹은 클러스터링 테이블이라 부른다.

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

• MyISAM이나 MEMEORY 테이블 같은 클러스터링 되지 않은 테이블은 레코드가 INSERT 된 후에는 절대 움직이지 않는다. 프라이머리 키나 세컨더리 인덱스의 각 키는 실제 데이터 레코드의 주소(ROWID)를 이용해 실제 레코드에 접근한다.
• 이런 경우, 클러스터링 테이블의 KEY값이 변경되면 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 한다.
• 이런 오버헤드를 줄이기 위해 InnoDB는 모든 세컨더리 인덱스는 실제 주소가 아닌 PK 값을 저장하도록 구현된 것이다.

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

• 장점
  • PK 검색 시 처리 성능이 매우 빠르다 (특히 PK 기준 범위 검색 시)
  • 테이블의 모든 세컨더리 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스)
• 단점
  • 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스 크기가 커짐
  • 세컨더리 인덱스를 통해서 검색하면 PK로 다시 한번 검색해야 하기 때문에 처리 성능이 느림
  • INSERT 하면 PK에 의해 레코드 저장 위치가 결정되기 때문에 처리 성능이 느림
  • PK 변경할 때 레코드를 DELETE 하고 INSERT 하는 작업이 필요하기 때문에 처리 성능이 느림

결과적으로, 클러스터링 인덱스의 장점은 빠른 읽기이며, 단점은 느린 쓰기 라는 것을 알 수 있다.