개요

6.1.1 쿼리 실행 절차

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

첫번째 단계를 "SQL 파싱"이라고 하며, SQL 파서라는 모듈로 처리한다. 또한 이단계에서 SQL 파스트리가 만들어진다. 두번째 단곈,ㄴ SQL파스트리를 참조하여 다음과 같은 내용을 처리한다.

  • 불필요한 조건의 제거 및 복잡한 연산의 단순화
  • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
  • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
  • 가져온 레코드들을 임시 테이블에 넣고 다시한번 가공해야 하는지 결정 세번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청하고, Mysql 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다. 첫번째와 두번째는 거의 Mysql 엔진에서 처리하며 세번째는 Mysql 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.

6.1.2 옵티마이저의 종류

옵티마이저는 대부분의 DBMS가 선택하고 있는 비용기반최적화(CBO) 방법과 예전 오라클에서 많이 사용된던 규칙기반최적화방법(RBO) 로 나눠 볼수 있다.

  • RBO: 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행계획을 수립하는 방법을 의미한다. 이방식에서는 통계 정보를 조사하지 않고 시행계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 낸다 하지만 규칙 기반의 최적화는 이미 오래전부터 많은 DBMS에서 거의 지원되지 않거나 업데이트 되지 않는 상태로 그대로 남아 있는 것이 현실이다.
  • CBO: 쿼리를 처리하기 위한 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상테이블의 예측된 통계쩡보를 이용해 각 실행 계획별 비용을 산출한다. 이렇게 산출된 각 실행방법벌로 최소 비용이 소요되는 처리방식을 선택해 최종 쿼리를 실행한다. 현재는 거의 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있으며, mysql도 마찬가지이다.

6.1.3 통계정보

비용기반 최적화에서 가장 중요한 것은 통계정보다. Mysql 에서 관리되는 통계정보는 대략의 레코드 건수와 인덱스의 유니크한 값의 개수 정도가 전부다. 레코드 건수가 많지 않으면 통계정보가 상당히 부정확한 경우가 많으므로 "ANALYZE" 명령을 이용해 강제적으로 통계정보를 갱신해야 할때도 있다. MEMORY 테이블은 별도로 통계쩡보가 없으며, MyISAM과 InnoDB 의 테이블과 인텍스 통계정보는 다음과 같이 확인할 수 있다. ANALYZE 명령은 인덱스 키값의 분포도만 업데이트 하며, 전체 테이블 건수는 테이블의 전체페이지 수를 이용해 예측한다.

  SHOW TABLE STATUS LIKE 'tb_test'\G
  SHOW INDEX FROM tb_test

통계정보를 갱신하려면 다음과 같이 싱행하면 된다.

  --// 파티션을 사용하지 않는 일반 테이블의 통계 정보 수집
  ANALYZE TABLE tb_test;
  --// 파티션을 사용하는 테이블에서 특정 파티션의 통계정보 수집
  ALTER TABLE tb_test ANALYZE PARTITION p3;

ANALYZE 실행하는 동안 MyISAM 테이블은 읽기는 가능하지만 쓰기는 안된다. 하지만 InnoDB 테이블은 읽기와 쓰기 모두 불가능하다. MyISAM 테이블은 정확한 키값 분포표를 위해 인덱스 전체를 스캔하므로 많은 시간이 소요된다. 그러나 InnoDB 테이블은 인덱스 페이지 중에서 8개 정도만 랜덤하게 선택해서 분석하고 그 결과를 통계 정보로 갱신한다.