들어가기 전

슬로우 쿼리를 고쳐보고 싶은데 어떤 부분에서 비효율이 발생하는지는 “실행 계획”을 보면 알 수 있다고 들었다. 실행 계획이 무엇이고 어떻게 활용해야 하는 것인지 공부해보자.

실행 계획

DBMS의 쿼리 실행은 같은 결과를 만들어 내는 다양한 방법이 있다. 그 중 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 한다. 이러한 최적의 실행 계획을 수립하는 역할은 옵티마이저가 담당한다.

파싱(SQL 파서)
- 쿼리 문장을 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리 생성)
최적화 및 실행 계획 수립(옵티마이저)
- 파스 트리를 기반으로 어떤 테이블부터 읽고 어떤 인덱스를 이용해서 테이블을 읽을지 선택
- 2번 단계가 완료되면 실행 계획이 만들어진다.
수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청. MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업 수행

현재 많은 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법과 예전 오라클에서 많이 사용했던 규칙 기반 최적화(Rule-based optimizer, RBO)로 나눌 수 있다.

규칙 기반 최적화
- 오직 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이다.
- 이 방식에서는 통계 정보(대상 테이블의 레코드 건수, 컬럼 값의 분포도 등)를 고려하지 않고 실행 계획이 수립된다.
- 각 테이블이나 인덱스의 통계 정보가 거의 없고, 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스러웠기 때문에 사용하던 방식이다.
  - 이미 오래 전부터 많은 DBMS에서 더 이상 지원하지 않는다.
비용 기반 최적화
- 산출된 각 실행 계획별 최소 비용이 소요되는 처리 방식을 선택한다.
- 쿼리를 처리하기 위한 여러 방법을 수립한다.
- 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 각 실행 계획별 비용을 산출한다.

비용 기반 최적화 방식에서 가장 중요한 것은 ‘통계 정보’이다. 통계 정보가 정확하지 않으면 쿼리가 매우 비효율적으로 실행될 수 있다.

기본적으로 MySQL에서 관리되는 통계 정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수정도이다.
- 레코드 건수가 많지 않으면 정보가 부정확한 경우가 많으므로 ANALYZE 명령을 이용해 강제적으로 통계 정보를 갱신해야 할 때도 있다.
오라클과 같은 DBMS에서는 통계 정보가 상당히 정적이고 수집에 많은 시간이 소요되기 때문에 통계 정보만 따로 백업하기도 한다.

MySQL에서는 기본적으로 EXPLAIN 명령을 사용해서 실행 계획을 확인할 수 있다. 또한, EXPLAIN EXTENDED, EXPLAIN PARTITIONS 명령을 이용해 더 상세한 실행 계획을 확인할 수도 있다.

EXPLAIN을 실행하면 다음과 같이 표 형태로된 결과가 표시된다.
표의 각 라인은 쿼리 문장에서 사용된 테이블(서브 쿼리를 통해 생성된 임시 테이블도 포함)의 개수만큼 출력된다.
표의 위쪽에 있을수록 먼저 접근한 테이블이다.
다른 DBMS와는 달리 MySQL에서는 필요에 따라 실행 계획을 산출하기 위해 쿼리의 일부분을 직접 실행할 때도 있다.
- 따라서 쿼리 자체가 상당히 복잡하고 무거운 경우에는 실행 계획의 조회 또한 느려질 가능성이 있다.
SELECT 이외의 DML(UPDATE, INSERT, DELETE)에 대해서는 실행계획을 확인할 방법이 없다.
- 이를 위해서는 WHERE 조건절만 같은 SELECT 쿼리를 만들어서 대략적으로 계획을 확인해 볼 수 있다.

다음 파트에서부터 실행 계획의 각 컬럼이 어떤 것을 의미하는지, 어떤 값이 나올 수 있는지 등에 대해 살펴볼 것이다.