MySQL(8)

오늘 한 것

KB국민은행 역량검사 보고옴. 하나은행 서류 제출 완료

이하 MySQL

9 옵티마이저와 힌트

SQL 쿼리는 결과는 동일하지만 결과를 만드는 방법은 매우 다양하다. 다양한 방법중에서 뭐가 최적이고 최소비용일지 결정해야 한다.

MySQL에서는 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조하며, 이런 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다. 대부분의 DBMS에서 옵티마이저가 그걸 한다.

9.1 개요

어떤 DBMS던지 옵티마이저는 가장 복잡한 부분으로 알려져 있다. 이게 만드는 실행 계획을 이해하는 것 또한 상당히 어렵다. 근데 이걸 꼭 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 계획을 수립하도록 유도해야 한다.

실행 계획을 보기 전에 알고 있어야 할것들을 보자.

9.1.1 쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 3단계로 나눌 수 있다.

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

• 1단계를 SQL 파싱 이라고 하며, SQL파서 라는 모듈로 처리한다. 문법 오류를 여기서 거른다. 이 단계에서 SQL 파스트리가 만들어지며, MySQL서버는 SQL 문장이 아니라 이 SQL 파스트리를 이용해 쿼리를 실행한다.

• 2단계에선 파스트리를 참조하면서 다음과 같은 내용을 처리한다.

  - 불필요한 조건 제거 및 복잡한 연산의 단순화
  - 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
  - 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
  - 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
  

이거 말고도 여러개 하는데, 대표적으로 이렇다.

• 2단계를 최적화 및 실행 계획 수립 단계라고도 하고, MySQL서버의 옵티마이저 에서 처리한다. 이게 완료되면 쿼리의 실행계획이 만들어 진다.

• 3단계는 실행계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, MySQL엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다. 1,2 단계는 MySQL엔진에서, 3단계는 MySQL엔진과 스토리지 엔진이 동시에 참여하여 처리한다.

9.1.2 옵티마이저의 종류

옵티마이저는 DB서버의 두뇌다. 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer) CBO) 방법과 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer) RBO) 로 크게 나뉜다.

• 규칙 기반 최적화 는 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이다. 통계정보를 조사하지 않기때문에 같은 쿼리는 거의 항상 같은 계획을 짠다. 근데 사용자 데이터는 분포도가 다양하므로 거의 안쓴다.

• 비용 기반 최적화는 쿼리를 처리하기 위한 여러 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 통계 정보를 이용해 실행 계획별 비용을 산출한다. 이후 최소 선택

규칙기반은 각 테이블이나 인덱스의 통계정보가 거의 없고, 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스러워서 선택하던 시절의 기술이다.

9.2 기본 데이터 처리

대부분 DBMS는 데이터를 정렬하거나 Grouping하는 기본 데이터 가공 기능을 가진다. 하지만 결과가 같더라도 그 과정은 천차만별이다.

기본적인 가공을 위해 MySQL서버가 어떤 알고리즘을 사용하는지 알아보자.