목차
느린 쿼리에 EXPLAIN을 붙였다. 알아볼 수 없는 게 스무 줄쯤 쏟아진다. 스크롤하다가 그냥 인덱스를 하나 더 만들어본다.
지금까지 인덱스와 조인에서 계속 같은 말로 끝났다. “추측하지 말고 실행 계획을 봐라.” 이번엔 그걸 어떻게 보는지다.
결론부터 말하면 세 가지만 본다. 나머지는 그 셋을 확인한 뒤에 봐도 늦지 않다.
계획은 트리다
SQL은 “무엇을” 만 적는다. “이 조건에 맞는 걸 저 테이블과 붙여서 줘.” “어떻게” 는 한 글자도 안 적혀 있다.
그 “어떻게”를 옵티마이저가 정해서 내놓은 게 실행 계획이고, 모양은 트리다.
데이터는 잎에서 뿌리로 흐른다. 테이블에서 행을 꺼내고(①②), 붙이고(③), 정렬해서(④) 내보낸다.
그래서 읽는 순서도 안쪽부터다. 대부분의 DB가 텍스트로 출력할 때 들여쓰기로 깊이를 표시하는데, 가장 깊이 들여쓴 것이 가장 먼저 실행된다. 맨 윗줄은 마지막에 일어나는 일이다. 처음 보면 여기서부터 헷갈린다.
볼 것 하나: 테이블을 어떻게 읽었나
각 잎에서 풀스캔인지 인덱스인지를 본다.
인덱스 글에서 본 그대로다. 풀스캔이 보인다고 무조건 나쁜 게 아니다. 많이 읽을 거면 풀스캔이 맞다. 90만 건을 흩어진 채 찾아다니느니 순서대로 읽는 게 빠르다.
문제는 몇 건 안 되는데 풀스캔인 경우다. 그때는 인덱스가 없거나, 있는데 못 타고 있다. 컬럼을 가공했는지, 복합 인덱스의 앞을 안 썼는지, 타입이 어긋났는지를 본다.
볼 것 둘: 조인을 뭘로 했나
조인 글의 셋 중 뭘 골랐는지 본다.
- 중첩 반복이면 → 바깥이 몇 건인지, 안쪽에 인덱스가 있는지
- 해시면 → 메모리에 들어갔는지, 디스크로 넘쳤는지
- 정렬 병합이면 → 정렬을 새로 하고 있는지
여기까지는 인덱스와 조인에서 본 것을 계획에서 확인하는 것뿐이다. 진짜는 그다음이다.
볼 것 셋: 예상과 실제가 얼마나 다른가
이게 전부라고 해도 된다.
인덱스에서도 조인에서도 같은 데서 끝났다. “옵티마이저가 통계를 보고 판단한다. 통계가 낡으면 판단도 틀린다.” 그럴듯하지만 실무에선 쓸모가 없다. 내 통계가 낡았는지를 알 방법이 없기 때문이다. 여기가 그 방법이다.
옵티마이저는 예상으로 판단한다. “이 조건이면 100건쯤 나오겠군” 하고 중첩 반복을 고른다. 그 예상이 맞으면 좋은 계획이다. 틀리면 좋은 판단이 나쁜 결과를 낳는다.
그래서 예상과 실제를 나란히 놓고 본다.
맨 아래에서 30배 틀렸다. 그 오차가 위로 올라가며 눈덩이가 된다. 조인 결과 예상 200건이 실제로는 6만 건이고, 옵티마이저는 그 200건을 믿고 중첩 반복을 골랐다. 바깥이 예상보다 훨씬 크니 안쪽 탐색도 그만큼 되풀이된다.
중첩 반복의 안쪽 노드를 읽을 때 주의할 게 있다. PostgreSQL은 그 노드의 실제 행 수를 한 번 돌 때의 평균으로 적고, 몇 번 돌았는지는 loops에 따로 적는다. 그래서 3만 번 반복된 노드가 “실제 1건”으로 보인다. 총량은 행 수 × loops다. 이걸 모르면 가장 바쁜 노드를 가장 한가한 노드로 읽게 된다.
범인은 맨 위가 아니라 처음 어긋난 그 지점이다. 위쪽은 아래쪽 오차를 물려받았을 뿐이다. 계획을 읽을 때는 가장 아래에서부터 예상과 실제가 처음 벌어지는 곳을 찾는다. 거기가 원인이고, 나머지는 증상이다.
EXPLAIN만 보면 이걸 못 본다
여기서 중요한 게 하나 있다. 그냥 EXPLAIN은 실제 건수를 안 알려준다.
| 하는 일 | 알려주는 것 | |
|---|---|---|
EXPLAIN | 계획만 세운다. 실행 안 함 | 예상만 |
EXPLAIN ANALYZE | 실제로 실행한다 | 예상 + 실제 + 실제 시간 |
예상만 봐서는 그게 맞는지 틀린지 알 수가 없다. 예상끼리는 항상 앞뒤가 맞기 때문이다. 옵티마이저가 자기 예상을 근거로 세운 계획이니까.
이 절의 문법은 PostgreSQL 기준이다. 여기까지 시리즈의 SQL은 대체로 표준이라 아무 DB에서나 돌지만, 실행 계획만은 제품마다 다르고 표준이 없다. MySQL은 8.0.18부터 EXPLAIN ANALYZE가 있고, Oracle은 이 구문 자체가 없어서 DBMS_XPLAN으로 따로 뽑는다.
다만 “뽑는 방법만 다르다”고 하면 지나치다. MySQL의 기본 EXPLAIN은 트리가 아니라 평평한 표이고 실제 행 수 자체가 없다. 이 글이 셋째로 꼽은 예상·실제 비교를 하려면 EXPLAIN ANALYZE나 FORMAT=TREE로 갈아타야 한다. 무엇을 볼지는 같아도, 그걸 볼 수 있는 명령이 제품마다 다르다.
EXPLAIN ANALYZE는 쿼리를 진짜로 실행한다. SELECT면 상관없지만 UPDATE나 DELETE에 붙이면 데이터가 실제로 바뀐다. 트랜잭션으로 감싸고 롤백해야 한다.
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'done' WHERE ...;
ROLLBACK;비용은 시간이 아니다
계획에 cost 같은 숫자가 붙어 있는데, 이건 초가 아니다. 옵티마이저가 계획끼리 비교하려고 쓰는 임의 단위다.
cost=1000이 1초라는 뜻이 아니다. cost=2000짜리 계획보다 싸게 예상된다는 뜻일 뿐이다. 절대값을 붙들고 씨름할 필요가 없다. 실제 시간이 궁금하면 EXPLAIN ANALYZE의 실측 시간을 본다.
예상은 왜 틀리나
예상이 틀리는 데는 몇 가지 단골이 있다.
통계가 낡았다. 가장 흔하다. 데이터는 계속 쌓이는데 통계가 옛날 그대로면 옵티마이저는 옛날 세상을 기준으로 판단한다. 대량 적재 직후가 특히 위험하다.
컬럼끼리 상관관계가 있다. 옵티마이저는 대개 컬럼들이 서로 독립이라고 가정한다.
WHERE city = '서울' AND country = '한국'서울이 전체의 20%, 한국이 90%라면 옵티마이저는 0.2 × 0.9 = 18%로 추정한다. 그런데 서울이면 반드시 한국이다. 실제로는 20%다. 이런 조합이 겹치면 오차가 곱해지며 커진다.
함수나 표현식을 썼다. WHERE lower(email) = ?처럼 쓰면 lower(email)에 대한 통계가 없다. 옵티마이저는 기본값으로 찍는다.
그래서 순서는
EXPLAIN ANALYZE로 뽑는다. 그냥EXPLAIN은 예상만 준다.- 아래에서부터 예상과 실제가 처음 벌어지는 지점을 찾는다.
- 벌어졌으면 → 통계 문제다. 통계를 갱신하고 다시 뽑는다. 그래도 틀리면 상관관계나 표현식을 의심한다.
- 예상이 정확한데도 느리면 → 그때부터 쿼리·인덱스 문제다. 접근 방법과 조인 알고리즘을 본다.
순서를 뒤집으면 헛수고한다. 예상이 30배 틀린 상태에서 인덱스를 아무리 만들어도, 옵티마이저는 여전히 틀린 근거로 판단한다. 통계부터 맞춰놓고 봐야 한다.
마지막으로
실행 계획은 약속이 아니라 판단이다. 데이터가 바뀌면 판단도 바뀐다. 오늘 좋은 계획이 다음 달에 나쁜 계획이 될 수 있고, 그건 버그가 아니라 정상이다.
그래서 힌트로 계획을 박아두는 건 마지막 수단이다. 지금 상황엔 맞겠지만, 데이터가 변해도 그 선택에 묶인다. 대개는 통계를 맞춰주는 게 답이다.