인덱스란?
인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료구조이다.
특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다.
또한, 인덱스 생성 시 오름차순으로 정렬하기 때문에 정렬된 주소체계라고 표현할 수 있다.
인덱스를 책에서의 목차라고 생각하면 이해하기 쉽다.
책에서 원하는 내용을 찾을 때 목차나 색인을 이용하면 훨씬 빠르게 찾을 수 있듯, 테이블에서 원하는 데이터를 찾기 위해 인덱스를 이용하면 빠르게 찾을 수 있다.
데이터 = 책 내용, 인덱스 = 목차, 물리적 주소 = 페이지 번호
인덱스의 자료구조
인덱스의 대표적인 자료구조는 해시테이블과 B+Tree가 있다.
1. 해시 테이블(Hash Table)
해시 테이블은 컬럼의 값과 물리적 주소를 (Key, Value)의 한쌍으로 저장하는 자료구조이다.
그러나 잘 사용하지 않는다.
그 이유는 다음과 같다.
1) 범위 쿼리: 해시 인덱스는 특정 키 값을 효율적으로 찾는데 유용하지만, 범위 쿼리(예: SELECT * FROM TABLE WHERE KEY BETWEEN X AND Y)에는 적합하지 않다.
해시 테이블은 데이터를 정렬된 순서로 저장하지 않으므로 범위 쿼리를 위해서는 전체 테이블을 스캔해야 할 수 있다.
2) 해시 충돌: 해시 인덱스는 모든 유일한 키 값에 대해 유일한 해시 값을 생성하지 못할 때가 있다.
이를 해시충돌이라고 하며 해시 충돌이 발생하면 같은 해시 값을 가진 여러 키를 관리해야 하므로 인덱스 검색 성능이 저하될 수 있다.
3) 순차접근: B-트리 인덱스는 데이터를 정렬된 순서로 저장하므로, 순차적인 접근이 필요한 작업 (예: ORDER BY)을 효율적으로 수행할 수 있다. 반면, 해시 인덱스는 데이터를 정렬하지 않으므로, 순차적인 접근은 B-트리 인덱스에 비해 덜 효율적이다.
2. B+Tree
B+Tree는 대부분의 DBMS 그리고 오라클에서 특히 중점적으로 사용하고 있는 가장 보편적인 인덱스이다.
구조는 Root Node(기준) / Branch Node(중간) / Leaf Node(말단)으로 구성되며 계층적 구조를 가지고 있다.
Node는 데이터가 존재하는 공간
Leaf Node만 인덱스(Key)와 함께 데이터(Value)를 가지고 있고, 나머지 Root Node와 Branch Node는 데이터를 위한 인덱스(Key)만을 갖는다.
Leaf Node에만 데이터를 저장하고 Leaf Node들끼리 LinkedList로 연결되어 있어 선형 시간이 소모되어 시간 효율이 올라간다.
Root Node에서 경로를 확인 후, 그에 알맞는 Node들로 이동하여 최종적으로 원하는 데이터가 있는 Leaf Node에 도달한다.
3. 인덱스 장단점
인덱스를 사용한다면 데이터가 정렬되어 있기 때문에 테이블에서 검색과 정렬 속도를 향상시킨다.
- 조건 검색 Where절의 효율성: 보통 Where절을 사용할 때 특정 조건에 맞는 데이터를 찾기 위해 데이터를 처음부터 끝까지 다 비교해야 하는데, 인덱스를 통해 데이터가 정렬되어 있으면 해당 인덱스로 Where절을 수행할 시 빠르게 찾아낼 수 있다.
- 정렬 Order by 정의 효율성: 인덱스를 사용하면 Order by에 의한 Sort 과정을 피할 수 있다. 본래 Order by는 굉장히 부하가 많이 걸리는 작업이기 때문에 인덱스를 통해 이미 정렬되어 있으면 부하가 걸리지 않을 수 있다.
- 단, desc(내림차순)정렬을 한다면 인덱스가 정렬 성능을 향상시키지 못할 수도 있다.
- 또한, 데이터가 많은 테이블일 경우 인덱스를 이용한다면 도움이 되지만 데이터가 아주 작은 테이블에서는 인덱스가 비효율적이다. 그 이유는 인덱스를 생성하는데 드는 CPU 비용, I/O 비용 등이 발생하기 때문이다.
- 따라서 'EXPLAIN'명령을 사용하여 쿼리의 실행 계획을 확인해보는것이 좋다.
- MIN, MAX의 효율적인 처리가 가능: MIN(), MAX() 함수는 각각 주어진 열의 최소값과 최대값을 반환한다.
인덱스가 값을 정렬된 순서로 저장하고 있으므로 최소값은 인덱스의 맨 앞에, 최대값은 인덱스의 맨 뒤에 위치하기 때문이다. 모든 상황에서 이런 이점이 적용되는 것은 아니다.
WHERE 절이 있는 쿼리에서는 인덱스가 항상 MIN(), MAX() 계산을 가속화하지 않는데, WHERE 절의 조건에 따라 MIN()값이 인덱스의 맨 앞에, MAX()값이 인덱스의 맨 뒤에 존재하지 않을 수 있기 때문이다.
인덱스 사용시 주의점
1) 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지시켜야 한다는 점이다.
인덱스가 적용된 컬럼에 정렬을 변경시키는 INSERT, UPDATE, DELETE 명령어가 수행된다면 계속 정렬을 해주어야 하므로 그에 따른 부하가 발생한다.
따라서 추가, 변경, 삭제가 많은 데이터에 대해서는 인덱스를 적용하지 않는것이 좋다.
2) 무조건 인덱스 스캔이 좋은 것은 아니다.
검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋지만 데이터가 비교적 적은 예를 들면, 10개의 데이터를 가진 테이블에서는 오히려 인덱스를 사용함으로써 인덱스 스캔보다는 풀 스캔이 더 빠르다.
3) 속도 향상을 위해 인덱스를 많이 만드는 것이 좋지 않다.
인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요하다. 때문에 너무 많이 인덱스를 생성하면 하나의 쿼리문을 빠르게 만들 수 있지만 대신에 전체적인 데이터베이스의 성능 부하를 초래한다. 때문에, 무조건적인 인덱스 생성보다 SQL문을 효율적으로 짜고, 인덱스 생성은 마지막 수단으로 사용해야 한다.
느낀점
사실 인덱스의 개념을 보면 아~하고 이해하고 넘어가지만 실제로 적용하게 될 시 많이 혼란스럽고 어렵다.
where조건이나 order, 그룹함수, select 등에 효율이 좋다고하는데 실사용에서는 사실상 하나의 컬럼으로만 조건을 거는것보다는 여러개의 컬럼에 동시에 조건을 거는 경우가 많기 때문이다. 또한 모든 쿼리에 EXPLAIN을 적용하여 하나하나 비교할 시간적인 여유도 없으며 데드라인에 맞추기 급급하기 때문에 후다닥 대충 넘어가는 경향이 많았던 것 같다.
인덱스를 단순히 WHERE조건 등에 사용해야지 하고 적용하기보다는 전체 프로세스에 대한 이해가 먼저 있는 다음 어디에 적용할지 고민해야 될 것 같다. 하지만 개발이 끝난 유지보수 기간에 로직이 변경되면서 인덱스가 오히려 마이너스가 되는 경우도 있을거라 판단한다.
댓글