1. DB 에서 Index 란?
2. Index 의 장점
3. Index 의 단점
4. 효율적인 Index 설정 방법
1. DB 에서 Index 란?
우선 DB 에서 Index 는 DBMS 에서 조회시 탐색 속도를 높이기 위한 기술 이다.
사전에서 단어를 찾을때 찾아보기 쉽도록 일정한 순서로 나열한 목록이라고 생각하면 된다 (색인)
Index 없이 데이터를 저장한다면, 데이터는 순차적으로 쌓이고 이러한 구조에서 원하는 데이터를 찾기 위해선
모든 데이터에 대하여 순차탐색(O(n))이 일어난다.
이것을 우린 풀 스캔 (FULL-SCAN) 또는 테이블 스캔(TABLE-SCAN) 이라고 부른다.
그래서 이 쌓인 데이터들을 좀더 효과적으로 조회 하기 위해
DBMS 는 특정 자료구조를 활용하여 특정 데이터를 쌓아 테이블의 탐색 속도를 개선 시키는 기능을 제공 한다.
위와 같이 데이터베이스 테이블의 검색 속도를 향상시키기 위해 만든 데이터 구조를 우리는 인덱스 라 부르고
인덱스를 활용하여 특정 컬럼의 데이터로 구성된 테이블을 인덱스 테이블이라 부른다
인덱스를 구현하는덴 다양한 자료구조가 사용될수 있는데 가장 흔히 사용되는 것은 B Tree 구조(B*TREE, B+TREE) 이다.
B 트리는 이진 탐색 트리의 일종으로 탐색 성능을 높이기 위해 균형있게 높이를 유지하는 밸런스 트리 이다.
그리고 인덱스 자료구조로 속도(O(1))가 가장 빠른 해시 테이블 (Hash Table) 이 아니라 B Tree 가 쓰이는것에 의아할수도 있다.
우선 인덱스에 해시테이블을 사용하게 될 경우 기능이 매우 제한적이다 왜냐하면 해시테이블 자료구조는 등호(=) 연산에만 특화되어 있기 때문이다. 부등호 ( <, > ) 연산이 자주 사용되는 DBMS 조회에선 해시테이블이 적합하지 않다.
2. Index 의 장점
대부분의 Index 는 B 트리 자료구조로 이루어져 있고 이는 곧 B트리의 장점과 일맥상통 한다.
B 트리의 특징으론 데이터들이 정렬되어 있으므로 아래와 같은 효과를 가지게 된다.
1. 조건 검색 WHERE 절의 효율성
테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드는 내부적으로 순서 없이 뒤죽박죽으로 섞여 저장이 되는데
인덱스가 없을 경우 풀 스캔(FULL-SCAN)이 일어난다. 하지만 인덱스 테이블 스캔(INDEX-TABLE-SCAN)시 인덱스 테이블에 있는 데이터들은 이미 B트리 자료구조를 활용하여 정렬되어 있기 때문에 WHERE 절 조건에 맞는 데이터들을 빠르게 찾아 낼수 있다.
2. 정렬 ORDER BY 절의 효율성
인덱스 테이블에 이미 정렬되어 있는 데이터를 활용하게 되면 ORDER BY 시 정렬하는데 들어가는 컴퓨팅 리소스를 아낄수 있다. 더군다나 ORDER BY 는 굉장히 부하가 많이 걸리는 작업이기 때문에 큰 효율성이 있다.
3. MIN, MAX 의 효율적인 처리
이것 또한 데이터가 이미 정렬되어 있기 때문에 볼수 있는 효과로 값의 시작과 끝을 한건식 가져오면 풀 스캔 (FULL-SCAN) 에 비해 매우 효율적 이다.
3. Index 의 단점
위와 같이 이미 데이터가 정렬되어 있어서 얻는 장점도 많지만 이것이 단점도 될수도 있다.
왜냐하면 데이터가 정렬되어 있다는 말은 곧 INSERT, UPDATE, DELETE 가 발생할때 마다 B트리가 재구성 되어 데이터가 정렬 되는 것 이기 때문이다.
하지만 DBMS 에선 위와 같은 인덱스 테이블 유지 비용을 줄이기 위해 노드를 삭제하지 않고 사용하지 않는다는 마킹 처리만 한다. 그래서 결국 UPDATE, DELETE 명령은 노드 위치 탐색 비용(O(log n))만 발생 시킨다.
하지만 INSERT 명령에 대해선 노드 위치 탐색 비용 + B 트리 재구성 비용이 발생하는데 이는 감수해야 할 어쩔수 없는 비용인것 같다.
그리고 UPDATE, DELTE 명령시 노드 삭제후 재정렬이 일어나지 않고 마킹처리만 된다고 하였는데 이는 곧 점진적으로
B트리의 깊이가 깊어질수도 있다는 것을 의미하고, 성능저하로 이어지게 된다.
그래서 이 미사용 노드들에 대한 최적화 작업을 주기적으로 수행하여 성능저하를 줄여야 한다.
💡 Index를 무조건 많이 설정하면 성능이 향상 될까?
"답은 그렇지 않다" 왜냐하면
1. 인덱스 설정시 인덱스 테이블이 메모리에 저장 되는데 이는 곧 메모리 사용량 증가로 이어진다.
너무 많은 인덱스 설정은 엄청난 메모리 사용으로 이어질수도 있다.
2. 인덱스로 지정된 컬럼이 바뀌게 될 경우 인덱스 테이블 또한 변경이 일어나는데
이는 곧 데이터베이스 성능을 초래할수도 있다.
그렇기에 인덱스를 생성하는 것보다 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야 하고
인덱스 생성은 마지막 수단으로 강구해야 할 문제이다.
4. 효율적인 Index 설정 방법
인덱스는 한 테이블당 보통 3 ~ 5개가 적당하다고 한다. (하지만 상황에 따라 다르다..)
일단 위에서 살펴본 인덱스의 단점에서도 봤듯이 SELECT 보다 INSERT, UPDATE, DELTE 가 더 자주 발생하는 테이블에선 인덱스 기능이 오히려 성능 저하로 이어질수도 있다. 그리고 반대로 SELECT 가 자주 일어나는 컬럼일수록 인덱스 조건에 부합 한다.
효율적인 Index 설정의 핵심적인 기준을 4가지로 나누어보면 아래와 같다.
- 카디널리티 (Cardinality)
카디널리티가 높다 = 한 컬럼이 갖고 있는 값의 중복도가 낮다 (컬럼의 값들이 대부분 다른 값을 가지는 경우)
카디널리티가 낮다 = 한 컬럼이 갖고 있는 값의 중복도가 높다 (컬럼의 값들이 대부분 같은 값을 가지는 경우)
카디널리티가 높으면 인덱스로 설정하기에 괜찮은 컬럼이다.
(인덱스를 통해 불필요한 데이터 대부분을 걸러낼수 있다.)
- 선택도 (Selectivity)
선택도가 높다 = 한 컬럼이 갖고 있는 값 하나로 여러 row 가 찾아진다
선택도가 낮다 = 한 컬럼이 갖고 있는 값 하나로 적은 row 가 찾아진다
선택도가 낮으면 인덱스 설정에 좋은 컬럼이다 (일반적으로 5 ~ 10%가 적당)
선택도 계산법 (컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100)
ex) 10개의 데이터에서 고유한 학번(grade) 컬럼, 2명씩 같은 이름(name) 컬럼, 5명씩 같은 나이(age) 컬럼인 경우
① 학번(grade) 컬럼 선택도: 1 / 10 = 10%
② 이름(name) 컬럼 선택도: 2 / 10 = 20%
③ 나이(age) 컬럼 선택도: 5 / 10 = 50%
조회 활용도
자주 조회 되는지 간단하게 체크해볼수 있는 방법은 아래와 같다.
- 조건절에 자주 쓰이는 컬럼
- 등호(=) 연산에 자주 쓰이는 컬럼
- ORDER BY 절에 자주 쓰이는 컬럼
- JOIN 으로 자주 쓰이는 컬럼
수정 빈도
인덱스로 지정된 컬럼의 값이 바뀌게 되면 인덱스 테이블도 업데이트 되기 때문에 수정빈도가 낮을수록 좋다.
참고자료
https://mommoo.tistory.com/109
'IT > DB' 카테고리의 다른 글
| [MySQL] Character Set 불일치 문제 (0) | 2024.11.10 |
|---|---|
| [Mysql] 서비스 다운타임을 최소화할수 있는 테이블 스키마 업데이트 (0) | 2023.10.08 |
| [DB] 외래키 (foreign key) 사용을 하는게 좋을까? (0) | 2022.11.05 |
