9장 - 옵티마이저와 힌트 - 1편

 

옵티마이저란?

Optimize란 최대로 활용하다, 최적화하다는 뜻을 가진 영단어입니다.

DB에 조회하는 쿼리의 결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 다양합니다.

최소한의 비용을 드는방법으로 결과를 만들어내야 하는데 옵티마이저가 이 기능을 담당합니다.

 

옵티마이저의 종류로는 비용기반최적화, 규칙기반최적화기법이 있는데 대부분의 RDBMS가 비용기반최적화 방식을 활용하며 MySQL도 마찬가지입니다.

 

실행계획 확인하기

실행계획은 옵티마이저가 결과를 만들어내기 위한 방법을 사용자가 확인할 수 있는 방법입니다.

실행계획은 EXPLAIN이라는 명령으로 확인할 수 있습니다.

 

실행계획을 알아야 하는이유?

실행계획을 분석할 수 있어야 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있습니다.

 

 

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

데이터를 처음부터 끝까지 읽는 풀 테이블 스캔의 경우 단건씩 디스크로부터 페이지를 가져오진 않습니다.

최대 64개의 데이터 페이지를 읽어 버퍼풀에 저장하는 방식으로 쿼리의 속도를 최적화합니다.

 

SELECT COUNT(*) FROM employess;

 

같은 경우에는 레코드의 건수만 필요하기 때문에 풀 인덱스 스캔으로도 충분합니다.

풀 인덱스 스캔을 활용하면 인덱스는 보통 2~3개의 칼럼만 활용하기 때문에 용량이 적어 빠른 처리가 가능합니다.

 

Order by 그리고 Using filesort

인덱스는 정렬되어 있기 때문에 별도로 정렬할 필요가 없습니다.

하지만 인덱스가 없는 경우에는 Filesort라는 별도 처리를 통해 메모리에서 정렬이 됩니다.

만약 실행계획에서 using filesort 메시지가 표시된다면 별도의 정렬이 처리된 것입니다.

 

소트버퍼의 경우 별도의 메모리 공간을 받아서 정렬을 처리합니다.

하지만 정렬해야 할 레코드가 메모리 공간보다 많아지는 경우 임시 저장을 위해 디스크를 활용하게 되고 이런 작업을 multi -merge라고 표현합니다.

 

"소트버퍼를 크게 설정하면 괜찮을까?"라는 생각도 들지만 실제 벤치마크 결과로는 큰 차이를 보이지 않습니다.

 

또한 소트버퍼는 여러 클라이언트가 공유해서 사용하지 않기 때문에 너무 크게 잡으면 클라이언트가 실행될 때마다 메모리를 많이 소비하게 될 것이고 OOM-Killer가 여유 메모리를 확보하기 위해 프로세스를 강제로 종료할 수도 있습니다.

 

보통 OOM-Killer는 메모리를 가장 많이 사용하는 프로세스를 강제 종료하기 때문에 MySQL 서버가 1순위가 될 가능성이 큽니다.

 

싱글 패스 정렬, 투 패스 정렬

• 싱글 패스 : 정렬이 필요하지 않은 칼럼까지 넘김 (레코드 크기 또는 row수가 적을 때 효율적)
• 투 패스: 정렬이 필요한 칼럼만 넘기고 pk 기준으로 다시 조회해 옴 (레코드의 크기가 또는 row수가 클 때 효율적)

 

이런 이유로 SELECT 쿼리에서 꼭 필요한 칼럼만 조회하는 것이 효율적입니다.

 

조인과 정렬

• A, B를 조인하는데 A기준으로 ORDER BY 하는 경우

 

조인이 수행되면 레코의 건수가 몇 배로 불어나고, 레코드 단건의 크기도 증가합니다.

따라서 조인을 실행하기 전 첫 번째 테이블의 레코드를 먼저 정렬하고 조인을 실행하는 것이 차선책입니다.

이 방법을 위해서는 첫 번째로 읽히는 테이블의 칼럼만으로 ORDER BY를 작성해야 합니다.

 

임시 테이블을 이용한 정렬

• A, B를 조인하는데 B기준으로 ORDER BY 하는 경우

 

드라이빙 테이블(첫 번째로 읽히는 테이블)에서 조건 정렬이 일어나지 않는 경우입니다.

실행계획에 Using temporary; Using filesort라고 명시됩니다.

이는 조인의 결과를 임시 테이블에 저장하고 그 결과를 다시 정렬 처리했음을 의미합니다.

정렬 방법 중 가장 느린 경우로 경계해야 합니다.

 

정렬이 대게 느린 이유

데이터를 가져오는 2가지 방식

• 스트리밍 방식 - 데이터를 즉시 전달받아 LIMIT처럼 결과 건수를 제한하는 조건으로 실행 시간을 상당히 줄여줄 수 있음
• 버퍼링 방식 - ORDER BY, GROUP BY를 사용하면 모든 레코드를 검색하고 작업이 필요하기 때문에 스트리밍이 불가능하며 이런 이유로 응답속도가 느려짐

이런 이유로 LIMIT을 걸어두어도 네트워크에 전송되는 건수를 줄일 순 있지만 MySQL 서버가 해야 하는 작업량은 변화가 없음

 

단, 인덱스를 활용한 정렬은 스트리밍 형태의 처리가 가능하다!

 

 

정렬 최적화 방법

• 인덱스를 활용한 정렬이 가능하다면 인덱스 정렬
• 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 튜닝

 

 

GROUP BY 처리

GROUP BY와 함께 필터링을 위해 HAVING을 사용하곤 하는데 이런 이유로 스트리밍 된 처리를 할 수 없습니다.

GROUP BY에서 사용된 조건을 인덱스를 사용해서 처리되지 않으므로 HAVING 절을 튜닝하려고 인덱스를 생성할 필요는 없습니다.

 

GROUP BY의 경우에도 인덱스를 활용하면 루스 인덱스 스캔이 적용될 수 있습니다.

만약 인덱스를 활용하지 못한다면 임시 테이블을 활용합니다.

 

루스 인덱스 스캔을 활용하는 경우에는 Using index for group-by 코멘트가 표시됩니다.

 

만약 인덱스를 전혀 사용할 수 없다면 Using temporary 메시지가 표기됩니다.

MySQL 8.0부터는 묵시적인 정렬을 활용하지 않기 때문에 Using filesot가 표기되지는 않습니다.

이전 버전에서 묵시적 정렬을 피하기 위해서는 ORDER BY NULL을 사용할 수 있었습니다.

 

 

DISTINCT 처리

MIN, MAX와 같이 사용되는 경우와 그렇지 않은 경우가 달라집니다.

집합 함수와 같이 사용할 때는 임시 테이블이 필요하지만 Extra 칼럼에는 Using temporary 메시지가 출력되지는 않습니다.

버그처럼 보이지만 모든 버전의 MySQL 서버에서 보여주는 실행 계획에서 표기되지 않습니다.

 

SELECT DISTICT(A), B from MY_TABLE

 

위의 쿼리문은 A만 유니크하게 조회되는 것이 아니라 A, B가 모두 같이 유니크하게 조회합니다.

 

COUNT(DISTICT(A)), COUNT( COUNT(B))

위와 같은 쿼리에서는 A, B에 유니크 칼럼도 생성되며 각 칼럼에 대한 임시테이블을 만들어야 하기 때문에 전체적으로 2개의 임시 테이블을 사용합니다.

 

인덱스 된 칼럼에 대해서는 DISTINCT 처리를 위해 인덱스를 풀스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행할 수 있습니다.

 

임시 테이블

메모리상 혹은 디스크에 임시 테이블이 사용될 수 있습니다.

내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제됩니다.

 

Extra 칼럼에 Using temporary라는 메시지가 출력된다면 임시테이블을 활용하는 것이지만, 정확히 몇 개를 사용하는지는 모릅니다.

 

임시 테이블이 사용되는 경우를 경계하여야 빠른 쿼리 실행을 할 수 있습니다.