[mysql] 복합 인덱스와 explain

환경: mysql8+

복합 인덱스(Composite Index)는 하나의 인덱스 내에 두 개 이상의 컬럼(column)을 결합하여 생성한 인덱스로 단일 컬럼 인덱스와 달리, 여러 컬럼을 함께 고려하여 검색 성능을 향상시키기 위한 목적으로 사용된다.

 

• 복합 인덱스는 인덱스에 포함된 컬럼의 순서가 매우 중요
• 인덱스는 왼쪽에서부터 순차적으로 컬럼을 사용하여 쿼리를 최적화함
• 예를들어 두번째 컬럼의 인덱스를 타기 위해서는 첫 번째 컬럼의 인덱스를 반드시 탐색해야 함

 

explain 실행 시

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

 

type

system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL

• ALL은 제외한 나머지는 모두 인덱스를 사용하는 접근 방식
• system -> ALL로 갈수록 성능이 느려짐

• system: 테이블에 한 행만 존재하는 경우 (최고의 경우)
• const: 인덱스를 통해 한 행만 검색 가능한 경우 (매우 빠름)
• eq_ref: 각 조인에서 인덱스 키를 사용하여 단일 행을 찾는 경우 (우수)
• ref: 인덱스를 통해 여러 행을 찾는 경우 (좋음)
• range: 인덱스의 범위 스캔(RANGE scan)이 사용된 경우
• index: 인덱스 전용 스캔(Index scan)이 사용된 경우 (테이블 데이터 대신 인덱스만 읽음)
• ALL: 전체 테이블 스캔(Full Table scan)이 발생한 경우 (최악의 경우)

ref

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

index

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

 

extra

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

• Using Index: covering index 사용하여 테이블 데이터를 읽지 않고 인덱스만으로 쿼리 결과를 만족하는 경우
• Using where: 인덱스를 사용하여 데이터 접근은 하지만, WHERE 절의 조건 중 일부가 인덱스에 포함되지 않아서 서버에서 추가로 조건 검사를 수행해야 하는 경우. 즉, 인덱스 스캔 후에 반환된 결과에 대해 WHERE 조건을 한 번 더 필터링
• Using index condition: 인덱스 컨디션 푸쉬다운 (ICP) 과 관련 있음. 인덱스 스캔 시 인덱스에 포함된 컬럼에 대해 일부 조건을 미리 평가하는데 인덱스만으로 조건을 완전히 만족하지 못하는 경우(예: 인덱스에 포함되지 않은 컬럼의 조건이 있을 때) 실제 테이블 데이터에 접근하여 조건을 최종 확인하게 됨
• NULL: 인덱스는 사용하지만, 추가적인 extra 메시지가 표시되지 않는 경우로, 일반적으로 인덱스 스캔 후에도 테이블 데이터에 접근하는 경우, 인덱스 스캔 후 추가로 테이블 조회가 발생하기 때문에 디스크 I/O가 늘어나고, 전체 쿼리 실행 속도가 저하됨
• Using temporary: 쿼리 실행 중 임시 테이블이 생성되었음 (ORDER BY나 GROUP BY 등에서 사용)
• Using filesort: 인덱스가 아니라 별도의 정렬 알고리즘을 사용하여 정렬했음
• Using join buffer: 조인 시 버퍼를 사용한 조인 전략이 사용됨

 

Using where; Using index 순서?

직관적으로는 인덱스를 먼저 타고 그 후에 WHERE 조건을 적용하므로 "Using index; Using where" 순서가 자연스러워 보이지만, 실제 EXPLAIN의 extra 컬럼에 나타나는 메시지 순서는 반드시 실행 순서를 그대로 반영하지 않는다. 즉, 출력되는 순서는 내부 구현이나 옵티마이저가 수집한 여러 플래그를 특정 순서로 나열한 결과일 뿐, 실제 동작은 인덱스를 사용한 후 WHERE 조건으로 최종 결과를 필터링하는 방식이다. 표시되는 순서는 내부 구현에 따른 것이며, 성능이나 처리 순서를 해석하는 데 큰 영향을 주지 않는다.

 

인덱스 조건 푸시다운 (ICP)

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

 

참고

• 인덱스를 사용하더라도 인덱스의 카디널리티가 낮은 경우, 옵티마이저는 인덱스를 타지 않고 Full Table Scan을 선택할 수 있음
• MySQL 옵티마이저는 쿼리의 WHERE 조건을 분석하여 복합 인덱스의 순서에 맞게 조건을 재배열 할 수 있음
• 복합 인덱스의 첫 번째 컬럼을 타지 않는 상황이더라도, 옵티마이저는 커버링 인덱스를 통해 테이블 풀 스캔이 아닌 인덱스 풀 스캔을 채택해 테이블까지는 접근하지 않고 인덱스의 필터링을 통해서 데이터를 가져올 수 있음