MySQL의 기본 -8

MySQL이 인덱스를 어떻게 이용하는가?

인덱스 레인지 스캔

MySQL에서는 검색하려는 값의 개수나 검색 결과의 레코드 건수와 관계 없이, 특정 범위를 스캔하게 된다면 레인지 스캔이라고 표현한다. 인덱스 레인지 스캔(Index Range Scan)은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 레인지 스캔을 수행할 때는 B-Tree 인덱스의 리프 노드를 스캔하면서, 실제 데이터 파일에서 레코드를 읽어오는 과정이 필요할 수도 있다. 이 과정에서 다수의 랜덤 I/O가 발생할 수 있으므로, 경우에 따라 테이블 풀 스캔(Table Full Scan)이 더 효율적일 수도 있다.

과정

1. 인덱스 탐색(Index Lookup)

• B-tree 인덱스에서 루트 노드와 브랜치 노드를 이용해 탐색을 시작할 위치를 찾는 과정이다.
• 검색 조건을 만족하는 첫 번째 인덱스 키 값이 저장된 위치를 찾는다.
  • (ex: WHERE age >= 30 이라면, age = 30 인 값이 저장된 리프 노드의 위치를 찾는다.)
• 이 과정에서 트리의 깊이에 따라 디스크 I/O가 발생할 수 있다.

2. 인덱스 스캔(Index Scan)

• 탐색된 위치부터 검색 조건에 맞는 인덱스를 차례대로 순차 읽기(순차 I/O)한다.
• 읽는 방향은 두 가지가 있다.
  • ASC(오름차순) → 작은 값에서 큰 값으로 스캔
  • DESC(내림차순) → 큰 값에서 작은 값으로 스캔
• 이 과정은 인덱스의 정렬 특성 때문에 별도의 정렬 작업 없이 자동으로 정렬된 데이터를 가져오는 장점이 있다.
• 리프 노드의 인덱스 키만 읽는 경우 I/O 비용이 적지만, 이후 실제 데이터 파일의 레코드를 읽어야 하는 경우 추가 I/O 비용이 발생할 수 있다.

3. 데이터 레코드 접근

• 인덱스 리프 노드에서 검색 조건에 일치하는 레코드 주소(ROWID)를 확인한 후, 실제 데이터 레코드를 읽어온다.
• 이때, InnoDB의 경우 리프 노드에 프라이머리 키 값을 저장하고 있으므로, 해당 프라이머리 키를 기반으로 클러스터 인덱스를 조회하여 레코드를 가져온다.
• 랜덤 I/O가 발생하는 구간이며, 데이터 건수가 많을 경우 성능 저하의 원인이 된다.

적합한 경우

1. 범위 검색 연산(Range Condition)
   • where 절에서 범위 조건이 사용된 경우

select * from employees where salary > 50000;

salary 컬럼에 인덱스가 존재한다면, 해당 인덱스를 활용하여 "50000보다 큰 값"을 가진 키 값들을 연속적으로 읽는 방식으로 검색이 가능하다.  

2. 부등호 연산자
   • 부등호 조건을 사용할 경우에도 레인지 스캔이 활용될 수 있다.

SELECT * FROM employees WHERE age >= 30 AND age <= 50;

 age 컬럼에 인덱스가 존재하면, "30 이상 50 이하"에 해당하는 키 값들을 순차적으로 읽는 방식으로 스캔이 수행된다.  

3. LIKE 연산자 (접두사 일치 검색)
   • LIKE 'ABC%' 처럼 문자열의 앞부분이 일치하는 경우에도 인덱스를 활용할 수 있다.

SELECT * FROM customers WHERE name LIKE 'Kim%';

name 컬럼이 B-Tree 인덱스로 정렬되어 있으므로, "Kim"으로 시작하는 값들이 연속적으로 정렬되어 있어 인덱스 레인지 스캔이 가능하다.  

4. ORDER BY 및 GROUP BY 절 사용

ORDER BY 또는 GROUP BY 연산을 수행할 때, 인덱스 컬럼의 정렬 순서와 동일하게 조회하면 추가 정렬 작업을 하지 않아도 되므로 효율적이다.

SELECT * FROM employees ORDER BY salary ASC;

salary 컬럼이 인덱스라면, 정렬된 상태로 값을 가져올 수 있어 별도의 정렬 작업(Sorting)이 필요하지 않음.  

인덱스 레인지 스캔의 성능 고려 사항

인덱스 레인지 스캔이 항상 최적의 선택이 되는 것은 아니다. 다음과 같은 경우에는 성능 저하가 발생할 수 있다.

1. 데이터 레코드 접근으로 인한 랜덤 I/O 문제
   • 리프 노드에서 인덱스 키만 읽는 경우는 성능이 빠르지만, 레코드 조회를 위해 테이블을 다시 읽어야 할 경우 성능이 저하될 수 있다.
   • 레코드를 읽기 위해 랜덤 I/O가 발생하기 때문이다.
   • 예를 들어, 다음과 같은 쿼리는 id가 기본키(PK) 인덱스이므로 인덱스만 조회하고 데이터 접근 없이 결과를 반환할 수 있다.

SELECT id FROM employees WHERE age >= 30;

반면, 다음 쿼리는 나머지 컬럼을 조회하기 위해 실제 데이터 페이지를 추가적으로 읽어야 하므로 랜덤 I/O가 발생한다.  

SELECT * FROM employees WHERE age >= 30;

커버링 인덱스(Covering Index)를 활용하여 테이블 조회를 줄이는 것이 성능 최적화에 도움이 될 수 있다.  

2. 특정 조건에서 테이블 풀 스캔이 더 효율적인 경우
   • 레인지 스캔을 통해 읽어야 하는 데이터 비율이 전체 테이블의 20~25%를 초과하면, 테이블 풀 스캔(Table Full Scan)이 더 효율적일 수 있다.
   • 인덱스를 통해 레코드를 랜덤하게 여러 번 읽는 것보다, 전체 테이블을 한 번에 읽는 것이 오히려 비용이 낮을 수도 있기 때문이다.

예를 들어, 다음과 같은 쿼리는 직원의 절반 이상을 가져와야 하는 경우 테이블 풀 스캔이 더 빠를 수 있다.

SELECT * FROM employees WHERE age >= 20;

3. 다중 컬럼 인덱스에서 인덱스 앞부분만 활용하는 경우

복합 인덱스가 존재하는 경우, 선행 컬럼이 조건에 포함되지 않으면 인덱스를 활용할 수 없다.

SELECT * FROM employees WHERE salary > 50000;

age 컬럼이 빠져 있기 때문에, salary 조건만으로 인덱스를 활용할 수 없음.

결론

• 인덱스 레인지 스캔은 특정 범위를 검색하는 경우에 효과적인 방식이다.
• 정렬된 상태로 데이터를 조회할 수 있어 추가적인 정렬 비용이 발생하지 않는다.
• 하지만, 레코드 접근 시 발생하는 랜덤 I/O가 많을 경우 성능이 저하될 수 있으므로, 레인지 스캔의 효율성을 항상 고려해야 한다.
• 레인지 스캔을 사용할 때는, 커버링 인덱스를 활용하거나, 테이블 풀 스캔과 비교하여 최적의 실행 계획을 선택하는 것이 중요하다.

인덱스 역순 스캔(Index Reverse Scan)

인덱스 역순이 더 오래 걸리는 이유

1. B-Tree 인덱스의 구조

• B-Tree 인덱스는 루트 노드 → 브랜치 노드 → 리프 노드의 계층 구조로 구성되어 있으며, 리프 노드는 양방향이 아닌 단방향으로 연결된 구조이다.
• 각 리프 노드는 같은 레벨의 다음(오른쪽) 페이지와 연결되어 있다.
• 하지만, 이전(왼쪽) 페이지로 가는 역방향 포인터가 없다.
• 즉, 한 방향(오름차순)으로만 빠르게 탐색할 수 있도록 설계되어 있다.

2. 인덱스 정순 스캔(Forward Index Scan)

"오름차순 정렬된 인덱스에서 오름차순으로 데이터를 조회하는 경우"

• 리프 노드는 오른쪽 페이지와 연결되어 있으므로, 다음 페이지로 이동하는 것은 빠르게 가능하다.
• 페이지 내에서도 오름차순으로 정렬되어 있어 순차 접근이 가능하다.
• 순차 I/O(Sequential I/O) 가 발생하므로, 속도가 빠르다.

SELECT * FROM employees ORDER BY id ASC;

id 인덱스가 존재하면, 리프 노드의 왼쪽에서 오른쪽으로 정순 스캔이 이루어진다.
이 과정은 한 방향으로만 순차적으로 진행되므로 매우 빠르다.

3. 인덱스 역순 스캔(Reverse Index Scan)

"오름차순 정렬된 인덱스에서 내림차순으로 데이터를 조회하는 경우"

B-Tree 인덱스는 기본적으로 리프 노드 간 연결이 한 방향(오름차순)으로만 되어 있기 때문에, 역순으로 읽으려면 추가적인 연산이 필요하다. 역순으로 이동할 때 이전 페이지를 찾으려면 브랜치 노드를 다시 검색해야 하는 과정이 추가됨. 결과적으로, 랜덤 I/O(Random I/O)가 증가하여 성능이 떨어진다.

SELECT * FROM employees ORDER BY id DESC;

id 인덱스가 존재하더라도, 리프 노드의 오른쪽 끝에서 왼쪽으로 역순 스캔해야 한다. 하지만, 오른쪽에서 왼쪽으로의 직접적인 연결이 없으므로, 매번 브랜치 노드를 탐색하며 이전 페이지를 찾아야 한다. 따라서 추가적인 I/O 오버헤드가 발생하여 속도가 느려진다.

4. 역순 스캔이 더 오래 걸리는 이유

(1) 페이지 잠금이 인덱스 정순 스캔에 적합한 구조

MySQL(InnoDB)은 페이지 단위로 데이터를 관리하며, 여러 트랜잭션이 동시에 실행될 경우 페이지 잠금(Locking) 방식이 중요해진다.

일반적으로 오름차순 정렬을 기준으로 데이터를 처리하므로, 정순 스캔이 더 최적화되어 있음.
반면, 역순 스캔을 하면 페이지를 다시 찾아가면서 여러 페이지에서 잠금 충돌이 발생할 가능성이 높아진다.

(2) 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조

B-Tree의 리프 노드 페이지는 오름차순으로 다음 페이지와 연결되어 있다.
이전 페이지(왼쪽)를 가리키는 링크가 없기 때문에, 역순으로 스캔하려면 브랜치 노드에서 이전 페이지를 다시 찾아야 한다. 결과적으로, 브랜치 노드를 다시 검색하는 추가 연산이 필요하고, 이는 랜덤 I/O를 증가시켜 성능이 저하된다.

5. 성능 최적화를 위한 해결 방법

(1) 내림차순 인덱스 생성

MySQL 8.0부터는 DESC(내림차순) 인덱스를 지원하므로, 내림차순 정렬이 자주 필요하면 내림차순 인덱스를 별도로 생성하는 것이 효과적이다. 내림차순 인덱스가 있으면, 역순 스캔이 아니라 정순 스캔으로 동작할 수 있으므로 성능이 향상된다.

6. 결론

• B-Tree 인덱스의 리프 노드는 오름차순으로 정렬되어 있고, 다음(오른쪽) 페이지로 가는 포인터만 존재한다.
• 정순 스캔(오름차순 스캔)은 한 방향으로 순차적으로 진행되므로 빠르다.
• 역순 스캔(내림차순 스캔)은 이전(왼쪽) 페이지로 이동할 수 있는 포인터가 없어, 매번 브랜치 노드를 검색해야 하는 추가 연산이 필요하다.
• 랜덤 I/O가 증가하여 성능이 저하될 수 있다.
• MySQL 8.0 이상에서는 내림차순 인덱스를 활용하여 역순 스캔의 성능을 개선할 수 있다.