Real My SQL 8.0 읽고 공부하기 - ⑨ 옵티마이저와 힌트

1) 개요

● 실행 순서

MySQL에서 쿼리가 실행되는 과정은 다음과 같은 3단계이다.

 

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

2. 파싱정보 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.

3. 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.

 

1의 단계를 SQL 파싱이라고 하는데, 이 때 'SQL파서' 모듈로 처리한다. 

문법적으로 잘못된 SQL문인 경우 해당 단계에서 걸러진다. 해당 단계에서 SQL 파스 트리가 만들어지며

SQL문을 해당 SQL 파스트리를 실행하는 것이다.

2의 단계는 최적화 및 실행 계획 수립이며, '옵티마이저' 에서 처리를 한다. 완료되면 실행계획이 만들어진다.

3의 단계에서는 스토리지 엔진에서 레코드를 읽어오며 조인하거나 정렬하는 작업도 수행한다.

 

1,2단계는 MySQL엔진에서 주로 처리하며 3단계는 MySQL엔진과 스토리지 엔진이 동시에 참여해서 처리한다.

 

 

● 옵티마이저 종류

 

옵티마이저는 대부분 사용하고 있는 비용 기반 최적화 (CBO) 와 규칙 기반 최적화 방법(PBO)으로 나눌 수 있다.

비용기반 최적화는 말그대로 쿼리를 처리하기 위한 여러가지 가능한 방법을 만들고 각 단위 작업의 비용 정보를

계산하여서 비용이 최소로 소요되는 방법을 선택하는 방식이다.

규칙 기반 최적화는 옵티마이저 내장된 우선 순위에 따라 실행계획을 수립하는 방식이다.

 

 

2) 기본 데이터 처리

 

풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝가지 읽어서 요청된 작업을 처리하는 방식

주로 레코드건수가 너무 작은 경우, 쿼리에 적절한 조건이 없는 경우, 조건 일치 레코드 건수가 너무 많은 경우

이 방식을 선택한다.

InnoDB에서는 포그라운드 스레드로 읽기 시작하다가 특정 시점부터는 백그라운드 스레드로 읽는 작업(리드 어헤드)가 시작된다.

 

풀 인덱스 스캔은 인덱스를 처음부터 끝가지 스캔하는 것을 의미하며, 

SELECT COUNT(*) FROM table; 과같이 레코드 건수를 조회할때에는 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를

줄이므로 풀 인덱스 스캔을 이용한다.

 

 

● 병렬 처리

 

SET SESSION innodb_parallel_read_threads = {num}; 로 스레드 수 설정을 할 수 있으며

WHERE 조건 없이 단순하게 테이블 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.

병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수 있으니

주의하도록 하자.

 

 

● ORDER BY 처리

 

인덱스를 이용: 이미 인덱스가 정렬되어있어 순서대로 읽기만 하면 되므로 매우 빠르다.

레코드 추가/변경시 인덱스에 대한 작업이 추가적으로 이뤄지므로 느리고 디스크/메모리 공간이 더 필요하다.

Filesort 이용: 정렬해야할 레코드가 많지 않으면 메모리에서 FileSort해도 빠르지만, 레코드가 많아질 수록 느려질 수 있다.

 

모든 정렬에서 Index를 이용하도록 튜닝하기란 불가능하다. ● 정렬 기준이 너무 낳아서 요건별로 모두 인덱스 생성이 불가능한점

● Group By 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야하는 경우

● UNION 결과와 같이 임시 테이블 결과를 다시 정렬해야하는 경우

● 랜덤하게 결과 레코드를 가져와야 하는 경우

 

만약 FileSort를 이용하는 경우는 실행계획의 Extra 컬럼에서 Using filsort라는 메시지 표시 여부로 판단할 수 있다.

 

 

● 소트 버퍼

 

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용한다. 이를 소트 버퍼라고한다.

레코드가 소량인 경우 소트 버퍼를 이용할 수 있지만 레코드가 많다면 소트버퍼에 수차례 로드하면서 정렬을 시행하고

결과를 임시로 디스크에 저장한다.

그 이후 정렬된 레코드를 다시 병합하면서 정렬을 수행하며 이 병합 작업을 멀티 머지라고 한다.

 

 

● 정렬 알고리즘

 

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지  -> 싱글패스 정렬

정렬기준 컬럼만 담을지 ->  투패스 정렬 모드로 나눌 수 있다.

 

투 패스의 경우 테이블을 두번 읽어야 하기 때문에 불합리하지만 싱글 패스는 이러한 불합리는 없다.

단, 메모리 버퍼 공간이 더 필요하게된다.

 

싱글패스의 경우 정렬 대상 레코드 크기나 건수가 작은 경우 빠른 성능을 보이지만,

투 패스의 경우 정렬 대상 레코드의 크기나 건수가 많은 경우 효율적이다.

 

정렬 처리 방법

쿼리에 ORDER BY 가 사용되면 다음 3가지 처리 방법중 하나로 정렬 처리된다. Extra 컬럼에서 확인할 수 있다.

아래쪽으로 갈수록 처리속도는 느려진다.

- 인덱스를 사용한 정렬 : 별도 표기 없음

- 조인에서 드라이빙 테이블만 정렬: Using filesort 표기

- 조인에서 조인결과를 임시 테이블로 저장 후 정렬: Using temporary; Using filesort 표기

 

① 인덱스를 사용한 정렬

인덱스를 사용한 정렬을 위해서는 반드시 Order by에 명시된 컬럼이 제일 먼저 읽는 테이블에 속하고 ORDER BY 순서대로 생성된

인덱스가 존재하여야한다. 해시 인데스나, 전문 검색 인덱스 등에 사용할 수 없다.

여러 테이블이 조인될 경우 네스티드 루프 방식의 조인에서만 이방식을 사용할 수 있다.

인덱스를 이용해 정렬을 처리하는 경우 인덱스가 이미 정렬되어있기 때문에 인덱스 순서대로 읽기만 하면 된다.

 

② 조인의 드라이빙 테이블만 정렬

일반적으로 조인이 수행되면 결과 레코드 건수와 레코드 크기도 커지기 때문에 조인을 실행하기 전의 첫 번재 테이블의 레코드만

먼저 정렬 후 다음 조인을 실행하는 것이 해당 정렬의 차선책이다.

이 정렬로 처리 되려면 첫번째로 읽히는 테이블의 컬럼만으로 ORDER BY 절을 작성해야한다.

 

③ 임시 테이블을 이용한 정렬

2개 이상의 테이블을 조인해서 그 결과를 정렬해야한다면 임시 테이블이 필요할 수 있다.

조인의 결과를 임시 테이블에 저장하고, 그결과를 다시 정렬하는 과정을 거친다.

ORDER BY 기준 컬럼이 드리븐 테이블에 있으면 해당 정렬을 사용할 가능성이 높다.

 

 

● 쿼리 처리 방식 비교

 

스트리밍 방식

서버에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될때마다 클라이언트에게 바로 전송해주는 방식

응답속도가 빠른 방식이나 마지막 레코드가 언제 전달되는지는 알 수없다.

LIMIT 처럼 결과 건수를 제한해서 가져오면 전체 쿼리 실행시간을 줄여줄 수 있다.

웹 서비스 같은 OLTP 환경에서는 응답시간이 중요하기 때문에 해당 방식을 이용하는 것을 선호한다.

 

 

버퍼링 방식

모든 레코드를 가져온 후 정렬하거나 그루핑해서 차례대로 보낸다. 이로 인해 응답속도가 느려진다.

LIMIT처럼 결과 건수를 제한하는 내용이 있어도 성능향상에 큰 도움이 되지는 않는다.

 

※ SELECT * FROM data 같은 쿼리를 실행하면 MySQL은 레코드를 읽자마자 클라이언트로 그 결과를 전달하지만 JDBC는

MySQL서버로부터 받는 레코드를 읽단 내부 버퍼에 담아두고 마지막 레코드가 전달될 때까지 기다렸다가 모든 결과를 받으면

그때서야 클라이언트 애플리케이션 에 반환한다. 즉 MySQL서버는 스트리밍 방식으로 처리해서 반환하지만 클라이언트의

JDBC라이브러리가 버퍼링하는 것이다.

 

앞서 소개한 ORDER BY를 처리하는 방법 중 ① 인덱스를 이용한 정렬 방식만 스트리밍 형태로 처리되며 나머지는 모두

버퍼링 되어 정렬된다.

 

 

● GROUP BY 처리

 

GROUP BY HAVING 절로 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING절을 튜닝하는 것은 무의미하다.

GROUP BY 또한 인덱스를 사용한 경우, 그렇지 못한 경우로 나눌 수 있는데

인덱스를 사용한 경우 또 한번 인덱스 스캔 방법과 루스 인덱스 스캔방법으로 나눌 수 있다.

인덱스를 사용하지 못한 경우는 임시 테이블을 이용해서 그루핑화한다.

 

인덱스 스캔을 이용해서 GROUP BY 하는 경우 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인한다.

그룹 함수를 사용하는 경우에는 임시 테이블이 필요할때가 있다.

해당 방식을 이용하는 경우 Extra 컬럼에 메시지가 따로 표기되지 않는다.

 

루스 인덱스 스캔을 이용해서 GROUP BY 하는 경우 Extra 컬럼에 Using index for group-by가 출력되며

인덱스의 레코드를 건너 뛰면서 필요한 부분만 읽어온다.

 

임시 테이블을 사용하는 GROUP BY의 경우 그룹 기준 컬럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를

전혀 사용하지 못할 때 이 방식으로 처리된다. Extra 컬럼에 Using temporary가 출력된다.

또한 8.0 버전 부터는 GROUP BY 컬럼으로 묵시적 정렬을 수행하지 않는다. ORDER BY와 같이 사용될 경우 정렬리 사용될 수 있다.

 

 

● DISTINCT 처리

 

특정 컬럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.DISTINCT는 MIN(), MAX()와 같은 집합함수와

함께 사용되는 경우 , 아닌 경우를 구분해서 살펴보아야한다. 

또한, 집함함수와 같이 DISTINCT가 사용되는 쿼리의 실행계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 경우 임시테이블이

필요하게된다.

 

단순 SELECT DISTINCT... 으로 유니크한 레코드만 가져오고자 하면 GROUP BY와 동일한 처리방식으로 진행된다.

SELECT DISTINCT seq_member FROM member;
SELECT seq_member FROM member GROUP BY seq_member;

 

사용중 주의해야할 것은

SELECT DISTINCT name, age FROM member;

위와 같이 DISTINCT 한다고 해서 name으로 유니크한 값을 가져오는것이 아닌, (name, age) 조합으로 유니크한 값을 찾아온다는 것이다.

DISTINCT(name), age도 동일한 처리를 한다. "()" 를 무시하기 때문이다.

 

 

집합 함수와 함께 사용된 DISTINCT

COUNT(), MIN()과 같은 집합 함수내에서 DISTINCT 키워드가 사용될 수 있는데

그 안에서의 DISTINCT는  그 집합 함수의 인자로 전달된 컬럼값이 유니크한 것들을 가져온다.

SELECT COUNT(DISTINCT t.name)
  FROM member m, tmp t
 WHERE t.seq_member = m.seq_member
   AND m.seq_member BETWEEN 1001 AND 1005;

위의 COUNT(DISTINCT t.name)을 처리하기 위해  내부적으로 임시 테이블을 생성해서 처리한다.

 

만약 2개의 각각 다른 컬럼값을 COUNT해온다면 임시테이블을 2개 생성해야한다.

SELECT COUNT(DISTINCT t.name),
       COUNT(DISTINCT m.name)
  FROM member m, tmp t
 WHERE t.seq_member = m.seq_member
   AND m.seq_member BETWEEN 1001 AND 1005;

 

 

● 메모리 임시 테이블과 디스크 임시 테이블

 

8.0부터는 임시테이블이 메모리를 사용할 때는 TempTable(메모리 엔진이 지원하지 않는 가변타입 제공)이라는 스토리지 엔진을 사용하고

디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선되었다.

MySQL서버의 기본설정으로 TempTable의 메모리 크기가 1GB이상으로 커지는 경우 디스크로저장하게 하는데,

InnoDB엔진 혹은 MMAP(기본값)으로 저장할 수 있다.

 

임시 테이블이 항상 메모리 임시테이블을 먼저 사용하도록 되어있는 것은 아니다. 

처음 부터 디스크기반으로 진행될 수 있는데, 이 경우 기본값은 InnoDB로 되어있다.

 

 

● 임시 테이블이 필요한 쿼리 예시

 

- GROUP BY와 ORDER BY에 명시된 컬럼이 다른쿼리

- ORDER BY 나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리

- DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 DISTINCT가 인덱스로 처리되지 못하는 쿼리

- UNION이나 UNION DISTINCT가 사용된 쿼리

- 쿼리의 실행 계획에서 SELECT_TYPE이 DERIVED인 쿼리

 

 

● 임시 테이블이 디스크로 생성되는 경우

 

- UNION 이나 UNION ALL 에서 SELECT되는 칼럼중 길이가 512 바이트 이상인 크기의 컬럼 존재시

- GROUP BY 나 DISTINCT 컬럼에서 512 바이트 이상인 컬림이 있는 경우

- 메모리 임시 테이블 크기가 tmp_table size OR max_heap_table_size, temptable_max_ram 시스템 변수보다 큰 경우

 

 

 

3) 고급 최적화

옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립한다.

옵티마이저 옵션은 크게 옵션과 스위치로 구분할 수 있다.

 

 

● 옵티마이저 스위치 옵션

 

여러가지 시스템 변수를 이용해서 스위치 옵션을 제어한다. 

조인 알고리즘, 엔진의 기능, 최적화 처리에 대한 옵션 설정을 진행 할 수 있다.

 

 

 

● MRR과 배치 키 엑세스

 

MRR은 Multi-Range Read를 줄여서 부르는 이름이다. MySQL에서 지금까지 지원하던 조인방식은

드라이빙 테이블(조인에서 먼저 읽은 테이블)의 레코드 한건을 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서

조인을 수행하는 것이었다. 이를 네스티드 루프 조인이라고 한다.

 

MySQL은 조인 처리는 MySQL 엔진이 처리하지만 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당한다.

만약 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면 레코드를 찾고 읽는 스토리지 엔진에서

아무런 최적화 수행히 불가능하다.

 

이 단점을 보완하기 위해 MySQL 서버에서는 조인 대상 테이블중 하나로부터 레코드를 읽어 조인 버퍼에 버퍼링한다.

조인 버퍼에 레코드가 가득차면 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청한다.

이렇게 해서 디스크 읽기를 최소화할 수 있으며 이러한 읽기 방식을 MRR이라고한다.

MRR을 이용해서 실행되는 조인방식을 BKA(Batched Key Access) 조인이라고 한다.

해당 최적화는 기본적으로 비활성화되어있다. (단점이 있기;  때문)

 

 

● 블록 네스티드 루프 조인

 

조인알고리즘에서 Block이라는 단어가 사용되면 조인용으로 별도의 버퍼가 사용됬다는 것을 의미한다.

앞서 네스티드 루프조인과의 차이는 조인버퍼가 사용되었는지, 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인됬느냐가

차이가난다. 8.0.20 버전부터는 해시 조인 알고리즘으로 대체되어 사용된다.

 

● 인덱스 컨디션 푸시다운

 

last_name , first_name으로 인덱스가 이루어져 있을 때

SELECT *
  FROM member
 WHERE last_name = 'KIM'
   AND first_name = '%TAE';

인덱스 컨디션 푸시다운 기능이 꺼져있으면 last_name가 일치하는 인덱스를 모두 찾아온 후 first_name을 필터조건 (where)으로

처리 해버린다.

 

해당 기능이 켜져있는 경우 인덱스를 최대한 활용해 최대한 필터링을 이용해서 Action, %TAE로 이루어진 컬럼으로만

테이블 읽기를 수행한다.

 

 

● 인덱스 확장

 

InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리에 인덱스에 자동으로 추가된 프라이머리 키를

활용할 수 있게 할지 결정하는 옵션이다.

만약 프라이머리 키가 seq_member, name이고 인덱스 키가 reg_dt이면

reg_dt, seq_member, name조합으로 인덱스 생성한 것과 흡사하게 작동 시킬 수 있다.

이로인해 정렬에서 장점을 얻을 수도 있다.

 

 

● 인덱스 머지

 

보통 하나의 테이블에 하나의 인덱스를 이용하도록 실행계획을 처리하지만 인덱스 머지의 경우 2개 이상의 인덱스를 이용해 

쿼리를 처리한다.

 

두 개의 컬럼이 인덱스로 되어있고 쿼리 조건문(AND)에 사용한 경우 옵티마이저가 각각 조건에 일치하는 레코드 건수를 예측해본 결과

두 조건 모두 많은 레코드를 가져와야한다고 하였을때 교집합을 통한 인덱스 머지가 활용된다.

실행계획 Extra컬럼에 Using inersect가 표기된다.

 

위와 동일한 컬럼이 인덱스로 되어있고 조건문이 OR로 사용된 경우 합집합을 통해서 최적화할 수 있다.

실행 계획 Extra커럼에 Using Union이 표기된다.

Union은 합집합을 이용하게되는데 중복제거를 어떻게 하는 걸까?

이는 인덱스 각각에 저장된 프라이머리 키가 이미 정렬되어있기 때문에 두 집합을 비교하면서 하나씩 비교하면서 중복된 레코드들을

정렬없이 걸러낼 수 있다.

 

모든 경우에  위와 같이 정렬없이 합집합을 이뤄낼 수 없는데 이럴 경우 정렬 후 합집합을 이용해서 최적화(Sort Union 알고리즘)한다.

Extra 컬럼에 Using sort_union으로 표기된다.

 

 

● 세미 조인

 

실제 조인을 수행하지는 않고 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지 체크하는 형태 쿼리를

세미 조인이라고 한다.

8.0부터는 다음과 같은 최적화 전략들이 존재한다.

- Table Pull-out

서브 쿼리에 사용된 테이블을 아우터 쿼리로 끄지어낸 후에 쿼리를 조인 쿼리로 재작성하는 형태의 최적화이다.

서브쿼리 부분이 유니크 인덱스나 프라이머리 키 룩업으로 결과가 한 건인 경우에만 사용 가능하다.

 

- Duplicate Weed-out

세미 조인 서브 쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 최적화 알고리즘이다.

상관 서브 쿼리라고 해도 사용할 수 있으며, GROUP BY 혹은 집합함수에서는 사용 불가능하다.

 

- First Match

IN(서브쿼리) 형태의 세미조인을 EXISTS(서브쿼리) 형태로 튜닝한 것과 비슷한 방법으로 실행된다.

상관 서브 쿼리에서도 사용될될 수 있으며 해당 최적화가 적용되면 Extra컬럼에 FirstMatch(table-N)문구가 표기된다.

 

- Loose Scan

루스 인덱스 스캔으로 유니크한 값만 읽으면 아주 효율적으로 서브 쿼리 부분을 실행할 수 있다. (중복된 레코드를 제거하면서)

루스 인덱스 스캔으로 서브 쿼리 테이블을 읽고 그 다음으로 아우터 테이블을 드리븐으로 사용해서 조인을 수행한다.

그래서 서브 쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용가능한 최적화다.

 

- Materialization

세미 조인에 사용된 서브 쿼리를 통째로 구체화(내부 임시 테이블 생성) 해서 쿼리를 최적화한다는 의미이다.

실행 계획의 Select_type이 MATERIALIZED 가 나오면 내부 임시 테이블이 생성된 것이다.

다른 서브쿼리 최적화와 달리 GROUP BY 절이 있어도 이 최적화 전략을 사용할 수 있다.

 

※상관 서브쿼리란?

SELECT 
    e.EmployeeName,
    e.Department,
    e.Salary,
    (SELECT COUNT(*) 
     FROM Employees AS e2 
     WHERE e2.Department = e.Department AND e2.Salary > e.Salary) AS HigherSalaryEmployees
FROM Employees AS e;

 

위와 같이 서브쿼리의 조건이 아우터 쿼리의 값에 의존해얗라 때 주로 사용된다.

장점이 있지만 성능에 영향을 미칠 수 있으므로 신중하게 사용하는것이 좋다.

 

 

● 컨디션 팬아웃

 

조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미치는데, 어느 테이블이 드라이빙 테이블로 결정되느냐에 따라

읽어올 레코드 건수가 달라지기 때문이다.

그래서 옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.

condition_fanout_filter를 활성화하면 다음과 같은 조건을 만족하는 컬럼의 조건들에 대해 조건을 만족하는 레코드 비율을 계산할 수 있다.

1. WHERE 조건절에 사용된 컬럼에 대해 인덱스가 있는 경우

2. WHERE 조건절에 사용된 컬럼에 대해 히스토그램이 있는 경우

인덱스 + 필터 조건으로 인한 비율 (Extra의 Filtered컬럼) 레코드 건수를 비교하여 어떠한 순서대로 진행할것인지 선택하는 것이다.

 

● 파생 테이블 머지

예전 버전의 MySQL서버에서는 다음과 같이 FROM 절에 사용된 서브 쿼리는 먼저 실행해서 그 결과를 임시 테이블로 만든다음

외부 쿼리 부분을 처리했다.

SELECT *
  FROM (
  	SELECT *
      FROM member
     WHERE name = 'tae' 
  ) tb
 WHERE tb.reg_dt = '2023-10-29';

 

이 쿼리의 실행 계획을 보면 member테이블을 읽는 라인의 select_type의 값이 DERIVED라고 표기된다.

이는 member테이블 에서 name이 'tae'인 것만 읽어서 임시 테이블 생성 후 이 임시 테이블을 다시 읽어서

reg_dt가 '2023-10-29'인 값만 읽게 하는 것이다.

 

이렇게 FROM 절에 사용된 서브쿼리를 파생테이블(Derived Table)이라고 부른다.

하지만 이렇게 임시 테이블을 생성하게되면 메모리 소모가 커지고 때론 디스크를 사용하게 된다.

5.7버전부터는 이렇게 파생 테이블로 만들어지는 서브 쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입되었다.

 

 

● 인비저블 인덱스

 

8.0부터 인덱스의 가용상태를 제어할 수 있는 기능이 추가되었다.

인덱스를 삭제하지 않고 해당 인덱스를 사용하지 못하게 제어하는 기능이다.

ALTER TABLE ... ALTER INDEX ... [VISIBLE | INVISIBLE] 명령으로 인덱스 가용 상태를 변경할 수 있다.

(옵티마이저가 해당 인덱스를 볼 수 없다.)

 

● 스킵 스캔

 

인덱스가 (A,B,C) 순의 컬럼으로 되어있지만 B, 혹은 B,C에 대한 조건으로 쿼리를 짜는 경우 인덱스를 활용할 수 있게 하는 기법이다.

 

 

● 해시 조인

8.0.18 부터 해시조인이 추가로 지원되었는데, 네스티드 루프 조인보다 해시조인보다 빠르다고 생각하겠지만 항상 옳지는 않다.

네스티드 루프 조인은 첫번째 레코드를 찾아내는데 빠르며, 마지막 레코드를 찾을때까지가 느리다.

해시조인은 첫번째 레코드를 찾아내는것은 느려도 마지막 레코드를 찾는 것이 빠르다.

 

즉, 해시조인은 최고 스루풋 전략에 적합하며, 네스티드 루프 조인은 최고 응답 속도 전략에 적합하다.

OLTP에서는 응답속도가 조금 더 중요하고 분석과 같은 서비스는 전체 스루풋이 조금 더 중요하다.

 

 

해시 조인은 빌드 단계와 프로브 단계로 나뉘어 처리되는데 빌드단계에서는 조인 대상 테이블중에서 레코드 건수가 적어서

해시 테이블로 만들기 용이한 테이블을 골라서 메모리에 해시 테이블을 생성하는 작업을 수행한다.

 

프로브 단계에서는 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정을 진행한다.

 

해시 테이블을 메모리에 저장할때 조인버퍼를 이용하는데 만약 공간이 부족하는 경우 빌드 테이블과 프로브 테이블을 

적당한 크기(하나의 청크가 조인 버퍼 보다 작도록) 청크로 분리(디스크 저장)한다음 청크 별로 해시 조인과 동일방식으로

해시조인을 처리한다.

 

 

● 인덱스 정렬 선호

 

옵티마이저는 GROUP BY 또는 ORDER BY를 인덱스를 이용해 처리 가능한 경우 실행 계획에서 이 인덱스 가중치를

높게 설정해서 실행된다.

 

그러나 이렇게 가중치가 높게 된다면 잘못된 실행 계획이 될 가능성이 높아서

8.0.21부터는 prefer_ordering_index 옵션이 추가되어서 옵티마이저가 자주 실수하는 경우 OFF할 수 있게 변경되었다.

 

● 조인 최적화 알고리즘

 

MySQL 5.0부터 제공된 기능으로 조인 쿼리의 실행 계획 최적화를 위한 알고리즘이 2개 존재한다.

하나의 쿼리에서 조인되는 테이블의 개수가 많아지면 실행 계획 수립시에도 몇 분이 걸릴 수 있다. 다음 2개의 알고리즘을

살펴보자

 

- Exhaustive 알고리즘 : FROM절에 명시된 모든 테이블 조합에 대해 실행 계획의 비용을 계산, 최적의조합 1개를 찾는방법이다.

테이블이 20개라면 가능한 조인 조합을 20! (3628800)개인데 이 방법으로 사용하면 계획 수립시 몇분이 걸릴지 모른다.

 

- Greedy 검색 알고리즘: 앞선 Exhaustive알고리즘을 해결하기 위한 최적화 기법으로  다음과 같은 알고리즘 과정을 거친다.

1. 전체 N개의 테이블 중에서 optimizer_search_depth 변수에 설정된 개수의 테이블로 가능한 조인 조합 형성

2. 1번에서 생성된 조인 조합 중 최소 비용의 실행 계획 선정

3. 2번에서 선정된 실행 계획의 첫번째 테이블을 부분 실행 계획의 첫 번째 테이블로 선정

4. 전체 N-1개의 테이블 중 optimizer_search_depth 시스템 설정 변수에 정의 된 개수의 테이블로 가능한 조인 조합 생성

5. 4번에서 생성된 조인 조합들을 하나씩 3번에서 생성된 부분 실행계획에 대입해 실행 비용 계산

6. 5번 비용 계산 결과 최적의 실행 계획에서 두 번째 테이블을 3번에서 생성된 부분 실행계획의 두 번째 테이블로 선정

7. 남은 테이블이 없어질 때까지 4~6 반복하면서 부분 실행 계획에 테이블 조인 순서 기록

8. 최종적으로 부분 실행 계획이 테이블의 조인 순서로 결정됨.

 

 

---

 

4) 쿼리 힌트 

 

옵티마이저가 부족한 실행계획을 수립할 때가 있는데, 이럴 때 옵티마이저에게  쿼리의 실행 계획을 어떻게 수립해야할지

알려줄 방법이 필요한데 이것을 쿼리힌트라고 한다.

MySQL 서버에서 사용 가능한 쿼리 힌트는 다음과 같다.

 

● 인덱스 힌트

"STRAIGHT_JOIN" 과 "USE INDEX" 등을 포함한 인덱스 힌트들은 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에

사용되던 기능들이다. 이는 SQL 문법에 사용해야하는데, 이 것이 ANSI-SQL 표준 문법을 준수하지 못하게 되는 단점이 있다.

 

5.6버전부터 추가된 옵티마이저 힌트들은 다른 RDBMS에서는 주석으로 처리되어서 ANSI-SQL표준을 준수할 수 있게 됬다.

그래서 가능하면 인덱스 힌트대신 옵티마이저 힌트를 사용하는 것을 권장한다.

 

STRAIGHT_JOIN : 해당 쿼리는 옵티마이저 힌트인 동시에 조인 키워드이다. SELECT, UPDATE, DELETE 쿼리에서

여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.

 

SELECT /*! STRAIGHT_JOIN */
       m.name,
       o.order_id,
       p.payment_method
  FROM member m, order o, payment p
 WHERE m.seq_member = o.seq_member
   AND d.seq_order = p.seq_order
   AND m.seq_member = 100;

 

다음과 같은 경우에 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

(1) 임시 테이블(인러인 뷰 또는 파생된 테이블) 과 일반 테이블의 조인 

일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다.

(일반 테이블에 인덱스가 없는 경우 레코드 건수가 작은쪽을 드라이빙 테이블로 하도록 하자)

 

(2) 임시테이블 끼리 조인: 임시 테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 드라이빙으로 읽어도 무관한데,

되도록이면 건수가 작은 쪽을 선택한다.

 

(3) 일반 테이블끼리 조인: 양쪽 모두 조인 컬럼에 인덱스가 있거나 모두 없는 경우 레코드 건수가 적은 테이블을

드라이빙 테이블로 선택한다. 그 외의 경우 조인 컬럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋다.

 

비슷한 힌트로는 JOIN_FIXED_ORDER, JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX 가 있다.

 

USE INDEX / FORCE INDEX / IGNORE INDEX

STRAIGHT_JOIN 과 달리 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다.

SELECT *
  FROM member m USE INDEX(primary)
 WHERE m.seq_member = 100;

(1) USE INDEX: 가장 자주 사용되는 인덱스 힌트이며 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는

힌트이다. (항상 사용하지는 않는다.)

 

(2) FORCE INDEX: USE INDEX와 다른점은 없으며 옵티마이저에게 미치는 영향이 더 강한 힌트다. 

USE INDEX로도 인덱스 힌트가 되지 않으면 대부분 FORCE INDEX로도 힌트가 되지 않는다.

 

(3) IGNORE INDEX: 특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트다. 가끔 풀 테이블 스캔을

유도하기 위해 해당 힌트를 사용할 수 있다.

 

인덱스 용도를 명시해줄 수도 있다.

 

(1) USE INDEX FOR JOIN: 여기서 JOIN 키워드는 테이블간 조인뿐이아닌 레코드 검색하기 위한 용도까지 포함하는 용어다.

(2) USE INDEX FOR ORDER BY: 명시된 인덱스를 ORDER BY 용도로만 사용가능하게 한다.

(3) USE INDEX FOR GROUP BY : 명시된 인덱스를 GROUP BY 용도로만 사용 가능하게 한다.

 

인덱스의 사용법이나 좋은 실행 계획이 어떤 것인지 판단하기 힘든 상황인 경우 힌트를 사용해 실행계획에 영향을 끼치는 것은

좋지 않다. 최적의 실행계획은 데이터의 성격에 따라 시시각각 변하므로 옵티마이저가 가지고 있던 통계정보를 가지고 선택

하는 것이 가장 좋다.

 

 

SQL_CALC_FOUND_ROWS

LIMIT을 사용하는 경우 LIMIT에 명시된 수만큼 만족하는 레코드를 찾으면 즉시 검색 작업을 멈추게 되는데 

SQL_CALC_FOUND_ROWS 힌트가 포함된 쿼리의 경우 LIMIT를 넘어서 끝까지 검색을 수행한다.

 

해당 힌트가 사용된 쿼리가 실행된 경우 FOUND_ROWS()라는 함수를 이용해 조건을 만족하는 레코드가 전체 몇건인지 알아낼 수 있다.

하지만 이 힌트를 사용하는 경우 사실 COUNT(*) 쿼리 보다 I/O작업이 더 많이 일어나게 된다.

SELECT SQL_CACL_FOUND_ROWS * FROM member WHERE name = 'tae%' LIMIT 20;

SELECT FOUND_ROWS() AS total_record_count;

사실 위와 같이 쿼리 2번을 날려서 해당 레코드 갯수를 알아야 하며, 해당힌트가 있는 LIMIT쿼리의 경우 20번을 넘어서 레코드의

총 갯수까지 랜덤 I/O작업을 하기 때문이다.

 

반면 COUNT(*) 쿼리를 사용하는 경우 동일하게 쿼리를 2번 사용하지만 커버링 인덱스를 사용하기 때문에 

랜덤I/O작업이 일어나지는 않는다.

 

또한 해당 힌트는 여러가지 문제점을 가지고 있어 사용하지 않는 것을 권장하며 COUNT쿼리를 튜닝해나가면서 성능을 최적화하는

것을 권장한다.

 

 

● 옵티마이저 힌트

 

옵티마이저 힌트는 영향 범위에 따라 4개 그룹으로 나눌 수 있다.

 

◆ 인덱스: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트

◆ 테이블: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트

◆ 쿼리 블록: 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트 - 힌트가 명시된 쿼리블록에 대해서만 영향을 미친다.

◆ 글로벌(쿼리 전체): 전체 쿼리에 대해서 영향을 미치는 힌트

 

인덱스 힌트는 다음과 같이 사용할 수 있다. (테이블 명이 꼭 선행되어야한다.)

 

SELECT /*+ INDEX(member ix_name) */ *
  FROM member
 WHERE name = 'Tae';

 

하나의 SQL문장에서 SELECT 키워드는 여러번 사용될 수 있는데, 이 때 SELECT 키워드로 시작하는 서브 쿼리 영역을 쿼리 블록이라

한다.

 

MAX_EXECUTION_TIME

옵티마이저 힌트중 쿼리 실행계획에 영향이 없는 힌트이며 쿼리의 최대 실행시간을 설정하는 힌트이다.

 

SET_VAR

MySQL서버의 시스템 변수들 또한 실행계획에 영향을 미치는데 (조인 버퍼 공간의 크기가 여유있을시 조인버퍼를 이용하도록)

이렇게 시스템 변수(조인 버퍼 크기 같은) 를 일시적으로 변경시켜 대용량 쿼리 성능을 높일 수 있으므로 해당 힌트를 기억해두도록 하자.

 

SEMIJOIN & NO_SEMIJOIN

세미 조인을 Duplicate Weed-out, FirstMatch등 어떠한 세부 전략을 사용할지 제어하는데 사용할 수 있다.

 

SUBQUERY

세미조인 최적화가 사용되지 못할 때 서브 쿼리 최적화를 사용하는데 IN-to-EXISTS 혹은 Materialization을 사용할 수 있다.

(주로 안티 세미 조인 최적화에 사용된다.)

 

BNL& NO_BNL & HASHJOIN & NO_HASHJOIN

블록 네스티드 루프조인 사용, 해시조인 사용 여부에 대한 힌트이다. 8.0.18 이후 버전에서는

HASH조인 힌트가 없어서 BNL과 NO_BNL(해시조인사용)으로 해시조인을 유도하여야 한다.

 

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

조인 순서를 결정하기 위한 힌트이다. 각각 설명을 하자면

JOIN_FIXED_ORDER: STRAIGHT_JOIN 힌트와 동일하게 FROM절 테이블 순서대로 조인

JOIN_ORDER: FROM 절에 사용된 테이블 순서가 아닌 힌트에 명시된 테이블 순서대로 조인

JOIN_PREFIX: 조인에서 드라이빙 테이블만 강제하는 힌트

JOIN_SUFFIX: 조인에서 드리븐 테이블만 강제하는 힌트

 

MERGE & NO_MERGE

이전의 MySQL서버에서는 FROM절에 사용된 서브 쿼리를 항상 내부 임시 테이블로 생성하였는데(파생 테이블[Derived table])라고 하는데

불필요한 자원 소모를 유발하였다. 5.7부터는 임시 테이블을 사용하지 않게 FROM 절의 서브쿼리를 외부 쿼리와 병합하는 최적화를

도입했다. 하지만 때로는 임시테이블을 생성하는 것이 나을 수도 있어서 해당 힌트를 사용하여 실행 계획을 수정 할 수 있다.

 

INDEX_MERGE & NO_INDEX_MERGE

서버는 테이블당 하나의 인덱스만을 이용해 처리하려고 하는데, 하나의 인덱스로 검색범위가 충분히 좁혀지지 않는다면 가능한

다른 인덱스를 이용하기도 한다. 여러 인덱스를 통해서 검색된 레코드로부터 교집합 혹은 합집합만을 구하는데

이처럼 여러개의 인덱스를 동시에 사용하는 것을 INDEX MERGE라고 한다. 여러 인덱스 사용 유무 제어에 대한 힌트이다.

 

NO_ICP

인덱스 컨디션 푸시다운 최적화(ICP) 는 사용 가능하면 항상 성능 향상에 도움되므로 옵티마이저는 항상 사용하는 방향으로 

실행 계획이 완성된다.

 

SKIP_SCAN & NO_SKIP_SCAN

인덱스 스킵 스캔은 인덱스의 선행 컬럼에 대한 조건이 없어도 옵티마이저 해당 인덱스를 사용할 수 있게 해주는 최적화 기능이다.

하지만 선행 컬럼이 유니크한 값의 개수가 많아지면 성능은 오히려 떨어지기 때문에 옵티마이저에게 스킵스캔 사용 여부 힌트를

제공할 수 있다.

 

INDEX & NO_INDEX

해당 힌트는 인덱스 힌트를 대체하기 위하여 제공하는 힌트이다.