[DB] 복합인덱스 Composite Index

도입배경

• 다양한 데이터의 접근 형태에 따라 일일이 대응하여 인덱스를 만들 수 있는 환경이라면 인덱스 생성에 있어 고민의 여지가 없지만 아쉽게도 인덱스가 많아지면 그에 따른 사이트 이펙트*가 존재한다
• 따라서 적은 인덱스를 유지하면서 동시에 다양한 데이터 접근 형태에 대해 인덱스 스캔을 지원하는 것은 중요
• 이를 위해 복합 인덱스 기반으로 하나씩 인덱스가 여러 상황을 커버할 수 있도록 테이블 설계가 이루어져야 한다.
• 해당 글에서는 이를 돕기 위해 인덱스가 어떤 방식으로 작동하는지 알아보고, 복합 인덱스가 작동하는 경우의 수에 대해 알아보자

*성능저하, 공간차지

복합인덱스?

정의

• 인덱스를 생성할 떄 두개이상의 컬럼을 합쳐서 인덱스를 만드는 것을 말한다.
• 복합인덱스에서는 합치는 여러 컬럼간의 순서가 중요한데 순서가 다르다면 다른 복합인덱스가 되는 것이다.
• 따라서 순서를 어떻게 결정하는지에 따라 성능에 차이가 존재할 가능성이 크다 .
  
  
  

핵심요약

결합 인덱스란

(컬럼1, 컬럼2, 컬럼3, 컬럼4) 순서로 인덱스를 만들면,

• 쿼리 조건에서 왼쪽에서부터 연속되는 조건만 인덱스를 탑니다.
• = 조건은 연속 조건 유지에 유리
• BETWEEN, IN, >, < 등의 범위 조건이 등장하면, 그 이후 컬럼은 인덱스 사용이 제한됨

INDEX (col1, col2, col3, col4)

===
SELECT * FROM table
WHERE col1 = ?
  AND col2 = ?
  AND col3 BETWEEN ? AND ?
  AND col4 = ?

인덱스 순서 바꾸면 무조건 좋을까?

INDEX (col1, col2, col4, col3) 으로 바꾸면 col4를 인덱스 타게 되는가?

• 아니다.
• 인덱스 탐색은 왼쪽부터 차례로 연속적으로 조건이 충족되어야 가능
• col3이 범위 조건(BETWEEN) 이면 그 이후 컬럼(col4) 는 여전히 인덱스 못탐
• 주의사항 : 단순히 인덱스 순서를 바꿔도 해결되지 않음.
  
  
  

정리 :

1. col1, col2, col3, col4 인덱스 순서인 경우

• 조건 순서 : col1=, col2=, col3 BETWEEN, col4=

→ 인덱스 사용여부 : col1~col3까지 탐색 가능, col4는 필터

1. col1, col2, col4, col3인덱스 순서인 경우

조건 순서 : col1=, col2=, col3 BETWEEN, col4=

→ 인덱스 사용여부 : col1~col2까지 탐색, col3에서 범위 → col4 못탐

문제점 :

• 단순히 인덱스 순서를 바꿔도 해결되지 않음.
• 인덱스 순서를 바꾸는 건 상황 따라 성능이 더 안 좋아질 수도 있으므로 주의
  
  

해결방법 :

1. 쿼리 조건을 바꿀 수 있으면

• BETWEEN 대신 =` 조건으로 바꾸는 것이 최선
• 또는 col3를 WHERE 절 마지막에 사용하도록 재정렬
  
  

2. Index Merge 전략

• DBMS가 col1~2, col4 인덱스를 따로 타고 병합(Merge)할 수도 있음
• 다만 인덱스 병합(Index Merge) 은 성능이 예측 불가하고 느릴 수 있음
  
  

3. 인덱스 커버리지 활용

• 쿼리에서 조회되는 컬럼이 전부 인덱스에 포함되면 col4가 필터로만 쓰이더라도 디스크 접근 없이 처리 가능
• 즉, 인덱스 포함 컬럼(INCLUDE) 또는 covering index 설계 고려

복합인덱스 작동방식

• 복합 인덱스는 둘 이상의 컬럼을 통해 생성한 인덱스이다.

1. 인덱스 선두 컬럼에서 시작해서 조건절에 등호 조건으로 사용한 컬러들은 모두 인덱스 엑세스 조건으로 활용한다.
2. 만약 위와 같은 상황에서 처음 범위 탐색 조건을 발견한 경우 해당 조건까지만 인덱스 조건으로 활용하고 그 이후에 인덱스 존재 컬럼 조건절에 대해서는 모두 인덱스 필터 조건으로 활용
3. 그 후 인덱스 조건으로 사용되지 않느 조건절은 테이블 필터 조건으로 활용
4. 복합 인덱스 내 모든 컬럼을 활용하지 않더라도 선두 컬럼으로 이어지는 컬럼구성이 조건절에 포함된 경우 인덱스는 활용될 수 있다.
5. 통념과는 다르게 인덱스 카디널리티가 낮은 컬럽을 복합 인덱스 앞쪽에 배치한다고 해서 인데스 스캔 상태 가 더 효율적x
   
   
   

컬럼순서

• A, B, C 컬럼의 순서가 복합 인덱스와 C, B, A 컬럼 순서의 복합인덱스는 완전히 다른 인덱스이다.
• 복합 인덱스를 만들떄 가장 중요한 것은 인덱스를 구성하는 순서이다.
• 순서는 where에서 설정한다.
• 쿼리문 작성 시 결합 인덱스를 사용하고자 하면 반드시 결합 인덱스의 컬럼 중 선행하는 컬럼부터 조건에 지정하여 사용해야한다.
• 조건은 컬럼전체 / 일부컬럼 으로 걸 수 있다.
  
  

컬럼설정시 고려사항

• WHERE절 조건에 많이 사용되는 컬럼이 우선
• = 이사용되는 컬럼이 먼저
• 분포도가 좋은 컬럼이 먼저
• 자주 이용되는 컬럼을 순서대로 결합 인댁스 순서로 결정해야한다.

“WHERE절 조건에 많이 사용되는 컬럼이 우선”

• 조건절에서 첫번쨰 컬럼을 조건에서 사용하지 않는다면, 그 인덱스는 사용되지 않는 경우가 대부분
• 그렇기 떄문에 많은 쿼리에서 공통적으로 사용된 조건절의 컬럼을 인덱스 선행 컬럼에 주로 사용함
  
  

“= 이사용되는 컬럼이 먼저”

• 결합 인덱스에서 선행컬럼이 “=” 조건이 아니라면 후행컬럼조건에서 = 을 사용하더라도 처리범위가 줄어들지 않는다. 조건절 첫 컬럼이 =이어야한다.
  
  

BETWEEN 범위조건 컬럼 후행컬럼은 인덱스를 못 탄다

• 인덱스는 연속 조건만 사용가능하며, 범위조건BETWEEN이 걸리면 그 다음은 인덱스타지않음
• 인덱스 탄다 = 전체 테이블 스캔한다는 말

• 예시

  • 결합인덱스 = 컬럼1 + 컬럼2 + 컬럼3 + 컬럼4라고 하면

    WHERE 컬럼1 = ? 
    AND 컬럼2 = ? 
    AND 컬럼3 BETWEEN ? AND ? 
    AND 컬럼4 = ? 

• 컬럼3까지만 인덱스를 타고 그 다음 컬럼에서는 인덱스를 타지 않고 필터만 한다.

  *DBMS가 해당 컬럼(혹은 컬럼 조합)에 만들어진 인덱스를 사용해서, 테이블 전체를 스캔하지 않고 원하는 레코드에 빠르게 접근했다는 의미입니다.
  
  

번외 :

여기저기 알아보다보니 “인덱스탄다”라는 표현이 무슨 말이지 몰라서 찾아봤다. 업게에서 비공식으로 사용하는 표현이라고 한다.

• 인덱스 탄다 = “인덱스를 효과적으로 사용해서 빠르게 데이터를 찾았다는 말”
• 쿼리 성능의 핵심지표이므로 항상 EXPLAIN으로 확인하고 인덱스를 할 수 있는 쿼리를 작성해야 바람직
  
  

인덱스 타는경우

SELECT * FROM users WHERE age = 30;

• 만약 age 컬럼에 인덱스가 있다면, 이 쿼리는 인덱스를 타고(age index) 원하는 레코드를 빠르게 찾을 수 있습니다.
• 인덱스를 사용하여 빠르게 조건을 만족하는 데이터를 탐색
  
  

인덱스 안타는 경우

• 함수나 연산사용
• LIKE앞부분에 와일드카드 사용
• OR조건사용
• 데이터타입이 불일치 시
• BETWEEN IN이후 컬럼, 당사자는 인덱스 탄다
• 옵티마이저가 판단하기에 인덱스가 도움안될때 풀스캔함
• 결합 인덱스 순서 무시한 조건
  
  

• 예시 : 와일드카드

  SELECT * FROM users WHERE name LIKE '%john';

  
  
• 예시 : OR

  • age, name 각각 인덱스가 있어도 OR로 묶이면 인덱스 병합이 안 됨

    SELECT * FROM users WHERE age = 30 OR name = 'john';

    
    
• 예시 : 결합 인덱스 순서 무시한 조건
  • col1조건이 선행으로 들어가야함
  • col2만 있어서 안된다.

    INDEX (col1, col2)
    SELECT * FROM users WHERE col2 = 'A';  

    
    

• 예시 : 함수사용
  • name컬럼에 인덱스가 있더라도 LOWER()함수를 사용해서 full scan한다. 이를 인덱스 안탄다라고 함.
  • 인덱스를 못 써서 테이블 전체를 스캔함.

    SELECT * FROM users WHERE LOWER(name) = 'john';

• 예시 : between 범위조건들어가면 안됨

  INDEX (col1, col2, col3)
  
  SELECT * FROM users 
  WHERE col1 = ? AND col2 BETWEEN ? AND ? AND col3 = ?;

  
  
  
  

전체 요약

• 복합 인덱스는 단순히 여러 컬럼을 묶는 것 이상의 전략이 필요하다.
• WHERE 절의 조건 순서, 조건의 형태(=, BETWEEN 등), 데이터의 분포도, 조회 컬럼의 범위 등을 모두 고려해야인덱스를 “탄다”
• 단순히 인덱스를 추가하거나 순서를 바꾸는 것은 오히려 성능을 떨어뜨릴 수 있으므로, 항상 EXPLAIN 실행계획을 통해 실제로 어떤 인덱스가 쓰이고 있는지를 확인하는 습관이 중요하다.
  
  
  

새로운 개념 : Covering Index

커버링 인덱스(Covering Index) 와 복합 인덱스(Composite Index) 는 비슷해 보이지만, 개념적으로 다릅니다.

정의:

• 쿼리가 사용하는 모든 컬럼이 인덱스 안에 포함되어 있어, 테이블 접근 없이 인덱스만으로도 쿼리를 처리할 수 있는 경우에 사용하는 인덱스

다시말하자면, 보통 DB가 쿼리 처리시

• 인덱스를 탐색하여 원하는 레코드 주소를 찾아내고
• 그 주소를 따라서 테입르의 실제 데이터를 가져온다.(읽어) 이 과정을 Row Lookup 또는 테이블엑세스라 부른다. (비효율적이다)
• 하지만 쿼리에 사용된 컬럼이 전부 인덱스에 포함되어 있다면 굳이 테이블을 읽을 필요없다. (커버링 인덱스 사용으로 효율적으로 처리)
  
  

커버링 인덱스 예시
1. 테이블 생성

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  email VARCHAR(255)
);

2. 인덱스 생성

CREATE INDEX idx_users_age_email ON users (age, email);

3. 커버링 인덱스 사용 - age, email둘다 인덱스에 포함되어 있다. 그래서 테이블엑세스하지않고 인덱스로만 접근함

SELECT email FROM users WHERE age = 30;

4. 커버링 인덱스 사용불가 - `name` 컬럼은 인덱스에 없음

SELECT name FROM users WHERE age = 30;

적용조건은

• SELECT 컬럼 + WHERE 조건 컬럼이 모두 인덱스 안에 포함되어야 함

장점은

• 빠른 성능 (디스크 I/O 줄어듦), Row Lookup 제거

단점은

• 인덱스 크기 증가, INSERT/UPDATE 비용 증가
  
  
  
  
  

커버링 인덱스 확인하기 : 익스플랜

EXPLAIN SELECT email FROM users WHERE age = 30;

• Extra 컬럼에 Using index 라고 뜨면 → Covering Index 사용 중이라는 뜻
• 반대로 Using where; Using index → 조건은 인덱스로 처리했지만, 추가로 테이블도 읽음
  
  
  
  

커버링 인덱스 적절한 사용 팁

• 자주 조회되는 정적 데이터 테이블이라면 covering index 적극 활용
• 작고 읽기 많은 테이블에서 성능 최적화 효과가 큼
  
  
  
  
  
  

커버링 인덱스, 복합인덱스 비교 정리

• 커버링 인덱스는 "결과적으로 인덱스만으로 모든 쿼리를 처리할 수 있느냐"에 관한 개념 이고,
• 복합 인덱스는 "두 개 이상의 컬럼을 묶어 만든 인덱스"라는 구조적인 개념
• 복합 인덱스가 쿼리 조건 + 조회 컬럼을 모두 포함하면 → 커버링 인덱스임
• 커버링 인덱스는 기능적 개념
• 복합 인덱스는 구조적 개념