[REAL MYSQL 8.0] 옵티마이저 와 실행계획 (1) - 데이터 처리

 

옵티마이저는 최적의 실행계획을 수립하기 위한 DBMS 의 뇌라고 볼 수 있다.

실행계획을 이해하려면 옵티마이저에 대한 이해가 필수이다.

 

옵티마이저에 의한 쿼리 처리 과정

• SQL 을 SQL Parser 에 의해 분리 하여 Parse Tree 를 생성
• Parse Tree 를 보고 인덱스와 어떤 테이블을 읽을지 선정 (실행계획 수립)
  • 불필요 조건 제거, 연산 단순화
  • 조인의 경우 읽을 순서 선정 (드라이빙 테이블, 드리븐 테이블)
  • 조건문과 인덱스의 통계정보를 활용하여 어떤 인덱스를 태울지 선정
  • 가져온 레코드들을 임시 테이블에 넣고 가공할 필요가 있는지 판단
• 결정된 실행계획에 따라 실제로 엔진에 의해 쿼리 수행

 

옵티마이저의 종류

• 규칙 기반 최적화 - Oracle 에서 예전에 자주 사용하던 방식. 내장된 우선순위를 static 하게 적용하는 방식
• 비용 기반 최적화 - 여러가지 방법을 만든 후, 최소 비용의 방법을 채택 (근래에는 이 방법이 채택)

 

기본 데이터 처리

풀 테이블 스캔, 풀 인덱스 스캔

 

풀테이블 스캔

• 레코드 건수가 너무 적어 풀 인덱스 스캔보다 풀 테이블 스캔이 나은 경우 (보통 페이지 1개로 구성된 경우)
• 적절한 인덱스를 태울 조건이 없는 경우
• Range Scan 을 사용할 수 있는 쿼리라도, 예상되는 일치 레코드 수가 너무 많을 경우 (인덱스 샘플링 결과를 이용하여 추론)

풀 테이블 스캔을 이용할 때, 일반적으로 데이터 페이지를 하나씩 디스크에서 읽어 올 것이라고 생각되기 쉽지만 innodb 에서는 풀테이블 스캔으로 예상되는 스캔에서는 페이지 자체를 벌크로 가져온다.

InnoDB 는 innodb_read_ahead_threshold 변수에 설정된 만큼 포그라운드 스레드에서 연속된 데이터 페이지가 조회되면

백그라운드 스레드에 의해 여러 페이지를 디스크에서 읽어 버퍼풀에 가져다 둠. 이를 Read Ahead 라고 함.

 

한번에 4~8개 페이지를 읽고 점점 증가시켜 최대 64 페이지를 한번에 디스크에서 읽음.

이로 인해 빠르게 풀스캔이 가능하도록 함. 

 

SELECT COUNT(*) FROM employees;

위와 같은 쿼리는 풀 테이블 스캔을 사용할 것으로 예상되지만, 실제 실행 계획은 풀 인덱스 스캔을 할 가능성이 더 높다.

인덱스는 적은 컬럼으로 구성되기 때문에 더 작은 용량으로 훨씬 더 빠르게 처리될 가능성이 크기 때문.

 

쿼리의 병렬처리

 

MySQL 8.0 버전 부터는 쿼리의 병렬 처리를 지원함.

여기서 병렬 처리는 하나의 쿼리를 여러 스레드가 나누어 처리하는 것을 뜻함.

innodb_pararell_read_threads 시스템 변수에 설정된 만큼의 스레드를 사용하며,

Where 절 없이 단순히 전체 건수를 가져오는 Count 쿼리에만 사용 됨.

 

(2) 에서는 소팅에 대한 옵티마이징 정리 예정.