복합 인덱스의 순서는 중요하다

목차

0. 작성 계기
1. 인덱스란?
2. 인덱스 컬럼 기준
3. 복합 인덱스의 순서는 중요하다
4. 예시 사례 - 4개의 복합 인덱스
   1. 1번 인덱스만 타는 경우
   2. 1번, 2번 인덱스를 타는 경우
   3. 2번, 1번 인덱스를 타는 경우 (역방향)
   4. 1번, 4번 인덱스를 타는 경우
   5. 2번 ~ 4번 인덱스를 타는 경우
5. 결론
6. 참고 자료

0. 작성 계기

현업에서는 쿼리 작성 후 해당 쿼리가 적절한지에 대한 판단을 DBA분께 문의를 드립니다.
만약 성능이 좋지 못한 쿼리라면 개선책을 제시해주시며, 개발자는 이를 따라 쿼리를 수정하게 됩니다.

개발자가 DB에 대한 깊은 이해를 하지 못한 상황에서도 적절성에 대한 유효성을 체크해주시니 좋은 일이지만,
이에 의존하여 가끔은 내가 짠 쿼리가 제대로 인덱스를 타는지 아닌지를 모르는 경우가 발생합니다.

특히 업무가 바쁘거나, 생각보다 단순한 쿼리일 경우 DBA님께 쿼리 검수 문의를 드리지 않고 서비스에 반영이 되는 상황이 발생하기도 합니다.
여기서 예상치 못한 이슈가 발생되어 Slow Query가 발생하는 경우도 가끔씩 마주하게 됩니다.

이번 글은 해당 경험을 토대로, 적어도 내가 짠 쿼리를 EXPLAIN으로 분석했을때 인덱스를 제대로 타는지 파악하고 스스로 쿼리 튜닝을 할 수 있기 위해 작성하게 됐습니다.

단순히 인덱스를 타냐 아니냐에 중점을 두기보다는, 어떻게 인덱스를 타게 되는 것인지에 대한 궁금증과 흐름에 기반을 두어 작성했습니다.

요즘은 AI힘을 빌려 쿼리의 효율성을 쉽게 체크 할 수 있지만, 최근에는 너무 AI에 의존하게되어 내가 짠 쿼리가 스스로 적절한지 판단력을 잃어버렸다는 느낌을 받았습니다.

설령 AI의 도움을 받더라도, 최소한 AI가 대답한 내용을 CS적인 지식에 기반하여 뿌리부터 이해할 수 있도록, 보다 원초적인 개념에 집중하여 글을 작성했습니다.

짧은 시간동안 휘갈겨 쓴 글인 관계로, 잘못된 부분에 대한 피드백은 언제나 환영합니다!

1. 인덱스란?

인덱스 == 정렬

해당 소주제의 내용은 향로님의 [mysql] 인덱스 정리 및 팁 내용을 빌려왔습니다.

인덱스는 추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블의 검색 속도를 향상시키기 위한 자료구조 입니다.
비유를 하자면, 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것입니다.

한 가지 중요한 점은, 인덱스를 세움으로서 Insert, Update, Delete (= Command)의 성능을 희생하고, 대신 Select (= Query)의 성능을 향상시킵니다.

주의해야할 점은 Update, Delete의 행위가 느린것이지, Update, Delete를 하기 위해 해당 데이터를 조회하는 것은 인덱스가 있으면 오히려 빠르다는 점 입니다.

때문에 인덱스가 없는 컬럼을 조건으로 Update, Delete를 하게 되면 타겟 데이터를 찾는 지점에서부터 시간이 오래걸릴 수 있으므로, 결론적으로 작업의 속도가 느릴 수 있습니다.
많은 양의 데이터를 삭제해야하는 상황에서는 오히려 인덱스로 지정된 컬럼을 기준으로 진행하는 것을 권장합니다.

MySQL에서 인덱스는 B+Tree 알고리즘으로 구성되어있지만, 조금 더 이해하기 쉬운 B-Tree를 기반으로 인덱스의 탐색 구조를 설명하겠습니다.
B+Tree는 B-Tree의 개념에서 확장되었으므로, 하기 개념은 B+Tree에도 동일하게 해석될 수 있습니다.

• 인덱스 탐색은 Root -> Branch -> Leaf -> 디스크 저장소 순으로 진행됩니다.
  • 예를 들어 Branch (페이지번호 2) 는 dept_no가 d001이면서 emp_no가 10017 ~ 10024까지인 Leaf의 부모로 있습니다.
  • 즉, dept_no=d001 and emp_no=10017로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터파일의 주소를 불러와 반환하는 과정을 하게 됩니다.
• 복합 인덱스(= 복수개의 인덱스)의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있습니다.
  • 만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고,
    4번째 컬럼은 3번째 컬럼에 의존합니다.

• 디스크에서 읽는 것은 메모리에서 읽는것보다 성능이 훨씬 떨어집니다.
  • 결국 인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있습니다.

2. 인덱스 컬럼 기준

카디널리티(Cardinality)라는 개념을 먼저 이해해야 합니다.
카디널리티란 DB에서 특정 컬럼의 고유 값의 수를 나타내는 개념입니다.
즉, 해당 컬럼이 얼마나 중복되지 않고 다양한 데이터를 가지고 있는지를 표현합니다.

예시로 높은 카디널리티는 컬럼의 값이 대부분 고유한 경우를 가리킵니다. (ex : 주민등록번호)
반대로 낮은 카디널리티는 컬럼의 값에 중복이 어느정도 존재하는 경우를 가리킵니다. (ex : 성별)

때문에 인덱스는 높은 카디널리티를 갖는 경우, 다시 말해 중복되지 않은 고유 값이 많은 경우로 컬럼을 설정하는 것이 적합합니다.

3. 복합 인덱스의 순서는 중요하다

아래와 같은 테이블과 복합 인덱스가 존재한다고 가정하겠습니다.

-- create
CREATE TABLE member_promotions (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 기본 키
    member_no INT NOT NULL,            -- 회원 번호
    from_date DATE NOT NULL,           -- 시작 날짜
    is_bonus BOOLEAN NOT NULL,         -- 보너스 여부 (0 또는 1)
    is_promoted BOOLEAN NOT NULL,      -- 승급 여부 (0 또는 1)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 생성 시각
);

-- 복합 인덱스 생성
CREATE INDEX idx_member_promotion 
ON member_promotions (member_no, from_date, is_bonus, is_promoted);

-- insert
INSERT INTO member_promotions (member_no, from_date, is_bonus, is_promoted) VALUES
(1, '2025-01-01', TRUE, FALSE),
(1, '2025-01-02', FALSE, TRUE),
(1, '2025-01-03', TRUE, TRUE),
(1, '2025-01-04', FALSE, FALSE),
(1, '2025-01-05', TRUE, FALSE),
(2, '2025-01-06', FALSE, TRUE),
(3, '2025-01-07', TRUE, TRUE),
(4, '2025-01-08', FALSE, FALSE),
(5, '2025-01-09', TRUE, FALSE),
(6, '2025-01-10', FALSE, TRUE);

여기서 인덱스의 순서는 얼마나 중요할까요?
관련해서 테스트를 진행해보겠습니다.

참고로 모든 테스트는 MySQL 8.0 버전에서 진행됐으며,
간편하게 테스를 원하시면 Online MySQL (8.0 ver)에서 시험해보실 수 있습니다.

(1) 1번 인덱스만 타는 경우

-- Only First Index
EXPLAIN SELECT from_date FROM member_promotions 
WHERE member_no = 1

위의 쿼리는 인덱스를 탈까요?

type : 각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식
key : 최종 선택된 실행 계획에서 사용되는 인덱스
extra : 옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값

예상대로 해당 쿼리는 인덱스를 탑니다.
이는 key에 언급되는 idx_member_promotion(= 복합 인덱스)과 Extra의 Using Index로 확인이 가능합니다.

쿼리가 반환해야하는 모든 데이터(= from_date)가 인덱스에 포함되어있기 때문에,
데이터를 찾기 위해 디스크(= 데이터 파일) 까지 접근하지 않고, 인덱스라는 별도의 저장 공간에서 원하는 데이터를 찾을 수 있다는 해석이 됩니다.

이처럼 데이터 테이블에 접근하지 않고 인덱스만으로 쿼리를 처리하는 방식을 커버링 인덱스(Covering Index)라고 합니다.

이번에는 인덱스가 포함되지 않는 부분까지 모두 SELECT 해보도록 하겠습니다.

-- Only First Index
EXPLAIN SELECT * FROM member_promotions 
WHERE member_no = 1

key에 언급되는 idx_member_promotion(= 복합 인덱스)은 동일하지만, Extra가 Using Index에서 NULL로 바꼈습니다.
이는 인덱스를 활용해 WHERE의 필터링은 이루어졌으나, SELECT *에 해당하는 모든 필드가 복합 인덱스로 설정되어있지 않은 상태이기 때문에 SELECT의 필드를 완성하기 위해 테이블을 추가로 읽어왔다는 뜻입니다.
따라서 테이블의 접근이 필요하므로, Using Index로 표시되지 않고 대신 Extra에는 NULL로 표기됩니다.
당연하지만 이는 Using Index보다 속도가 떨어질 수 밖에 없습니다.

그렇다면 해당 쿼리는 항상 인덱스를 활용해 WHERE의 필터링이 이루어질까요?
아래와 같이 카디널리티를 낮춰서 테스트를 진행해보겠습니다.

-- insert
INSERT INTO member_promotions (member_no, from_date, is_bonus, is_promoted) VALUES
(0, '2025-01-01', TRUE, FALSE),
(1, '2025-01-02', FALSE, TRUE),
(1, '2025-01-03', TRUE, TRUE),
(1, '2025-01-04', FALSE, FALSE),
(1, '2025-01-05', TRUE, FALSE),
(1, '2025-01-06', FALSE, TRUE),
(1, '2025-01-07', TRUE, TRUE),
(1, '2025-01-08', FALSE, FALSE),
(1, '2025-01-09', TRUE, FALSE),
(1, '2025-01-10', FALSE, TRUE);

-- Only First Index
EXPLAIN SELECT * FROM member_promotions 
WHERE member_no = 1;

꼭 그렇지는 않은것 같습니다.

이번에는 type은 ALL, key는 null, 그리고 Extra는 Using Where로 출력이 됐습니다.
type에 선언된 ALL은 테이블의 모든 데이터를 스캔하는 풀 테이블 스캔(Full Table Scan)을 뜻합니다.

[ type 컬럼에 표기될 수 있는 값들 (12개) ]
- system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL
- ALL은 제외한 나머지는 모두 인덱스를 사용하는 접근 방식
- system -> ALL로 갈수록 성능이 느려짐

이는 인덱스를 타는 것을 결정하는 역할은 개발자가 작성한 쿼리를 기반으로 옵티마이저가 결정하기 때문입니다.
(* 옵티마이저 : SQL의 실행 계획을 수립하고 SQL을 실행하는 DB 엔진)

때문에 아무리 인덱스 컬럼으로 조회 쿼리를 실행해도, 인덱스를 타는 것 보다 더 빠른 방법이 있으면 옵티마이저는 다른 선택지를 실행합니다.

(2) 1번, 2번 인덱스를 타는 경우

-- First and Second Indexes
EXPLAIN SELECT from_date, is_bonus FROM member_promotions 
WHERE member_no = 1 
AND from_date = '2025-01-01';

마찬가지로 인덱스를 탑니다.

그러나 (1)번과 마찬가지로 카디널리티가 낮은 경우, 옵티마이저는 인덱스를 타지 않고 Full Table Scan을 선택할 수 있습니다.

(3) 2번, 1번 인덱스를 타는 경우 (역방향)

이번엔 반대로 인덱스를 타는 순서를 거꾸로 해보겠습니다.

-- Second and First Indexes
EXPLAIN SELECT from_date, is_bonus FROM member_promotions 
WHERE from_date = '2025-01-01'
AND member_no = 1

분명 위의 인덱스 설명에서는 두번째 컬럼의 인덱스를 타기 위해서는 첫 번째 컬럼의 인덱스를 반드시 탐색해야 한다고 언급했습니다.

하지만 인덱스를 타는 것으로 확인됐습니다.
왜 그러는 걸까요? 이는 옵티마이저가 조건을 재배열했기 때문입니다.

[ 조건 재배열 ]
- MySQL 옵티마이저는 쿼리의 `WHERE` 조건을 분석하여 복합 인덱스의 순서에 맞게 조건을 재배열 할 수 있습니다. 
- 즉, 내부적으로는 `member_no = 1 AND from_date = '2025-01-01'`로 해석되어 인덱스를 활용합니다.

(4) 1번, 4번 인덱스를 타는 경우

-- First And Fourth Indexes
EXPLAIN SELECT from_date, is_bonus FROM member_promotions 
WHERE member_no = 1
AND is_promoted = TRUE;

이 경우엔 인덱스를 탈까요?

마찬가지로 인덱스를 탄다고 나옵니다.

그런데 한 가지 눈여겨 볼 점은, EXTRA가 Using Where, Using Index라는 점입니다.
해석을 하면 인덱스를 타긴 탔지만, WHERE을 통한 필터링도 이루어진 것으로 유추할 수 있으며,
member_no에서는 인덱스를 타긴 탔지만, is_promoted에서는 WHERE을 통한 필터링이 이루어졌음을 뜻합니다.

분명 is_promoted도 복합 인덱스에 설정이 되어있는데,
왜 인덱스를 바로 타지 않고 WHERE을 통한 필터링이 이루어진걸까요?
이유는 초반에 설정한 복합 인덱스의 순서에서 확인할 수 있습니다.

CREATE INDEX idx_member_promotion 
ON member_promotions (member_no, from_date, is_bonus, is_promoted);

보다시피 is_promoted는 가장 마지막 인덱스로 위치하고 있습니다.
그리고 본문의 초반에 인덱스와 관련해서 아래와 같이 설명을 남겼습니다.

• 복합 인덱스(= 복수개의 인덱스)의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있습니다.
  • 즉, 두번째 컬럼의 인덱스를 타기 위해서는 첫 번째 컬럼의 인덱스를 반드시 탐색해야 합니다.
  • 만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고, 4번째 컬럼은 3번째 컬럼에 의존합니다.

다시 말해 is_promoted는 복합 인덱스의 마지막 열이며, 선행 열(from_date is_bonus)이 없기 때문에 탐색 조건(= 인덱스)으로 직접 사용되지 않습니다.
그 대신 member_no 인덱스를 먼저 탐색한 후, 커버링 인덱스로 추가적인 WHERE 조건에 따라 필터링이 이루어집니다.

Q : 근데 인덱스를 먼저 타면 Using where; Using index가 아니라 Using index; Using where가 나와야하지 않아요?
index를 먼저 타는게 이루어지고, 그 다음에 where로 필터링이 이루어지는게 순서니까요.
A : MySQL에서 WHERE 조건의 사용 여부를 먼저 표시하는 것이 관례입니다. 쿼리를 작성할때도 조건문에는 WHERE이 먼저 나오죠.

Q : WHERE을 통한 필터링이 이루어졌다고 하는데, 이는 디스크에 접근해서 필터링이 이루어진걸까요?
A : is_promoted가 복합 인덱스 중 하나로 선언되었기 때문에, 커버링 인덱스의 개념을 따라 해당 WHERE 조건은 디스크까지 가지 않고 인덱스에서 필터링 됐습니다.

여기서 한 가지 다른 테스트를 시도해보겠습니다.
직전 테스트에서는 복합 인덱스에 해당하는 컬럼을 SELECT 했지만, 만약 그렇지 않은 컬럼을 SELECT하면 어떻게 될까요?

-- First And Fourth Indexes
EXPLAIN SELECT created_at 
FROM member_promotions 
WHERE member_no = 1 AND is_promoted = TRUE;

Extra에 이전과는 다른 조건이 나왔습니다.
인덱스를 타긴 타는 것 같은데, 무언가 추가적인 조건이 적용된 것 같습니다.
이는 인덱스 조건 푸시 다운 (Index Condition Pushdown, 이하 ICP)가 이루어졌음을 뜻합니다.

[ 인덱스 조건 푸시다운 (ICP) ] 
- MySQL 5.6이상 버전부터 도입됐으며, 복합 인덱스의 일부 조건이 충족되면 나머지 조건을 인덱스 스캔 중에 필터링 할 수 있습니다.
- 즉, WHERE 조건의 일부 또는 전부를 인덱스에서 먼저 처리한 다음, 필요할 경우 데이터 테이블에 접근합니다.
- 이를 통해 불필요한 디스크 I/O를 줄일 수 있습니다.

이를 통해 아래와 같은 프로세스로 쿼리가 실행됐음을 유추할 수 있습니다.

1. member_no = 1의 조건으로 인덱스를 탐
2. is_promoted = TRUE는 커버링 인덱스로 인해 인덱스 스캔으로 필터링이 이루어짐
3. 그러나 created_at은 인덱스로 설정되지 않았으므로, 테이블에 접근해서 데이터를 가져옴

따라서 인덱스를 타는 상황일지라도, Extra에 출력되는 결과 값에 따라 아래와 같은 순서로 순위를 매길 수 있습니다.

(제일 빠름) Using index -> Using where; Using index -> Using index condition (제일 느림)

(5) 2번 ~ 4번 인덱스를 타는 경우

-- All Indexes Without First Index
EXPLAIN SELECT from_date, is_bonus FROM member_promotions 
WHERE from_date = '2025-01-01'
AND is_bonus = TRUE
AND is_promoted = FALSE;

마지막으로 1번 인덱스를 제외한 나머지 인덱스로 조회를 진행해보겠습니다.

type은 index고, extra는 Using where; Using index로 출력됐습니다.
분명 본문의 첫 번째에 아래와 같이 언급을 했다는 점을 기억해야 합니다.

• 복합 인덱스(= 복수개의 인덱스)의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있습니다.
  • 즉, 두번째 컬럼의 인덱스를 타기 위해서는 첫 번째 컬럼의 인덱스를 반드시 탐색해야 합니다.
  • 만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고, 4번째 컬럼은 3번째 컬럼에 의존합니다.

복합 인덱스의 첫 번째 컬럼을 타지 않고있는데도, 위의 쿼리는 인덱스를 타는 것으로 출력됐습니다.
무슨 일이 일어난걸까요? 이를 이해하기 위해서는 먼저 type이 ref가 아닌 index가 출력됐음을 이해해야 합니다.

[type : ref]
- 참조 조건으로 특정 값을 기준으로 검색
- 주로 복합 인덱스의 일부를 활용하거나 WHERE 조건의 =, 또는 연산자 IN을 활용
- 인덱스를 활용하여 특정 값에 대한 행을 검색할 때 주로 출력됩니다.

[type : index]
- 인덱스 전체를 순차적으로 스캔하는 Index Full Scan을 뜻합니다.
- Table Full Scan은 테이블 전체를 읽는 것을 뜻하지만, Index Full Scan은 설정된 인덱스 전체를 스캔하는 것을 뜻합니다.
- 즉, 복합 인덱스의에서는 복합 인덱스에 선언된 모든 컬럼을 순서대로 풀 스캔을 하는 것을 뜻합니다.

비록 복합 인덱스의 첫 번째 컬럼을 타지 않는 상황이더라도, 옵티마이저는 커버링 인덱스를 통해 테이블 풀 스캔이 아닌 인덱스 풀 스캔을 채택해 테이블까지는 접근하지 않고 인덱스의 필터링을 통해서 데이터를 가져왔음을 유추할 수 있습니다.

4. 결론

• 복합 인덱스의 순서는 중요하며, 선두에 서는 인덱스의 카디널리티가 높을수록 성능이 더 좋습니다.
• 인덱스를 타는 쿼리를 EXPLAIN으로 실행 계획을 확인한 뒤 확인할 수 있는 Extra의 뜻은 하기와 같습니다.
• (제일 빠름) Using index -> Using where; Using index -> Using index condition (제일 느림)
• Using where; Using index -> 커버링 인덱스와 관련 있음 -> 테이블 접근 없이 인덱스에서 해결
  Using index condition -> 인덱스 컨디션 푸쉬다운 (ICP)와 관련 있음 -> 필요할 경우 테이블에 접근
• 인덱스의 갯수는 3~4개가 적당합니다.
  • 인덱스는 결국 추가적인 저장 공간을 할당하는 자료구조이므로, 과도한 인덱스 생성은 성능 저하를 야기할 수 있습니다.
• 인덱스로 조회한다고 항상 인덱스를 타는 것은 아닙니다.
  • 옵티마이저의 판단에 따라 쿼리 실행이 결정됩니다.
  • 카디널리티가 낮은 컬럼이 복합 인덱스의 첫 번째 컬럼으로 설정될 경우, Full Table Scan이 발생할 수 있습니다.

5. 참고 자료

• Real MySQL 8.0 (저자 : 백은빈, 이성욱)
• 향로님의 [mysql] 인덱스 정리 및 팁
• 향로님의 1. 커버링 인덱스 (기본 지식 / WHERE / GROUP BY)
• doogang님의 [MySQL] index condition pushdown이란