08. 인덱스

인덱스는 데이터베이스 쿼리의 성능과 밀접한 관계가 있다. MySQL 서버의 옵티마이저가 발전하고 성능이 개선됐다고 해도 여전히 관리자(개발자)의 역할은 매우 중요하다.

인덱스란?

색인(찾아보기)에 비유를 해보자면,

책의 내용: 데이터 파일
페이지 번호: 데이터 파일에 저장된 레코드 주소

또한 이것들은 정렬이 되어있다는 것이다. 책의 색인 내용도 많아지면 우리가 원하는 검색어를 빠르게 찾기 어려워진다.

그래서 최대한 빠르게 찾을 수 있게 정렬되어있으며, 이는 DBMS에서도 동일하다.

인덱스의 장단점

장점

데이터가 정렬되어 있어 아주 빨리 원하는 값을 찾기 가능

단점

데이터가 저장될 때마다 항상 값을 재정렬해야 함.
- INSERT, UPDATE, DELETE 작업 시 추가적인 작업 발생

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

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

B-Tree 인덱스

인덱싱 알고리즘 가운데 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘이다.

B-Tree의 B는 "Balanced" 라는 점을 주의하자.

B-Tree는 컬럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다. 즉, 컬럼의 실제 값을 항상 정렬된 트리 구조로 따로 관리해서 빠른 검색이 가능하게 해준다는 것이다.

B-Tree 구조

인덱스의 키 값은 모두 정렬되어 있지만, 데이터 파일의 레코드는 정렬되어 있지 않고 임의의 순서로 저장된다.

InnoDB에서 B-Tree 인덱스의 각 노드는 페이지 단위로 관리된다. 여기서 "페이지"는 InnoDB의 기본 저장 단위이다.

루트 노드 (Root Node)

트리의 시작점으로 하나의 페이지로 구성
모든 탐색, 삽입, 삭제 연산은 루트 노드에서 시작
인덱스 키와 자식노드 주소 값 쌍으로 구성

브랜치 노드(Branch Node)

루트와 리프 사이에 위치한 중간 단계의 노드
여러 층이 존재할 수 있으며, 하위 브랜치 또는 리프 노드로 가는 경로 저장

리프 노드(Leaf Node)

트리의 최하위에 위치한 노드
실제 데이터의 주소(ROWID)를 포함
- 프라이머리 키 인덱스의 경우 데이터 주소에 프라이머리 키 값이 저장되어 있음
인덱스 키 값 순으로 정렬되어 있음

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

인덱스 키 추가

B-Tree에 값이 저장될 때는 저장될 키 값을 이용해 적절한 위치를 검색한 후에 저장해야한다.

저장될 위치가 결정되었을 경우 B-Tree 리프 노드에 레코드 키 값과 주소 정보가 저장
만약, 리프 노드가 꽉 차서 저장할 수 없을 경우 리프 노드가 분리되어야 함.
- 이러한 경우, 상위 브랜치 노드까지 처리 범위가 넓어져 상대적으로 쓰기 작업에 비용이 많이 든다.

InnoDB의 경우 필요하다면 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있다. (체인지 버퍼)

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

인덱스 키 삭제

인덱스 키 삭제는 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아 삭제 마크만 하면 작업이 완료된다. 이 작업 또한 버퍼링되어 지연 처리될 수 있으며 삭제 마킹된 인덱스 키 공간은 그대로 방치하거나 재활용할 수 있다.

인덱스 키 변경

인덱스 키 값은 값에 따라 저장될 리프 노드의 위치가 결정되므로 단순 변경은 불가능하다.

B-Tree의 키 값을 삭제한다.
다시 새로운 키 값을 B-Tree에 추가한다.

이 작업 또한 체인지 버퍼를 활용해 지연 처리 될 수 있다.

인덱스 키 검색

빠른 검색을 위해 인덱스를 구축하는 가장 큰 이유로 루트 노드 -> 브랜치 노드 -> 최종 리프 노드까지 이동하면서 "트리 탐색" 과정을 거치게 된다.

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

B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분(Left-most part)만 일치하는 경우에 사용할 수 있다.
부등호('<', '>') 비교 조건에서도 활용할 수 있다.
인덱스 구성 키 값의 뒷부분만 검색하는 용도로는 인덱스 사용 불가!

InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키 락(갭 락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어 있다.

UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 존재하지 않을 경우 불필요하게 많은 레코드를 잠근다.

B-Tree 인덱스 사용시 주의할 점

1. 인덱스 키 값 크기

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

Binary-Tree(이진 트리)가 아닌 B-Tree(Balanced-Tree)를 사용하기에 자식 노드의 개수가 가변적인 구조를 가질 수 있다.

인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야하는 횟수가 늘어나고, 그만큼 느려진다.
- 데이터를 페이지 단위로 가져온다.
인덱스 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 것을 의미한다.
- 인덱스의 한 페이지에 저장할 수 있는 키의 용량은 제한적이다.
인덱스의 크기가 커지면 커질수록 메모리에 캐시해둘 수 있는 레코드가 줄어든다.
- 인덱스를 캐시해두는 InnoDB 버퍼 풀이나 MyISAM의 캐시 영역의 크기는 제한적이다.

2. B-Tree 깊이

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

깊이는 MySQL에서 값을 검색할 때 랜덤하게 디스크를 몇 번이나 더 읽어야하는지와 직결되는 문제
인덱스 키 값의 크기가 커지면 커질수록 인덱스 페이지 하나에 담을 수 있는 인덱스 키 값의 개수가 작아짐
- 같은 레코드의 건수라 하더라도 깊이가 깊어져서 디스크 읽기가 더 많이 필요

3. 선택도(기수성, 카디널리티)

모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 중복된 키 값이 많아질 수록 카디널리티는 낮아진다.

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

4. 읽어야 하는 레코드의 건수

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

B-Tree 인덱스를 통한 읽기

어떤 경우에 인덱스를 사용하게 유도할지, 또는 사용하지 않게 할지 판단하는 것은 중요하다.

인덱스 레인지 스캔

인덱스 접근 방법 중 가장 대표적인 접근 방법으로 뒤에 설명할 두 방법에 비해서 빠른 방법이다.

레코드 한 건만 조회와 한 건 이상을 조회하는 경우 각각 다른 이름으로 분류하지만 여기선 "인덱스 레인지 스캔"이라고 표현한다.

검색해야할 인덱스 범위가 결정됐을 때 사용하는 방식
루트 노드 -> 브랜치 노드 -> 리프 노드까지 가야 필요한 레코드의 시작 지점을 파악할 수 있음
- 시작 위치를 찾은 후 최종 위치까지 스캔하게 된다.

정리하자면,

인덱스 조건을 만족하는 값이 저장된 위치를 찾는다. (인덱스 탐색, Index Seek)
탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. (인덱스 스캔, Index Scan)
읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽는다.

3번 과정의 경우 커버링 인덱스로 처리되는 쿼리의 경우 필요하지 않게 된다.

커버링 인덱스: 조회하는 모든 컬럼이 인덱스에 포함되어 있어, 테이블 접근 없이 인덱스만으로 데이터를 조회할 수 있는 인덱스
-- idx_member_email_name: (email, name)
SELECT email, name
FROM member 
WHERE email = 'test@example.com';

인덱스 풀 스캔

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

대표적으로 쿼리 조건절에 사용된 컬럼이 인덱스의 첫 번째 컬럼이 아닌 경우에 발생
- index: (A, B, C) 인 상황에서 쿼리 조건절에 B나 C를 사용한 경우
일반적으로 테이블 풀 스캔보다는 더 효과적이긴 하다.
- 테이블 전체를 읽는 것보다는 적은 디스크 I/O로 쿼리를 처리

하지만, 인덱스 생성 시 인덱스 풀 스캔 방식을 고려하고 설계하지는 않기에 인덱스를 효율적으로 사용하지 못한다.

루스(loose) 인덱스 스캔

말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 말한다.

인덱스 레인지 스캔과 비슷하게 동작하지만 중간에 필요하지 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리한다.

일반적으로 GROUP BY 또는 집합 함수 가운데 MAX(), MIN() 함수에 대한 최적화를 하는 경우에 사용

인덱스 스킵 스캔

인덱스의 핵심은 값이 정렬되어 있다는 것이며 컬럼의 순서가 매우 중요하다!

MySQL 8.0 버전부터 옵티마이저가 조건절에 잘못된 순서로 조회 쿼리를 작성하더라도 인덱스 검색이 가능하게 해주는 방식을 말한다.

이러한 인덱스 스킵 스캔(Index skip scan) 최적화 기능은 루스 인덱스 스캔이 있었지만, 루스 인덱스 스캔의 경우 GROUP BY 작업 처리를 위해 사용하는 경우에만 적용할 수 있었다.

-- index skip scan on/off 
SET optimizer_switch='skip_scan=[on | off]';

주의할 점

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

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

두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스(복합 컬럼 인덱스)라고 한다.

인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다.
- 만약 컬럼이 4개라면, 세 번째 컬럼은 두 번째 컬럼에 의존하고 네 번째 컬럼은 세 번째 컬럼에 의존하는 형태

다중 컬럼 인덱스에서는 인덱스 내에서 각 컬럼의 순서가 상당히 중요하다.

클러스터링 인덱스

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

클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원한다.

프라이머리 키 값에 의해 레코드의 저장 위치가 결정됨
프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 변경되어야 함

이렇듯 프라이머리 키 값에 대해 의존도가 상당히 크기 때문에 프라이머리 키를 신중하게 결정해야 한다.

B-Tree vs 클러스터링 인덱스

B-Tree 인덱스도 인덱스 키 값으로 이미 정렬되어 저장되어있어 클러스터링된 것으로 생각할 수 있지만, 일반적인 B-Tree 인덱스는 클러스터링 인덱스라고 부르지 않는다. 테이블의 레코드가 '프라이머리 키'값으로 정렬되어 저장된 경우에만 '클러스터링 인덱스', '클러스터링 테이블'이라고 한다.

또한 B-Tree의 경우 세컨더리 인덱스를 위한 리프 노드와는 달리 클러스터링 인덱스의 경우 리프 노드에 레코드의 모든 컬럼이 같이 저장되어 있다.

즉, 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것이다.

프라이머리 키가 없는 경우의 클러스터링 테이블

InnoDB 스토리지 엔진은 다음과 같은 우선순위로 프라이머리 키를 대체할 컬럼을 선택한다.

프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
NOT NULL 옵션의 유니크 인덱스(UNIQUE INDEX) 중에서 첫 번쨰 인덱스를 클러스터링 키로 선택
자동으로 유니크한 값을 가지도록 증가되는 컬럼을 내부적으로 추가한 후, 클러스터링 키로 선택

3번 케이스가 될 경우, 아무 의미 없는 숫자 값으로 클러스터링 되기 때문에 아무런 혜택을 받을 수 없다. 클러스터링 인덱스가 주는 엄청난 혜택을 누릴 수 있게 가능하다면 프라이머리 키를 명시적으로 생성하자.

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

세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면 어떤 현상이 발생할까?

클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경됨
그 때마다 해당 테이블의 인덱스에 저장된 주소값을 변경해야 함

이러한 오버헤드를 제거하기 위해 InnoDB 클러스터링 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아닌 프라이머리 키 값을 저장하도록 구현돼 있다. (세컨더리 인덱스 탐색 후 프라이머리 키를 기반으로 찾는게 더 느리다고 생각했었는데 이러한 이유가 있었다니..)

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

장점

프라이머리 키로 검색할 때 처리 성능이 매우 빠름
- 특히, 프라이머리 키를 범위 검색하는 경우
테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음

단점

테이블의 모든 세컨더리 인덱스가 클러스터링 키를 가지고 있어 클러스터링 키 값의 크기가 클 경우 인덱스가 커짐
세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한 번 검색해야하므로 처리 성능이 느림
INSERT 시 프라이머리 키에 의해 레코드의 저장 위치가 결정되므로 처리 성능이 느림
프라이머리 키를 변경할 때 레코드를 DELETE 후 INSERT 하는 작업이 필요하기때문에 처리 성능이 느림

이러한 이유에서 클러스터링 키(프라이머리 키)의 크기를 고려하여 선정해야하며, 반드시 명시하는게 좋다.

유니크 인덱스

유니크는 사실 인덱스라기보단 제약 조건에 더 가깝다. 하지만, MySQL에서 인덱스 없이 유니크 제약만 설정할 방법이 없다.

왜 인덱스 없이 유니크 제약조건만을 설정할 수 없는걸까? 지금 드는 생각은 유니크의 경우 INSERT 시 값 체크가 무조건 필요하니까 이 부분에 대해서 성능의 이점을 챙기려고 INDEX가 필요하다고 판단이 된다.

인덱스 인덱스와 일반적인 세컨더리 인덱스와 구조상 아무런 차이점이 없다.

유니크 인덱스 읽기

하나의 값을 검색하는 경우, 유니크 인덱스와 일반 세컨더리 인덱스는 사용되는 실행 계획이 다르지만, 이는 인덱스의 성격이 유니크한지 아닌지에 따른 차이일뿐 큰 차이는 없다.

유니크 인덱스 쓰기

새로운 레코드가 INSERT 되거나 인덱스 컬럼 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다. 이 때, 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 체크하는 과정이 필수적으로 들어간다. 이러한 이유로 일반 세컨더리 인덱스의 쓰기보다 느리다.

유니크 인덱스 쓰기의 경우 중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하여 이 과정에서 데드락이 빈번하게 발생한다. 또한, InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하지만, 인덱스 쓰기의 경우 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다.

유일성이 꼭 보장돼야 하는 컬럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방법도 고려해보자.

유일성을 보장하는 위치를 Application Level 으로 제한하는 방식은 별로인건가?

외래 키

InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼이 인덱스까지 생성된다.

테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
외래 키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)를 발생시키지 않는다.

Previous05. 트랜잭션과 잠금 Next09. 옵티마이저와 힌트

Last updated 27 days ago

Was this helpful?