[Tech-Talk] 데이터베이스 Index

Index란?

인덱스란 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조입니다. 만약 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아 보는것은 오랜 시간이 걸립니다. 그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인을 추가하는데, 데이터베이스의 index는 책의 색인과 같은 역할을 합니다. 데이터베이스에서도 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 해줍니다.

인덱스(index)의 관리

DBMS는 index를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있습니다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 각각 다음과 같은 연산을 추가적으로 해주어야 하며 그에 따른 오버헤드가 발생합니다.

💡 INSERT: 새로운 데이터에 대한 인덱스를 추가함 DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함 UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

인덱스(index)의 장점과 단점

장점

테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있습니다.
전반적인 시스템의 부하를 줄일 수 있습니다.

단점

인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요합니다.
인덱스를 관리하기 위해 추가 작업이 필요합니다.
인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있습니다.

만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있습니다. 그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문인데, 앞에서 설명한 것처럼 UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해줍니다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 100만 건이 넘어가게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것입니다.

조건 검색 Where 절의 효율성

테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서가 없이 뒤죽박죽으로 저장됩니다. 이렇게 되면 Where절에 특정 조건에 맞는 데이터들을 찾아낼때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 합니다. 이것을 풀 테이블 스캔 (Full Table Scan)이라고 합니다. 하지만 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건 (Where)에 맞는 데이터들을 빠르게 찾아낼 수 있겠죠. 이것이 인덱스(Index)를 사용하는 가장 큰 이유입니다.

정렬 Order by 절의 효율성

인덱스(Index)를 사용하면 Order by에 의한 Sort과정을 피할수가 있습니다. Order by는 굉장히 부하가 많이 걸리는 작업입니다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 메모리보다 큰 작업이 필요하다면 디스크 I/O도 추가적으로 발생됩니다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 됩니다. 이미 정렬이 되어 있기 때문에 가져오기만 하면 되니까요.

MIN, MAX의 효율적인 처리 가능

이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점입니다. MIN값과 MAX값을 레코드의 시작값과 끝 값 한건씩만 가져오면 되기에 FULL TABLE SCAN으로 테이블을 다 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있습니다.

💡 여기서 잠깐!
Index를 남발하지 말아야 하는 이유
데이터베이스 서버에 성능 문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성입니다. 문제가 발생할 때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게 만들 수는 있지만, 전체적인 데이터베이스의 성능 부하를 초래합니다. 조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 INSERT, UPDATE, DELETE 시에 부하가 발생해 전체적인 데이터베이스 성능을 저하합니다. 그렇기에인덱스를 생성하는 것보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야 한다. 인덱스 생성은 마지막 수단으로 강구해야 할 문제이다.

인덱스(index)를 사용하면 좋은 경우

규모가 작지 않은 테이블
INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
데이터의 중복도가 낮은 컬럼

인덱스를 사용하는 것 만큼이나 생성된 인덱스를 관리해주는 것도 중요합니다. 그러므로 사용되지 않는 인덱스는 바로 제거를 해줘야 효율적으로 인덱스를 사용할 수 있습니다.

인덱스 생성 전략

생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 Index 테이블이 생성됩니다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋습니다. 가장 최선은 PK로 인덱스를 거는것이겠죠. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고. 반대로 모든 값이 같은 컬럼이 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것입니다.

조건절에 자주 등장하는 컬럼
항상 = 으로 비교되는 컬럼
중복되는 데이터가 최소한인(분포도가 좋은) 컬럼
ORDER BY 절에서 자주 사용되는 컬럼
조인 조건으로 자주 사용되는 컬럼

인덱스의 자료 구조

1. 해시 테이블(Hash Table)

해시 테이블은 key와 value를 한 쌍으로 데이터를 저장하는 자료구조입니다. (key, value)로 쌍을 표현하며, key값을 이용해 대응되는 value값을 구하는 방식입니다. 해시 충돌이라는 변수가 존재하지만 평균적으로O(1)의 매우 빠른 시간만에 원하는 데이터를 탐색할 수 있는 구조입니다.

해시 테이블을 이용한다면 인덱스는(key, value) = (컬럼의 값, 데이터의 위치)로 구현하는데, 해시 테이블은 실제로 인덱스에서 잘 사용되지 않습니다.

그 이유는, 해시 테이블은 등호(=) 연산에 최적화되어있기 때문입니다.

데이터베이스에선 부등호(<, >) 연산이 자주 사용되는데, 해시 테이블 내의 데이터들은 정렬되어 있지 않으므로 특정 기준보다 크거나 작은 값을 빠른 시간 내에 찾을 수가 없습니다.

2. B+Tree 인덱스

인덱스에는 여러가지 유형이 있지만 그 중에서도 가장 많이 사용하는 인덱스의 구조는 밸런스드 트리 인덱스 구조입니다. 그리고 B TREE 인덱스 중에서도 가장 많이 사용하는것은 B*TREE 와 B+TREE 구조를 가장 많이 사용되는 인덱스의 구조입니다.

B * Tree 인덱스는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스입니다. 구조는 위와 같이 Root(기준) / Branch(중간) / Leaf(말단) Node로 구성됩니다. 특정 컬럼에 인덱스를 생성하는 순간 컬럼의 값들을 정렬하는데, 정렬한 순서가 중간 쯤 되는 데이터를 뿌리에 해당하는 ROOT 블록으로 지정하고 ROOT 블록을 기준으로 가지가 되는 BRANCH블록을 정의하며 마지막으로 잎에 해당하는 LEAF 블록에 인덱스의 키가 되는 데이터와 데이터의 물리적 주소 정보인 ROWID를 저장합니다.