포스팅 내용
- 인덱스 개념
- 인덱스를 사용하는 이유
- 인덱스 사용은 항상 성능을 향상시키는 것은 아니다.
- 인덱스 종류 : Clustered Index와 Non-Clustered Index
이번 포스팅은 쿼리 조회 속도를 향상시키는 개체인 인덱스에 대해 알아보고자 한다.
1. 인덱스 개념
인덱스 (Index) 란, '데이터 검색 성능의 향상' 을 위해 테이블 열에 사용하는 자료구조로, 데이터베이스 객체이다.
데이터베이스를 책에 비유하자면, 인덱스는 책의 색인과도 같다.
보통 select 쿼리의 where 절이나 join 예약어 사용시에 인덱스가 사용되며, select 쿼리의 검색 속도를 빠르게 하는데 목적이 있다.
2. 인덱스를 사용하는 이유
테이블에 데이터가 쌓여가면서 db는 저장 공간에 비어있는 공간이 있으면 그 곳에 데이터를 insert하기때문에
테이블 레코드는 순서와 상관없이 저장되는데, 이때 데이터 저장영역을 Heap이라고 한다.
Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 테이블을 처음부터 끝까지 비교하여 탐색하는 Full Scan 방식을 사용한다.
예를 들어, SQL문에서의 호출 빈도가 높은 컬럼이 있는 경우, 항상 Full Scan방식으로 탐색하는 건 비효율적이다.
특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터(Key) 와 데이터의 주소(RowId) 를 정렬하여 B+ Tree 구조로, 별도의 메모리 공간에 저장한다.
dbms는 인덱스를 사용해서 검색하는 것이 빠른지, 전체 테이블을 검색하는 것이 빠른지 판단하는데,
만약 인덱스를 사용해서 검색하는 것이 빠르다면, 인덱스에서 먼저 해당 컬럼의 데이터를 찾고, 데이터의 주소를 가지고
테이블에서 조회해오기 때문에 조회 성능이 더 좋다.
적절한 인덱스를 생성하고 인덱스를 사용하는 SQL을 만든다면 기존보다 빠른 조회 응답 속도로 성능을 향상시킬 수 있다.
따라서 전반적인 시스템 부하를 줄일 수 있다.
where 절에서의 효율성
인덱스 테이블은 데이터들이 정렬되어 저장되어있기 때문에, where절에 맞는 데이터를 빠르게 찾아낼 수 있다.
order by 절에서의 효율성
order by절은 sql구문의 마지막에 실행되는 구문으로, 조회해온 데이터를 전체 정렬한다.
인덱스를 사용하면 이미 데이터가 정렬되어있기 때문에, order by 에 의한 정렬과정을 회피할 수 있다.
MIN() 과 MAX()에서의 효율성
인덱스 테이블에 데이터가 정렬되어 저장되어있기 때문에, 특정하게 인덱스된 컬럼에 대해 MIN() 또는 MAX()값을 조회할 때 효율적이다.
3. 인덱스 사용은 항상 성능을 향상시키는 것은 아니다.
인덱스는 대략 테이블 크기의 10% 내외의 공간을 추가로 사용한다.
-> 사용하지 않는 인덱스는 제거를 해주어야하므로, 생성된 인덱스에 대한 관리비용이 든다.
select(쿼리) 작업이 아닌 insert, delete, update (Command) 작업가 자주일어나는 경우, 오히려 성능이 나빠질 수 있다.
-> insert, delete, update 작업이 자주 발생하지 않는 컬럼에 인덱스를 사용한다.
dbms는 인덱스를 항상 최신 정렬상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다.
그래서 insert, delete, update 가 수행되는 경우 인덱스와 관련된 추가 연산을 수행한다.
- insert : 새로운 데이터에 대한 인덱스 추가
- delete : 삭제하는 데이터의 인덱스를 사용하지 않음 처리
- update : 기존 인덱스를 사용하지 않음 처리 후, 갱신 데이터에 대한 인덱스 추가
위와 같이, dbms는 insert, delete, update가 계속 수행된다면 계속 정렬을 해주어야하는데, 그에 따른 부하가 발생한다.
이런 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념을 기존의 인덱스를 삭제처리하는 것이 아닌, 사용하지 않음 처리를 한다.
그렇기 때문에 insert, delete, update가 자주 발생하는 컬럼에 인덱스를 사용한 경우,
실제 데이터는 10만건이라도 이에 대한 인덱스가 차지하는 크기는 10만건 이상이 되기 때문에 쿼리문 처리시 오히려 성능이 저하될 수 있다.
여기서 주의해야할 점은, insert, delete, update (Command) 에 대한 처리속도가 저하된다는 것이지, Command 작업을 하기 위해
선처리되는 select 작업은 인덱스를 사용했을 경우에 성능이 더 좋다.
예를들어, 실무에서 많은 양의 데이터를 삭제처리해야하는 경우, 인덱스를 사용한 컬럼을 기준으로 조회하여 삭제처리하는 것이 더 빠르게 처리하는 방법이다.
4. 인덱스 종류 : Clustered Index와 Non-Clustered Index
클러스터형 인덱스 (Clustered Index)
- 컬럼을 PK로 설정하면 해당 컬럼에 대한 클러스터형 인덱스가 자동 생성된다.
- 물리적으로 해당 컬럼에 대한 데이터를 재배열하기 때문에 비클러스터형 인덱스보다 조회 속도가 빠르다.
- 기본키로 지정하기 때문에 테이블 당 1개만 생성할 수 있다.
- 데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지한다. 따라서 비클러스터형 인덱스보다 변경 작업에 대한 처리 속도가 느리다.
- 인덱스 자체의 리프 페이지가 곧 데이터로, 즉, 테이블 자체가 인덱스이기 때문에 따로 인덱스 페이지를 만들지 않는다.
비클러스터형 인덱스 (Non-Clustered Index, 보조인덱스)
- 비클러스터형 인덱스는 테이블에 여러 개 설정할 수 있다.
- 레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬된다.
- 인덱스 자체의 리프 페이지는 데이터가 아닌, 데이터의 주소 (RowId)이기 때문에 클러스터형보다 검색 속도는 느리다.
- 데이터의 입력, 수정, 삭제에 대한 처리에 대해 물리적으로 데이터를 재정렬하지 않기 때문에 변경 작업 처리에 대해서는 클러스터형보다 속도가 빠르다
- 인덱스를 생성할 때, 데이터 페이지와 별도의 인덱스 페이지를 만들기 때문에 용량을 더 차지한다.
- 비클러스터형 인덱스는 우선, 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열을 정렬하고, 데이터 위치 포인터를 생성한다.
데이터의 위치 포인트는 클러스터형 인덱스와 달리 '페이지 번호 + #오프셋' 이 기록되어 바로 데이터 주소를 가리킨다.
고유 인덱스
- 값이 중복되지 않는 인덱스
- 기본 키나 고유 키로 지정하면 값이 중복되지 않아서 고유 인덱스가 자동 생성된다.
ref.
'DataBase > 개념' 카테고리의 다른 글
| select 문 실행순서와 처리 과정 (0) | 2022.01.24 |
|---|---|
| [SQL Mapper/iBatis] iBatis 문법정리 / Oracle 쿼리정리 (0) | 2020.10.12 |
| SQL Mapper (0) | 2020.10.11 |
| 잘못 설계된 데이터베이스는 어떻게 수정해야 할까? (3) (feat.정규화) (0) | 2020.07.31 |
| 잘못 설계된 데이터베이스는 어떻게 수정해야 할까? (2) (feat.함수 종속성) (0) | 2020.07.29 |
