[DB] 쿼리 튜닝은 무엇인가 (1) - 랜덤 I/O와 쿼리 튜닝

프로젝트를 진행하면서 조회 성능을 올리기 위해 인덱스를 건 적이 있다. 어떨 때는 성능이 개선되지만, 어떨 때는 오히려 성능이 안 좋아지기도 한다. "인덱스를 많이 걸면 성능이 저하된다" 라는 이야기를 많이 들어봤는데, 그 이유가 무엇인지 정리해보려고 한다.  목차는 아래와 같다.

• 순차 I/O vs 랜덤 I/O
• 쿼리 튜닝이란 무엇인가
• 인덱스가 성능을 저하시키는 경우에 대하여

 

 

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

결론적으로 조회 성능은 "몇 행을 읽었는가" 보다는 "디스크에 점프를 몇 번 했는가"로 결정된다. 디스크는 데이터를 물리적으로 연속된 블록 단위로 저장하는데, 데이터를 읽기 위해서는 그 위치로 이동해야한다. 이때 나오는 개념이 순차 I/O와 랜덤 I/O이다.

 

순차 I/O

물리적으로 인접한 블록을 연속해서 읽는 것이다. 연속으로 읽기 때문에, 헤드를 거의 움직일 필요가 없어 빠르다. 또, 아무리 많이 읽어도 점프가 없기 때문에, 페이지당 비용이 싸다는 특징이 있다. 예시로는 풀 테이블 스캔이 있다.

 

풀 테이블 스캔이란, 테이블 페이지를 처음부터 끝까지 순서대로 읽는 것을 의미한다. DB는 데이터를 페이지 단위로 읽는데, 이 페이지를 순서대로 읽는 것이다. 읽는 양은 많지만 페이지당 비용이 싸다.

 

랜덤 I/O

여기저기 흩어진 블록을 읽는 것이다. 매번 다른 위치로 이동해야 하기 때문에 seek time과 회전 지연이 누적되어 느려진다. 예시로는 인덱스가 있다.

 

인덱스로 조회한다는 것은 인덱스에서 원하는 행의 위치를 찾고, 실제 데이터가 저장된 테이블 페이지로 점프해서 읽는 것이다. 이때 읽는 행의 수와 읽는 페이지의 수는 다른데, 원하는 행이 모두 다른 페이지에 있다면, 랜덤 I/O가 계속 일어나지만, 여러 행이 모두 같은 페이지에 모여있다면 한 번의 점프로 끝난다. 즉, 행의 개수와 점프 횟수가 같지 않을 수 있다.

 

seek time (탐색 시간)
디스크의 헤드가 데이터가 저장된 트랙 위치까지 이동하는데 걸리는 시간

회전지연 (Rotational Latency)
디스크 헤드가 원하는 트랙에 도달한 후 플래터가 회전하면서 실제 데이터가 저장된 섹터가 헤드 밑으로 올떄까지 기다리는 시간

 

 

HDD 기준으로 랜덤 접근 한 번의 seek time은 수 ms 수준인데, 그 시간이면 순차 I/O로는 수백~수천 블록을 읽을 수 있다. 즉, 점프 한 번의 비용이 비싼 셈이다. 

 

여기서 비용은 페이지가 메모리에 없어서 디스크까지 내려가는 경우의 이야기이다. DB는 자주 읽는 페이지를 메모리의 버퍼 풀에 캐시해둔다. 만약 필요한 데이터가 이미 메모리에 올라와있다면, 순차던 랜덤이던 디스크 seek이 일어나지 않아 문제가 없다. 

 

 

2. 쿼리 튜닝이란 무엇인가

위에서 보았듯이 쿼리 튜닝의 결론적인 목표는 랜덤 I/O를 최소화 하는 것이다. 더 정확하게는 점프 횟수를 줄이는 것이다. 풀 스캔과 달리 인덱스를 타면 읽는 행이 적어 빠르다고 생각할 수 있지만, 꼭 그런 건 아니라는 뜻이다. 예시를 들어보자.

 

post 테이블에 100만건의 데이터가 있고, 특정 유저의 post를 조회하는 쿼리를 자주 날린다고 가정하면 다음과 같이 쿼리문을 작성할 수 있다.

SELECT title, created_at
FROM posts
WHERE user_id = 123
ORDER BY created_at DESC;

 

조회하는 데이터가 50건이라고 가정한다면 100만건을 풀스캔 하는 것보다는 userId에 인덱스를 거는게 성능상 유리할 것이다. 

인덱스를 userId, createAt, title를 이용해 커버링 인덱스로 만든다면 실제 테이블로 점프할 필요가 없어져 성능을 더 올릴 수 있게 된다. 이렇게 쿼리튜닝의 본질은, 점프를 덜하게 만드는 것이라고 볼 수 있다.

 

🔗 커버링 인덱스
만약 인덱스를 userId, createAt, title로 만든 복합인덱스라고 가정하면, 위 쿼리문이 요구하는 칼럼을 인덱스가 모두 포함하는 셈이다. 이처럼 쿼리를 충족시키는 데 필요한 모든 컬럼이 인덱스에 이미 다 포함되어 있어서, 실제 테이블 블록(디스크)으로 점프(랜덤 I/O)하지 않고 인덱스만 읽어서 쿼리를 완료하는 상태를 커버링 인덱스라고 한다. 

 

 

 

3. 인덱스가 성능을 저하시키는 경우에 대하여

위처럼 잘 쓴 인덱스는 성능을 올려주지만,  항상 올려주지는 않는다. 만약 user_id = 123 인 데이터가 50만건이라면, 점프가 많이 일어나게 되어 오히려 성능을 저하시킬 수 있다. 즉, 모든 데이터를 스캔하는 게 차라리 빠를 수도 있다는 뜻이다. 

인덱스를 걸었는데 풀스캔이 일어나는 경우는 이렇게 풀스캔이 오히려 빠른 상황인 셈이다.

 

보통 결과가 전체의 20%를 넘으면 풀 스캔이 유리하다고 하고, 정확하게는 비용으로 계산해 어떤 선택이 유리한지 판단한다. 

 

• 인덱스 경로: (예상 결과 행 수) × (랜덤 페이지 비용)
• 풀 스캔 경로: (테이블 전체 페이지 수) × (순차 페이지 비용)

옵티마이저는 위 계산을 통해 비용이 더 싼 쪽을 선택하게 된다.

 

---

다음 게시글에서는 인덱스의 원리에 대해 정리해보려고 한다!