[DB] 옵티마이저 (1)

Mysql 서버로 요청한 쿼리는 최적으로 실행 되기 위해 최적의 실행 계획을 수립하는 작업이 필요.

대부분의 DBMS 에서는 옵티마이저가 이런 기능을 담당한다.

Explain 명령으로 쿼리의 실행 계획 확인 가능.

 

Mysql 에서는 비용 기반 최적화 (Cost-based optimizer, CBO) 를 사용한다.

 

기본 데이터 처리

풀 테이블 스캔 조건

- 테이블 레코드 건수가 작아, 인덱스를 택하기 보다 풀 테이블 스캔 하는 편이 더 빠른 경우

- where 절이나 on 절에 인덱스를 이용할 수 있는 적절한 조건이 없을 경우

- 인덱스 레인지 스캔을 사용할 수 있는 쿼리더라도, 옵티마이저가 판단한 조건 일치 레코드 건수가 많은 경우

 

위와 같은 조건에서 Mysql 옵티마이저는 풀 테이블 스캔을 시도한다.

풀 테이블 스캔은 디스크로부터 페이지를 하나씩 읽어 오는 것이 아니다.

InnoDB 스토리지 엔진은 특정 테이블의 연속 데이터 페이지가 읽힐 때, 백그라운드 스레드의 리드 어헤드(Read ahead) 작업 진행

이 작업은 미리 필요할 데이터를 예측하여 InnoDB 버퍼 풀에 쌓아두는 작업.

그래서 처음 쿼리는 느리지만, 그 뒤에 쿼리가 빨라지는 것이다.

 

풀 인덱스 스캔 조건

SELECT COUNT(*) FROM employees;

 

위 쿼리는 풀 인덱스 스캔을 진행한다.

하지만 집계함수 없이 *(애스터리크) 로 모든 컬럼을 조회할 경우, 풀 테이블 스캔을 진행함.

꼭 필요한 컬럼만을 조회하도록 권장.

 

Order  By 처리 (Using filesort)

정렬을 처리하는 방법은 인덱스를 이용하거나, Filesort 를 이용하는 방법이 있다.

 

Filesort 사용 특징

- 정렬할 레코드가 적으면, 메모리에서 filesort 처리 되므로 빠르다.

- 쿼리 실행 시, 정렬 작업이 진행되므로 레코드 건수가 많을 경우 쿼리 응답 속도가 느리다.

 

Sort Buffer

Mysql 은 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받아 사용 -> Sort buffer

버퍼의 크리는 정렬할 레코드 크기에 따라 가변적으로 증가함. (시스템 변수로 설정 가능)

쿼리 실행이 완료되면 즉시 시스템을 반납 됨.

 

정렬할 레코드가 소트 버퍼 크기보다 클 경우, Mysql 은 정렬할 레코드를 여러 개로 나눠 처리.

임시 저장을 위해 디스크를 사용하며, 병합한다. 이 과정을 멀티 머지(Multi merge) 라고 함.

디스크 쓰기, 읽기를 모두 유발한다.

 

소트 버퍼를 크기를 늘리면 되지 않을까 하지만, 성능 차이가 그렇게 나지는 않는다.

더욱 더 큰 메모리 공간을 할당하기 때문에.

 

정렬 처리 방법

- 인덱스를 사용한 정렬

- 조인에서 드라이빙 테이블만 정렬

- 조인에서 조인 결과를 임시 테이블로 저장 후 정렬

 

정렬은 보통 위 3가지 방법으로 보통 처리 된다.

인덱스를 사용하지 못하면, 2번 째 조건을 최대한 노려야 한다.

조인 시, 레코드의 수와 크기는 배수로 불어나기 때문이다.

 

Group by 처리

Having 절은 Group by 결과에 대해 인덱스를 사용해서 처리될 수 없다. (고민하지 말자)

 

루스 스캔을 이용하는 Group by (타이트 인덱스 스캔)

루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어 가져오는 것.

Extra 칼럼에 Using index for group-by 코멘트 표시.

 

임시 테이블을 사용하는 Group by

group by 기준 칼럼이 드라이빙, 드리븐 관계 없이 인덱스를 전혀 사용하지 못할 경우, 채택.

Extra 칼럼에 Using temporary 코멘트 표시.

 

임시 테이블을 사용하면, group by 칼럼으로 유니크 인덱스를 가진 임시 테이블 생성.

조인 결과를 한 건씩 가져와 중복 체크 및 insert, update 진행.

그 후 order by 가 있다면, filesort 추가 진행.