실행계획(ExecutionPlan)의 이해

실행계획이란?

• DBMS의 쿼리 실행에 같은 결과를 만들어 내는데는 한가지 방법만 있는 것은 아니다.
• 아주 많은 방법이 있지만 각 처리 방법마다 실행 시간(성능)은 서로 다를 수 있기에 그 중에서 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 한다.

어떻게 최적의 방법을 결정할까?

• DBMS에서는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 “통계 정보”를 참조한다.
• DBMS의 “옵티마이저”는 그러한 기본 데이터를 비교해 “최적의 실행 계획”을 수립하는 작업을 한다.
• 하지만 실행계획은 통계 자료 값을 기반으로 옵티마이저가 선택한 “예상치”이기 때문에 실제 수행할 때 발생하는 I/O 또는 시간은 차이가 날 수 있으며, 결과도 다를 수 있다.
  • 옵티마이저가 대부분 좋은 선택을 해서 가장 효율적인 실행계획을 알려줄 수 있지만, SQL이 복잡할수록 실수할 가능성도 크다.
  • 그리고 옵티마이저 보다 실제 운영하는 개발자가 업무 특성을 고려하여 더 효율적인 액세스 경로를 찾아낼 수 있다.
    • 이럴 때 옵티마이저 힌트를 사용해서 데이터 액세스 경로를 바꿀 수 있다.

옵티마이저의 종류

• 옵티마이저는 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화 방법(Cost-based optimizer) 규칙 기반 최적화 방법(Rule-based optimizer)으로 크게 나눠 볼 수 있다.
  • 규칙 기반 최적화 방법은 예전에 오라클에서 많이 사용됐다.

규칙 기반 최적화

• 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다.
• 이 방식에서는 통계 정보(테이블의 레코드 건수나 칼럼 값의 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어낸다.

비용 기반 최적화

• SQL 문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.
• 비용기반 옵티마이저는 비용을 예측하기 위해서 규칙기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 컬럼등의 다양한 객체 “통계정보”와 시스템 통계정보를 이용한다.
• 통계정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요하다.
  • 통계 정보가 정확하지 않다면 전혀 엉뚱한 방향으로 쿼리를 실행해 버릴 수 있기 때문에 비용 기반 최적화에서 가장 중요한 것은 통계 정보다.
• 현재는 거의 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있으며, MySQL 역시 마찬가지다.

1억 건의 레코드가 저장된 테이블의 통계 정보가 갱신되지 않아서 레코드가 10건 미만인 것처럼 돼 있다면 옵티마이저는 실제 쿼리 실행 시에 인덱스 레인지 스캔이 아니라 테이블을 처음부터 끝까지 읽는 방식(풀 테이블 스캔)으로 실행해 버릴 수도 있다. 부정확한 통계 정보 탓에 0.1초에 끝날 쿼리가 1시간이 소요될 수도 있는 것이다.

쿼리 실행 절차

• MySQL 서버에서 쿼리가 실행되는 과정은 크게 3가지로 나눌 수 있다.
  1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
  2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
  3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.