복합 인덱스의 첫 열이 빠지면 MySQL은 어떻게 찾을까

복합 인덱스의 첫 열 조건이 없다고 해서 인덱스를 어떤 방식으로도 못 읽는 것은 아닙니다. 다만 뒤쪽 열만으로는 일반적인 선두 접두부 탐색을 할 수 없으며, 전체 인덱스 스캔이나 조건이 맞는 skip scan 등 다른 접근을 검토해야 합니다.

여기서는 MySQL 8.4의 (school_id, student_id, term) 인덱스를 기준으로 설명합니다. 인덱스가 후보가 되는 것과 실제 실행 계획에 선택되는 것은 다른 판단입니다.

학교부터 정렬된 명단을 그려 보기

다음은 원리를 설명하려고 만든 여섯 행입니다. school_id가 먼저, 같은 학교 안에서는 student_id가 다음, 같은 학생 안에서는 term이 마지막 기준입니다. 성적이나 이름은 이 인덱스의 열에 들어 있지 않다고 가정합니다. 학생 번호만 보고 표 전체가 번호순일 것이라고 생각하면 탐색 범위를 잘못 잡습니다.

school_id=10이면 앞의 세 항목이 하나의 묶음입니다. 여기에 student_id=80까지 정하면 두 항목으로 좁힙니다. 반면 student_id>70만으로 찾으려면 학교 10의 두 항목, 학교 20의 한 항목, 학교 30의 한 항목을 모아야 합니다. 학교 10의 끝과 학교 20의 시작 사이에는 조건을 만족하지 않는 번호 15가 끼어 있습니다.

이 그림에서 접두부는 문자열의 앞부분이 아니라 인덱스 열 목록의 왼쪽부터 이어지는 조합입니다. (school_id), (school_id, student_id), 세 열 전체는 접두부지만 (student_id, term)은 아닙니다. WHERE에 조건을 적은 문장 순서보다 인덱스가 정의된 열 순서가 중요합니다.

첫 열이 없을 때 후보를 세 가지로 나누기

SELECT school_id, student_id, term
FROM enrollment
WHERE student_id > 70;

전체 인덱스 스캔은 인덱스 항목을 넓게 읽은 뒤 조건을 판정하는 방식입니다. 인덱스에 필요한 열이 모두 들어 있으면 이것도 선택지가 될 수 있습니다. '인덱스를 쓴다'는 표현만으로 소수 항목에 바로 도달한다고 결론 내리면 안 됩니다. 테이블 전체 스캔과 전체 인덱스 스캔, 조건 범위 탐색은 구별해서 읽어야 합니다.

MySQL의 skip scan은 앞부분의 서로 다른 값을 차례로 찾고, 그 값마다 뒤쪽 열의 부분 범위를 탐색할 수 있습니다. 위 그림에서는 학교 10에서 번호 70 초과, 학교 20에서 번호 70 초과, 학교 30에서 번호 70 초과를 따로 찾는 모습으로 이해할 수 있습니다. 이것은 알고리즘을 이해하기 위한 그림이며 실제 읽기 횟수나 실행 시간 측정값은 아닙니다.

8.4 문서의 적용 전제에는 단일 테이블, GROUP BY와 DISTINCT가 없는 질의, 인덱스에 포함된 열만의 참조, 건너뛸 열 뒤의 범위 조건 등이 있습니다. 선행 동등 조건과 이후 조건의 결합 형식에도 제한이 있습니다. 예제는 이런 전제를 검토하기 쉽게 단순화했으며, 조건을 충족하더라도 비용 판단 때문에 다른 접근이 선택될 수 있습니다.

기억할 규칙과 실제 계획을 분리하기

노트에 '첫 열 없음=무조건 전체 테이블'이라고 적기보다 '선두 접두부 탐색은 어려움, 다른 인덱스 접근은 별도 확인'이라고 적어 보세요. 작은 표의 결과 네 행은 손으로 확정할 수 있지만, 어떤 방식이 빠른지는 표만으로 확정할 수 없습니다. 학교 수, 값 분포, 통계와 비용이 달라지면 같은 SQL도 다른 계획을 가질 수 있습니다.

1. 인덱스의 열 순서를 적고 조건이 걸린 열에 표시합니다.
2. 결과 행부터 계산합니다. 위 표에서 번호 70 초과는 네 행입니다.
3. 허가된 연습 DB에서 EXPLAIN FORMAT=TRADITIONAL로 계획을 확인합니다.
4. Extra의 Using index for skip scan을 확인합니다. possible_keys에 이름이 있다는 사실만으로 선택됐다고 적지 않습니다.
5. 실제 시간을 비교할 때는 EXPLAIN ANALYZE가 질의를 실행한다는 점을 확인하고 작은 연습 데이터만 사용합니다.

실행 계획을 보기 전에 답을 먼저 정해 놓으면 그 답에 맞는 열만 읽기 쉽습니다. 결과의 정확성, 접근 방식, 측정 성능을 노트의 세 칸으로 나눠 보세요. 이 글은 문서와 자작 예제를 바탕으로 한 설명이며 특정 데이터에서 skip scan이 선택되었다는 실행 결과를 제시하는 글은 아닙니다.