데이터베이스 테이블에 저장된 데이터의 검색 속도를 향상시키기 위한 자료구조
- 인덱스는 책에 있는 목차라고 생각하면 이해하기 쉽다.
- 우리가 책에서 정보를 찾을때도 먼저 원하는 카테고리를 목차에서 찾고 목차에 있는 페이지 번호를 보고 찾아가듯 인덱스도 인덱스에서 내가 원하는 데이터를 먼저 찾고 저장되어 있는 물리적 주소로 찾아간다.
- 특정 컬럼(열)이나 컬럼들의 조합에 대한 값과 해당 값이 저장된 레코드(행)의 위치를 매핑하여 데이터베이스 쿼리의 성능을 최적화하는 데 중요한 역할을 한다.
- 실제 DB 관련 작업을 할 때 대부분의 속도 저하는 바로 Select문 특히 조건 검색 Where절에서 발생하는데 가장 먼저 생각해 볼 수 있는 대안으로 Index를 생각할 수 있기도 하고, SQL 튜닝에서도 Index와 관련된 문제사항과 해결책이 많기 때문이다.
- 인덱스를 사용하면 테이블 전체를 읽지 않아도 되므로, 검색과 질의에 대한 처리가 빠르게 이루어진다. 이러한 인덱스는 사용자가 직접 접근할 수는 없으며, 검색과 질의에 대한 처리에서만 사용된다.
📍 인덱스 생성과 원리
특정 컬럼에 인덱스를 생성(CREATE)하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다.
이렇게 인덱스가 생성하였다면 앞으로 쿼리문에 "인덱스 생성 컬럼을 Where 조건으로 거는 등"의 작업을 하면 옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있다.
만약 인덱스를 타게 되면 아래의 그림과 같이 인덱스를 타게 되고 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작을 하여 검색 속도의 향상을 가져올 수 있다.
📍 장점
-
검색 대상 레코드의 범위를 줄여 검색 속도를 빠르게 할 수 있다.
-
중복 데이터를 방지하거나 특정 컬럼의 유일성(Unique)을 보장할 수 있다.
-
ORDER BY 절과 GROUP BY 절, WHERE 절 사용시 효율적인 처리가 가능하다.
정렬 order by : 인덱스를 사용하면 ****order by 에 의한 sort 과정을 피할 수 있다. 본래 order by는 괴장히 부하가 많이 걸리는 작업이기 때문에 인덱스를 통해 이미 정렬되어 있으면 부하가 걸리지 않을 수 있다.
조건 검색 where 절 : 보통 where절의 사용할 때 특정 조건에 맞는 데이터를 찾기 위해 데이터를 처음부터 끝까지 다 비교해야 하는데, 인덱스를 통해 데이터가 정렬되어 있으면 빠르게 찾아낼 수 있다.
-
MIN, MAX의 효율적인 처리가 가능하다.
인덱스를 통해 데이터가 정렬되어 있기 때문에 처음부터 끝까지 뒤져서 찾는 것이 아닌 인덱스로 정렬된 데이터에서 MIN, MAX를 효율적으로 추출할 수 있다.
📍 단점
-
인덱스 생성에 따른 관리를 위해 약 10%에 해당하는 추가적인 저장 공간이 필요하다.
(인덱스 사용 시 해당 정보를 담은 MYI 파일 생성)
때문에 너무 많이 인덱스를 생성하면 하나의 쿼리문을 빠르게 만들 수 있지만 대신에 전체적인 데이터베이스의 성능 부하를 추래한다. 무조건적인 인덱스 생성보다 SQL문을 효율적으로 짜고, 인덱스 생성은 마지막 수단으로 사용해야한다.
-
INSERT(삽입), DELETE(삭제), UPDATE(수정) 작업 시에도 인덱스를 업데이트해야 하므로 성능 저하가 발생할 수 있다. (계속 정렬해주어야 하기 때문)
그래서 인덱스는 수정보다는 검색이 자주 사용되는 테이블에서 사용하는 것이 더 좋다.
-
한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.
-
인덱스 생성 시간이 오래 걸릴 수 있다.
📍 인덱스의 관리
인덱스는 항상 최신 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다.
따라서 인덱스가 적용된 원본 테이블 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 인덱스에도 다음과 같은 연산을 추가적으로 해주어야 하며, 그에 따른 오버헤드가 발생한다. 안쓰는 인덱스는 삭제시키는것이 좋다.
- INSERT : 새로운 데이터에 대한 인덱스를 추가한다.
- DELETE : 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행한다.
- UPDATE : 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가한다.
리빌딩
생성된 인덱스는 트리구조를 가진다. 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어진다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업을 거치는것이 좋다.
🔗 참조 링크 : https://coding-factory.tistory.com/746
