6장 실행 계획 필요성
5장에서 옵티마이저가 실행 방법을 고르고, 그 선택이 읽는 페이지 수를 정한다는 것을 봤다. 이번 장에서는 옵티마이저가 고른 실행 방법, 곧 실행 계획을 왜 직접 확인해야 하는지와 확인하지 않고 고칠 때 생기는 비용을 정리한다. 강의 섹션 4의 17강에 해당한다.
1. 실행 계획이란
실행 계획은 옵티마이저가 쿼리를 처리하기로 정한 구체적인 방법이다. 테이블을 어떤 순서로 읽을지, 각 테이블을 인덱스로 찾을지 처음부터 끝까지 훑을지, 정렬이나 임시 테이블이 필요한지가 여기서 정해진다. EXPLAIN으로 쿼리 최적화
SQL은 "무엇을 가져올지"만 적고 "어떻게 가져올지"는 적지 않는다. 그래서 같은 SQL이라도 데이터 양과 통계 정보에 따라 실행 계획이 달라진다. 개발 환경에서는 인덱스를 타던 쿼리가 운영 데이터에서는 테이블 전체를 읽을 수도 있다. SQL 문장만 보고 성능을 판단할 수 없는 이유다.
2. 인덱스가 있어도 쓰지 않는 경우
"조건 컬럼에 인덱스가 있으니 빠르겠지"라는 기대는 자주 빗나간다. 공식 문서는 옵티마이저가 인덱스 대신 테이블 전체를 읽는 대표적인 경우를 다음처럼 든다. 풀 테이블 스캔 피하기
- 테이블이 아주 작아서 인덱스를 거치는 것보다 그냥 읽는 편이 빠를 때.
WHERE나ON조건에 인덱스 컬럼을 쓸 수 있는 형태의 조건이 없을 때.- 조건에 맞는 행이 테이블의 너무 큰 비율을 차지한다고 계산될 때.
- 같은 값을 가진 행이 많은 인덱스(카디널리티가 낮은 인덱스)를 통해 찾아야 할 때.
대부분의 행을 읽어야 한다면 인덱스를 거치는 것보다 처음부터 순서대로 읽는 편이 빠르다. 순차 읽기는 디스크 위치를 옮겨 다니는 횟수가 적기 때문이다. MySQL 인덱스 사용 방식
인덱스가 여러 개면 그중 무엇을 쓸지도 옵티마이저가 정한다. 외래 키를 걸 때 MySQL이 자동으로 만든 인덱스처럼, 개발자가 의식하지 못한 인덱스가 선택될 수도 있다. 외래 키 제약 조건 어떤 인덱스가 실제로 쓰이는지는 실행 계획을 봐야만 알 수 있다.
3. 확인하지 않고 고칠 때의 비용
실행 계획을 보지 않고 "이 컬럼에 인덱스를 걸면 되겠지" 하고 고치면, 그 인덱스가 쓰이지 않을 수 있다. 강의 실습에서도 조건 컬럼에 급하게 인덱스를 만들었지만 옵티마이저가 기존의 다른 인덱스를 골라 조회는 빨라지지 않았고, 테이블 크기만 늘었다.
쓰이지 않는 인덱스도 비용은 그대로 든다.
- 저장 공간: 인덱스마다 별도의 B-Tree를 디스크와 버퍼 풀에 둔다.
- 쓰기 비용:
INSERT,UPDATE,DELETE마다 관련 인덱스를 모두 고쳐야 한다. - 옵티마이저 부담: 후보 인덱스가 많을수록 어떤 것을 쓸지 판단하는 데 시간이 든다. MySQL 인덱스 최적화
원인을 모른 채 인덱스를 하나씩 더하면, 문제는 그대로 두고 비용만 쌓는 악순환에 빠진다.
이미 쌓인 인덱스 가운데 쓰이지 않는 것은 sys.schema_unused_indexes 뷰로 찾는다. 서버가 평소 부하를 충분히 오래 처리한 뒤에 봐야 결과를 믿을 수 있다. schema_unused_indexes
SELECT * FROM sys.schema_unused_indexes;4. EXPLAIN으로 보는 것
EXPLAIN은 쿼리를 실행하지 않고 옵티마이저가 세운 실행 계획만 보여 준다. 데이터를 읽지 않으므로 무거운 쿼리도 바로 확인할 수 있다. EXPLAIN
EXPLAIN SELECT * FROM product
WHERE category_id = 3 AND price BETWEEN 10000 AND 20000;결과에서 무엇을 보는지는 다음과 같다. 각 컬럼은 이어지는 강에서 하나씩 다룬다.
type: 테이블을 어떤 방식으로 읽는가 (19·20강).possible_keys,key: 어떤 인덱스를 후보로 보고, 실제로 무엇을 쓰는가 (21강).key_len,ref: 인덱스를 어디까지, 무엇과 비교해 쓰는가 (22강).rows,filtered: 몇 행을 읽고 그중 얼마가 남을 것으로 보는가 (23강).Extra: 정렬이나 임시 테이블 같은 추가 작업이 있는가 (24강).
출력 형식은 FORMAT 옵션으로 바꿀 수 있다. 기본은 표 형태(TRADITIONAL)이고, TREE는 처리 단계를 트리로 더 자세히 보여 준다. 해시 조인 사용 여부는 TREE 형식에서만 보인다. EXPLAIN 형식
EXPLAIN이 보여 주는 행 수와 비용은 추정값이다. 실제로 걸린 시간과 처리한 행 수는 쿼리를 실제로 실행하는 EXPLAIN ANALYZE로 본다(섹션 5).
5. 실행 계획을 보고 고치는 순서
EXPLAIN으로 실행 계획을 본다.- 병목이 되는 곳을 찾는다. 예: 기대한 인덱스를 쓰지 않음, 읽는 행 수가 너무 많음, 정렬을 따로 함.
- 원인에 맞춰 인덱스를 설계하거나 쿼리를 고친다.
- 같은 조건에서 고치기 전후의 실행 계획과 실행 시간을 비교한다.
고친 뒤에도 실행 계획을 다시 봐야 한다. 새로 만든 인덱스가 실제로 key에 나타나는지 확인해야 효과를 말할 수 있다.
이해를 확인할 질문
- 같은 SQL인데 개발 환경과 운영 환경에서 실행 계획이 다를 수 있는 이유는 무엇인가?
- 조건 컬럼에 인덱스가 있는데도 옵티마이저가 테이블 전체를 읽는 경우는 언제인가?
- 쓰이지 않는 인덱스가 계속 만드는 비용은 무엇인가?
EXPLAIN의rows값을 실제로 읽은 행 수로 믿으면 안 되는 이유는 무엇인가?
관련 문서: 5장 쿼리 실행 흐름, 공부 목표.