[실전 DB 04] EXPLAIN 읽는 법, 스캔과 검색을 구분하기

앞 편에서 인덱스를 만들면 실제로 쓰이는지 확인하라고 했다. 그 확인 도구가 실행 계획, 흔히 EXPLAIN이라 부르는 기능이다. 이걸 읽을 줄 알면 쿼리가 인덱스를 타는지 전체를 훑는지 한눈에 안다. 이번 편은 실행 계획을 읽는 법, 특히 스캔과 검색을 구분하는 핵심을 다룬다.

실행 계획은 데이터베이스 최적화의 청진기 같은 것이다. 이걸 읽을 줄 알아야 쿼리의 병을 진단하고 처방할 수 있다.

1. 실행 계획이란 무엇인가

실행 계획은 데이터베이스가 쿼리를 실제로 어떻게 실행할지 보여주는 것이다. 쿼리를 받으면 데이터베이스는 어떤 순서로 어떤 방법으로 데이터를 찾을지 계획을 세운다. 이 계획을 미리 보여달라고 요청하는 게 실행 계획 조회다. 쿼리 앞에 해당 명령을 붙이면 실제 실행 없이 계획만 보여준다.

대부분의 데이터베이스에서 이 기능을 제공한다. 형태는 조금씩 다르지만, 쿼리 앞에 EXPLAIN QUERY PLAN 같은 구문을 붙이는 식이다. 그러면 이 쿼리가 어느 테이블을 어떻게 접근하는지가 줄로 나온다. 이 줄들을 읽는 게 실행 계획 판독이다.

왜 실행 계획을 봐야 하냐면, 쿼리만 봐선 성능을 알 수 없기 때문이다. 같은 결과를 내는 쿼리도 데이터베이스가 어떻게 실행하느냐에 따라 빠를 수도 느릴 수도 있다. 실행 계획은 그 실제 실행 방식을 드러낸다. 겉으로 멀쩡한 쿼리가 속으로 전체를 훑고 있는지, 실행 계획을 봐야 안다.

실행 계획을 청진기에 비유한 게 딱 맞다. 겉으로는 멀쩡해 보이는 쿼리도 속을 들여다봐야 병을 안다. 실행 계획이 그 속을 보여준다. 이걸 읽을 줄 모르면 쿼리가 왜 느린지 짐작만 하지만, 읽을 줄 알면 정확히 진단한다. 최적화의 실력이 실행 계획을 읽는 능력과 직결되는 이유다.

한 가지 더, 실행 계획은 실제 실행 없이 계획만 보여주는 것이라 안전하다. 무거운 쿼리라도 실행 계획 조회는 실제로 데이터를 훑지 않고 어떻게 훑을지만 보여준다. 그래서 서비스에 부담을 주지 않고 마음껏 확인할 수 있다. 이 안전함 덕에 새 쿼리든 기존 쿼리든 부담 없이 실행 계획을 볼 수 있다.

2. 스캔과 검색의 차이

실행 계획에서 가장 중요한 구분이 스캔과 검색이다. 스캔은 테이블 전체를 훑는 것이고, 검색은 인덱스로 원하는 부분만 찾아가는 것이다. 실행 계획에 스캔이라는 표현이 나오면 그 쿼리는 전체를 훑고 있고, 검색이라는 표현과 인덱스 이름이 나오면 인덱스를 타고 있는 것이다.

이 한 가지만 봐도 쿼리의 효율을 판단할 수 있다. 큰 테이블에서 스캔이 나오면 그건 앞서 다룬 읽기 폭증의 주범일 가능성이 높다. 전체를 훑으니 읽기량이 크다. 반면 검색이 나오면 인덱스로 적게 읽고 있으니 효율적이다. 그래서 실행 계획을 볼 때 제일 먼저 스캔이냐 검색이냐를 본다.

가상의 예로, 어떤 글 테이블에서 특정 게시판의 글을 시각순으로 조회한다고 하자. 인덱스가 없으면 실행 계획에 전체 스캔이 나온다. 게시판과 시각에 맞는 인덱스를 만들면, 같은 쿼리의 실행 계획이 인덱스를 타는 검색으로 바뀐다. 이 전후를 실행 계획으로 비교하면 인덱스 효과가 눈에 보인다.

스캔과 검색을 구분하는 게 실행 계획 판독의 첫걸음이다. 이 둘만 구분해도 쿼리의 효율을 대략 판단한다. 큰 테이블에서 스캔이 나오면 무거운 쿼리이고, 검색이 나오면 효율적이다. 복잡한 실행 계획도 결국 이 스캔이냐 검색이냐를 각 단계에서 보는 것이다. 기본 구분을 확실히 하면 나머지는 응용이다.

3. 임시 정렬이라는 또 다른 신호

실행 계획에서 스캔 외에 주의할 신호가 임시 정렬이다. 쿼리에 정렬이 있는데 그 정렬에 맞는 인덱스가 없으면, 데이터베이스는 데이터를 다 모아 임시로 정렬한다. 실행 계획에 임시 정렬을 뜻하는 표현이 나오면, 그 쿼리는 정렬을 위해 무거운 작업을 하고 있는 것이다.

이 임시 정렬이 읽기 폭증의 숨은 원인인 경우가 많다. 목록을 시각순으로 보여주는 쿼리가 정렬 인덱스가 없어서 매번 전체를 임시 정렬하면, 그게 큰 부담이 된다. 앞서 인덱스가 정렬에도 결정적이라고 했는데, 실행 계획에서 이 임시 정렬이 사라지는지로 그 효과를 확인한다.

그래서 실행 계획을 볼 때 두 가지를 본다. 전체 스캔이 있는지, 그리고 임시 정렬이 있는지다. 둘 다 없이 인덱스 검색만으로 결과가 나오면 그 쿼리는 효율적이다. 둘 중 하나라도 큰 테이블에서 나오면 최적화 대상이다. 이 두 신호가 진단의 핵심 지표다.

임시 정렬이라는 신호를 놓치기 쉽다. 스캔은 눈에 잘 띄는데 임시 정렬은 간과하기 쉽다. 그런데 정렬이 있는 목록 쿼리에서 이 임시 정렬이 큰 부담이 된다. 그래서 실행 계획을 볼 때 스캔과 임시 정렬 두 가지를 함께 챙긴다. 둘 중 하나라도 큰 테이블에서 나오면 최적화 대상이다.

또 하나, 임시 정렬은 정렬 인덱스로 없앤다. 정렬에 맞는 인덱스가 있으면 데이터베이스는 인덱스 순서를 그대로 쓰고 별도 정렬을 안 한다. 그래서 목록을 시각순으로 보여주는 쿼리라면, 시각 컬럼이 인덱스에 정렬돼 있게 만든다. 그러면 실행 계획에서 임시 정렬이 사라지고 그만큼 부담이 준다.

4. 실행 계획으로 인덱스를 검증한다

실행 계획의 가장 실용적인 쓰임이 인덱스 검증이다. 앞 편에서 인덱스를 만들었다고 쓰이는 게 아니라고 했다. 만든 인덱스가 실제로 쓰이는지는 실행 계획으로만 확인할 수 있다. 인덱스를 만든 쿼리의 실행 계획을 보고, 그 인덱스 이름이 검색에 나오는지 확인하는 것이다.

만약 인덱스를 만들었는데 실행 계획에 여전히 스캔이 나오면, 그 인덱스는 안 쓰이고 있는 것이다. 원인은 여러 가지다. 통계 정보가 없거나 낡았을 수도, 쿼리 구조가 인덱스와 안 맞을 수도, 데이터 분포상 스캔이 낫다고 판단됐을 수도 있다. 실행 계획이 이 문제를 드러내준다.

그래서 인덱스를 만들 때마다 실행 계획으로 확인하는 걸 절차로 삼는다. 만들고, 실행 계획을 보고, 검색으로 바뀌었으면 성공, 여전히 스캔이면 원인을 찾는다. 이 확인 없이 인덱스만 만들고 넘어가면, 안 쓰이는 인덱스를 만들어놓고 최적화했다고 착각하게 된다.

실행 계획으로 인덱스를 검증하는 게 가장 실용적인 쓰임이다. 인덱스를 만들었으면 실행 계획을 봐서 그게 실제로 쓰이는지 확인한다. 인덱스 이름이 검색에 나오면 성공이다. 만들었다고 믿지 말고 쓰인다고 확인하는 이 습관이, 안 쓰이는 인덱스를 만들고 최적화했다 착각하는 걸 막는다.

또 하나, 실행 계획은 여러 테이블을 조인하는 복잡한 쿼리에서 더 값어치가 크다. 조인은 어느 테이블을 먼저 접근하고 어떻게 연결하느냐에 따라 성능이 크게 갈리는데, 실행 계획이 그 순서와 방식을 보여준다. 조인이 느리면 실행 계획을 봐서 어느 단계가 전체를 훑는지 찾아 그 부분에 인덱스를 태운다.

5. 실행 계획을 습관으로

실행 계획을 읽는 게 처음엔 낯설지만, 익숙해지면 강력한 습관이 된다. 새 쿼리를 짜면 실행 계획을 확인하고, 무거운 쿼리를 발견하면 실행 계획으로 원인을 진단하고, 인덱스를 만들면 실행 계획으로 검증한다. 실행 계획이 최적화의 모든 단계에 함께한다.

이 습관이 있으면 비효율적인 쿼리가 서비스에 몰래 들어가는 걸 막는다. 배포 전에 주요 쿼리의 실행 계획을 확인하면, 전체 스캔이나 임시 정렬을 하는 쿼리를 미리 걸러낸다. 이건 앞서 여러 시리즈에서 강조한 배포 전 확인의 데이터베이스 판이다. 사고를 사후에 수습하는 대신 사전에 예방하는 것이다.

정리하면 실행 계획은 쿼리의 실제 실행 방식을 드러내는 청진기이고, 스캔과 임시 정렬이 나오는지를 봐서 무거운 쿼리를 진단하며, 인덱스가 쓰이는지 검증한다. 다음 편에서는 인덱스를 한 단계 깊이 들어가, 여러 컬럼을 묶는 복합 인덱스와 그 컬럼 순서의 기술을 다룬다.

실행 계획을 습관으로 만들면 비효율이 서비스에 몰래 들어가는 걸 막는다. 새 쿼리를 배포하기 전에 실행 계획을 확인하면, 전체 스캔이나 임시 정렬을 하는 쿼리를 미리 걸러낸다. 사고를 사후에 수습하는 대신 사전에 예방하는 것이다. 이 짧은 확인이 나중의 큰 비용 폭탄을 막아준다.

실행 계획을 읽는 능력이 곧 데이터베이스 실력이라 해도 과언이 아니다. 이걸 읽을 줄 알면 어떤 쿼리가 왜 느린지 정확히 진단하고, 인덱스가 효과를 내는지 검증하고, 비효율을 사전에 막는다. 최적화의 모든 단계에 실행 계획이 함께한다. 그래서 데이터베이스를 다룬다면 이 판독법을 가장 먼저 익히길 권한다.