루즈 인덱스 스캔은 말 그대로 인덱스를 듬성듬성 읽는 방식이다. 모든 인덱스 키를 연속으로 읽는 것이 아니라 필요한 지점만 건너뛰며 읽는다.
인덱스 레인지 스캔이 구간 내의 모든 인덱스를 읽는 방식이라면 루즈 인덱스 스캔은 중간에 불필요한 인덱스 키를 과감히 생략한다.
이 스캔 방식은 GROUP BY와 집합 함수, 특히 MIN(), MAX() 최적화에서 가장 큰 효과를 발휘한다. 그 배경에는 인덱스의 정렬 특성이 있다.
루즈 인덱스 스캔 정렬 원리
인덱스는 항상 좌측을 기준으로 정렬된다. 문자열이라면 왼쪽 문자부터, 복합 인덱스라면 가장 왼쪽 컬럼부터 정렬 기준이 된다.
employees 테이블에서 first_name과 hire_date로 복합 인덱스를 구성했다고 가정하자.
정렬 순서는 다음과 같다. 먼저 first_name 기준으로 정렬되고, 같은 first_name 안에서는 hire_date가 오름차순으로 정렬된다.
즉 데이터는 “이름별로 묶인 상태에서 입사일이 정렬된 구조”가 된다.
이 상태에서 first_name으로 GROUP BY를 하고 hire_date의 MIN 값을 구한다면 각 그룹의 첫 번째 레코드만 읽으면 된다.
나머지 동일한 first_name 레코드는 굳이 읽을 필요가 없다. 이렇게 필요한 지점만 읽고 다음 그룹으로 이동하는 방식이 바로 루즈 인덱스 스캔이다.
루즈 인덱스 스캔 실행계획 비교
인덱스가 없는 상태에서 다음 쿼리를 실행해보자.
SELECT first_name, MIN(hire_date), MAX(hire_date)
FROM employees
GROUP BY first_name;
실행계획을 보면 type=ALL로 테이블 풀 스캔이 발생하고, Extra에는 Using temporary가 표시된다.
이는 MySQL 엔진이 내부 임시 테이블을 생성해 그룹핑과 집계를 처리한다는 의미다.
이 내부 임시 테이블은 처음엔 메모리에 생성되지만 크기가 커지면 디스크로 내려갈 수 있다. 사용자가 접근할 수 없는 엔진 내부 전용 테이블이지만 분명한 비용이 발생한다.
이제 인덱스를 추가해보자.
CREATE INDEX ix_loose
ON employees(first_name, hire_date);
같은 쿼리를 다시 실행하면 실행계획은 완전히 달라진다.
type은 range로 변경되고, Extra에는 Using index for group-by가 표시된다.
이는 MySQL이 루즈 인덱스 스캔을 적용해 각 그룹당 필요한 인덱스 엔트리만 읽고 있다는 의미다.
읽어야 할 rows 수도 약 30만 건에서 1천여 건 수준으로 급감한다.
내부 임시 테이블과 성능 차이
실제 실행 시간에서도 차이는 분명하게 나타난다.
인덱스가 없는 경우 쿼리 실행 시마다 Created_tmp_tables 값이 증가한다. 이는 내부 임시 테이블이 매번 생성된다는 뜻이다.
반면 루즈 인덱스 스캔이 적용된 경우 같은 쿼리를 여러 번 실행해도 Created_tmp_tables 값은 증가하지 않는다.
즉 내부 임시 테이블을 아예 사용하지 않거나 극히 제한적으로만 사용한다.
이 차이는 실행 시간에서도 그대로 드러난다. 테이블 풀 스캔은 0.12초 내외가 걸렸지만 루즈 인덱스 스캔은 0.01초 수준으로 줄어들었다.
특히 데이터가 많아질수록 이 격차는 더 커진다. 불필요한 레코드 스캔과 임시 테이블 생성 비용을 근본적으로 제거하기 때문이다.
결론적으로 루즈 인덱스 스캔은 GROUP BY와 MIN, MAX가 포함된 쿼리에서 가장 강력한 최적화 수단 중 하나다.