옵티마이저와 힌트

요청된 쿼리는 결과가 같아도 결과를 만들어내는 방식은 매우 다양하다. Mysql에서는 Explain으로 쿼리의 실행계획을 확인할 수 있는데, 어떤식으로 최적화되어 실행되었는지 알수있다. 대부분의 DBMS에서는 옵티마이저가 이러한 동작을 담당한다.

Optimizer

옵티마이저는 DB구조중 가장 복잡한 부분으로 알려져 있고, 실행 계획을 이해하는 것 또한 상당히 어렵고 까다롭다 이러한 실행 계획을 이해할 수 있어야, 실행 계획의 불합리한 부분을 찾아내고 쿼리를 좀더 최적화 할 수있다.

Parse Tree 에선 Sql Parser라는 모듈로 문법확인및 서버 Parse tree를 생성한다 get data에서는 데이터 및 테이블을 선택한 후 내용을 가져온다

종류

옵티마이저의 종류는 크게 두종류의 옵티마이저가 존재한다.

규칙 기반 최적화 : 옵티 마이저의 우선도에 따라 실행 계획을 결정한다 비용 기반 최적화 : 여러가지 가능한 방법을 만들고 각 단위 작업의 비용 정보와 예측된 통계를 바탕으로 최소 비용 쿼리를 실행한다

데이처의 처리 방식은 풀 테이블 스캔이나 풀 인덱스 스캔이 주로 사용된다. Innodb의 경우 연속된 데이터 페이지가 읽히면 read Ahead작업으로 필요한 데이터를 미리 버퍼 풀에 가져오게 된다. 처음 데이터는 포그라운드 스레드, 이후는 백그라운드 스레드에서 캐싱 된다.

[full index scan]
> SELECT COUNT(*) FROM employees;
[full table scan]
> SELECT * FROM employees;

처리

병렬 처리 : Mysql 8.0부터는 쿼리의 병렬처리가 가능해졌다 [ innodb_parallel_read_threads ] 라는 시스템 변수로 하나의 쿼리가 처리하는 스레드의 개수를 지정할 수 있다. 단순 조회의 경우에만 사용할 수 있으며, 힌트는 따로 존재하지 않는다.

ORDER BY : 정렬의 처리는 인덱스를 이용하는 방법과 Filesort의 별도의 처리를 하는 방법이 있다 SORT BUFFER : 정렬을 수행하기 위한 별도의 메모리 공간이다. 정렬이 필요한 경우 사용하며, 최대 사용가능한 크기를 시스템변수로 지정할 수 있다

정렬

레코드를 정렬할 때, Sort buffer에 전체를 담을지, 기준을 담을지에 따라서 싱글 패스&투 패스 정렬 모드로 나뉘어지게 된다.

싱글패스 : 전체 대상을 담아 정렬을 수행하는 방식이다. 투패스 : 정렬 대상&키 값만 소트 버퍼에 담아 정렬을 수행

Order By가 사용되면 반드시 다음 처리 방법중 하나로 정렬이 수행된다

방법	내용	방식
Index	X	Order By가 가장 먼저 읽는 테이블에 맞게, Order by 순서에 맞는 인덱스가 있어야한다
Drive	using, file sort	드라이빙 테이블을 먼저 조회하여 정렬한 뒤 조인을 수행
Temporary	using temporary	2개이상의 테이블을 조인하는 경우, 조인결과를 임시테이블에 저장 후 다시 정렬하는 방식이며, 가장 느리다.

스트리밍 방식 : 레코드가 검색될 때마다 바로바로 클라이언트에 전송해주는 방식이다. 클라이언트가 쿼리를 요청후 곧바로 첫번 째 레코드를 전달받는다. 쿼리가 얼마나 많던지 상관없이 빠른 응답 시간을 보장해 주게 된다.

버퍼링 방식 : order By, Group by는 스트리밍된 결과값을 받을 수 없는데, 모든 레코드를 가져온 후 정렬&그룹화 해야하기 때문이다. 이 시간동안 클라이언트는 작업을 기다려야 하기 때문에 Buffering이라 한다

그룹화

Group by가 있는곳은 Having을 사용할 수 있는데, Group by의 필터링 역할을 수행하며, 인덱스를 사용할 수 없다.

With Index : 드라이빙 테이블에 속한 칼럼을 그룹화 할때는 인덱싱을 읽으며 수행한 후 조인한다.

Without Index : 인덱스 레코드를 건너 뛰면서 필요한 부분만 읽어 가져오는 것이다. [ using index for group-by ] With Temporary : 인덱스를 전혀 사용하지 못하는 경우 사용된다 [ Using Temporary ]

Distinct

유니크한 값을 가져오는 경우 사용하며, 집합함수의 유&무로 구분하여 살펴볼 수 있다.

With select : 단순 조회의 경우 Select Distinct 의 형태를 사용하며 group by와 동일하게 처리된다.

With group function : 조회하는 모든 조합의 유니크한 것들만 가져와 수행한다.

최적화