📌 결합(복합) 인덱스란?
결합 인덱스란 두 개 이상의 컬럼을 합쳐서 인덱스를 만드는 것을 말한다.
단일 컬럼보다 여러 개의 컬럼으로 합쳤을 때 좋은 분포도를 가질 경우 사용된다.
주로 WHERE절에서 AND 조건에 많이 사용되는 컬럼들을 결합 인덱스로 구성한다.
단일 인덱스 VS 결합 인덱스
| 단일 인덱스 | 결합 인덱스 |
| 1개의 컬럼으로 구성 | 2개 이상의 컬럼으로 구성 |
| ◾ 조회속도개선에는 도움이되지만, 입력/수정/삭제가 빈번한 컬럼일 경우 성능이 저하됨 | ◾ 단일 인덱스를 능가하는 성능을 낼수있으며, 여러개의 인덱스를 대신 할 수도 있음 |
| ◾ 1번필드를 인덱스로 구성하면 1번필드는 인덱스에서 찾을수있지만 2번필드는 테이블에 접근해야만 확인 할 수 있음 | ◾ 1번필드, 2번필드를 구성하면 모두 인덱스안에서 처리 가능 |
👉 인덱스에 없는 컬럼을 확인하기위해서는 테이블접근이 필수이다. 적절한 컬럼수로 결합인덱스를 구성하여 성능향상을 고려해야한다.
📌 결합 인덱스 컬럼 선택
- where절에서 and 조건으로 자주 결합되어 사용되면서 각각의 분포도 보다 두 개 이상의 컬럼이 결합될 때 분포도가 좋아지는 컬럼들
- 다른 테이블과 조인의 연결고리로 자주 사용되는 컬럼들
- order by에서 자주 사용되는 컬럼들
- 하나의 이상의 키 컬럼 조건으로 같은 테이블의 컬럼들이 자주 조회될 때
📌 결합 인덱스의 컬럼 순서
결합 인덱스를 만들때 컬럼들의 배열 순서가 중요한 이유
컬럼 순서에 따라 완전히 다른 인덱스가 되며 성능 차이도 천차만별이다. 컬럼의 순서를 잘못 배열하면 결합 인덱스의 발동 확률이 매우 낮아질 수 있다.
조건절에서 *첫 번째 컬럼을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않는 경우가 대부분이다.
그렇기에 많은 쿼리에서 공통적으로 사용된 조건절의 컬럼을 인덱스 선행 컬럼에 주로 사용한다.
절대적인 우선순위는 없으며 시스템 상황에 맞게 적용하는 것이 중요하다.
* 결합 인덱스의 첫번째 컬럼은 조회조건에 항상 포함 되어야 인덱스를 사용할 수 있다.
⭐ 결합 인덱스 컬럼의 설정 시 고려해야 할 우선순위
1. where절 조건에 많이 사용되는 컬럼이 우선시
2. 첫번째 컬럼으로 최대한 많이 필터링 할 수 있는 컬럼이 우선시
3. Equal ('=' / 등치조건)로 사용되는 컬럼 우선
3. 분포도가 좋은 컬럼을 우선
4. 자주 이용되는 순서대로 결합 인덱스 컬럼의 순서 결정
1. 결합 인덱스의 첫번째 컬럼이 정상적인 조건으로 WHERE절에 있는 한 결합 인덱스를 사용 할 수 있다.
- 수많은 조회조건을 모두 인덱스로 구성하기는 어렵다.
- 조건별로 카운트를 해보고 적은건수의 데이터를 위주로 생성하는것이 유리하다.
2. 결합 인덱스의 첫번째 컬럼으로 처리 범위를 산정하기 때문에 최대한 많은 필터링을 처리하도록 결정하는 것이 좋다.
예시)
1. [ 직원 ] 테이블에 남자 50명 여자 50명 총 100명의 직원이 있다.
2. 남자 중 이름이 '철수'인 사람이 단 2명이 있다.
👉 성별이 남자이며 이름이 철수인 사람을 찾아라.
CASE 1. (성별, 이름)
이 경우
1) 총 직원 100명 중 성별을 검사해서 50건을 걸러낸 후
2) 다시 이름을 검사해서 '철수'를 2건을 걸러 내어 결과를 화면에 출력한 경우
👍 CASE 2. (이름, 성별)
이 경우
1) 총 직원 100명 중 이름을 먼저 검사해서 2건을 찾은 후
2) 성별을 검사해서 2건을 화면에 출력한 경우
👉 이름으로 최대한 걸러준 후 성별을 검색하여 효율성을 높였다.
3. "=" 조건이 미치는 영향
- 결합인덱스의 첫번째 컬럼이 '=' 조건이 아니라면 이후의 조건에서 '='을 사용하더라도 처리범위는 줄어들지 않는다.
- '='이 아닌 조건을 사용하는 첫번째 컬럼의 경우 첫번째 컬럼만 인덱스를 타고(부분 인덱스), 이후의 컬럼은 인덱스를 타지 않는다.(즉, 체크만 한다)
CASE 1
-- 인덱스: (col1, col2)
WHERE col1 between 111 and 113
AND col2 = 'A';
1) between에 의해 범위가 결정되고, 검색 중에
2) A가 되는 데이터만 추출한 후 범위에서 벗어나면 마무리가 된다.
따라서 COL2에 대한 '='는 범위를 좁히지는 못하고 단지 해당 조건을 만족하는지 확인하는 용도로만 사용되는 것을 볼 수 있다.
👍 CASE 2
-- 인덱스: (col1, col2)
WHERE col1 IN (111, 112, 113)
AND col2 = 'A';
1) 범위에 속한 값들을 나열시키고 각 값들마다
2) 해당 조건에 맞는 컬럼을 선택한다.
즉, 두 칼럼에 대한 '='를 모두 사용해서 범위를 좁히는데 사용되는 것을 볼 수 있으며 이런 경우 성능이 좋은 SQL문이 된다.
4. 흔히 분포도가 좋은 컬럼이 처리 범위를 줄여주므로 결합 인덱스의 선행컬럼으로 해줘야 한다.
굳이 분포도가 좋은 컬럼이면 단일 인덱스로서 사용되면 되고 굳이 결합 인덱스로 사용할 필요가 없다.
결합 인덱스를 사용하는 이유중 하나가 하나의 컬럼만으로는 분포도가 좋지 않지만 여러개의 컬럼으로 분포도를 향상시켜 처리범위를 줄여주는데에 있기 때문이다.
📌 결합 인덱스의 효율성이 떨어지는 경우
결합 인덱스도 일반적인 인덱스와 마찬가지로 데이터들이 정렬되어 보관되기 때문에 소수의 데이터를 빠르게 찾는 것에는 유리하지만 스캔이 많이 생기게 된다면 효율성이 떨어지게 된다.
아래의 예시들은 emp_pay_idx 인덱스를 사용하기는 하지만 스캔이 많이 생기는 경우로 인덱스의 효율성이 떨어지는 경우들의 예시이다.
인덱스 생성
create index emp_pay_idx on emp_pay(급여년월, 급여코드, 사원번호);
예시 1)
select * from emp_pay where 급여년월 LIKE '2021%' and 급여코드 = '정기급여';- 결합 인덱스의 첫 번째 컬럼인 급여년월의 조건이 있더라도 Equal(=)이 아닌 범위 연산자인 LIKE '2021%' 조건을 사용했으므로, 세개의 칼럼이 모두 필요한 emp_pay_idx 인덱스를 찾을 때 두번째 칼럼인 급여코드에 대한 조건을 B*Tree에서 쉽게 찾을수가 없게 된다.
- 이는 결합 인덱스가 각 칼럼별로 정렬이 되어 있는 것이 아니라 첫번째, 두번째, 세번째 칼럼이 결합이 되어 정렬이 되어있기 때문이다.
- 이때 급여코드에 대한 조건은 인덱스를 찾아가는 검색조건이 아니라 인덱스 값이 조건에 맞는지 여부를 검증하는 체크 조건이 된다.
예시 2)
select * from emp_pay where 급여년월 = '202107' and 사원번호 = '20210401';- 결합 인덱스의 첫번째 칼럼인 급여년월의 조건이 equal(=)이더라도 두번째 컬럼인 급여코드에 대한 조건이 없으므로 세번째 칼럼인 사원번호 조건을 검색 조건이 아닌 체크 조건으로 밖에 사용할 수 없게 된다.
- 즉 결합 인덱스에서 급여년월인 모든 데이터를 찾아서 사원번호 조건에 맞는지 일일이 확인하는 풀 테이블 스캔이 일어나고 있는 셈이다.
📖 참고
- 데이터 베이스 결합 인덱스에 대하여 - https://coding-factory.tistory.com/755
- DB의 결합 인덱스에 관해서 - https://marrrang.tistory.com/74
- 단일인덱스 & 복합인덱스 - https://velog.io/@tothek/%EB%8B%A8%EC%9D%BC%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EB%B3%B5%ED%95%A9%EC%9D%B8%EB%8D%B1%EC%8A%A4
- 인덱스 생성과 스캔, 결합인덱스 - https://velog.io/@semi-cloud/8%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%83%9D%EC%84%B1%EA%B3%BC-%EC%8A%A4%EC%BA%94-%EA%B2%B0%ED%95%A9-%EC%9D%B8%EB%8D%B1%EC%8A%A4
'📁 Database > Concept' 카테고리의 다른 글
| [ Database ] 정규화란? (0) | 2022.12.28 |
|---|---|
| [ Database ] 인덱스(Index)란? (1) | 2022.12.25 |
댓글