MySQL(13) - 옵티마이저

쿼리 실행 절차

1. SQL 파싱 - SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.

2. 최적화 및 실행 계획 수립 - 파스 트리를 확인하면서 어떤 테이블/인덱스를 이용할지 선택

3. 두 번째 단계에서 결정된 계획으로 작업 수행

 

옵티마이저의 종류

• 비용 기반 최적화 - 작업의 비용 정보와 예측 통계 정보를 이용하여 실행 계획을 산출하고, 비용이 최소로 소요되는 처리 방식을 선택한다.
• 규칙 기반 최적화 - 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어낸다.

 

ORDER BY 처리

소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼라고 한다. 소트 버퍼는 정렬이 필요한 경우에만 할당되고 정렬해야 할 레코드의 크기에 따라 가변적으로 증가한다. 대신 최대 사용 가능한 공간을 설정할 수 있다.

만약 정렬해야 할 레코드의 건수가 소트 버퍼의 공간보다 크다면 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해 둔다. 

이렇게 반복적으로 디스크에 임시 저장하는 것을 '멀티 머지'라고 한다. 이 작업은 디스크의 쓰기와 읽기를 유발하여 속도가 느려질 수 있다.

'멀티 머지'를 줄이기 위해 소트 버퍼의 공간을 크게 한다고 해도 일정 이상 크기까지만 효과가 있다. 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져 성능이 더 떨어질 수도 있다.

 

정렬 알고리즘

싱글 패스 정렬 방식 - 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담음

투 패스 정렬 방식 - 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식

투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 불합리하기 때문에 최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주로 사용한다.

SELECT 쿼리에서 모든 칼럼을 가져오도록 개발하는 것은 정렬 버퍼를 비효율적으로 사용할 가능성이 크다. 그래서 특히 정렬이 필요한 SELECT는 불필요한 칼럼을 가져오지 않도록 작성하는 것이 효율적이다.

 

정렬 처리 방법

• 인덱스를 사용한 정렬
  이 방법으로 정렬을 하려면 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
• 조인에서 드라이빙 테이블만 정렬
  이 방법으로 정렬을 하려면 조인에서 첫 번째로 읽히는 테이블의 칼럼만으로 ORDER BY 절을 작성해야 한다. 일반적으로 조인이 수행되면 레코드의 수가 늘어나기 때문에, 조인 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 차선책이다.
• 조인에서 조인 결과를 임시 테이블로 저장 후 정렬
  ORDER BY 절의 정렬 기준 칼럼이 드리븐 테이블이 있으면 이 방법이 사용된다.

정렬 처리 방법의 성능 비교

• 스트리밍 방식
  데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식이다.
  이 방식으로 처리되는 쿼리에서 LIMIT처럼 조건은 쿼리 전체 실행 시간을 상당히 줄여줄 수 있다.
• 버퍼링 방식
  ORDER BY나 GROUP BY 같은 처리는 스트리밍되는 것을 불가능하게 한다. 우선 조건에 맞는 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야 하기 때문이다.

어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 처리 방식으로 처리되는지는 더 큰 성능 차이를 만든다.

GROUP BY 처리

 

DISTINCT 처리

DISTINCT 처리를 위해 인덱스를 이용할 수 없는 경우 임시 테이블이 필요하다. 하지만 인덱스된 칼럼에 대해 DISTINCT 처리를 수행할 때는 인덱스를 사용하여 최척화된 처리를 수행할 수 있다.

 

내부 임시 테이블

내부 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없고 사용도 불가능하다. 사용자가 생성한 임시 테이블과 달리 내부 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.

일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 처리 성능이 상당히 느리다.

 

참조: 책 Real MySQL 8.0