🟣 같은 질의가 왜 느려지나
개발할 때는 빠르던 조회가 서비스가 커지면서 느려진다. 회원이 백 명일 때 잘 돌던 "이메일로 회원 찾기"가 회원 천만 명에서는 몇 초씩 걸린다. 질의문은 한 글자도 안 바뀌었는데 속도만 달라진다.
이유는 데이터베이스가 그 회원을 어떻게 찾는지에 있다. 아무 단서가 없으면 표의 첫 행부터 끝까지 하나하나 비교하며 훑는다. 이를 전체 훑기(풀 스캔)라 한다. 행이 백 개면 백 번 비교하면 되지만, 천만 개면 천만 번이다. 데이터가 늘어난 만큼 정직하게 느려진다. 데이터를 안전하게 다루는 법까지는 앞에서 갖췄으니, 마지막 숙제는 커진 데이터에서도 원하는 행을 빨리 찾는 것이다.
🟣 SQL은 실행되기 전에 계획이 세워진다
느린 질의를 손보려면 데이터베이스가 질의를 실제로 어떻게 처리하는지부터 알아야 한다. SELECT 한 줄을 던지면 곧바로 데이터를 뒤지는 게 아니다. 그 전에 세 단계를 거친다.
먼저 문법이 맞는지, 참조한 표와 열이 실재하는지 검사한다(파싱). 다음이 핵심인데, 같은 결과를 내는 여러 실행 방법 중 가장 비용이 적은 하나를 고른다(최적화). 마지막으로 그 방법대로 데이터를 읽어 결과를 만든다(실행). 이 가운데 두 번째 단계를 맡은 것이 옵티마이저다.
1장에서 SQL이 "무엇을 원하는지"만 적는 선언형 언어라 했다. 그 "어떻게"를 채우는 주체가 바로 옵티마이저다. VIP 회원을 찾으라는 같은 요구에도, 표를 통째로 훑을지 인덱스로 바로 찾을지, 두 표를 어떤 순서로 조인할지가 전부 옵티마이저의 선택이다. 옵티마이저는 각 방법의 예상 비용을 데이터 분포 통계로 어림잡아 계산하고 가장 싼 것을 고른다. 이 비용 기반 판단의 결과물이 실행계획이다.
실행계획은 EXPLAIN으로 미리 들여다볼 수 있다. 이 질의가 인덱스를 타는지 전체를 훑는지, 예상 처리 행 수가 몇인지가 나온다. 쿼리 튜닝은 대체로 이 실행계획을 읽고 "왜 인덱스를 안 타는가"를 되짚는 일에서 시작한다.
🟣 인덱스: B+트리로 범위를 좁힌다
전체 훑기를 피하는 핵심 도구가 인덱스다. 인덱스는 책 뒤의 색인과 같다. 본문을 처음부터 읽지 않고 색인에서 단어를 찾아 쪽 번호로 바로 넘어가듯, 데이터베이스도 인덱스로 원하는 행의 위치를 바로 찾는다.
인덱스의 자료구조는 대개 B+트리다. 값이 정렬된 채로 층층이 쌓인 트리인데, 위에서 아래로 내려가며 범위를 좁힌다. 맨 위 뿌리에서 "찾는 값이 이 가지냐 저 가지냐"를 판단해 한 갈래로 내려가고, 이를 잎에 닿을 때까지 반복한다. 천만 건이라도 트리 높이는 서너 층이면 충분해서, 서너 번만 비교하면 원하는 잎에 도착한다.
B+트리가 단순 정렬 배열보다 나은 지점은 잎에 있다. 모든 실제 값은 맨 아래 잎에만 있고, 잎끼리 정렬된 순서로 연결돼 있다. 그래서 "30 이상 60 이하" 같은 범위 조회에 강하다. 30이 든 잎을 트리로 찾아 내려간 다음, 연결된 잎을 따라 옆으로 훑으며 60까지 읽으면 된다. 등호 조회뿐 아니라 범위 조회와 정렬까지 인덱스 하나로 해결되는 이유가 이 잎의 연결 구조다.
🟣 인덱스는 공짜가 아니다
인덱스가 만능이면 모든 열에 걸면 될 텐데 그렇지 않다. 인덱스에는 분명한 대가가 있다.
첫째, 쓰기가 느려진다. 인덱스는 정렬을 유지해야 하므로, 행을 넣거나 지울 때마다 트리도 함께 고쳐야 한다. 인덱스가 다섯 개면 INSERT 한 번에 트리 다섯 개를 갱신한다. 읽기를 빠르게 하려고 쓰기를 느리게 하는 거래다. 그래서 조회는 거의 없고 삽입만 잦은 로그성 표에 인덱스를 남발하면 오히려 손해다.
둘째, 값의 종류가 적으면 효과가 없다. 성별처럼 값이 두세 가지뿐인 열에 인덱스를 걸어도, 조건에 맞는 행이 전체의 절반이라 결국 표의 절반을 읽어야 한다. 이런 자리에서는 옵티마이저가 인덱스를 두고도 전체 훑기를 고른다. 값이 고르게 흩어져 소수의 행만 걸러지는 열일수록 인덱스가 유리하다.
셋째, 인덱스를 걸어도 조건을 잘못 쓰면 안 탄다. 인덱스 열을 함수로 감싸거나 계산에 넣으면, 정렬된 원래 값과 어긋나 인덱스를 못 쓴다. WHERE YEAR(joined_at) = 2024는 인덱스를 버리지만, WHERE joined_at >= '2024-01-01'은 인덱스를 탄다. 두 조건이 같은 결과를 내도 실행 속도는 딴판이다. 여러 열을 묶은 복합 인덱스에서는 열의 순서도 중요해서, 앞 열을 조건에 안 쓰면 뒤 열만으로는 인덱스를 제대로 못 탄다. 인덱스는 걸었다고 쓰이는 게 아니라, 조건이 인덱스가 이해하는 형태일 때만 쓰인다.
🟣 진짜 병목은 디스크 읽기다
왜 이렇게까지 훑는 행 수에 매달리는지는 저장 매체의 속도 차이에서 온다. 데이터는 디스크에 있고, 디스크에서 한 조각을 읽는 것은 메모리에서 읽는 것보다 압도적으로 느리다. 그래서 질의 성능은 연산 횟수보다 디스크를 몇 번 읽느냐로 갈린다.
데이터베이스는 이 비용을 줄이려고 자주 읽는 데이터를 메모리 버퍼에 올려 둔다. 필요한 조각이 버퍼에 있으면 디스크까지 안 가고 메모리에서 꺼낸다. 버퍼에 없어서 디스크에서 실제로 읽어 오는 것을 물리적 읽기, 버퍼에서 꺼내는 것을 논리적 읽기라 한다. 튜닝의 목표는 이 물리적 읽기를 줄이는 것이다. 앞 책에서 공유 메모리 위에 캐시를 올려 디스크·네트워크 접근을 줄이던 발상이, 데이터베이스 버퍼 캐시에서 똑같이 되풀이된다.
읽는 방식도 비용이 다르다. 인덱스로 여기저기 흩어진 행을 하나씩 찾아 읽는 임의 읽기는, 표를 처음부터 순서대로 쭉 읽는 순차 읽기보다 조각당 비용이 비싸다. 그래서 걸러낼 행이 표의 상당 부분을 차지하면, 인덱스로 임의 읽기를 수백만 번 하느니 차라리 순차로 전체를 훑는 편이 빠르다. 옵티마이저가 인덱스를 두고 전체 훑기를 고르는 판단이 이 지점에서 나온다. 인덱스가 항상 답은 아니라는 것, 이것이 이 책이 최적화에서 도달한 결론이다.
🟣 정리
- 데이터가 커지면 전체 훑기는 정직하게 느려진다. 옵티마이저가 비용을 계산해 실행계획을 고르며,
EXPLAIN으로 그 계획을 미리 본다. - 인덱스는 B+트리로 범위를 층층이 좁혀 서너 번의 비교로 원하는 행에 닿는다. 잎끼리 연결돼 있어 범위 조회와 정렬에도 강하다.
- 인덱스는 쓰기를 느리게 하고, 값의 종류가 적으면 효과가 없으며, 조건을 함수로 감싸면 안 탄다. 걸었다고 쓰이는 게 아니다.
- 진짜 병목은 디스크 읽기다. 버퍼 캐시로 물리적 읽기를 줄이고, 걸러낼 행이 많으면 인덱스보다 전체 훑기가 빠를 수 있다.
최적화는 앱 개발과 가장 가깝게 닿는 장이었다. 서버가 느릴 때 API 코드를 아무리 봐도 답이 없다가, 실행계획을 열어 보니 인덱스를 안 타는 조건 한 줄이 범인이던 경험이 실무에서 반복된다. 기기 안의 SQLite에서도 인덱스 하나로 화면 로딩이 눈에 띄게 빨라지는 것은 같은 원리다. 면접에서 "이 쿼리가 느린데 왜 그럴까"를 물으면, 결국 훑는 행 수와 디스크 읽기로 되짚어 설명할 수 있는지를 보는 것이었다.
이 책은 하나의 사실을 다루는 생애 주기를 따라왔다. 중복 없이 담기 위해 표를 쪼갰고(1장), 쪼갠 표를 조인과 서브쿼리로 다시 이었고(2장), 여럿이 동시에 건드려도 깨지지 않게 트랜잭션으로 묶었고(3장), 커진 데이터에서도 빠르도록 인덱스와 실행계획을 손봤다(4장). 각 장의 도구는 앞 장의 결정이 낳은 문제를 풀기 위해 등장했다. 관계형 데이터베이스가 왜 이렇게 생겼는지를, 문법 항목이 아니라 이 필요의 사슬로 기억하게 된 것이 이 정리의 가장 큰 소득이었다.