DB Index란 무엇인가

Yukicow·2024년 2월 19일

Database

Database에 데이터가 많아질 수록 조회 속도가 느려지는 현상을 겪게 된다.

데이터의 조회가 느리다는 것은 요청에 대한 처리 시간이 늦어진다는 것이고, 사용자 경험이 감소한다는 뜻이기도 하다.

데이터 전체(또는 대부분)를 조회하는 경우라면 조회 속도가 느린 것이 당연하고 느릴 수밖에 없고 더 개선 여지가 없다.

하지만 조건을 이용해서 특정 데이터를 찾아야 하는 경우라면, 조건에 맞게 정렬된 형태로 데이터를 조죄할 수 있다면 조회 속도를 빠르게 만들 수 있다.

Database는 이러한 접근법을 이용해서 Index라는 개념을 통해 특정 데이터에 빠르게 조회할 수 있도록 도와 준다.

오늘은 Index란 무엇이고 어떻게 동작하는지 간단하게 알아보자.

( MySQL을 기준으로 작성한다. )

1. Index란

인덱스(Index)는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료구조를 일컫는다.

필자는 자료구조라는 말때문에 조금 햇갈린 기억이 있다.

자료구조 자체를 일컫는 말인지, 자료구조 형태로 저장된 데이터들을 말하는 것인지 애매하기 때문이었다.

그러나 지금 와서 생각해 보면 자료구조라는 단어 자체가 두 가지 의미를 모두 담고 있는 것이 아닐까 싶다.

데이터가 특정 자료구조의 형태로 저장되어 있다는 것은 데이터 자체가 자료구조이면서 데이터인 것이다.

필자도 두 가지 경우를 혼용해서 글을 작성할 것이고, 상황에 맞게 해석하면 될 듯 하다.

2. 인덱스의 특징

Database에는 테이블이라는 개념이 있고 컬럼이라는 개념이 있다. 인덱스 생성에는 '컬럼'을 이용한다.

인덱스를 생성한다는 것은 특정 컬럼을 기준으로 정렬된 상태의 데이터 복사본을 만들어내겠다는 것이다.

즉, 1000만건의 데이터가 있는 테이블에서 인덱스 하나를 만들면 똑같이 1000만건의 데이터를 복사해서 정렬된 상태로 저장한다는 뜻이다.

주의할 점은, 인덱스가 모든 컬럼을 다 복사해서 저장하는 형태는 아니라는 것이다.

그렇게 되면 너무 비효율적이기도 하고 굳이 그렇게 할 필요도 없기 때문이다.

인덱스를 생성하면 특정 컬럼을 기준으로 정렬이 이루어져야 하니 인덱스를 만드는데 설정된 컬럼은 필수적으로 복사된다.

그리고 해당 컬럼을 제외하고는 실제 원본 데이터가 저장된 메모리 주소를 가리키는 포인터를 함께 저장한다.

포인터만 있으면 해당 데이터로의 접근은 바로 가능하기 때문에 굳이 모든 컬럼을 다 저장해서 용량을 차지할 필요가 없다.

MySQL은 조금 다르게 PK를 클러스터드 인덱스로 만들고 모든 논 클러스터드 인덱스는 메모리 주소가 아닌 PK값을 저장함으로써 동작한다.

자세한 내용은 https://mangkyu.tistory.com/285 해당 블로그에 잘 나와 있다.

예를 들어 아래와 같은 아이디, 이름, 생성된 날짜를 갖는 테이블이 있다고 가정해 보자.

id	name	created_date
1	MinWoo	2024-02-15
2	ChulSu	2024-02-16
3	YeongHee	2024-02-17
4	Yujin	2024-02-17
5	ChangSu	2024-02-17

만약 생성된 날짜(created_date)를 기준으로 인덱스를 생성한다면 아래와 같은 복사본이 만들어질 것이다.

created_date	pointer
2024-02-15	id가 1인 Row의 메모리 주소
2024-02-16	id가 2인 Row의 메모리 주소
2024-02-17	id가 3인 Row의 메모리 주소
2024-02-17	id가 4인 Row의 메모리 주소
2024-02-17	id가 5인 Row의 메모리 주소

이렇게 만들어진 복사본(인덱스)은 Secondary Storage(실제 DB 데이터들이 저장되는 공간 = 보조기억장치)에 저장되고 사용된다.

"인덱스를 탄다", "인덱스를 사용한다"라는 말을 많이 듣는데, 원본 데이터 집합이 아닌 인덱스 데이터 집합을 통해서 데이터를 조회하게 될 때 사용하는 말이라고 볼 수 있다.

즉, 인덱스는 특정 컬럼과 실제 메모리 주소를 가리키는 컬럼을 묶어 복사본으로 만든 데이터 집합이고, 실제 원본 데이터가 저장되는 원리와 하나도 다를 게 없다는 뜻이 된다.

( 인덱스는 사용되는 과정에서 인덱스의 목적에 맞게 최적화되어 메모리에 로드하고 사용한다고 한다. )

위와 같은 특징 때문에 인덱스를 생성하게 되면 추가적인 메모리를 차지하게 된다. 따라서 인덱스를 무작정 많이 만드는 것은 메모리 효율이 좋지 않다.

또한 테이블에 삽입, 수정, 삭제가 발생할 경우 인덱스에도 변경 사항을 적용하기 위한 부가적인 작업이 들어가기 때문에 3~4개 정도의 인덱스를 유지하는 것이 좋다고 한다.

이렇게 보면 인덱스를 유지하기 위한 오버헤드가 상당히 안 좋게만 보일 수 있는데, 데이터의 삽입, 수정, 삭제 시 추가적인 오버헤드가 발생하는 것이지, 데이터를 수정하기 위해 데이터에 접근하는 속도 자체가 빨라지는 경우도 있을 수 있다.

3. 인덱스의 종류

위는 인덱스를 생성했을 때 실제 저장되는 데이터로서의 개념에 가까웠다면, 이번에는 자료구조 그 자체에 좀 집중을 해 보자.

인덱스의 최종적인 목표는 데이터 탐색 성능을 높이는 것이다. 원하는 데이터로의 접근을 높이기 위한 방법이라는 뜻이다.

그렇다면 단순히 특정 컬럼을 정렬한 형태로 제공하는 것으로는 아무 효과가 없다.

정렬된 형태에서 조회할 때 효율을 발휘하는 자료구조 형태로 저장되어야 의미가 있는 것이다.

예를 들어 정렬된 형태의 자료구조가 LinkedList라고 생각해 보자. 아무리 정렬되어 있다고 한들 모든 데이터를 순회해야 특정 데이터에 접근할 수 있다.

많고 많은 자료구조들 중에서도 탐색에 특화된 자료구조들이 존재하는데, 인덱스도 그러한 여러가지 자료구조들 중 몇가지를 채택하여 제공하고 있다.

그래서 인덱스에는 종류가 여러가지가 있고, 각각의 종류에 따라 장단점이 나뉜다.

MySQL에서 제공하는 자료구조에는 필자가 현재 알고 있는 것만 보면 B-Tree계열(B+tree, B*tree 등)와 Hash Table 정도가 있다.

( PostgreSQL의 경우 bloom filter와 같은 자료구조도 있는 듯 하다. )

일반적으로는 B-Tree계열(최근에는 B+tree가 많이 사용됨)이 가장 많이 사용되기 때문에 B-Tree계열에 대해 공부하고 다른 것들은 추가적으로 필요할 때 공부하면 될 것 같다.

즉, DB에서는 인덱스를 구현하기 위해 탐색에 특화된 여러가지 자료구조 중 몇 가지를 채택하였고, 상황에 따라 효율이 더 좋은 자료구조가 있기 때문에 적절하게 골라 적용하면 된다.

참고하면 좋은 영상들

https://www.youtube.com/watch?v=bqkcoSm_rCs
https://www.youtube.com/watch?v=H_u28u0usjA&t=8s
https://www.youtube.com/watch?v=liPSnc6Wzfk

가장 일반적으로 사용되는 B-Tree 자료구조의 원리, DB에서 B-Tree를 채택하는 이유 등을 설명한 영상이다.

인덱스 컬럼 선택 기준

인덱스를 생성할 때 특정 단일 컬럼 또는 다중 컬럼을 선택하여 생성해야 하는데, 어떤 기준으로 컬럼을 선택하는 것이 효울적인지에 대해 알아보자.

1. 카디널리티가 높은 컬럼

카디널리티란 얼마나 중복되는 데이터가 없는지(중복도)를 나타내는 용어라고 보면 된다.

카디널리티가 높으면 중복된 데이터가 적은 것이고, 낮으면 많은 것이다.

용어가 중복도의 기준을 반대로 해석하기 때문에 가끔 헷갈리는 경우가 많다. 필자는 유니크한 정도로 외우는 것이 개인적으로 제일 확실하게 와닿았다.

인덱스가 최대 효율을 발휘하기 위해서는 특정 컬럼을 기준으로 많은 데이터의 묶음(블록)을 만들어 내야 한다.

블록이 많을 수록 각 블록에 담겨 있는 데이터 수는 줄어들기 때문에 조회되는 데이터가 줄어든다.

조회하는 데이터가 매우 많다면 그건 인덱스가 필요한 상황이 아닐 수 있다. 또한, 조건에는 인덱스와 관련 없는 조건이 포함되기도 하는데, 이때 인덱스를 통해 조회된 데이터가 적을 수록 이러한 조건 비교 연산이 줄어들어 효율적이다.

2. 조건 비교나 JOIN, 정렬, 그룹핑에 많이 사용되는 컬럼

인덱스 라는 것이 특정 컬럼을 기준으로 정렬된 자료구조이기 때문에 조건 비교나 JOIN, 정렬, 그룹핑에 많이 사용되는 컬럼에 사용하면 효율적이다.

3. 다중 컬럼 인덱스를 사용할 경우 카디널리티가 높은순으로

해당 내용은 원리를 설명은 하겠지만, 필자의 개인적인 의견으로 정리한 것이라 맞는지는 보장할 수 없다.

위 1번의 내용과 조금 비슷할 수 있는데, 한 번 조회할 때 최대한 많이 걸러낼 수록 비교해야할 조건군이 줄어들기 때문에

카디널리티가 높은순으로 인덱스를 설정하는 것이 조건을 순서대로 비교해가는 과정에서 블록 수를 최대 효율로 줄여나갈 수 있기 때문이 아닐까 싶다.

물론 단순히 이러한 장점만을 생각하고 무조건 카디널리티가 높은순으로 인덱스를 만들어서는 안 된다.

카디널리티가 높은순으로 인덱스를 생성했을 때의 장점은 약간의 성능을 올려 주는 정도일 수 있지만, 순서 자체로 인해서 인덱스를 탈 수 있는 쿼리가 늘어나는 중요 포인트를 놓칠 수도 있기 때문이다.

4. 인덱스 사용시 주의 사항

인덱스를 사용할 때에는 조심해야 할 것들이 있다.

1. 정렬순서

다중 컬럼 인덱스를 사용하게 되면, 컬럼 여러개를 지정하여 생성하게 되는데, 이 때 순서를 매우 중요하게 생각해야 한다.

다중 컬럼 인덱스에서는 컬럼의 순서대로 정렬이 이루어지기 때문에 뒤에 정의한 컬럼이 먼저 정의한 컬럼에 의존하게 된다.

예를 들어 위에서처럼 id, name, created_date가 있는 테이블에서 name, created_date순서대로 인덱스를 만들면 name을 기준으로 정렬이 일어나고 그 뒤에 name이 같은 값들을 기준으로 created_date정렬이 일어난다.

이러한 정렬이 별거 아닌 것 같지만, 매우 중요하게 동작한다.

선행 컬럼에 의존한다는 뜻은 반대로 말하면 선행 컬럼에 대한 조건이 부여되지 않으면 인덱스를 사용할 수 없다는 의미이기도 하다.

위의 예시에서 name에 대한 조건 없이 created_date조건만으로는 인덱스를 탈 수가 없다는 뜻이다.

WHERE name='hi' AND created_date > '2024-01-01' 이라는 조건을 작성한다면, name이 'hi'인 데이터를 인덱스를 통해서 찾고 그 뒤에 created_date가 2024-01-01보다 큰 데이터를 걸러내면 된다.

하지만 WHERE created_date > '2024-01-01' 라고만 사용하면 created_date가 2024-01-01 이상인 모든 데이터를 찾아야 한다.

인덱스는 name으로 정렬이 되어 있는 상태에서 created_date 정렬이 되어 있기 때문에 결국은 모든 페이지를 순회해야 한다.

2. 특정 조건 사용시 후위 컬럼 인덱스 사용이 불가능하다

between, like, <, > 등 범위 조건을 사용하면 해당 컬럼에 대해서는 인덱스를 타지만, 그 뒤에 오는 후위 인덱스 컬럼들은 인덱스가 사용되지 않는다.

( like는 '%’가 앞에 붙을 때 인덱스를 타지 않는다. )

=, in은 다음 컬럼에서도 인덱스를 사용한다. 하지만 in은 인자값으로 상수가 포함되면 문제 없지만, 서브쿼리를 사용하게 되면 성능 이슈가 발생할 수 있는데, 서브쿼리의 외부가 먼저 실행되고, in은 체크조건으로 실행되기 때문이다.

이곳에 해당하는 내용인 듯 하다.

MySQL 5.6부터는 서브쿼리 최적화가 이루어지기 때문에 조건만 맞는다면 괜찮겠지만, 조건이 맞지 않을 수 있으니 성능 문제가 발생한다면 이 글을 참고하여 해결해 보면 좋을 듯 하다.

3. 인덱스로 사용된 컬럼값 그대로 사용해야 한다

"where id + 1 > 100" 는 인덱스를 못 타지만, "where id > 100 + 1" 은 인덱스를 사용한다.

또, 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않는다. 정확한 타입을 사용해야 한다.

4. OR 연산은 테이블 풀스캔을 발생시킬 수 있다.

항상 그런 것은 아니지만 옵티마이저가 OR 연산을 최적화 하는 것이 어려울 수 있는 듯 하다.

따라서 풀 테이블 스캔이 발생할 확률이 높아지기 때문에 OR 연산은 조심해서 사용해야 한다.

5. MySQL은 IS (NOT) NULL을 통해 Index range scan(const, ref, range)이 가능하다.

IS NULL의 경우 테이블 접근 방식(type)이 ref로 동작하고 IS NOT NULL의 경우 range로 동작한다.

용어

1. 커버링 인덱스(Covering Index)

커버링 인덱스 는 쿼리를 충족하는데 필요한 모든 데이터를 갖는 인덱스를 말한다.

조회되는 데이터이든, 조건에 사용되는 데이터이든 하나의 인덱스에서 모두 조회가 가능하다면 그것은 해당 쿼리에 있어서 커버링 인덱스이다.

인덱스는 인덱스를 구성하는데 필요한 특정 컬럼 외에 데이터의 주소만 저장하기 때문에 실제 데이터를 꺼내기 위해서는 주소값을 통해 디스크(Secondary Storage)에서 I/O작업을 일으켜야 한다.

하지만 조회 또는 사용되는 데이터가 모두 인덱스를 구성하는데 사용된 컬럼밖에 없다면, 굳이 디스크에 접근하지 않아도 인덱스 안에서 해결이 가능하다.

위에서 말했듯 인덱스는 필요에 따라 메모리에 올라오기 때문에 디스크를 통한 접근 보다 훨씬 빠르다.

그래서 커버링 인덱스를 타는 쿼리는 속도가 훨씬 빠르다.

2. 클러스터드 인덱스(Clustered Index)

실제 데이터의 정렬에 영향을 주는 인덱스이다.

MySQL은 PK에 대해 클러스터드 인덱스를 생성하고 정렬한다. 실제 물리적인 데이터도 클러스터드 인덱스의 정렬 순서에 맞게 정렬된다.

쉽게 생각하면 테이블 자체가 인덱스가 된다고 생각하면 된다. 실제 원본 데이터를 정의하는 인덱스이기 때문에 테이블당 하나밖에 생성할 수 없다.

리프가 메모리 주소를 가리키지 않고 실제 데이터로 구성되어 있다.

MySQL에서 모든 논 클러스터드 인덱스는 메모리 주소 대신 PK를 저장하고 실제 데이터를 조회할 때 클러스터드 인덱스를 한 번 거치게 된다.

필자는 왜 이런 형태의 인덱스가 필요한지는 사실 와닿지 않는다.

굳이 장점이라면 클러스터드 인덱스에 사용되는 컬럼으로 연속된 데이터를 조회할 때 이점이 있다는 것과 논 클러스터드 인덱스가 클러스터드 인덱스를 의존하게 되면서, 커버링 인덱스를 통해 PK를 조회하는 부분에서 이점이 있다는 정도라고 생각한다.

개인적인 의견이기 때문에, 더 좋은 이점들이 많이 있을 수 있다. MySQL을 개발한 개발자들에게 물어 보고 싶다.

3. 논 클러스터드 인덱스(Non clustered Index = Secondary Index)

클러스터드 인덱스를 제외한 모든 인덱스가 여기에 해당한다.