Real MySQL 9-2장

김재연·2025년 7월 27일

리마큐

인덱스 머지

인덱스 머지란 인덱스를 하나만 활용하는 것이 아니라, 여러개의 인덱스를 활용하는 경우입니다.

그리고 인덱스 머지 실행 계획은 다음과 같이 3개의 세부 실행 계획으로 나누어 볼 수 있는데요, 이들의 차이점은 각각의 결과를 어떤 방식으로 병합할지를 나누는 것이에요.

다음 쿼리를 봐보죠

first_name, emp_no 에 각각 인덱스가 걸려있어요.

근데 AND 로 연결되어 있죠.

이 경우 intersect(교집합) 머지 계획이 실행됐다는 것을 확인할 수 있어요.

그냥 결과 조회해서 AND 연산하면 되는 거 아니야? 라고 생각할 수도 있지만, 아래의 쿼리 결과의 수를 보면 그런 말을 할 수가 없을 것입니다.

이 정도면 그냥 할 수 있는거 아니야? 라고 생각할 수 있지만 다음 결과를 보면

조건에 부합하는 결과는 14개 밖에 없어요.

즉, 253 개 중 239 개를 버렸어야 했구요. 10000 개를 먼저 가져왔다면 9986 개를 버렸어야 했어요

그러니까 옵티마이저는 인덱스 머지를 수립할 수 밖에 없었던 것이에요.

근데 ix_firstname 인덱스는 프라이머리 키인 emp_no 칼럼을 자동으로 포함하고 있기 때문에, 그냥 ix_firstname 인덱스만 사용하는 것이 성능이 더 좋을 것으로 생각할 수도 있어요.

그렇기 때문에 이는 비활성 할 수 있는 옵션을 제공합니다.

이것 역시 Scope 마다 제어 할 수 있는 영역을 선택할 수 있어요.

인덱스 머지 - 합집합

인덱스 머지의 Using union 은 where 절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화에요

Matt?

Using union 실행 계획이 수립된 것을 볼 수 있어요.

이것 역시 where 에 사용된 컬럼 모두 인덱스를 가지고 있기 때문에 가능했던 것입니다.

근데 이상해요.

union 하게 되면 중복을 방지해야하는데, 그러면 정렬을 진행해야하지 않나요?

MySQL 서버는 중복을 제거하기 위해서 정렬하는 방법을 많이 사용했으니까요, 하지만 여기는 결과들이 primary key 로 정렬이 되어있다는 점이 숨어있습니다.

위 그림은 인덱스 머지 최적화의 Union 알고리즘의 작동 방식을 그림으로 표현한 것이에요.

이미 emp_no 로 정렬이 되어 있기 때문에, 그냥 수들을 순서대로 순서대로 집어넣기만 하면 돼요.

그러다가 양쪽에 겹치는 숫자가 있다면, 그것은 중복이 발생한 것이죠.

그 때는 결과를 제외해주면 돼요.

이렇기 때문에, 정렬이 필요가 없었던 것이에요.

이 두개의 Select 의 결과가 그랬던 것이죠.

그래서 이것을 우선순위 큐라고 칭한다고 하는데, 솔직히 별로 중요하지는 않은 것 같습니다.

or 연산자로 연결된 경우 둘 중 하나라도 제대로 인덱스를 사용하지 못하면, 당연히 풀 테이블 스캔으로 밖에 처리될 수 없어요, 여기서 인덱스로 걸러도 다른 조건에서 나와버리면 인덱스가 무의미해질 수도 있으니까요

인덱스 머지 - 정렬 후 합집합

도중에 정렬이 필요한 경우도 있어요.

여기 조건은 Or, And 가 합쳐져서 써졌어요.

이것을 조금 더 잘게 나눠볼까요

첫 번쨰 쿼리는 결과가 emp_no 로 정렬되어 출력되지만, 두 번째 쿼리의 결과는 emp_no 칼럼으로 정렬되어 있지 않아요.

중복을 제거하기 위해 우선순위 큐를 사용하는 것이 불가능한 것이죠.

그렇기 떄문에, 정렬한 후 중복 제거를 진행해요.

해당 쿼리의 실행계획은 sort_union 인 것을 확인할 수 있죠.

세미 조인

다른 테이블과 실제로 조인을 수행하지 않고, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리를 세미 조인이라고 해요.

옛날에는 세미 조인 최적화가 굉장히 약했지만, 지금은 강해졌죠.

위의 쿼리를 예시로 살펴볼까요?

위 쿼리의 실행계획을 보면 그냥 57건만 읽으면 될 쿼리를 30 만 건 넘게 읽어서 처리된다는 것을 알 수 있어요. (그냥 57 개 읽고, id 로 검색)

예전 처리 방식으로 확인해보려고 하면 위처럼 하면 돼요.

이어서 말하자면 세미 조인 형태의 쿼리와 안티 세미 조인 형태의 쿼리는 최적화 방법이 조금 차이가 있어요.

우선 = (subquery) 형태와 In (subquery) 형태의 세미 조인 쿼리에 대해 다음과 같이 3가지 최적화 방법을 적용할 수 있어요.

<> (subquery), Not In (subquery) 는 다음과 같이 있구요.

이제 MySql 8.0 버전부터 세미 조인 쿼리의 성능을 개선하기 위해 어떠한 최적화 전략이 있는지 살펴볼게요.

다음 최적화 방법들이 있어요.

Table Pull-out 최적화 전략은 사용 가능하면 무조건 세미 조인보다 좋은 성능을 내기 때문에 별도로 제어하는 옵티마이저 옵션을 제공하지 않는다고 해요.

테이블 풀-아웃

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

위 쿼리가 의미하는 바는 부서가 d009 인 회사원을 조회하는 것이에요.

여기서 id 가 모두 1이라는 것을 확인할 수 있죠?

이거는 서브 쿼리 형태가 아니라 조인으로 처리되었음을 의미해요.

테이블 풀아웃 최적화가 사용되었는지는 따로 Extra 컬럼에 나오지 않아요.

그렇기 때문에 위처럼 id 를 확인해보거나, 다음과 같이 명령어를 통해 실제 진행된 쿼리를 확인해볼 수 있어요.

이 쿼리를 보면 In(subquery) 형태는 사라지고, JOIN으로 쿼리가 재작성된 것을 확인할 수 있어요.

다음과 같은 경우에서는 테이블 풀 아웃 최적화가 실행될 수 없어요.

퍼스트 매치

퍼스트 매치 최적화 전략은 In(subquery) 형태의 세미 조인을 Exists 형태로 튜닝한 것과 비슷하게 진행이 돼요.

이름이 Matt 인 사원중에서 직급이 1995-01-01 이나 1995-01-30 사이에서 변경된 적이 있는 사원을 조회하는 용도에요.

실행계획을 보니 id 도 같고 Extra 에도 퍼스트 매치라고 나왔어요.

퍼스트 매치는 간단해요.

각각 first_name 칼럼의 값이 Matt 인 사원의 정보를 ix_firstname 인덱스를 이용해 레인지 스캔으로 읽고, 그렇게 읽은 뒤에 emp_no 를 가지고 titles 테이블과 조인해서 조건에 부합하는 레코드가 있는지 확인해요.

그 다음에 하나라도 확인되면 바로 반환해버리죠.

불필요한 반복을 줄이는 것이에요.

근데 해당 최적화 방식은 MySQL 5.5 에서 수행했던 최적화 방법인 IN-to-EXISTS 변환과 거의 비슷해보여요. (그냥 IN 을 exists 로 바꾸는거겠지)

하지만, 퍼스트 매치는 조금 다른 점들이 있어요.

정리하면 퍼스트 매치에서는 조인 형태로 쿼리가 처리되어 아우터 쿼리의 테이블까지 전파될 수 있고, 이 과정에서 옵티마이저가 자동으로 추가한 동등 조건으로 인해서 더 많은 조건이 주어지며 더 나은 최적화가 이뤄질 수 있는 것이에요.

또한, IN-to-EXISTS 는 아무런 조건 없이 변환이 가능한 경우(쿼리 최적화) 무조건 수행했는데, 퍼스트 매치는 조금 더 발전해서 서브 쿼리의 모든 테이블에 대해 퍼스트 매치 최적화를 수행할지 일부 테이블에 대해서만 수행할지 선택할 수 있다는 점에서 조금 더 정밀해졌다는 것을 알 수 있어요.

또한 위는 퍼스트 매치의 제한 사항과 특성이에요.

아우터 테이블이 먼저 조회된 후에, 서브 쿼리를 실행하면서 맞는 결과가 나오면 끝내는 것이기 떄문에, 아우터 테이블을 전부 다 조회해야 한다는 단점이 있어요.

또한 Group By 나 집합 함수처럼 데이터의 특성(?)이 일부 휘발된 것, 즉 하나만 찾고 바로 빠질 수 있는 것이 아닌 쿼리 들에 대해서는 실행할 수 없다는 것을 알 수 있어요.

루스 스캔

루스 스캔 방식은 이전에 Group By 최적화 방법에서 살펴본 루스 인덱스 스캔과 굉장히 비슷한 읽기 방식을 사용한다.

위에서 보면 알 수 있듯이, dept_emp 테이블을 드라이빙 테이블로서 활용하며, 유니크한 dept_no 값들을 점프 점프 하면서, 레코드 한 건만을 조회하고, 바로 결과를 반환하는 것을 볼 수 있다.

또한 id 컬럼이 동일하게 1이라는 것은, MySql 내부적으로 조인처럼 처리됐다는 것을 말해준다.

예제에서 사용된 쿼리는 사실 루스 스캔 최적화 방식으로 처리하기에 좋은 예제이지만, MySQL 8.0 에서의 최적화의 성숙도가 좀 떨어져 Materialization 알고리즘으로 실행하려고해, 저자가 설정을 위와 같이 변경하고 실행했다는 내용이다.

루스 스캔 최적화 방식은 아우터 쿼리를 드리븐 테이블로 활용하고, 서브 쿼리는 드라이빙 테이블로 활용하기 때문에, 서브 쿼리가 루스 인덱스 스캔을 사용할 수 있는 조건이 갖춰져야 사용할 수 있다.

또한, 루스 스캔 최적화를 비활성화 하기 위해서는 아래와 같이 시스템 변수를 설정해주면 된다.

구체화 (Materialization)

해당 최적화 방식은 세미 조인에 사용된 서브 쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미다.

쉽게 표현하면 내부 임시 테이블을 생성한다는 것이다.

위 쿼리에서 퍼스트 매치 최적화 방식을 활용하면, employees 테이블을 걸러내는 조건이 없기 때문에 짜피 풀 스캔을 하게 될 것이다.

큰 의미가 없으니 다른 최적화 방식을 사용해야했고, 이 때 서브쿼리 구체화 최적화 방식을 사용하는 형태로 진행되는 것을 볼 수 있다.

이 때, 쿼리에 사용된 테이블은 2개의 테이블인데, 3개가 나오는 것부터 임시 테이블이 사용되었다는 것을 예측할 수 있다.

dept_emp 테이블을 읽는 서브쿼리가 먼저 실행되어 임시 테이블이 생성되고, 최종적으로 서브쿼리가 구체화된 임시 테이블과 employees 테이블을 조인해서 결과를 반환한다.

또한, 해당 방식은 Group By 절이 있어도 사용가능하다.

구체화 최적화 방식에는 아래와 같은 제한 사항과 특징이 있다.

중복 제거 (Duplicated Weed-out)

중복 제거 방식은 서브쿼리를 일반적인 Inner Join 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.

위 쿼리에서 어떻게 실행되는지 살펴보자.

위 쿼리에서 사용된 salaries 테이블은 프라이머리 키가 (emp_no, from date) 이므로 급여가 15만원 이상인 레코들르 찾게 되면 emp_no 의 중복이 발생할 수 있다.

그래서 위와 같이 Group By 절을 넣어주면 동일한 결과를 반환받을 수 있다.

실제로 중복제거 최적화 알고리즘은 원본 쿼리를 위와 같이 Inner Join + Group By 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리하게 된다.

위는 중복 제거 최적화 방식이 돌아가는 방식을 설명한 예제이다.

먼저 salary 테이블의 인덱스를 활용하여 조건에 부합하는 레코드를 빠르게 찾고 employees 테이블과 조인을 실행해주고, 그를 임시 테이블로 저장하게 됩니다.

임시 테이블에 저장된 결과에서 emp_no 기준으로 중복을 제거하고, 최종 반환하게 되는 것이다.

또한 실행계획에 Start temporary 가 표시된 것을 볼 수 있는데, 이는 조인을 한 결과를 임시테이블에 저장하기 때문에 생기는 결과이다.

퍼스트 매치, 루스 스캔을 비활성화 해놓고서야, 중복 제거 최적화 방식을 사용할 수 있었다고 저자는 설명한다.

중복제거 최적화가 가지는 장점과 제약사항은 위와 같다.

상관쿼리라는 말이 너무 자주나와서, 찾아봤더니 아래와 같은 의미를 가진다고 한다.

컨디션 팬아웃 (condition_fanout_filter)

조인하는 순서도 굉장히 중요하다.

만일 A 테이블에는 조건에 일치하는 레코드가 1만건 B 테이블에는 조건에 일치하는 레코드가 10건일 때, A 테이블을 조인의 드라이빙 테이블로 결정하게 되면, B 테이블을 1만번 읽어야 한다.

물론 최종적으로 레코드를 탐색하는 횟수는 동일하겠지만, B-Tree 의 루트 노드부터 검색을 실행해야 한다는 점이 이 쿼리의 효율을 결정한다.

그렇기 때문에, 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행해야 한다.

컨디션 팬아웃 필터를 off 시켜놓은 경우의 쿼리는 위와 같이 진행이 된다.

여기서 중요한 것은 employees 테이블의 row 칼럼의 값이 233 이고, filtered 칼럼의 값이 100% 라는 것이다.

옵티마이저가 이를 하나도 걸러내지 못한 것이다.

이제 컨디션 팬 아웃 필터를 활성화해주고 쿼리를 실행했더니, 칼럼의 값은 동일하지만, filtered 컬럼의 값이 23.20 으로 줄어들었다.

즉, 시스템 변수로 컨디션 팬 아웃 필터 기능이 활성화 되면서, 인덱스를 사용할 수 있는 first_name 칼럼 조건 이외 나머지 조건에 대해서도 얼마나 조건을 충족할지를 고려했다는 것이다.

컨디션 팬 아웃을 켠 결과 employees 테이블에서 54건만 조건을 충족할 것이라고, 예측한 것을 볼 수 있고, 이렇게 더 정확한 건수를 예측한다면 더 빠른 실행계획을 만들어낼 수 있는 것이다.

컨디션 팬 아웃 최적화가 filtered 칼럼의 값을 예측해내기 위해 사용되는 조건들은 위와 같다.

즉, first_name 칼럼의 인덱스를 이용해 대략 first_name = 'Matt' 조건에 일치하는 레코드 건수가 233 건 정도라는 것을 알아내고, hire_date 칼럼의 조건을 만족하는 레코드의 비율이 대략 23.2% 일 것으로 예측한다.

또한 통계 정보만 사용하는 것이 아니고, 위와 같은 방식들을 순차적으로 탐색하여 사용 가능한 방식이 나오게 되면, 바로 사용하게 된다.

정리하면, 컨디션 팬 아웃 최적화는 정교한 계산을 거쳐서 실행 계획을 수립할 수 있게끔한다.

그렇기 때문에, 컴퓨팅 자원을 더 많이 활용하는데, 쿼리 계획을 너무 잘못 짠 것이 아닌 이상 성능상 차이가 유의미하지 않다고 한다.

그렇기 때문에, 해당 조건을 켜놓을 때에는 꼭! 성능 테스트를 진행해보고 켜라고 저자가 일러두었다.

파생 테이블 머지

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

이는 결과를 Insert 하고, 다시 읽는 작업을 하기 때문에, 그만큼의 오버헤드가 추가된다.

그리고 내부적으로 처음에 생성될 때에는 메모리에 생성되지만, 어느정도 크기가 커지게 되면 디스크에 생성된다.

그렇다보니, 실제로 디스크 I/O 가 발생하게 될 것이고, 이는 FROM 절에 명시된 서브쿼리에 부합하는 레코드가 많아질 수록 더욱 더 성능 차이가 많이 일어난다라는 것으로 귀결된다.

현재 임시테이블 생성 방식을 어떻게 정하게 되었는지 설명하는 글이다.

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

이는 derived_merge 최적화 옵션에 따라 결정된다.

위 실행 계획을 보면 select type 에 Derived 값을 가진 레코드가 사라진 것을 볼 수 있다.

실제로 실행된 쿼리를 보면, 위와 같이 FROM 절에 사용된 서브쿼리를 외부 쿼리로 병합하는 작업을 진행해준 것을 볼 수 있다.

현재는 옵티마이저가 많이 해주지만, 위와 같은 경우들에서는 자동으로 해주지 못한다, 이런 경우는 수동으로 서브쿼리를 외부쿼리로 빼주는 작업을 진행해주어야한다.

인비저블 인덱스(use_invisible_indexes)

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

인덱스를 삭제하지 않고, 옵티마이저가 해당 인덱스를 사용하지 못하게 제어하는 기능이다.

use_invisible_indexes 상태를 조정하면 된다.

아래와 같이 말이다.

스킵 스캔

이전에 다루었던 스킵스캔과 동일하다.

인덱스 조건 중 앞선 하나의 컬럼이 생략되어도 내부적으로 쿼리를 해당 칼럼을 활용할 수 있도록 구성하여 인덱스를 활용할 수 있도록 해주는 기법이다.

위와 같은 테이블과, 위와 같은 쿼리가 있다고 가정하자.

스킵 스캔을 통해서 첫번째 쿼리를 인덱스를 활용할 수 있는 것이다.

이를 통해서 실행되는 과정은 유니크한 gender 값들을 가져오고, 쿼리들을 새로 구성하여 유니크한 gender 값마다 쿼리를 날려주는 것이다.

예측이 되듯이, 선행 칼럼의 값이 너무 다양하다면, 이는 성능저하로 이어질 수 있기 때문에, 옵티마이저는 유니크한 값의 개수를 보고, 해당 최적화를 실행하게 된다.

인덱스 스킵 스캔을 활용하기 위해서 시스템 변수를 활용하고 힌트를 활용하는 것을 볼 수 있다.

해시 조인

해시 조인과 네스티드 루프 조인의 특성

A 지점은 쿼리가 실행되면서 MySQL 서버가 첫번째 레코드를 찾아낸 시점이며, B 지점은 MySQL 서버가 마지막 레코드를 찾아낸 시점을 의미합니다.

네스티드 루프 조인은 첫번째 레코드를 찾는 것이 굉장히 빠릅니다.

하지만, 쿼리의 결과를 내뱉기까지 시간이 많이 걸리죠.

하지만, 해시조인은 조금 다릅니다.

첫번째 레코드를 찾는 것이 느리지만, 쿼리의 결과는 그만큼 빨리 뱉게 됩니다.

이는 해시 조인을 선택할 것인지, 네스티드 루프를 선택할 것인지에 대한 판단 기준이 됩니다.

전체적(스루풋)으로 보았을 때, 빠르게 처리할 것이냐, 혹은 빠른 응답을 받아볼 것이냐이죠.

이러한 특성으로 인하여 웹 서비스는 꽤나 명확하게 두 가지 방법중 하나를 선택할 수 있습니다.

스루풋도 중요하긴 하지만, 웹은 빠른 응답이 더 중요합니다. 사용자를 지루하게 만들면 안되니까요.

MySQL 은 범용 RDBMS며, 여기에서 범용이라 함은 온라인 트랜잭션 처리를 위한 데이터베이스 서버를 지칭하는 것입니다.

그렇기 때문에, MySQL 서버는 주로 조인 조건의 칼럼이 인덱스가 없다거나, 조인 대상 테이블 중 일부의 레코드 건수가 굉장히 적은 경우에만 해시 조인 알고리즘을 사용합니다.

즉, 해시 조인은 차선책으로 사용되는 것이죠.

해시 조인이 사용될 때

MySQL 8.0.17 버전까지는 해시 조인 기능이 없었습니다.

그렇기 때문에, 조인 조건이 좋지 않은 경우 블록 네스티드 루프 조인(조인 버퍼를 사용해서 조인을 진행)이라는 조인 알고리즘을 사용했죠.

하지만 조인 버퍼를 무한정 크게 설정할 수 없으며, 조인 대상 테이블의 레코드 크기가 조인 버퍼보다 큰 경우에는 드라이빙 테이블(조인의 주가 되는 테이블)을 여러 번 반복해서 스캔해야 하는 문제점이 있었습니다.

그렇기 때문에, MySQL 8.0.20 버전 부터는 네스티드 루프 조인을 사용할 수 없는 경우에는 항상! 해시 조인을 사용하도록 바뀌었습니다.

해시 조인 최적화 방법

일반적으로 해시 조인은 빌드 단계, 프로브 단계로 나뉘어 처리됩니다.

빌드 단계에서는 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라 메모리에 해시 테이블을 생성하는 작업을 먼저 수행해주게 됩니다. (해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블이라고 합니다.)

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

이 때 읽는 테이블 나머지 테이블을 프로브 테이블이라고 합니다. (조인을 위해 해시 테이블에서 맞는 값을 찾기 때문이겠죠?)

이제 상세한 과정을 알아볼까요?

dept_emp, employees 테이블이 있고, 서로 emp_no 로 조인된다고 가정했을 때, 해시 조인은 다음과 같이 진행이 되게 됩니다.

dept_emp 테이블을 빌드 테이블로 지정하여 빌드 단계를 거쳐 해시 테이블을 메모리에 만들게 됩니다.

그리고 employees 테이블을 순차적으로 돌면서 emp_no 값으로 해시 테이블을 검색 및 조인하게 되죠.

알고리즘으로 따지면 그냥 이런 느낌일 것 같아요

public List<Record> join(Table1 table1, Table2 table2) {
	Map<Integer, Record> hash = table1.getRecords().. // 묶어주는 연산
  
	return talbe2.getRecords().map(() -> {
        ... 해시에서 찾아와서 조인
	}).toList();
}

당연히 빠르겠죠?

하지만, 이렇게 항상 좋은 경우만 존재하는 것은 아닙니다.

메모리 공간이 부족할 수 있어요.

그런 경우, 메모리가 부족한 경우 빌드 테이블 청크, 프로브 테이블 청크로 나누어 디스크 공간에 저장합니다.

그러고서 다음과 같이 처리해요

빌드 테이블 청크로 해시를 구성합니다. (메모리가 부족해서 청크로 만든거니까 당연히 조금씩 올리겠죠?)

그 다음에 프로브 테이블 청크를 전부 돌면서 빌드 테이블 청크로 구성된 해시에서 일치하는 레코드를 찾아 조인한 뒤 결과를 반환합니다.

즉, 메모리 공간이 부족한 경우 조인 결과가 1, 2차가 존재하는 것입니다

인덱스 정렬 선호(prefer_ordering_index)

MySQL 옵티마이저는 Order By 또는 Group By 를 인덱스를 사용해 처리 가능한 경우 실행 계획에서 이 인덱스의 가중치를 높이 설정해서 실행됩니다.

이 쿼리는 대쵸적으로 2가지 실행계획을 선택할 수 있습니다.

당연하게도 1번이 효율적일 수도, 2번이 효율적일 수도 있습니다.

일반적으로 hire_date 컬럼의 초건에 부합되는 레코드 건수가 많지 않다면 1번이 효율적이겠죠?

하지만, 2번 실행계획을 선택할 수도 있습니다.

풀테이블 스캔하면서 hire_date 칼럼의 값을 필터링 조건으로 처리하는 것이죠.

이렇게 체크해야 하는 레코드 건수가 상당히 많아도, 정렬된 인덱스 활용으로 실행 계획이 수립되는 것은, 옵티마이저의 실수일 확률이 상당히 높습니다.

그렇기 때문에, 이러한 실수를 방지하기 위해 특정 인덱스를(Order By를 위한 인덱스) 사용하지 못하게 하는 힌트 혹은 시스템 변수를 통해 설정할 수 있게 되었습니다.

그래서 Order By 를 위한 인덱스에 너무 가중치를 부여하지 않도록 하는 것이죠.

조인 최적화 알고리즘

MySQL 에는 조인 쿼리의 실행 계획 최적화를 위한 알고리즘이 2개 있습니다.

사실 옛날에는 테이블의 개수가 많아질 수록 시간은 기하급수적으로 증가되어왔습니다.

이제부터 그 이유를 살펴보시죠.

이런 쿼리가 있다고 했을 때, 조인 최적화 알고리즘을 순차적으로 살펴보겠습니다.

Exhaustive 알고리즘

다음과 같이 그냥 브루트 포스를 하는 것입니다.

만일 테이블이 20개라면? 20! 으로 3628800 개의 경우의 수를 돌아야하는 것입니다.

이렇기 때문에, 예전에는 테이블이 10개만 넘어도 실행 계획을 수립하는 데 몇분이 걸렸습니다.

이를 보완하기 위해 아래와 같은 알고리즘이 나오게 되었습니다.

Greedy 검색 알고리즘

이는 시스템 변수 search_depth 의 값에 따릅니다.

여기서는 2라고 가정하고 넘어가보도록 하죠.

실행계획이 위처럼 복잡하게 나와있습니다.

그냥 간단하게 말하면, 현재 선택할 수 있는 테이블 중(아직 선택되지 않은 테이블) search_depth 개수까지 테이블을 선택하는 경우의 수를 구하는 것입니다.

그리고 거기서 구한 경우의 수중 가장 효율적인 최소 비용의 실행 계획을 가진 것을 하나 설정하는 것이죠.

그리고 최소 비용의 실행 계획을 가진 테이블들 중에서 첫번째 테이블을 먼저 실행될 테이블로 설정하는 것입니다.

이를 모든 테이블을 선택할 때까지 반복하면 될 것입니다.

당연히 알 수 있듯이, search_depth 의 값에 따라 탐색되는 경우의 수는 엄청나게 줄어들 것입니다.

그리고 조인 최적화를 위한 시스템 변수로 optimizer_prune_level 과 앞서 말한 search_depth 가 있습니다.

저는 search depth 가 쿼리의 속도에 엄청난 영향을 주는 줄 알았습니다.

하지만, 다음 실험을 통해 나온 결과를 한번 봐보죠

테이블을 위와 같이 설정, 그리고 쿼리를 위와 같이 날린다고 가정해봅시다.

그냥 보기만해도 테이블이 정말 많은 것을 볼 수 있는데요.

이 때 Prune 을 활성화하여 Heuristic 최적화 방식(현재 탐색하고 있는 방식보다 최적의 방식이 이미 존재한다면, 더 이상 실행하지 않는 똑똑한 방법, 일종의 백트래킹)을 활성화해봅시다.

이 경우 search_depth 를 62까지 변화시키면서 쿼리를 실행하더라도 거의 시간 차이 없이 0.01 초 이내에 완료되었습니다. (진짜 최적화가 엄청난가 봅니다.)

하지만, Prune 을 끄니까? 역시 search_depth 의 변화에 따라 엄청난 변화폭이 생기는 것을 볼 수 있습니다.

이래서 절대로 Prune 의 값을 변경하면 안된다는 것을 배웠습니다.

쿼리 힌트

서비스 개발자나 DBA 보다 MySQL 서버가 부족한 실행 계획을 수립할 때가 있습니다.

이런 경우 극한의 튜닝을 위해 힌트를 사용하는 경우가 많습니다.

그 때 사용할 수 있는 힌트의 종류로는 위 2가지가 존재합니다.

Straight_join (인덱스 힌트)

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

위 쿼리는 3개의 테이블을 조인하지만 어느 테이블이 드라이빙 테이블이 되고 드리븐 테이블이 되는지 알 수 없다.

위에서 실행한 쿼리는 옵티마이저가 최적의 실행 계획을 수립한 뒤 실행한 계획이다.

이러한 계획을 아래와 같이 조정할 수 있다.

두 예제 모두 Straight_join 키워드가 select 키워드 바로 뒤에 사용됐다는 것에 주의하자.

이처럼 힌트는 어디에 명시되어야 하는지 이미 다 정해져있다.

Straigth_join 힌트는 옵티마이저가 from 절에 명시된 순서대로 조인을 수행하도록 유도한다.

위 실행계획을 보면 e(employees) -> de(dept_emp) -> d(departments) 순서대로 조인이 수행되었다는 것을 볼 수 있다.

주로 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 힌트로 조인 순서를 조정하는 것이 좋고, 이외에는 추천하지 않는다고 한다.

여기서 언급한 레코드 건수라는 것은 인덱스를 사용할 수 있는 where 조건까지 포함해서 그 조건을 만족하는 레코드 건수를 의미하는 것이지, 무조건 테이블 전체의 레코드 건수를 의미하는 것은 아니다.

위와 같은 경우 employees 의 테이블 건수가 훨씬 많지만, 조건을 만족하는 employees 테이블의 레코드 건수가 훨씬 적은 경우, straight join 을 활용하여 조인 순서를 강제할 수 있다. (물론 옵티마이저가 알아서 잘해주겠지만)

straight_join 힌트와 비슷한 역할을 하는 옵티마이저 힌트로는 다음과 같은 것들이 있다.

use index / force index / ignore index

조인의 순서를 변경하는 것 다음으로 자주 사용되는 것이 인덱스 힌트이다.

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

위와 같이 3가지 힌트가 존재하고, 모두 용도를 명시해줄 수 있다. 하지만, 용도가 명시되지 않으면 아래와 같은 용도로 사용된다.

이렇게 용도를 3가지로 나누긴 했지만, order by 나 group by 작업에서 인덱스를 사용할 수 있다면, 나은 성능을 보장하며, 용도는 옵티마이저가 대부분 최적으로 선택해주기 때문에, 고려할 필요는 없다.

이론은 여기까지 하고, 쿼리를 수행해보자.

물론 위와 같은 쿼리들은 옵티마이저가 대부분 최적화해주니, 굳이 확실한 경우가 아니라면 힌트를 사용하지 않도록하자.

Sql_calc_found_rows

Limit 을 명시하면 조건에 만족하는 레코드의 수가 limit 에 맞춰지면 멈춘다.

하지만, 위와 같은 힌트를 사용하게 되면, 다 찾았더라도, 총 레코드 수를 찾기 위해서 쿼리를 멈추지 않는다.

이는 쿼리의 성능 저하로 이어질 수 있다.

위와 같이 결과를 확인할 수 있고

실제로 사용할 때 위와 같이 진행할 수 있을 것 같다.

하지만 위 쿼리는 아래의 쿼리에 대체될 수 있고, 위 쿼리는 랜덤 I/O 가 조건에 부합하는 레코드 수만큼 발생하고, 아래 쿼리는 카운트는 커버링 인덱스를 활용하고, 레코드도 Limit 수만큼만 가져와 랜덤 I/O 가 그만큼만 발생하기 때문에 성능이 훨씬 좋다.

이러한 힌트가 개발자의 편의를 위해 생겨난 쿼리라고 한다면, 왜 이렇게 비효율적인지 알 수 있을 것이다.

옵티마이저 힌트

옵티마이저 힌트의 영향 범위는 위 4가지로 나뉘어진다.

힌트의 사용 위치가 달라지는 것은 아니고, 그냥 영향 범위만 달라지는 것이다.

힌트의 종류와 설명, 영향 범위로는 아래 장표를 보면 된다.

종류로는 위와 같이 엄청 많다.

또한 모든 인덱스 수준의 힌트는 반드시 테이블 명이 선행되어야 하는데, 잘못 사용하게 되면, 경고 메시지가 출력된다고 한다.

힌트를 사용할 때면, Explain 을 통해서 문법 오류가 있는지 항상 확인해보도록하자.

하나의 SQL 문장에서 select 키워드는 여러 번 사용될 수 있다. 이 때 각 select 키워드로 시작하는 서브 쿼리 영역을 쿼리 블록이라고 한다.

이 때, 특정 쿼리 블록을 외부 쿼리 블록에서 사용하려면 QB_NAME() 이라는 힌트를 이용해 해당 쿼리 블록에 이름을 부여해야 한다.

위는 이를 실제로 사용하는 예제이다.

이제부터는 하나하나 힌트가 어떻게 사용되는지 빠르게 살펴보도록 하겠다.

Max_execution_time

옵티마이저 힌트 중에서 유일하게 쿼리의 실행 계획에 영향을 끼치지 않고, 쿼리의 수행 시간에만 영향을 주는 힌트이다.

Set_var

set var 힌트는 실행 계획을 바꾸는 용도 뿐만 아니라 조인 버퍼나 정렬용 버퍼(소트 버퍼)의 크기를 일시적으로 증가시켜 대용량 처리 쿼리의 성능을 향상시키는 용도로도 사용할 수 있다.

SEMIJOIN & NO_SEMIJOIN

세미 조인의 최적화는 여러 가지 세부 전략이 있다는 것을 이미 살펴봤다.

Table pull out 은 사용하면 무조건 이득이기 때문에 힌트를 사용할 수 없다.

하지만, 다른 최적화 전략들은 상황에 따라 더 나은 성능을 보장할 수 있는 대안들이 존재하기 때문에, NO_SEMIJOIN 힌트도 제공되는 것이다.

위 쿼리는 퍼스트 매치 최적화 방식을 사용한다.

다음 예제는 위의 쿼리가 다른 최적화 전력을 사용하도록 세미 조인 힌트를 사용한 것이다.

세미 조인 최적화 힌트는 외부 쿼리가 아니라 서브 쿼리에 명시한다.

위에서 언급했던 QB_NAME 에 쿼리 블록의 이름을 정의하고 밖에서 정의하는 방식도 있다.

특정 세미 조인 최적화 전략을 사용하지 않게 하려면 다음과 같이 NO_SEMIJOIN 힌트를 명시해야한다.

Sub Query

서브 쿼리 최적화는 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법으로 서브쿼리는 다음 2가지 형태로 최적화할 수 있다.

세미 조인 최적화는 주로 IN(subquery) 형태의 쿼리에 사용될 수 있지만, 안티 세미 조인의 최적화에는 사용될 수 없다.

그래서 주로 안티 세미 조인 최적화에는 위의 2가지 최적화가 사용되게 된다.

서브쿼리 최적화 전략은 사용할 기회가 많지 않으므로 자세한 예시는 들지 않아줬다.

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

다음과 같이 BNL 과 NO_BNL 힌트를 사용해 해시 조인을 유도하거나, 해시 조인을 사용하지 않게 할 수 있게 됐다.

실제로 MySQL 서버는 인덱스가 존재한다면 해시 조인은 거의 사용되지 않기 때문에, 위에서 실제로 해시조인이 수행되게끔 하려면 인덱스가 사용되는 컬럼들을 지워주어야 한다.

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

이전에 straight join 은 너무 사용자에게만 책임이 주어졌다.

이제 옵티마이저에게도 적절하게 책임을 부여하기 위해 위와 같은 힌트들이 제공이 된다.

간단하게 위 4가지 힌트들을 사용한 예제이다.

Merge & No_merge

앞서 살펴봤듯이, 임시 테이블을 생성하지 않게 끔 하는 것이 Merge 였다.

해당 최적화 방식을 사용할 것인지 아닌지 선택하는 힌트가 아래와 같은 힌트이다.

Index Merge & No Index Merge

MySQL 서버는 가능하다면 테이블당 하나의 인덱스만을 이용해 쿼리를 처리하려고 한다.

하지만, 하나의 인덱스만으로 검색 대상 범위를 충분히 좁힐 수 없다면, MySQL 옵티마이저는 사용 가능한 다른 인덱스를 이용하기도 한다.

여러 인덱스를 통해 검색된 레코드로부터 교집합 또는 합집합만을 구해서 그 결과를 반환한다.

이처럼 하나의 테이블의 여러 개의 인덱스를 동시에 사용하는 것이 인덱스 머지인데, 이를 조절하기 위해서 아래와 같이 힌트를 사용할 수 있다.

No ICP

인덱스 컨디션 푸시다운(ICP) 최적화는 사용가능하다면 항상 성능 향상에 도움이 되므로 옵티마이저는 최대한 인덱스 컨디션 푸시다운 기능을 사용하는 방향으로 실행 계획을 수립한다.

하지만, 인덱스 컨디션 푸시다운으로 인해 여러 실행 계획의 비용 계산이 잘못된다면 결과적으로 잘못된 실행 계획을 수립하게 될 수도 있다.

예를 들어 A 인덱스와 B 인덱스 둘 중에서 하나를 선택해야 하는 상황에서 A 인덱스에서는 인덱스 컨디션 푸시다운이 가능해서 A 인덱스를 사용하는 것이 비용이 낮게 예측됐다고 가정해보자.

하지만, 실제로 B 인덱스를 사용하는 것이 더 효율적일 수도 있다.

그렇기 때문에, ICP 최적화만 비활성화해서 조금 더 유연하고 정확하게 실행 계획을 선택하게 할 수 있다.

Skip Scan & No Skip Scan

이는 이전에도 살펴봤듯 스킵 스캔의 단점을 방지하기 위해서 스킵 스캔을 사용하지 못하도록 하는 방법도 있다.

김재연

끊임없이 '성장'하는 개발자 김재연입니다.

이전 포스트

Real MySQL 9-1장

다음 포스트