InnoDB에서 쿼리 분할해 성능 개선하기

서론

이전에 ETL 작업을 하면서 대량의 데이터를 조회할 때 다음과 같은 단일 쿼리를 사용한 적이 있다.

SELECT *
FROM A
INNER JOIN B ON A.b_id = B.id
INNER JOIN C ON A.c_id = C.id
WHERE A.updated_at = '2025-01-01';

이 쿼리로 한번에 모든 데이터를 가져오려고 했는데 A 테이블이 데이터 양이 많고 실제로는 조인문도 10개가 넘어서 실행시간이 몇분이 걸렸다. 이를 해결하기 위해 쿼리를 두 단계로 분리하는 방식을 사용했다. 먼저 조건에 맞는 id만 조회한 후, 그 id 목록을 이용해 실제 데이터를 가져오는 방식이다.

# 첫번째 쿼리 
SELECT id
FROM A
WHERE updated_at = '2025-01-01';

# 두번째 쿼리 
SELECT A.*, B.*, C.*
FROM A
INNER JOIN B ON A.b_id = B.id
INNER JOIN C ON A.c_id = C.id
WHERE A.id IN ( /* 첫 번째 단계에서 조회된 id 목록 */ );

얼핏보면 두 방식 모두 동일한 작업을 수행하는 것처럼 보이지만 실제로는 성능에 차이가 난다. production DB에서 실제 쿼리를 날려봤는데, 1번 쿼리로 10분 단위로 조회했을때 19초 소요되던 것이 두 단계로 나누면 각각 첫번째 쿼리에서 9초, 두번째 쿼리에서 3초 정도가 소요되었다. 이번 글에서는 왜 이런 성능차이가 발생하는지 InnoDB의 인덱스 구조와 디스크 I/O 특성을 중심으로 살펴보려고 한다.

---

InnoDB 인덱스

1) 클러스터드 인덱스 (Primary Index)

• InnoDB는 테이블의 기본 키(primary key)를 기준으로 클러스터드 인덱스를 구성한다.
• 이 인덱스는 테이블의 실제 데이터 행이 저장되는 순서를 결정하며, 데이터 자체가 B+ 트리의 리프 노드에 저장된다.
• 즉, 디스크 상에서도 기본 키 순서대로 행이 정렬되어 있는 셈이라, 기본 키를 이용해 조회하면 순차 I/O가 발생할 가능성이 높다.

2) 보조 인덱스 (Secondary Index)

• 기본 키 외의 다른 컬럼에 대해 생성된 인덱스를 보조 인덱스라고 한다.
• 보조 인덱스 역시 B+ 트리 구조를 쓰지만, 실제 데이터 행을 담지 않고, 인덱스 컬럼 + 해당 행의 기본 키 값만 저장한다.

비커버링 인덱스 (Non-Covering Index)

• 보조 인덱스에 쿼리에서 필요한 모든 컬럼이 포함되어 있지 않은 경우, 이를 비커버링 인덱스라고 한다.
• 이 상황에서 쿼리가 SELECT * 같은 여러 컬럼을 요구하면, 보조 인덱스만으로는 해당 정보를 다 가져올 수 없으므로 추가로 클러스터드 인덱스(기본 키)를 통해 실제 행을 조회(bookmark lookup) 해야 한다.

조회 과정에서 발생하는 I/O

단일 쿼리

SELECT *
FROM A
INNER JOIN B ON A.b_id = B.id
INNER JOIN C ON A.c_id = C.id
WHERE A.updated_at = '2025-01-01';

1. 보조 인덱스 검색
   • updated_at에 보조 인덱스가 걸려 있다면, 쿼리는 이 인덱스를 먼저 탐색해 조건에 맞는 행들의 기본 키 값을 얻는다.
2. 실제 데이터 접근(bookmark lookup)
   • 보조 인덱스에는 updated_at 값과 PK(id) 정도만 있으므로, 쿼리가 SELECT *로 많은 컬럼을 요구하면 실제 테이블(클러스터드 인덱스)에 다시 접근해야 한다.
   • 이때 해당 PK로 클러스터드 인덱스를 한 건씩 탐색하게 되는데, 행이 많을수록 그만큼 반복해서 접근해야 한다.
3. 랜덤 I/O 발생
   • 클러스터드 인덱스가 기본 키 순서대로 정렬되어 있긴 하지만, 우리가 찾으려는 여러 PK들이 물리적으로 디스크 여기저기에 분산되어 있을 가능성이 높다.
   • 각 PK를 조회할 때마다 디스크의 서로 다른 페이지(블록)에 접근하므로 랜덤 I/O가 증가하게 된다.
   • 디스크는 순차 I/O보다 랜덤 I/O가 훨씬 느리다. 디스크 헤드가 여러 위치로 움직여야 하니 탐색 시간이 늘어난다.

랜덤 I/O vs. 순차 I/O:

• 순차 I/O는 디스크의 인접한 영역을 한 번에 읽어오기 때문에 속도 빠르다. 
  • InnoDB는 데이터를 16KB 정도 Page로 관리하는데 하나의 디스크 페이지에는 여러 행이 저장되어 있으므로, 기본 키를 이용해 조회하면 한번의 I/O으로 여러 행을 가져올 수 있다.
  • 쿼리에서 조회하는 데이터가 물리적으로 인접해 있다면 디스크 헤드 이동(seek)없이 페이지를 읽어 순차 I/O의 이점이 생긴다. 
• 랜덤 I/O는 디스크 헤드가 여러 위치로 이동하며 읽어야 하므로 탐색 시간이 증가해 성능 저하

두단계 쿼리

첫번째 단계

SELECT id FROM orders WHERE updated_at >= '2025-01-01';

•  updated_at 보조 인덱스로 기본 키(id) 목록만을 빠르게 조회
• 이 과정은 보조 인덱스 내에서 순차적인 B+ 트리 순회로 이루어지므로, 상대적으로 랜덤 I/O가 크게 발생하지 않는다

두 번째 단계

SELECT A.*, B.*, C.*
FROM A
INNER JOIN B ON A.b_id = B.id
INNER JOIN C ON A.c_id = C.id
WHERE A.id IN ( /* 첫 번째 단계에서 조회된 id 목록 */ );

• 첫 번째 단계에서 확보한 id들을 이용해 클러스터드 인덱스에서 실제 행을 가져온다.
• 인덱스 접근이 'PK 기반'이므로, 데이터를 일괄적으로 가져오는 과정에서 (물리적으로 어느 정도) 순차적인 I/O를 기대할 수 있다.
• 조인도 기본 키 기반으로 연결되기 때문에, 랜덤 I/O가 덜 발생하고 전체적으로 빠르다.

결론

• 정리하자면,
  1. PK(기본 키)로 조회하면 곧바로 클러스터드 인덱스로 접근하여 순차 I/O가 발생할 가능성이 높은 것이고, 
  2. 보조 인덱스로 조회하면, 그 인덱스에서 PK를 읽고 → 다시 PK로 클러스터드 인덱스를 조회하는 두 단계를 매번 반복해야 해서 그 과정에서 랜덤 I/O가 많이 생길 수 있는 것이다. 

결과적으로, 대량 데이터나 많은 조인을 포함한 쿼리에서 두 단계로 나눠 실행하면 랜덤 I/O를 크게 줄이고, 순차 I/O의 장점을 살려 성능을 개선할 수 있다. 또한, 쿼리 결과에 너무 많은 행이 걸리지 않도록(시간이나 조건을 더 세분화하는 식으로) 조건을 잘 조절하면 더 나은 성능을 기대할 수 있다. 이상으로 InnoDB의 인덱스 구조와 디스크 I/O 특성으로 인해 발생하는 성능차이에 대해 정리해봤다. 실제 운영환경에서 단일 쿼리가 비효율적으로 동작할 때, 이런 쿼리 분할 전략을 적용해보면 좋다.